But that "invalid" index is being used by queries.... On Fri, Aug 18, 2023 at 10:41 AM David G. Johnston < david.g.johns...@gmail.com> wrote:
> On Thu, Aug 17, 2023 at 8:37 PM Stefan Knecht <knecht.ste...@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. > > https://www.postgresql.org/docs/current/sql-createindex.html > > David J. > >
