Okay, thanks for the explanation and sorry for the false alarm!
Best,
Manuel
On Sat, Jul 6, 2019 at 12:52 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Manuel Rigger <rigger.manuel@gmail.com> writes:
> > Consider the following example:
>
> > CREATE TABLE t0(c0 INT);
> > INSERT INTO t0(c0) VALUES(0), (0);
> > CREATE UNIQUE INDEX CONCURRENTLY i0 ON t0(c0);
> > ALTER TABLE t0 SET WITH OIDS; -- expected: no error, actual: ERROR:
> > could not create unique index "i0" DETAIL: Key (c0)=(0) is
> > duplicated.
>
> > The concurrent creation of the UNIQUE INDEX fails, which is expected.
> > However, I would expect that the index is then ignored and that the
> > ALTER TABLE is unaffected.
>
> I'm afraid your expectations are too high. This isn't a bug, although the
> documentation for CREATE INDEX CONCURRENTLY perhaps needs to call out the
> hazard more explicitly. The docs already say
>
> Another caveat when building a unique index concurrently is that the
> uniqueness constraint is already being enforced against other
> transactions when the second table scan begins. This means that
> constraint violations could be reported in other queries prior to the
> index becoming available for use, or even in cases where the index
> build eventually fails. Also, if a failure does occur in the second
> scan, the “invalid” index continues to enforce its uniqueness
> constraint afterwards.
>
> Basically, once an invalid index is in place, it's going to cause you
> problems until you drop it or make it valid. This is the price of
> not taking any exclusive locks in CREATE INDEX CONCURRENTLY: we don't
> really have the option to clean up nicely after failure, because
> that'd require an exclusive table lock :-(
>
> regards, tom lane