Triggers/perform oddity between 7.2 and 7.3 - Mailing list pgsql-general

Hi gurus,

Anyone knows how to solve this? I was playing around with triggers on
my own machine which runs 7.3 and there it works okey, but when trying
it on 7.2 I get an error about duplicate keys. Upgrading to 7.3 on the
other machines is not possible yet. Any help on getting it to work
with 7.2 would be appreciated.

<snip>
create table a(
        n       varchar,
        x       int,
        y       int,
        z       int
);

create table b(
        n       varchar,
        x_sum   int,
        y_sum   int,
        z_sum   int,

        primary key(n)
);

create function plpgsql_call_handler() returns language_handler as
'/usr/lib/postgresql/lib/plpgsql' language c;
create language plpgsql handler plpgsql_call_handler;

create function "a_trigger"() returns "trigger" as '
declare n_rows integer;
begin
        if (tg_op = ''INSERT'') then
                perform n from b where n = new.n; -- Problem is here
                if not found then                 -- I think..
                        insert into b (
                                n,
                                x_sum,
                                y_sum,
                                z_sum
                        ) values (
                                new.n,
                                new.x,
                                new.y,
                                new.z
                        );
                else
                        update b set
                                x_sum = x_sum + new.x,
                                y_sum = y_sum + new.y,
                                z_sum = z_sum + new.z
                        where n = new.n;
                end if;
                return new;
        end if;
        if (tg_op = ''DELETE'') then
                perform n from a where n = old.n;
                get diagnostics n_rows = ROW_COUNT;
                if n_rows = 1 then
                        delete from b where n = old.n;
                else
                        update b set
                                x_sum = x_sum - old.x,
                                y_sum = y_sum - old.y,
                                z_sum = z_sum - old.z
                        where n = old.n;
                end if;
                return old;
        end if;
        if (tg_op = ''UPDATE'') then
                update b set
                        x_sum = x_sum - old.x + new.x,
                        y_sum = y_sum - old.y + new.y,
                        z_sum = z_sum - old.z + new.z
                where n = new.n;
                return new;
        end if;
end
' language 'plpgsql';

create trigger a_trigger before insert or update or delete on a for
each row execute procedure a_trigger();
</snap>

In 7.2.1 I get error:

trigger_test=# insert into a values('a', 1, 2, 3);
INSERT 49555 1
trigger_test=# insert into a values('a', 1, 2, 3);
NOTICE:  Error occurred while executing PL/pgSQL function a_trigger
NOTICE:  line 6 at SQL statement
ERROR:  Cannot insert a duplicate key into unique index b_pkey
trigger_test=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

In 7.3.3 it works fine:

trigger_test=# insert into a values('a', 1, 2, 3);
INSERT 456365 1
trigger_test=# insert into a values('a', 1, 2, 3);
INSERT 456366 1
trigger_test=# select version();
                                    version
-------------------------------------------------------------------------------
 PostgreSQL 7.3.3 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3
(Debian)
(1 row)

Thanks.

--
Riddler & Headcrusher - Plasma Fire