From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kirill Reshke <reshkekirill@gmail.com>
Cc: n.bartek3762@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
Date: Wed, 29 Oct 2025 14:52:38 +0100
Message-ID: <2b831e6818fb193e4826da7d087816cdf3e56c98.camel@cybertec.at> (raw)
In-Reply-To: <CALdSSPgxP30Ly=moH9LecURsA26+WjE1hXPkHNcVeponOeLwAg@mail.gmail.com>
References: <19098-7f8deb5093c13da2@postgresql.org>
<11e73b0d2d89476fb04e6055a80638f776bb6f18.camel@cybertec.at>
<CALdSSPgxP30Ly=moH9LecURsA26+WjE1hXPkHNcVeponOeLwAg@mail.gmail.com>
On Wed, 2025-10-29 at 17:43 +0500, Kirill Reshke wrote:
> On Wed, 29 Oct 2025 at 16:31, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> >
> >
> > I think I see what you mean:
> >
> > CREATE TABLE temp (
> > id bigint NOT NULL,
> > valid tstzrange NOT NULL,
> > CONSTRAINT temp_pkey PRIMARY KEY (id, valid WITHOUT OVERLAPS)
> > );
> >
> > SELECT pg_get_indexdef('temp_pkey'::regclass);
> >
> > pg_get_indexdef
> > ----------------------------------------------------------------------
> > CREATE UNIQUE INDEX temp_pkey ON laurenz.temp USING gist (id, valid)
> >
> > That CREATE INDEX statement won't work.
> >
> > Yours,
> > Laurenz Albe
> >
> >
>
> Yep, this is probably a valid rewording of the first email. Is it a
> problem that is worth fixing?
I personally think so, although the fact that this occurs in the regression tests
might mean that it is intentional.
Anyway, here is a patch that fixes the problem for me. I am not happy with it,
because it hard-codes that CREATE UNIQUE INDEX only works with B-tree indexes,
but I don't have a better idea.
Yours,
Laurenz Albe
Attachments:
[text/x-patch] v1-0001-Fix-pg_get_indexdef-for-temporal-constraints.patch (7.3K, ../2b831e6818fb193e4826da7d087816cdf3e56c98.camel@cybertec.at/2-v1-0001-Fix-pg_get_indexdef-for-temporal-constraints.patch)
download | inline diff:
From 501de87eea0552d4693b09ff2d46059e83e6f872 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
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 <laurenz.albe@cybertec.at>
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.outindex 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.cindex 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.outindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, reshkekirill@gmail.com, n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that
In-Reply-To: <2b831e6818fb193e4826da7d087816cdf3e56c98.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox