Re: table locking on creating FK - Mailing list pgsql-admin

Mark,
You can also read this article
http://momjian.us/main/writings/pgsql/locking.pdf, it article help me
a lot with understanding postgresql locking mechanism.

2014-05-08 4:54 GMT+04:00 Stephen Frost <sfrost@snowman.net>:
> 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



--
Best Regards,
Seliavka Evgenii