Message-ID: <4207418B.E7807E82@rodos.fzk.de>
Date: Mon, 07 Feb 2005 11:23:07 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: maillist@shauny.de
Cc: pgsql-sql@postgresql.org
Subject: Re: Rule problem with OLD / NEW record set
References: <Pine.LNX.4.61.0501270225500.10119@lilly.baden-online.de>
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

Ralph Graulich wrote:
> 
> 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 ...
> 

I've read your mail pretty late (meaning today), and I was surprised 
about what is happening the same you were. 
But after reading the manual 
$PGSQLD/doc/html/rules-update.html Chapter 34. The Rule System 
and especially 34.3.1.1. A First Rule Step by Step 
it became obvious what's going on behind a rule execution. 
HTH

Regards, Christoph