Re: Implementation of global temporary tables?

2015-02-02 12:04 GMT+01:00 Andres Freund <andres(at)2ndquadrant(dot)com>:

> 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?
>

1. Main catalogue will be stable.
2. There is not necessary to implement new storage and it can helps with
transaction support.

>
> > 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 don't plan to implement a storage - I expect only few functions for
store/read data from session memory context

>
>
> 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.
>
>
If I understand well, it is similar to my fast implementation from 2008. It
works partially, because it doesn't solve other (session) property - like
relpages, reltuples and related data from pg_statistics

> Or something roughly like that.
>
> Greetings,
>
> Andres Freund
>
> --
> Andres Freund http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>