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

[Justin Pryzby]

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