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