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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
