From: Tom Lane <tgl@sss.pgh.pa.us>
To: Florian Weimer <fweimer@bfk.de>
Cc: kakarukeys <kakarukeys@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: adding foreign key constraint locks up table
Date: Tue, 04 Jan 2011 10:21:29 -0500
Message-ID: <9848.1294154489@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, fweimer@bfk.de, kakarukeys@gmail.com
Subject: Re: adding foreign key constraint locks up table
In-Reply-To: <9848.1294154489@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox