pg_upgrade fails when using custom extension with operator class - Mailing list pgsql-hackers

Hello community!

I develop an extension that adds UINT data types (and as a bonus support for 128 bit signed integers) to PostgreSQL, including support for all int_ops and BTREE/HASH OPERATOR CLASSes.

And I run into problem: pg_upgrade fails to upgrade from Postgres 16 to Postgres 17 with following error:

command: "/usr/lib/postgresql/17/bin/pg_restore" --host /var/lib/postgresql --port 50432 --username postgres --clean --create --exit-on-error --verbose --transaction-size=1000 --dbname template1 "/var/lib/postgresql/17/main/pg_upgrade_output.d/20250227T013708.392/dump/pg_upgrade_dump_5.custom" >> "/var/lib/postgresql/17/main/pg_upgrade_output.d/20250227T013708.392/log/pg_upgrade_dump_5.log" 2>&1

...

pg_restore: creating OPERATOR CLASS "public.int16_ops"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3311; 2616 17482 OPERATOR CLASS int16_ops postgres
pg_restore: error: could not execute query: ERROR:  operator does not exist: public.int16 public.< public.int16
Command was: CREATE OPERATOR CLASS "public"."int16_ops"
    DEFAULT FOR TYPE "public"."int16" USING "btree" FAMILY "pg_catalog"."integer_ops" AS
    OPERATOR 1 "public".<("public"."int16","public"."int16") ,
    OPERATOR 2 "public".<=("public"."int16","public"."int16") ,
    OPERATOR 3 "public".=("public"."int16","public"."int16") ,
    OPERATOR 4 "public".>=("public"."int16","public"."int16") ,
    OPERATOR 5 "public".>("public"."int16","public"."int16") ,
    FUNCTION 1 ("public"."int16", "public"."int16") "public"."int16_cmp"("public"."int16","public"."int16");

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "uint128" ADD OPERATOR CLASS "public"."int16_ops" USING "btree";

Then I inspected the generated dump (using pg_restore -l cmd) to figure out what's happening, and found that there's an attempt to create an OPERATOR CLASS  before the necessary OPERATORs had been restored.

There's one of very first lines from pg_restore -l output:

3311; 2616 17482 OPERATOR CLASS public int16_ops postgres

That actually causes pg_restore to fail.

Here's part of my extension installation script:

CREATE FUNCTION int16_lt_int16(int16, int16) RETURNS boolean
    IMMUTABLE
    PARALLEL SAFE
    STRICT
    LEAKPROOF
    LANGUAGE C
    AS '$libdir/uint128', 'int16_lt_int16';

...

CREATE OPERATOR < (
    LEFTARG=int16,
    RIGHTARG=int16,
    PROCEDURE=int16_lt_int16,
    COMMUTATOR = <,
    NEGATOR = >=,
    RESTRICT = scalarltsel,
    JOIN = scalarltjoinsel
);

...

CREATE FUNCTION int16_cmp(int16, int16) RETURNS int
    IMMUTABLE
    STRICT
    LANGUAGE C
    AS '$libdir/uint128', 'int16_cmp';

CREATE FUNCTION int16_hash(int16) RETURNS integer
    IMMUTABLE
    STRICT
    LANGUAGE C
    AS '$libdir/uint128', 'int16_hash';

CREATE OPERATOR CLASS int16_ops
DEFAULT FOR TYPE int16 USING btree FAMILY integer_ops AS
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,
    FUNCTION 1 int16_cmp(int16, int16)
;

CREATE OPERATOR CLASS int16_ops
DEFAULT FOR TYPE int16 USING hash FAMILY integer_ops AS
    OPERATOR        1       = ,
    FUNCTION        1       int16_hash(int16);

I also tried to insert additional records to pg_depend to force PostgreSQL restore OPERATORs before OPERATOR CLASS, but had no luck with it:

INSERT INTO pg_depend (
    classid,
    objid,
    objsubid,
    refclassid,
    refobjid,
    refobjsubid,
    deptype
)
SELECT
    'pg_opclass'::regclass::oid AS classid,
    pg_opclass.oid AS objid,
    0 AS objsubid,
    'pg_operator'::regclass::oid AS refclassid,
    pg_operator.oid AS refobjid,
    0 AS refobjsubid,
    'n' AS deptype

FROM (SELECT 'int16'::regtype AS oid) typ

JOIN pg_opclass ON pg_opclass.opcintype = typ.oid

-- Ensure BTREE operator family
JOIN pg_opfamily ON pg_opfamily.oid = pg_opclass.opcfamily
JOIN pg_am ON pg_am.oid = pg_opfamily.opfmethod AND pg_am.amname = 'btree'

-- BTREE operators
JOIN pg_operator ON oprname IN ('<', '<=', '=', '>=', '>') AND oprleft = typ.oid AND oprright = typ.oid
;

select * from pg_depend_view where objid = 19107 order by refobjid;
 classid |    obj_type    | objid | obj_name  | refclassid | ref_obj_type | refobjid | ref_obj_name | deptype
---------+----------------+-------+-----------+------------+--------------+----------+--------------+---------
    2616 | OPERATOR CLASS | 19107 | int16_ops |       2615 | SCHEMA       |     2200 | public       | n
    2616 | OPERATOR CLASS | 19107 | int16_ops |       3079 | EXTENSION    |    18010 | uint128      | e
    2616 | OPERATOR CLASS | 19107 | int16_ops |       1247 | TYPE         |    18015 | int16        | n
    2616 | OPERATOR CLASS | 19107 | int16_ops |       2617 | OPERATOR     |    19023 | =            | n
    2616 | OPERATOR CLASS | 19107 | int16_ops |       2617 | OPERATOR     |    19036 | <=           | n
    2616 | OPERATOR CLASS | 19107 | int16_ops |       2617 | OPERATOR     |    19037 | >            | n
    2616 | OPERATOR CLASS | 19107 | int16_ops |       2617 | OPERATOR     |    19050 | >=           | n
    2616 | OPERATOR CLASS | 19107 | int16_ops |       2617 | OPERATOR     |    19051 | <            | n
(8 rows)

Can you help me to figure out what's happening and what is the proper way to fix it?