Assaf Arkin <arkin@exoffice.com> writes:
> I ran a very simple test using ctid and noticed that each update to a
> row (a three-row table) changes the ctid. I want to know if that is by
> accident or by design. Assuming that I update a row, will ctid change
> each time, or only when the row is repositioned in the database.
> And, if ctid does not have to change when a row is updated, why does it
> change in such a small table (less than the size of a page)?
Since ctid represents the physical location within the table file,
it must change in an update --- otherwise we'd be overwriting the
original tuple, which would prevent rollback if the transaction is
aborted later on.
However, once the updating transaction is committed, the original
tuple position is not needed anymore, and in theory it could be
recycled at any later instant --- perhaps for a second update of
that same logical tuple. If that happened, you couldn't tell by
ctid that the third-generation tuple wasn't the same as the
first-generation one.
Right now, we do not recycle tuples (ctids) until VACUUM, but that's
likely to change someday.
In short, checking for tid change would probably work in the current
state of Postgres, but I couldn't recommend it as a long-term solution.
Instead consider using an update serial number field that's stamped
from a SEQUENCE object by an on-insert-or-update trigger.
regards, tom lane