On Thu, Jan 26, 2006 at 07:17:57PM +0000, ATTILA GATI wrote: > create table test(id serial, txt varchar); > create table mon(n int); > create rule monitor as on insert to test do > insert into mon values (NEW.id); > insert into test (txt) values ('xxx'); > > What I expect is to get the latest id written in > table mon whenever I insert a data into table test. > However test.id will be incremented by 2!
This isn't a bug, it's a misunderstanding of how rewrite rules work. NEW.id in the rule is rewritten as whatever expression that column had in the original query, so if id in the original query is evaluated as nextval('test_id_seq') then it will be the same in the rule; hence, nextval() gets called twice. See the archives for numerous past discussion. Try using a trigger instead of a rule. > However - although the relevant part of the documentation is identical for > both versions - in case > of version 8.1 I found now holes when the transaction was aborted for some > reason (not in the above example, > just without a trigger or rule). > So there must be a difference between the 2 versions, but the documentation > hasn't been modified. Sequences don't roll back so they can have holes; that's long-standing behavior that hasn't changed. Can you provide a test case that behaves differently in different versions of PostgreSQL? -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend