Re: Adding a rule makes my sequence increment twice

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