MIME-Version: 1.0
From: Marcelo Lauxen <marcelolauxen16@gmail.com>
Date: Tue, 12 May 2026 15:03:14 -0300
Message-ID: 
 <CANKisB3AG6JK1tCMp4myj75tF3t93pEK_ojECdJ=Fnin-E+J_A@mail.gmail.com>
Subject: 
 =?UTF-8?Q?pg=5Fget=5Findexdef=28=29_output_not_idempotent_for_partial_?=
	=?UTF-8?Q?indexes_with_ALL=28ARRAY=5B=E2=80=A6=5D=29=3A=3Atext=5B=5D?=
To: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f3af510651a2ad4e"
Archived-At: 
 <https://www.postgresql.org/message-id/CANKisB3AG6JK1tCMp4myj75tF3t93pEK_ojECdJ%3DFnin-E%2BJ_A%40mail.gmail.com>
Precedence: bulk

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

*PostgreSQL version*: 18.3 (Homebrew) on aarch64-apple-darwin24.6.0

*pg_get_indexdef()* produces SQL that, when executed, yields a different
pg_get_indexdef() output. This means a pg_dump =E2=86=92 pg_restore cycle s=
ilently
changes the deparsed form of partial index WHERE clauses that use NOT IN
(...) on a varchar column, causing cosmetic drift in tools that compare
index definitions (e.g. ORM schema dumps, annotation generators).

*Reproduction:*

> -- Setup
> CREATE TABLE test_idempotent (
>   id bigint PRIMARY KEY,
>   state varchar NOT NULL,
>   space_id bigint
> );
>
> -- Step 1: Create index using NOT IN
> CREATE UNIQUE INDEX idx_original ON test_idempotent (space_id)
>   WHERE state NOT IN ('completed', 'failed', 'cancelled')
>     AND space_id IS NOT NULL;
>
> -- Step 2: Capture pg_get_indexdef output
> SELECT pg_get_indexdef(indexrelid) AS pass_1
>   FROM pg_stat_user_indexes
>   WHERE indexrelname =3D 'idx_original';
>
> -- pass_1 result:
> -- CREATE UNIQUE INDEX idx_original ON public.test_idempotent USING btree
> (space_id)
> --   WHERE (((state)::text <> ALL ((ARRAY['completed'::character varying,
> --     'failed'::character varying, 'cancelled'::character
> varying])::text[]))
> --     AND (space_id IS NOT NULL))
>
> -- Step 3: Feed pass_1 output back as a new index
> CREATE UNIQUE INDEX idx_round_trip ON public.test_idempotent USING btree
> (space_id)
>   WHERE (((state)::text <> ALL ((ARRAY['completed'::character varying,
>     'failed'::character varying, 'cancelled'::character varying])::text[]=
))
>     AND (space_id IS NOT NULL));
>
> -- Step 4: Compare both
> SELECT indexrelname, pg_get_indexdef(indexrelid)
>   FROM pg_stat_user_indexes
>   WHERE indexrelname IN ('idx_original', 'idx_round_trip')
>   ORDER BY indexrelname;
>
> -- Cleanup
> DROP TABLE test_idempotent;


*Observed result (step 4):*

> idx_original   | ... WHERE (((state)::text <> ALL
> ((ARRAY['completed'::character varying, 'failed'::character varying,
> 'cancelled'::character varying])::text[])) AND (space_id IS NOT NULL))

 idx_round_trip | ... WHERE (((state)::text <> ALL
> (ARRAY[('completed'::character varying)::text, ('failed'::character
> varying)::text, ('cancelled'::character varying)::text])) AND (space_id I=
S
> NOT NULL))


The array-level cast (ARRAY[...])::text[] is redistributed to per-element
casts ARRAY[(...::text), (...::text), (...::text)] after a single
parse=E2=86=92deparse cycle.

*Expected result:*

Both indexes should produce identical pg_get_indexdef() output since
idx_round_trip was created using the exact SQL that pg_get_indexdef()
produced for idx_original.

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

<div dir=3D"ltr"><b>PostgreSQL version</b>: 18.3 (Homebrew) on <span aria-i=
nvalid=3D"spelling" class=3D"LI ng" style=3D"border-style:none;background:n=
one">aarch64-apple-darwin24.6.0</span>


