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

From pgsql-bugs@postgresql.org
Subject Bug #820: RULE on INSERT unable to access NEW serial value anymore
Date
Msg-id 20021119031336.974A947592C@postgresql.org
Whole thread Raw
Responses Re: Bug #820: RULE on INSERT unable to access NEW serial value anymore
Re: Bug #820: RULE on INSERT unable to access NEW serial
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

pgsql-bugs by date:

Previous
From: Jiang Sheng
Date:
Subject: Re: Different result when using '=' and 'like' in unicode mode
Next
From: Tom Lane
Date:
Subject: Re: Bug #820: RULE on INSERT unable to access NEW serial value anymore