Performance on DISABLE TRIGGER

-----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

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

-----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-----

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

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.

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