Re: table locking on creating FK

On 05/07/2014 06:54 PM, Stephen Frost wrote:
> Mark,
>
> * M. D. (lists@turnkey.bz) wrote:
>> Why does a table lock up if I want to create a FK to it?  e.g.  I
>> have a separate schema for my own mods to the database but if I want
>> to reference anything in the public schema on a customer table, that
>> table will be locked up.
> That's correct, creating a foreign key to a table requires an
> AccessExclusiveLock on the referred-to table.
>
>> Why does a table lockup when disabling a trigger on it?
> For both of these, the issue is that we have to make sure every backend
> has the same view of the table and all triggers, etc, which exist on the
> table.  There is ongoing work to reduce lock levels where possible, now
> that PG accesses the catalogs using MVCC semantics (which was not true
> previously), but I wouldn't get your hopes up on these changing.
>
>> I just tried this on a live database, and ended up restarting the
>> postgres service because the whole table was locked and no users
>> were able to do anything.
> You would need to simply kill the transaction which held the locks,
> using pg_terminate_backend().
>
>> I guess I'm dumb (or stupid) to try it in production, but I wanted
>> to create an index on an audit table, so I knew enough that I would
>> have to disable the audit trigger before I could create an index on
>> a 1.8 million row table.  Then the main gltx table locked up on
>> disabling the trigger.  I found the pid of the process by doing
>> this:
> You might want to investigate the 'CONCURRENTLY' option of CREATE INDEX.
>
>> select * from pg_stat_activity where query ilike '%trigger%';
>>
>> Then tried to cancel the query using this:
>>
>> select pg_cancel_backend(17069);
> pg_cancel_backend() will cancel a running *query* but it does not
> terminate the transaction.  Locks are held until the end of a
> transaction.  You likely wanted 'pg_terminate_backend()', as mentioned
> above, which would have both rolled back the transaction and termianted
> the database connection.
>
>> But that did not happen within 1 min, and with 90 sales people all
>> waiting on this server, I did a kill -9 on that pid to get everyone
>> back as soon as possible.  This caused a bunch of "terminating
>> connection because of crash of another server process" errors in
>> pg_log, but I don't see anything serious after that.
> Doing a -9 against a PG server is a very bad idea- don't do it.  Use
> pg_terminate_backend().
>
>> Is there any way to recover from a locked situation like this?
> In general, I'd suggest you avoid trying to do DDL without a proper
> outage window or at least only during non-peak times and only once you
> have a good understanding of what locks will be taken out, and for how
> long, during your DDL work.
>
> Note also that the way locking is done in PG, once someone wants a
> higher lock on a table, everyone else wanting locks on the table have to
> wait (even if the table is only currently locked at the lower level).
> This avoids the higher-level lock process being stalled forever but does
> mean those locks have a high impact on the running system.
>
>     Thanks,
>
>         Stephen
Thank you ---  I'm very grateful for such a clear description and help.
The FK creation was something I had tried before and knew not to do
again, but I did not think that disabling the trigger would do the same.

Thanks again.   I'll tag this for myself as reference.