Re: ctid access is slow

On Tue, Aug 23, 2005 at 06:42:33PM -0700, Jeff Eckermann wrote:
> > I believe that's not necessarily true. If you select
> > a tuple and it's
> > ctid and it's updated more than once with a vacuum
> > in-between I believe
> > it could end up back in the same position, which
> > would mean the same
> > ctid.
>
> True. But the probability of that happening would
> generally be low enough not to bother the designers of
> most applications.

Designers that don't care about their data, maybe. Here's the use case
that was implied:

Application selects a bunch of data to present to the user to be edited
User edits data even though it's not locked in the database
Application gets data and checks to see if it's changed. If it not,
*BLAM*, new changes are put into the database

Now, if that check for changed data fails with a false negative, you
just nuked data.

A better solution is to use a combination of a timestamp and a sequence.
Why both? Because it's possible for the clock to be set back (though
this is something best avoided), and a sequence will eventually roll
over. It's impossible to have a collision in this scenario unless you
roll the clock way back AND reset the sequence (assuming you're using an
integer sequence).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461