Re: SELECT * FROM <table> LIMIT 1; is really slow

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> On Fri, May 28, 2004 at 03:48:11PM -0400, Tom Lane wrote:
>> Nope. Think about sub-subtransactions.

> They are all aborted if the parent is, so

> BEGIN;
> BEGIN; -- cid = 1
> BEGIN; -- cid = 2
> INSERT INTO foo VALUES (1) -- cid=3
> COMMIT;
> ROLLBACK; -- aborts from Cid 1 to Cid 3
> -- here we can't see the tuple because Xmin == my-xid
> -- and Cmin=1 is aborted
> COMMIT;

> I assume this is how you think it works, isn't it?

[ thinks about it for a while... ] Yeah, I guess you are right. Since
we don't have threading, an outer transaction cannot assign any new CIDs
while a subtransaction is in progress. Therefore, when a subtransaction
ends, all CIDs from its start to current belong to either itself or its
subtransactions. On abort we can just mark *all* of these as aborted.
If we had to do anything at subtrans commit, we'd need more state, but
we don't have to do anything at subtrans commit.

So you're right, the per-open-subtrans state is just its starting CID.
Slick.

However, I just remembered why we rejected this idea to start with :-(.
If we do it this way then when the overall xact commits, we no longer
have state that tells which particular tuples are good or not. We would
have to trawl for tuples written by aborted subtransactions and mark
them dead before committing, else other transactions would think they
were good.

What this says is that we still need persistent pg_subtrans status.
I'm not sure if we can use CIDs as subtrans IDs this way and still have
a reasonably efficient storage representation for the global pg_subtrans
table.

regards, tom lane