Updating to PostgreSQL 17.5

[Paul Foerster]

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;