Thread: 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 23.08.2007, at 11:23, Alban Hertroys wrote:
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 alsopolitical, financial, and resource constraints and impacts fromdowntime that at times outweigh the technical merits ofupgrading 'right now'.Since you're setting up replication to another database, you might aswell try replicating to a newer release and swap them around once it'sdone. I've seen that method of upgrading mentioned on this list a few times.
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
On 23.08.2007, at 16:10, Michael Glaesemann wrote:
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 aswell try replicating to a newer release and swap them around once it'sdone. 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 agoCare 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.
Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)