Thread: TID clarification

I'm implementing an O/R mapping tools for Java on top of JDBC. One of
the issues I'm dealing with are dirty checks.

An object is loaded from a row and modified in memory without acquiring
a lock. Prior to storing the object I would like to assure that the
memory copy is current and no changes occured to the database in the
meantime.

One way of doing that is by comparing fields from the original object
copy. Another solution is using a TIMESTAMP or RAWID.

In RDBMS that support timestamp, each update to a row updates the
timestamp, so loading the timestamp and comparing it during update is
sufficient to identity dirtyness.

In RDBMS that support RAWID (as opposed to ROWID), every update to the
row updates the RAWID, providing the same functionality as TIMESTAMP but
without the time value.

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

arkin


-- 
----------------------------------------------------------------------
Assaf Arkin                                           www.exoffice.com
CTO, Exoffice Technologies, Inc.                        www.exolab.org

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