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

Show quoted text

Cheers

Andrew

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

Hi

pá 26. 6. 2026 v 21:37 odesílatel Dean Rasheed <dean.a.rasheed@gmail.com>
napsal:

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.)

I am testing this patchset. Generally, it looks very well. I didn't see any
bloat on catalog

I found an issue - TRUNCATE waits when gtt is used by different
transactions, and TRUNCATE removes content in all sessions.

Is it expected?

Regards

Pavel

Show quoted text

Regards,
Dean

On Tue, 30 Jun 2026 at 20:29, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I am testing this patchset. Generally, it looks very well. I didn't see any bloat on catalog

I found an issue - TRUNCATE waits when gtt is used by different transactions, and TRUNCATE removes content in all sessions.

Is it expected?

Thanks for testing!

The waiting issue for TRUNCATE is interesting. I hadn't considered
that at all, so TRUNCATE on a global temporary table still takes an
access exclusive lock, like any other table, but I guess that's not
necessary. I wonder what lock level we could reduce it to.

I can't reproduce TRUNCATE removing content in all sessions, but
that's definitely not supposed to happen. Can you share what you did
to cause that?

Regards,
Dean

út 30. 6. 2026 v 22:21 odesílatel Dean Rasheed <dean.a.rasheed@gmail.com>
napsal:

On Tue, 30 Jun 2026 at 20:29, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

I am testing this patchset. Generally, it looks very well. I didn't see

any bloat on catalog

I found an issue - TRUNCATE waits when gtt is used by different

transactions, and TRUNCATE removes content in all sessions.

Is it expected?

Thanks for testing!

The waiting issue for TRUNCATE is interesting. I hadn't considered
that at all, so TRUNCATE on a global temporary table still takes an
access exclusive lock, like any other table, but I guess that's not
necessary. I wonder what lock level we could reduce it to.

I can't reproduce TRUNCATE removing content in all sessions, but
that's definitely not supposed to happen. Can you share what you did
to cause that?

I cannot reproduce it now. I was confused. I am sorry for the noise.

Regards

Pavel

Show quoted text

Regards,
Dean

On Fri, Jun 26, 2026 at 12: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.

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

I did another round of testing on the v5 GTT patch set, based on
commit 57f19774d6c. The build used --enable-cassert --enable-debug
--enable-tap-tests.

The additional tests covered:
--------------------------------------------------------------------------------
pg_dump / pg_restore
pg_basebackup / restore
permissions around pg_temp_statistic / pg_all_statistic
deeper partitioned GTT cases
DDL while another session is using the GTT
concurrent stress with multiple sessions
--------------------------------------------------------------------------------
All of those tests passed. I did not find correctness bugs in these areas.

A few items still seem worth addressing:
1. There is no SQL-visible diagnostic for PGPROC.tempfrozenxid / tempminmxid.
If an idle backend that used a GTT prevents datfrozenxid advancement,
it does not seem easy for a DBA to identify the backend from SQL. It
would be useful to expose this, either in pg_stat_activity or in a
small diagnostic view/function.

2. The base-vs-effective metadata model should be documented.
For GTTs, direct pg_class / pg_index reads may show base metadata,
while pg_temp_class / pg_temp_index, pg_relation_filenode(), psql \d,
and the planner use current-session effective metadata. Related DDL
behavior is also subtle: some operations affect only the current
session’s local storage, while rewrite operations may be rejected if
another session is using the GTT. This seems reasonable, but it should
be documented.

3. CREATE INDEX CONCURRENTLY on a GTT needs clarification.
The current behavior appears to follow the local-temp-table path and
treat CIC as a normal index build. That may be OK, since only the
current session’s local storage is built, but the nearby comment about
other backends not accessing the relation is misleading for GTTs. I
slightly lean toward rejecting CIC on GTTs explicitly, unless we want
to document that GTTs intentionally follow local temporary table
behavior here.

Minor cleanup:
describe.c still has FIXME: needs to be PG20 version checks using 190000.

Overall, the patch looks much more robust than I initially expected.
My remaining concerns are mostly diagnostics and documentation rather
than correctness bugs.

Thanks

Regards
Haibo

Hi, Dean

Thanks for working on this.

On Fri, 26 Jun 2026 at 20:37, 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.

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.)

