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

Kristofer Munn (kmunn@munn.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
RULE on INSERT unable to access NEW serial value anymore

Long Description
Enclosed is example code that behaves differently on versions 7.1.3 and 7.2.3 with a loss of functionality in the
latterversion. 

The purpose of the rule is to record the insertion of rows into table tblData recording the new record id (comes from a
sequence)and the id of the user that inserted the row.  In version 7.1.3, the ids are recorded (from new.id).  In
version7.2.3, only a null is recorded.  Hopefully this is enough data to demonstrate the problem - if not please feel
freeto contact me for additional information. 

Here are the results of two runs with the output from the successful drop & create commands omitted and the timestamps
shortenedfor readability: 

*** VERSION 7.2.3

test=# select version();
                            version
---------------------------------------------------------------
 PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)

test=# insert into tblData (name, userID) values ('this', 7);
INSERT 635463 1
test=# insert into tblData (name, userID) values ('that', 8);
INSERT 635465 1
test=# insert into tblData (name, userID) values ('the other thing', 9);
INSERT 635467 1
test=# select * from tblData;
 id |      name       | userid
----+-----------------+--------
  1 | this            |      7
  2 | that            |      8
  3 | the other thing |      9
(3 rows)

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

test=#

*** VERSION 7.1.3

test=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

test=# insert into tblData (name, userID) values ('this', 7);
INSERT 22011451 1
test=# insert into tblData (name, userID) values ('that', 8);
INSERT 22011453 1
test=# insert into tblData (name, userID) values ('the other thing', 9);
INSERT 22011455 1
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)

test=#



Sample Code
select version();

drop rule in_tblData;
drop table tblData;
drop table dbmodlog;
drop sequence tbldata_id_seq;
drop sequence dbmodlog_id_seq;

create table tblData (
    id serial,
    name varchar(80),
    userID int4
);

create table dbmodlog (
    id serial,
    tablename varchar(80),
    rowID int4,
    action char(1),
    userID int4,
    modtime timestamp default 'now()'
);

CREATE RULE in_tblData AS ON INSERT TO tblData
    DO INSERT INTO dbmodlog (tablename, rowID, action, userID)
    select 'tblData', new.id, 'I', new.userID;

insert into tblData (name, userID) values ('this', 7);
insert into tblData (name, userID) values ('that', 8);
insert into tblData (name, userID) values ('the other thing', 9);

select * from tblData;

select * from dbmodlog;



No file was uploaded with this report

pgsql-bugs@postgresql.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

On Mon, 18 Nov 2002 pgsql-bugs@postgresql.org wrote:

> Kristofer Munn (kmunn@munn.com) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> RULE on INSERT unable to access NEW serial value anymore
>
> Long Description

> 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.

I don't think rules really work for this purpose.

Even from your 7.1.3 example, notice that you don't really have a
functional key linking the two, rowid=(1,3,5) in dbmodlog and (2,4,6) in
id.

>  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)

And even replace NEW.id with a call to currval which might work for single
row cases fails for multiple row inserts.  I'd suggest converting to using
a trigger.

Both Tom Lane & Stephan Szabo have pointed out that I should use a trigger
for my needs (and I will).  Hopefully that will work for me in my
case.  Good to know the NULL bug will be fixed in the next version, even if
I won't be needing it.

Thanks, as always, for the quick turnaround.

- Kristofer Munn

KMI  www.munn.com  732-254-9305  503-905-1959(fax)  AIM:KrMunn  ICQ:352499