public inbox for [email protected]
help / color / mirror / Atom feed pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[]
3+ messages / 2 participants
[nested] [flat]
* pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[]
@ 2026-05-12 18:03 Marcelo Lauxen <[email protected]>
2026-05-13 13:51 ` Re: pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[] Tom Lane <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Marcelo Lauxen @ 2026-05-12 18:03 UTC (permalink / raw)
To: [email protected]
*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 → pg_restore cycle silently
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 = '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 IS
> NOT NULL))
The array-level cast (ARRAY[...])::text[] is redistributed to per-element
casts ARRAY[(...::text), (...::text), (...::text)] after a single
parse→deparse 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[]
2026-05-12 18:03 pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[] Marcelo Lauxen <[email protected]>
@ 2026-05-13 13:51 ` Tom Lane <[email protected]>
2026-05-13 14:25 ` Re: pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[] Marcelo Lauxen <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2026-05-13 13:51 UTC (permalink / raw)
To: Marcelo Lauxen <[email protected]>; +Cc: [email protected]
Marcelo Lauxen <[email protected]> writes:
> *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 → pg_restore cycle silently
> 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).
You are assuming a property that we've never guaranteed and don't plan
to start guaranteeing, ie that the output of expression decompilation
matches the input even in semantically-insignificant details.
My own advice about how to fix this particular example is not to use
varchar --- especially not unconstrained varchar, which doesn't even
have the thin excuse of being spec-compliant. Postgres' native string
type is text.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[]
2026-05-12 18:03 pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[] Marcelo Lauxen <[email protected]>
2026-05-13 13:51 ` Re: pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[] Tom Lane <[email protected]>
@ 2026-05-13 14:25 ` Marcelo Lauxen <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Marcelo Lauxen @ 2026-05-13 14:25 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
Gotcha, it wasn't clear to me that this was never guaranteed. I will change
the type of this column to text to resolve this.
Appreciate the quick response!
Regards,
Marcelo
On Wed, May 13, 2026 at 10:51 AM Tom Lane <[email protected]> wrote:
> Marcelo Lauxen <[email protected]> writes:
> > *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 → pg_restore cycle
> silently
> > 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).
>
> You are assuming a property that we've never guaranteed and don't plan
> to start guaranteeing, ie that the output of expression decompilation
> matches the input even in semantically-insignificant details.
>
> My own advice about how to fix this particular example is not to use
> varchar --- especially not unconstrained varchar, which doesn't even
> have the thin excuse of being spec-compliant. Postgres' native string
> type is text.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-05-13 14:25 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-12 18:03 pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[] Marcelo Lauxen <[email protected]>
2026-05-13 13:51 ` Tom Lane <[email protected]>
2026-05-13 14:25 ` Marcelo Lauxen <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox