pg_sphere 1.5.1 caused postgresql 16 to postgresql 17 pg_upgrade failure.

[zjworacle]

The error is:
`pg_restore: creating OPERATOR "public.@"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4992; 2617 5984041 OPERATOR @ postgres
pg_restore: error: could not execute query: ERROR: commutator operator @> is already the commutator of operator <@
Command was: CREATE OPERATOR "public".@ (
FUNCTION = "public"."healpix_subset_smoc",
LEFTARG = bigint,
RIGHTARG = "public"."smoc",
COMMUTATOR = OPERATOR("public".@>),
NEGATOR = OPERATOR("public".!<@),
RESTRICT = "contsel",
JOIN = "contjoinsel"
);

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "pg_sphere" ADD OPERATOR "public".@ (bigint, "public"."smoc");`

The problem looks caused by stricter commutator check.

[esabol]

The problem looks caused by stricter commutator check.

OK… So how should this be fixed?

[zjworacle]

The CREATE OPERATOR "public".@ may have to be changed so no conflict to existing commutator of operator.

[esabol]

Can you be more specific? I don't see the conflict.

[df7cb]

This was already fixed by e810f5d, or did we miss some instance, or forgot about covering upgrades?

[zjworacle]

No, the fix didn't cover upgrades.

[esabol]

@df7cb wrote:

This was already fixed by e810f5d, or did we miss some instance, or forgot about covering upgrades?

Ah, that would explain why I wasn't seeing the conflict when I looked at the code in the repository!

@zjworacle wrote:

No, the fix didn't cover upgrades.

But that commit that @df7cb linked to above clearly shows DROP OPERATOR IF EXISTS @(bigint, smoc); DROP OPERATOR IF EXISTS @(spoint, smoc); in the upgrade script. Unless it was added to the wrong upgrade script somehow? Or were those DROPs insufficient to prevent this error? Or perhaps you didn't upgrade PgSphere using the proper method to run the upgrade script (psql -c "ALTER EXTENSION pg_sphere UPDATE TO 'A.B.C';" <database>)?

[df7cb]

Is your 16 database already on 1.5.1?

[zjworacle]

Yes. the 16 database is already on pg_sphere 1.5.1.

[zjworacle]

The pg_sphere 1.5.1 came from pg_sphere 1.3.2 in pg 14.14.
The 1.3.2 was no-op hacked to 1.4.0, upgraded through 1.4.0->1.41->1.5.0->1.5.1 chain with pg_sphere 1.5.1 extension, db is then upgraded to pg 16.

[df7cb]

The operators were supposed to be dropped before 1.3.2:

+++ b/upgrade_scripts/pg_sphere--1.3.1--1.3.2.sql.in
@@ -12,3 +12,7 @@ EXCEPTION
    WHEN OTHERS THEN RAISE;
 END;
 $$;
+
+-- remove legacy spellings of operators
+DROP OPERATOR IF EXISTS @(bigint, smoc);
+DROP OPERATOR IF EXISTS @(spoint, smoc);

But who know what else happened to that installation if you had to do the last upgrade like that.

I'd think we can close this unless there is a reproducer of how to get a database into this state.

[zjworacle]

Thanks for looking into this.
Can the @(bigint, smoc) and @(spoint, smoc) opeator be safely dropped in later version after 1.3.2 (ex: 1.5.1)?

[esabol]

Can the @(bigint, smoc) and @(spoint, smoc) opeator be safely dropped in later version after 1.3.2 (ex: 1.5.1)?

Yes. Just execute DROP OPERATOR IF EXISTS @(bigint, smoc); DROP OPERATOR IF EXISTS @(spoint, smoc); in all your databases.

[zjworacle]

It said the operators are being used.
ppcentralbutler=# DROP OPERATOR IF EXISTS @(bigint, smoc); ERROR: cannot drop operator @(bigint,smoc) because extension pg_sphere requires it HINT: You can drop extension pg_sphere instead. ppcentralbutler=# DROP OPERATOR IF EXISTS @(spoint, smoc); ERROR: cannot drop operator @(spoint,smoc) because extension pg_sphere requires it HINT: You can drop extension pg_sphere instead.

[df7cb]

alter extension pg_sphere DROP OPERATOR @(bigint, smoc);
alter extension pg_sphere DROP OPERATOR @(spoint, smoc);
DROP OPERATOR @(bigint, smoc)
DROP OPERATOR @(spoint, smoc);