Changing constraints to deferrable

Greg Stark <gsstark@mit.edu> writes:

Is it enough to just do
update pg_constraint set condeferrable = 't' where contype = 'f';

I think you'd need to start a fresh backend session --- the relcache
entries for the tables probably won't notice the above hack.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Greg Stark <gsstark@mit.edu> writes:

Is it enough to just do
update pg_constraint set condeferrable = 't' where contype = 'f';

I think you'd need to start a fresh backend session --- the relcache
entries for the tables probably won't notice the above hack.

Sorry, forgot to mention that I did that.

--
greg

Greg Stark wrote:

I want all my foreign key constraints to be deferrable. They were all created
with the default (not deferrable).

Is it enough to just do

update pg_constraint set condeferrable = 't' where contype = 'f';

No - the constraints are actually enforced by triggers - Just just
normally don't see those triggers - but if you look into pg_triggers,
you'll find them. The have "tgisconstraint" set to true, so it should
be easy to find them.

Try an additional "update pg_trigger set isdeferrable=true where
pgisconstraint = true", and it should work..

I'm not etirely sure about the fieldnames - so better check them - e.g
"\d pg_catalog.pg_trigger" could help, when typed into psql ;-)

mfg, Florian Pflug

"Florian G. Pflug" <fgp@phlo.org> writes:

Greg Stark wrote:

I want all my foreign key constraints to be deferrable. They were all created
with the default (not deferrable).
Is it enough to just do update pg_constraint set condeferrable = 't' where
contype = 'f';

Try an additional "update pg_trigger set isdeferrable=true where pgisconstraint
= true", and it should work..

Thanks. That works.

Consider this a plea for an ALTER TABLE ALTER CONSTRAINT command :)

--
greg

On Wed, Mar 23, 2005 at 12:13:33PM -0500, Greg Stark wrote:

Consider this a plea for an ALTER TABLE ALTER CONSTRAINT command :)

Shouldn't ALTER TABLE DROP CONSTRAINT followed by ALTER TABLE ADD
CONSTRAINT work? It does for me in simple tests. It's a little
more work than a single ALTER TABLE ALTER CONSTRAINT would be, but
it's less hackish than updating the system catalogs directly. Or
am I missing something?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> writes:

On Wed, Mar 23, 2005 at 12:13:33PM -0500, Greg Stark wrote:

Consider this a plea for an ALTER TABLE ALTER CONSTRAINT command :)

Shouldn't ALTER TABLE DROP CONSTRAINT followed by ALTER TABLE ADD
CONSTRAINT work? It does for me in simple tests. It's a little
more work than a single ALTER TABLE ALTER CONSTRAINT would be, but
it's less hackish than updating the system catalogs directly. Or
am I missing something?

But I want to do *all* constraints. If I tried to do that manually for
hundreds of constraints I'm certain to get at least some of them wrong.

It would also take a long time to readd all those constraints. And there's
really no reason to have to recheck a constraint to make it deferrable.
Similarly, there's no reason to have to recheck a constraint to change its
behaviour ON DELETE and ON UPDATE.

There could be some tricky bits around making a deferrable constraint not
deferrable. And disabling a constraint would be nice too, reenabling it would
require rechecking but at least it would eliminate the error-prone manual
process of reentering the definition.

--
greg

On Mar 24, 2005, at 12:42 AM, Greg Stark wrote:

There could be some tricky bits around making a deferrable constraint
not
deferrable. And disabling a constraint would be nice too, reenabling
it would
require rechecking but at least it would eliminate the error-prone
manual
process of reentering the definition.

there are some tricky bits. check the archives for either this list or
the performance list for what I did to mark my reference checks
deferrable. it was within the last few months (no more than 6).

Vivek Khera, Ph.D.
+1-301-869-4449 x806