Hi, I have strange problem with rules. It looks like when multiple rows
are affected, the rule is executed only for the first row.
Is this expected behaviour? Please someone hit me with a cluebat :)
Please try the attached two tests to see what I mean.
The tests are for pgsql-7.2 if you want to try them on 7.3 after
DROP TABLE xxx add CASCADE, and remove DROP RULE xxx lines.
Here's what I see:
*** First test ***
DROP
CREATE
DROP
CREATE
ERROR: Rule or view "add_to_data" not found
CREATE
ERROR: Rule or view "del_from_data" not found
CREATE
INSERT 357883308 1
INSERT 357883309 1
INSERT 357883310 1
INSERT 357883311 1
INSERT 357883312 1
id | data_counters | expected
----+---------------+------------------------------
1 | 4 | data_counters should be == 4
(1 row)
DELETE 4
id | data_counters | expected
----+---------------+------------------------------------
1 | 3 | data_counters should be == 0 NOT 3
(1 row)
*** Second test ***
DROP
CREATE
DROP
CREATE
ERROR: Rule or view "add_to_data" not found
CREATE
ERROR: Rule or view "del_from_data" not found
CREATE
INSERT 357886505 1
INSERT 357886506 1
INSERT 357886507 1
INSERT 357886508 1
INSERT 357886509 1
id | data_counters | expected
----+---------------+------------------------------
1 | 4 | data_counters should be == 4
(1 row)
DELETE 1
DELETE 1
DELETE 1
DELETE 1
id | data_counters | expected
----+---------------+------------------------------
1 | 0 | data_counters should be == 0
(1 row)
--
Georgi Chorbadzhiyski
http://georgi.unixsol.org/
DROP TABLE counters;
CREATE TABLE counters (
id integer,
data_counters integer DEFAULT 0
);
DROP TABLE data;
CREATE TABLE data (
id integer,
counters_id integer
);
/* RULES */
DROP RULE add_to_data;
CREATE RULE add_to_data AS ON insert TO data DO
UPDATE counters SET data_counters = data_counters + 1 WHERE id = NEW.counters_id;
DROP RULE del_from_data;
CREATE RULE del_from_data AS ON delete TO data DO
UPDATE counters SET data_counters = data_counters - 1 WHERE id = OLD.counters_id;
/* TESTS */
INSERT INTO counters (id) VALUES (1);
INSERT INTO data (id,counters_id) VALUES (1,1);
INSERT INTO data (id,counters_id) VALUES (2,1);
INSERT INTO data (id,counters_id) VALUES (3,1);
INSERT INTO data (id,counters_id) VALUES (4,1);
SELECT *,'data_counters should be == 4' AS expected FROM counters;
/* ---> BUG: Only the first row will be deleted, data_counters will become 3 instead of 0 */
DELETE FROM data WHERE counters_id = 1;
SELECT *,'data_counters should be == 0 NOT 3' AS expected FROM counters;
DROP TABLE counters;
CREATE TABLE counters (
id integer,
data_counters integer DEFAULT 0
);
DROP TABLE data;
CREATE TABLE data (
id integer,
counters_id integer
);
/* RULES */
DROP RULE add_to_data;
CREATE RULE add_to_data AS ON insert TO data DO
UPDATE counters SET data_counters = data_counters + 1 WHERE id = NEW.counters_id;
DROP RULE del_from_data;
CREATE RULE del_from_data AS ON delete TO data DO
UPDATE counters SET data_counters = data_counters - 1 WHERE id = OLD.counters_id;
/* TESTS */
INSERT INTO counters (id) VALUES (1);
INSERT INTO data (id,counters_id) VALUES (1,1);
INSERT INTO data (id,counters_id) VALUES (2,1);
INSERT INTO data (id,counters_id) VALUES (3,1);
INSERT INTO data (id,counters_id) VALUES (4,1);
SELECT *,'data_counters should be == 4' AS expected FROM counters;
DELETE FROM data WHERE id = 1;
DELETE FROM data WHERE id = 2;
DELETE FROM data WHERE id = 3;
DELETE FROM data WHERE id = 4;
SELECT *,'data_counters should be == 0' AS expected FROM counters;
