truncate table getting blocked

Jayadevan M wrote:

I have a python script. It opens a cursor, and sets the search_path (using psycopg2). In case
something goes wrong in the script , a record is inserted into a table. In that script, I am not doing
any thing else other than reading a file and publishing the lines to a queue (no database operations).
The database is used just to track the errors. But my set search_path is locking a truncate table I am
executing from a psql session. Is this expected?

When the truncate table hung, I used this query
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid

WHERE NOT blocked_locks.GRANTED;

and got this (schemaname/user/tablename modified)-

blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement |
current_statement_in_blocking_process
-------------+--------------+--------------+---------------+----------------------------------+-------
--------------------------------
9223 | myuser | 12861 | myuser | truncate table myschema.table1; | SET
search_path TO myschema,public

PG version :

PostgreSQL 9.4.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5, 64-bit

It is not the "SET search_path" statement that is blocking the truncate,
but probably some earlier statement issued in the same transaction.

Take a look at pg_locks to find out what lock the transaction is holding on myschema.table1.

Use statement logging to find out which statement causes the lock.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Apr 26, 2016 at 7:25 PM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:

It is not the "SET search_path" statement that is blocking the truncate,
but probably some earlier statement issued in the same transaction.

You are right. I had a select against that table.
Adding this line fixed it ...
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

Reference -
http://initd.org/psycopg/docs/faq.html

Thanks,
Jayadevan

From: Jayadevan M Sent: Tuesday, April 26, 2016 6:32 AM

Hello,

I have a python script. It opens a cursor…

Thanks,

Jayadevan