Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] TID clarification

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Assaf Arkin <arkin(at)exoffice(dot)com>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] TID clarification
Date: 2000-01-28 04:39:59
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Assaf Arkin <arkin(at)exoffice(dot)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

In response to

pgsql-hackers by date

Next:From: Hiroshi InoueDate: 2000-01-28 04:40:56
Subject: RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
Previous:From: Tom LaneDate: 2000-01-28 04:10:03
Subject: OR-of-ANDs dragon slain ... or at least seriously wounded ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group