While testing the v5 patch, I encountered a lock wait.

2026-07-01 14:18:43.603 CST [1486593] LOG: process 1486593 still waiting for AccessExclusiveLock on relation 16384 of database 5 after 1000.106 ms
2026-07-01 14:18:43.603 CST [1486593] DETAIL: Process holding the lock: 1486484. Wait queue: 1486593.
2026-07-01 14:18:43.603 CST [1486593] CONTEXT: waiting for AccessExclusiveLock on relation 16384 of database 5
2026-07-01 14:18:43.603 CST [1486593] STATEMENT: SELECT * FROM gtt_delete;

To reproduce:

Session 1:

CREATE GLOBAL TEMPORARY TABLE gtt_delete (id int) ON COMMIT DELETE ROWS;
BEGIN;
INSERT INTO gtt_delete VALUES (1);

Session 2:

SELECT * FROM gtt_delete; ---> blocked

Is this expected?

Regards,
Dean

--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

On Tue, 30 Jun 2026 at 22:41, Haibo Yan <tristan.yim@gmail.com> wrote:

I did another round of testing on the v5 GTT patch set, based on
commit 57f19774d6c. The build used --enable-cassert --enable-debug
--enable-tap-tests.

Thank you for testing!

Overall, the patch looks much more robust than I initially expected.
My remaining concerns are mostly diagnostics and documentation rather
than correctness bugs.

I agree with all your points. I'll try to post an update later this week.

Regards,
Dean

On Wed, 1 Jul 2026 at 07:24, Japin Li <japinli@hotmail.com> wrote:

Thanks for working on this.

Thank you for testing it!

While testing the v5 patch, I encountered a lock wait.

2026-07-01 14:18:43.603 CST [1486593] LOG: process 1486593 still waiting for AccessExclusiveLock on relation 16384 of database 5 after 1000.106 ms
2026-07-01 14:18:43.603 CST [1486593] DETAIL: Process holding the lock: 1486484. Wait queue: 1486593.
2026-07-01 14:18:43.603 CST [1486593] CONTEXT: waiting for AccessExclusiveLock on relation 16384 of database 5
2026-07-01 14:18:43.603 CST [1486593] STATEMENT: SELECT * FROM gtt_delete;

Is this expected?

Ah yes, you're right. That's not good.

The root cause looks to be the same issue that Pavel reported -- the
ON COMMIT DELETE ROWS does a TRUNCATE when the transaction is
committed, which requires an AccessExclusiveLock. I think the patch
should reduce the lock level required for TRUNCATE on a global
temporary relation to RowExclusiveLock, like DELETE.

I'll try to post an update later this week.

Regards,
Dean

On Fri, Jun 26, 2026 at 08:37:07PM +0100, Dean Rasheed wrote:

Thanks. Any reviews will be very helpful.

I drove Claude through a review. You can too, so there's nothing
special to it, but I'll go over it here, with a bit of my content added,
and hopefully y'all don't hate me for sharing LLM outputs.

1. You have an uninitialized have_gtrs in
src/backend/commands/vacuum.c:504.

2. TRUNCATE on a GTT can cause corruption.

Claude's description (I don't know how to evaluate this one myself):

RelationSetNewRelfilenumber writes session-local freeze xids into
shared pg_class for GTTs (relcache.c:4057-4076). Only relfilenode is
split via SetEffective_relfilenode. relpages=0, reltuples=-1,
relallvisible=0, relallfrozen=0, relfrozenxid=freezeXid,
relminmxid=minmulti, relpersistence are written directly into
classform and committed via CatalogTupleUpdate(pg_class, ...). Called
for GTT TRUNCATE (tablecmds.c:2301) and REINDEX (index.c:3908). For a
GTT, the new freezeXid reflects this session's freshly created
storage; clobbering shared pg_class.relfrozenxid with it can advance
the global horizon past what other sessions' local storage actually
has.

Exploring further Claude added:

The failure chain:

