Is drop/restore trigger transactional?

From: Craig James <cjames(at)emolecules(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Is drop/restore trigger transactional?
Date: 2012-08-07 18:48:23
Message-ID: CAFwQ8rfBDpt8qsPeRigM1Fnivm4ZCdxx_yRoVYg1_4eWXuZg5w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Thanks,
Craig

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2012-08-07 20:15:19 Re: Is drop/restore trigger transactional?
Previous Message Jeff Janes 2012-08-07 17:42:08 Re: Sequential scan instead of index scan