From: Bath, David <dave.bath@unix.net>
To: pgsql-sql@postgresql.org
Subject: Disabling triggers/constraints pg<8.1
Date: Mon, 27 Feb 2006 13:44:31 +1100
Message-ID: <200602271344.32530.dave.bath@unix.net> (raw)
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/ENABLE CONSTRAINT statements at some stage.
Great!! Toggling things on/off is MUCH better than dropping/recreating them.
However, I note that pgdump from earlier pg releases includes code that
does coarse-grained disabling of triggers (all on a 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/deletes if there
are other triggers active against that table? (I've had problems
trying to drop tables when 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 involving indices 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dave.bath@unix.net
Subject: Re: Disabling triggers/constraints pg<8.1
In-Reply-To: <200602271344.32530.dave.bath@unix.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox