Rule problem with OLD / NEW record set

From: Ralph Graulich <maillist(at)shauny(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Rule problem with OLD / NEW record set
Date: 2005-01-27 01:42:33
Message-ID: Pine.LNX.4.61.0501270225500.10119@lilly.baden-online.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello everyone,

given is a table with a version history kind of thing I am currently
working on. Upon this table there is a view and the application interacts
with the view only, updating/inserting/deleting is controlled by rules. It
seems like the record set "OLD" gets changed when it is used in a SQL
expression:

CREATE TABLE table1
(
id INTEGER NOT NULL,
version INTEGER NOT NULL DEFAULT 0,
vnoflag CHAR(1),
content VARCHAR(20)
);

INSERT INTO table1 (id, version, vnoflag, content) VALUES (1, 1, 'Y',
'Test');

CREATE VIEW view_table1 AS SELECT * FROM table1;

-- create a rule for update
CREATE OR REPLACE RULE ru_view_table1_update
AS
ON UPDATE TO view_table1 DO INSTEAD
(
-- insert a new record with the old id, old version number incremented
-- by one, versionflag set to 'Y' and the new content
INSERT INTO table1 (id, version, vnoflag, content) VALUES (OLD.id,
OLD.version+1, 'Y', NEW.content);
-- update the old version and set its versionflag to 'N' as it is no
-- longer the current record
UPDATE table1 SET vnoflag = 'N' WHERE id = OLD.id AND version =
OLD.version;
);

SELECT * FROM view_table1;
id | version | vnoflag | content
----+---------+---------+---------
1 | 1 | Y | Test
(1 row)

UPDATE view_table1 SET content = 'New Test' WHERE id = 1 AND vnoflag =
'Y';
SELECT * FROM view_table1;
id | version | vnoflag | content
----+---------+---------+----------
1 | 1 | N | Test
1 | 2 | N | New Test

It seems like the UPDATE statement updates both the old and the new
version. If I correctly go through the statements by hand, they should
read:

INSERT INTO table1 (id, version, vnoflag, content) VALUES (1, 1+1, 'Y',
'New Test');
UPDATE table1 SET vnoflag = 'N' WHERE id = 1 AND version = 1;

If I change the UPDATE statement to read:

UPDATE table1 SET vnoflag = 'N' WHERE id = OLD.id AND vno = NEW.vno-1;

it works like expected:

id | version | vnoflag | content
----+---------+---------+----------
1 | 2 | Y | New Test
1 | 1 | N | Test

Where is my logical error? Shouldn't the first UPDATE statement suffice?

Best regards
... Ralph ...

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Clint Stotesbery 2005-01-27 02:12:38 Re: Moving from Transact SQL to PL/pgSQL
Previous Message PFC 2005-01-26 23:07:02 Re: testing/predicting optimization using indexes