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
postgresql-bug.sql
Description: Binary data
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
