[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    [BUGS] BUG #11821: The delete rule problem
From:       djlu126 () 126 ! com
Date:       2014-10-29 9:27:27
Message-ID: 20141029092727.2613.83924 () wrigleys ! postgresql ! org
[Download RAW message or body]

The following bug has been logged on the website:

Bug reference:      11821
Logged by:          Justin Lu
Email address:      djlu126@126.com
PostgreSQL version: 9.3.5
Operating system:   Windows 7 Ultimate
Description:        

The situation as following:

CREATE TABLE test
(
  test_id integer NOT NULL,
  CONSTRAINT test_pkey PRIMARY KEY (test_id)
);

CREATE TABLE details
(
  test_id integer NOT NULL,
  prd_id integer NOT NULL,
  quantity integer,
  CONSTRAINT details_pkey PRIMARY KEY (test_id, prd_id)
);

CREATE TABLE stock
(
  prd_id integer NOT NULL,
  remaining integer,
  CONSTRAINT stock_pkey PRIMARY KEY (prd_id)
);

CREATE OR REPLACE RULE test_d1 AS
    ON DELETE TO test DO  DELETE FROM details
  WHERE details.test_id = old.test_id;

CREATE OR REPLACE RULE details_d1 AS
    ON DELETE TO details
   WHERE (EXISTS ( SELECT stock_1.prd_id
           FROM stock stock_1
          WHERE stock_1.prd_id = old.prd_id)) DO  UPDATE stock SET remaining
= stock.remaining - old.quantity
  WHERE stock.prd_id = old.prd_id;

CREATE OR REPLACE RULE details_d2 AS
    ON DELETE TO details
   WHERE NOT (EXISTS ( SELECT stock_1.prd_id
           FROM stock stock_1
          WHERE stock_1.prd_id = old.prd_id)) DO  INSERT INTO stock (prd_id,
remaining)
  VALUES (old.prd_id, - old.quantity);

COPY stock (prd_id, remaining) FROM stdin;
1	9
2	9
3	9
4	9
5	9
6	9
7	9
8	9
9	9
\.

COPY test (test_id) FROM stdin;
1
2
3
4
5
6
7
8
9
\.

COPY details (test_id, prd_id, quantity) FROM stdin;
1	1	1
1	2	1
1	3	1
1	4	1
1	5	1
1	6	1
1	7	1
1	8	1
1	9	1
2	1	1
2	2	1
2	3	1
2	4	1
2	5	1
2	6	1
2	7	1
2	8	1
2	9	1
3	1	1
3	2	1
3	3	1
3	4	1
3	5	1
3	6	1
3	7	1
3	8	1
3	9	1
4	1	1
4	2	1
4	3	1
4	4	1
4	5	1
4	6	1
4	7	1
4	8	1
4	9	1
5	1	1
5	2	1
5	3	1
5	4	1
5	5	1
5	6	1
5	7	1
5	8	1
5	9	1
6	1	1
6	2	1
6	3	1
6	4	1
6	5	1
6	6	1
6	7	1
6	8	1
6	9	1
7	1	1
7	2	1
7	3	1
7	4	1
7	5	1
7	6	1
7	7	1
7	8	1
7	9	1
8	1	1
8	2	1
8	3	1
8	4	1
8	5	1
8	6	1
8	7	1
8	8	1
8	9	1
9	1	1
9	2	1
9	3	1
9	4	1
9	5	1
9	6	1
9	7	1
9	8	1
9	9	1
\.

Then I execute the command:

delete from test where test_id in(1,2);
select * from stock;

The result is:

 prd_id | remaining
--------+-----------
      1 |         8
      2 |         8
      3 |         8
      4 |         8
      5 |         8
      6 |         8
      7 |         8
      8 |         8
      9 |         8

Shouldn't it be:

 prd_id | remaining
--------+-----------
      1 |         7
      2 |         7
      3 |         7
      4 |         7
      5 |         7
      6 |         7
      7 |         7
      8 |         7
      9 |         7
?



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[prev in list] [next in list] [prev in thread] [next in thread]