Skip site navigation (1) Skip section navigation (2)

Re: Recreate primary key without dropping foreign keys?

From: Chris Ernst <cernst(at)zvelo(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Recreate primary key without dropping foreign keys?
Date: 2012-04-16 08:32:28
Message-ID: 4F8BD91C.6070405@zvelo.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
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

In response to

Responses

pgsql-admin by date

Next:From: Frank LanitzDate: 2012-04-16 08:39:14
Subject: Re: Recreate primary key without dropping foreign keys?
Previous:From: Albe LaurenzDate: 2012-04-16 08:31:09
Subject: Re: invalid byte sequence for encoding "UTF8": 0xf481 - how could this happen?

pgsql-general by date

Next:From: Frank LanitzDate: 2012-04-16 08:39:14
Subject: Re: Recreate primary key without dropping foreign keys?
Previous:From: Andreas KretschmerDate: 2012-04-16 07:52:39
Subject: Re: Value to long for type ....: Columnname missing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group