geog_brin_inclusion upgrade issue

[Justin Pryzby]

On Mon, Mar 17, 2025 at 12:20:35AM -0400, lr at pcorp.us wrote:
> Well I think it's something broken, but worried that yours is not an isolated situation.
> So is the database that is failing the one that had missing bits of
> geography

Yes -- the DB which hit an upgrade problem is the one where your queries
seemed to show missing geo bits.

> Or is this in all your broken databases.  I wasn't sure if it was one or
> just this particular one.

We only have only one instance that hit an issue running
postgis_extensions_upgrade().

I looked across our ~35 instances and for *most* customers, your first
query returned 4 rows:

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
 brin_geography_inclusion_ops | geography  | geography  | geography_overlaps   |       3580 |              0
(4 rows)

However for 3 customers only returned three:

a.client.telsasoft | CHANGED | rc=0 >>
            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)

b.client.telsasoft | CHANGED | rc=0 >>
            family            | tl_typname | tr_typname |       oprcode        | amopmethod | amopsortfamily 
------------------------------+------------+------------+----------------------+------------+----------------
 brin_geography_inclusion_ops | geography  | gidx       | public.overlaps_geog |       3580 |              0
 brin_geography_inclusion_ops | gidx       | geography  | public.overlaps_geog |       3580 |              0
 brin_geography_inclusion_ops | gidx       | gidx       | public.overlaps_geog |       3580 |              0
(3 filas)

c.client.telsasoft | CHANGED | rc=0 >>
            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)

And for the 2nd query, none of our instances returned anything at all, except
for customer "a", which gives one row:

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;

a.client.telsasoft | CHANGED | rc=0 >>
            family            | tl_typname | tr_typname |       oprcode        | amopmethod | amopstrategy | amopsortfamily 
------------------------------+------------+------------+----------------------+------------+--------------+----------------
 brin_geography_inclusion_ops | geography  | gidx       | public.overlaps_geog |       3580 |            3 |              0
(1 row)

Customer "a" is the one where we hit the upgrade problem.

-- 
Justin