Re: Recreate primary key without dropping foreign keys?

On 04/15/2012 10:57 PM, Frank Lanitz wrote:
> On Sun, 15 Apr 2012 18:41:05 -0600 Chris Ernst <cernst(at)zvelo(dot)com>
> wrote:
>
>> Hi all,
>>
>> In PostgreSQL 9.1.3, I have a few fairly large tables with
>> bloated primary key indexes. I'm trying to replace them using
>> newly created unique indexes as outlined in the docs. Something
>> like:
>>
>> CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON
>> distributors (dist_id); ALTER TABLE distributors DROP CONSTRAINT
>> distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY
>> USING INDEX dist_id_temp_idx;
>>
>> However, the initial drop of the primary key constraint fails
>> because there are a whole bunch of foreign keys depending on it.
>>
>> I've done some searching and haven't found a workable solution.
>> Is there any way to swap in the new index for the primary key
>> constraint without dropping all dependent foreign keys? Or am I
>> pretty much stuck with dropping and recreating all of the foreign
>> keys?
>
> REINDEX is not working here?

Hi Frank,

Thanks, but REINDEX is not an option as it would take an exclusive
lock on the table for several hours.

For all of the other indexes, I create a new index concurrently, drop
the old and swap in the new. But the primary key is a bit trickier
because I can't drop the primary key index without dropping the
primary key constraint and I can't drop the primary key constraint
without dropping all of the foreign keys that reference that column.

- Chris