Re: Recreate primary key without dropping foreign keys?

How about deferring the FK's while recreating the PK ?
or using a temporary parallel table to be pointed by the other tables
(FK) and swap it up on the recreation.

Cheers,
A.A

On 04/16/2012 06:54 AM, Chris Ernst wrote:
> On 04/16/2012 02:39 AM, Frank Lanitz wrote:
>> Am 16.04.2012 10:32, schrieb Chris Ernst:
>>> 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.
>> Well, from my little view I guess all rebuilding index action would
>> require such, as its the primary key with uniqueness. I'd think of a
>> complete reinit of the cluster with pg_dump and restoring, but this
>> would also need a downtime at least for write access.
>>
>> Why is the index so bloated?
> As in my original post, you can create a unique index concurrently and
> then replace the primary key index with it. This way, the index
> creation doesn't require an exclusive lock. You only need a very brief
> exclusive lock to drop and recreate the primary key constraint using the
> new index.
>
> However, the index creation is not the issue here. That part is done.
> The issue is that there are several foreign keys depending on the
> primary key index that I want to drop and replace with the newly built
> unique index. I would prefer not to drop and recreate all of the
> foreign keys as that would require many hours of down time as well (the
> very situation I was trying to avoid by building the index concurrently
> and swapping it in).
>
> I believe the index bloat is due to a combination of under aggressive
> autovacuum settings and recently deleting about 30% of the table.
>
> - Chris
>