ERROR: Could not add geog_brin_inclusion_merge to brin_geography_inclusion_ops..null value in column "refobjid" (Re: Do you use PostGIS BRIN?)

[lr at pcorp.us]

Justin,

Thanks.  So it looks like brin was never installed in one version of yours
and in your second the geography brin was never installed.

We've had issues over the years with some BRIN installs being missing or
partly missing because of not catching pg_upgrade changes in PostgreSQL
version.

Anyrate I'll try to catch this situation and install BRIN if it is found to
be missing.

Thanks,
Regina

> -----Original Message-----
> From: Justin Pryzby <pryzby at telsasoft.com>
> Sent: Sunday, March 2, 2025 5:24 PM
> To: lr at pcorp.us
> Cc: Paul Ramsey <pramsey at cleverelephant.ca>; postgis-devel at lists.osgeo.org
> Subject: Re: ERROR: Could not add geog_brin_inclusion_merge to
> brin_geography_inclusion_ops..null value in column "refobjid" (Re: Do you
use
> PostGIS BRIN?)
> 
> On Sun, Mar 02, 2025 at 04:52:48PM -0500, lr at pcorp.us wrote:
> > I've ticketed this issue here:
> > https://trac.osgeo.org/postgis/ticket/5856
> >
> > I'm going to try to revise the upgrade script to account for this
scenario.
> >
> > Can you do me a favor, send me the output of this from your problem
> > database:
> >
> > SELECT * FROM pg_catalog.pg_opclass WHERE opcname ILIKE 'brin%';
> 
> The DB where we hit the error says:
> 
> ts=# SELECT * FROM pg_catalog.pg_opclass WHERE opcname ILIKE 'brin%';
>   oid  | opcmethod |            opcname             | opcnamespace |
opcowner |
> opcfamily | opcintype | opcdefault | opckeytype
>
-------+-----------+--------------------------------+--------------+--------
--+-----------
> +-----------+------------+------------
>  17704 |      3580 | brin_geometry_inclusion_ops_2d |         2200 |
10 |
> 17694 |     16419 | t          |      16454
>  17716 |      3580 | brin_geometry_inclusion_ops_3d |         2200 |
10 |
> 17712 |     16419 | f          |      16458
>  17726 |      3580 | brin_geometry_inclusion_ops_4d |         2200 |
10 |
> 17722 |     16419 | f          |      16458
> (3 rows)
> 
> And the 2nd instance I mentioned which has no
> opcname='brin_geography_inclusion_ops' says:
> 
> ts=# SELECT * FROM pg_catalog.pg_opclass WHERE opcname ILIKE 'brin%';  oid
> | opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype
> | opcdefault | opckeytype
>
-----+-----------+---------+--------------+----------+-----------+----------
-+------------
> +------------
> (0 rows)
> 
> --
> Justin