Thread: Changing constraints to deferrable
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'; ? It doesn't seem to be enough. I still get constraint violations as soon as I try to delete a referenced column even after "set constraints all deferred". -- greg
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
Attachment
"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