Re: POC: Lock updated tuples in tuple_update() and tuple_delete() - Mailing list pgsql-hackers

Hi,

An off-list conversation veered on-topic again. Reposting for posterity:

On 2023-03-23 23:24:19 +0300, Alexander Korotkov wrote:
> On Thu, Mar 23, 2023 at 8:06 PM Andres Freund <andres@anarazel.de> wrote:
> > I seriously doubt that solving this at the tuple locking level is the right
> > thing. If we want to avoid refetching tuples, why don't we add a parameter to
> > delete/update to generally put the old tuple version into a slot, not just as
> > an optimization for a subsequent lock_tuple()? Then we could remove all
> > refetching tuples for triggers. It'd also provide the basis for adding support
> > for referencing the OLD version in RETURNING, which'd be quite powerful.
>
> I spent some time thinking on this.  Does our attempt to update/delete
> tuple imply that we've already fetched the old tuple version?

Yes, but somewhat "far away", below the ExecProcNode() in ExecModifyTable(). I
don't think we can rely on that. The old tuple is just identified via a junk
attribute (c.f. "For UPDATE/DELETE/MERGE, fetch the row identity info for the
tuple..."). The NEW tuple is computed in the target list of the source query.
It's possible that for some simpler cases we could figure out that the
returned slot is the "old" tuple, but it'd be hard to make that work.

Alternatively we could evaluate returning as part of the source query
plan. While that'd work nicely for the EPQ cases (the EPQ evaluation would
compute the new values), it could not be relied upon for before triggers.

It might or might not be a win to try to do so - if you have a selective
query, ferrying around the entire source tuple might cost more than it
saves.


> We needed that at least to do initial qual check and calculation of the new
> tuple (for update case).

The NEW tuple is computed in the source query, as I mentioned, I don't think
we easily can get access to the source row in the general case.


> We currently may not have the old tuple at hand at the time we do
> table_tuple_update()/table_tuple_delete().  But that seems to be just and
> issue of our executor code.  Do it worth to make table AM fetch the old
> *unmodified* tuple given that we've already fetched it for sure?

Not unconditionally (e.g. if you neither have triggers, nor RETURNING, there's
not much point, unless the query is simple enough that we could make it
free). But in the other cases it seems beneficial. The caller would reliably
know whether they want the source tuple to be fetched, or not.

We could make it so that iff we already have the "old" tuple in the slot,
it'll not be put in there "again", but if it's not the right row version, it
is.

We could use the same approach to make the "happy path" in update/delete
cheaper. If the source tuple is provided, heap_delete(), heap_update() won't
need to do a ReadBuffer(), they could just IncrBufferRefCount(). That'd be a
quite substantial win.

Greetings,

Andres Freund