Recreating a primary key

Ericson Smith <eric@did-it.com> writes:

Is there a way to re-create a primary key?

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

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.

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@did-it.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@did-it.com>

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?

Not sure I understand what you mean - whatever "quick and dirty" way you choose to rebuild an index, it has to lock the table,
because otherwise the resulting index will be incorrect...