PostgreSQL `set statement_timeout = 0;` doesn't seem to work – why?

Question

I'm connecting to PostgreSQL using command

psql "host=localhost port=6432 dbname=mydatabase user=myuser password=mypassword connect_timeout=5"

and I want to run vacuum analyze but the default statement_timeout for the server is set to 30 minutes, which is not enough for this query (yeah, I found this out after trying it a few times).

However, when I run following query, it goes as follows:

set statement_timeout = 0; show statement_timeout;
SET
 statement_timeout 
-------------------
 30min
(1 row)

And I don't see any errors in the logs either! How on earth the SET statement can succeed but still have no effect?

Answer 1

You're connecting to port 6432 instead of PostgreSQL default 5432. Are you connecting to PostgresqL via PgBouncer?

If you're running PgBouncer in pool_mode=transaction, that would explain the behavior you're seeing. This is because the transaction mode of the PgBouncer will reset the state of the connection between each query. In practice, the query set statement_timeout = 0; is executed successfully and does have effect. After that PgBouncer resets the state before executing the next query show statement_timeout; which obviously will show the default state after the reset. And similarly if you try to run

set statement_timeout = 0; vacuum analyze;

the VACUUM will be executed after resetting the connection state and that will use the default timeout, too.

When you use pool mode transaction, you should typically run only full transactions such as begin; set LOCAL statement_timeout = 0; ...; commit but vacuum cannot be run within a transaction which prevents using this solution for executing vacuum analyze.

Probably the best solution is to connect to PostgreSQL directly, instead of using PgBouncer in between. That would allow setting the statement_timeout only for that connection and successfully allow running the vacuum analyze. If you cannot do that, you have to set database or role specific default value for the timeout instead and simply use the default value for the connection while using pool mode transaction for the PgBouncer.