pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcan you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
7+ messages / 5 participants
[nested] [flat]
* can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
@ 2005-03-30 09:07 frank@joerdens.de
2005-03-30 09:48 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE PFC <lists@boutiquenumerique.com>
0 siblings, 1 reply; 7+ messages in thread
From: frank@joerdens.de @ 2005-03-30 09:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
2005-03-30 09:07 can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
@ 2005-03-30 09:48 ` PFC <lists@boutiquenumerique.com>
2005-03-30 10:33 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
0 siblings, 1 reply; 7+ messages in thread
From: PFC @ 2005-03-30 09:48 UTC (permalink / raw)
To: frank@joerdens.de; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
2005-03-30 09:07 can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
2005-03-30 09:48 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE PFC <lists@boutiquenumerique.com>
@ 2005-03-30 10:33 ` frank@joerdens.de
2005-03-30 17:52 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 7+ messages in thread
From: frank@joerdens.de @ 2005-03-30 10:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
2005-03-30 09:07 can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
2005-03-30 09:48 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE PFC <lists@boutiquenumerique.com>
2005-03-30 10:33 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
@ 2005-03-30 17:52 ` Bruno Wolff III <bruno@wolff.to>
2005-03-30 17:52 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Bruno Wolff III @ 2005-03-30 17:52 UTC (permalink / raw)
To: frank@joerdens.de; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
2005-03-30 09:07 can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
2005-03-30 09:48 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE PFC <lists@boutiquenumerique.com>
2005-03-30 10:33 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
2005-03-30 17:52 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Bruno Wolff III <bruno@wolff.to>
@ 2005-03-30 17:52 ` Michael Fuhr <mike@fuhr.org>
2005-03-30 19:02 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-03-30 17:52 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; frank@joerdens.de; pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
2005-03-30 09:07 can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
2005-03-30 09:48 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE PFC <lists@boutiquenumerique.com>
2005-03-30 10:33 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
2005-03-30 17:52 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Bruno Wolff III <bruno@wolff.to>
2005-03-30 17:52 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Michael Fuhr <mike@fuhr.org>
@ 2005-03-30 19:02 ` Bruno Wolff III <bruno@wolff.to>
2005-03-30 22:51 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Bruno Wolff III @ 2005-03-30 19:02 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: frank@joerdens.de; pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
2005-03-30 09:07 can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
2005-03-30 09:48 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE PFC <lists@boutiquenumerique.com>
2005-03-30 10:33 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
2005-03-30 17:52 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Bruno Wolff III <bruno@wolff.to>
2005-03-30 17:52 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Michael Fuhr <mike@fuhr.org>
2005-03-30 19:02 ` Re: can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE Bruno Wolff III <bruno@wolff.to>
@ 2005-03-30 22:51 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 7+ messages in thread
From: Greg Stark @ 2005-03-30 22:51 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Michael Fuhr <mike@fuhr.org>; frank@joerdens.de; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-03-30 22:51 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-30 09:07 can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE frank@joerdens.de
2005-03-30 09:48 ` PFC <lists@boutiquenumerique.com>
2005-03-30 10:33 ` frank@joerdens.de
2005-03-30 17:52 ` Bruno Wolff III <bruno@wolff.to>
2005-03-30 17:52 ` Michael Fuhr <mike@fuhr.org>
2005-03-30 19:02 ` Bruno Wolff III <bruno@wolff.to>
2005-03-30 22:51 ` Greg Stark <gsstark@mit.edu>
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