Re: Implementation of global temporary tables?

Hi,

On 2015-02-02 11:15:22 +0100, Pavel Stehule wrote:
> Six years ago we did discuss about global temporary tables - persistent
> schema, ephemeral data.
>
> http://postgresql.nabble.com/idea-global-temp-tables-td2007217.html
>
> I am thinking so some reasons why implement this feature are valid:
>
> * we can get some performance benefit against current temp tables - less
> the catalogue bloating,
>
> * we can simplify a static validation of plpgsql functions when temp tables
> are used,
> more global temp tables are little bit comfortable for developers,
>
> * we can simplify migration from some other databases, where global temp
> tables are default.

I agree that the feature would be interesting.

> 2. Implementation
>
> I see three possible ways how to implement it:
>
> 2.a - using on demand created temp tables - most simple solution, but
> doesn't help with catalogue bloating

Yea, that's no good.

> 2.b - using unlogged tables for holding statistics, relfilenode, and all
> necessary data

I can't follow why that'd achieve anything?

> 3.c - store ephemeral metadata only in memory without MVCC

I think that's not an option. That'd end up being a massive amount of
duplication at a low rate of functionality.

I think it's more realistic way to implement is to have a separate
'relpersistence' setting for global temp tables. The first access to
such one in a session (or xact if truncate on commit) copies the table
from the _init fork. By having the backend id in all filenames (besides
the init fork) they're unique between sessions.

Or something roughly like that.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services