Re: Locking referenced table when creating and dropping tables with foreign key constraints (SOLVED)

On Tue, Feb 2, 2010 at 12:19 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> frank joerdens <fiskadoro(at)gmail(dot)com> writes:
>> It seems that whenever I create a new empty table with a foreign key
>> constraint, the transaction will acquire an exclusive lock on the
>> referenced table, locking out other writers (not sure if even readers
>> as well), and I don't quite see why that is necessary
>
> It involves an ALTER TABLE ADD TRIGGER command, which necessarily locks
> out writers to avoid race conditions.  I think at the moment it may take
> an exclusive lock and thereby lock out readers as well.  There has been
> some talk of trying to reduce the lock strength needed for ALTER
> operations, but you should not expect that it'll ever be possible to
> do that without blocking writers.

Turns out that on the main db instance, where the problematic table is
mastered, the FK constraint creation goes through in a couple seconds
which is not long enough to take down the app.

The actual real world issue we had was due to a script which applied
the new tables and constraints across a set of replicated slave
databases as well as on the master, and the script was broken in that
it left the transaction open on the master database while applying the
constraints on the slaves, and the lock was held way longer than
actually needed that way.

So in spite of the fact that we manage to pretty much max out a
16-core/64GB/half-decent RAID box with our OLTP load (and the FK's in
question reference one of the most update-heavy tables), this
basically still works.

Sorry about kicking up a fuss for mostly nothing.

Regards,

Frank