pg_dumping a table having a primary key yields commands like

--
-- TOC Entry ID 2 (OID 139812)
--
-- Name: table1 Type: TABLE Owner: postgres
--

CREATE TABLE "table1" (
	"column10" character varying(255) NOT NULL,
	"column1" character varying(255) NOT NULL,
	"column2" smallint NOT NULL,
	"column6" numeric,
	"column7" "char",
	Constraint "table1_pkey" Primary Key ("column10", "column1", "column2")
);

[snip]

--
-- TOC Entry ID 5 (OID 139817)
--
-- Name: "table1_pkey" Type: CONSTRAINT Owner: postgres
--

Alter Table "table1" Add Constraint "table1_pkey" Primary Key ("column10", "column1", "column2");


which on execution quite properly complains about duplicate primary
keys.

I assume this is traceable to this patch:

2002-03-06 15:48  momjian

	* src/bin/pg_dump/pg_dump.c: Enable ALTER TABLE ADD PRIMARY KEY for
	pg_dump, for performance reasons so index is not on table during
	COPY.
	
	> > AFAICT, the patch I posted to -patches a little while to enable
	the
	> > usage of ALTER TABLE ADD PRIMARY KEY by pg_dump hasn't been
	applied, nor
	> > is it in the unapplied patches list. I was under the impression
	that
	> > this was in the queue for application -- did it just get lost?
	
	Neil Conway <neilconway@rogers.com>


It would seem that more thought is needed here.

			regards, tom lane

Fri, 12 Apr 2002 13:28:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
pg_dump is broken in CVS tip
On Fri, 12 Apr 2002 13:28:34 -0400
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> pg_dumping a table having a primary key yields commands like
> 
> --
> -- TOC Entry ID 2 (OID 139812)
> --
> -- Name: table1 Type: TABLE Owner: postgres
> --
> 
> CREATE TABLE "table1" (
> 	"column10" character varying(255) NOT NULL,
> 	"column1" character varying(255) NOT NULL,
> 	"column2" smallint NOT NULL,
> 	"column6" numeric,
> 	"column7" "char",
> 	Constraint "table1_pkey" Primary Key ("column10", "column1", "column2")
> );
> 
> [snip]
> 
> --
> -- TOC Entry ID 5 (OID 139817)
> --
> -- Name: "table1_pkey" Type: CONSTRAINT Owner: postgres
> --
> 
> Alter Table "table1" Add Constraint "table1_pkey" Primary Key ("column10", "column1", "column2");
> 
> which on execution quite properly complains about duplicate primary
> keys.

Thanks for finding this Tom -- my apologies, this is likely my bug.

However, when I created a table using the commands above and then
dumped it again, I got a dump that worked properly: there was no
Constraint within the table definition itself, just an ALTER
TABLE at the end of the dump to add the PK (i.e. the patch worked
as intended and the table could be restored properly).

If you can give me a reproduceable test-case, I'll fix the bug.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Fri, 12 Apr 2002 18:44:17 -0400
Neil Conway <neilconway@rogers.com>
Re: pg_dump is broken in CVS tip
Neil Conway <neilconway@rogers.com> writes:
> However, when I created a table using the commands above and then
> dumped it again, I got a dump that worked properly:
> ...
> If you can give me a reproduceable test-case, I'll fix the bug.

Sigh ... I should take my own advice about checking that I've described
a problem completely :-(.  It looks like you also need a foreign-key
reference to the table.  This will generate the problem:

	create table t1 (f1 int primary key);

	create table t2 (f1 int references t1);

The dump of t1 will now read

CREATE TABLE "t1" (
        "f1" integer NOT NULL,
        Constraint "t1_pkey" Primary Key ("f1")
);

Sorry for the inadequate report.

			regards, tom lane

Fri, 12 Apr 2002 18:51:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump is broken in CVS tip 
I said:
> This will generate the problem:

> 	create table t1 (f1 int primary key);

> 	create table t2 (f1 int references t1);

Actually, I find that I get the double declaration of t1_pkey even
without t2.  Either we're not using quite the same sources, or the
problem is platform-dependent.  I can dig into it if you cannot
reproduce it ...

			regards, tom lane

Fri, 12 Apr 2002 18:59:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump is broken in CVS tip 
On Fri, 12 Apr 2002 18:59:33 -0400
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> I said:
> > This will generate the problem:
> 
> > 	create table t1 (f1 int primary key);
> 
> > 	create table t2 (f1 int references t1);
> 
> Actually, I find that I get the double declaration of t1_pkey even
> without t2.  Either we're not using quite the same sources, or the
> problem is platform-dependent.  I can dig into it if you cannot
> reproduce it ...

Curious -- I was previously using ~1 week old sources, and I was
unable to reproduce the problem (using either the original
test-case or the one provided above: neither has any problems).
When I built the current CVS code, both test-case exhibits the
problem quite obviously. Therefore, it seems that the problem
has been introduced recently.

I'll investigate...

Cheers,

Neil

P.S. Tom, would you mind adding my IP to your spam whitelist?
Your spam-blocking software rejects my emails.

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Fri, 12 Apr 2002 19:24:21 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: pg_dump is broken in CVS tip
On Fri, 12 Apr 2002 19:24:21 -0400
"Neil Conway" <nconway@klamath.dyndns.org> wrote:
> When I built the current CVS code, both test-case exhibits the
> problem quite obviously. Therefore, it seems that the problem
> has been introduced recently.

The problem was introduced here:

------------------
revision 1.246
date: 2002/04/05 11:51:12;  author: momjian;  state: Exp;  lines: +129 -3
Adds domain dumping support to pg_dump.

Rod Taylor
------------------

Rod's patch does what it is supposed to do, but it also includes
some old code to add PK constraints to CREATE TABLE. That stuff
had been removed as part of my original patch for pg_dump a
little while ago.

The attached patch fixes this by removing (again :-) ) the
code in dumpTables() to perform PK creation during CREATE
TABLE. I briefly tested it locally and it fixes both of
Tom's test cases.

