Re: Global temporary tables

On 2026-06-21 Su 3:08 PM, Dean Rasheed wrote:
> 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.
>

Wow, we're on the same track. I have a patch series for exactly this
feature that I was about to submit.

FTR here's where I'm at. I'll try to take a look at yours ASAP.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com