Problems with pg_upgrade and extensions referencing catalog tables/views
pgTap has a view that references pg_proc; to support introspection of functions and aggregates. That view references proisagg in versions < 11, and prokind in 11+. pgtap's make process understands how to handle this; modifying the creation scripts 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 normal dump does, so that it can control the OIDs that are assigned to objects[1]. That means that attempting to pg_upgrade a 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 knowledge in a function. This would be more difficult in other types of extensions though, especially any that are attempting to 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: /messages/by-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:
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:
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@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’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.