Re: idea: global temp tables

2009/4/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Greg Stark <stark(at)enterprisedb(dot)com> writes:
>> Well I claim it's not just a nice bonus but is the difference between
>> implementing something which falls technically within the standard's
>> rules but fails to actually be useful for the standard's intended
>> purpose.
>
> I agree with Kevin's objection that you are unfairly raising the bar
> for this feature by demanding a performance improvement to go along
> with a functionality change.  The use-case for this feature is to
> simplify application logic by allowing apps to assume that a temp
> table exists without having to create it at the start of a session.
> That's particularly handy in connection-pooling scenarios, for instance.
> Currently, you have to have some sort of "if exists" check, and you
> pay just as much in catalog thrashing as you would if the feature
> was present without any catalog optimization.
>

exactly

> It would be great to find a way to avoid the catalog thrashing,
> but I completely disagree with a point of view that says we can't
> have this without solving that first.  It's an improvement on the
> current state of affairs anyway.
>
>> I've been thinking about Alvaro's idea of a separate smgr. If you had
>> a single pg_class entry for all sessions but the smgr knew to store
>> the actual data for it in a session-local file, either in a
>> session-specific tablespace or using the same mechanism the temporary
>> files use to direct data then the backend would basically never know
>> it wasn't a regular table.
>
> 1. pg_statistic.
>
> 2. How you going to have transaction-safe behavior for things like
> TRUNCATE, if you don't have an updatable private catalog entry to keep
> the current relfilenode in?
>
>> It could still use local buffers but it could use the global relcache,
>> invalidation, locks, etc.
>
> Locks would be another big problem: if only smgr knows that different
> instances of the table are different, then different backends' locks
> would conflict, which would be Bad.  This might not matter for simple
> read/update, but again TRUNCATE is a counterexample of something that
> is likely to be needed and should not cause cross-backend conflicts.

I though about some techniques for elimination changes in pg_class and
pg_statistic. Teoreticly, we could to overwrite some columns (or
complete rows) from these tables via stored values in memory. My last
(and not sucessfull) prototype was based on some alchymy over
syscache. It was wrong way.

Maybe we could do some like

int get_relpages(oid)
{
tuple = read_tuple_pg_class(oid);
if is_global(tuple)
{
tuple2 = find_global(oid);
if (tuple2 == NULL)
{
store_global(tuple);
return relpages(tuple);
}
else
return relpages(tuple2);
}
else
return relpages(tuple);
}

But question?

about MVCC?
Is necessary to use MVCC on pg_statistic and some columns from pg_proc?

regards
Pavel Stehule

>
>                        regards, tom lane
>