From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
---|---|
To: | Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com> |
Cc: | Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED |
Date: | 2010-12-13 15:49:53 |
Message-ID: | 1292255393.2737.3159.camel@ebony |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, 2010-12-13 at 11:54 +0000, Peter Geoghegan wrote:
> On 13 December 2010 10:30, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> > Seriously, real-world use cases such as Kevin's one seems to warrant
> > that we are able to create a table withouth enforcing the FK. That's
> > horrid, yes, that's needed, too. Maybe some operations would have to be
> > instructed that the constraint ain't trustworthy but just declared to be
> > so by the user?
>
> Might I suggest that we call them "aspirational foreign keys", while
> sticking with Simon's syntax?
Just checking what we are saying:
1.
(a) ALTER TABLE ... ADD FOREIGN KEY ... NOT VALIDATED INITIALLY;
will add a FK but NOT run the check - we mark it as "check pending".
Lock held: ShareRowExclusiveLock
(b) Every new change to the table has the FK enforced - the triggers are
fully enabled and active. (That could mean we update a row and have the
update fail because of a FK violation.)
2. pg_validate_foreign_key('constraint name');
Returns immediately if FK is valid
Returns SETOF rows that violate the constraint, or if no rows are
returned it updates constraint to show it is now valid.
Lock held: AccessShareLock
Note that 1 & 2 together are the equivalent of ADD FK CONCURRENTLY,
except that step 2 more usefully tells you which rows fail.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-12-13 15:50:43 | Re: ALTER EXTENSION ... UPGRADE; |
Previous Message | Tom Lane | 2010-12-13 15:40:18 | Re: Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE |