David Steele wrote
>
> ALTER TABLE requires an exclusive lock - my guess is that another
> process has a lock on the table. It could even be a select.
>
> pg_locks is your friend in this case:
> http://www.postgresql.org/docs/9.4/static/view-pg-locks.html
Hi David
I'm a bit confused on how to interpret the result of the pg_locks view.
After running the following (as per linked page).
SELECT pl.* , psa.query FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON
pl.pid = psa.pid;
I get a result of 2 locks on the "ALTER TABLE ..." statement:
Expanded display is on.
-[ RECORD 1 ]------+-----------------
locktype | virtualxid
database |
relation |
page |
tuple |
virtualxid | 5/57182
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 5/57182
pid | 6128
mode | ExclusiveLock
granted | t
fastpath | t
query | ALTER TABLE tab DISABLE TRIGGER trig;
-[ RECORD 2 ]------+----------------
locktype | relation
database | 16393
relation | 22595
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 5/57182
pid | 6128
mode | AccessExclusiveLock
granted | f
fastpath | f
query | ALTER TABLE tab DISABLE TRIGGER trig;
Something else I noticed while checking out the Server Status window in the
pgAdmin tool:
The stats_activity query run there displays a "blocked by" column , which
I'm assuming is retrieved using data from pg_locks .
I assume I'm seeing the pid of the process which is causing the block.
THe process however, is a query generated by the pgADmin tool itself:
SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype
FROM pg_type WHERE oid=1700
May this be the case of pg_catalog data being in need of maintenance ?
Regards
--
View this message in context: http://postgresql.nabble.com/Performance-on-DISABLE-TRIGGER-tp5839727p5840221.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
