Re: CREATE INDEX and HOT - revised design

Pavan Deolasee wrote:
> On 3/28/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> "Florian G. Pflug" <fgp@phlo.org> writes:
>> > Couldn't you store the creating transaction's xid in pg_index, and
>> > let other transaction check that against their snapshot like they
>> > would for any tuple's xmin or xmax?
>>
>> What snapshot?  I keep having to remind people that system catalog
>> operations are SnapshotNow.  In the particular context here, the
>> place where doing something else would break down is that the planner
>> has no idea when it makes a plan what snapshot(s) the plan might later
>> be used with.
>>
> Tom, please correct me if I am wrong. But ISTM that this idea might
> work in this context. In get_relation_info(), we would check if "xcreate"
> xid stored in pg_index for the index under consideration is seen
> committed with respect to the snapshot at that point of time.
> Even if the snapshot changes later and index becomes valid, we
> might not replan and hence not use index. But that doesn't seem
> like a big problem to me.

That problem are usecases like
PREPARE my_plan .... ;
BEGIN;
EXECUTE my_plan .... ;
COMMIT ;

Is that "PREPARE" even run inside a transaction? Even if it is, it
probably won't have created a snapshot...

I think allowing the use of some sort of snapshot from inside the planner
would allow some locking to be relaxed, but there seems be a lot of
corner cases to consider :-(

OTOH, if you manage to make this work, a TRUNCATE that doesn't block
concurrent selects might become possible to do. This would for example
allow dropping and rebuilding subscriptions on a slony node while it
is in use.

greetings, Florian Pflug