| From: | Marcelo Lauxen <marcelolauxen16(at)gmail(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | pg_get_indexdef() output not idempotent for partial indexes with ALL(ARRAY[…])::text[] |
| Date: | 2026-05-12 18:03:14 |
| Message-ID: | CANKisB3AG6JK1tCMp4myj75tF3t93pEK_ojECdJ=Fnin-E+J_A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
*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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Ayush Tiwari | 2026-05-12 18:04:21 | Re: BUG #19476: Segmentation fault in contrib/spi |
| Previous Message | PG Bug reporting form | 2026-05-12 14:23:39 | BUG #19476: Segmentation fault in contrib/spi |