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
