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@xxxxxxxxx>
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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
