Skip site navigation (1) Skip section navigation (2)

General Bug Report: pg_dump produces erroneous output

From: Unprivileged user <nobody>
To: pgsql-bugs(at)postgresql(dot)org
Subject: General Bug Report: pg_dump produces erroneous output
Date: 1999-02-27 23:03:17
Message-ID: 199902272303.SAA90101@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
============================================================================
                        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:
---------


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


pgsql-bugs by date

Next:From: John SummerfieldDate: 1999-03-01 05:02:35
Subject: Problem storing dates with JDBC
Previous:From: Dave BodenstabDate: 1999-02-27 22:33:30
Subject: pg_dump problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group