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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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...1/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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend