Thread: Adding a rule makes my sequence increment twice

Hello,

I have a table (table1) with a column called 'id' which has a default
value of nextval('id_seq').  I insert into table1, it increments, all is
happy.  I add a totally unrelated rule (rule1) to this table, one which
just changes the value of a field in another table (table2) (and table2
is in no way connected to the id_seq), and now when I insert into
table1, the sequence gets incremented twice.  I'm wondering if this is a
product of the way that rules rewrite the query or something.  Any
insight?

Unfortunately, I cannot duplicate this behavior using simple test
tables, but I am quite sure that table2 in no way alters the value of
id_seq.  If for instance the value of the sequence before the insert was
10000, I see something like this:

sequence value = 10000
I insert a row into table 1
At the start of the rule1 execution value of sequence is 10001
At the end of the rule1 execution value of sequence is 10001
insert returns
sequence value = 10002

It's got me baffled after exploring it all morning.

Thanks,
Fran

Fran Fabrizio <ffabrizio@mmrd.com> writes:
> I have a table (table1) with a column called 'id' which has a default
> value of nextval('id_seq').  I insert into table1, it increments, all is
> happy.  I add a totally unrelated rule (rule1) to this table, one which
> just changes the value of a field in another table (table2) (and table2
> is in no way connected to the id_seq), and now when I insert into
> table1, the sequence gets incremented twice.  I'm wondering if this is a
> product of the way that rules rewrite the query or something.  Any
> insight?

You didn't show us the rule, but I'm wondering if it refers to NEW.id?
Remember that NEW is basically a macro for the expression(s) being
inserted into the target table, and if one of those expressions is
nextval('id_seq'), you're very likely to end up with multiple
evaluations of the expression.

Usually, when someone has a complaint like this, my advice is that they
should be using triggers not rules.  Triggers have a little more
notational cruft to them (because you have to write a plpgsql
procedure), but in terms of semantics they are way simpler than rules.
A rule is good for *transforming* the given query to do something else;
you need to think of it as changing the whole query, not as operating on
any individual tuple.  If what you want to do is take some additional
action when a tuple is inserted/updated/deleted, a trigger is what you
want, not a rule.

            regards, tom lane