Thread: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
Hello. Is it possible to change an FK constraint from NOT DEFERRABLE (the default) to DEFERRABLE without dropping and re-creating it? One idea that came up was to create a parallel set of constraints which perform the same checks as the existing ones as DEFERRABLE (and then drop the old set), but the objection there was that it'd lock the tables during the initial check. We're having a fairly serious deadlock issue and the thinking goes that Tom's suggestion here http://www.webservertalk.com/archive139-2004-8-364172.html to defer FK checks until transaction commit would maybe help. Right now we can't try this because all the FK checks where created with default settings. We'd like to avoid taking the database down for recreating foreign keys. Regards, Frank
Checks the docs on ALTER TABLE ... ALTER CONSTRAINT ... On Wed, 30 Mar 2005 11:07:32 +0200, <frank@joerdens.de> wrote: > Hello. Is it possible to change an FK constraint from NOT DEFERRABLE > (the default) to DEFERRABLE without dropping and re-creating it? One idea > that came up was to create a parallel set of constraints which perform > the same checks as the existing ones as DEFERRABLE (and then drop the > old set), but the objection there was that it'd lock the tables during > the initial check. > > We're having a fairly serious deadlock issue and the thinking goes that > Tom's suggestion here > > http://www.webservertalk.com/archive139-2004-8-364172.html > > to defer FK checks until transaction commit would maybe help. Right now > we can't try this because all the FK checks where created with default > settings. We'd like to avoid taking the database down for recreating > foreign keys. > > Regards, > > Frank > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
On Wed, Mar 30, 2005 at 11:48:31AM +0200, PFC wrote: > > Checks the docs on ALTER TABLE ... ALTER CONSTRAINT ... ALTER CONSTRAINT? I did check for that, and it does not appear to exist?! That's why I asked ... Rgds, Frank
On Wed, Mar 30, 2005 at 12:33:11 +0200, frank@joerdens.de wrote: > On Wed, Mar 30, 2005 at 11:48:31AM +0200, PFC wrote: > > > > Checks the docs on ALTER TABLE ... ALTER CONSTRAINT ... > > ALTER CONSTRAINT? I did check for that, and it does not appear to > exist?! That's why I asked ... What version of Postgres are you running? I think ALTER CONSTRAINT is a relatively recent addition.
On Wed, Mar 30, 2005 at 11:52:32AM -0600, Bruno Wolff III wrote: > > What version of Postgres are you running? I think ALTER CONSTRAINT is a > relatively recent addition. Where are you seeing ALTER CONSTRAINT? I don't see it in gram.y even in HEAD. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Wed, Mar 30, 2005 at 10:52:42 -0700, Michael Fuhr <mike@fuhr.org> wrote: > On Wed, Mar 30, 2005 at 11:52:32AM -0600, Bruno Wolff III wrote: > > > > What version of Postgres are you running? I think ALTER CONSTRAINT is a > > relatively recent addition. > > Where are you seeing ALTER CONSTRAINT? I don't see it in gram.y > even in HEAD. I guess only in the previous messages in the thread. I remembered some recent additions to the ALTER TABLE command and incorrectly assumed that ALTER CONSTRAINT was one of those. It does look like you can only ADD and DROP constraints, not directly alter or replace them. So making a reference deferable is go to require a DROP and ADD which will need to recheck the constraint.
Bruno Wolff III <bruno@wolff.to> writes:
> It does look like you can only ADD and DROP constraints, not directly
> alter or replace them. So making a reference deferable is go to require
> a DROP and ADD which will need to recheck the constraint.
I asked the same question a few days ago on pgsql-general.
In short, if you want to skip the rechecking you have to update system tables
directly and you have to do two of them.
The updates you want would look something like these. But these would do *all*
your constraints, make sure to get only the ones you really want to change:
update pg_constraint set condeferrable = 't' where contype = 'f'
update pg_trigger set tgdeferrable=true where tgisconstraint = true
I think an ALTER CONSTRAINT to change these settings as well as the
ON {UPDATE,DELETE} behaviour would be neat.
--
greg