Re: BUG #4620: Unexpected(doc'd) side effects of using serial and rules

Many thanks. I guessed as much. It would be useful to add something to the
documentation to explain that functions used as defaults are re-written into
the sql and the implications of this if used with rules.

I did re-write it with a trigger but because statement level triggers do not
have access to NEW and OLD it means row level triggers and the performance
issues they bring with changes in large numbers of rows.

Cheers

Simon Keen

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 18 January 2009 16:35
To: Simon Keen
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4620: Unexpected(doc'd) side effects of using
serial and rules

"Simon Keen" <simon(dot)keen(at)eglimited(dot)co(dot)uk> writes:
> I have used serial columns as primary keys in some tables. I have a
> rule that on insert to a table inserts in another table. However, the
> rule appears to cause the seq nextval() to be invoked multiple times
> thus destroying any value in NEW.serial_col.

This is a well-known issue arising from the fact that a rule is really a
macro: multiple references to any expression in the original statement
result in multiple evaluations of that expression.

By and large, the best way to propagate inserted information to other tables
is with a trigger.

regards, tom lane