Re: Proposal: In-Place upgrade concept

On Tue, Jul 03, 2007 at 11:36:03AM -0400, Tom Lane wrote:
> I'm not sure it's feasible to expect that we can change representations
> of user-defined types, either. I don't see how you would do that
> without catalog access (to look up the UDT), and the page conversion
> procedure is going to have to be able to operate without catalog
> accesses. (Thought experiment: a page is read in during crash recovery
> or PITR slave operation, and discovered to have the old format.)

Well, there are two types of conversions:
1. Simple byte rearrangement. If it's not too many you could simply
build them into pg_migrator. Doesn't help with user-defined types, but
maybe you allow plugins to define a seperate hook whose only purpose is
to upgrade the value (without catalog access...).

2. Otherwise you could do a VACUUM over the table to touch every page,
thus solving it. Dunno what to do about crashing at this point.

Hmm, actually, what's the problem with PITR restoring a page in the old
format. As long as it's clear it's the old format it'll get fixed when
the page is actually used.

> BTW, I thought of a likely upgrade problem that we haven't discussed
> (AFAIR) in any of the many threads on this subject. What about an index
> access method change that involves an index-wide restructuring, such
> that it can't be done one page at a time? A plausible example is
> changing hash indexes to have multiple buckets per page. Presumably
> you can fix the index with REINDEX, but that doesn't meet the goal of
> limited downtime, if the index is big. Is there another way?

Well, we have concurrent index builds these days. I certainly don't
have ideas on how to fix this, especially if the index is on a datatype
that has changed format... I suppose those indexes will just have to be
rebuilt (a REINDEX will upgrade every page in the table anyway...). I
think it'd still be cheaper than dump/restore.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.