Re: Bug #820: RULE on INSERT unable to access NEW serial value anymore

pgsql-bugs(at)postgresql(dot)org writes:
> Enclosed is example code that behaves differently on versions 7.1.3
> and 7.2.3 with a loss of functionality in the latter version.

Um, your example shows that 7.1's behavior isn't actually useful either,
since its dbmodlog.rowid values don't match tblData.id:

> test=# select * from tblData;
> id | name | userid
> ----+-----------------+--------
> 2 | this | 7
> 4 | that | 8
> 6 | the other thing | 9
> (3 rows)

> test=# select * from dbmodlog;
> id | tablename | rowid | action | userid | modtime
> ----+-----------+-------+--------+--------+------------------------
> 1 | tblData | 1 | I | 7 | 2002-11-18 22:10:18-05
> 2 | tblData | 3 | I | 8 | 2002-11-18 22:10:18-05
> 3 | tblData | 5 | I | 9 | 2002-11-18 22:10:18-05
> (3 rows)

The fact that 7.2 yields NULLs is certainly a bug, but the fix applied
for 7.3 results in yet a third behavior:

regression=# select * from tblData;
id | name | userid
----+-----------------+--------
1 | this | 7
3 | that | 8
5 | the other thing | 9
(3 rows)

regression=# select * from dbmodlog;
id | tablename | rowid | action | userid | modtime
----+-----------+-------+--------+--------+----------------------------
1 | tblData | 2 | I | 7 | 2002-11-18 22:48:19.129938
2 | tblData | 4 | I | 8 | 2002-11-18 22:48:19.164941
3 | tblData | 6 | I | 9 | 2002-11-18 22:48:19.1785
(3 rows)

which is not any more useful for your purpose. The problem here is that
a rule is a macro, and as such it has all the well-known problems with
multiple evaluations of arguments: nextval() *will be called twice*
during execution of the rule and the original insert.

It is unlikely that we will change this behavior, as it would break the
cases where rules are actually useful. In your situation, I think a
rule is the wrong thing anyhow: you should be using a trigger. A
trigger gets the computed insertion row as argument, and does not need
to worry about side-effects of the expressions used in INSERT.

regards, tom lane