Re: pg_dump performance lossage for primary keys

Philip Warner <pjw(at)rhyme(dot)com(dot)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