Re: Avoiding deadlock errors in CREATE INDEX CONCURRENTLY

> On Jun 10, 2019, at 1:20 PM, Goel, Dhruv <goeldhru(at)amazon(dot)com> wrote:
>
>
>> On Jun 9, 2019, at 5:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> Andres Freund <andres(at)anarazel(dot)de> writes:
>>> On June 9, 2019 8:36:37 AM PDT, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>> I think you are mistaken that doing transactional updates in pg_index
>>>> is OK. If memory serves, we rely on xmin of the pg_index row for
>>>> purposes such as detecting whether a concurrently-created index is safe
>>>> to use yet.
>
> I took a deeper look regarding this use case but was unable to find more evidence. As part of this patch, we essentially make concurrently-created index safe to use only if transaction started after the xmin of Phase 3. Even today concurrent indexes can not be used for transactions before this xmin because of the wait (which I am trying to get rid of in this patch), is there any other denial of service you are talking about? Both the other states indislive, indisready can be transactional updates as far as I understand. Is there anything more I am missing here?

Hi,

I did some more concurrency testing here through some python scripts which compare the end state of the concurrently created indexes. I also back-ported this patch to PG 9.6 and ran some custom concurrency tests (Inserts, Deletes, and Create Index Concurrently) which seem to succeed. The intermediate states unfortunately are not easy to test in an automated manner, but to be fair concurrent indexes could never be used for older transactions. Do you have more inputs/ideas on this patch?

Thanks,
Dhruv