Re: pg_dump performance lossage for primary keys - Mailing list pgsql-hackers

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