1. t=0: Session B opens gtt_foo, MyProc->tempfrozenxid = X0 (~old
RecentXmin). B never inserts anything; its local file has no rows
but is still bound to freezeXid X0.
2. t=1: Session A opens gtt_foo, inserts rows in xids X0…X1, commits.
A's local storage holds rows with xmin in that range. B's local
file is untouched.
3. t=2: Session A: TRUNCATE gtt_foo;. RelationSetNewRelfilenumber
runs. table_relation_set_new_filelocator returns freezeXid = X2
(~current). Code writes pg_class.relfrozenxid = X2 — no cap.
4. t=3: Any session VACUUMs any tables. vac_update_datfrozenxid walks
pg_class, sees gtt_foo.relfrozenxid = X2. If other pg_class rows
all have similar or newer values, newFrozenXid ≈ X2.
5. t=4: dbform->datfrozenxid = X2. vac_truncate_clog(X2, ...) at
:2019 truncates CLOG for everything older than X2.
6. t=5: Session B, whose local gtt_foo file was actually created back
at t=0 with freezeXid X0, has no problem itself (empty file). But
suppose C is any other backend also holding local storage with
xids older than X2 (any GTT, or a running long snapshot's stale
hint-bit-less pages) — reading such rows now does
TransactionIdDidCommit(x) → CLOG lookup → "ERROR: could not access
status of transaction x".
7. Worse: because the whole cluster's CLOG is truncated, this is not
just B's GTT. Any tuple anywhere the cluster still holds with an
xid older than X2 that lacks the HEAP_XMIN_COMMITTED hint bit
becomes unreadable. TOAST chains, index-only scan hints — all
vulnerable to torn interpretation.

Fix: the post-SetEffective_relfilenode block must update
temp_classform instead of classform when temp_tuple is valid (or skip
stats/freeze fields on classform entirely for GTT).

3. vac_get_min_tempfrozenxids() scans ProcGlobal->allProcCount without
ProcArrayLock (vacuum.c:1769-1799). Every comparable consumer
(vac_update_datfrozenxid, GetSnapshotData) acquires it.

Without this lock, a backend can exit mid-scan and a new backend
reuse the slot; you'll read databaseId/tempfrozenxid from
inconsistent slot generations. This can lead to corruption.

4. eoxact_usage_list_len / eoxact_usage_list_overflowed never reset at
end-of-xact (global_temp.c:1176-1179). Only the storage list counters
reset. Once a long-lived session overflows (>32 GTTs touched
cumulatively), every subsequent xact falls back to full hash_seq
scans. Correctness preserved, performance silently degrades.

5. MyProc->tempfrozenxid never decays. Set on first GTT access; only
moves forward on a session's VACUUM of its own GTT. A backend that
touches a GTT once and never vacuums holds back pg_class.relfrozenxid
advancement for all sessions for the connection's lifetime. No path
clears it on GlobalTempRelationDropped or when local usage_count
drops to zero.

I explored this one a bit because I was surprised that the frozenxid
in the catalog should be relevant to GTTs. If I understand correctly
this tempfrozenxid exists because while the GTT storage is
per-session, there is some coupling via the CLOG, but I don't
understand why _that_ has to be, and this is what Claude told me:

An alternative that would have avoided the coordination

You could have designed it so that GTT pg_class rows are skipped
from datfrozenxid computation (just like local temp), and each
backend's tempfrozenxid fed directly into vac_update_datfrozenxid
as an additional constraint. That would let the shared
pg_class.relfrozenxid be advisory/stale without a safety issue,
removing one leg of the coordination.

The current design chose to keep the shared row accurate — probably
because it's user-visible (SELECT relfrozenxid FROM pg_class) and
drives per-relation autovacuum urgency signals. That's the
tradeoff: an accurate shared row, in exchange for needing
cross-session horizon exchange. Same fundamental need as local
temp; different plumbing because the pg_class row is shared.

And, ah, well, I guess you could say that `relfrozenxid` in
`pg_class` is not meaningful for GTTs, or that you'll get your
session's GTT's relfrozenxid.

6. InsertPgTempIndexTuple mutates rel->rd_index->indisvalid in-place
(global_temp.c:885-892). Relcache fields are normally immutable
outside relcache.c. Works today but fragile.

7. AbortTransaction ordering — gtrs_dropped is list_freed
unconditionally in AtEOXact (:1178) even though the in-source comment
(:1077-1080) claims it'll be "repeated next commit". Mostly benign
(storage is reaped at exit) but the comment is wrong.

