PostgreSQL thinking - when can tuples be deleted?

Preparatory knowledge

<PostgreSQL transaction - MVCC>

summary

PostgreSQL supports MVCC, so when deleting, the related tuples will not and cannot be completely deleted. PostgreSQL only sets the T of the tuples involved in the Delete operation_ Xmax to indicate that the tuple has been t_ The transaction corresponding to xmax is deleted. However, at this time, this tuple may still be visible to some query transactions, which is why tuples cannot be deleted directly. So the next two questions?

• When can tuples be deleted?
• When are tuples deleted?

When tuples are deleted, the answer is given directly here: when the user executes Vacuum. When tuples can be deleted is the focus of this paper. In fact, the core idea of this problem is very simple. The tuple cannot be deleted directly because it may also be visible to other transactions. Then, tuples can be deleted only when they are invisible to all transactions! In PostgreSQL transactions - MVCC, we discussed transaction visibility and tuple visibility, so we know that deleted tuples are not visible to all transactions, which means the T of tuples_ Xmax is visible to all transactions!

The core idea is simple to say, but difficult to do. Let's elaborate on how to judge whether tuples can be deleted.

Tuple expiration

From the previous description, we know that a tuple can be really deleted, which requires two elements:

• t_xmax is not 0
• t_xmax is visible to all active transactions

To judge whether a tuple can be deleted, we only need to judge t in the global transaction chain_ Xmax visibility. Through PostgreSQL transactions - MVCC, it is not difficult to see that judging visibility is a complex process. Therefore, if we determine whether tuples can be deleted according to the visibility judgment process, the efficiency will be very low. So is there any way to improve efficiency? Of course.

In PostgreSQL, there is a global PGXACT array, and each PGXACT tuple in the array represents a current user process. PostgreSQL needs to take a Snapshot of the current active transaction chain before adding, deleting, querying and modifying. Then store the smallest transaction id in the Snapshot in the xmin member of PGXACT. Through PostgreSQL transactions - MVCC, we know that for the current transaction, transactions with transaction id less than xmin are visible to the current transaction. Then, if we traverse the PGXACT array, get the smallest xmin of all PGXACT - > xmin as globalxmin. Transactions with transaction id less than globalxmin are visible to all current transactions.

Therefore, if we obtain and modify globalxmin before each addition, deletion, query and modification, when we need to judge whether a tuple can be deleted, we only need to judge the T of the tuple_ Whether xmax is less than globalxmin.

The code for obtaining globalxmin is as follows:

//procarray.c line 1585
numProcs = arrayP->numProcs;
for (index = 0; index < numProcs; index++)
{
    int			pgprocno = pgprocnos[index];
    volatile PGXACT *pgxact = &allPgXact[pgprocno];
    TransactionId xid;
    
    if (pgxact->vacuumFlags & PROC_IN_LOGICAL_DECODING)
        continue;

    /* Ignore procs running LAZY VACUUM */
    if (pgxact->vacuumFlags & PROC_IN_VACUUM)
        continue;

    /* Update globalxmin to be the smallest valid xmin */
    xid = pgxact->xmin; /* fetch just once */
    if (TransactionIdIsNormal(xid) &&
        NormalTransactionIdPrecedes(xid, globalxmin))
        globalxmin = xid;

    /* Fetch xid just once - see GetNewTransactionId */
    xid = pgxact->xid;

    if (!TransactionIdIsNormal(xid)
        || !NormalTransactionIdPrecedes(xid, xmax))
        continue;

    if (NormalTransactionIdPrecedes(xid, xmin))
        xmin = xid;
    if (pgxact == MyPgXact)
        continue;

    /* Add XID to snapshot. */
    snapshot->xip[count++] = xid;

    if (!suboverflowed)
    {
        if (pgxact->overflowed)
            suboverflowed = true;
        else
        {
            int	nxids = pgxact->nxids;

            if (nxids > 0)
            {
                volatile PGPROC *proc = &allProcs[pgprocno];

                memcpy(snapshot->subxip + subcount,
                       (void *) proc->subxids.xids,
                       nxids * sizeof(TransactionId));
                subcount += nxids;
            }
        }
    }
}

The function to judge whether tuples can be deleted is HeapTupleSatisfiesVacuum.