Re: Is drop/restore trigger transactional? - Mailing list pgsql-performance

On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Tue, Aug 7, 2012 at 1:48 PM, Craig James <cjames@emolecules.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.
>
> But why must it?  Why can't other writers simply obey the trigger,
> since its removal has not yet been committed?
>> there's no concept in SQL of
>> 'enforce this trigger for all writers, except for me' nor should there
>> be.
>
> Why shouldn't there be, other than the bother of implementing and
> documenting it?  Sometimes theory needs to compromise with reality.
> When we don't provide slightly dangerous ways to make those
> compromises, people are forced to use very dangerous ways instead.
>
>>
>> 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>
>
> I don't know Craig's case, but often the most expensive of the
> "expensive stuff" is the bare fact of firing a trigger in the first
> place.

My use case is pretty simple: Copy some already-validated user data
from one schema to another.  Since the trigger has already been
applied, we're guaranteed that the data is already in the form we
want.

For your amusement: The trigger ensures that you can't buy illegal
drugs, explosives, weapons of war, corrosives and other dangerous or
illegal chemical compounds.  It executes a query against known
compounds from the DEA, Homeland Security, Department of
Transportation and several other lists.  Then calls a series of
functions that implement "rules" to find illegal or dangerous
compounds that aren't on anyone's list.  Some examples: "cocaine
derivatives" for obvious reasons; "two or more nitro groups on a small
molecule" to find chemicals that might explode; and "Metal-hydrogen
bond" to find things that will catch fire if exposed to air.

This is implemented in the database to esure that no matter how badly
a programmer screws up an app, you still can't get these chemical
compounds into an order.  The chemicals need to be in our database for
informational purposes, but we don't want law enforcement knocking on
our door.

Obviously this is a very expensive trigger, but one that we can drop
in a very specific circumstance.  But we NEVER want to drop it for
everyone.  It seems like a very reasonable use-case to me.

Craig James