Re: Pre-set Hint bits/VACUUM FREEZE on data load..?

On Mar 24, 2011, at 4:42 PM, Greg Stark wrote:
> On Thu, Mar 24, 2011 at 9:39 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
>>
>> We could conceivably deal with that by not setting the frozenxid but
>> setting the hint bit for those tuples and having a documented special
>> case that if the hint bit is set but it's the same xid as your own you
>> have to treat it as not-committed.
>
> Or I suppose we could set the frozenxid but maintain a hash table of
> relations that we use to remember which relations we created in this
> transaction and are treating this way. For any table in that hash
> table we ignore the xmin and just look at cmin/cmax.
>
> I'm not sure this solves the cases of subtransactions -- but perhaps
> we just wouldn't store frozenxid if we're in a subtransaction. And I'm
> not sure we have access to the relation id when we're doing visibility
> checks. I think we do. This would involve no serious ugliness in the
> actual on-disk storage and it would make data loads a write-once
> operation which is the holy grail :)

I have an even broader idea...

If we had the ability to specify in a transaction exactly what tables it was going to read from, enforce that it only read from those tables, and published that information to other backends, vacuum could then ignore long running transactions that are guaranteed not to hit the table they're vacuuming. This would eliminate a large pain-point for anyone that has a high-velocity table in an environment that also has to support longer running transactions.

If we had that mechanism you could also make use of it for this idea; you would specify that your transaction will never read from the new table, which means that any data inserted into that table can be pre-frozen (assuming we make sure that no one else can see that table exists yet).
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net