Re: idea: global temp tables - Mailing list pgsql-hackers
From | Greg Stark |
---|---|
Subject | Re: idea: global temp tables |
Date | |
Msg-id | 4136ffa0904291142k265c4511l710e5ba1dd2b7196@mail.gmail.com Whole thread Raw |
In response to | Re: idea: global temp tables (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-hackers |
On Wed, Apr 29, 2009 at 6:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Greg Stark <stark@enterprisedb.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
pgsql-hackers by date: