Thread: disable/enable trigger hangs

Hi, I'm using PostgreSQL 8.1.4 and am having some problems with the new
disable/enable trigger command.

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?

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?


Mike

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:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/