geog_brin_inclusion upgrade issue

[Justin Pryzby]

On Sun, Mar 16, 2025 at 10:59:31PM -0400, lr at pcorp.us wrote:
> Yah that's my assumption that the problem was introduced around 2.3 - 2.5 
> I think something went wrong early on and we didn't completely catch
> these cases. 

It might also be related to the "CREATE EXTENSION postgis FROM
unpackaged" stuff.  I just noticed that the 2nd DB we have which hit an
error during 

> This one is a bit puzzling why this fails. It seems somehow you managed to
> have an operator family created without an 
> Operator class.  I think there was a time a long time ago when we did have
> to explicitly create operator families and operator classes.
> But these days when you create an operator class it automatically creates an
> operator family.
> 
> Please output the results of these two queries:
> 
> SELECT fam.opfname AS family, tl.typname AS tl_typname, tr.typname AS
> tr_typname, o.oprcode, am.amopmethod, am.amopsortfamily
> 	FROM pg_catalog.pg_amop AS am 
> 		INNER JOIN pg_catalog.pg_opfamily AS fam ON am.amopfamily = fam.oid
> 		INNER JOIN  pg_catalog.pg_type AS tl ON  am.amoplefttype = tl.oid 
> 		INNER JOIN  pg_catalog.pg_type AS tr ON  am.amoprighttype = tr.oid
> 		INNER JOIN pg_catalog.pg_operator AS o ON am.amopopr = o.oid
> WHERE fam.opfname = 'brin_geography_inclusion_ops' AND am.amopstrategy = 3;

            family            | tl_typname | tr_typname |       oprcode        | amopmethod | amopsortfamily
------------------------------+------------+------------+----------------------+------------+----------------
 brin_geography_inclusion_ops | gidx       | gidx       | public.overlaps_geog |       3580 |              0
 brin_geography_inclusion_ops | gidx       | geography  | public.overlaps_geog |       3580 |              0
 brin_geography_inclusion_ops | geography  | gidx       | public.overlaps_geog |       3580 |              0
(3 rows)

> SELECT fam.opfname AS family, tl.typname AS tl_typname, tr.typname AS
> tr_typname, o.oprcode, am.amopmethod, am.amopstrategy, am.amopsortfamily
> 	FROM pg_catalog.pg_amop AS am 
> 		INNER JOIN pg_catalog.pg_opfamily AS fam ON am.amopfamily = fam.oid
> 		INNER JOIN  pg_catalog.pg_type AS tl ON  am.amoplefttype = tl.oid 
> 		INNER JOIN  pg_catalog.pg_type AS tr ON  am.amoprighttype = tr.oid
> 		INNER JOIN pg_catalog.pg_operator AS o ON am.amopopr = o.oid
> WHERE am.amopfamily = 17690 AND am.amoplefttype = 17039 AND am.amoprighttype = 16458;

            family            | tl_typname | tr_typname |       oprcode        | amopmethod | amopstrategy | amopsortfamily
------------------------------+------------+------------+----------------------+------------+--------------+----------------
 brin_geography_inclusion_ops | geography  | gidx       | public.overlaps_geog |       3580 |            3 |              0
(1 row)

I originally said that we had a 2nd instance with this issue, but it
seems I was wrong.

If you think this is something that just got broken in this DB, and not
likely to affect anyone else, feel free to give up, and I'll kick it
into shape.  We can drop and re-add the extension without much fuss.
Which might be a good idea -- this one problem might be indicative of
some weird upgrade path with other symptoms that we haven't hit yet.

-- 
Justin