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