Re: To create or not to create that index - Mailing list pgsql-general

From Stefan Knecht
Subject Re: To create or not to create that index
Date
Msg-id CAP50yQ99dF1XLVhC34JCovh7rZR2VtkwJXZ0hQw37VDm84Mm_w@mail.gmail.com
Whole thread Raw
In response to Re: To create or not to create that index  (Stefan Knecht <knecht.stefan@gmail.com>)
List pgsql-general
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.stefan@gmail.com> wrote:
But that "invalid" index is being used by queries....

On Fri, Aug 18, 2023 at 10:41 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu, Aug 17, 2023 at 8:37 PM Stefan Knecht <knecht.stefan@gmail.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.


David J.

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: To create or not to create that index
Next
From: Bryn Llewellyn
Date:
Subject: Bryn is retiring. Last day at work Friday 18-Aug-2023