Re: Avoiding deadlock errors in CREATE INDEX CONCURRENTLY

Hello,

On Wed, May 15, 2019 at 1:45 PM Goel, Dhruv <goeldhru(at)amazon(dot)com> wrote:

>
>
> Proposed Solution:
>
> We remove the third wait state completely from the concurrent index build.
> When we mark the index as ready, we also mark “indcheckxmin” to true which
> essentially enforces Postgres to not use this index for older snapshots.
>
>
>
I think there is a problem in the proposed solution. When phase 3 is
reached, the index is valid. But it might not contain tuples deleted
just before the reference snapshot was taken. Hence, we wait for those
transactions that might have older snapshot. The TransactionXmin of these
transactions can be greater than the xmin of the pg_index entry for this
index.
Instead of waiting in the third phase, if we just set indcheckxmin as true,
the above transactions will be able to use the index which is wrong.
(because they won't find the recently deleted tuples from the index that
are still live according to their snapshots)

The respective code from get_relation_info:
if (index->indcheckxmin &&

!TransactionIdPrecedes(HeapTupleHeaderGetXmin(indexRelation->rd_indextuple->t_data),
TransactionXmin))
{ /* don't use this index */ }

Please let me know if I'm missing something.

--
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com