Re: Implementation of global temporary tables?

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.

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