Please apply.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
Fri, 12 Apr 2002 21:28:48 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: pg_dump is broken in CVS tip

I will apply shortly because pg_dump is broken.  I will give it 8 hours.

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

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


Neil Conway wrote:
> On Fri, 12 Apr 2002 19:24:21 -0400
> "Neil Conway" <nconway@klamath.dyndns.org> wrote:
> > When I built the current CVS code, both test-case exhibits the
> > problem quite obviously. Therefore, it seems that the problem
> > has been introduced recently.
> 
> The problem was introduced here:
> 
> ------------------
> revision 1.246
> date: 2002/04/05 11:51:12;  author: momjian;  state: Exp;  lines: +129 -3
> Adds domain dumping support to pg_dump.
> 
> Rod Taylor
> ------------------
> 
> Rod's patch does what it is supposed to do, but it also includes
> some old code to add PK constraints to CREATE TABLE. That stuff
> had been removed as part of my original patch for pg_dump a
> little while ago.
> 
> The attached patch fixes this by removing (again :-) ) the
> code in dumpTables() to perform PK creation during CREATE
> TABLE. I briefly tested it locally and it fixes both of
> Tom's test cases.
> 
> Please apply.
> 
> Cheers,
> 
> Neil
> 
> -- 
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 12 Apr 2002 21:40:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump is broken in CVS tip
Neil Conway <nconway@klamath.dyndns.org> writes:
> Curious -- I was previously using ~1 week old sources, and I was
> unable to reproduce the problem (using either the original
> test-case or the one provided above: neither has any problems).
> When I built the current CVS code, both test-case exhibits the
> problem quite obviously. Therefore, it seems that the problem
> has been introduced recently.

[ scratches head ... ]  I think most of the major recent changes
have been schema related, so this is quite likely my fault.  Let
me know if you need help debugging it.

> P.S. Tom, would you mind adding my IP to your spam whitelist?
> Your spam-blocking software rejects my emails.

24.102.202.* whitelisted; let me know if that's not the correct
IP range for you.  (Although you might think I'm blocking most
of the net, I still get a depressingly large amount of spam ---
and that's not even counting the Klez virus that seems to be
deliberately targeting my jpeg-info alter ego ... grumble ...)

			regards, tom lane

Fri, 12 Apr 2002 23:35:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump is broken in CVS tip 
Tom Lane wrote:
> Neil Conway <nconway@klamath.dyndns.org> writes:
> > Curious -- I was previously using ~1 week old sources, and I was
> > unable to reproduce the problem (using either the original
> > test-case or the one provided above: neither has any problems).
> > When I built the current CVS code, both test-case exhibits the
> > problem quite obviously. Therefore, it seems that the problem
> > has been introduced recently.
> 
> [ scratches head ... ]  I think most of the major recent changes
> have been schema related, so this is quite likely my fault.  Let
> me know if you need help debugging it.

Tom, did you see his patch posted a few hours ago.  Did that fix it?  I
can apply.

> 
> > P.S. Tom, would you mind adding my IP to your spam whitelist?
> > Your spam-blocking software rejects my emails.
> 
> 24.102.202.* whitelisted; let me know if that's not the correct
> IP range for you.  (Although you might think I'm blocking most
> of the net, I still get a depressingly large amount of spam ---
> and that's not even counting the Klez virus that seems to be
> deliberately targeting my jpeg-info alter ego ... grumble ...)

Have you read my spam blocking article and tools:

	http://candle.pha.pa.us/main/writings/spam

It blocks ~70-80% with no false blocks.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 12 Apr 2002 23:39:01 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump is broken in CVS tip

Patch applied.  Thanks.

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



Neil Conway wrote:
> On Fri, 12 Apr 2002 19:24:21 -0400
> "Neil Conway" <nconway@klamath.dyndns.org> wrote:
> > When I built the current CVS code, both test-case exhibits the
> > problem quite obviously. Therefore, it seems that the problem
> > has been introduced recently.
> 
> The problem was introduced here:
> 
> ------------------
> revision 1.246
> date: 2002/04/05 11:51:12;  author: momjian;  state: Exp;  lines: +129 -3
> Adds domain dumping support to pg_dump.
> 
> Rod Taylor
> ------------------
> 
> Rod's patch does what it is supposed to do, but it also includes
> some old code to add PK constraints to CREATE TABLE. That stuff
> had been removed as part of my original patch for pg_dump a
> little while ago.
> 
> The attached patch fixes this by removing (again :-) ) the
> code in dumpTables() to perform PK creation during CREATE
> TABLE. I briefly tested it locally and it fixes both of
> Tom's test cases.
> 
> Please apply.
> 
> Cheers,
> 
> Neil
> 
> -- 
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 13 Apr 2002 15:57:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump is broken in CVS tip