On Wed, 2002-09-18 at 15:18, pgsql-bugs@postgresql.org wrote:
> 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
I believe this is the proper (or explained) results. A rule is not
executed once per row, but rather replaces the current statement (in the
case of INSTEAD), or is added beside it as a seperate execution event
(as is the case here). In fact, this occurs well before execution or
any knowledge of the table structure is in place.
This means that any given statement within a rule is executed once and
only once.
So, for mass update 2 what your really doing is:
BEGIN;
-- Statement you submitted
UPDATE T2 SET X=3 WHERE ID=1;
-- Rule statements
UPDATE T1 SET X = X - old.X WHERE ID = old.T1;
UPDATE T1 SET X = X + new.X WHERE ID = new.T1;
COMMIT;
BEGIN;
-- Statement you submitted
UPDATE T1 SET X=0;
-- Rule statements
UPDATE T1 SET X = X - old.X WHERE ID = old.T1;
UPDATE T1 SET X = X + new.X WHERE ID = new.T1;
COMMIT;
If you want execution FOR EACH ROW, you must use a trigger. The trigger
will run once per each row being affected during the execution stage,
rather than replacing the plan early on during the parsing stage.
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
Rod Taylor
