|
Home > Archive > PostgreSQL SQL > March 2005 > can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE
|
|
| frank@joerdens.de 2005-03-30, 9:42 am |
| 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/archiv...4-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
| |
|
|
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/archiv...4-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
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| |
| frank@joerdens.de 2005-03-30, 9:42 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
| |
| Bruno Wolff III 2005-03-30, 7:05 pm |
| 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:
>
> 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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)
| |
| Michael Fuhr 2005-03-30, 7:05 pm |
| 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/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
| |
| Bruno Wolff III 2005-03-30, 7:05 pm |
| 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:
>
> 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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Greg Stark 2005-03-30, 7:05 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
|
|
|
|
|