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@sss.pgh.pa.us> wrote:
> > Laurenz Albe <laurenz.albe@cybertec.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