The following bug has been logged online:
Bug reference: 1373
Logged by: Bazsi
Email address: bazsi@jonapot.hu
PostgreSQL version: 7.2.2
Operating system: debian linux
Description: constraints, rules
Details:
I have the following four table:
CREATE TABLE folder
(
folderid SERIAL,
name TEXT NOT NULL,
parent INT4 NOT NULL,
PRIMARY KEY (folderid),
CONSTRAINT parent_ref
FOREIGN KEY (parent)
REFERENCES folder
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO folder(name,parent) VALUES('root',1);
CREATE RULE folder_root_cant_delete AS ON DELETE
TO folder WHERE OLD.folderid=1
DO INSTEAD NOTHING;
----
CREATE TABLE item
(
itemid SERIAL,
name TEXT NOT NULL,
parent INT4 NOT NULL,
PRIMARY KEY (itemid)
CONSTRAINT folder_ref
FOREIGN KEY (parent)
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO item(name,parent) VALUES('default',1);
CREATE RULE item_default_cant_delete AS ON DELETE
TO item WHERE OLD.itemid=1
DO INSTEAD NOTHING;
--
CREATE TABLE menu
(
menuid SERIAL,
name TEXT NOT NULL,
folderref INT4 DEFAULT 1,
itemref INT4 DEFAULT 1,
parent INT4 NOT NULL,
PRIMARY KEY (menuid),
CONSTRAINT m_folder_ref
FOREIGN KEY (folderref)
REFERENCES folder
ON DELETE SET DEFAULT
ON UPDATE CASCADE,
CONSTRAINT m_item_ref
FOREIGN KEY (itemref)
REFERENCES item
ON DELETE SET DEFAULT
ON UPDATE CASCADE,
CONSTRAINT m_parent_ref
FOREIGN KEY (parent)
REFERENCES menu
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO menu(name,parent) VALUES('root',1);
CREATE RULE menu_root_cant_delete AS ON DELETE
TO menu WHERE OLD.menuid=1
DO INSTEAD NOTHING;
----
CREATE TABLE version
(
versionid SERIAL,
menuref INT4 NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (versionid),
CONSTRAINT menu_ref
FOREIGN KEY (menuref)
REFERENCES menu
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE RULE menu_update AS ON UPDATE
TO menu
DO INSERT INTO version(name,menuref) VALUES(OLD.name,OLD.menuid);
I insert a record into the "folder" table:
INSERT INTO folder(name,parent) VALUES('teszt',1);
If i want to delete this record from the folder table, then the server
terminate abnormally (sigterm).
If i not use the menu_update rule, then everything is OK.