Thread: Rule problem with OLD / NEW record set (repost)

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

Ralph Graulich <maillist@shauny.de> writes:
> 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;
>    );

> It seems like the UPDATE statement updates both the old and the new
> version.

Yes, because (loosely speaking) OLD refers to the view, and once you've
done the INSERT there is now another matching row in the view.  Try
doing the UPDATE first, then the INSERT.

            regards, tom lane

Hi Tom,

> Yes, because (loosely speaking) OLD refers to the view, and once you've
> done the INSERT there is now another matching row in the view.  Try
> doing the UPDATE first, then the INSERT.

First of all thanks alot for your input. - If I change the order of the
statement, so the UPDATE comes before the INSERT statement, I do only get
the following result:

UPDATE view_table1 SET content = 'New Test' WHERE id = 1 AND vnoflag =
'Y';
UPDATE 1

SELECT * FROM view_table1;
  id | version | vnoflag | content
----+---------+---------+---------
   1 |       1 | N       | Test

So the update works, but no row is inserted, however the rule now looks
like:

CREATE OR REPLACE RULE ru_view_table1_update
AS
ON UPDATE TO view_table1 DO INSTEAD
   (
   UPDATE table1 SET vnoflag = 'N' WHERE id = OLD.id AND version =
OLD.version;
   INSERT INTO table1 (id, version, vnoflag, content) VALUES (OLD.id,
OLD.version+1, 'Y', NEW.content);
   );

It seems like the best solution would involve storing the old values as
well as the new values into temporary variables during the execution of
the rule? Do you know wether that affects the performance?

Best regards
... Ralph ...