FAQ
hello, everyone:

version 8.3.0 in function plpgsql_exec_trigger.

in a trigger, if NEW is returned as the result and we do nothing to NEW.


for example, we have a table like this:

create table test (a int);
insert into test values(1);

and a trigger like:

create or replace function test_trigger() returns trigger as $$
begin
return new;
end; $$language plpgsql;

create trigger before_update_test before update
on test for each row execute procedure test_trigger();


in this trigger, we don't change the value of NEW.

than execute:

update test set a = 3;



after execution:

/* Copy tuple to upper executor memory */
rettup = SPI_copytuple((HeapTuple) (estate.retval));

we come to function ExecUpdate():

HeapTuple newtuple;
newtuple = ExecBRUpdateTriggers(estate, resultRelInfo,
tupleid, tuple);



Since the trigger's return value is copied to another memory address, the newtuple is impossible equal to the oldtuple.

so the following condition:

if (newtuple != tuple) /* modified by Trigger(s) */
{

is FALSE for ever.



I think we can add some judgment conditions in function plpgsql_exec_trigger() to avoid this problem.


billywq@163.com
2008-06-10

Search Discussions

  • Billy at Jun 11, 2008 at 1:46 am
    pgsql-hackers,hello:


    version 8.3.0 in function plpgsql_exec_trigger.

    in a trigger, if NEW is returned as the result and we do nothing to NEW.


    for example, we have a table like this:

    create table test (a int);
    insert into test values(1);

    and a trigger like:

    create or replace function test_trigger() returns trigger as $$
    begin
    return new;
    end; $$language plpgsql;

    create trigger before_update_test before update
    on test for each row execute procedure test_trigger();


    in this trigger, we don't change the value of NEW.

    than execute:

    update test set a = 3;



    after execution:

    /* Copy tuple to upper executor memory */
    rettup = SPI_copytuple((HeapTuple) (estate.retval));

    we come to function ExecUpdate():

    HeapTuple newtuple;
    newtuple = ExecBRUpdateTriggers(estate, resultRelInfo,
    tupleid, tuple);



    Since the trigger's return value is copied to another memory address, the newtuple is impossible equal to the oldtuple.

    so the following condition:

    if (newtuple != tuple) /* modified by Trigger(s) */
    {

    is FALSE for ever.



    I think we can add some judgment conditions in function plpgsql_exec_trigger() to avoid this problem.


    billy
    billywq@163.com
    2008-06-11
  • Billy at Jun 11, 2008 at 3:25 am
    Hi pghackers:

    version 8.3.0 in function plpgsql_exec_trigger.

    in a trigger, if NEW is returned as the result and we do nothing to NEW.


    for example, we have a table like this:

    create table test (a int);
    insert into test values(1);

    and a trigger like:

    create or replace function test_trigger() returns trigger as $$
    begin
    return new;
    end; $$language plpgsql;

    create trigger before_update_test before update
    on test for each row execute procedure test_trigger();


    in this trigger, we don't change the value of NEW.

    than execute:

    update test set a = 3;



    after execution:

    /* Copy tuple to upper executor memory */
    rettup = SPI_copytuple((HeapTuple) (estate.retval));

    we come to function ExecUpdate():

    HeapTuple newtuple;
    newtuple = ExecBRUpdateTriggers(estate, resultRelInfo,
    tupleid, tuple);



    Since the trigger's return value is copied to another memory address, the newtuple is impossible equal to the oldtuple.

    so the following condition:

    if (newtuple != tuple) /* modified by Trigger(s) */
    {

    is FALSE for ever.



    I think we can add some judgment conditions in function plpgsql_exec_trigger() to avoid this problem.


    billy
    billywq@163.com
    2008-06-11
  • Tom Lane at Jun 11, 2008 at 3:44 am

    "billy" <billywq@163.com> writes:
    I think we can add some judgment conditions in function plpgsql_exec_trigger() to avoid this problem.
    I don't especially see the point of adding extra complexity here.
    AFAICS you are talking about avoiding one or two palloc/pfree
    cycles, which is surely down in the noise compared to the cost of
    calling a plpgsql trigger.

    regards, tom lane
  • Billy at Jun 11, 2008 at 4:58 am
    Tom Lane,

    er, your explanation is reasonable.

    But at least the comment
    if (newtuple != tuple) /* modified by Trigger(s) */
    {
    .....
    is likely to misdirect us.

    It took me a few hours to figure it out. :-(

    ======= 2008-06-10 23:43:00 In your letter you say:=======
    "billy" <billywq@163.com> writes:
    I think we can add some judgment conditions in function plpgsql_exec_trigger() to avoid this problem.
    I don't especially see the point of adding extra complexity here.
    AFAICS you are talking about avoiding one or two palloc/pfree
    cycles, which is surely down in the noise compared to the cost of
    calling a plpgsql trigger.

    regards, tom lane

    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
    billy
    billywq@163.com
    2008-06-11

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 10, '08 at 9:51a
activeJun 11, '08 at 4:58a
posts5
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Billy: 4 posts Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase