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
pryzby at telsasoft.com
Thu Feb 12 08:05:12 PST 2026
I've hit this 2 or 3 times now on different customers. We usually
pg_upgrade to new major versions pretty quickly after they're released,
and maybe this issue is fallout from that.
This instance started with pg14 and was pg_upgraded every year since
then. This is the version history for postgres:
postgres=# SELECT t::date, name, setting FROM pg_settings_snap WHERE name='server_version_num' ORDER BY 1;
t | name | setting
------------+--------------------+---------
2021-11-12 | server_version_num | 140000
2022-02-11 | server_version_num | 140001
2022-05-13 | server_version_num | 140002
2022-06-20 | server_version_num | 140003
2022-08-12 | server_version_num | 140004
2022-10-20 | server_version_num | 140005
2022-11-11 | server_version_num | 150000
2023-02-21 | server_version_num | 150001
2023-05-11 | server_version_num | 150002
2023-08-10 | server_version_num | 150003
2023-09-20 | server_version_num | 150004
2023-11-07 | server_version_num | 160000
2024-02-06 | server_version_num | 160001
2024-05-10 | server_version_num | 160002
2024-08-09 | server_version_num | 160003
2024-09-30 | server_version_num | 160004
2024-11-12 | server_version_num | 170000
2024-11-20 | server_version_num | 170001
2025-02-19 | server_version_num | 170002
2025-05-06 | server_version_num | 170004
2025-08-20 | server_version_num | 170005
2026-02-12 | server_version_num | 170006
2026-02-12 | server_version_num | 180002
Here's the available version history for postgis:
$ sudo grep gis /var/log/yum.log-* /var/log/yum.log
/var/log/yum.log-20230101:Oct 20 18:08:51 Installed: postgis33_15-3.3.1-2.rhel7.x86_64
/var/log/yum.log-20230101:Oct 20 18:08:51 Installed: postgis33_15-client-3.3.1-2.rhel7.x86_64
/var/log/yum.log-20240101:Sep 20 05:43:25 Installed: postgis34_16-3.4.0-1PGDG.el7.x86_64
/var/log/yum.log-20240101:Nov 30 03:45:49 Updated: postgis34_16-3.4.1-1PGDG.el7.x86_64
/var/log/yum.log-20250101:Aug 09 05:42:49 Updated: postgis34_16-3.4.2-1PGDG.el7.x86_64
/var/log/yum.log-20250101:Sep 30 04:57:32 Installed: postgis34_17-3.4.2-4PGDG.el7.x86_64
/var/log/yum.log:Feb 10 05:20:59 Installed: postgis36_18-3.6.1-1PGDG.el7.x86_64
This package is still installed, and might indicate the oldest postgis
that it started with:
postgis31_14-docs-3.1.4-1.rhel7.x86_64
On Thu, Feb 12, 2026 at 10:09:31AM -0500, Regina Obe wrote:
> 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
More information about the postgis-devel
mailing list