Presence of pg_ivm causes pg_upgrade failure: ERROR: permission denied to create "pg_catalog.pg_ivm_immv"

[ivan]

In my upgrades from PostgreSQL 14.7 -> 15.2 and PostgreSQL 15.5 -> 16.1 on NixOS, I noticed that having pg_ivm in the database prevents pg_upgrade from succeeding. The upgrade succeeds only after dropping the extension from the old cluster.

The relevant part in the second log below is:

pg_restore: creating TABLE "pg_catalog.pg_ivm_immv"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 229; 1259 18065 TABLE pg_ivm_immv postgres
pg_restore: error: could not execute query: ERROR:  permission denied to create "pg_catalog.pg_ivm_immv"
DETAIL:  System catalog modifications are currently disallowed.

---

/var/lib/postgresql# pg_upgrade -d 15 -D 16 -b /nix/store/dln4b6ss9q6mhkgygwfri48f479f7wi8-postgresql-and-plugins-15.5/bin -B /nix/store/wzwsnhxxb91gw2bwr5w7w2g8nz4f4cff-postgresql-and-plugins-16.1/bin
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database user is the install user                    ok
Checking database connection settings                         ok
Checking for prepared transactions                            ok
Checking for system-defined composite types in user tables    ok
Checking for reg* data types in user tables                   ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking for incompatible "aclitem" data type in user tables  ok
Creating dump of global objects                               ok
Creating dump of database schemas
                                                              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
Analyzing all rows in the new cluster                         ok
Freezing all rows in the new cluster                          ok
Deleting files from new pg_xact                               ok
Copying old pg_xact to new server                             ok
Setting oldest XID for new cluster                            ok
Setting next transaction ID and epoch for new cluster         ok
Deleting files from new pg_multixact/offsets                  ok
Copying old pg_multixact/offsets to new server                ok
Deleting files from new pg_multixact/members                  ok
Copying old pg_multixact/members to new server                ok
Setting next multixact ID and offset for new cluster          ok
Resetting WAL archives                                        ok
Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster
  ivan
*failure*

Consult the last few lines of "16/pg_upgrade_output.d/20231117T143344.919/log/pg_upgrade_dump_16584.log" for
the probable cause of the failure.
Failure, exiting

command: "/nix/store/wzwsnhxxb91gw2bwr5w7w2g8nz4f4cff-postgresql-and-plugins-16.1/bin/pg_dump" --host /var/lib/postgresql --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="16/pg_upgrade_output.d/20231117T143344.919/dump/pg_upgrade_dump_16584.custom" 'dbname=ivan' >> "16/pg_upgrade_output.d/20231117T143344.919/log/pg_upgrade_dump_16584.log" 2>&1

command: "/nix/store/wzwsnhxxb91gw2bwr5w7w2g8nz4f4cff-postgresql-and-plugins-16.1/bin/pg_restore" --host /var/lib/postgresql --port 50432 --username postgres --create --exit-on-error --verbose --dbname template1 "16/pg_upgrade_output.d/20231117T143344.919/dump/pg_upgrade_dump_16584.custom" >> "16/pg_upgrade_output.d/20231117T143344.919/log/pg_upgrade_dump_16584.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating DATABASE "ivan"
pg_restore: connecting to new database "ivan"
pg_restore: creating DATABASE PROPERTIES "ivan"
pg_restore: connecting to new database "ivan"
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating EXTENSION "pg_ivm"
pg_restore: creating COMMENT "EXTENSION "pg_ivm""
pg_restore: creating SCHEMA "__pg_ivm__"
pg_restore: creating SCHEMA "ivan"

[...]

pg_restore: creating FUNCTION "pg_catalog.IVM_immediate_before()"
pg_restore: creating FUNCTION "pg_catalog.IVM_immediate_maintenance()"
pg_restore: creating FUNCTION "pg_catalog.IVM_prevent_immv_change()"
pg_restore: creating FUNCTION "pg_catalog.create_immv("text", "text")"
pg_restore: creating FUNCTION "pg_catalog.get_immv_def("regclass")"
pg_restore: creating FUNCTION "pg_catalog.ivm_visible_in_prestate("oid", "tid", "oid")"
pg_restore: creating FUNCTION "pg_catalog.refresh_immv("text", boolean)"

[...]

pg_restore: creating TABLE "pg_catalog.pg_ivm_immv"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 229; 1259 18065 TABLE pg_ivm_immv postgres
pg_restore: error: could not execute query: ERROR:  permission denied to create "pg_catalog.pg_ivm_immv"
DETAIL:  System catalog modifications are currently disallowed.
Command was: 
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('18067'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('18066'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_class oids and relfilenodes
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('18065'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('18065'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_toast_pg_class_oid('18068'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_toast_relfilenode('18068'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('18069'::pg_catalog.oid);
SELECT pg_catalog.binary_upgrade_set_next_index_relfilenode('18069'::pg_catalog.oid);

CREATE TABLE "pg_catalog"."pg_ivm_immv" (
    "immvrelid" "regclass" NOT NULL,
    "viewdef" "text" NOT NULL,
    "ispopulated" boolean NOT NULL
);

-- For binary upgrade, set heap's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '840', relminmxid = '1'
WHERE oid = '"pg_catalog"."pg_ivm_immv"'::pg_catalog.regclass;

-- For binary upgrade, set toast's relfrozenxid and relminmxid
UPDATE pg_catalog.pg_class
SET relfrozenxid = '840', relminmxid = '1'
WHERE oid = '18068';

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "pg_ivm" ADD TABLE "pg_catalog"."pg_ivm_immv";

[yugo-n]

Creating the catalog table pg_ivm_immv in pg_catalog is useful in the sense that it can be easily accessed without considering search_path, but I've found some problem. In addition to the permission problem you reported, data in pg_catalog are not copied by pg_upgrade. Technically, we can allow pg_upgrade to create a table in pg_catalog, and provide some method to copy data at upgrading, but I might be better to place the catalog table in other schema in future.

[maherbeg]

Is the best way to continue the upgrade to drop the extension and then create it again after the upgrade? Or is there some modification we can make to the schemas that is safe for upgrading (e.g. drop the catalog table?)