disable/enable trigger hangs

Mike Charnoky <noky@nextbus.com> writes:

First, a question: For a PG8.1 database, is it preferable to use the new
"alter table disable|enable trigger" command as opposed to the old
method of setting pg_class.reltriggers = 0?

Very much so --- manual manipulation of reltriggers has never been
anything but a dangerous kluge.

I'm assuming the "alter table" approach is preferred, so I converted
some scripts to use the new method. However, sometimes the
enable/disable trigger command hangs when operating on certain tables.
I use the syntax "ALTER TABLE mytable DISABLE TRIGGER ALL;". Any hints
on how to debug this?

Look in pg_locks to see who's got a lock on the table. One of the
reasons the pg_class update is a kluge is exactly that it ignores
locking considerations ...

regards, tom lane

Thanks for the quick reply Tom! The pg_locks table helped me to get to
the bottom of this.

For future reference to others, here is a good way to view the pg_locks
table for a particular database, adding table name annotation:

SELECT pg_locks.*, pg_class.relname from pg_locks, pg_class
WHERE pg_locks.relation=pg_class.oid and pg_locks.database=
(SELECT datid from pg_stat_database where datname = 'my_db_name');

Mike

Tom Lane wrote: