The following bug has been logged on the website:
Bug reference: 11804
Logged by: Justin Lu
Email address: djlu126@126.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
?
