From: Chris Ernst <cernst@zvelo.com>
To: pgsql-admin@postgresql.org
Subject: Re: Recreate primary key without dropping foreign keys?
Date: Mon, 16 Apr 2012 02:32:28 -0600
Message-ID: <4F8BD91C.6070405@zvelo.com> (raw)
In-Reply-To: <20120416065717.70a31a6361ac5d7bdad416e8@frank.uvena.de>
References: <4F8B6AA1.1040709@zvelo.com>
<20120416065717.70a31a6361ac5d7bdad416e8@frank.uvena.de>
On 04/15/2012 10:57 PM, Frank Lanitz wrote:
> On Sun, 15 Apr 2012 18:41:05 -0600 Chris Ernst <cernst@zvelo.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: cernst@zvelo.com
Subject: Re: Recreate primary key without dropping foreign keys?
In-Reply-To: <4F8BD91C.6070405@zvelo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox