Thread: 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
Re: Problems with pg_upgrade and extensions referencing catalogtables/views
From
Bruce Momjian
Date:
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.