MIME-Version: 1.0
References: <CAHzebO-2qy2RkrhTvqPs8=6Ju_6-7C=MSumVMZxTs7zRYp-6mA@mail.gmail.com>
 <20250224023225.197cac12@ardentperf.com> <CAHzebO92PxW=e2FLN=ZSkMEvfoS+c-b9YKQNgj0Y3hj21pMw+A@mail.gmail.com>
 <CAFCRh--NUP9-g08QDiCX2Yntqea1tPMk01OgYOD7uhsAja==VQ@mail.gmail.com> <CAHzebO8SMasPTACdMASnSXY29WzuOEe1GBW+xgqBOn6pf+mPrQ@mail.gmail.com>
In-Reply-To: <CAHzebO8SMasPTACdMASnSXY29WzuOEe1GBW+xgqBOn6pf+mPrQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 24 Feb 2025 09:59:00 -0500
Message-ID: <CANzqJaD=5s2TRmnuArvF5JNXcRVo-DWGns_NpxF3ZJzt563C8Q@mail.gmail.com>
Subject: Re: COLLATION update in 13.1
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000578cd5062ee49566"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD%3D5s2TRmnuArvF5JNXcRVo-DWGns_NpxF3ZJzt563C8Q%40mail.gmail.com>
Precedence: bulk

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

On Mon, Feb 24, 2025 at 6:53=E2=80=AFAM Matthias Apitz <gurucubano@googlema=
il.com>
wrote:
[snip]

> pgsql -Usisis sisis
>
> sisis=3D# REINDEX (VERBOSE) DATABASE sisis;
> sisis=3D# ALTER COLLATION "de_DE.utf8" REFRESH VERSION;
> ALTER COLLATION
>
> Correct?
>
>
Just reindex those with text columns.

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 =3D 'i')
        inner join pg_class tbcl
            on (nd.indrelid =3D tbcl.oid and tbcl.relkind =3D 'r')
        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"}';

(This view might not handle indices on the parents of declared-partition
tables.)

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Feb 24, 2025 at 6:53=E2=80=AFAM M=
atthias Apitz &lt;<a href=3D"mailto:gurucubano@googlemail.com">gurucubano@g=
ooglemail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_con=
tainer"><div>[snip]=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex"><div dir=3D"ltr"><div></div><div>pgsql -Usisis sisis</div><div><pre>s=
isis=3D# REINDEX (VERBOSE) DATABASE sisis;<br>sisis=3D# ALTER COLLATION &qu=
ot;de_DE.utf8&quot; REFRESH VERSION;<br>ALTER COLLATION<br><br></pre><pre>C=
orrect?</pre></div></div></blockquote><div><br></div><div></div></div><div>=
Just reindex those with text columns.</div><div><br></div><div><font face=
=3D"monospace">create or replace view dba.all_indices_types as<br>=C2=A0 =
=C2=A0 select tbcl.relnamespace::regnamespace::text||&#39;.&#39;||tbcl.reln=
ame as table_name<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 , ndcl.relna=
me as index_name<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 , array_agg(t=
y.typname order by att.attnum) as index_types<br>=C2=A0 =C2=A0 from pg_clas=
s 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 and ndcl.rel=
kind =3D &#39;i&#39;)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join pg_class tb=
cl<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 on (nd.indrelid =3D tbcl.oi=
d and tbcl.relkind =3D &#39;r&#39;)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner jo=
in pg_attribute att<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 on att.att=
relid =3D nd.indexrelid<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 inner join pg_type t=
y<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 on att.atttypid =3D ty.oid<b=
r>=C2=A0 =C2=A0 where tbcl.relnamespace::regnamespace::text !=3D &#39;pg_ca=
talog&#39;<br>=C2=A0 =C2=A0 group by tbcl.relnamespace::regnamespace::text|=
|&#39;.&#39;||tbcl.relname<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 , n=
dcl.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;}&#39;;</fo=
nt></div><div><br></div><div>(This view might not handle indices on the par=
ents of declared-partition tables.)</div><div><br></div><span class=3D"gmai=
l_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature=
"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39=
;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</di=
v></div></div></div></div></div>

--000000000000578cd5062ee49566--