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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster