disable trigger from transaction

[Postgres General]

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

[Terry Lee Tucker]

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.

Post by Postgres General
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: ***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Jeff Davis]

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

Post by Terry Lee Tucker
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
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
"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.
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.

Post by Postgres General
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Terry Lee Tucker]

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.

Post by Jeff Davis
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

Post by Terry Lee Tucker
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
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
"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.
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.

Post by Postgres General
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq

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

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

Regards,
Jeff Davis

Post by Terry Lee Tucker
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.

Post by Jeff Davis
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

Post by Terry Lee Tucker
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
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
"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.
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.

Post by Postgres General
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Terry Lee Tucker]

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)

Post by 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.
Yours doesn't require any special locking, so it seems yours would be
the preferred solution.
Regards,
Jeff Davis

Post by Terry Lee Tucker
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: ***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org