Thread

1. Is drop/restore trigger transactional?

   Craig James <cjames@emolecules.com> — 2012-08-07T18:48:23Z

   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
   
   

2. Re: Is drop/restore trigger transactional?

   Merlin Moncure <mmoncure@gmail.com> — 2012-08-07T20:15:19Z

   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.  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
   
   

3. Re: Is drop/restore trigger transactional?

   Scott Marlowe <scott.marlowe@gmail.com> — 2012-08-07T20:39:58Z

   On Tue, Aug 7, 2012 at 2: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.  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.
   
   I'm willing to bet that even without doing anything, just invoking the
   trigger will still cost a LOT more than the cost incurred with it just
   turned off.
   
   

4. Re: Is drop/restore trigger transactional?

   Jeff Janes <jeff.janes@gmail.com> — 2012-08-07T20:45:18Z

   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?  You could have the
   anomaly that a longer-running later-committing transaction used the
   old trigger while a shorter-running earlier-committing transaction
   used the new one (which isn't really an anomaly if the old and new are
   identical), but is that even barred if neither of them is in
   serializable mode?  And since triggers can do pretty much anything
   they want internally, there isn't much of a transactional guarantee
   with them anyway.
   
   > 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.
   
   cheers,
   
   Jeff
   
   

5. Re: Is drop/restore trigger transactional?

   Merlin Moncure <mmoncure@gmail.com> — 2012-08-07T21:21:36Z

   On Tue, Aug 7, 2012 at 3:45 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
   >> 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?  You could have the
   > anomaly that a longer-running later-committing transaction used the
   > old trigger while a shorter-running earlier-committing transaction
   > used the new one (which isn't really an anomaly if the old and new are
   > identical), but is that even barred if neither of them is in
   > serializable mode?  And since triggers can do pretty much anything
   > they want internally, there isn't much of a transactional guarantee
   > with them anyway.
   
   Triggers give a 100% transactional guarantee, period.  Yes, you can do
   things in them that violate MVCC, like make dblink calls, but you can
   do that from any SQL statement; they are no less transactionally
   guaranteed than regular SQL. As to your wider point, you could in
   theory interleave other work with adjustment of triggers although it
   seems pretty complicated and weird.  Also RI triggers (the most
   important case) would need special handling since (like check
   constraints) they are supposed to apply to the table as a whole, not
   records inserted since trigger creation.   Also serializable would be
   right out as you noted.
   
   >> 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.
   
   That's highly debatable.  a function call is somewhat expensive but is
   a fixed cpu cost.  RI triggers or complicated queries can really get
   expensive, especially with large tables.
   
   merlin
   
   

6. Re: Is drop/restore trigger transactional?

   Craig James <cjames@emolecules.com> — 2012-08-07T21:39:58Z

   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
   
   

7. Re: Is drop/restore trigger transactional?

   Samuel Gendler <sgendler@ideasculptor.com> — 2012-08-07T22:01:28Z

   On Tue, Aug 7, 2012 at 2:39 PM, Craig James <cjames@emolecules.com> wrote:
   
   >
   > 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.
   >
   >
   Sounds like you should try doing the work inside the trigger conditionally
   and see if that improves performance enough, since you aren't likely to get
   anything that better suits your needs without patching postgres.
   

8. Re: Is drop/restore trigger transactional?

   Merlin Moncure <mmoncure@gmail.com> — 2012-08-07T22:02:31Z

   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
   
   

9. Re: Is drop/restore trigger transactional?

   Jeff Janes <jeff.janes@gmail.com> — 2012-08-07T22:22:55Z

   On Tue, Aug 7, 2012 at 2: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:
   >>>
   >>> 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:
   
   Thanks.  That was probably more amusing to me in particular than to most
   pgsql hackers, as I think I've been a victim of your trigger.
   
   
   ...
   >
   > 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.
   
   And since the query is absolutely expensive, not just expensive
   relative to a no-op, then Merlin's suggestion seems entirely suitable
   for your use-case.
   
   Cheers,
   
   Jeff
   
   

10. Re: Is drop/restore trigger transactional?

    Craig James <cjames@emolecules.com> — 2012-08-07T22:46:53Z

    On Tue, Aug 7, 2012 at 3:22 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
    > On Tue, Aug 7, 2012 at 2: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:
    >>>>
    >>>> 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:
    >
    > Thanks.  That was probably more amusing to me in particular than to most
    > pgsql hackers, as I think I've been a victim of your trigger.
    >
    >
    > ...
    >>
    >> 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.
    >
    > And since the query is absolutely expensive, not just expensive
    > relative to a no-op, then Merlin's suggestion seems entirely suitable
    > for your use-case.
    
    Thanks for the ideas.  I think I have something to work with.
    
    Craig James
    
    

11. Re: Is drop/restore trigger transactional?

    Craig Ringer <ringerc@ringerc.id.au> — 2012-08-08T00:29:02Z

    On 08/08/2012 04:15 AM, Merlin Moncure wrote:
    > IF current_user = 'bulk_writer' THEN
    >    return new;
    > END IF;
    > <expensive stuff>
    ... or re-create the trigger with a `WHEN` clause (only available in 
    newer Pg versions, see CREATE TRIGGER) that excludes the migrated 
    customer ID. You'd have to do it in a new tx to avoid locking the table 
    for ages though.
    
    --
    Craig Ringer
    
    

12. Re: Is drop/restore trigger transactional?

    Merlin Moncure <mmoncure@gmail.com> — 2012-08-08T00:42:17Z

    On Tue, Aug 7, 2012 at 5:29 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
    > On 08/08/2012 04:15 AM, Merlin Moncure wrote:
    >>
    >> IF current_user = 'bulk_writer' THEN
    >>    return new;
    >> END IF;
    >> <expensive stuff>
    >
    > ... or re-create the trigger with a `WHEN` clause (only available in newer
    > Pg versions, see CREATE TRIGGER) that excludes the migrated customer ID.
    > You'd have to do it in a new tx to avoid locking the table for ages though.
    
    yeah --- and, locking aside, I'd advise you not to do that anyways:
    try and keep one block of code that enforces all the rules properly.
    also, good deployment practices (especially in cases of security
    sensitive environments) should have good firewalls between production
    services and developer introduced code.
    
    merlin
    
    

13. Re: Is drop/restore trigger transactional?

    Matheus de Oliveira <matioli.matheus@gmail.com> — 2012-08-11T14:16:02Z

    Creating an inherit table without a trigger would be a good idea? Like a
    kind of partitioning, but simpler.
    
    Cheers,
    Matheus de Oliveira