Re: Fwd: question on foreign key lock

Lists: pgsql-generalpgsql-hackers
From: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
To: pgsql-general list <pgsql-general(at)postgresql(dot)org>
Subject: question on foreign key lock
Date: 2012-11-01 16:33:22
Message-ID: 5092A452.3070602@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general pgsql-hackers

Hello.

Why adding FK creates AccessExclusiveLock on referenced tabble?

{{{
CREATE TABLE A ( id integer, idb integer );
INSERT INTO A (id,idb) SELECT x, x%4 FROM generate_series(1,100) x;

CREATE TABLE B ( id int primary key );
INSERT INTO B VALUES (0),(1),(2),(3);

BEGIN;
ALTER TABLE A ADD CONSTRAINT a_idb_fkey FOREIGN KEY (idb) REFERENCES b;
SELECT * FROM pg_locks l, pg_class c WHERE l.pid = pg_backend_pid() AND
l.locktype='relation' AND l.mode ilike '%exclusive%' AND l.relation=c.oid;
ROLLBACK;
}}}

Last SELECT is showing AccessExclusive on B.
Why not Exclusive?

Thanks,
Filip


From: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Fwd: question on foreign key lock
Date: 2012-11-08 08:45:58
Message-ID: CAP_rwwmCVbaKAydiYTykpV6axt0LWFCt9pDf8YC+wTBHSwEXng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general pgsql-hackers

Hi,

maybe this is a better group for this question?

I can't see why creating foreign key on table A referencing table B,
generates an AccessExclusiveLock on B.
It seems (to a layman :-) ) that only writes to B should be blocked.

I'm really interested if this is either expected effect or any open TODO
item or suboptimal behavior of postgres.

Thanks

---------- Forwarded message ----------
From: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
Date: Thu, Nov 1, 2012 at 5:33 PM
Subject: question on foreign key lock
To: pgsql-general list <pgsql-general(at)postgresql(dot)org>

Hello.

Why adding FK creates AccessExclusiveLock on referenced tabble?

{{{
CREATE TABLE A ( id integer, idb integer );
INSERT INTO A (id,idb) SELECT x, x%4 FROM generate_series(1,100) x;

CREATE TABLE B ( id int primary key );
INSERT INTO B VALUES (0),(1),(2),(3);

BEGIN;
ALTER TABLE A ADD CONSTRAINT a_idb_fkey FOREIGN KEY (idb) REFERENCES b;
SELECT * FROM pg_locks l, pg_class c WHERE l.pid = pg_backend_pid() AND
l.locktype='relation' AND l.mode ilike '%exclusive%' AND l.relation=c.oid;
ROLLBACK;
}}}

Last SELECT is showing AccessExclusive on B.
Why not Exclusive?

Thanks,
Filip


From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Fwd: question on foreign key lock
Date: 2012-11-12 16:20:28
Message-ID: CA+TgmoYp_UfVj392xkpeWCDiv4Ry0Jt30mgcNjSjtRABtCdK-A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general pgsql-hackers

On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski
<filip(dot)rembialkowski(at)gmail(dot)com> wrote:
> maybe this is a better group for this question?
>
> I can't see why creating foreign key on table A referencing table B,
> generates an AccessExclusiveLock on B.
> It seems (to a layman :-) ) that only writes to B should be blocked.
>
> I'm really interested if this is either expected effect or any open TODO
> item or suboptimal behavior of postgres.

This comment explains it:

/*
* Grab an exclusive lock on the pk table, so that someone doesn't delete
* rows out from under us. (Although a lesser lock would do for that
* purpose, we'll need exclusive lock anyway to add triggers to the pk
* table; trying to start with a lesser lock will just create a risk of
* deadlock.)
*/
pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock);

Concurrent DDL is something that's been discussed in detail on this
list in the past; unfortunately, there are some tricky race conditions
are the shared invalidation queue and SnapshotNow that make it hard to
implement properly. I'm hoping to have some time to work on this at
some point, but it hasn't happened yet.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


From: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Fwd: question on foreign key lock
Date: 2012-12-05 12:08:18
Message-ID: CAP_rwwnpem7Bd4i9zLFSn+NnAf9jr4ezzSuSEmzYZksrA3QQLQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general pgsql-hackers

Robert, thank you for the answer.

1. "need exclusive lock anyway to add triggers".
Why adding a trigger needs exclusive lock?
Someone would say blocking reads is not needed (since possible trigger
events are: Insert/Update/Delete/Truncate).

2. "will create a risk of deadlock".
From user perspective a risk of deadlock is sometimes better than
excessive locking. Transactional DDL users should be prepared for
exceptions/retries anyway.

3. I made a naive test of simply changing AccessExclusiveLock to
ExclusiveLock, and seeing how many regression tests it breaks. It
breaks none :-)
Current Git head gives me 2 fails/133 tests regardless of this change.

regards,
Filip

On Mon, Nov 12, 2012 at 5:20 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Nov 8, 2012 at 3:45 AM, Filip Rembiałkowski
> <filip(dot)rembialkowski(at)gmail(dot)com> wrote:
>> maybe this is a better group for this question?
>>
>> I can't see why creating foreign key on table A referencing table B,
>> generates an AccessExclusiveLock on B.
>> It seems (to a layman :-) ) that only writes to B should be blocked.
>>
>> I'm really interested if this is either expected effect or any open TODO
>> item or suboptimal behavior of postgres.
>
> This comment explains it:
>
> /*
> * Grab an exclusive lock on the pk table, so that someone doesn't delete
> * rows out from under us. (Although a lesser lock would do for that
> * purpose, we'll need exclusive lock anyway to add triggers to the pk
> * table; trying to start with a lesser lock will just create a risk of
> * deadlock.)
> */
> pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock);
>
> Concurrent DDL is something that's been discussed in detail on this
> list in the past; unfortunately, there are some tricky race conditions
> are the shared invalidation queue and SnapshotNow that make it hard to
> implement properly. I'm hoping to have some time to work on this at
> some point, but it hasn't happened yet.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company