8. DeleteRelationTuple calls GlobalTempRelationDropped after
CatalogTupleDelete; GlobalTempRelationDropped then calls
get_rel_relkind(relid) (global_temp.c:1038). After a CCI, the
syscache will return '\0' and skip DeletePgTempIndexTuple. Pass
relkind explicitly instead of re-deriving it.

Nico
--

Hi, Dean

On Wed, 01 Jul 2026 at 08:32, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

On Wed, 1 Jul 2026 at 07:24, Japin Li <japinli@hotmail.com> wrote:

Thanks for working on this.

Thank you for testing it!

While testing the v5 patch, I encountered a lock wait.

2026-07-01 14:18:43.603 CST [1486593] LOG: process 1486593 still waiting for AccessExclusiveLock on relation 16384 of database 5 after 1000.106 ms
2026-07-01 14:18:43.603 CST [1486593] DETAIL: Process holding the lock: 1486484. Wait queue: 1486593.
2026-07-01 14:18:43.603 CST [1486593] CONTEXT: waiting for AccessExclusiveLock on relation 16384 of database 5
2026-07-01 14:18:43.603 CST [1486593] STATEMENT: SELECT * FROM gtt_delete;

Is this expected?

Ah yes, you're right. That's not good.

The root cause looks to be the same issue that Pavel reported -- the
ON COMMIT DELETE ROWS does a TRUNCATE when the transaction is
committed, which requires an AccessExclusiveLock. I think the patch
should reduce the lock level required for TRUNCATE on a global
temporary relation to RowExclusiveLock, like DELETE.

Here is my review on v5-0002. Please take a look.

1.
$ git am ~/Patches/gtt/*.patch
Applying: Save temporary table ON COMMIT actions to pg_class.
Applying: Basic support for global temporary tables.
Applying: Support indexes on global temporary tables.
Applying: Support global temporary sequences.
Applying: Support global temporary catalog tables and add pg_temp_class.
Applying: Add relation statistics columns to pg_temp_class.
Applying: Add relfrozenxid and relminmxid columns to pg_temp_class.
.git/rebase-apply/patch:940: new blank line at EOF.
+
warning: 1 line adds whitespace errors.
Applying: Add pg_temp_statistic global temporary catalog table.
Applying: Add pg_temp_statistic_ext_data global temporary catalog table.
Applying: Add pg_temp_index global temporary catalog table.

2.
+static void
+gtr_delete_all_storage_on_exit(int code, Datum arg)
+{
+	ProcNumber	backend;
+	HASH_SEQ_STATUS status;
+	GtrStorageEntry *entry;
+	SMgrRelation srel;
+
+	/* Loop over all storage created and delete it */
+	backend = ProcNumberForTempRelations();
+	hash_seq_init(&status, gtr_local_storage);
+	while ((entry = hash_seq_search(&status)) != NULL)
+	{
+		srel = smgropen(entry->rlocator, backend);
+		smgrdounlinkall(&srel, 1, false);
+		smgrclose(srel);
+	}
+}

We can restrict srel to the loop scope.

