Thread

1. Sequence bug in insert trigger

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-11-06T21:32:25Z

   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. Updates work 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
   
   
   

2. Re: Sequence bug in insert trigger

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-07T07:33:58Z

   pgsql-bugs@postgresql.org writes:
   > insert into test_table values('jim');
   > --Doh! ____rowver____ = 1 (should be 2)
   
   1 is correct for the first nextval() from a sequence.
   
   > insert into test_table values('joe');
   > --Doh! ____rowver____ = 1 (should be 3)
   
   I can't duplicate this under either 7.0.2 or current sources.
   I get this final state from your example:
   
   play=> select * from test_table;
    name | ____rowver____
   ------+----------------
    jim  |              2
    joe  |              3
   (2 rows)
   
   which looks fine to me...
   
   			regards, tom lane