Обсуждение: Problems with pg_upgrade and extensions referencing catalogtables/views

pgTap has a view that references pg_proc; to support introspection of functions and aggregates. That view references
proisaggin versions < 11, and prokind in 11+. pgtap's make process understands how to handle this; modifying the
creationscripts as necessary. It actually has to do this for several functions as well.
 

The problem is that pg_dump --binary-upgrade intentionally does not simply issue a `CREATE EXTENSION` command the way a
normaldump does, so that it can control the OIDs that are assigned to objects[1]. That means that attempting to
pg_upgradea database with pgtap installed to version 11+ fails trying to create the view that references
pg_proc.proisagg[2].

For pgtap, we should be able to work around this by removing the offending column from the view and embedding the
knowledgein a function. This would be more difficult in other types of extensions though, especially any that are
attemptingto provide more user-friendly views of catalog tables.
 

I don’t recall why pg_upgrade wants to control OIDs… don’t we re-create all catalog entries for user objects from
scratch?

1: https://www.postgresql.org/message-id/AANLkTimm1c64=xkdpz5ji7Q-rH69zd3cMewmRpkH0WSf@mail.gmail.com
2: https://github.com/theory/pgtap/issues/201

"Nasby, Jim" <nasbyj@amazon.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.

            regards, tom lane

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.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

> On May 9, 2019, at 7:14 PM, Bruce Momjian <bruce@momjian.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
needto deal with those somehow.
 

> 
> On May 8, 2019, at 6:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> "Nasby, Jim" <nasbyj@amazon.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’scluster, and that extension authors need to support at least 2 major versions per extension version so that
userscan upgrade. But that’s kind of moot unless we can solve the OID issues. I think that’s possible with a special
modefor 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
viewsthat reference catalog tables. Right now, extension authors could work around that by defining the view on top of
aplpgsql (or maybe plsql) SRF. The function won’t be checked when it’s created, so the upgrade would succeed. The
extensionwould have to have an upgrade available that did whatever was necessary in the new version, and users would
needto ALTER EXTENSION UPDATE after the upgrade. This is rather ugly, but potentially workable. Presumably it won’t
performas 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
timeto 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
setof views on the catalogs that have a defined policy for deprecation of old columns.