3.
+gtr_remove_all_usage_on_exit(int code, Datum arg)
+{
+	HASH_SEQ_STATUS status;
+	GtrUsageEntry *local_entry;
+	GtrSharedUsageKey key;
+	GtrSharedUsageEntry *shared_entry;
+
+	/* Loop over all the global temporary relations we were using */
+	hash_seq_init(&status, gtr_local_usage);
+	while ((local_entry = hash_seq_search(&status)) != NULL)
+	{
+		/* Find the shared usage entry */
+		key.dbid = MyDatabaseId;
+		key.relid = local_entry->relid;
+		shared_entry = dshash_find(gtr_shared_usage, &key, true);

Same as above: move the declarations of key and shared_entry inside the loop.

4.
+static void
+gtr_init_usage_tables(void)
+{
+	HASHCTL		ctl;
+	MemoryContext oldcontext;
+
+	/* Local usage table */
+	if (gtr_local_usage == NULL)
+	{
+		ctl.keysize = sizeof(Oid);
+		ctl.entrysize = sizeof(GtrUsageEntry);
+
+		gtr_local_usage = hash_create("Global temporary relations in use locally",
+									  128, &ctl, HASH_ELEM | HASH_BLOBS);
+	}
+
+	/* Shared usage table */
+	if (gtr_shared_usage == NULL)
+	{
+		/* Use a lock to ensure only one process creates the table */
+		LWLockAcquire(GlobalTempRelControlLock, LW_EXCLUSIVE);
+
+		/* Be sure any local memory allocated by DSA routines is persistent */
+		oldcontext = MemoryContextSwitchTo(TopMemoryContext);

Likewise, narrow ctl to the first if and oldContext to the second.

5.
+	 * the usage hash table entries for it.  Otherwise, reset the hash entry's
+	 * subids to zero.

Instead of zero, how about using an invalid sub-transaction ID?

6.
+static void
+gtr_process_invalidated_gtrs(void)
+{
+	MemoryContext oldcontext;
+	Oid			relid;
+
+	/*
+	 * Scan the gtrs_invalidated list and add any dropped relations to the
+	 * gtrs_dropped list.  Since the transaction might fail later on, we need
+	 * the gtrs_dropped list to persist until we can successfully process it.
+	 */
+	oldcontext = MemoryContextSwitchTo(TopMemoryContext);
+
+	/*
+	 * As we scan the gtrs_invalidated list, more invalidation messages might
+	 * arrive, so keep going until it is empty.
+	 */
+	while (gtrs_invalidated != NIL)
+	{
+		/* Pop the last relid from the list */
+		relid = llast_oid(gtrs_invalidated);

Restrict relid to the while loop scope.

7.
+void
+TrackGlobalTempRelationStorage(Oid relid, RelFileLocator rlocator,
+							   ProcNumber backend, bool create)
+{
+	GtrStorageEntry *entry;
+	bool		found;
+	SMgrRelation srel;
+
+	if (create)
+	{
+		/* Initialize the storage table, if necessary */
+		gtr_init_storage_table();
+
+		/* Insert an entry to track the storage */
+		entry = hash_search(gtr_local_storage, &rlocator, HASH_ENTER, &found);

Likewise, restrict found and srel to the if statement.

8.
+		/* Register the ON COMMIT action for relation, if it's DELETE ROWS */
+		Assert(relation->rd_rel->reloncommit == RELONCOMMIT_PRESERVE_ROWS ||
+			   relation->rd_rel->reloncommit == RELONCOMMIT_DELETE_ROWS);
+
+		if (relation->rd_rel->reloncommit == RELONCOMMIT_DELETE_ROWS)
+			register_on_commit_action(relation->rd_id, ONCOMMIT_DELETE_ROWS);

How about moving the comment to just before the if statement?

9.
+void
+InvalidateGlobalTempRelation(Oid relid)
+{
+	MemoryContext oldcontext;
+	HASH_SEQ_STATUS status;
+	GtrUsageEntry *entry;
+
+	/* Quick exit if we haven't used any global temporary relations */
+	if (gtr_local_usage == NULL)
+		return;

Restrict status and entry to the else statement.

10.
+void
+GlobalTempRelationDropped(Oid relid)

Not a fan of this name, but no better idea yet.

11.
+void
+PreCommit_GlobalTempRelation(void)
+{
+	HASH_SEQ_STATUS status;
+	GtrStorageEntry *storage_entry;
+	Relation	rel;
+

Restrict rel to the while loop.

12.
+List *
+AllGlobalTempRelationsInUse(Oid dbid)

How about GetGlobalTempReplationsInUse()?

13.
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						 errmsg("cannot create relations in temporary schemas of other sessions")));

For consistency with the above, I suggest changing the error message to:

cannot create global temporary relation in temporary schema of other sessions

14.
+ * If they're global temp relations, reassign rel2's storage to rel1.

We use "global temporary relations" elsewhere – should we keep the same
terminology here?

15.
+                     errmsg(RELATION_IS_GLOBAL_TEMP(relation)
+                            ? "cannot create a local temporary relation as partition of global temporary relation \"%s\""
+                            : "cannot create a temporary relation as partition of permanent relation \"%s\"",

Should we use the following error message:

cannot create a local temporary relation as partition of permanent relation \"%s\"

16.
+                            : relpersistence == RELPERSISTENCE_GLOBAL_TEMP
+                            ? "cannot create a global temporary relation as partition of local temporary relation \"%s\""
                             : "cannot create a permanent relation as partition of temporary relation \"%s\"",

Same as above:

cannot create a permanent relation as partition of local temporary relation \"%s\"

17.
+            if (RELATION_IS_GLOBAL_TEMP(OldHeap) &&
+                IsOtherUsingGlobalTempRelation(RelationGetRelid(OldHeap)))
+                ereport(ERROR,
+                        errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                        errmsg("cannot rewrite global temporary table \"%s\" because it is being used in another session",
+                               RelationGetRelationName(OldHeap)));

18.
+                            errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                            errmsg("cannot add or alter constraints of global temporary table \"%s\" because it is being used in another session",
+                                   get_rel_name(tab->relid)));

19.
 			if (pkrel->rd_rel->relpersistence != RELPERSISTENCE_TEMP)
 				ereport(ERROR,
 						(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-						 errmsg("constraints on temporary tables may reference only temporary tables")));
+						 errmsg("constraints on local temporary tables may reference only local temporary tables")));
 			if (!pkrel->rd_islocaltemp || !rel->rd_islocaltemp)
 				ereport(ERROR,
 						(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
-						 errmsg("constraints on temporary tables must involve temporary tables of this session")));
+						 errmsg("constraints on local temporary tables must involve local temporary tables of this session")));
+			break;
+		case RELPERSISTENCE_GLOBAL_TEMP:
+			if (!RELATION_IS_GLOBAL_TEMP(pkrel))
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+						 errmsg("constraints on global temporary tables may reference only global temporary tables")));

s/global temporary table/global temporary relation/g
s/local temporary table/local temporary relation/g

20.
 		attachrel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-				 errmsg("cannot attach a temporary relation as partition of permanent relation \"%s\"",
+				 errmsg(RELATION_IS_GLOBAL_TEMP(rel)
+						? "cannot attach a local temporary relation as partition of global temporary relation \"%s\""
+						: "cannot attach a temporary relation as partition of permanent relation \"%s\"",
 						RelationGetRelationName(rel))));

Same as 15:

cannot attach a local temporary relation as partition of permanent relation \"%s\"

21.
 		attachrel->rd_rel->relpersistence != RELPERSISTENCE_TEMP)
 		ereport(ERROR,
 				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-				 errmsg("cannot attach a permanent relation as partition of temporary relation \"%s\"",
+				 errmsg(RELATION_IS_GLOBAL_TEMP(attachrel)
+						? "cannot attach a global temporary relation as partition of local temporary relation \"%s\""
+						: "cannot attach a permanent relation as partition of temporary relation \"%s\"",
 						RelationGetRelationName(rel))));

