Re: Tuples inserted and deleted by the same transaction

On Tue, 13 Sep 2022, 12:04 Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Tue, 2022-09-13 at 11:47 +0300, Nikita Malakhov wrote:
>> Please correct me if I'm wrong, despite tuples being inserted and deleted by the same
>> transaction - they are visible inside the transaction and usable by it, so considering them
>> dead and cleaning up during execution is a bad idea until the transaction is ended.
>
> But once they are deleted or updated, even the transaction that created them cannot
> see them any more, right?

Not quite. The command that is deleting the tuple might still be
running, and because deletions are only "visible" to statements at the
end of the delete operation, that command may still need to see the
deleted tuple (example: DELETE FROM tab t WHERE t.randnum > (select
count(*) from tab)); that count(*) will not change during the delete
operation.

So in order to mark that tuple as all_dead, you need proof that the
deleting statement finished executing. I can think of two ways to do
that: either the commit/abort of that transaction (this would be
similarly expensive as the normal commit lookup), or (e.g.) the
existence of another tuple with the same XID but with a newer CID.
That last one would not be impossible, but probably not worth the
extra cost of command id tracking.

Kind regards,

Matthias van de Meent