Bug #820: RULE on INSERT unable to access NEW serial value anymore - Mailing list pgsql-bugs

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