Re: Converting non-null unique idx to pkey

On 8/21/07, Ed L. <pgsql(at)bluepolka(dot)net> wrote:
>
> I'm preparing a fairly large 7.4.6 DB for trigger-based
> replication. I'm looking for ways to minimize my impact on the
> existing schema & data and uptime. This replication solution
> requires every table to have a primary key. Rather than adding
> a new key column and index for the pkey, it's appealing to just
> to reuse existing unique indices on non-null columns. Are there
> are any known or obvious gotchas associated with transforming a
> unique index on a non null column into a primary key via this
> sql?

What replication system is this? Slony only requires a unique key on
a not null column. If that's what you're using, see if alter it to
not null will get around this. Note you may have to update any null
values to something else first.

If you have a large db in 7.4.6, you should do two things.

1: Update to 7.4.19 or whatever the latest flavor of 7.4 is, right
now. There are a few known data eating bugs in 7.4.6.
2: Start planning a migration to 8.2.4 now. Start implementing it as
soon after that as you can.
3: Slony allows you to migrate from old to new versions, but only the
older versions of slony support 7.4.

>
> update pg_index
> set indisprimary = 't'
> where indexrelid = <my non-null unique index oid>

I wouldn't bet on that working right. Others know the internals of
the db better than me, but that looks like a foot gun.