Dear debuggers,
I'd like to report the following "strange" behavior that I encountered while trying (a bad idea, I know) to use a rule as a "poor man sql-trigger".
It seems that "on update do also" rules the new.* fields are evaluated several times instead of being computed once, which is a bad idea, esp. for "nextval".
As a consequence, my update does not work, i.e. new.id is actually different from the id being used by the insert in the example so the expected behavior that previous . [Note that even if it would work as I expected, concurrent transaction could break the property.]
Anyway, it really looks like a strange behavior to me, so misleading and unexpected that it could be qualified as a bug rather than a feature. I did not spot such caveats by a quick look thru the documentation.
In the attached file, the result of 4 inserts is:
id | data | islast ----+-------+-------- 1 | one | f 3 | two | f 6 | three | f 10 | four | f
last_val is 14
Where I would rather have expected:
id | data | islast ----+-------+-------- 1 | one | f 2 | two | f 3 | three | f 4 | four | t
last_val should be 4
Have a nice day,
-- Fabien.
DROP TABLE t;
CREATE TABLE t
( id SERIAL PRIMARY KEY,
data TEXT NOT NULL,
isLast BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE RULE t_update AS
ON INSERT TO t DO ALSO
UPDATE t SET isLast=FALSE
WHERE id<new.id;
INSERT INTO t(data) VALUES('one');
SELECT last_value FROM t_id_seq;
INSERT INTO t(data) VALUES('two');
SELECT last_value FROM t_id_seq;
INSERT INTO t(data) VALUES('three');
SELECT last_value FROM t_id_seq;
INSERT INTO t(data) VALUES('four');
SELECT last_value FROM t_id_seq;
SELECT * FROM t;
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
