Masquerading a unique index as a primary key in 8.4?

From: David Pirotte <dpirotte(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Masquerading a unique index as a primary key in 8.4?
Date: 2011-10-18 22:21:26
Message-ID: CAOXUAc+QceyRqG2xFYdLT7To=GPZO2gUfqwBa0FpK93JaOKwRQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a large, frequently accessed table that needs a primary key
constraint added. The table already has a unique index (but not a unique
constraint) on one of its columns. The overly simplified schema looks like
this:

CREATE TABLE table_without_pk (not_a_pk integer not null, some_data text);
CREATE UNIQUE INDEX not_a_pk_idx ON table_without_pk (not_a_pk);

It looks like 9.1 added syntax that would allow us to create the primary key
constraint off of the existing unique index, but unfortunately we're on
8.4. A post from several years ago (
http://archives.postgresql.org/pgsql-general/2004-12/msg01161.php) implies
that one can update the metadata tables underneath in order to accomplish
this:

UPDATE pg_index i SET indisprimary = 't' FROM pg_stat_user_indexes u WHERE
u.indexrelid = i.indexrelid AND u.indexrelname = 'not_a_pk_idx';

The underlying purpose is to get Londiste to acknowledge the table's key,
and this strategy seems to work without any problems. Londiste doesn't seem
to care that the "primary key" is only reflected in pg_index and isn't
accompanied by the relevant pg_constraint entry. Is modifying the
underlying pg_catalog tables like this "Very Bad"? Will it have mysterious
and unintended consequences, or can I get away with it? Thanks!

Cheers,
Dave

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mark Priest 2011-10-19 00:06:11 Fwd: Out of Memory Error on Insert
Previous Message Scott Marlowe 2011-10-18 20:25:26 Re: Postgre Performance