Re: Avoiding deadlock errors in CREATE INDEX CONCURRENTLY

On Mon, Oct 28, 2019 at 05:17:52AM +0000, imai(dot)yoshikazu(at)fujitsu(dot)com wrote:
> According to the commit 3c8404649 [1], transactional update in
> pg_index is not safe in non-MVCC catalog scans before PG9.4.
> But it seems to me that we can use transactional update in pg_index
> after the commit 813fb03155 [2] which got rid of SnapshotNow.

That's actually this part of the patch:
- /* Assert that current xact hasn't done any transactional updates */
- Assert(GetTopTransactionIdIfAny() == InvalidTransactionId);
And this thread (for commit 3c84046):
https://www.postgresql.org/message-id/19082.1349481400@sss.pgh.pa.us

And while looking at this patch, I have doubts that what you are doing
is actually safe either.

> If we apply this patch back to 9.3 or earlier, we might need to
> consider another way or take the Andres suggestion (which I don't
> understand the way fully though), but which version do you want/do
> we need to apply this patch?

Per the arguments of upthread, storing a 64-bit XID would require a
catalog change and you cannot backpatch that. I would suggest to keep
this patch focused on HEAD, and keep it as an improvement of the
existing features. Concurrent deadlock risks caused by CCI exist
since the feature came to life.

> Also, if we apply this patch in this way, there are several comments
> to be fixed which state the method of CREATE INDEX CONCURRENTLY.

Are we sure as well that all the cache lookup failures are addressed?
The CF robot does not complain per its latest status, but are we sure
to be out of the ground here?

The indentation of your patch is wrong in some places by the way.
--
Michael