On Tue, Aug 7, 2012 at 4:39 PM, Craig James <cjames@xxxxxxxxxxxxxx> wrote: > On Tue, Aug 7, 2012 at 1:45 PM, Jeff Janes <jeff.janes@xxxxxxxxx> wrote: >> On Tue, Aug 7, 2012 at 1:15 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: >>> 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. >> >> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
