Why can't I override statement_timeout in Dapper?

Question

I have postgresql statement timeout set globally like this:

ALTER ROLE user SET statement_timeout = '10s';

When I have a long running query I can override the timeout like this and it works fine from my DB IDE:

SET statement_timeout TO '0';
SELECT pg_sleep(20);

But when I execute the same thing from Dapper it throws the exception:

57014: canceling statement due to statement timeout

How can I make it work in Dapper?

Answer 1

The statement_timeout setting takes effect only starting from a next statement sent over network. My IDE sends multiple statements one by one while Dapper sends them all in one roundtrip. So to solve this I had to just split the thing into 2 Execute calls:

c.Execute("SET statement_timeout TO '0';");
c.Execute("SELECT pg_sleep(20);");

btw SET is applied to a session while SET LOCAL is applied to a transaction.