Postgres Pro
Downloads
Home   >   mailing lists

Thread: minimal update trigger

minimal update trigger

From
Andrew Dunstan
Date:
As discussed a little while back, I would like to add a generic trigger 
function which will force an update to skip if the new and old tuples 
are identical.

The guts of this is the following snippet of code:

|        rettuple  = newtuple = trigdata->tg_newtuple;       oldtuple = trigdata->tg_trigtuple;
       if (newtuple->t_len == oldtuple->t_len &&           newtuple->t_data->t_hoff == oldtuple->t_data->t_hoff &&
    HeapTupleHeaderGetNatts(newtuple->t_data) == HeapTupleHeaderGetNatts(oldtuple->t_data) &&
(newtuple->t_data->t_infomask& ~HEAP_XACT_MASK) ==            (oldtuple->t_data->t_infomask & ~HEAP_XACT_MASK) &&
   memcmp(((char *)newtuple->t_data) + offsetof(HeapTupleHeaderData, t_bits),                  ((char
*)oldtuple->t_data)+ offsetof(HeapTupleHeaderData, t_bits),                    newtuple->t_len -
offsetof(HeapTupleHeaderData,t_bits)) == 0)       {           rettuple = NULL;       }
 
       return rettuple;


I propose to call the function pg_minimal_update.

Unless there is an objection I will put together a patch + docs for this shortly. Not quite sure what section of the
docsto put it in - maybe a new subsection of the Functions chapter?
 


cheers

andrew
|

Re: minimal update trigger

From
David Fetter
Date:
On Tue, Feb 19, 2008 at 09:32:30PM -0500, Andrew Dunstan wrote:
>
> As discussed a little while back, I would like to add a generic
> trigger function which will force an update to skip if the new and
> old tuples are identical.

This one has lots of use cases.  Did the earlier discussion settle on
whether there should be a GUC and/or CREATE DATABASE and/or initdb
option for this?

Cheers,
David.
>
> The guts of this is the following snippet of code:
>
> |        rettuple  = newtuple = trigdata->tg_newtuple;
>        oldtuple = trigdata->tg_trigtuple;
>
>        if (newtuple->t_len == oldtuple->t_len &&
>            newtuple->t_data->t_hoff == oldtuple->t_data->t_hoff &&
>            HeapTupleHeaderGetNatts(newtuple->t_data) == HeapTupleHeaderGetNatts(oldtuple->t_data) &&
>            (newtuple->t_data->t_infomask & ~HEAP_XACT_MASK) ==            
> (oldtuple->t_data->t_infomask & ~HEAP_XACT_MASK) &&
>            memcmp(((char *)newtuple->t_data) + offsetof(HeapTupleHeaderData, t_bits),
>                   ((char *)oldtuple->t_data) + offsetof(HeapTupleHeaderData, t_bits),
>                     newtuple->t_len - offsetof(HeapTupleHeaderData, t_bits)) == 0)
>        {
>            rettuple = NULL;
>        }
>
>        return rettuple;
>
>
> I propose to call the function pg_minimal_update.
>
> Unless there is an objection I will put together a patch + docs for this shortly. Not quite sure what section of the
docsto put it in - maybe a new subsection of the Functions chapter?
 
>
>
> cheers
>
> andrew
> |
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: minimal update trigger

From
Andrew Dunstan
Date:

David Fetter wrote:
> On Tue, Feb 19, 2008 at 09:32:30PM -0500, Andrew Dunstan wrote:
>   
>> As discussed a little while back, I would like to add a generic
>> trigger function which will force an update to skip if the new and
>> old tuples are identical.
>>     
>
> This one has lots of use cases.  Did the earlier discussion settle on
> whether there should be a GUC and/or CREATE DATABASE and/or initdb
> option for this?
>
>   

None of the above. All we will be providing is a trigger function. You 
would create the trigger as with any other trigger:

| CREATE TRIGGER _min BEFORE UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE pg_minimal_update();

cheers

andrew
|