PostgreSQL Bugs - field-update in before-trigger causes distinct to 'fail', new in v8.1 versus v8.0.4, demo-sql inclu

Author

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

Frank van Vugt

2005-11-14, 7:23 am

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_i
ud() 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

Sponsored Links