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

On Sun, 21 Jun 2026 at 23:06, Andrew Dunstan <andrew@dunslane.net> wrote:

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.

Oh, wow. There's a lot of similarity between our patchsets, which is
reassuring, but there are also a number of differences, which I need
to think about in more detail.

In the meantime, here's a v2 of my patchset fixing up a few things
noted by the cfbot, and one bug I spotted -- REPACK/VACUUM FULL on a
global temporary table with associated toast tables failed if the
table hadn't already been opened in the session, because the code in
repack.c didn't open toast tables, so they were never being
initialized.

Regards,
Dean

On Jun 22, 2026, at 4:57 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

On Sun, 21 Jun 2026 at 23:06, Andrew Dunstan <andrew@dunslane.net> wrote:

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.

Oh, wow. There's a lot of similarity between our patchsets, which is
reassuring, but there are also a number of differences, which I need
to think about in more detail.

Yeah, agree on both fronts. The main areas I see are the catalog and handling wraparound. I don’t think there’s necessarily a clear winner on either, so I’ll be interested to hear what others say.

Cheers

Andrew

Hi

po 22. 6. 2026 v 15:43 odesílatel Andrew Dunstan <andrew@dunslane.net>
napsal:

On Jun 22, 2026, at 4:57 AM, Dean Rasheed <dean.a.rasheed@gmail.com>

wrote:

On Sun, 21 Jun 2026 at 23:06, Andrew Dunstan <andrew@dunslane.net>

wrote:

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.

Oh, wow. There's a lot of similarity between our patchsets, which is
reassuring, but there are also a number of differences, which I need
to think about in more detail.

Yeah, agree on both fronts. The main areas I see are the catalog and
handling wraparound. I don’t think there’s necessarily a clear winner on
either, so I’ll be interested to hear what others say.

next week I'll have free time, I can check these patches

generally - global temporary tables is super useful for migration from
other systems

Regards

Pavel

v3 attached, attempting to fix another issue reported by the cfbot --
need to tolerate relation_open() on a global temporary relation, when
in parallel mode because pg_get_viewdef() does that.

Regards,
Dean

On Tue, 23 Jun 2026 at 10:12, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

v3 attached, attempting to fix another issue reported by the cfbot --
need to tolerate relation_open() on a global temporary relation, when
in parallel mode because pg_get_viewdef() does that.

Ugh, that still wasn't quite right. Here's v4 with more fixes for
parallel workers.

Regards,
Dean

Le 24/06/2026 à 06:34, Dean Rasheed a écrit :

On Tue, 23 Jun 2026 at 10:12, Dean Rasheed<dean.a.rasheed@gmail.com> wrote:

v3 attached, attempting to fix another issue reported by the cfbot --
need to tolerate relation_open() on a global temporary relation, when
in parallel mode because pg_get_viewdef() does that.

Ugh, that still wasn't quite right. Here's v4 with more fixes for
parallel workers.

Regards,
Dean

Congratulations, it's an ingenious implementation. Aside from
portability with other DBMSs, the fundamental advantage of GTTs is
avoiding catalog fragmentation caused by the intensive use of temporary
tables. This is what the patch looks to achieve.

I will try to do more checks.

Best regards.

--
Gilles Darold
http://hexacluster.ai/

On Tue, Jun 23, 2026 at 7:35 PM Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

On Tue, 23 Jun 2026 at 10:12, Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

v3 attached, attempting to fix another issue reported by the cfbot --
need to tolerate relation_open() on a global temporary relation, when
in parallel mode because pg_get_viewdef() does that.

Ugh, that still wasn't quite right. Here's v4 with more fixes for
parallel workers.

Regards,
Dean

+ 1 for the idea. I will try to review this over the weekend. We could
use this.

On Fri, 26 Jun 2026 at 04:52, Kirk Wolak <wolakk@gmail.com> wrote:

+ 1 for the idea. I will try to review this over the weekend. We could use this.

Thanks. Any reviews will be very helpful.

In the meantime, comparing the 2 patchsets, I realised that there was
a bug in mine -- ON COMMIT DELETE ROWS wasn't working properly in all
cases.

More specifically, it was working for a global temporary table created
in the current session, but disconnecting and then reconnecting caused
it to stop working, because I hadn't realised that the ON COMMIT
action of a temporary table is not saved to the database.

Not saving the ON COMMIT action to the database kind-of made sense for
old-style temporary tables, since they disappear at the end of the
session. However, even then, it can be useful to have it in the
database so that psql's \d meta-command can display it, and of course,
for global temporary tables, saving it to the database is essential so
that new sessions can pick it up.

So here's a new patchset, where 0001 is new -- it saves a temporary
table's ON COMMIT action to pg_class, and updates psql's \d to display
it. I think we could commit that independently of the global temporary
tables feature, since it seems somewhat useful by itself.

(I chose to do it as a new column "reloncommit" in pg_class, rather
than as a reloption, because a reloption didn't seem quite right for
this, but that's a matter of opinion.)

Regards,
Dean

On Fri, Jun 26, 2026 at 3:37 PM Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

On Fri, 26 Jun 2026 at 04:52, Kirk Wolak <wolakk@gmail.com> wrote:

+ 1 for the idea. I will try to review this over the weekend. We could

use this.

Thanks. Any reviews will be very helpful.

In the meantime, comparing the 2 patchsets, I realised that there was
a bug in mine -- ON COMMIT DELETE ROWS wasn't working properly in all
cases.

...

Not saving the ON COMMIT action to the database kind-of made sense for
old-style temporary tables, since they disappear at the end of the
session. However, even then, it can be useful to have it in the
database so that psql's \d meta-command can display it, and of course,
for global temporary tables, saving it to the database is essential so
that new sessions can pick it up.

Okay, a small "nit" on your wording. No new "sessions" should see any data.
They can't see that data, as it is not theirs.
\d should see it. Future selects should see it.
In our case, we use this because we do a crap ton of work in temp tables
(unlogged).
And then we commit (along with the flags that indicate we are 1/2 done, and
the timing logs).
Then, we insert that into the table we want to see that data.
Mark ourselves fully done.
COMMIT.

Temp data is still there, but then we disconnect and the table data goes
away.

...

Regards,
Dean

Kirk