Message-ID: <536AA329.9030908@turnkey.bz>
Date: Wed, 07 May 2014 15:18:33 -0600
From: "M. D." <lists@turnkey.bz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.5.0
MIME-Version: 1.0
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: table locking on creating FK
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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