From: Paul Foerster <paul.foerster@gmail.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.500.181.1.5\))
Subject: Updating to PostgreSQL 17.5
Message-Id: <492F08D2-73F0-4CB7-85C7-8602304A448F@gmail.com>
Date: Sun, 11 May 2025 20:08:14 +0200
To: Pgsql-General List <pgsql-general@lists.postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/492F08D2-73F0-4CB7-85C7-8602304A448F%40gmail.com>
Precedence: bulk

Hi,

the release notes for PostgreSQL 17.5 =
(https://www.postgresql.org/docs/17/release-17-5.html) state:

"Also, if you have any BRIN bloom indexes, it may be advisable to =
reindex them after updating."

I don't know what exactly that means. So I read about BRIN and BLOOM =
indexes and learned how to create them using the "USING BRIN..." or =
"USING BLOOM..." clause. But there is no such thing as "USING BRIN =
BLOOM" or other variation. After quite some research, I only found that =
there may be a BRIN and a BLOOM index on the same table, created by two =
individual CREATE INDEX commands. As I understand, those are the ones =
referred to in the release notes.

Also, the \di+ command wasn't much of a help in this case. So I came up =
with the following query which also creates the necessary reindex =
commands. Please let me know if my query below hits the wanted indexes =
or not.

I'd be really cool if release notes contained the necessary queries to =
find the objects in question.

Thanks in advance,
Paul


with
    brin_indexes as (
        select
            e.nspname as schema_name,
            c.relname as table_name,
            b.relname as index_name
        from
            pg_catalog.pg_index as a
            join pg_catalog.pg_class as b on b.oid =3D a.indexrelid
            join pg_catalog.pg_class as c on c.oid =3D a.indrelid
            join pg_catalog.pg_am as d on b.relam =3D d.oid
            join pg_catalog.pg_namespace as e on e.oid =3D =
c.relnamespace
        where
            d.amname =3D 'brin'
    ),
    bloom_indexes as (
        select
            e.nspname as schema_name,
            c.relname as table_name,
            b.relname as index_name
        from
            pg_catalog.pg_index as a
            join pg_catalog.pg_class as b on b.oid =3D a.indexrelid
            join pg_catalog.pg_class as c on c.oid =3D a.indrelid
            join pg_catalog.pg_am as d on b.relam =3D d.oid
            join pg_catalog.pg_namespace as e on e.oid =3D =
c.relnamespace
        where
            d.amname =3D 'bloom'
    )
select
    concat ('reindex index "', schema_name, '"."', index_name, '"; -- =
brin index') as reindex_cmd
from
    brin_indexes
where
    schema_name in (select schema_name from bloom_indexes) and
    table_name in (select table_name from bloom_indexes)
union all
select
    concat ('reindex index "', schema_name, '"."', index_name, '"; -- =
bloom index') as reindex_cmd
from
    bloom_indexes
where
    schema_name in (select schema_name from brin_indexes) and
    table_name in (select table_name from brin_indexes)
order by
    reindex_cmd;=