Folks,
Questions about disabling/enabling triggers/constraints "through the back door"
in pg versions that do not support DISABLE|ENABLE of such things?
Background: pg 8.1 has the ability to DISABLE/ENABLE a particular trigger, and it looks like we'll be getting
DISABLE/ENABLECONSTRAINT statements at some stage. Great!! Toggling things on/off is MUCH better than
dropping/recreatingthem.
However, I note that pgdump from earlier pg releases includes code that does coarse-grained disabling of triggers (all
ona table, not just a nominated trigger): UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'"myschema"."mytable"'::pg_catalog.regclass;
UPDATE pg_catalog.pg_class SET reltriggers = ( SELECT pg_catalog.count(*) FROM pg_catalog.pg_trigger where
pg_class.oid= tgrelid ) WHERE oid = '"myschema"."mytable"'::pg_catalog.regclass;
Note: These are from dumps created using pgAdmin3
Questions: 1. Is it possible to disable only a single trigger using similar SQL, without confusing postgres during
inserts/updates/deletesif there are other triggers active against that table? (I've had problems trying to drop
tableswhen pg_class.reltrigger doesn't match the count in pg_trigger - such as when a restore from ASCII dumps are
interrupted halfway through a COPY).
2. Is there a similar SQL query that effectively disables constraints (even if this does not include those
involvingindices such as primary or unique constraints)?
3. How "evil" are such queries? Should they be avoided wherever possible? What other "gotchas" should I watch out
for?
Thanks in advance
--
David T. Bath
dave.bath@unix.net
