Re: Recreating a primary key

I guess what I was trying to avoid, was the exclusive table locking
with REINDEX. What we ended up doing was writing a script to iterate
through pg_index (and others) and drop/recreate all our indices.

This works very well, without locking the tables. There is a little
slowdown as there are some sequential scans when indices cannot be used,
but on the whole, we saved about 4Gigs through rebuilding our indices,
while having concurrent access to the database by users and other
scripts. We plan to run this script maybe once per week or so.

My original question was answered by Tom Lane:

> In 7.3, there's ALTER TABLE ADD PRIMARY KEY. In earlier releases,
> you'd have to fake it by manually poking pg_index.indisprimary
> after you create a unique index.
>
> regards, tom lane

His suggestion will work for our primary keys, which we are skipping in
that script now.

- Ericson Smith
eric(at)did-it(dot)com

On Fri, 2003-02-28 at 15:33, scott.marlowe wrote:
> On 28 Feb 2003, Ericson Smith wrote:
>
> > Hi,
> >
> > Is there a way to re-create a primary key?
> >
> > So I create tables with CREATE TABLE... PRIMARY KEY(col...) syntax.
> >
> > I later want to drop the primary key and re-create it (performance
> > considerations, and to not worry about exclusive table locking with a
> > rebuild index).
> >
> > The documentation says that primary keys are basically UNIQUE and NOT
> > NULL keys, but checking with pg_index, I see the "indisprimary" column
> > is set to true.
> >
> > Any quick and dirty way to rebuild these indices?
>
> Have you tried reindex? It does basically just that. And I've just
> tested to be sure, it does work on pkey indexes just fine.
--
Ericson Smith <eric(at)did-it(dot)com>