BUG #1373: constraints, rules

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.

Responses

Browse pgsql-bugs by date

  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