Re: Is drop/restore trigger transactional?

On Tue, Aug 7, 2012 at 2:15 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Aug 7, 2012 at 1:48 PM, Craig James <cjames(at)emolecules(dot)com> 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.