Bug #772: Rewriting on multi-record updates is unreliable

Anto Prijosoesilo (antop64@yahoo.com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Rewriting on multi-record updates is unreliable

Long Description
Using RULEs to push values up a hierarchy of relations fail to work correctly when multiple records are updated.

I'm including the output of the code below.

I've inserted some comments below to mark the interesting parts corresponding to the same parts in the code.

Notice that when I updated T2 with 1 query per record, the results were as expected ("Update one-by-one" comment).
Multi-recordupdates per query also works as expected if the updates are grouped on T1.ID ("Mass update (1)" comment). 

The thing  broke down when multi-record updates across T1.ID happens ("Mass update (2)" and "(3)" comments).

Thanks.

>>> Output follows >>>
SELECT VERSION();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.2.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4
(1 row)

CREATE TABLE T1 (
        ID integer NOT NULL Primary Key,
        X integer DEFAULT 0 NOT NULL
);
psql:test.sql:10: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 't1_pkey' for table 't1'
CREATE
CREATE TABLE T2 (
        ID integer NOT NULL,
        T1 integer NOT NULL Constraint T2_T1 REFERENCES T1(ID),
        Constraint T2_PKey Primary Key (ID, T1),
        X integer DEFAULT 0 NOT NULL
);
psql:test.sql:17: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 't2_pkey' for table 't2'
psql:test.sql:17: NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
CREATE RULE UpdateT2 AS
        ON UPDATE TO T2 DO
        (UPDATE T1 SET X = X - old.X WHERE ID = old.T1;
        UPDATE T1 SET X = X + new.X WHERE ID = new.T1);
CREATE
INSERT INTO T1(ID) VALUES(1);
INSERT 32052 1
INSERT INTO T1(ID) VALUES(2);
INSERT 32053 1
INSERT INTO T2(ID, T1) VALUES(1, 1);
INSERT 32054 1
INSERT INTO T2(ID, T1) VALUES(1, 2);
INSERT 32055 1
INSERT INTO T2(ID, T1) VALUES(2, 1);
INSERT 32056 1
INSERT INTO T2(ID, T1) VALUES(2, 2);
INSERT 32057 1
SELECT * FROM T2;
 id | t1 | x
----+----+---
  1 |  1 | 0
  1 |  2 | 0
  2 |  1 | 0
  2 |  2 | 0
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  1 | 0
  2 | 0
(2 rows)

-- Update one-by-one

UPDATE T2 SET X=2 WHERE ID=1 AND T1=1;
UPDATE 1
SELECT * FROM T2;
 id | t1 | x
----+----+---
  1 |  2 | 0
  2 |  1 | 0
  2 |  2 | 0
  1 |  1 | 2
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  2 | 0
  1 | 2
(2 rows)

UPDATE T2 SET X=3 WHERE ID=1 AND T1=2;
UPDATE 1
SELECT * FROM T2;
 id | t1 | x
----+----+---
  2 |  1 | 0
  2 |  2 | 0
  1 |  1 | 2
  1 |  2 | 3
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  1 | 2
  2 | 3
(2 rows)

UPDATE T2 SET X=5 WHERE ID=2 AND T1=1;
UPDATE 1
SELECT * FROM T2;
 id | t1 | x
----+----+---
  2 |  2 | 0
  1 |  1 | 2
  1 |  2 | 3
  2 |  1 | 5
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  2 | 3
  1 | 7
(2 rows)

UPDATE T2 SET X=7 WHERE ID=2 AND T1=2;
UPDATE 1
SELECT * FROM T2;
 id | t1 | x
----+----+---
  1 |  1 | 2
  1 |  2 | 3
  2 |  1 | 5
  2 |  2 | 7
(4 rows)

SELECT * FROM T1;
 id | x
----+----
  1 |  7
  2 | 10
(2 rows)

UPDATE T2 SET X=0 WHERE ID=1 AND T1=1;
UPDATE 1
UPDATE T2 SET X=0 WHERE ID=1 AND T1=2;
UPDATE 1
UPDATE T2 SET X=0 WHERE ID=2 AND T1=1;
UPDATE 1
UPDATE T2 SET X=0 WHERE ID=2 AND T1=2;
UPDATE 1
SELECT * FROM T2;
 id | t1 | x
----+----+---
  1 |  1 | 0
  1 |  2 | 0
  2 |  1 | 0
  2 |  2 | 0
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  1 | 0
  2 | 0
(2 rows)

-- Mass update (1)

UPDATE T2 SET X=3 WHERE ID=1;
UPDATE 2
UPDATE T2 SET X=5 WHERE ID=2;
UPDATE 2
SELECT * FROM T2;
 id | t1 | x
----+----+---
  1 |  1 | 3
  1 |  2 | 3
  2 |  1 | 5
  2 |  2 | 5
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  1 | 8
  2 | 8
(2 rows)

UPDATE T2 SET X=0;
UPDATE 4
UPDATE T1 SET X=0;
UPDATE 2
SELECT * FROM T2;
 id | t1 | x
----+----+---
  1 |  1 | 0
  1 |  2 | 0
  2 |  1 | 0
  2 |  2 | 0
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  1 | 0
  2 | 0
(2 rows)

-- Mass update (2)

UPDATE T2 SET X=3 WHERE T1=1;
UPDATE 2
UPDATE T2 SET X=5 WHERE T1=2;
UPDATE 2
SELECT * FROM T2;
 id | t1 | x
----+----+---
  1 |  1 | 3
  2 |  1 | 3
  1 |  2 | 5
  2 |  2 | 5
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  1 | 3
  2 | 5
(2 rows)

UPDATE T2 SET X=0;
UPDATE 4
UPDATE T1 SET X=0;
UPDATE 2
SELECT * FROM T2;
 id | t1 | x
----+----+---
  1 |  1 | 0
  2 |  1 | 0
  1 |  2 | 0
  2 |  2 | 0
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  1 | 0
  2 | 0
(2 rows)

-- Mass update (3)

UPDATE T2 SET X=7;
UPDATE 4
SELECT * FROM T2;
 id | t1 | x
----+----+---
  1 |  1 | 7
  2 |  1 | 7
  1 |  2 | 7
  2 |  2 | 7
(4 rows)

SELECT * FROM T1;
 id | x
----+---
  1 | 7
  2 | 7
(2 rows)



Sample Code
-- Version

SELECT VERSION();

-- Create test tables

CREATE TABLE T1 (
    ID integer NOT NULL Primary Key,
    X integer DEFAULT 0 NOT NULL
);

CREATE TABLE T2 (
    ID integer NOT NULL,
    T1 integer NOT NULL Constraint T2_T1 REFERENCES T1(ID),
    Constraint T2_PKey Primary Key (ID, T1),
    X integer DEFAULT 0 NOT NULL
);

CREATE RULE UpdateT2 AS
    ON UPDATE TO T2 DO
    (UPDATE T1 SET X = X - old.X WHERE ID = old.T1;
    UPDATE T1 SET X = X + new.X WHERE ID = new.T1);

-- Test initial values

INSERT INTO T1(ID) VALUES(1);
INSERT INTO T1(ID) VALUES(2);

INSERT INTO T2(ID, T1) VALUES(1, 1);
INSERT INTO T2(ID, T1) VALUES(1, 2);
INSERT INTO T2(ID, T1) VALUES(2, 1);
INSERT INTO T2(ID, T1) VALUES(2, 2);

SELECT * FROM T2;
SELECT * FROM T1;

-- Update one-by-one

UPDATE T2 SET X=2 WHERE ID=1 AND T1=1;

SELECT * FROM T2;
SELECT * FROM T1;

UPDATE T2 SET X=3 WHERE ID=1 AND T1=2;

SELECT * FROM T2;
SELECT * FROM T1;

UPDATE T2 SET X=5 WHERE ID=2 AND T1=1;

SELECT * FROM T2;
SELECT * FROM T1;

UPDATE T2 SET X=7 WHERE ID=2 AND T1=2;

SELECT * FROM T2;
SELECT * FROM T1;

UPDATE T2 SET X=0 WHERE ID=1 AND T1=1;
UPDATE T2 SET X=0 WHERE ID=1 AND T1=2;
UPDATE T2 SET X=0 WHERE ID=2 AND T1=1;
UPDATE T2 SET X=0 WHERE ID=2 AND T1=2;

SELECT * FROM T2;
SELECT * FROM T1;

-- Mass update (1)

UPDATE T2 SET X=3 WHERE ID=1;
UPDATE T2 SET X=5 WHERE ID=2;

SELECT * FROM T2;
SELECT * FROM T1;

UPDATE T2 SET X=0;
UPDATE T1 SET X=0;

SELECT * FROM T2;
SELECT * FROM T1;

-- Mass update (2)

UPDATE T2 SET X=3 WHERE T1=1;
UPDATE T2 SET X=5 WHERE T1=2;

SELECT * FROM T2;
SELECT * FROM T1;

UPDATE T2 SET X=0;
UPDATE T1 SET X=0;

SELECT * FROM T2;
SELECT * FROM T1;

-- Mass update (3)

UPDATE T2 SET X=7;

SELECT * FROM T2;
SELECT * FROM T1;


No file was uploaded with this report