<div><br></div><div><i>pg_get_indexdef()</i> produces SQL that, when execut=
ed, yields a different pg_get_indexdef() output. This means a pg_dump =E2=
=86=92 pg_restore cycle silently changes the <span aria-invalid=3D"spelling=
" class=3D"LI ng" style=3D"border-style:none;background:none">deparsed</spa=
n> form of partial index WHERE clauses that use NOT IN (...) on a varchar c=
olumn, causing cosmetic drift in tools that compare index definitions (e.g.=
 ORM schema dumps, annotation generators).</div><div><br></div><div><b>Repr=
oduction:</b></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">-- Set=
up<br>CREATE TABLE test_idempotent (<br>=C2=A0 id bigint PRIMARY KEY,<br>=
=C2=A0 state varchar NOT NULL,<br>=C2=A0 space_id bigint<br>);<br><br>-- St=
ep 1: Create index using NOT IN<br>CREATE UNIQUE INDEX idx_original ON test=
_idempotent (space_id)<br>=C2=A0 WHERE state NOT IN (&#39;completed&#39;, &=
#39;failed&#39;, &#39;cancelled&#39;)<br>=C2=A0 =C2=A0 AND space_id IS NOT =
NULL;<br><br>-- Step 2: Capture pg_get_indexdef output<br>SELECT pg_get_ind=
exdef(indexrelid) AS pass_1<br>=C2=A0 FROM pg_stat_user_indexes<br>=C2=A0 W=
HERE indexrelname =3D &#39;idx_original&#39;;<br><br>-- pass_1 result:<br>-=
- CREATE UNIQUE INDEX idx_original ON public.test_idempotent USING btree (s=
pace_id)<br>-- =C2=A0 WHERE (((state)::text &lt;&gt; ALL ((ARRAY[&#39;compl=
eted&#39;::character varying,<br>-- =C2=A0 =C2=A0 &#39;failed&#39;::charact=
er varying, &#39;cancelled&#39;::character varying])::text[]))<br>-- =C2=A0=
 =C2=A0 AND (space_id IS NOT NULL))<br><br>-- Step 3: Feed pass_1 output ba=
ck as a new index<br>CREATE UNIQUE INDEX idx_round_trip ON public.test_idem=
potent USING btree (space_id)<br>=C2=A0 WHERE (((state)::text &lt;&gt; ALL =
((ARRAY[&#39;completed&#39;::character varying,<br>=C2=A0 =C2=A0 &#39;faile=
d&#39;::character varying, &#39;cancelled&#39;::character varying])::text[]=
))<br>=C2=A0 =C2=A0 AND (space_id IS NOT NULL));<br><br>-- Step 4: Compare =
both<br>SELECT indexrelname, pg_get_indexdef(indexrelid)<br>=C2=A0 FROM pg_=
stat_user_indexes<br>=C2=A0 WHERE indexrelname IN (&#39;idx_original&#39;, =
&#39;idx_round_trip&#39;)<br>=C2=A0 ORDER BY indexrelname;<br><br>-- Cleanu=
p<br>DROP TABLE test_idempotent;</blockquote><div><br></div><div><b>Observe=
d result (step 4):</b></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x">idx_original =C2=A0 | ... WHERE (((state)::text &lt;&gt; ALL ((ARRAY[=
9;completed&#39;::character varying, &#39;failed&#39;::character varying, &=
#39;cancelled&#39;::character varying])::text[])) AND (space_id IS NOT NULL=
))</blockquote><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=C2=A0idx_=
round_trip | ... WHERE (((state)::text &lt;&gt; ALL (ARRAY[(&#39;completed&=
#39;::character varying)::text, (&#39;failed&#39;::character varying)::text=
, (&#39;cancelled&#39;::character varying)::text])) AND (space_id IS NOT NU=
LL))=C2=A0</blockquote><div><br>The array-level cast (ARRAY[...])::text[] i=
s redistributed to per-element casts ARRAY[(...::text), (...::text), (...::=
text)] after a single parse=E2=86=92deparse cycle.<br><br><b>Expected resul=
t:</b><br><br>Both indexes should produce identical pg_get_indexdef() outpu=
t since idx_round_trip was created using the exact SQL that pg_get_indexdef=
() produced for idx_original.=C2=A0</div></div>

--000000000000f3af510651a2ad4e--