pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMake a SQL statement not run trigger
8+ messages / 4 participants
[nested] [flat]
* Make a SQL statement not run trigger
@ 2007-08-21 21:26 Jon Collette <jon@etelos.com>
2007-08-21 21:51 ` Re: Make a SQL statement not run trigger Rodrigo De León <rdeleonp@gmail.com>
2007-08-21 22:07 ` Re: Make a SQL statement not run trigger Scott Marlowe <scott.marlowe@gmail.com>
2007-08-21 22:24 ` Re: Make a SQL statement not run trigger Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 3 replies; 8+ messages in thread
From: Jon Collette @ 2007-08-21 21:26 UTC (permalink / raw)
To: pgsql-sql
Is it possible to run an insert,update, or delete and have it not launch
a trigger like it normally would?
For example could I set a value
DONOTRUN = True;
insert into contacts ....
Where the trigger on contacts would call a function that would have an
IF statment for that DONOTRUN value?
Or is there just a global variable I could set to disable triggers and
then reset it? And would that be a per connection variable?
Thanks
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Make a SQL statement not run trigger
2007-08-21 21:26 Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
@ 2007-08-21 21:51 ` Rodrigo De León <rdeleonp@gmail.com>
2007-08-21 22:04 ` Re: Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
2 siblings, 1 reply; 8+ messages in thread
From: Rodrigo De León @ 2007-08-21 21:51 UTC (permalink / raw)
To: Jon Collette <jon@etelos.com>; +Cc: pgsql-sql
On 8/21/07, Jon Collette <jon@etelos.com> wrote:
> Is it possible to run an insert,update, or delete and have it not launch
> a trigger like it normally would?
alter table disable trigger ...
http://www.postgresql.org/docs/8.2/static/sql-altertable.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Make a SQL statement not run trigger
2007-08-21 21:26 Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
2007-08-21 21:51 ` Re: Make a SQL statement not run trigger Rodrigo De León <rdeleonp@gmail.com>
@ 2007-08-21 22:04 ` Jon Collette <jon@etelos.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jon Collette @ 2007-08-21 22:04 UTC (permalink / raw)
To: Rodrigo De León <rdeleonp@gmail.com>; +Cc: pgsql-sql
Thanks for the reply. It looks like this will disable the trigger for
all connections to the database. So if the chance occured that another
connection was using the table at the same time it wouldn't launch the
trigger either? am I wrong? I hope so ;)
Rodrigo De León wrote:
> On 8/21/07, Jon Collette <jon@etelos.com> wrote:
>
>> Is it possible to run an insert,update, or delete and have it not launch
>> a trigger like it normally would?
>>
>
> alter table disable trigger ...
>
> http://www.postgresql.org/docs/8.2/static/sql-altertable.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Make a SQL statement not run trigger
2007-08-21 21:26 Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
@ 2007-08-21 22:07 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-08-21 22:49 ` Re: Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
2 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2007-08-21 22:07 UTC (permalink / raw)
To: Jon Collette <jon@etelos.com>; +Cc: pgsql-sql
On 8/21/07, Jon Collette <jon@etelos.com> wrote:
> Is it possible to run an insert,update, or delete and have it not launch
> a trigger like it normally would?
>
> For example could I set a value
> DONOTRUN = True;
> insert into contacts ....
The closest thing to a session variable for pgsql is going to likely
be a temp table. you could have a temp table for each session that
stores such things and the trigger looks in the temp table to see what
to do.
> Or is there just a global variable I could set to disable triggers and
> then reset it? And would that be a per connection variable?
Yes, but that would open you up to race conditions. If another
session wanted the trigger to fire it would not get it. Using a temp
table would allow you to get around the race condition.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Make a SQL statement not run trigger
2007-08-21 21:26 Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
2007-08-21 22:07 ` Re: Make a SQL statement not run trigger Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-08-21 22:49 ` Jon Collette <jon@etelos.com>
2007-08-21 23:01 ` Re: Make a SQL statement not run trigger Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jon Collette @ 2007-08-21 22:49 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
I think this will work for what I need. I have been messing around with
this using select into
/select True as donothing into temporary table table_trigger_name;
then run statement that I want to be ignored
/
The trigger would have a select upon the table_trigger_name to determine
if it should run or not. I am having issues catching the exception when
the table is not found.
This is my test function of my trigger
/Create or replace function trigger_test() returns boolean as $$
declare
donothing boolean;
begin
donothing := False;
select donothing into donothing from table_trigger_name;
return boolean;
end;
$$ LANGUAGE plpgsql;
/
Which of course will error when there is no table_trigger_name made for
that session. I couldn't find an exception in the exceptions list for
table not found errors.
Scott Marlowe wrote:
> On 8/21/07, Jon Collette <jon@etelos.com> wrote:
>
>> Is it possible to run an insert,update, or delete and have it not launch
>> a trigger like it normally would?
>>
>> For example could I set a value
>> DONOTRUN = True;
>> insert into contacts ....
>>
>
> The closest thing to a session variable for pgsql is going to likely
> be a temp table. you could have a temp table for each session that
> stores such things and the trigger looks in the temp table to see what
> to do.
>
>
>> Or is there just a global variable I could set to disable triggers and
>> then reset it? And would that be a per connection variable?
>>
>
> Yes, but that would open you up to race conditions. If another
> session wanted the trigger to fire it would not get it. Using a temp
> table would allow you to get around the race condition.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Make a SQL statement not run trigger
2007-08-21 21:26 Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
2007-08-21 22:07 ` Re: Make a SQL statement not run trigger Scott Marlowe <scott.marlowe@gmail.com>
2007-08-21 22:49 ` Re: Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
@ 2007-08-21 23:01 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2007-08-21 23:01 UTC (permalink / raw)
To: Jon Collette <jon@etelos.com>; +Cc: pgsql-sql
On 8/21/07, Jon Collette <jon@etelos.com> wrote:
> I think this will work for what I need. I have been messing around with
> this using select into
>
> /select True as donothing into temporary table table_trigger_name;
> then run statement that I want to be ignored
> /
> The trigger would have a select upon the table_trigger_name to determine
> if it should run or not. I am having issues catching the exception when
> the table is not found.
>
Note that if you use temp tables much, you need to make sure that
autovacuum is up and running (or vacuum regularly) to keep the system
catalogs from getting bloated. Just FYI.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Make a SQL statement not run trigger
2007-08-21 21:26 Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
@ 2007-08-21 22:24 ` Thomas Kellerer <spam_eater@gmx.net>
2007-08-21 22:44 ` Re: Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
2 siblings, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2007-08-21 22:24 UTC (permalink / raw)
To: pgsql-sql
Jon Collette wrote on 21.08.2007 23:26:
> Is it possible to run an insert,update, or delete and have it not launch
> a trigger like it normally would?
>
> For example could I set a value
> DONOTRUN = True;
> insert into contacts ....
>
> Where the trigger on contacts would call a function that would have an
> IF statment for that DONOTRUN value?
>
> Or is there just a global variable I could set to disable triggers and
> then reset it? And would that be a per connection variable?
What we have done once, was to include a column in the table for this purpose.
If a special value for the column was provided during UPDATE or INSERT, the
trigger would immediately terminate, not doing any work. Thus the trigger still
"fires" every time, but simply won't do nothing. Not very elegant, but worked
for our environment.
Thomas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Make a SQL statement not run trigger
2007-08-21 21:26 Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
2007-08-21 22:24 ` Re: Make a SQL statement not run trigger Thomas Kellerer <spam_eater@gmx.net>
@ 2007-08-21 22:44 ` Jon Collette <jon@etelos.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jon Collette @ 2007-08-21 22:44 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
Good Idea. However some of my triggers use the DELETE action as well.
So I can't use this method.
Thomas Kellerer wrote:
> Jon Collette wrote on 21.08.2007 23:26:
>> Is it possible to run an insert,update, or delete and have it not
>> launch a trigger like it normally would?
>>
>> For example could I set a value
>> DONOTRUN = True;
>> insert into contacts ....
>>
>> Where the trigger on contacts would call a function that would have
>> an IF statment for that DONOTRUN value?
>>
>> Or is there just a global variable I could set to disable triggers
>> and then reset it? And would that be a per connection variable?
>
> What we have done once, was to include a column in the table for this
> purpose. If a special value for the column was provided during UPDATE
> or INSERT, the trigger would immediately terminate, not doing any
> work. Thus the trigger still "fires" every time, but simply won't do
> nothing. Not very elegant, but worked for our environment.
>
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-08-21 23:01 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-21 21:26 Make a SQL statement not run trigger Jon Collette <jon@etelos.com>
2007-08-21 21:51 ` Rodrigo De León <rdeleonp@gmail.com>
2007-08-21 22:04 ` Jon Collette <jon@etelos.com>
2007-08-21 22:07 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-08-21 22:49 ` Jon Collette <jon@etelos.com>
2007-08-21 23:01 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-08-21 22:24 ` Thomas Kellerer <spam_eater@gmx.net>
2007-08-21 22:44 ` Jon Collette <jon@etelos.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox