Re: Tuples inserted and deleted by the same transaction

On Tue, 2022-09-13 at 16:13 +0200, Matthias van de Meent wrote:
> 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.

Yes, that makes sense. Thanks.

Yours,
Laurenz Albe