Rakesh Kumar wrote:
> I understand that when an update of say 100,000 rows are made, PG
> writes the updated rows as a new row. These new rows are not visible
> to any sessions except the one creating it. At commit time PG flips
> something internally to make these rows visible to all.
>
> My Q: what happens to those rows which use to contain the values
> before the update. Shouldn't something change in those rows to
> indicate that those rows are no longer valid. Who does it chain those
> rows to the new rows.
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.
Yours,
Laurenz Albe