Обсуждение: To create or not to create that index
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
^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 ?
Cheers
Stefan
On Thu, Aug 17, 2023 at 8:37 PM Stefan Knecht <knecht.stefan@gmail.com> wrote:
HelloWhy 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 existsAre 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.
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:HelloWhy 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 existsAre 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.
On Thu, Aug 17, 2023 at 8:43 PM Stefan Knecht <knecht.stefan@gmail.com> wrote:
But that "invalid" index is being used by queries....
Please don't top-post.
If it is used by queries it isn't invalid and thus its existence shouldn't be surprising. So I'm not sure what you are saying.
David J.
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:HelloWhy 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 existsAre 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.