To: Florian Weimer <fweimer@bfk.de>
cc: kakarukeys <kakarukeys@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: adding foreign key constraint locks up table 
In-reply-to: <8239p8rd2f.fsf@mid.bfk.de> 
References: 
 <75218696-61be-4730-89f6-dd6058fa9eda@a28g2000prb.googlegroups.com>
	<82ei926j3p.fsf@mid.bfk.de> <19238.1293548900@sss.pgh.pa.us>
	<8239p8rd2f.fsf@mid.bfk.de>
Comments: In-reply-to Florian Weimer <fweimer@bfk.de>
	message dated "Tue, 04 Jan 2011 13:52:24 +0000"
Date: Tue, 04 Jan 2011 10:21:29 -0500
Message-ID: <9848.1294154489@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Florian Weimer <fweimer@bfk.de> writes:
> * Tom Lane:
>> ALTER ADD FOREIGN KEY must lock both tables to add triggers to them.

> But why is such a broad lock needed?  If the table was created in the
> current transaction and is empty, the contents of the foreign key
> table should not matter.

It's not about content, it's about having reproducible results.  We
cannot commit an ADD TRIGGER operation when there are table-modifying
queries already in progress, because they might (will) fail to notice
the trigger.  If you don't believe this is a problem, consider the
following sequence of events:

1. Session 1 issues "DELETE FROM pk WHERE true".  It fetches the table
definition, sees there are no triggers, and begins to execute the
DELETE.  Now it goes to sleep for awhile.

2. Session 2 issues ALTER TABLE fk ADD FOREIGN KEY pk.  If it doesn't
take a lock on pk that would exclude the concurrent DELETE, it can fall
through and commit before session 1 makes any more progress.

3. Session 2 inserts some rows in fk.  They are valid since the matching
rows in pk are valid (and not yet even marked for deletion).

4. Session 1 wakes up and finishes its DELETE.  Not knowing there is any
committed trigger on pk, it performs no FK checking.

Now you have rows in fk that violate the foreign key constraint.

			regards, tom lane