Hi All...

I'm been fighting this problem for a few days now, and it seems like it
should be simple. But the solution has eluded me so far...

I need to flag a record when it is updated or when it is a new insert.
Then I SELECT for the changed records and do something not related to
Postgres. Easy enough, I created a trigger procedure and fired it on
INSERT OR UPDATE and modify NEW to set the flag field to true.

But then the problem is how do I reset the trigger? If I do an UPDATE the
trigger fires again. I thought I could check for the flag field being NULL
and that works for an INSERT, but apparently if it is an update NEW
contains the existing value of the field.

I am trying to avoid modifying the cost the needs to set the flags (I can
change the schema), but I have full control over the code that has to reset
them. Is there a way I can update a record without firing the trigger, or
by bypassing it? This is a multi-user environment, so I can't really drop
the trigger and readd it.

Is there a solution not related to this?

I would appreciate some help, thanks very much!



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Nov 18, 2004, at 2:53 PM, Jim Archer wrote:

Would it work to drop and readd the trigger within a transaction? Would
that make it multi-user safe? I can't think of another way to bypass an
update trigger.

Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Why don't you check in your update trigger if the new record has the
flag "false" ? In that case you replace new with old, except you set the
flag to false. This way you can reset the flag by a simple update to
false of the flag field. All other queries should not touch the field.
In other words, use the trigger to reset it too, instead of disable
it...

HTH,
Csaba.

On Thu, 2004-11-18 at 06:53, Jim Archer wrote:

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Jim,

How about having the trigger write the pk of the table to a new table. The
backend processing could then just join the new table on the pk to the
existing table to give you a proper result set. In the same transaction
delete the contents of the new pk table. Not as efficient as setting a
flag but relationally sound and portable.

Note that this problem cries out for column triggers. I don't know if
anyone has them on a to do list.

Rick



Jim Archer
<jim (AT) archer (DOT) net> To: pgsql-general (AT) postgresql (DOT) org
Sent by: cc:
pgsql-general-owner@pos Subject: [GENERAL] How to know a record has been updated, then reset the flag?
tgresql.org


11/18/2004 12:53 AM
Please respond to Jim
Archer






Hi All...

I'm been fighting this problem for a few days now, and it seems like it
should be simple. But the solution has eluded me so far...

I need to flag a record when it is updated or when it is a new insert.
Then I SELECT for the changed records and do something not related to
Postgres. Easy enough, I created a trigger procedure and fired it on
INSERT OR UPDATE and modify NEW to set the flag field to true.

But then the problem is how do I reset the trigger? If I do an UPDATE the
trigger fires again. I thought I could check for the flag field being NULL

and that works for an INSERT, but apparently if it is an update NEW
contains the existing value of the field.

I am trying to avoid modifying the cost the needs to set the flags (I can
change the schema), but I have full control over the code that has to reset

them. Is there a way I can update a record without firing the trigger, or
by bypassing it? This is a multi-user environment, so I can't really drop
the trigger and readd it.

Is there a solution not related to this?

I would appreciate some help, thanks very much!



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match




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

http://www.postgresql.org/docs/faqs/FAQ.html

Jim Archer <jim (AT) archer (DOT) net> writes:
I think you need a three-state value instead of a boolean. The trigger
has to account for four cases:
* freshly inserted row (which will have the field's default value)
* newly updated row
* re-updated row (where we don't want to reset the flag)
* update that is supposed to reset the flag
and you simply cannot tell the third and fourth cases apart without
an additional state.

One possibility is to make the flag field be "int default 0", with
trigger logic along the lines of

if new.flag = 0 then
-- freshly inserted or newly updated row, so set flag
new.flag = 1;
elsif new.flag = 1 then
-- re-update, no change needed
elsif new.flag = 2 then
-- command to reset flag
new.flag = 0;
else
-- possibly raise error here
end if;

and obviously the convention for resetting the flag is to attempt to
update it to 2.

(Thinks some more...) Actually you could stick with a boolean field,
if you make use of NULL as your third state --- that is, the convention
becomes that the command for resetting the flag is to attempt to update
it to NULL. However this might be more fragile than the above, since
you can certainly imagine ordinary inserts or updates accidentally doing
it.

regards, tom lane

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