Re: ctid & updates

On Mon, 3 Jun 2002, Jan Wieck wrote:

> Joshua b. Jore wrote:
> > 1#
> > I saw a post from Jan Wieck about how ctid can be used for a fast update.
> > I noticed that ctid changes on update (as expected since it's really a new
> > row). Is there anyway to get the new ctid from the update so later
> > updates to the row can continue to use ctid to zero in on the row
> > location?
>
> That's one of the details I'm still thinking about. And in
> the case of a cursor using a junk attribute it gets worse,
> because there is no easy way to push that new value back into
> the cursor's result set.
>
> But I doubt that this would become a real world problem ever.
> People who need to update one and the same DB row again and
> again during the same transaction are spaghetti-code-script-
> kiddies who know for sure that "a cursor's that thingy on ya
> screen that ya move withe mouse", so we're pretty safe here

True, some really bad code could be written. In my case I just wanted to
do something less than optimal as a short term fix which might mean the
same row is updated multiple times. That's a queueing/aggregation cache
just waiting to get implemented to get around that (that's in my app, not
Postgresql for others who are reading this) ;-). I just figured that if I
was going to be dumb and update the same row multiple times it might as
well be speedy.

I did notice that this puts a wrinkle into MVCC. While MVCC operates
normally this method of finding the row makes the conflicting
update/delete miss the row and alter nothing. I was only playing with Read
Committed but if I started two transactions, updated the row in one, then
updated in another it acted normally (the second waited until the first
completed). When the first txn committed the second went on and found that
the row had disappeared and returned zero rows updated. I might just use
that behaviour to detect conflicts (of course, only when possible).

Neato keen. Thanks Jan

Josh