Thread
-
General Bug Report: pg_dump produces erroneous output
Unprivileged user <nobody> — 1999-02-27T23:03:17Z
============================================================================ POSTGRESQL BUG REPORT TEMPLATE ============================================================================ Your name : Dave Bodenstab Your email address : imdave@mcs.net Category : unknown Severity : serious Summary: pg_dump produces erroneous output System Configuration -------------------- Operating System : FreeBSD PostgreSQL version : 6.4.2 Compiler used : gcc Hardware: --------- shouldn't matter... see problem description Versions of other tools: ------------------------ pg_dump problem -------------------------------------------------------------------------- Problem Description: -------------------- pg_dump cannot be used to restore a view sometimes. Apparently, some qualification is missing as I get the error message: ERROR: Column city is ambiguous BTW, I tried emailing this and I got a `not a member, bounced' message. Kind of hard to report bugs when one has to be a `member' to do so. Also, clicking on `bugs-list' above gives a `no such page' message -------------------------------------------------------------------------- Test Case: ---------- I'm learning sql, and I've created my textbook supplier/parts DB. A view was produced by: create view city_pairs as select distinct s.city as scity, p.city as pcity from s, sp, p where s.sno = sp.sno and sp.pno = p.pno; I then used pg_dump to backup the DB. When trying to restore the DB, I got a failure for the city_pairs view. I've attached the output from pg_dump, and the results of the attempted restore. ------ pg_dump output --------- $ pg_dump -f pg_dump.output -d supplierparts $ cat pg_dump.output CREATE TABLE "s" ( "sno" character(5) NOT NULL, "sname" character(20), "status" int4, "city" character(15)); CREATE TABLE "p" ( "pno" character(6) NOT NULL, "pname" character(20), "color" character(6), "weight" int4, "city" character(15)); CREATE TABLE "sp" ( "sno" character(5) NOT NULL, "pno" character(6) NOT NULL, "qty" int4); CREATE TABLE "ls" ( "sno" character(5), "sname" character(20), "status" int4); CREATE TABLE "pq" ( "pno" character(6), "totq" int4); CREATE TABLE "city_pairs" ( "scity" character(15), "pcity" character(15)); INSERT INTO "s" values ('S1 ','Smith ',20,'London '); INSERT INTO "s" values ('S2 ','Jones ',10,'Paris '); INSERT INTO "s" values ('S3 ','Blake ',30,'Paris '); INSERT INTO "s" values ('S4 ','Clark ',20,'London '); INSERT INTO "s" values ('S5 ','Adams ',30,'Athens '); INSERT INTO "p" values ('P1 ','Nut ','Red ',12,'London '); INSERT INTO "p" values ('P2 ','Bolt ','Green ',17,'Paris '); INSERT INTO "p" values ('P3 ','Screw ','Blue ',17,'Rome '); INSERT INTO "p" values ('P4 ','Screw ','Red ',14,'London '); INSERT INTO "p" values ('P5 ','Cam ','Blue ',12,'Paris '); INSERT INTO "p" values ('P6 ','Cog ','Red ',19,'London '); INSERT INTO "sp" values ('S1 ','P1 ',300); INSERT INTO "sp" values ('S1 ','P2 ',200); INSERT INTO "sp" values ('S1 ','P3 ',400); INSERT INTO "sp" values ('S1 ','P4 ',200); INSERT INTO "sp" values ('S1 ','P5 ',100); INSERT INTO "sp" values ('S1 ','P6 ',100); INSERT INTO "sp" values ('S2 ','P1 ',300); INSERT INTO "sp" values ('S2 ','P2 ',400); INSERT INTO "sp" values ('S3 ','P2 ',200); INSERT INTO "sp" values ('S4 ','P2 ',200); INSERT INTO "sp" values ('S4 ','P4 ',300); INSERT INTO "sp" values ('S4 ','P5 ',400); CREATE UNIQUE INDEX "s_pkey" on "s" using btree ( "sno" "bpchar_ops" ); CREATE UNIQUE INDEX "p_pkey" on "p" using btree ( "pno" "bpchar_ops" ); CREATE UNIQUE INDEX "sp_pkey" on "sp" using btree ( "sno" "bpchar_ops", "pno" "bpchar_ops" ); CREATE RULE "_RETls" AS ON SELECT TO "ls" DO INSTEAD SELECT "sno", "sname", "status" FROM "s" WHE RE "city" = 'London'::"bpchar"; CREATE RULE "_RETpq" AS ON SELECT TO "pq" DO INSTEAD SELECT "pno", "sum"("qty") AS "totq" FROM "s p" GROUP BY "pno"; CREATE RULE "_RETcity_pairs" AS ON SELECT TO "city_pairs" DO INSTEAD SELECT "city" AS "scity", "c ity" AS "pcity" FROM "s", "sp", "p" WHERE ("sno" = "sno") AND ("pno" = "pno"); Here is the attempt to restore: $ psql supplierparts Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: supplierparts supplierparts=> \d Couldn't find any tables, sequences or indices! supplierparts=> \i pg_dump.output CREATE TABLE "s" ( "sno" character(5) NOT NULL, "sname" character(20), "status" int4, "city" character(15)); CREATE CREATE TABLE "p" ( "pno" character(6) NOT NULL, "pname" character(20), "color" character(6), "weight" int4, "city" character(15)); CREATE CREATE TABLE "sp" ( "sno" character(5) NOT NULL, "pno" character(6) NOT NULL, "qty" int4); CREATE CREATE TABLE "ls" ( "sno" character(5), "sname" character(20), "status" int4); CREATE CREATE TABLE "pq" ( "pno" character(6), "totq" int4); CREATE CREATE TABLE "city_pairs" ( "scity" character(15), "pcity" character(15)); CREATE INSERT INTO "s" values ('S1 ','Smith ',20,'London '); INSERT 146755 1 INSERT INTO "s" values ('S2 ','Jones ',10,'Paris '); INSERT 146756 1 INSERT INTO "s" values ('S3 ','Blake ',30,'Paris '); INSERT 146757 1 INSERT INTO "s" values ('S4 ','Clark ',20,'London '); INSERT 146758 1 INSERT INTO "s" values ('S5 ','Adams ',30,'Athens '); INSERT 146759 1 INSERT INTO "p" values ('P1 ','Nut ','Red ',12,'London '); INSERT 146760 1 INSERT INTO "p" values ('P2 ','Bolt ','Green ',17,'Paris '); INSERT 146761 1 INSERT INTO "p" values ('P3 ','Screw ','Blue ',17,'Rome '); INSERT 146762 1 INSERT INTO "p" values ('P4 ','Screw ','Red ',14,'London '); INSERT 146763 1 INSERT INTO "p" values ('P5 ','Cam ','Blue ',12,'Paris '); INSERT 146764 1 INSERT INTO "p" values ('P6 ','Cog ','Red ',19,'London '); INSERT 146765 1 INSERT INTO "sp" values ('S1 ','P1 ',300); INSERT 146766 1 INSERT INTO "sp" values ('S1 ','P2 ',200); INSERT 146767 1 INSERT INTO "sp" values ('S1 ','P3 ',400); INSERT 146768 1 INSERT INTO "sp" values ('S1 ','P4 ',200); INSERT 146769 1 INSERT INTO "sp" values ('S1 ','P5 ',100); INSERT 146770 1 INSERT INTO "sp" values ('S1 ','P6 ',100); INSERT 146771 1 INSERT INTO "sp" values ('S2 ','P1 ',300); INSERT 146772 1 INSERT INTO "sp" values ('S2 ','P2 ',400); INSERT 146773 1 INSERT INTO "sp" values ('S3 ','P2 ',200); INSERT 146774 1 INSERT INTO "sp" values ('S4 ','P2 ',200); INSERT 146775 1 INSERT INTO "sp" values ('S4 ','P4 ',300); INSERT 146776 1 INSERT INTO "sp" values ('S4 ','P5 ',400); INSERT 146777 1 CREATE UNIQUE INDEX "s_pkey" on "s" using btree ( "sno" "bpchar_ops" ); CREATE CREATE UNIQUE INDEX "p_pkey" on "p" using btree ( "pno" "bpchar_ops" ); CREATE CREATE UNIQUE INDEX "sp_pkey" on "sp" using btree ( "sno" "bpchar_ops", "pno" "bpchar_ops" ); CREATE CREATE RULE "_RETls" AS ON SELECT TO "ls" DO INSTEAD SELECT "sno", "sname", "status" FROM "s" WHE RE "city" = 'London'::"bpchar"; CREATE CREATE RULE "_RETpq" AS ON SELECT TO "pq" DO INSTEAD SELECT "pno", "sum"("qty") AS "totq" FROM "s p" GROUP BY "pno"; CREATE CREATE RULE "_RETcity_pairs" AS ON SELECT TO "city_pairs" DO INSTEAD SELECT "city" AS "scity", "c ity" AS "pcity" FROM "s", "sp", "p" WHERE ("sno" = "sno") AND ("pno" = "pno"); ERROR: Column city is ambiguous EOF supplierparts=> \d Database = supplierparts +------------------+----------------------------------+----------+ | Owner | Relation | Type | +------------------+----------------------------------+----------+ | imdave | city_pairs | table | | imdave | ls | view? | | imdave | p | table | | imdave | p_pkey | index | | imdave | pq | view? | | imdave | s | table | | imdave | s_pkey | index | | imdave | sp | table | | imdave | sp_pkey | index | +------------------+----------------------------------+----------+ -------------------------------------------------------------------------- Solution: --------- --------------------------------------------------------------------------