Re: disable trigger from transaction - Mailing list pgsql-general

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.

Yours doesn't require any special locking, so it seems yours would be
the preferred solution.

Regards,
    Jeff Davis

On Mon, 2005-01-24 at 13:45 -0500, Terry Lee Tucker wrote:
> I don't know if droping a trigger inside a transaction will work. Besides
> that, we want the trigger to do its work in all other circumstances. With a
> hundred connections on the database, I don't know what kind of issues that
> would cause if the trigger were there, and suddenly, not there. We figured
> this was a safe approach.
>
> On Monday 24 January 2005 01:27 pm, Jeff Davis saith:
> > Would it work to just do a DROP TRIGGER at the begining of the
> > transaction and a CREATE TRIGGER at the end?
> >
> > Regards,
> >     Jeff Davis
> >
> > On Mon, 2005-01-24 at 06:50 -0500, Terry Lee Tucker wrote:
> > > Razvan,
> > >
> > > I don't believe there is a way of doing this from by way of some
> > > postgreSQL command. We accomplish this by creating a table called
> > > "override". It is defined as:
> > > recid     | integer                    | not null default
> > >     nextval('public.override_recid_seq'::text)
> > > trig_name | character varying | not null
> > > pid          | integer                  | not null
> > > batch     | character varying | not null
> > > Indexes:
> > >     "override_pkey" primary key, btree (recid)
> > >     "override_pid_key" unique, btree (pid, trig_name)
> > >     "override_pid_pkey1" btree (pid, batch)
> > >
> > > We use this table to accomplish what you are talking about. We insert
> > > into the table the trigger name, pid, and some made up string into batch.
> > > We use batch so we can provide different levels of override, but you may
> > > not need that. For the triggers we are interested in overriding, we code
> > > them to check for the existance of a record in override that matches the
> > > trigger name and the pid, and possibly, a batch name. If we find an
> > > override record, we simply return.
> > >
> > > Here is an example:
> > >     SELECT INTO ovrRec * FROM override WHERE
> > >         pid = pg_backend_pid () AND trig_name = name;
> > >     IF FOUND THEN
> > >         IF dbg THEN
> > >             RAISE NOTICE ''%: Overriding'', name;
> > >         END IF;
> > >         RETURN true;                        -- outa here
> > >     END IF;
> > >     RETURN false;
> > >
> > > Actually, we put the above code into a function and call the function
> > > from triggers that we may need to override from some other place.
> > >
> > > Maybe some of the others have a better way. Hope this helps.
> > >
> > > On Monday 24 January 2005 06:02 am, Postgres General saith:
> > > > hello,
> > > >
> > > > I am interested in disabling a trigger from a transaction.
> > > > I am not want to disable the trigger globally but only for the current
> > > > transaction.
> > > >
> > > > Can I do it somehow ?
> > > >
> > > >
> > > > thanks,
> > > > Razvan Radu
> > > >
> > > >
> > > > ---------------------------(end of
> > > > broadcast)--------------------------- TIP 5: Have you checked our
> > > > extensive FAQ?
> > > >
> > > >                http://www.postgresql.org/docs/faq
> > >
> > > __
> > >  Work: 1-336-372-6812
> > >  Cell: 1-336-363-4719
> > > email: terry@esc1.com
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
>