RE: rules problem

Here is my $0.02 :
* when you create "id SERIAL", Postgres remembers to call function nextval on each insertion,
* the rule's NEW.id item uses the function nextval itself instead of it's result
This explains why the ID's are what you see :
* first of all, you insert the log, calling nextval for the SERIAL (id=1 in the log)
* then you actually insert the data into the colors table (first row has id=2)
* then you insert a second time : first into the log (id=3) then into the actual table (id=4)
This make me think about date constants : 'now' is a constant that have a different value each time you call it. In your case, the rule must use then constant 'nextval', which increments the actual sequence on each call.
Either this is a bug... or a feature...
I don't see any genral workaround here. Maybe there is another way of retreiving the actual inserted data (other than NEW.id)

Yours,

Nicolas Huillard
G.H.S
Directeur Technique
Tél : +33 1 43 21 16 66
Fax : +33 1 56 54 02 18
mailto:nhuillard(at)ghs(dot)fr
http://www.ghs.fr

-----Message d'origine-----
De: Vladimir V. Zolotych [SMTP:gsmith(at)eurocom(dot)od(dot)ua]
Date: lundi 8 mai 2000 18:00
À: pgsql-admin(at)postgresql(dot)org
Objet: [ADMIN] rules problem

Hello all,

Encountered the problem with using RULEs. Cannot log
(e.g. write some info about insertions into sepearate table)
insertions properly. Detailed description (not long or sophisticated)
follows:

I do:

1) CREATE TABLE colors (id SERIAL, color TEXT);

2) Create table for log info:

CREATE TABLE colors_log (color_id INT4, color TEXT);

3) Create RULE that actually makes log:

CREATE RULE log_color
AS ON INSERT
TO colors
DO INSERT INTO colors_log VALUES (NEW.id, NEW.color);

4) Make some insertions:

INSERT INTO colors (color) VALUES ('red');

The same for 'green', 'blue'.

5) SELECT * FROM colors;

id|color
--+-----
2|red
4|green
6|blue

Here appears the first question:
why 'id' is 2, 4, 6, not 1, 2, 3?

7) SELECT * FROM colors_log;

color_id|color
--------+-----
1|red
3|green
5|blue

The problem is: the 'id's differ. E.g.,
In colors_log table the saved 'id' are wrong.

Thanks!

--
Vladimir Zolotych gsmith(at)eurocom(dot)od(dot)ua