Rakesh Kumar wrote:
>> Every row has two system columns associated with it: xmin and xmax
>>
>> xmin is the transaction ID that created the row, while xmax is
>> the transaction ID that removed the row.
>>
>> So when an update takes place, xmax of the original row and xmin
>> of the new row are set to the transaction ID of the current transaction.
>>
>> Furthermore, the commit log (CLOG) logs for each transaction whether
>> it was committed or rolled back.
>>
>> Now when a backend examines a row, it first checks if the row is
>> visible, i.e. xmin must be less or equal to the current transaction ID
>> and xmax must be 0 or greater than the current transaction ID
>> or belong to a transaction that was rolled back.
>>
>> To save CLOG lookups, the first reader who consults the CLOG will
>> save the result in so-called "hint bits" on the row itself.
>
> I am assuming the same must be true for the indexes also. Does PG look
> up primary key
> by examining the rows like you described above.
Rows remain in the index as long as they remain in the table.
What actually removes old rows from the table - VACUUM - also
removes them from the index.
The index does not contain visibility information about the indexed row,
so every row found in an index must still be checked for visibility,
either as described above, or in the case of an index-only scan
by checking the visibility map if all tuples on that heap page are
visible to all transactions.
Yours,
Laurenz Albe