I think it's important that it's actually documented that they can add
primary keys after the fact!

Also, we need to add regression tests for alter table / add primary key
and alter table / drop constraint.  These shouldn't be added until 7.3 tho
methinks...

Chris
Sun, 17 Feb 2002 18:54:15 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Patch to ALTER TABLE docs for 7.2.1

Patch applied.  Thanks.

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


Christopher Kings-Lynne wrote:
> I think it's important that it's actually documented that they can add
> primary keys after the fact!
> 
> Also, we need to add regression tests for alter table / add primary key
> and alter table / drop constraint.  These shouldn't be added until 7.3 tho
> methinks...
> 
> Chris
> 

Content-Description: 

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.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

Sun, 17 Feb 2002 06:49:58 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Patch to ALTER TABLE docs for 7.2.1
Awww Rats.

Wish I'd known a primary key could be added after table creation about 4
hours ago.  Am wrapping up the 2nd CBT now (on Referential Integrity)
and it's a bit late for adding another section.  Must read the docs more
often.

Oh well.  Next CBT maybe.

:)

Regards and best wishes,

Justin Clift


Bruce Momjian wrote:
> 
> Patch applied.  Thanks.
> 
> ---------------------------------------------------------------------------
> 
> Christopher Kings-Lynne wrote:
> > I think it's important that it's actually documented that they can add
> > primary keys after the fact!
> >
> > Also, we need to add regression tests for alter table / add primary key
> > and alter table / drop constraint.  These shouldn't be added until 7.3 tho
> > methinks...
> >
> > Chris
> >
> 
> Content-Description:
> 
> [ Attachment, skipping... ]
> 
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Sun, 17 Feb 2002 23:19:23 +1100
Justin Clift <justin@postgresql.org>
Re: Patch to ALTER TABLE docs for 7.2.1
Another idea.  Now that we have ALTER TABLE / ADD PRIMARY KEY - we should
modify the pg_dump format to instead of doing this:

CREATE TABLE food (
	a int4,
	PRIMARY KEY (a)
);

COPY FROM ...

It should be like this:

CREATE TABLE food (
	a int4
);

COPY FROM ...

ALTER TABLE food ADD PRIMARY KEY (a);

This will be a lot faster.  The only reason (I believe) that it was not done
like this previously is that it wasn't possible to recreate a PK in any
other way without twiddling the catalogs.

Chris


> -----Original Message-----
> From: Justin Clift [mailto:justin@postgresql.org]
> Sent: Sunday, 17 February 2002 8:19 PM
> To: Bruce Momjian
> Cc: Christopher Kings-Lynne; pgsql-patches@postgresql.org
> Subject: Re: [PATCHES] Patch to ALTER TABLE docs for 7.2.1
>
>
> Awww Rats.
>
> Wish I'd known a primary key could be added after table creation about 4
> hours ago.  Am wrapping up the 2nd CBT now (on Referential Integrity)
> and it's a bit late for adding another section.  Must read the docs more
> often.
>
> Oh well.  Next CBT maybe.
>
> :)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Bruce Momjian wrote:
> >
> > Patch applied.  Thanks.
> >
> >
> ------------------------------------------------------------------
> ---------
> >
> > Christopher Kings-Lynne wrote:
> > > I think it's important that it's actually documented that they can add
> > > primary keys after the fact!
> > >
> > > Also, we need to add regression tests for alter table / add
> primary key
> > > and alter table / drop constraint.  These shouldn't be added
> until 7.3 tho
> > > methinks...
> > >
> > > Chris
> > >
> >
> > Content-Description:
> >
> > [ Attachment, skipping... ]
> >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>    - Indira Gandhi
>


Mon, 18 Feb 2002 09:34:14 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Patch to ALTER TABLE docs for 7.2.1
> Christopher Kings-Lynne wrote:
> Another idea.  Now that we have ALTER TABLE / ADD PRIMARY KEY - we should
> modify the pg_dump format to instead of doing this:
> 
> CREATE TABLE food (
> 	a int4,
> 	PRIMARY KEY (a)
> );
> 
> COPY FROM ...
> 
> It should be like this:
> 
> CREATE TABLE food (
> 	a int4
> );
> 
> COPY FROM ...
> 
> ALTER TABLE food ADD PRIMARY KEY (a);
> 
> This will be a lot faster.  The only reason (I believe) that it was not done
> like this previously is that it wasn't possible to recreate a PK in any
> other way without twiddling the catalogs.

Very good point.  Added to TODO:

	* Have pg_dump use ADD PRIMARY KEY after COPY, for performance

-- 
  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

Sun, 17 Feb 2002 21:00:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Patch to ALTER TABLE docs for 7.2.1
Hi Christopher,

