Re: BUG #2217: serial in rule and trigger

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