overlapping rules can let you break referential integrity
От | Denis de Bernardy |
---|---|
Тема | overlapping rules can let you break referential integrity |
Дата | |
Msg-id | 000001c62e8d$a28aa720$6802a8c0@kergan обсуждение исходный текст |
Ответы |
Re: overlapping rules can let you break referential integrity
|
Список | pgsql-bugs |
Step by step how to reproduce: -- nodes CREATE TABLE nodes ( node_id serial, CONSTRAINT nodes_pkey PRIMARY KEY (node_id) ) WITHOUT OIDS; -- domains CREATE TABLE domains ( domain_id int NOT NULL, domain_is_public bool NOT NULL default false, CONSTRAINT domains_pkey PRIMARY KEY (domain_id), CONSTRAINT domains_domain_id_fkey FOREIGN KEY (domain_id) REFERENCES nodes (node_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) WITHOUT OIDS; -- drop_domain: drop the node and rely on the delete cascade CREATE OR REPLACE RULE drop_domain AS ON DELETE TO domains DO INSTEAD DELETE FROM nodes WHERE node_id = OLD.domain_id; -- public_domain_delete_protect: add delete protection CREATE OR REPLACE RULE public_domain_delete_protect AS ON DELETE TO domains WHERE domain_is_public = true DO INSTEAD NOTHING; -- version check select version(); -- 8.1.1 on i686-pc-mingw32 yada yada (standard binary on WinXP SP2) -- create a node insert into nodes default values; -- 1 row affected, normal -- create a domain insert into domains (domain_id, domain_is_public) values (currval('nodes_node_id_seq'), true); -- 1 row affected, normal -- delete the domain delete from domains; -- 1 row affected, not normal -- 0 expected because of public_domain is write protected -- lookup nodes select * from nodes; -- 0 rows, normal since the write protection didn't work -- lookup domaisn select * from domains; -- 1 row -- ouch! this piece of data is now corrupt I'm not familiar with the pgsql internals, but it looks as if: 1. delete on domains 2. rewritten as delete on nodes via drop_domain 3. triggers cascade delete on domains via foreign key 4. rewritten as do nothing <-- missing integrity check and/or rollback here via public_domain_delete_protect (things work fine without this step) Best, Denis
В списке pgsql-bugs по дате отправления: