Re: Is drop/restore trigger transactional?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

merlin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux