Re: remove spurious CREATE INDEX CONCURRENTLY wait

Alvaro Herrera <alvherre@alvh.no-ip.org>

From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Dmitry Dolgov <9erthalion6@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, James Coleman <jtc331@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2020-11-26T19:56:15Z
Lists: pgsql-hackers
So let's discuss the next step in this series: what to do about REINDEX
CONCURRENTLY.

I started with Dmitry's patch (an updated version of which I already
posted in [1]).  However, Dmitry missed (and I hadn't noticed) that some
of the per-index loops are starting transactions also, and that we need
to set the flag in those.  And what's more, in a couple of the
function-scope transactions we do set the flag pointlessly: the
transactions there do not acquire a snapshot, so there's no reason to
set the flag at all, because WaitForOlderSnapshots ignores sessions
whose Xmin is 0.

There are also transactions that wait first, without setting a snapshot,
and then do some catalog manipulations.  I think it's prett much useless
to set the flag for those, because they're going to be very short
anyway.  (There's also one case of this in CREATE INDEX CONCURRENTLY.)

But there's a more interesting point also.  In Dmitry's patch, we
determine safety for *all* indexes being processed as a set, and then
apply the flag only if they're all deemed safe.  But we can optimize
this, and set the flag for each index' transaction individually, and
only skip it for those specific indexes that are unsafe.  So I propose
to change the data structure used in ReindexRelationConcurrently from
the current list of OIDs to a list of (oid,boolean) pairs, to be able to
track setting the flag individually.

There's one more useful observation: in the function-scope transactions
(outside the per-index loops), we don't touch the contents of any
indexes; we just wait or do some catalog manipulation.  So we can set
the flag *regardless of the safety of any indexes*.  We only need to
care about the safety of the indexes in the phases where we build the
indexes and when we validate them.


[1] https://postgr.es/m/20201118175804.GA23027@alvherre.pgsql



Commits

  1. Restore lock level to set vacuum flags

  2. Restore lock level to update statusFlags

  3. Avoid spurious waits in concurrent indexing

  4. Centralize logic for skipping useless ereport/elog calls.

  5. Don't hold ProcArrayLock longer than needed in rare cases

  6. Relax lock level for setting PGPROC->statusFlags

  7. Rename PGPROC->vacuumFlags to statusFlags

  8. snapshot scalability: Move PGXACT->vacuumFlags to ProcGlobal->vacuumFlags.

  9. Allow an autovacuum worker to be interrupted automatically when it is found