The following bug has been logged online:
Bug reference: 3929
Logged by: Arjan Tuinhout
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1
Operating system: Kubuntu 6.06 Dapper
Description: RULE causes unintended update of SEQUENCE
Details:
-- The SQL code explains the problem:
-- I have 2 tables one storing basic data about documents 'doc' and one
storing the revisions 'rev'
-- I add one RULE to make sure that the latest available revision identifier
is available in the 'doc' table
-- The rev_id is generated by a SEQUENCE
-- However the intended code does not work: appearantly the SEQUENCE is
update by calling the RULE; proofed by the workaround.
-- Please help. Thanx. Arjan.
CREATE SEQUENCE id;
CREATE TABLE doc (doc_id INT4 DEFAULT nextval('id') PRIMARY KEY, name TEXT,
curr_rev_id INT4);
CREATE TABLE rev (doc_id INT4 REFERENCES doc, rev_id INT4 DEFAULT
nextval('id') PRIMARY KEY, revision TEXT);
CREATE RULE upd_doc AS ON INSERT TO rev DO ALSO UPDATE doc SET curr_rev_id =
NEW.rev_id WHERE doc_id = NEW.doc_id;
INSERT INTO doc VALUES(1, 'Book on PostgreSQL', NULL);
INSERT INTO rev (doc_id, revision) VALUES (1, 'Revision 1');
INSERT INTO rev (doc_id, revision) VALUES (1, 'Revision 2');
-- The next select statement should yield the current doc version, but
returns nothing...
SELECT * FROM doc NATURAL JOIN rev WHERE curr_rev_id = rev_id;
-- From the next select statement indicates the problem: the RULE does
increment the SEQUENCE id!!!
SELECT * FROM doc NATURAL JOIN rev;
-- A work around, proofing the problem lies in the RULE statement, could
be:
CREATE OR REPLACE RULE upd_doc AS ON INSERT TO rev DO ALSO UPDATE doc SET
curr_rev_id = currval('id')WHERE doc_id = NEW.doc_id;
-- This works but is dangerous... because you need to rely on using the
sequence to create unique identifiers for revisions...)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
