Sequence bug in insert trigger (rob@cabrion.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Sequence bug in insert trigger
Long Description
Insert triggers always read sequences at their min (or start) value. This only occurs when triggerd by an insert.
Updateswork as expected.
ALL access to sequence's value returns the same insider an insert trigger!
everywhere else: currval(seq) = 9
in insert trigger:
nextval(seq) = 1;
currval(seq) = 1;
seq.last_val = 1;
The sample below uses only one function and only one trigger. You can split them up any way you like, it still doesn't
work.
I am: RedHat 6.2 (generic) Postgres 7.0.2 (generic from rpm's)
Happy hunting!
Sample Code
--just paste this into psql, make sure you have plpgsql installed
create sequence ____version_seq____;
create table test_table (name text, ____rowver____ int4);
create function sync_test_ver() returns opaque as
'begin
if TG_OP = ''INSERT'' then
if new.____rowver____ isnull then
new.____rowver____ := nextval(''____version_seq____'');
end if;
return NEW;
end if;
if new.____rowver____ = old.____rowver____ then
new.____rowver____ := nextval(''____version_seq____'');
end if;
return NEW;
end;' language 'plpgsql';
create trigger test_trigger before insert or update on test_table for
each row execute procedure sync_test_ver();
insert into test_table values('jim');
--Doh! ____rowver____ = 1 (should be 2)
select * from test_table;
update test_table set name = name;
--yeah! ____rowver____ = 2
select * from test_table;
insert into test_table values('joe');
--Doh! ____rowver____ = 1 (should be 3)
select * from test_table;
No file was uploaded with this report
