Thread: field-update in before-trigger causes distinct to 'fail', new in v8.1 versus v8.0.4, demo-sql included

L.S.

I noticed that after creating these necessary objects:

================================================
create table t_src(value int);
create table t_dest(value int primary key);
create or replace function tr_t_dest_before_iud()
    returns trigger
    language 'plpgsql'
    volatile
    strict
    security invoker
    AS '    declare
        begin
RAISE NOTICE ''tr_t_dest_before_iud() triggered for value (%)'', NEW.value;
            NEW.value = NEW.value + 1;
            RETURN NEW;
        END;';
create trigger t_dest_before before insert or update or delete on t_dest for
each row execute procedure tr_t_dest_before_iud();

insert into t_src values (1);
insert into t_src values (5);
insert into t_src values (9);
insert into t_src values (5);
================================================


The following statement causes an error due to the fact that the distinct
isn't producing distinct values anymore.

db=# insert into t_dest select distinct value from t_src;
NOTICE:  tr_t_dest_before_iud() triggered for value (1)
NOTICE:  tr_t_dest_before_iud() triggered for value (5)
NOTICE:  tr_t_dest_before_iud() triggered for value (5)
ERROR:  duplicate key violates unique constraint "t_dest_pkey"


This seems to be caused by the update of 'value' in the before-trigger.
Removing the update will let the distinct produce proper results.



4cleanup:
================================================
drop table t_dest;
drop table t_src;
drop function tr_t_dest_before_iud();
================================================


select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.3
(1 row)





--
Best,




Frank.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> The following statement causes an error due to the fact that the distinct
> isn't producing distinct values anymore.
> This seems to be caused by the update of 'value' in the before-trigger.

Fix committed --- thanks for the report!

            regards, tom lane

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> (NB what would be the best way to get to such a patch without bothering you? I
> looked at the webcsv, but I wasn't sure whether you changed anything outside
> of execMain.c and execUtils.c and I'm not sure how to find out ;))

Looking at the pgsql-committers message is the easiest way to verify
which files were touched:
http://archives.postgresql.org/pgsql-committers/2005-11/msg00307.php
It also provides links that will give you the per-file diffs directly.

In this case, since the patch touches the widely known EState struct,
I'd recommend a full backend recompile after patching.

            regards, tom lane