Обсуждение: BUG #1373: constraints, rules

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.

"Bazsi" <bazsi@jonapot.hu> writes:
> PostgreSQL version: 7.2.2
> ...
> If i want to delete this record from the folder table, then the server
> terminate abnormally (sigterm).

I tried to reproduce this in current versions (7.2.6, 7.3.7, etc)
and could not.  So either it was fixed long ago, or you weren't
sufficiently clear about exactly how to trigger the crash.  What
I substituted for the above handwaving was

SELECT * FROM folder;

UPDATE folder SET name = 'z' where name = 'teszt';

SELECT * FROM folder;

DELETE FROM folder WHERE name = 'z';

SELECT * FROM folder;

DELETE FROM folder;

SELECT * FROM folder;


            regards, tom lane

On Wed, Jan 05, 2005 at 11:38:57AM +0000, Bazsi wrote:

> PostgreSQL version: 7.2.2

That version of PostgreSQL is pretty old.  Trying a recent version
before reporting a bug is a good idea, as the bug might already
have been fixed.

> I have the following four table:

The SQL statements you posted fail to load in 7.4.6 and 8.0.0rc4
due to syntax errors.  Are you sure they're the *exact* statements
you're using?

> 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).

Simply wanting to delete the record causes the server to terminate?
Wow, I wasn't aware that PostgreSQL was psychic :-)

I assume you mean that deleting the record causes the server to
terminate.  Are you sure the signal was SIGTERM?  How did you
determine that?  What showed up in the server's logs?

Since your SQL statements wouldn't load I had to guess at what you
meant to do, which now makes further analysis dubious.  After fixing
the syntax errors and running the statements, I executed the following:

DELETE FROM folder WHERE name = 'teszt';

This statement ran without error in 8.0.0rc4 and 7.4.6.  However,
since I modified your SQL to make it load, we can't be sure that I
duplicated your test case.  Please make sure the SQL statements you
post are correct and complete.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sun, Jan 09, 2005 at 10:18:47AM -0700, Michael Fuhr wrote:

> This statement ran without error in 8.0.0rc4 and 7.4.6.

I couldn't duplicate the problem in 7.2.2 either.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/