Forwarding this to [HACKERS] as it sounds like it belong there now.

:)

This is sounds worthwhile, from the point of view that it makes it
easier for users with large data sets, and it'll assist in the
"PostgreSQL loads database dump data slowly" type problem which seems to
show itself in some people's testing.

How easy to implement this do you reckon?

:-)

Regards and best wishes,

Justin Clift


Christopher Kings-Lynne wrote:
> 
> Another idea.  Now that we have ALTER TABLE / ADD PRIMARY KEY - we should
> modify the pg_dump format to instead of doing this:
> 
> CREATE TABLE food (
>         a int4,
>         PRIMARY KEY (a)
> );
> 
> COPY FROM ...
> 
> It should be like this:
> 
> CREATE TABLE food (
>         a int4
> );
> 
> COPY FROM ...
> 
> ALTER TABLE food ADD PRIMARY KEY (a);
> 
> This will be a lot faster.  The only reason (I believe) that it was not done
> like this previously is that it wasn't possible to recreate a PK in any
> other way without twiddling the catalogs.
> 
> Chris
> 
> > -----Original Message-----
> > From: Justin Clift [mailto:justin@postgresql.org]
> > Sent: Sunday, 17 February 2002 8:19 PM
> > To: Bruce Momjian
> > Cc: Christopher Kings-Lynne; pgsql-patches@postgresql.org
> > Subject: Re: [PATCHES] Patch to ALTER TABLE docs for 7.2.1
> >
> >
> > Awww Rats.
> >
> > Wish I'd known a primary key could be added after table creation about 4
> > hours ago.  Am wrapping up the 2nd CBT now (on Referential Integrity)
> > and it's a bit late for adding another section.  Must read the docs more
> > often.
> >
> > Oh well.  Next CBT maybe.
> >
> > :)
> >
> > Regards and best wishes,
> >
> > Justin Clift
> >
> >
> > Bruce Momjian wrote:
> > >
> > > Patch applied.  Thanks.
> > >
> > >
> > ------------------------------------------------------------------
> > ---------
> > >
> > > Christopher Kings-Lynne wrote:
> > > > I think it's important that it's actually documented that they can add
> > > > primary keys after the fact!
> > > >
> > > > Also, we need to add regression tests for alter table / add
> > primary key
> > > > and alter table / drop constraint.  These shouldn't be added
> > until 7.3 tho
> > > > methinks...
> > > >
> > > > Chris
> > > >
> > >
> > > Content-Description:
> > >
> > > [ Attachment, skipping... ]
> > >
> > > >
> > > > ---------------------------(end of
> > broadcast)---------------------------
> > > > TIP 6: Have you searched our list archives?
> > > >
> > > > http://archives.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
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> > --
> > "My grandfather once told me that there are two kinds of people: those
> > who work and those who take the credit. He told me to try to be in the
> > first group; there was less competition there."
> >    - Indira Gandhi
> >

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Mon, 18 Feb 2002 13:40:41 +1100
Justin Clift <justin@postgresql.org>
Re: [PATCHES] Patch to ALTER TABLE docs for 7.2.1
On Sun, 2002-02-17 at 21:00, Bruce Momjian wrote:
> Very good point.  Added to TODO:
> 
> 	* Have pg_dump use ADD PRIMARY KEY after COPY, for performance

I have started working on this. AFAICT, it shouldn't be too difficult
(although I haven't finished it yet, so perhaps I speak too soon ;-) ).

Cheers,

Neil

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


17 Feb 2002 22:37:46 -0500
Neil Conway <nconway@klamath.dyndns.org>
Re: Patch to ALTER TABLE docs for 7.2.1
[re-sending to -hackers, as the thread seems to be moving there]

On Sun, 2002-02-17 at 21:00, Bruce Momjian wrote: 
> Very good point.  Added to TODO:
> 
> 	* Have pg_dump use ADD PRIMARY KEY after COPY, for performance

I have started working on this. AFAICT, it shouldn't be too difficult
(although I haven't finished it yet, so perhaps I speak too soon ;-) ).

Cheers,

Neil

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


17 Feb 2002 22:41:19 -0500
Neil Conway <nconway@klamath.dyndns.org>
Re: [PATCHES] Patch to ALTER TABLE docs for 7.2.1
> > 	* Have pg_dump use ADD PRIMARY KEY after COPY, for performance
>
> I have started working on this. AFAICT, it shouldn't be too difficult
> (although I haven't finished it yet, so perhaps I speak too soon ;-) ).

Doh!  I was just about to say that I'll do it this afternoon!  I'll leave it
to you then :)

Chris


Mon, 18 Feb 2002 12:08:28 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Patch to ALTER TABLE docs for 7.2.1