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!

Looks similar problem here , https://stackoverflow.com/questions/11306583/postgres-client-locking-up-when-creating-new-table — Sujitmohanty30, Sep 02 '20 at 15:38
Then your "standard SQL editor" has the same problem. Believe me or not: if the session is **idle in transaction** and **waiting to read from the client**, that's exactly what it is doing. It is the client that is confused and sits on its thumbs rather than proceed. — Laurenz Albe, Sep 04 '20 at 14:30
@LaurenzAlbe, it happens on this specific kind of query (the query itself is long and contains references to ~500 columns) with different DB instances and table (also long queries) and 2 different kinds of SQL editors. Also reproduced on v11.7. It's only happening with CREATE TABLE AS SELECT or SELECT INTO queries and nothing special is reported in logs. Is there any way to trace what exactly happens to the connection and why Postgres thinks the connection is and waiting and client gets stuck? Tnx — Meir Tseitlin, Sep 04 '20 at 14:36
You'd have to trace or debug the client code. Perhaps there is a timeout that causes misbehavior if the query takes too long, but I am only guessing. If psycopg2 is used in both cases, maybe the problem is there. I can guarantee that this has nothing to do with the database server. — Laurenz Albe, Sep 04 '20 at 14:41
What does `SELECT (row_number() over())::integer "id", "col" FROM tbl2` returns ? ;-) Also, are you expecting a duplicate of the table or a calculated table updating over time ( VIEW) ? — Martial P, Sep 07 '20 at 13:58
I don't see how this question is related to a programming problem. The problem persists for SQL editors also, so maybe is better to move your question on another Stack site? — Carlo Zanocco, Sep 08 '20 at 14:32

score 2 · Answer 1 · answered Dec 01 '20 at 20:51

2

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.

answered Dec 01 '20 at 20:51

Meir Tseitlin

1,878
2
17
28

Carlo Zanocco · Answer 2 · 2020-09-09T09:39:22.057

The problem is not reporducible, you have to investigate more. You must share more details about your database table, your python code and server OS.

You can also share with us the strace attached to Python, so we can see what actually happens during the query.

wait_event_type = Client: The server process is waiting for some activity on a socket from user applications, and that the server expects something to happen that is independent from its internal processes. wait_event will identify the specific wait point.
wait_event = ClientRead: A session that waits for ClientRead is done processing the last query and waits for the client to send the next request. The only way that such a session can block anything is if its state is idle in transaction. All locks are held until the transaction ends, and no locks are held once the transaction finishes.
Idle in transaction: The activity can be idle (i.e., waiting for a client command), idle in transaction (waiting for client inside a BEGIN block), or a command type name such as SELECT. Also, waiting is appended if the server process is presently waiting on a lock held by another session.

The problem could be related to:

Network problems
Uncommitted transaction someplace that has created the same table name.
The transaction is not committed

You pointed out that is not a commit problem because the SQL editor do the same, but in your question you specify that the editor succesfully create the table.

In pgModeler you see idle, that means the session is idle, not the query.

If the session is idle, the "query" column of pg_stat_activity shows the last executed statement in that session. So this simply means all those sessions properly ended their transaction using a ROLLBACK statement.

If sessions remain in state idle in transaction for a longer time, that is always an application bug where the application is not ending the transaction.

You can do two things:

Set the idle_in_transaction_session_timeout so that these transactions are automatically rolled back by the server after a while. This will keep locks from being held indefinitly, but your application will receive an error.
Fix the application as shown below

`.commit()` solution

The only way that I found to reproduce the problem is to omit the commit action.

The module psycopg2 is Python DB API-compliant, so the auto-commit feature is off by default.

Whit this option set to False you need to call conn.commit to commit any pending transaction to the database.

Enable auto-commit

You can enable the auto-commit as follow:

import psycopg2

connection = None

try:
    connection = psycopg2.connect("dbname='myDB' user='myUser' host='localhost' password='myPassword'")
    connection.autocommit = True
except:
    print "Connection failed."

if(connection != None):
    cursor = connection.cursor()

    try:
        cursor.execute("""CREATE TABLE tbl AS (SELECT (row_number() over())::integer 'id', 'col' FROM tbl2)""")
    except:
        print("Failed to create table.")

`with` statement

You can also use the with statement to auto-commit a transaction:

with connection, connection.cursor() as cursor:  # start a transaction and create a cursor
    cursor.execute("""CREATE TABLE tbl AS (SELECT (row_number() over())::integer 'id', 'col' FROM tbl2)""")

Traditional way

If you don't want to auto-commit the transaction you need to do it manually calling .commit() after your execute.

I don't see how this answer is related to the question asked. The problem persists for SQL editors also - with or without transactions. (see comments) — Meir Tseitlin, Sep 08 '20 at 14:27

score -1 · Answer 3 · answered Aug 06 '20 at 19:41

-1

just remove the ( ) around the SELECT...

https://www.postgresql.org/docs/11/sql-createtableas.html

answered Aug 06 '20 at 19:41

Игорь Тыра

905
4
12

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

3 Answers3

.commit() solution

Enable auto-commit

with statement

Traditional way

`.commit()` solution

`with` statement