From: | "Bazsi" <bazsi(at)jonapot(dot)hu> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #1373: constraints, rules |
Date: | 2005-01-05 11:38:57 |
Message-ID: | 200501051138.j05BcvUT014213@developer.pgadmin.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 1373
Logged by: Bazsi
Email address: bazsi(at)jonapot(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2005-01-05 12:47:18 | Re: [BUGS] More SSL questions.. |
Previous Message | Magnus Hagander | 2005-01-05 08:59:50 | Re: BUG #1372: Service won't start with tcpip_socket = true |