Re: Is drop/restore trigger transactional?

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.

merlin