Re: Patch to ALTER TABLE docs for 7.2.1

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)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

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(at)postgresql(dot)org]
> Sent: Sunday, 17 February 2002 8:19 PM
> To: Bruce Momjian
> Cc: Christopher Kings-Lynne; pgsql-patches(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)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
>

> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)postgresql(dot)org]
> > Sent: Sunday, 17 February 2002 8:19 PM
> > To: Bruce Momjian
> > Cc: Christopher Kings-Lynne; pgsql-patches(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)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

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(at)rogers(dot)com>
PGP Key ID: DB3C29FC

[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(at)rogers(dot)com>
PGP Key ID: DB3C29FC

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