proposal: global temp tables

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: proposal: global temp tables
Date: 2011-06-26 08:58:12
Message-ID: BANLkTin1Gha0SS77E64jczPfAPn6Oxb8hQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello all,

I am returning back to discuss
http://web.archiveorange.com/archive/v/alpsnfEhA5L6HGsV2lNb

Robert introduces a global defined tables there. The talk was about
good naming - and I think, so there was agreement so name "global temp
tables" should be used for tables with persistent structure and
session limited content - other names should be "persistent temporary
tables", or "session content tables", "local content tables".

A introduction of new database class has two goals - a simplification
of work with session based content (temporary tables) and a reduction
overhead of current temporary tables.

The separation of content between sessions is possible now via
relmapper mechanism - but it's not enough. We need to overwrite (per
session relfilenode, relpages and reltuples attributes). We need to
hold a related pg_statistics per session.

Next area that should be modified are keys - or relation between key
and data. There is a new relation between key on shared table (system
table) and session based data.

One result from discuss was, so we don't need to implement a ALTER
TABLE statement in basic implementation. It should be replaced by
DROP/CREATE. I agree with this - because it can significantly simplify
a work with table metadata - it ensure, so table description will not
newer updated. I am thinking, so this decision is maybe too hard - we
should to enable a ALTER TABLE statement when there is not any
instance (a usage) of global temp table.

A content of global temp table is not dumped in default.

A implementation.

I see a two way. One way (my preferred) is a enhancing of relmapper
mechanism to overwriting necessary attributes in pg_class and
buffering a pg_statistics's tuple in session memory. This should be
fast and doesn't need a vacuum. A cleanup of unrelated files should be
simple - the name should have a some mark of owner's session and all
files with this mark should be deleted in server startup - and some
autovacuum process can check if there are some session opened still
and if not then remove files. These files should be deleted in logout
time, but it can to slowdown a logout time (there was some discuss
about this topic some time ago).

Second way is introduce a two new system tables - (for this moment I
use simply names) pg_class2 and pg_statistics2. These tables can
contains a local attributies. The access to global temp table's
metadata should be forwarded to these tables. A cleanup can be based
on pg_class2 table. This method needs a vacuum but it is reduced to
tables pg_class2 and pg_statistics2.

What do you thinking about this proposal?

Regards

Pavel Stehule

Browse pgsql-hackers by date

  From Date Subject
Next Message Kohei KaiGai 2011-06-26 09:34:07 Re: per-column generic option
Previous Message Darren Duncan 2011-06-26 07:57:55 Re: Range Types, constructors, and the type system