Masquerading a unique index as a primary key in 8.4?
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
On Tue, Oct 18, 2011 at 6:21 PM, David Pirotte <dpirotte@gmail.com> wrote:
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!
The badness I see that will eventually come back to bite you is that
your unique constraint is lacking "NOT NULL" and a PK by definition
has NOT NULL. Therefore some other parts of the system is permitted
to make that assumption, and when stuff fails because you lied to the
system, you will probably never ever figure out or even know.
On Tue, Nov 8, 2011 at 11:28 AM, Vick Khera <vivek@khera.org> wrote:
On Tue, Oct 18, 2011 at 6:21 PM, David Pirotte <dpirotte@gmail.com> wrote:
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!The badness I see that will eventually come back to bite you is that
your unique constraint is lacking "NOT NULL" and a PK by definition
has NOT NULL. Therefore some other parts of the system is permitted
to make that assumption, and when stuff fails because you lied to the
system, you will probably never ever figure out or even know.
Agreed. I'd be more inclined to change londiste, or just ditch it for
something else that will recognize the unique index as a unique enough
identifier to enable replication. That limitation is kind of lame.
Robert Treat
conjecture: xzilla.net
consulting: omniti.com