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

On Tue, Aug 7, 2012 at 4:39 PM, Craig James <cjames@emolecules.com> wrote:
> 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.

well, there you go:  create a role that is excepted from having to run
through those checks and take appropriate precautions (password,
pg_hba.conf etc) so that only people/things that are supposed to
bypass the checks can do so.  then the trigger can look for the role
and punt.

merlin