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
yourcase, 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@ghs.fr
http://www.ghs.fr


-----Message d'origine-----
De:    Vladimir V. Zolotych [SMTP:gsmith@eurocom.od.ua]
Date:    lundi 8 mai 2000 18:00
À:    pgsql-admin@postgresql.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@eurocom.od.ua