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
Are these operations not atomic ?
Cheers
Stefan
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 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.
https://www.postgresql.org/docs/current/sql-createindex.html
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:
Show quoted text
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 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.
https://www.postgresql.org/docs/current/sql-createindex.html
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:
Show quoted text
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 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.
https://www.postgresql.org/docs/current/sql-createindex.html
David J.