Skip site navigation (1) Skip section navigation (2)

Re: question: foreign key constraints and AccessExclusive locks

From: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
To:
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: question: foreign key constraints and AccessExclusive locks
Date: 2013-01-07 02:22:31
Message-ID: CAKuK5J1STudDTewSZXW5LqtFEcTu2nkUhNtA=cd8gxYiqmvYkg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sun, Jan 6, 2013 at 4:14 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 6 January 2013 03:08, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
>> When adding a foreign key constraint on tableA which references
>> tableB, why is an AccessExclusive lock on tableB necessary? Wouldn't a
>> lock that prevents writes be sufficient, or does PostgreSQL have to
>> modify *both* tables in some fashion? I'm using PostgreSQL 8.4 on
>> Linux.
>
> FKs are enforced by triggers currently. Adding triggers requires
> AccessExclusiveLock because of catalog visibility issues; you are
> right that a lower lock is eventually possible.
>
> SQLStandard requires the check to be symmetrical, so adding FKs
> requires a trigger on each table and so an AEL is placed on tableB.

I've read and re-read this a few times, and I think I understand.
However, could you clarify "you are right that a lower lock is
eventually possible" for me, please?

-- 
Jon


In response to

Responses

pgsql-hackers by date

Next:From: Tatsuo IshiiDate: 2013-01-07 02:29:42
Subject: Re: too much pgbench init output
Previous:From: Fabrízio de Royes MelloDate: 2013-01-07 00:54:48
Subject: Re: Re: Proposal: Store "timestamptz" of database creation on "pg_database"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group