Same as 16:

cannot attach a permanent relation as partition of local temporary relation \"%s\

22.
@@ -22776,7 +22828,9 @@ createPartitionTable(List **wqueue, RangeVar *newPartName,
 		newPartName->relpersistence == RELPERSISTENCE_TEMP)
 		ereport(ERROR,
 				errcode(ERRCODE_WRONG_OBJECT_TYPE),
-				errmsg("cannot create a temporary relation as partition of permanent relation \"%s\"",
+				errmsg(parent_relform->relpersistence == RELPERSISTENCE_GLOBAL_TEMP
+					   ? "cannot create a local temporary relation as partition of global temporary relation \"%s\""
+					   : "cannot create a temporary relation as partition of permanent relation \"%s\"",
 					   RelationGetRelationName(parent_rel)));

/* Permanent rels cannot be partitions belonging to a temporary parent. */

Suggestion:

cannot create a local temporary relation as partition of permanent relation \"%s\"

23.
@@ -22784,7 +22838,9 @@ createPartitionTable(List **wqueue, RangeVar *newPartName,
 		parent_relform->relpersistence == RELPERSISTENCE_TEMP)
 		ereport(ERROR,
 				errcode(ERRCODE_WRONG_OBJECT_TYPE),
-				errmsg("cannot create a permanent relation as partition of temporary relation \"%s\"",
+				errmsg(newPartName->relpersistence == RELPERSISTENCE_GLOBAL_TEMP
+					   ? "cannot create a global temporary relation as partition of local temporary relation \"%s\""
+					   : "cannot create a permanent relation as partition of temporary relation \"%s\"",
 					   RelationGetRelationName(parent_rel)));

Suggest:

cannot create a permanent relation as partition of local temporary relation \"%s\"

--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.