[YSQL] Spurious duplicate key constraint while creating unique index using online index backfill

[deeps1991]

Jira Link: DB-2962

Description

This error was noted while creating unique index using online index backfill:

person_core=# create unique index testidx on test(testid) include (profile_id, card_id, email_address);
ERROR:  Aborted: ERROR:  duplicate key value violates unique constraint "testidx"

However, no duplicate keys were found:

person_core=# select test_id from test group by test_id having count(test_id) > 1;

 test_id
--------------
(0 rows)

person_core=# select test_id, count(*) from test group by test_id having count(*) > 1;
 test_id | count
--------+-------
(0 rows)

Note that adding a nonconcurrent index succeeded without errors. Also note that there were concurrent INSERTs running while the DDL was happening.

[acube123]

What's the behavior when there are no INSERTs?

INSERTS could change the state of the table, and whether or not the table has the uniqueness property. If there is a possible violation,-- during backfill -- it is fair game to either fail,

• the insert, or
• index-creation.

So fair game for one implementation to fail the insert, vs another to fail the index-creation.

Are we sure that the duplicate error wasn’t “inserted”? If so, we should probably see this difference even when run in “isolation”.