Обсуждение: How to know a record has been updated, then reset the flag?

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!

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

>  This is a multi-user environment, so I can't really drop the trigger
> and readd it.

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

--On Thursday, November 18, 2004 3:15 PM +0900 Michael Glaesemann
<grzm@myrealbox.com> 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.

I had that idea too, and I have no idea. :-(

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:
> 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

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@archer.net>               To:       pgsql-general@postgresql.org
                     
                      Sent by:                       cc:
                     
                      pgsql-general-owner@pos        Subject:  [GENERAL] How to know a record has been updated, then
resetthe 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

Jim Archer <jim@archer.net> writes:
> 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 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