Bug #778: pg_dump crashes when dumping a view

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