Hi everyone,
We are in the process of upgrading from PostgreSQL 9.6 to 13. When our
database gets created in our regression tests, we run some unit tests
first. We see one of those tests hang.
It seems the client is waiting on more data to arrive from the
PostgreSQL server. A thread dump shows it waiting on the socket.
On the server, I see the connection from the client. Looking at
pg_stat_activity, I see it is in state 'active'. I have seen query_start
be as old as eight hours ago. The documentation states that 'active'
means a query is being executed. The query in question is:
Looking at pg_locks, I only see locks with granted = true for the PID.
Is this reproducible, I mean this happens multiple times?
can you please run the below query in a separate session and share the result, feel free to anonymize sensitive stuff.
SELECT db.datname, locktype, relation::regclass, mode, transactionid
AS tid, virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l
LEFT JOIN pg_catalog.pg_database db ON db.oid = l.database
WHERE NOT pid = pg_backend_pid();
There is nothing in the logs as far as I can see. Configuration
variables log_min_messages and log_min_error_statement are on 'debug1'.
This is a snippet of the logs:
2021-07-09 20:35:16.374 CEST [30399] STATEMENT: START TRANSACTION
2021-07-09 20:35:18.703 CEST [30399] WARNING: there is already a
transaction in progress
Are you having multiple BEGIN tx not matching commit/rollbacks ? not sure related, but fixing this will help reduce noise.
I am a bit out of ideas - does anyone have any tips where I should look
to see what is causing the query to hang?
Although I am not an expert at this, I am exploring myself.
Can you collect backtrace for this pid?
You can try enabling core dumps, then run a core dump using `gcore` on the hanging pid (if you do not want to attach to a live process)
and generate a backtrace on that pid using `gdb` and share the trace.
I think that might help.