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

[Regina Obe]

Justin,

Remind me do you always have this issue when upgrading older customers
(those who have been running PostGIS since at least PostgreSQL 9.3) or only
with some customers?

> -----Original Message-----
> From: Justin Pryzby <pryzby at telsasoft.com>
> Sent: Thursday, February 12, 2026 9:11 AM
> 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?)
> 
> We hit this again while/after upgrading one of our remaining
pg17+postgis3.4
> customers to pg18+postgis3.6.
> 
> # SELECT postgis_full_version();
> postgis_full_version | POSTGIS="3.6.1 f533623" [EXTENSION] PGSQL="170"
> (procs need upgrade for use with PostgreSQL "180") GEOS="3.11.2-CAPI-
> 1.17.2" PROJ="7.1.1 NETWORK_ENABLED=OFF
> URL_ENDPOINT=https://cdn.proj.org
> USER_WRITABLE_DIRECTORY=/var/lib/pgsql/.local/share/proj
> DATABASE_PATH=/usr/proj71/share/proj/proj.db" LIBXML="2.9.1"
> LIBJSON="0.11" (core procs from "3.4.2 3.4.2" need upgrade)
> 
> $ rpm -qa postgis\*
> postgis34_17-3.4.2-4PGDG.el7.x86_64
> postgis36_18-3.6.1-1PGDG.el7.x86_64
> 
> # SELECT * FROM pg_catalog.pg_opclass WHERE opcname ILIKE 'brin%';
>   oid  | opcmethod |            opcname             | opcnamespace |
opcowner |
> opcfamily | opcintype | opcdefault | opckeytype
>
-------+-----------+--------------------------------+--------------+--------
--+-----------
> +-----------+------------+------------
>  21756 |      3580 | brin_geometry_inclusion_ops_2d |         2200 |
10 |
> 21746 |     27847 | t          |      27830
>  21768 |      3580 | brin_geometry_inclusion_ops_3d |         2200 |
10 |
> 21764 |     27847 | f          |      27859
>  21778 |      3580 | brin_geometry_inclusion_ops_4d |         2200 |
10 |
> 21774 |     27847 | f          |      27859
> 
> > Also, the data dir was originally initdb'd under centos (7, and
> > probably earlier versions before that), and most recently migrated to
> > debian (with the requisite reindex).
> 
> This time, the server is still running centos7.  I've built my own RPMs
for
> pg18 and postgis, since pgdg doesn't provides RPMs for new versions on old
> OSes.
> 
> Let me know if I can add anything else.
> 
> 
> On Sun, Mar 02, 2025 at 05:53:23PM -0500, lr at pcorp.us wrote:
> > 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 Pryzby
> System Administrator
> Telsasoft
> +1-952-707-8581