[GENERAL] Why do we need an AccessExclusiveLock to validate a FK constraint marked as NOT VALID?
Hi, currently, ALTER TABLE VALIDATE CONSTRAINT for foreign key constraints acquires an AccessExclusiveLock on the referencing table. Why? If the constraint is in place but not validated (ADD CONSTRAINT ... NOT VALID) it already prevents new modifications from violating the constraint. The code that is called to validate the constraint, RI_Initial_Check, contains this comment: * We expect that the caller has made provision to prevent any problems * caused by concurrent actions. This could be either by locking rel and * pkrel at ShareRowExclusiveLock or higher, or by otherwise ensuring * that triggers implementing the checks are already active. * Hence, we do not need to lock individual rows for the check. Doesn't the presence of the NOT VALID constraint qualify as "otherwise ensuring that triggers implementing the checks are already active"? Is there any deeper reason? Or is it simply not implemented yet? Thanks, Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why do we need an AccessExclusiveLock to validate a FK constraint marked as NOT VALID?
On 04/13/2014 12:58 PM, Torsten Förtsch wrote: > Hi, > > currently, ALTER TABLE VALIDATE CONSTRAINT for foreign key constraints > acquires an AccessExclusiveLock on the referencing table. > > Why? > > If the constraint is in place but not validated (ADD CONSTRAINT ... NOT > VALID) it already prevents new modifications from violating the constraint. > > The code that is called to validate the constraint, RI_Initial_Check, > contains this comment: > > * We expect that the caller has made provision to prevent any problems > * caused by concurrent actions. This could be either by locking rel and > * pkrel at ShareRowExclusiveLock or higher, or by otherwise ensuring > * that triggers implementing the checks are already active. > * Hence, we do not need to lock individual rows for the check. > > Doesn't the presence of the NOT VALID constraint qualify as "otherwise > ensuring that triggers implementing the checks are already active"? > > Is there any deeper reason? Or is it simply not implemented yet? > Actually, it is implemented yet. http://www.postgresql.org/message-id/e1wwovd-0004ts...@gemulon.postgresql.org It'll be in 9.4. -- Vik -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why do we need an AccessExclusiveLock to validate a FK constraint marked as NOT VALID?
On 13/04/14 13:34, Vik Fearing wrote: > Actually, it is implemented yet. > > http://www.postgresql.org/message-id/e1wwovd-0004ts...@gemulon.postgresql.org > > It'll be in 9.4. That's good news. So, I could validate a FK constraint this way: UPDATE pg_constraint SET convalidated = NOT EXISTS( SELECT 1 FROM ONLY fkrel a LEFT JOIN ONLY pkrel b ON (a.fkcol1=b.pkcol1 AND ...) -- all fk columns WHERE b.pkcol1 IS NULL -- inner join failed AND (a.fkcol1 IS NOT NULL OR/AND -- MATCH SIMPLE: AND; FULL: OR a.fkcol2 IS NOT NUL ...) ) WHERE contype='f' AND ... fkrel is confrelid::regclass and pkrel conrelid::regclass. That's essentially what AT VALIDATE CONSTRAINT does. Torsten -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general