Обсуждение: FOREIGN KEY ... CONCURRENTLY

Esteemed hackers,

I can't be the only person to have encountered a situation where
adding a new foreign key pointing at a busy table essentially never
happens because the way things work now, creating the constraint
trigger on that busy table requires an AccessExclusive lock, or a
unicorn, whichever you can acquire first.

So I'd like to propose, per a conversation with Andrew Gierth, that we
make an option to create foreign keys concurrently, which would mean
in essence that the referencing table would:
   1) need to be empty, at least in the first version, and
   2) needs to stay in a non-writeable state until all possible   conflicting transactions had ended.

Now, the less-fun part.  Per Andres Freund, the current support for
CONCURRENTLY in other operations is complex and poorly understood, and
there's no reason to believe this new CONCURRENTLY would be simpler or
easier to understand.

A couple of questions:
   1) Would people like to have FOREIGN KEY ... CONCURRENTLY as   described above?
   2) Is there another way to solve the problem of adding a foreign   key constraint that points at a busy table?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:

>     2) Is there another way to solve the problem of adding a foreign
>     key constraint that points at a busy table?

Add it as NOT VALID and do a later VALIDATE CONSTRAINT step, after the
patch to reduce lock levels for ALTER TABLE goes in, perhaps?

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services