PostgreSQL connection timeout caused MaterializedPostgreSQL to fail.

Company or project name

null

Describe what's wrong

Hello, I am a PostgreSQL developer. Our users have encountered an issue when using ClickHouse's MaterializedPostgreSQL feature:

ClickHouse uses connection 1 to execute EXPORT_SNAPSHOT and then waits for the initial_sync process to complete before committing the transaction. During this period, connection 2 executes "SET TRANSACTION SNAPSHOT xxx" and then proceeds with initial_sync.

If connection 1 exits early, for example, due to the postgres parameter idle_in_transaction_session_timeout, the postgres log will show "FATAL: terminating connection due to idle-in-transaction timeout". In such cases, connection 2 will receive an error "ERROR: invalid snapshot identifier: xxx" when executing "SET TRANSACTION SNAPSHOT", because postgres deletes the exported snapshot upon transaction completion.

In the postgres autovacuum processes, as well as in tools like pg_dump and pg_rewind, parameters such as

statement_timeout=0
lock_timeout=0
idle_in_transaction_session_timeout=0

are often set after establishing a connection to ensure stability.

Following this idea, I reviewed ClickHouse's implementation in src/Core/PostgreSQL/Connection.cpp. In the "updateConnection()" function, only the default_transaction_isolation parameter is set. I believe setting the above-mentioned timeout parameters here would be beneficial for the connection stability of ClickHouse.

Does it reproduce on the most recent release?

Yes

How to reproduce

null

Expected behavior

No response

Error message and/or stacktrace

No response

Additional context

No response