Re: weirdness with the a sql update

On Thu, 2005-08-04 at 09:19 -0500, Tony Caduto wrote:
> Hi,
>
> I just noticed this, if I do a update like this:
>
> update new_requests set name = 'tony' where request_id = 2
>
> If I do a select * from new_requests that record I just updated is now
> at the bottom , before the update it was at the top?
>
> Why is Postgresql changing the ordering of the results after a simple
> update?
> It almost looks like the record is being dropped and then readded to the
> end.
>
> Thanks,
>
If I understand MVCC correctly, a "new" tuple is actually created and
the "old" tuple is marked with an effective "end" transaction id (or
marked "dead" for any transactions with an id greater than the
transaction id that updated that particular tuple). Your subsequent
select then sees the "new" tuple and, in natural order, it would appear
at the bottom. Vacuuming is the process by which these "old" tuples are
examined and, if there are no transactions open with an id less than the
"end" transaction id associated with that dead tuple, it is removed (or
the space is marked as available for a new tuple to be written).

Sven