Bug #778: pg_dump crashes when dumping a view - Mailing list pgsql-bugs

Michael Bravo (mbravo@tag-ltd.spb.ru) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
pg_dump crashes when dumping a view

Long Description
pg_dump crashes when trying to dump a certain database structure.
plaform: Linux, Debian 3.0 (stable)
PostgreSQL version: 7.2.1-3 (Debian package version, that is, 3rd build)

steps to reproduce:
create an empty database foo
populate it with a db structure listed below in example code
execute 'pg_dump -v foo'
the following output appears:

pg_dump: saving database definition
pg_dump: last built-in oid is 16554
pg_dump: reading user-defined types
pg_dump: reading user-defined functions
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined tables
pg_dump: finding triggers for table element
pg_dump: finding triggers for table variant
pg_dump: finding triggers for table unit
pg_dump: finding triggers for table cost_sheet
pg_dump: finding triggers for table assembly
pg_dump: finding triggers for table assembly_body
pg_dump: finding triggers for table parent
pg_dump: finding triggers for table parent_assembly
pg_dump: finding triggers for table parent_element
pg_dump: query to obtain definition of view "parent_body_view" failed: ERROR:  phony_equal: unexpected node type 721
pg_dump: *** aborted because of error

Please do copy your messages about this bug to my e-mail - I am not currently subscribed to pgsql-bugs.

Thanks ever so much in advance


Sample Code
--
-- Some elements
--

CREATE TABLE element (
  id SERIAL,                            -- unique ID
  name text NOT NULL UNIQUE,            -- Name of Element
  deleted bool NOT NULL DEFAULT FALSE,  -- Deleted flag

  PRIMARY KEY ( id )
);


--
-- Some variants of Elements
--

CREATE TABLE variant (
  id SERIAL,                            -- unique ID
  name text NOT NULL UNIQUE,            -- Name of Variant
  deleted bool NOT NULL DEFAULT FALSE,  -- Deleted flag

  PRIMARY KEY ( id )
);
--
-- Some measure units
--

CREATE TABLE unit (
  id SERIAL,                            -- unique ID
  name text NOT NULL UNIQUE,            -- Name of Unit
  deleted bool NOT NULL DEFAULT FALSE,  -- Deleted flag

  PRIMARY KEY ( id )
);


--
-- Cost sheet \ price list
--
CREATE TABLE cost_sheet (
  id SERIAL,                                    -- unique ID
  element_id int4 NOT NULL,                     -- Element ref.
  variant_id int4 NOT NULL,                     -- Variant ref.
  unit_id int4 NOT NULL,                        -- Unit ref.
  cost numeric(15,2) NOT NULL DEFAULT '0',      -- Cost of 1 Unit of Element in
Variant
  deleted bool NOT NULL DEFAULT FALSE,          -- Deleted flag

  UNIQUE ( element_id, unit_id, variant_id ),
  PRIMARY KEY ( id ),
  FOREIGN KEY ( element_id ) REFERENCES element ( id ),
  FOREIGN KEY ( variant_id ) REFERENCES variant ( id ),
  FOREIGN KEY ( unit_id ) REFERENCES unit ( id )
);

--
-- Assembly of Elements ( header )
--

CREATE TABLE assembly (
  id SERIAL,
  name text NOT NULL UNIQUE,
  deleted bool NOT NULL DEFAULT FALSE,

  PRIMARY KEY ( id )
);


--
-- Body of assembly
--

CREATE TABLE assembly_body (
  id SERIAL,
  assembly_id int4 NOT NULL,
  cs_element_id int4 NOT NULL,
  amount numeric(15,4) NOT NULL,

  PRIMARY KEY ( id ),
  FOREIGN KEY ( assembly_id ) REFERENCES assembly ( id ),
  FOREIGN KEY ( cs_element_id ) REFERENCES cost_sheet ( id )
);

--
-- Main relation - Parent (header)
--

CREATE TABLE parent (
  id SERIAL,                                    -- unique ID
  name text NOT NULL UNIQUE,                    -- Name of Parent
  deleted bool NOT NULL DEFAULT FALSE,          -- Deleted flag

  PRIMARY KEY ( id )
);


--
-- Sequence  - generate ID for body of Parent.
-- Its body contents assemblies and elements.
--

CREATE SEQUENCE position_id_seq;

--
-- Assemblies - part of parent body with names of Assembly
--

CREATE TABLE parent_assembly (
  pos_id int4 NOT NULL DEFAULT nextval('position_id_seq'),      -- unique ID
  parent_id int4 NOT NULL,                                      -- Parent ref.
  name text NOT NULL,                                           -- Name of Assem
bly (just copied here)
  amount numeric(15,4) NOT NULL DEFAULT '1',                    -- Amount

  PRIMARY KEY ( pos_id ),
  FOREIGN KEY ( parent_id ) REFERENCES parent ( id ) ON DELETE CASCADE
);

--
-- Elements - Part of parent body only with elements. Some of them maybe attache
d to header's assembly
--

CREATE TABLE parent_element (
  pos_id int4 NOT NULL DEFAULT nextval('position_id_seq'),      -- unique ID
  parent_id int4 NOT NULL,                                      -- Parent ref.
  cs_element_id int4 NOT NULL,                                  -- Cost sheet re
f.
  parent_assembly_id int4,                                      -- Parent Assemb
ly ref.
  amount numeric(15,4) NOT NULL DEFAULT '0',                    -- Amount
  extra_charge_percent numeric(4,2) NOT NULL DEFAULT '0',       -- Extra charge
in %

  PRIMARY KEY ( pos_id ),
  FOREIGN KEY ( parent_id ) REFERENCES parent ( id ) ON DELETE CASCADE,
  FOREIGN KEY ( cs_element_id ) REFERENCES cost_sheet ( id )
);


CREATE VIEW parent_body_view AS
SELECT
    j.pos_id,
    j.parent_id,
    j.name as assembly_name,
    (SELECT element_id FROM cost_sheet WHERE id = j.cs_element_id) as element_id
,
    (SELECT name FROM element WHERE id =
        (SELECT element_id FROM cost_sheet WHERE id = j.cs_element_id)) as eleme
nt_name,
    (SELECT name FROM unit WHERE id =
        (SELECT unit_id FROM cost_sheet WHERE id = j.cs_element_id)) as element_
unit,
    j.cs_element_id,
    j.amount,
    (SELECT variant_id FROM cost_sheet WHERE id = j.cs_element_id) as variant_id
,
    j.extra_charge_percent,
    (SELECT cost FROM cost_sheet WHERE id = j.cs_element_id) as cost,
    CASE
        WHEN j.parent_assembly_id IS NULL
        THEN j.pos_id
        ELSE j.parent_assembly_id
    END AS p_id,
    CASE
        WHEN j.parent_assembly_id IS NULL
        THEN 'assembly'
        ELSE 'element'
    END AS type
FROM
    (parent_assembly NATURAL FULL JOIN parent_element) j
;



No file was uploaded with this report