Re: Much Ado About COUNT(*)

On Sun, Jan 16, 2005 at 03:22:11PM -0500, Tom Lane wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> > Manfred Koizar <mkoi-pg(at)aon(dot)at> writes:
> >> Last time we discussed this, didn't we come to the conclusion, that
> >> resetting status bits is not a good idea because of possible race
> >> conditions?
>
> > There's no race condition,
>
> Actually, wait a minute --- you have a point. Consider a tuple whose
> inserting transaction (A) has just dropped below GlobalXmin.
> Transaction B is doing an index scan, so it's going to do something like
>
> * Visit index entry, observe that it is in "uncertain" state.
> * Visit heap tuple, observe that A has committed and is < GlobalXmin,
> and there is no deleter.
> * Return to index entry and mark it "visible to all".
>
> Now suppose transaction C decides to delete the tuple. It will
>
> * Insert itself as the XMAX of the heap tuple.
> * Visit index entry, set state to "uncertain" if not already that way.
>
> C could do this between steps 2 and 3 of B, in which case the index
> entry ends up improperly marked "visible to all" while in fact a
> deletion is pending. Ugh. We'd need some kind of interlock to prevent
> this from happening, and it's not clear what. Might be tricky to create
> such an interlock without introducing either deadlock or a big
> performance penalty.

Wouldn't the original proposal that had a state machine handle this?
IIRC the original idea was:

new tuple -> known good -> possibly dead -> known dead

In this case, you would have to visit the heap page when an entry is in
the 'new tuple' or 'possibly dead' states. When it comes to transitions,
you would enforce the transitions as shown, which would eliminate the
race condition you thought of.

Err, I guess maybe you have to allow going from possibly dead back to
known good? But I think that would be the only 'backwards' transition.
In the event of a rollback on an insert I think you'd want to go
directly from new tuple to known dead, as well.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"