Обсуждение: rules problem
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
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