Thread: disable trigger from transaction

disable trigger from transaction

From

Postgres General

Date:

24 January 2005, 14:03:16

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

Re: disable trigger from transaction

From

Terry Lee Tucker

Date:

24 January 2005, 14:54:23

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

Re: disable trigger from transaction

From

Jeff Davis

Date:

24 January 2005, 21:27:59

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)

Re: disable trigger from transaction

From

Terry Lee Tucker

Date:

24 January 2005, 21:49:36

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

--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Re: disable trigger from transaction

From

Jeff Davis

Date:

25 January 2005, 08:03:14

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
>

Re: disable trigger from transaction

From

Terry Lee Tucker

Date:

25 January 2005, 14:23:59

I'm glad your curiosity got the best of you ;o)

I was planning to test it out, but didn't have the time to do it. I too, was
very curious as to what the ramifications of dropping the trigger would be in
that scenario. Now, we know :o)

On Monday 24 January 2005 11:07 pm, Jeff Davis saith:
> 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.
> >

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com