overlapping rules can let you break referential integrity

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