create view

Hello,

I've found a way to crash PostgreSQL, using a self-refering view:

CREATE TABLE phy_tree_node (
node_id int primary key,
parent_id int references phy_tree_node(node_id),
node_name varchar(64)
);

CREATE VIEW phy_node_name AS
SELECT node_id, parent_id, CAST(node_name AS text) FROM phy_tree_node;

CREATE OR REPLACE VIEW phy_node_name AS
SELECT node_id, parent_id,
CAST(
CASE WHEN c.parent_id IS NOT NULL THEN
(SELECT node_name FROM phy_node_name AS p
WHERE p.node_id = c.parent_id)
ELSE ''
END
|| '/' || c.node_name AS text) AS node_name
FROM phy_tree_node AS c;

When I try to do any select from it, I get the following:
psql=> SELECT * FROM phy_tree_node WHERE node_id = 6;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

In my serverlog, I have the following:
LOG: server process (pid 23461) was terminated by signal 11
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing shared memory and semaphores
LOG: database system was interrupted at 2003-02-25 12:15:30 EST
LOG: checkpoint record is at 0/2C1ECAEC
LOG: redo record is at 0/2C1ECAEC; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 941978; next oid: 320082
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/2C1ECB2C
LOG: ReadRecord: record with zero length at 0/2C215334
LOG: redo done at 0/2C215310
FATAL: The database system is starting up
LOG: database system is ready

(Note: I like the "FATAL" error: 'The database system is starting up').

And, no, i'm not suprised that what I was trying to do caused an error.
If anyone cares, my phy_tree_node is a tree structure, and I'm trying to
figure a way that I can have make a report in crystal reports that displays
the full path of the document.

Thanks!

Ryan

--
Ryan VanderBijl rvbijl(at)vanderbijlfamily(dot)com