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

Re: question: foreign key constraints and AccessExclusive locks

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: question: foreign key constraints and AccessExclusive locks
Date: 2013-01-06 10:14:18
Message-ID: CA+U5nM+Bu8Js=dZVucG3L2KzQKnCY31sE6KAXd9_=AHy5Vg3JQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


In response to

Responses

pgsql-hackers by date

Next:From: Tomas VondraDate: 2013-01-06 12:18:29
Subject: Re: too much pgbench init output
Previous:From: Tatsuo IshiiDate: 2013-01-06 09:35:47
Subject: Re: too much pgbench init output

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