FATAL: terminating connection due to idle-in-transaction timeout

[nagaraju11]

When I tried to repack my bloated table an error occurred:

FATAL: terminating connection due to idle-in-transaction timeout
ERROR: query failed: SSL connection has been closed unexpectedly
DETAIL: query was: SAVEPOINT repack_sp1

and this error is occurring in large tables only, and current table size which is running about 700GB

/pg_repack --version
pg_repack 1.4.3

DB version: PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit

[Melkij]

Hello
You need disable idle_in_transaction_session_timeout. This is postgresql setting and it is disabled by default. pg_repack does not handle this case.

[nagaraju11]

Yes its disable. show idle_in_transaction_session_timeout; - - - 0 Thanks

…

On Tue, Jan 7, 2020 at 2:22 AM Melkij ***@***.***> wrote: Hello You need disable idle_in_transaction_session_timeout. This is postgresql setting and it is disabled by default. pg_repack does not handle this case. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#222?email_source=notifications&email_token=AFMFC2JTLSFAU7MPLN3UZLLQ4RJWRA5CNFSM4KDL5HX2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEIIM5FI#issuecomment-571526805>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFMFC2IBEIGXO4UPN34Q6HTQ4RJWRANCNFSM4KDL5HXQ> .

[Melkij]

Is it on the same user and database? I means such timeout may be per-user or per-database setting (\drds in psql)
terminating connection due to idle-in-transaction timeout is clearly related only to idle_in_transaction_session_timeout https://github.com/postgres/postgres/blob/REL9_6_11/src/backend/tcop/postgres.c#L3019

[nagaraju11]

Yes, I’m using same user. It is working fine on small tables in size.

…

On Tue, Jan 7, 2020 at 2:34 AM Melkij ***@***.***> wrote: Is it on the same user and database? I means such timeout may be per-user or per-database setting (\drds in psql) terminating connection due to idle-in-transaction timeout is clearly related only to idle_in_transaction_session_timeout https://github.com/postgres/postgres/blob/REL9_6_11/src/backend/tcop/postgres.c#L3019 — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#222?email_source=notifications&email_token=AFMFC2JBWNM456RLUPSZFB3Q4RLBZA5CNFSM4KDL5HX2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEIIN4PI#issuecomment-571530813>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AFMFC2MCAVJNDYEUBXJYY73Q4RLBZANCNFSM4KDL5HXQ> .

[nagaraju11]

yeah its working now.

I have disabled idle_in_transaction_session_timeout but did not reload the config file. its working fine after reloaded the server.

Thank you

[davidklika]

Hello. It is not very nice to reconfigure (especially production) database server before and after every db maintenance task. Would it be possible to set idle_in_transaction_session_timeout to zero locally in pg_repack sessions? (for version 9.6 and newer). Any comments? Thank you

[bukem]

Agree with @davidklika and I also have another question - why does pg_repack has transaction that fails with idle_in_transaction timeout?
Thanks