Simple queries run for hours, but only for one table, what can be the cause?
I tried deleting it, obviously it got stalled too, and I can't do any other basic or complex queries for that table. I am at digitalocean, so I can't just do a restart on the DB. I imported this table from a URL with python and I forgot to close the psycopg2 connection before running the script so I had to terminate it, and maybe that could have caused the problem.
BTW. this is my personal database, I don't have other admins who could have locked me out or anything. I also looked at the logs, but there isn't any sign of unusual activities. Here is it though, originally its in German, I had to translate it.
09:18:35.644: PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit launches
09:18:35.645: expected connections on IPv6 address "::", port 25060
09:18:35.645: expecting connections on IPv4 address "0.0.0.0", port 25060
09:18:35.691: database system was interrupted; last known action on 2022-06-16 23:54:18 CEST
09:18:36.137: database system was not shut down properly; automatic recovery in progress
09:18:36.140: Redo starts at 0/172B560
09:18:36.141: invalid record length at 0/172B598: 24 expected, 0 received
09:18:36.141: redo complete at 0/172B560 System usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
09:18:36.164: database system is ready to accept connections
20:34:07.203: fast shutdown requested
20:34:07.208: any active transactions are aborted
20:34:07.232: background worker "logical replication launcher" (PID 15088) terminated with status 1
20:34:07.241: shut down
20:34:07.292: database system shut down
You probably have an orphaned idle process that has acquired a lock on the table which has not been released due to an uncommitted transaction -and will never be released since you have no ability to commit it.
If simple queries like SELECT * FROM table are blocking, you most likely have an Access Exclusive Lock on the table from something like an ALTER/DROP statement that had run in the script that you cancelled.
You can verify this by running:
SELECT
psa.pid,
psa.query,
psa.state,
pg_locks.mode lock_mode,
relation::regclass locked_relation
FROM
pg_locks
JOIN pg_stat_activity psa on pg_locks.pid = psa.pid
WHERE
granted
and 'my_schema.my_table'::regclass = relation
Then you can kill any process that has a lock on your table:
SELECT
pg_terminate_backend(psa.pid),
FROM
pg_locks
JOIN pg_stat_activity psa on pg_locks.pid = psa.pid
WHERE
granted
and 'my_schema.my_table'::regclass = relation
If you still have blocked queries, you can open a new connection while the blocked query is running, and execute the following:
SELECT
activity.pid pid,
activity.state state,
activity.query blocked_query,
blocking_pid,
blocking_activity.state blocking_state,
blocking_activity.query blocking_query,
locks.locked_relations blocking_relation_locks
FROM
pg_stat_activity activity
JOIN LATERAL unnest(pg_blocking_pids(activity.pid)) blocking_pids(blocking_pid) ON TRUE
JOIN pg_stat_activity blocking_activity ON (blocking_pids.blocking_pid = blocking_activity.pid)
JOIN LATERAL (
SELECT
string_agg(locks.relation::regclass::text,',') locked_relations
FROM
pg_locks locks
WHERE
locks.pid = blocking_pid
GROUP BY
blocking_pid
) locks ON TRUE;
This will show you every process that is blocking another process. Once you find the offending processes you can exterminate them using pg_terminate_backend(pid)
If you want to kill every pid blocking another pid, you can run:
SELECT
pg_terminate_backend(unnest(pg_blocking_pids(pid)))
FROM
pg_stat_activity
If don't have other critical processes running and want to eliminate every single database connection for the sake of simplicity, do:
SELECT
pg_terminate_backend(pg_stat_activity.pid)
FROM
pg_stat_activity
WHERE
--delete this line if you want to kill all connections on the whole cluster
pg_stat_activity.datname = '<your database name>'
AND pid <> pg_backend_pid();
Once complete, you'll only have the connection on which you executed this query.
If none of the above steps work it's possible -as Laurenz Albe pointed out in the comments -that you may have a prepared transaction that is locking your table. The locks that a prepared transaction creates can survive backend terminations -and even server restarts. They must be explicitly rolled back or committed.
You can check if a prepared transaction is locking any of your tables with the following query
SELECT
ptx.transaction prep_tx_id,
ptx.gid prep_tx_name,
locks.mode lock_mode,
locks.relation::regclass locked_relation
FROM
pg_prepared_xacts ptx
JOIN pg_locks tx ON ptx.transaction = tx.transactionid and tx.locktype = 'transactionid'
JOIN pg_locks locks ON tx.virtualtransaction = locks.virtualtransaction
WHERE
locks.granted
and locks.relation = 'my_schema.my_table'::regclass
You can then rollback the prepared transaction like:
ROLLBACK PREPARED '<TRANSACTION_NAME>'