From: Stephen Frost <sfrost@snowman.net>
To: M. D. <lists@turnkey.bz>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: table locking on creating FK
Date: Wed, 7 May 2014 20:54:17 -0400
Message-ID: <20140508005417.GG2556@tamriel.snowman.net> (raw)
In-Reply-To: <536AA329.9030908@turnkey.bz>
References: <536AA329.9030908@turnkey.bz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Attachments:
signature.asc (application/pgp-signature, 835B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sfrost@snowman.net, lists@turnkey.bz
Subject: Re: table locking on creating FK
In-Reply-To: <20140508005417.GG2556@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox