Re: Implementation of global temporary tables?

2015-07-15 13:58 GMT+02:00 Simon Riggs <simon(at)2ndquadrant(dot)com>:

> On 14 July 2015 at 23:20, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
>
>> On 7/9/15 12:45 AM, Pavel Stehule wrote:
>>
>>>
>>> 2015-07-09 7:32 GMT+02:00 Zhaomo Yang <zhy001(at)cs(dot)ucsd(dot)edu
>>> <mailto:zhy001(at)cs(dot)ucsd(dot)edu>>:
>>>
>>> > I am not sure, if it is not useless work.
>>>
>>> I don't understand why an implementation taking approach 2.a would
>>> be useless. As I said, its performance will be no worse than current
>>> temp tables and it will provide a lot of convenience to users who
>>> need to create temp tables in every session.
>>>
>>>
>>> Surely it should be step forward. But you will to have to solve lot of
>>> problems with "duplicated" tables in system catalogue, and still it
>>> doesn't solve the main problem with temporary tables - the bloating
>>> catalogue - and related performance degradation.
>>>
>>
>> That being the "main" problem is strictly a matter of opinion based on
>> your experience. Many people don't have a performance problem today, but do
>> have to deal with all the pain of handling this manually (as well as all
>> the limitations that go with that).
>>
>> If it's easy to fix the bloat problem at the same time as adding GLOBAL
>> TEMP then great! But there's no reason to reject this just because it
>> doesn't fix that issue.
>
>
> Agreed
>
> There are some good arguments for why we need this feature.
>
> Pavel's original description of how to do this seem valid, and from the
> link Tom agreed in 2009.
>
> For me the design summary is this
>
> * CREATE GLOBAL TEMP TABLE creates catalog entries like a normal table but
> with different relkind
> * When we see a request to INSERT, DEL, UPD, SEL from the temp table, if
> it does not exist we create it as a TEMP table of the same name, using the
> Global's pg_class entry as a template
>
> That meets the SQL Standard and doesn't contain any visibility problems or
> need for new internals.
>
> The purpose of this feature is to automatically create a temp table with
> the same definition whenever needed. The discussion of "bloat" is just
> wrong. We create exactly the same amount of bloat as if we had typed CREATE
> TEMP TABLE. Optimising temp table entries in the catalog is another,
> separate patch, if we care.
>

The optimization of local temp tables is little bit harder - you cannot to
share pg_class and pg_attribute - although some memory entries can be used
too.

Regards

Pavel

>
> --
> Simon Riggs http://www.2ndQuadrant.com/
> <http://www.2ndquadrant.com/>
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>