possible bug using combination of 'serial' and rule

From: Ralph Heinkel <ralph(at)strubi(dot)ox(dot)ac(dot)uk>
To: pgsql-bugs(at)postgresql(dot)org
Subject: possible bug using combination of 'serial' and rule
Date: 2004-10-31 15:37:26
Message-ID: 200410311537.26743.ralph@strubi.ox.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

I think this is a bug (I hope not a feature).

Description:
--------------------
The table 'tmp' gets records added, and uses a serial to fill
the attribute 'strorage_id'. The table has a rule which logs
all inserts into the table 'log'.

Problem:
----------------
For each insert into table 'tmp' the serial counter is increased
twice, once to create the entry in 'tmp', once for 'log'. The problem
is that the rule does not see the correct 'storage_id'!!!
You can see that the 'tmp' table only contains odd storage_ids,
while the log table only contains even ones.

The problem can be reproduced with postgresql 8.0.0beta4
but also with 7.4.1. So it does not seem to be new.

Example:
-------------------
create table log
(
storage_id integer
);

create table tmp
(
storage_id serial not null,
location_id integer
);

create or replace rule INS_STORAGE as on INSERT to tmp
do (insert into log (storage_id) values (NEW.storage_id);
);

-- Now fill the table:
insert into tmp (location_id) values (1);
insert into tmp (location_id) values (1);

test=# select * from tmp;
storage_id | location_id
------------+-------------
1 | 1
3 | 1
(2 rows)

test=# select * from log;
storage_id
------------
2
4
(2 rows)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2004-10-31 15:56:37 Re: date_trunc problems crossing DST border
Previous Message Robert Treat 2004-10-31 15:29:36 Re: minor issue with psql keeping connections