Search Postgresql Archives

Re: Converting non-null unique idx to pkey

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 8/21/07, Ed L. <pgsql@xxxxxxxxxxxxx> 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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux