Re: table locking on creating FK

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