Re: Problems with pg_upgrade and extensions referencing catalog tables/views

> On May 9, 2019, at 7:14 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Wed, May 8, 2019 at 10:07:23PM +0000, Nasby, Jim wrote:
>> I don’t recall why pg_upgrade wants to control OIDs… don’t we
>> re-create all catalog entries for user objects from scratch?
>
> The C comment at top of pg_upgrade.c explains why some oids must be preserved:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/bin/pg_upgrade/pg_upgrade.c;h=0b304bbd56ab0204396838618e86dfad757c2812;hb=HEAD
>
> It doesn't mention extensions.

Right, but it does mention tables, types and enums, all of which can be created by extensions. So no matter what, we’d need to deal with those somehow.

>
> On May 8, 2019, at 6:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Nasby, Jim" <nasbyj(at)amazon(dot)com> writes:
>> The problem is that pg_dump --binary-upgrade intentionally does not
>> simply issue a `CREATE EXTENSION` command the way a normal dump does, so
>> that it can control the OIDs that are assigned to objects[1].
>
> That's not the only reason. The original concerns were about not
> breaking the extension, in case the destination server had a different
> version of the extension available. CREATE EXTENSION doesn't normally
> guarantee that you get an exactly compatible extension version, which
> is a good thing for regular pg_dump and restore but a bad thing
> for binary upgrade.
>
> I'm not really sure how to improve the situation you describe, but
> "issue CREATE EXTENSION and pray" doesn't sound like a solution.

I think it’s reasonable to expect that users have the same version of the extension already installed in the new version’s cluster, and that extension authors need to support at least 2 major versions per extension version so that users can upgrade. But that’s kind of moot unless we can solve the OID issues. I think that’s possible with a special mode for CREATE EXTENSION where we specify the OIDs to use for specific objects.

That does leave the question of whether all of this is worth it; AFAICT the only place this is really a problem is views that reference catalog tables. Right now, extension authors could work around that by defining the view on top of a plpgsql (or maybe plsql) SRF. The function won’t be checked when it’s created, so the upgrade would succeed. The extension would have to have an upgrade available that did whatever was necessary in the new version, and users would need to ALTER EXTENSION UPDATE after the upgrade. This is rather ugly, but potentially workable. Presumably it won’t perform as well as a native view would.

Another option is to allow for views to exist in an invalid state. This is something Oracle allows, and comes up from time to time. It still has the upgrade problems that using SRFs does.

However, since this is really just a problem for referencing system catalogs, perhaps the best solution is to offer a set of views on the catalogs that have a defined policy for deprecation of old columns.