BUG #11804: The delete rule problem

From: djlu126(at)126(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11804: The delete rule problem
Date: 2014-10-27 06:29:43
Message-ID: 20141027062943.2615.76034@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 11804
Logged by: Justin Lu
Email address: djlu126(at)126(dot)com
PostgreSQL version: 9.3.5
Operating system: Windows 7 Ultimate x86
Description:

The situation as flowing:

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2014-10-27 15:32:59 Re: ltree::text not immutable?
Previous Message Jim Nasby 2014-10-27 04:12:10 Re: [HACKERS] ltree::text not immutable?