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@vanderbijlfamily.com