Horton
Menu

Re: CREATE INDEX CONCURRENTLY does not index prepared xact's data

Noah Misch <noah@leadboat.com>

View thread
From: Noah Misch <noah@leadboat.com>
To: Andres Freund <andres@anarazel.de>, Andrey Borodin <x4mmm@yandex-team.ru>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>, Michael Paquier <michael@paquier.xyz>, Tom Lane <tgl@sss.pgh.pa.us>, Peter Geoghegan <pg@bowt.ie>
Date: 2021-09-08T06:45:15Z
Lists: pgsql-bugs 
On Sun, Aug 29, 2021 at 11:38:03PM +0500, Andrey Borodin wrote:
> > 29 авг. 2021 г., в 23:09, Andres Freund <andres@anarazel.de> написал(а):
> >>> It seems like it's going to add a substantial amount of work even when
> >>> no 2PC xacts are involved?
> >> Only if 2PCs are enabled.
> > 
> > I don't think that's good enough. Plenty of systems have 2PC enabled but very
> > few if any transactions end up as 2PC ones.

> Best optimisation I can imagine here is to gather all vxids with unknown xids and search for them in one call to TwoPhaseGetXidByVXid() with one LWLockAcquire(TwoPhaseStateLock, LW_SHARED).
> 
> Does it worth the complexity?

https://www.postgresql.org/search/?m=1&q=TwoPhaseStateLock&l=&d=-1&s=r
suggests this is the first postgresql.org discussion of TwoPhaseStateLock as a
bottleneck.  Nonetheless, if Andres Freund finds it's worth the complexity,
then I'm content with it.  I'd certainly expect some performance benefit.
Andres, what do you think?

We could start with an unlocked scan of the twophase shared memory.  If the
unlocked scan finds a potential match, acquire TwoPhaseStateLock and repeat
the scan.  Otherwise, we don't need the locked scan, because we can deduce
that the locked scan would find nothing.  I'm not fond of relying on such
reasoning without a known-strong performance need, but it's an alternative.

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Improve contrib/amcheck's tests for CREATE INDEX CONCURRENTLY.

  2. Fix minor memory leaks in pg_dump.

  3. Avoid race in RelationBuildDesc() affecting CREATE INDEX CONCURRENTLY.

  4. Fix CREATE INDEX CONCURRENTLY for the newest prepared transactions.

  5. Add -w back to the flags for pg_ctl (re)start in PostgresNode

  6. Fix CREATE INDEX CONCURRENTLY for simultaneous prepared transactions.