PostgreSQL idle_in_transaction_session_timeout seems to have no effect

Question

I'm working with a PostgreSQL server version 10.5 (can't upgrade it at the moment) running in Docker. XL Deploy is connected to it and I upload a new archive that is 232MB. I get the error FATAL: terminating connection due to idle-in-transaction timeout.

In the config, I saw that idle_in_transaction_session_timeout was set to 11000ms. I updated the value to 600000ms and reload the configuration. I see the message LOG: parameter "idle_in_transaction_session_timeout" changed to "600000" in the logs, so I know the setting has taken.

However, when I upload the archive again, I still get the same FATAL timeout message. It's as if the setting is having no effect.

I think it can be set from the client end too - perhaps check what the setting is in your client code. — Richard Huxton
– Richard Huxton, Commented May 17, 2022 at 12:45

sffortytwo · Accepted Answer · 2022-05-19 12:18:41Z

6

As it turns out, the issue was in Postgres, but not in the config file. It seems you can update the value of idle_in_transaction_session_timeout at various levels. In my case, it was at the ROLE level. Issuing this SQL statement fixed the timeout.

ALTER ROLE role_abc SET idle_in_transaction_session_timeout = '10min';

answered May 19, 2022 at 12:18

sffortytwo

3271 gold badge5 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vrms Over a year ago

how can you detect this was set on the role level? pg_dumball --globals-only comes to mind.

sffortytwo Over a year ago

I don't know. Just trial and error.

Collectives™ on Stack Overflow

PostgreSQL idle_in_transaction_session_timeout seems to have no effect

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related