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)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Ralph Heinkel wrote:
This is as expected (though perhaps not what you want). Rules are
basically macros, so you can end up with nextval() etc. being evaluated
multiple times.

For logging inserts, you want a trigger.

HTH

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Sun, Oct 31, 2004 at 03:37:26PM +0000, Ralph Heinkel wrote:
It's a feature.

Rules are macros, so when you refer to NEW.storage_id in the rule
then the default expression "nextval('tmp_storage_id_seq')" is
evaluated again. If you don't want that to happen then use a
trigger.

This comes up often -- maybe somebody could add it to the FAQ.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly