From: M. D. <lists@turnkey.bz>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: table locking on creating FK
Date: Wed, 07 May 2014 15:18:33 -0600
Message-ID: <536AA329.9030908@turnkey.bz> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi everyone,
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.
Why does a table lockup when disabling a trigger on it?
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.
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:
select * from pg_stat_activity where query ilike '%trigger%';
Then tried to cancel the query using this:
select pg_cancel_backend(17069);
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.
Is there any way to recover from a locked situation like this?
Thanks,
Mark
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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: lists@turnkey.bz
Subject: Re: table locking on creating FK
In-Reply-To: <536AA329.9030908@turnkey.bz>
* 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