Re: Implementation of global temporary tables?

On 15 July 2015 at 13:26, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> On 07/15/2015 07:58 AM, Simon Riggs wrote:
>
>
>> 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.
>>
>>
>>
> Sounds fine in general. I'm a bit curious to know what are the locking
> implications of vivifying the table on access.
>

We would lock the Global Temp Table at the same lock level for the
activity, just as we do for INSERT, SELECT etc.. That prevents concurrent
DDL like DROP or ALTER on the Global Temp Table.

The Local temp table created is a copy of the Global Temp Table. The Local
temp table is only locally locked, so no worries.

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