Re: [PATCH] Clarify CREATE INDEX IF NOT EXISTS output for invalid indexes

From: Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>
To: Richard Yen <richyen3(at)gmail(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: [PATCH] Clarify CREATE INDEX IF NOT EXISTS output for invalid indexes
Date: 2026-11-19 06:56:49
Message-ID: CAExHW5v2-dQ9o5TZpUzOGcBoAc97-HPcM2AhwkXPoyaa8J3QTQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Sun, Nov 8, 2026 at 12:27 AM Richard Yen <richyen3(at)gmail(dot)com> wrote:

> Hello Hackers,
>
> I've encountered a number of users (mostly people who do not use a
> database on a day-to-day basis, such as consultants and support engineers,
> and therefore unfamiliar with the state of the database they're working
> with) who have experienced challenges when attempting to use CREATE INDEX
> IF NOT EXISTS. Specifically, if the index creation was attempted
> previously but failed, the CREATE INDEX IF NOT EXISTS syntax happily
> reports that the index exists, but falls short of identifying it as
> invalid. In more than one instance, such users lose a fair amount of time
> wondering why a query is not choosing the index, only to discover minutes
> (or hours) later that it is an invalid index.
>
>
There may be other reasons why a query does not use an existing index. Some
related to how an index gets used e.g. the optimizer didn't think it's
optimal to use the index or some that are related to the properties of the
index itself like your case or simply the index doesn't cover all the
columns required. Users issuing CREATE INDEX IF NOT EXISTS should
understand that it checks name existence, not whether the existing object
is a usable or equivalent index; this is already documented. Why should
invalidity receive special treatment among the properties that are not
checked?

To me it looks like a tool or set of tools which will evaluate why an index
is not being used by a query should be used here, instead of adding a
detail in the error message. That will be more comprehensive.

Do we have similar proposals in the past? Collectively they may point to
the right solution.

> As such, I'm proposing a small patch to make the notice from CREATE INDEX
> IF NOT EXISTS more informative when the existing index is invalid.
>
> The patch preserves the existing notice and adds detail when the
> conflicting relation has pg_index.indisvalid = false:
>
> NOTICE: relation "idx_test" already exists, skipping
> DETAIL: The existing index is invalid.
>
> Would this additional detail be useful? Comments on the wording and
> approach are welcome.
>
>
Independent of my opinion above, here are some comments about the patch
itself

Do not throw an error if a relation with the same name already
exists.
- A notice is issued in this case. Note that there is no guarantee
that
+ A notice is issued in this case, with additional detail if the
existing
+ relation is an invalid index. The existing index is not rebuilt or
+ validated. Note that there is no guarantee that

I suggest leaving the documentation unchanged here. It already states that
the existing index is not guaranteed to be anything like the one that would
have been created; I don't think we need to document this particular
diagnostic detail. The clarification that the existing index is not rebuilt
or validated describes existing semantics and, if needed, could be proposed
separately.

- if (get_relname_relid(indexRelationName, namespaceId))
+ existingRelationId = get_relname_relid(indexRelationName, namespaceId);
+ if (OidIsValid(existingRelationId))
{
if ((flags & INDEX_CREATE_IF_NOT_EXISTS) != 0)

Why is CREATE INDEX IF NOT EXISTS special? Why not report it for CREATE
INDEX as well?

+-- The conflicting relation need not be an index.
+CREATE INDEX IF NOT EXISTS onek ON onek USING btree(unique1 int4_ops);
+NOTICE: relation "onek" already exists, skipping

This tests existing behavior for a conflicting non-index relation, which
the patch does not change. If that behavior is not already covered, adding
coverage would be useful, but I suggest doing so separately.

+-- IF NOT EXISTS reports that the existing index is invalid, without
rebuilding it.
+CREATE UNIQUE INDEX IF NOT EXISTS concur_index3 ON concur_heap(f2);
+NOTICE: relation "concur_index3" already exists, skipping
+DETAIL: The existing index is invalid.

This test fits this patch.

+CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS concur_index3 ON
concur_heap(f2);
+NOTICE: relation "concur_index3" already exists, skipping
+DETAIL: The existing index is invalid.

This appears to exercise the same newly added lookup and diagnostic branch
as the preceding test. What additional behavior or failure mode does
CONCURRENTLY cover here? Unless there is a distinct risk, I suggest keeping
just the preceding test.

+-- An index created on ONLY a partitioned table is also invalid.
+CREATE TABLE invalid_index_heap (a int) PARTITION BY RANGE (a);
+CREATE TABLE invalid_index_partition PARTITION OF invalid_index_heap
+ FOR VALUES FROM (0) TO (10);
+CREATE INDEX invalid_index ON ONLY invalid_index_heap (a);
+CREATE INDEX IF NOT EXISTS invalid_index ON invalid_index_heap (a);
+NOTICE: relation "invalid_index" already exists, skipping
+DETAIL: The existing index is invalid.
+DROP TABLE invalid_index_heap;

Although this covers a partitioned index, the new code checks indisvalid
without branching on the relation kind. What additional failure mode does
this test protect against beyond the preceding invalid-index test? Unless
there is a distinct risk, I suggest omitting it.

--
Best Wishes,
Ashutosh Bapat

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro Horiguchi 2026-11-19 07:00:46 Re: [PATCH] Reject empty string in option_parse_int()
Previous Message Zhijie Hou 2026-11-19 06:32:37 Re: Detect mismatched subscription and replication slot properties