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