BUG #1373: constraints, rules

Поиск
Список
Период
Сортировка
От Bazsi
Тема BUG #1373: constraints, rules
Дата
Msg-id 200501051138.j05BcvUT014213@developer.pgadmin.org
обсуждение исходный текст
Ответы Re: BUG #1373: constraints, rules  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: BUG #1373: constraints, rules  (Michael Fuhr <mike@fuhr.org>)
Список pgsql-bugs
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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Bug in 8.0.0rc3 query planner: constant column in view changes execution plan
Следующее
От: Wangliang
Дата:
Сообщение: Fwd: How to intall postgresql-docs-7.4.6 on win2000?