Thread: Tuples inserted and deleted by the same transaction

Shouldn't such tuples be considered dead right away, even if the inserting
transaction is still active?  That would allow cleaning them up even before
the transaction is done.

There is this code in HeapTupleSatisfiesVacuumHorizon:

        else if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmin(tuple)))
        {
            [...]
            /* inserted and then deleted by same xact */
            if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(tuple)))
                return HEAPTUPLE_DELETE_IN_PROGRESS;

Why HEAPTUPLE_DELETE_IN_PROGRESS and not HEAPTUPLE_DEAD?

Yours,
Laurenz Albe

On Tue, 2022-09-13 at 11:47 +0300, Nikita Malakhov wrote:
> On Tue, Sep 13, 2022 at 11:06 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Shouldn't such tuples be considered dead right away, even if the inserting
> > transaction is still active?  That would allow cleaning them up even before
> > the transaction is done.
> > 
> > There is this code in HeapTupleSatisfiesVacuumHorizon:
> > 
> >         else if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmin(tuple)))
> >         {
> >             [...]
> >             /* inserted and then deleted by same xact */
> >             if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(tuple)))
> >                 return HEAPTUPLE_DELETE_IN_PROGRESS;
> > 
> > Why HEAPTUPLE_DELETE_IN_PROGRESS and not HEAPTUPLE_DEAD?
>
> 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?

Yours,
Laurenz Albe

On Tue, Sep 13, 2022 at 11:04 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Tue, 2022-09-13 at 11:47 +0300, Nikita Malakhov wrote:
> > On Tue, Sep 13, 2022 at 11:06 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > > Shouldn't such tuples be considered dead right away, even if the inserting
> > > transaction is still active?  That would allow cleaning them up even before
> > > the transaction is done.
> > >
> > > There is this code in HeapTupleSatisfiesVacuumHorizon:
> > >
> > >         else if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmin(tuple)))
> > >         {
> > >             [...]
> > >             /* inserted and then deleted by same xact */
> > >             if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(tuple)))
> > >                 return HEAPTUPLE_DELETE_IN_PROGRESS;
> > >
> > > Why HEAPTUPLE_DELETE_IN_PROGRESS and not HEAPTUPLE_DEAD?
> >
> > 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?

Forgive me if this is not related but if there is a savepoint between
the insertion and deletion, wouldn't it be possible for the
transaction to recover the deleted tuples?

Best regards
Pantelis Theodosiou

On Tue, 13 Sep 2022, 12:04 Laurenz Albe, <laurenz.albe@cybertec.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

On Tue, 13 Sept 2022 at 12:40, Pantelis Theodosiou <ypercube@gmail.com> wrote:
>
> Forgive me if this is not related but if there is a savepoint between
> the insertion and deletion, wouldn't it be possible for the
> transaction to recover the deleted tuples?

Savepoints result in changed TransactionIds (well, subtransactions
with their own ids), so if a tuple was created before a savepoint and
deleted after, the values in xmin and xmax would be different, as you
can see in the following:

matthias=> CREATE TABLE tst(i int);
matthias=> BEGIN; INSERT INTO tst VALUES (1); SAVEPOINT s1; DELETE
FROM tst; ROLLBACK TO SAVEPOINT s1;
CREATE TABLE
BEGIN
INSERT 0 1
SAVEPOINT
DELETE 1
ROLLBACK
matthias=*> SELECT xmin, xmax FROM tst;
 xmin  | xmax
-------+-------
 62468 | 62469
(1 row)

Note that this row has different xmin/xmax from being created and
deleted in different subtransactions. This means that this needs no
specific handling in the HTSVH code that Laurenz asked about.


Kind regards,

Matthias van de Meent

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.)

            regards, tom lane

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.

Kind regards,

Matthias van de Meent

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