Updating to PostgreSQL 17.5

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 = a.indexrelid
join pg_catalog.pg_class as c on c.oid = a.indrelid
join pg_catalog.pg_am as d on b.relam = d.oid
join pg_catalog.pg_namespace as e on e.oid = c.relnamespace
where
d.amname = '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 = a.indexrelid
join pg_catalog.pg_class as c on c.oid = a.indrelid
join pg_catalog.pg_am as d on b.relam = d.oid
join pg_catalog.pg_namespace as e on e.oid = c.relnamespace
where
d.amname = '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;