On Tue, Aug 7, 2012 at 2:15 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On Tue, Aug 7, 2012 at 1:48 PM, Craig James <cjames@xxxxxxxxxxxxxx> wrote: >> I found this discussion from 2005 that says you can drop and restore a >> trigger inside a transaction, but that doing so locks the whole table: >> >> http://archives.postgresql.org/pgsql-general/2005-01/msg01347.php >>> From: Jeff Davis >>> >>> It got me curious enough that I tested it, and apparently droping a >>> trigger locks the table. Any actions on that table must wait until the >>> transaction that drops the trigger finishes. >>> >>> So, technically my system works, but requires a rather nasty lock while >>> the transaction (the one that doesn't want the trigger to execute) >>> finishes. >> >> I have a process that copies customer data from one database to >> another, and we know that the trigger has already done its work. The >> trigger is thus redundant, but it slows the copy WAY down, so I wanted >> to drop/restore it inside a transaction. >> >> Is it still true that drop-trigger inside a transaction will lock the >> whole table? We're using 8.4. > > absolutely -- the database needs to guard against other writers to the > table doing inserts in the meantime. there's no concept in SQL of > 'enforce this trigger for all writers, except for me' nor should there > be. > > one possible workaround is to hack your trigger function so that it > doesn't operate for particular roles. so your trigger might be: > > IF current_user = 'bulk_writer' THEN > return new; > END IF; > <expensive stuff> > > then you can log in with the bulk_writer role when you want to bypass > the checks. if your triggers are RI triggers though, you're hosed. I'm willing to bet that even without doing anything, just invoking the trigger will still cost a LOT more than the cost incurred with it just turned off. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
