General Bug Report: pg_dump produces erroneous output

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Dave Bodenstab
Your email address : imdave(at)mcs(dot)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:
---------

--------------------------------------------------------------------------