Re: To create or not to create that index

On Thu, Aug 17, 2023 at 8:37 PM Stefan Knecht <knecht(dot)stefan(at)gmail(dot)com>
wrote:

> Hello
>
> Why does this happen?
>
> profile_aggregates=> create index concurrently foo_idx on agg (status,
> foots, created_ts);
>
>
> ^CCancel request sent
> ERROR: canceling statement due to user request
> profile_aggregates=>
> profile_aggregates=> create index concurrently foo_idx on agg (status,
> foots, created_ts);
> ERROR: relation " foo_idx" already exists
>
> Are these operations not atomic ?
>
>
No, being atomic would interfere with doing things concurrently. Per the
docs:

In a concurrent index build, the index is actually entered as an “invalid”
index into the system catalogs in one transaction, then two table scans
occur in two more transactions.
...
If a problem arises while scanning the table, such as a deadlock or a
uniqueness violation in a unique index, the CREATE INDEX command will fail
but leave behind an “invalid” index.

"Problem" includes you forcibly killing it while it is running.

https://www.postgresql.org/docs/current/sql-createindex.html

David J.