Re: adding foreign key constraint locks up table

От: Tom Lane
Тема: Re: adding foreign key constraint locks up table
Дата: ,
Msg-id: 9848.1294154489@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: adding foreign key constraint locks up table  (Florian Weimer)
Список: pgsql-performance

Скрыть дерево обсуждения

adding foreign key constraint locks up table  (kakarukeys, )
 Re: adding foreign key constraint locks up table  (Gurjeet Singh, )
  Re: adding foreign key constraint locks up table  ("Kevin Grittner", )
   Re: adding foreign key constraint locks up table  (Gurjeet Singh, )
    Re: adding foreign key constraint locks up table  ("Kevin Grittner", )
 Re: adding foreign key constraint locks up table  (Lew, )
 Re: adding foreign key constraint locks up table  (kakarukeys, )
  Re: adding foreign key constraint locks up table  (Gurjeet Singh, )
 Re: adding foreign key constraint locks up table  (Florian Weimer, )
  Re: adding foreign key constraint locks up table  (Tom Lane, )
   Re: adding foreign key constraint locks up table  (Florian Weimer, )
    Re: adding foreign key constraint locks up table  (Tom Lane, )
 Re: adding foreign key constraint locks up table  (kakarukeys, )
  Re: adding foreign key constraint locks up table  (Robert Haas, )
 Re: adding foreign key constraint locks up table  (kakarukeys, )

Florian Weimer <> 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


В списке pgsql-performance по дате сообщения:

От: "Strange, John W"
Дата:
Сообщение: Re: Question: BlockSize > 8192 with FusionIO
От: Craig Ringer
Дата:
Сообщение: Re: Same stament sometime fast, something slow