Florian Weimer <fweimer@xxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
