Thread: pg_dump performance lossage for primary keys

I notice that pg_dump now dumps primary-key indexes in the style

CREATE TABLE ... ("dest_index" integer DEFAULT ...,Constraint "dest_addresses_pkey" Primary Key ("dest_index")
);

...

COPY ...  FROM stdin;
-- load data
\.

-- create other indexes for table

Isn't this pretty darn stupid?  Previously, we created indexes after
loading the data.  We're going to take a huge performance hit to do it
this way.

IMHO it would be better to reach in and set the "primary key" flag on
the index after creating it normally.
        regards, tom lane

Philip Warner <pjw@rhyme.com.au> writes:
> At 14:33 3/04/01 -0400, Tom Lane wrote:
>> I notice that pg_dump now dumps primary-key indexes in the style
>> 
>> CREATE TABLE ... (
>> "dest_index" integer DEFAULT ...,
>> Constraint "dest_addresses_pkey" Primary Key ("dest_index")
>> );

> My 7.0 dumps PK in table definitions as well, AFAICT (but it may have been
> patched)  - can you check yours? 

Ah, you are right.  My mistake --- the lossage is of longer standing
than I thought.

> We really need ALTER TABLE ADD CONSTRAINT for PK.

That would be a cleaner way to do it, all right ... but for now, you can
just reach in and set the indisprimary flag in pg_index after creating
the index.  I'm visualizing
CREATE TABLE table( field int NOT NULL, ...);
load data
CREATE UNIQUE INDEX table_pkey ON table(field);
UPDATE pg_index SET indisprimary = true WHERE indexrelid =(SELECT oid FROM pg_class WHERE relname = 'table_pkey');

On the other hand, that would fall over if executed by a non-superuser.
Drat.  Okay, I guess we just have to leave this as a TODO item for now.
        regards, tom lane

At 14:33 3/04/01 -0400, Tom Lane wrote:
>I notice that pg_dump now dumps primary-key indexes in the style
>
>CREATE TABLE ... (
>    "dest_index" integer DEFAULT ...,
>    Constraint "dest_addresses_pkey" Primary Key ("dest_index")
>);
>
>Isn't this pretty darn stupid?

Yep.

>Previously, we created indexes after
>loading the data.  We're going to take a huge performance hit to do it
>this way.

My 7.0 dumps PK in table definitions as well, AFAICT (but it may have been
patched)  - can you check yours? 

The first time PK-in-table was implemented was in rev 1.124/5, but it may
have been taken out afterwards.

We really need ALTER TABLE ADD CONSTRAINT for PK.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

On Tue, Apr 03, 2001 at 03:34:51PM -0400, Tom Lane wrote:
> Philip Warner <pjw@rhyme.com.au> writes:
> 
> > We really need ALTER TABLE ADD CONSTRAINT for PK.
> 
> That would be a cleaner way to do it, all right ... but for now, you can
> just reach in and set the indisprimary flag in pg_index after creating
> the index.  I'm visualizing
> 
<snip>
> 
> On the other hand, that would fall over if executed by a non-superuser.
> Drat.  Okay, I guess we just have to leave this as a TODO item for now.

This is one of those 'dual roles of pg_dump' problems: Philip has been
slowly migrating it from being a 'quickest possible backup dump' tool
to a 'recover my db in as human friendly (and SQL standards compliant)
a format as possible' tool.  Which, not coincidently, has dramatically
reduced the version fragility of the dump output.

Adding implementation specific performance hacks back in is probably
a necessary evil, but should probably be protected by a '--fastdump'
switch or some such.

Ross