Re: idea: global temp tables

On Wed, Apr 29, 2009 at 6:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

I think having the feature is making a promise that we can't keep.
Having a feature which meets the letter of the rules but fails to
actually work as users have a right to expect is going to trap people
unaware that they're writing code that works in testing but will never
scale.

> 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.

That seems like a trivial little annoyance. Spending effort fixing
that that in a way that will only have to be replaced if we ever want
to support using temporary tables for heavy oltp load is a waste of
effort.

> 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.

Not if it promises something we can't deliver. My claim is that the
whole point of having a persistent catalog definition is *precisely*
to avoid the catalog thrashing and that's obvious to users who would
be using this feature. This is just like the idea of prepared queries
-- nowhere in the standard does it say that prepared queries have to
cache the prepared plan and run any faster than non-prepared queries
but if we didn't it would be somewhat broken. Now in that case
planning queries is relativelyl cheap, but how happy do you think
users would be if we supported prepared queries and they built their
application around that fact only to discover that every time any
session executed a PREPARE it created new records in catalog tables?
We would be better off not supporting the command at all so at least
users would know not to head down that dead-end path.

> 1. pg_statistic.
> 2. How you going to have transaction-safe behavior for things like
> TRUNCATE

hm, truncate is a bit of a special case. I had been thinking that ddl
would basically require preventing other backends from using the table
at the same time. A lot of people don't realize truncate behaves like
ddl but in any case I agree it sure seems like it would be important
for temporary tables.

I still maintain that this feature is not primarily about programmer
convenience, but rather comes with an expectation that the schema
definition is being given in advance so that the database doesn't have
to incur the costs of issuing the ddl for every session. If we provide
the interface but not the expected behaviour it could be worse than
not having the interface at all.

--
greg