Global temporary tables

I have been thinking about global temporary tables (that is, temporary
tables whose definition is permanent, and visible to all sessions, but
whose data is temporary, and local to each session), and I have a
rough patch set implementing this.

I didn't look closely at the previous patch attempting this, because
it is quite old, and as others noted, it had significant design
issues. So I have attempted to come up with a new design, which I have
split into a series of patches, each focusing on a different aspect of
the problem, which hopefully makes it easier to think about.


0001 is the basic patch allowing the syntax CREATE GLOBAL TEMP TABLE
to create a global temporary table with a new relpersistence of
RELPERSISTENCE_GLOBAL_TEMP. Such tables are only allowed in
non-temporary schemas.

The majority of the code in this patch is establishing the basic
infrastructure needed to manage these tables -- the first time a
global temporary table is used in a session, it is initialised, which
includes creating local storage for it, using local buffers, just like
a local temporary table. All global temporary tables in use, and all
storage created for them, is tracked through operations like
(sub)transaction rollback, TRUNCATE, etc. and any storage remaining at
backend exit is deleted.

In the event of a backend crash, there is pre-existing code in
RemovePgTempFiles() that will delete any temporary files left behind,
if remove_temp_files_after_crash is on, but if that doesn't happen,
the new initialisation code will automatically delete any existing
storage for a relation before creating new storage.

A shared hash table is used to track global temporary tables in use
across all backends. This is used to prevent operations like ALTER
TABLE from altering a table that is being used by some other backend,
if the change would require a rewrite or scan of the table's contents,
which isn't possible because one backend cannot access the local
buffers of another.

There's a large header comment in global_temp.c that explains the
design in more detail.


0002 adds support for indexes. The first time an index on a global
temporary table is opened in a session other than the session that
created it, an empty index is built in local storage using
ambuildempty() (which the patch modifies to accept a fork number
argument). If the table is not empty (the session had already added
some data to the table before another session defined the index), then
the index is marked invalid (more on that in 0009), and cannot be used
in that session without doing a REINDEX.


0003 adds support for sequences.


0004 allows system catalog tables to be global temporary tables, and
defines the first such example: pg_temp_class. The idea is that
pg_temp_class has a subset of the columns of pg_class, allowing those
properties to override the values from pg_class, allowing global
temporary tables to operate independently in each session.

In this commit, the only columns are oid, relfilenode, and
reltablespace, allowing each session to independently track the
location of the storage of global temporary tables. If a session
executes CLUSTER, REINDEX, REPACK, TRUNCATE, or VACUUM FULL on a
global temporary table, the updates are saved to pg_temp_class instead
of pg_class, so they only affect that session.

ALTER TABLE ... SET TABLESPACE works a little differently in that it
updates reltablespace in both pg_temp_class and pg_class. This way,
the change applies to the current session and any future sessions that
use the table, but not to any other existing sessions that are already
using it, which continue to use their own pg_temp_class.reltablespace
values.

There's another large header comment in pg_temp_class.h, explaining
the design in more detail.

(BTW, I intentionally chose the name pg_temp_class, rather than
something like pg_global_temp_class, because I think perhaps this, and
other similar catalog tables might possibly be used in the future for
local temporary tables too, though I have not explored that idea in
any detail.)


0005 adds relation statistics columns to pg_temp_class (relpages,
reltuples, relallvisible, and relallfrozen), and adjusts ANALYZE,
CREATE INDEX, REPACK, VACUUM, and pg_clear/restore_relation_stats() to
update pg_temp_class instead of pg_class, for global temporary tables,
so each session gets its own relation-level statistics.


0006 adds the VACUUM-related fields relfrozenxid and relminmxid to
pg_temp_class. This is not quite so straightforward though. In
addition to those fields, I added new fields tempfrozenxid and
tempminmxid to the PGPROC structure. These are set by each session to
the minimum values of relfrozenxid and relminmxid over all global
temporary tables in use by that session. Then, when VACUUM is run, it
sets pg_temp_class.relfrozenxid/relminmxid, based on the local
contents of the table, and pg_class.relfrozenxid/relminmxid taking
into account the contents of other sessions using global temporary
tables. It's a little crude, because it can't see other
relfrozenxid/relminmxid values on a per-table level for other
sessions, but this is sufficient to allow
pg_database.datfrozenxid/datminmxid to be advanced, provided that each
session runs VACUUM from time to time.

This still suffers from the same problem as local temporary tables
though -- if a session uses a local or global temporary table, and
then just sits there, without ever running VACUUM, there is no way to
advance the pg_database fields, and eventually there will be a XID
wraparound danger. Autovacuum doesn't help, because it can't vacuum
temporary tables. It's not at all clear what can be done about that.
It might be of some help to add a diagnostic function to identify the
offending backend, though I have not done so here.


0007 adds another global temporary system catalog table:
pg_temp_statistic. This has the exact same set of columns as
pg_statistic, but it is used to hold statistics about global temporary
tables (and again, it could in theory also be used for local temporary
tables).

ANALYZE writes to pg_temp_statistic instead of pg_statistic for global
temporary tables, and various selectivity estimating functions are
updated to read from it, so each session gets its own local set of
per-column statistics for the global temporary tables that it uses.

The pg_stats view is updated to a UNION ALL query selecting from
pg_statistic and pg_temp_statistic, so users can view their own
statistics data in the usual way.


0008 adds pg_temp_statistic_ext_data, which is exactly the same as
pg_statistic_ext_data, except that it is a global temporary table used
to store extended statistics data for global temporary tables. The
views pg_stats_ext and pg_stats_ext_exprs are updated to include this.


0009 adds a final global temporary system catalog table:
pg_temp_index. As noted in 0002, a session needs to be able to mark an
index on a global temporary table as invalid locally, if it was added
by another session after this session had already populated the table.
So pg_temp_index has indexrelid and indisvalid columns, so that the
valid state of an index can be overridden locally.


So far, I've focused on getting a set of patches that work, and these
do seem to operate as expected. However, it seems quite likely that
there are things that I have overlooked.

I'm also aware that I haven't written any documentation yet, and I
need to add more tests, but as a rough set of patches, I hope that
they're in good enough shape for review.

Regards,
Dean