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