Re: Avoiding deadlock errors in CREATE INDEX CONCURRENTLY

On Sun, Jun 30, 2019 at 7:30 PM Goel, Dhruv <goeldhru@amazon.com> wrote:
> > On Jun 10, 2019, at 1:20 PM, Goel, Dhruv <goeldhru@amazon.com> wrote:
> >> On Jun 9, 2019, at 5:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Andres Freund <andres@anarazel.de> writes:
> >>> On June 9, 2019 8:36:37 AM PDT, Tom Lane <tgl@sss.pgh.pa.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
essentiallymake concurrently-created index safe to use only if transaction started after the xmin of Phase 3. Even
todayconcurrent indexes can not be used for transactions before this xmin because of the wait (which I am trying to get
ridof in this patch), is there any other denial of service you are talking about? Both the other states indislive,
indisreadycan be transactional updates as far as I understand. Is there anything more I am missing here? 
>
> I did some more concurrency testing here through some python scripts which compare the end state of the concurrently
createdindexes. I also back-ported this patch to PG 9.6 and ran some custom concurrency tests (Inserts, Deletes, and
CreateIndex Concurrently) which seem to succeed. The intermediate states unfortunately are not easy to test in an
automatedmanner, but to be fair concurrent indexes could never be used for older transactions. Do you have more
inputs/ideason this patch? 

I noticed that check-world passed several times with this patch
applied, but the most recent CI run failed in multiple-cic:

+error in steps s2i s1i: ERROR:  cache lookup failed for index 26303

https://travis-ci.org/postgresql-cfbot/postgresql/builds/555472214

--
Thomas Munro
https://enterprisedb.com