Rule problem with OLD / NEW record set (repost)

[Ralph Graulich <maillist@xxxxxxxxx>]

Hello,

I posted the question below to the pgsql-sql list, but did not receive any 
answer yet. Therefore I repost it on pgsql-general in hope for any 
information regarding that issue. Thanks alot.

Best regards
... Ralph ...

---
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?
---


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match