Re: To create or not to create that index

But that "invalid" index is being used by queries....

On Fri, Aug 18, 2023 at 10:41 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
>
>