Skip site navigation (1) Skip section navigation (2)

overlapping rules can let you break referential integrity

From: "Denis de Bernardy" <denis(at)mesoconcepts(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: overlapping rules can let you break referential integrity
Date: 2006-02-10 22:02:06
Message-ID: 000001c62e8d$a28aa720$6802a8c0@kergan (view raw or flat)
Thread:
Lists: 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

Attachment: postgresql-bug.sql
Description: application/octet-stream (1.3 KB)

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-02-10 22:35:29
Subject: Re: overlapping rules can let you break referential integrity
Previous:From: Kris JurkaDate: 2006-02-10 08:15:25
Subject: Re: BUG #2250: JSTL parameterized queries inserting numeric

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group