MIME-Version: 1.0
References: <CAKZpw=dSX2yHq6M1aVXH0ep-kgW9a3tre=BPRcC-R2-nZC5nJQ@mail.gmail.com>
In-Reply-To: <CAKZpw=dSX2yHq6M1aVXH0ep-kgW9a3tre=BPRcC-R2-nZC5nJQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 17 Jan 2025 10:00:20 -0500
Message-ID: <CANzqJaAfHO_pY4E=ja83jmhuLZbX2zxeub9wABApmuOV-W0-Ng@mail.gmail.com>
Subject: Re: glibc 2.35-2.39 upgrade requirements
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000003e99f062be82c32"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAfHO_pY4E%3Dja83jmhuLZbX2zxeub9wABApmuOV-W0-Ng%40mail.gmail.com>
Precedence: bulk

--00000000000003e99f062be82c32
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Jan 17, 2025 at 1:12=E2=80=AFAM Kamen Kalchev <kalchev035@gmail.com=
> wrote:

> Hi everyone, we're planning to upgrade the OS running Postgres from ubunt=
u
> jammy to ubuntu noble. As part of the OS change, the glibc version will b=
e
> changed from glibc 2.35 to glibc 2.39..
>
> Can someone confirm if changing the glibc between those versions will
> require a full reindex of the Postgres cluster?
>

You never have to reindex _everything_.  Only (for some definition of
"only") indices with text/char/varchar/name columns need to be rebuilt.

This is how I find such indices:
create or replace view dba.all_indices_types as
    select tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname as
table_name
            , ndcl.relname as index_name
            , array_agg(ty.typname order by att.attnum) as index_types
    from pg_class ndcl
        inner join pg_index nd
            on (ndcl.oid =3D nd.indexrelid and ndcl.relkind in ('i', 'I')
        inner join pg_class tbcl
            on (nd.indrelid =3D tbcl.oid and tbcl.relkind in ('r', 'R', 'm'=
))
        inner join pg_attribute att
            on att.attrelid =3D nd.indexrelid
        inner join pg_type ty
            on att.atttypid =3D ty.oid
    where tbcl.relnamespace::regnamespace::text !=3D 'pg_catalog'
    group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
            , ndcl.relname
    order by 1, 2;
select * from dba.all_indices_types where index_types &&
'{"text","varchar","char","text"}';


--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--00000000000003e99f062be82c32
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jan 17, 2025 at 1:12=E2=80=AFAM K=
amen Kalchev &lt;<a href=3D"mailto:kalchev035@gmail.com">kalchev035@gmail.c=
om</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><span style=
=3D"color:rgb(0,0,0)"><div>Hi everyone, we&#39;re planning to upgrade the O=
S running Postgres from ubuntu jammy to ubuntu noble. As part of the OS cha=
nge, the glibc version will be changed from glibc 2.35 to glibc 2.39..<span=
>=C2=A0</span><br><br>Can someone confirm if changing the glibc between tho=
se versions will require a full reindex of the Postgres cluster?<span>=C2=
=A0</span></div></span></div></blockquote><div><br></div><div></div></div><=
div>You never have to reindex _everything_.=C2=A0 Only (for some definition=
 of &quot;only&quot;) indices with text/char/varchar/name columns need to b=
e rebuilt.</div><div><br></div><div>This is how I find such indices:</div><=
div><font face=3D"monospace">create or replace view dba.all_indices_types a=
s<br>=C2=A0 =C2=A0 select tbcl.relnamespace::regnamespace::text||&#39;.&#39=
;||tbcl.relname as table_name<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
, ndcl.relname as index_name<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ,=
 array_agg(ty.typname order by att.attnum) as index_types<br>=C2=A0 =C2=A0 =
from pg_class ndcl<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join pg_index nd<br=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 on (ndcl.oid =3D nd.indexrelid a=
nd ndcl.relkind in (&#39;i&#39;, &#39;I&#39;)<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 inner join pg_class tbcl<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 o=
n (nd.indrelid =3D tbcl.oid and tbcl.relkind in (&#39;r&#39;, &#39;R&#39;, =
&#39;m&#39;))<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join pg_attribute att<br=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 on att.attrelid =3D nd.indexreli=
d<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join pg_type ty<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 on att.atttypid =3D ty.oid<br>=C2=A0 =C2=A0 where =
tbcl.relnamespace::regnamespace::text !=3D &#39;pg_catalog&#39;<br>=C2=A0 =
=C2=A0 group by tbcl.relnamespace::regnamespace::text||&#39;.&#39;||tbcl.re=
lname<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 , ndcl.relname<br>=C2=A0=
 =C2=A0 order by 1, 2;<br></font></div><div><font face=3D"monospace">select=
 * from dba.all_indices_types where index_types &amp;&amp; &#39;{&quot;text=
&quot;,&quot;varchar&quot;,&quot;char&quot;,&quot;text&quot;}&#39;;</font><=
/div><div><br></div><div><br></div><span class=3D"gmail_signature_prefix">-=
- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">De=
ath to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m s=
till alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div><=
/div></div>

--00000000000003e99f062be82c32--