Обсуждение: Converting non-null unique idx to pkey

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?

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

TIA.
Ed

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

On Tuesday 21 August 2007 1:45 pm, Scott Marlowe wrote:
> 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.

Sounds like good advice from a strictly technical viewpoint.
Unfortunately, in our particular real world, there are also
political, financial, and resource constraints and impacts from
downtime that at times outweigh the technical merits of
upgrading 'right now'.

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

I'd still love to hear from any who know the internals well
enough to say if this should work or if it's a bad idea.  It
appears to work in some cursory testing.

TIA.
Ed

On 8/21/07, Ed L. <pgsql@bluepolka.net> wrote:
> On Tuesday 21 August 2007 1:45 pm, Scott Marlowe wrote:
> > 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.
>
> Sounds like good advice from a strictly technical viewpoint.
> Unfortunately, in our particular real world, there are also
> political, financial, and resource constraints and impacts from
> downtime that at times outweigh the technical merits of
> upgrading 'right now'.

Well, given the very real possibility of the entire database being
lost due to the bugs present in 7.4.6, and the fact that an upgrade
consists of pg_ctl stop;rpm -Uvh postgresql-7.4.xx;pg_ctl start I
can't really accept that as a reasonable argument.

I too work in the socalled "real world" and it took me almost a month
to get an update scheduled from 7.4.7 to 7.4.13 or so when some
serious bug fixes came out.  I can't remember the exact version after
7.4.7 that had the bug fixes I considered necessary off the top of my
head back then.

When is your next scheduled maintenance window?  I would definitely
schedule the update then.  5 minutes downtime versus hours or days if
the db gets corrupted seems a reasonable trade-off.  Plus that 5
minutes of downtime can be scheduled.  Murphy dictates that if your
data gets corrupted it will not happen at 2am when you're doing
maintenance.  It will happen wednesday at 12:30pm while you're at
lunch with your boss discussing your compensation.  :)

> > > 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.
>
> I'd still love to hear from any who know the internals well
> enough to say if this should work or if it's a bad idea.  It
> appears to work in some cursory testing.

Is this a unique index?  If it is, and you set the field(s) to not
null i would think that setting indisprimary might work.  But I'd test
it on a test database to be sure.

"Ed L." <pgsql@bluepolka.net> writes:
> 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?

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

The problem with that is there won't be any pg_constraint entry,
nor any pg_depend entries linking to/from the constraint.

I don't offhand know which bits of logic look at indisprimary
and which pay attention to the pg_constraint entry (and 7.4 is
probably different from current sources on the point anyway).
Things could get a bit weird though, particularly for pg_dump.

Of course, you could gin up the required pg_constraint and pg_depend
entries by hand too, but it's a lot more complex than the above.

If you really wanna do this I'd strongly recommend experimenting
in a scratch database.

            regards, tom lane

On Tuesday 21 August 2007 11:40 pm, Tom Lane wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > 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?
> >
> > update pg_index
> > set indisprimary = 't'
> > where indexrelid = <my non-null unique index oid>
>
> The problem with that is there won't be any pg_constraint
> entry, nor any pg_depend entries linking to/from the
> constraint...

Thanks.  Sounds messy enough, I'll try another route.

Ed

Ed L. wrote:
> On Tuesday 21 August 2007 1:45 pm, Scott Marlowe wrote:
>> 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.
>
> Sounds like good advice from a strictly technical viewpoint.
> Unfortunately, in our particular real world, there are also
> political, financial, and resource constraints and impacts from
> downtime that at times outweigh the technical merits of
> upgrading 'right now'.

Since you're setting up replication to another database, you might as
well try replicating to a newer release and swap them around once it's
done. I've seen that method of upgrading mentioned on this list a few times.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

On Aug 23, 2007, at 7:44 , Kristo Kaiv wrote:

>
> On 23.08.2007, at 11:23, Alban Hertroys wrote:
>
>> Since you're setting up replication to another database, you might as
>> well try replicating to a newer release and swap them around once
>> it's
>> done. I've seen that method of upgrading mentioned on this list a
>> few times.
> Don't try this. Belive me you don't want to do it. We have had our
> fun with this 1.5 y ago

Care to share? Were you using Slony? AIUI, one of the motivations for
Slony was to be able to do exactly that, so I'm sure there's interest
in what didn't go as expected.

Michael Glaesemann
grzm seespotcode net