From 501de87eea0552d4693b09ff2d46059e83e6f872 Mon Sep 17 00:00:00 2001 From: Laurenz Albe Date: Wed, 29 Oct 2025 14:49:49 +0100 Subject: [PATCH v1] Fix pg_get_indexdef for temporal constraints The indexes created by such constraints are marked as unique in pg_index, so pg_get_indexdef() produced a CREATE UNIQUE INDEX statement, which failed for anything but B-tree indexes. To fix, suppress the UNIQUE in the output for all except B-tree indexes. This is hacky, since it hard-codes that CREATE UNIQUE INDEX works only for B-tree indexes, which may change in the future. Perhaps it would have ben better not to set "indisunique" for such indexes, but it is too late to change that. Author: Laurenz Albe Discussion: https://postgr.es/m/19098-7f8deb5093c13da2%40postgresql.org Backpatch-To: 18 --- .../btree_gist/expected/without_overlaps.out | 6 ++-- src/backend/utils/adt/ruleutils.c | 2 +- .../regress/expected/without_overlaps.out | 36 +++++++++---------- 3 files changed, 22 insertions(+), 22 deletions(-) diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out index 18856900ded..08b518b787e 100644 --- a/contrib/btree_gist/expected/without_overlaps.out +++ b/contrib/btree_gist/expected/without_overlaps.out @@ -24,9 +24,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; - pg_get_indexdef -------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) + pg_get_indexdef +------------------------------------------------------------------------ + CREATE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) (1 row) INSERT INTO temporal_rng VALUES diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 050eef97a4c..a743ca3e286 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -1378,7 +1378,7 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, { if (!isConstraint) appendStringInfo(&buf, "CREATE %sINDEX %s ON %s%s USING %s (", - idxrec->indisunique ? "UNIQUE " : "", + (idxrec->indisunique && amrec->oid == BTREE_AM_OID) ? "UNIQUE " : "", quote_identifier(NameStr(idxrelrec->relname)), idxrelrec->relkind == RELKIND_PARTITIONED_INDEX && !inherits ? "ONLY " : "", diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index f3144bdc39c..dbf31393479 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -55,9 +55,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; - pg_get_indexdef -------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) + pg_get_indexdef +------------------------------------------------------------------------ + CREATE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) (1 row) -- PK from LIKE: @@ -148,9 +148,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; - pg_get_indexdef ---------------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng2_pk ON temporal_rng2 USING gist (id1, id2, valid_at) + pg_get_indexdef +-------------------------------------------------------------------------------- + CREATE INDEX temporal_rng2_pk ON temporal_rng2 USING gist (id1, id2, valid_at) (1 row) -- PK with a custom range type: @@ -185,9 +185,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_ml (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng_pk'; - pg_get_indexdef -------------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_mltrng_pk ON temporal_mltrng USING gist (id, valid_at) + pg_get_indexdef +------------------------------------------------------------------------------ + CREATE INDEX temporal_mltrng_pk ON temporal_mltrng USING gist (id, valid_at) (1 row) -- PK with two columns plus a multirange: @@ -214,9 +214,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_ml (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; - pg_get_indexdef ---------------------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at) + pg_get_indexdef +-------------------------------------------------------------------------------------- + CREATE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at) (1 row) -- UNIQUE with no columns just WITHOUT OVERLAPS: @@ -264,9 +264,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; - pg_get_indexdef ---------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id, valid_at) + pg_get_indexdef +-------------------------------------------------------------------------- + CREATE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id, valid_at) (1 row) DROP TABLE temporal_rng3; @@ -294,9 +294,9 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rn (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; - pg_get_indexdef ---------------------------------------------------------------------------------------- - CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id1, id2, valid_at) + pg_get_indexdef +-------------------------------------------------------------------------------- + CREATE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id1, id2, valid_at) (1 row) DROP TABLE temporal_rng3; -- 2.51.0