Message-ID: <4F8CC11A.5020003@d2.com>
Date: Mon, 16 Apr 2012 18:02:18 -0700
From: amador alvarez <aalvarez@d2.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US;
	rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Recreate primary key without dropping foreign keys?
References: <4F8B6AA1.1040709@zvelo.com>
	<20120416065717.70a31a6361ac5d7bdad416e8@frank.uvena.de>
	<4F8BD91C.6070405@zvelo.com> <4F8BDAB2.7010209@frank.uvena.de>
	<4F8C248E.7070307@zvelo.com>
In-Reply-To: <4F8C248E.7070307@zvelo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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@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.
>> 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
>