Postgres "CREATE TABLE AS (SELECT ...)" stuck

Question

I am using Python with psycopg2 2.8.6 against Postgresql 11.6 (also tried on 11.9)

When I am running a query

CREATE TABLE tbl AS (SELECT (row_number() over())::integer "id", "col" FROM tbl2)

Code is getting stuck (cursor.execute never returns), killing the transaction with pg_terminate_backend removes the query from the server, but the code is not released. Yet in this case, the target table is created.

Nothing locks the transaction. The internal SELECT query on its own was tested and it works well.

I tried analysing clues on the server and found out the following inside pg_stat_activity:

• Transaction state is idle in transaction
• wait_event_type is Client
• wait_event is ClientRead

The same effect is happening when I am running the query from within SQL editor (pgModeler), but in this case, the query is stuck on Idle state and the target table is created.

I am not sure what is wrong and how to proceed from here. Thanks!

Answer 1

I am answering my own question here, to make it helpful for others.

The problem was solved by modifying tcp_keepalives_idle Postgres setting from default 2 hours to 5 minutes.