From: | Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | field-update in before-trigger causes distinct to 'fail', new in v8.1 versus v8.0.4, demo-sql included |
Date: | 2005-11-14 12:51:37 |
Message-ID: | 200511141351.37550.ftm.van.vugt@foxi.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2005-11-14 14:47:20 | Re: BUG #2039: Problem with Installation |
Previous Message | mattias | 2005-11-14 11:56:50 | BUG #2042: Installation failed |