Horton

Re: POC: Lock updated tuples in tuple_update() and tuple_delete()

Chris Travers <chris.travers@gmail.com>

View thread View on postgr.es
From: Chris Travers <chris.travers@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Date: 2023-03-10T17:16:40Z
Lists: pgsql-hackers 
"Right, the improvement this patch gives to the heap is not the full motivation. Another motivation is the improvement it gives to TableAM API. Our current API implies that the effort on locating the tuple by tid is small. This is more or less true for the heap, where we just need to pin and lock the buffer. But imagine other TableAM implementations, where locating a tuple is more expensive."

Yeah. Our TableAM API is a very nice start to getting pluggable storage, but we still have a long ways to go to have an ability to really provide a wide variety of pluggable storage engines.

In particular, the following approaches are likely to have much more expensive tid lookups:
 - columnar storage (may require a lot of random IO to reconstruct a tuple)
 - index oriented storage (tid no longer physically locatable in the file via seek)
 - compressed cold storage like pg_ctyogen (again seek may be problematic).

To my mind I think the performance benefits are a nice side benefit, but the main interest I have on this is regarding improvements in the TableAM capabilities.  I cannot see how to do this without a lot more infrastructure.

Commits

  1. Add EvalPlanQual delete returning isolation test

  2. Allow locking updated tuples in tuple_update() and tuple_delete()

  3. Revert 764da7710b

  4. Revert 11470f544e

  5. Evade extra table_tuple_fetch_row_version() in ExecUpdate()/ExecDelete()

  6. Check that xmax didn't commit in freeze check.