Re: To create or not to create that index

Ah no it is not. Something else was changed at the same time. Sigh.

Thanks for clarifying David

On Fri, Aug 18, 2023 at 10:42 AM Stefan Knecht <knecht(dot)stefan(at)gmail(dot)com>
wrote:

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