From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>
Cc: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Fwd: question on foreign key lock
Date: 2012-12-05 22:05:41
Message-ID: CA+TgmoakoMXWXMBVx4xukVWDEt_MJvkhb+De_kNt3AvwEeo2Dw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general pgsql-hackers

On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiałkowski
<filip(dot)rembialkowski(at)gmail(dot)com> wrote:
> Robert, thank you for the answer.
>
> 1. "need exclusive lock anyway to add triggers".
> Why adding a trigger needs exclusive lock?
> Someone would say blocking reads is not needed (since possible trigger
> events are: Insert/Update/Delete/Truncate).
>
> 2. "will create a risk of deadlock".
> From user perspective a risk of deadlock is sometimes better than
> excessive locking. Transactional DDL users should be prepared for
> exceptions/retries anyway.
>
> 3. I made a naive test of simply changing AccessExclusiveLock to
> ExclusiveLock, and seeing how many regression tests it breaks. It
> breaks none :-)
> Current Git head gives me 2 fails/133 tests regardless of this change.

Sure. You could probably downgrade it quite a bit further without
breaking the regression tests, but that doesn't mean it's safe in all
cases. Rather than having this discussion all over again, I suggest
that you have a look at commits
2dbbda02e7e688311e161a912a0ce00cde9bb6fc,
2c3d9db56d5d49bdc777b174982251c01348e3d8,
a195e3c34f1eeb6a607c342121edf48e49067ea9, and the various mailing list
discussions pertaining thereto, particularly the thread "ALTER TABLE
lock strength reduction patch is unsafe" which was started by Tom
Lane.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Fwd: question on foreign key lock
Date: 2012-12-05 22:19:01
Message-ID: 20121205221900.GW27424@awork2.anarazel.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general pgsql-hackers

On 2012-12-05 17:05:41 -0500, Robert Haas wrote:
> On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiałkowski
> <filip(dot)rembialkowski(at)gmail(dot)com> wrote:
> > Robert, thank you for the answer.
> >
> > 1. "need exclusive lock anyway to add triggers".
> > Why adding a trigger needs exclusive lock?
> > Someone would say blocking reads is not needed (since possible trigger
> > events are: Insert/Update/Delete/Truncate).
> >
> > 2. "will create a risk of deadlock".
> > From user perspective a risk of deadlock is sometimes better than
> > excessive locking. Transactional DDL users should be prepared for
> > exceptions/retries anyway.
> >
> > 3. I made a naive test of simply changing AccessExclusiveLock to
> > ExclusiveLock, and seeing how many regression tests it breaks. It
> > breaks none :-)
> > Current Git head gives me 2 fails/133 tests regardless of this change.
>
> Sure. You could probably downgrade it quite a bit further without
> breaking the regression tests, but that doesn't mean it's safe in all
> cases. Rather than having this discussion all over again, I suggest
> that you have a look at commits
> 2dbbda02e7e688311e161a912a0ce00cde9bb6fc,
> 2c3d9db56d5d49bdc777b174982251c01348e3d8,
> a195e3c34f1eeb6a607c342121edf48e49067ea9, and the various mailing list
> discussions pertaining thereto, particularly the thread "ALTER TABLE
> lock strength reduction patch is unsafe" which was started by Tom
> Lane.

Just to give an example about the complexities surrounding this:
Lowering the lock level for foreign key creation probably would be
dangerous for query planning more precisely join removal.

S1: BEGIN TRANSACTION ISOLATION LEVEL REPATABLE READ;
S1: SELECT * FROM a;

S2: DELETE FROM a WHERE a.id IN (all_duplicate_ids);
S2: ALTER TABLE a ADD CONTSTRAINT a_unique UNIQUE (a);
S2: ALTER TABLE b ADD CONSTRAINT b_fkey FOREIGN KEY (b_id) REFERENCES a(id));

S1: SELECT b.id FROM a LEFT JOIN b ON(b.id = a.id);

The last S1 query might now remove the join to b because of the foreign
key (which it sees due to SnapshotNow semantics) although rows that
violate unique key (which is required for the foreign key) still
exist. The existance of those duplicate values would change the result
though!

(come to think of it, I think we might still hit the above case if S1
doesn't access a before the foreign key gets altered...)

Greetings,

Andres Freund

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


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Filip Rembiałkowski <filip(dot)rembialkowski(at)gmail(dot)com>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Fwd: question on foreign key lock
Date: 2012-12-06 00:13:40
Message-ID: 18461.1354752820@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general pgsql-hackers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Dec 5, 2012 at 7:08 AM, Filip Rembiakowski
> <filip(dot)rembialkowski(at)gmail(dot)com> wrote:
>> 3. I made a naive test of simply changing AccessExclusiveLock to
>> ExclusiveLock, and seeing how many regression tests it breaks. It
>> breaks none :-)

> Sure. You could probably downgrade it quite a bit further without
> breaking the regression tests, but that doesn't mean it's safe in all
> cases.

In fact, since the regression tests make no attempt whatsoever to stress
DDL executed concurrently with table accesses, it doesn't prove a darn
thing. The standard regression tests actually try quite hard to avoid
such scenarios, so that the results will be repeatable. You could
perhaps build relevant test cases using the isolationtester
infrastructure, but I don't think anyone has tried particularly.

regards, tom lane