How to drop a trigger in a resilient manner in postgresql

Question

I'm looking to drop a currently in production trigger because it's no longer needed, but the problem is that when I try the simplest way, which is something like

drop trigger <triggername> on <tablename>

It caused a huge table lock and everything froze!

What the trigger does is:

When a row is inserted or updated, check for a field's contents, split it and populate another table.

How should I proceed to instantly disable (and dropping afterwards) without causing hassle in our production environment?

Thanks in advance and sorry for my english ;)

Answer 1

You could try ALTER TABLE ... DISABLE TRIGGER - but it requires the same strength of lock, so I don't think it'll do you much good.

There's work in PostgreSQL 9.4 to make ALTER TABLE take weaker locks for some operations. It might help with this.

In the mean time, I'd CREATE OR REPLACE FUNCTION to replace the trigger with a simple no-op function.

Then, to actually drop the trigger, I'd probably write a script that does:

BEGIN;
LOCK TABLE the_table IN ACCESS EXCLUSIVE MODE NOWAIT;
DROP TRIGGER ...;
COMMIT;

If anybody's using the table the script will abort at the LOCK TABLE.

I'd then run it in a loop until it succeeded.

If that didn't work (if the table is always busy) but if most transactions were really short, I might attempt a LOCK TABLE without NOWAIT, but set a short statement_timeout. So the script would be something like:

BEGIN;
SET LOCAL statement_timeout = '5s';
LOCK TABLE the_table IN ACCESS EXCLUSIVE MODE NOWAIT;
DROP TRIGGER ...;
COMMIT;

That ensures a fairly short disruption by failing if it can't complete the job in time. Again, I'd run it periodically until it succeeded.

If neither approach was effective - say, due to lots of long-running transactions - I'd probably just accept the need to lock it for a little while. I'd start the drop trigger then I'd pg_terminate_backend all concurrent transactions that held locks on the table so their connections dropped and their transactions terminated. That'd let the drop trigger proceed promptly, at the cost of greater disruption. You can only consider an approach like this if your apps are well-written so they'll just retry transactions on transient errors like connection drops.

Another possible approach is to disable (not drop) the trigger by modifying the system catalogs directly.