Thread: Performance on DISABLE TRIGGER
Hi all I'm doing some maintenance - which is done quite often, never had this problem before - which requires me to disable triggers, run some updates and then re-enable the triggers. Where the whole process normally take 30 sec , it took much longer today and I cancelled after 5 minutes. After running the statements individually to pinpoint the issue , I identified that the problem is on the first line. >> ALTER TABLE tab DISABLE TRIGGER trig; I have not been able to run this successfully -- after 10 minutes the connection ( to remote DB ) got severed . Any ideas on where I can start to look for the cause of the problem ? Thanks -- View this message in context: http://postgresql.nabble.com/Performance-on-DISABLE-TRIGGER-tp5839727.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I'm doing some maintenance - which is done quite often, never had this > problem before - which requires me to disable triggers, run some updates and > then re-enable the triggers. Disabling triggers requires a heavy lock. A better way is to use the session_replication_role feature. See: http://blog.endpoint.com/2015/01/postgres-sessionreplication-role.html - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201502271149 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlTwoDMACgkQvJuQZxSWSshyzwCfQulu6DCOBu28gvoY++evftuo xAAAn01YlcLj+TvkCsur10riMUD1y5uY =UR3z -----END PGP SIGNATURE-----
On 2/27/15 9:07 AM, gmb wrote: > Hi all > > I'm doing some maintenance - which is done quite often, never had this > problem before - which requires me to disable triggers, run some updates and > then re-enable the triggers. > Where the whole process normally take 30 sec , it took much longer today and > I cancelled after 5 minutes. > > After running the statements individually to pinpoint the issue , I > identified that the problem is on the first line. > >> ALTER TABLE tab DISABLE TRIGGER trig; > I have not been able to run this successfully -- after 10 minutes the > connection ( to remote DB ) got severed . > Any ideas on where I can start to look for the cause of the problem ? 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 -- - David Steele david@pgmasters.net
Attachment
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.
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I'm doing some maintenance - which is done quite often, never had this > problem before - which requires me to disable triggers, run some updates and > then re-enable the triggers. Disabling triggers requires a heavy lock. A better way is to use the session_replication_role feature. See: http://blog.endpoint.com/2015/01/postgres-sessionreplication-role.html - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201502271149 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlTwoDMACgkQvJuQZxSWSshyzwCfQulu6DCOBu28gvoY++evftuo xAAAn01YlcLj+TvkCsur10riMUD1y5uY =UR3z -----END PGP SIGNATURE-----
Greg Sabino Mullane wrote > Disabling triggers requires a heavy lock. A better way is to use > the session_replication_role feature. See: > > http://blog.endpoint.com/2015/01/postgres-sessionreplication-role.html This is a very effective solution to my problem. Thanks for the tip, Greg. -- View this message in context: http://postgresql.nabble.com/Performance-on-DISABLE-TRIGGER-tp5839727p5840247.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.