Re: Tuples inserted and deleted by the same transaction

On Tue, 13 Sept 2022 at 15:45, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> > But once they are deleted or updated, even the transaction that created them cannot
> > see them any more, right?
>
> I would not trust that claim very far. The transaction might have active
> snapshots with a command ID between the times of insertion and deletion.
> (Consider a query that is firing triggers as it goes, and the triggers
> are performing new actions that cause the command counter to advance.
> The outer query should not see the results of those actions.)

I hadn't realized that triggers indeed consume command ids but might
not be visible to the outer query (that might still be running). That
invalidates the "or (e.g.) the existence of another tuple with the
same XID but with a newer CID" claim I made earlier, so thanks for
clarifying.

Kind regards,

Matthias van de Meent