Thread

Commits

doc: clarify MERGE PARTITIONS adjacency requirement
- 57f19774d6c8 master cited

Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-06-21T19:08:31Z

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

Re: Global temporary tables

Andrew Dunstan <andrew@dunslane.net> — 2026-06-21T22:05:55Z

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

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-06-22T08:56:54Z

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

Re: Global temporary tables

Andrew Dunstan <andrew@dunslane.net> — 2026-06-22T13:43:00Z


> 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

Re: Global temporary tables

Pavel Stehule <pavel.stehule@gmail.com> — 2026-06-22T15:30:59Z

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


>
> Cheers
>
> Andrew
>
>
>
>
>

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-06-23T09:12:43Z

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

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-06-23T23:34:26Z

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

Re: Global temporary tables

Gilles DAROLD <gilles@darold.net> — 2026-06-24T04:15:21Z

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/

Re: Global temporary tables

Kirk Wolak <wolakk@gmail.com> — 2026-06-26T03:51:53Z

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.

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-06-26T19:37:07Z

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

Re: Global temporary tables

Kirk Wolak <wolakk@gmail.com> — 2026-06-26T21:37:21Z

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

Re: Global temporary tables

Pavel Stehule <pavel.stehule@gmail.com> — 2026-06-30T19:29:05Z

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


>
> Regards,
> Dean
>

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-06-30T20:21:27Z

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

Re: Global temporary tables

Pavel Stehule <pavel.stehule@gmail.com> — 2026-06-30T20:32:19Z

ú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





> Regards,
> Dean
>

Re: Global temporary tables

Haibo Yan <tristan.yim@gmail.com> — 2026-06-30T21:41:08Z

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

Re: Global temporary tables

Japin Li <japinli@hotmail.com> — 2026-07-01T06:24:46Z

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.

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-07-01T07:28:07Z

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

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-07-01T07:32:37Z

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

Re: Global temporary tables

Nico Williams <nico@cryptonector.com> — 2026-07-01T23:20:23Z

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

Re: Global temporary tables

Japin Li <japinli@hotmail.com> — 2026-07-02T09:11:35Z

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.

Re: Global temporary tables

Japin Li <japinli@hotmail.com> — 2026-07-03T03:16:57Z

Hi, Dean

On Thu, 02 Jul 2026 at 17:11, Japin Li <japinli@hotmail.com> wrote:
> 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\"
>
>

While testing the v5 patch, I noticed that the index of a table with ON COMMIT
DELETE ROWS has the PRESERVE ROWS flag.  See the example below.


	postgres=# CREATE EXTENSION pageinspect;
	CREATE EXTENSION
	postgres=# CREATE GLOBAL TEMPORARY TABLE gtt_delete (id int primary key, info text) ON COMMIT DELETE ROWS;
	CREATE TABLE
	postgres=# CREATE GLOBAL TEMPORARY TABLE gtt_preserve (id int primary key, info text);
	CREATE TABLE
	postgres=# SELECT relname, reloncommit FROM pg_class WHERE relname ~ '^gtt_.*';
	      relname      | reloncommit
	-------------------+-------------
	 gtt_delete        | d
	 gtt_delete_pkey   | p                <-- See here.
	 gtt_preserve      | p
	 gtt_preserve_pkey | p
	(4 rows)
	postgres=# BEGIN;
	BEGIN
	postgres=*# INSERT INTO gtt_delete SELECT id FROM generate_series(1, 10000) id;
	INSERT 0 10000
	postgres=*# SELECT * FROM bt_metap('gtt_delete_pkey');
	 magic  | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage
	--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
	 340322 |       4 |    3 |     1 |        3 |         1 |                         0 |                      -1 | t
	(1 row)
	
	postgres=*# SELECT * FROM bt_page_items('gtt_delete_pkey', 1) limit 2;
	 itemoffset | ctid  | itemlen | nulls | vars |          data           | dead | htid  | tids
	------------+-------+---------+-------+------+-------------------------+------+-------+------
	          1 | (1,1) |      16 | f     | f    | 6f 01 00 00 00 00 00 00 |      |       |
	          2 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | f    | (0,1) |
	(2 rows)
	
	postgres=*# END;
	COMMIT
	postgres=# SELECT * FROM bt_metap('gtt_delete_pkey');
	 magic  | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage
	--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
	 340322 |       4 |    0 |     0 |        0 |         0 |                         0 |                      -1 | t
	(1 row)
	
	postgres=# SELECT * FROM bt_page_items('gtt_delete_pkey', 1) limit 2;
	ERROR:  block number 1 is out of range
	postgres=# BEGIN;
	BEGIN
	postgres=*# INSERT INTO gtt_preserve SELECT id FROM generate_series(1, 10000) id;
	INSERT 0 10000
	postgres=*# SELECT * FROM bt_metap('gtt_preserve_pkey');
	 magic  | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage
	--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
	 340322 |       4 |    3 |     1 |        3 |         1 |                         0 |                      -1 | t
	(1 row)
	
	postgres=*# SELECT * FROM bt_page_items('gtt_preserve_pkey', 1) limit 2;
	 itemoffset | ctid  | itemlen | nulls | vars |          data           | dead | htid  | tids
	------------+-------+---------+-------+------+-------------------------+------+-------+------
	          1 | (1,1) |      16 | f     | f    | 6f 01 00 00 00 00 00 00 |      |       |
	          2 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | f    | (0,1) |
	(2 rows)
	
	postgres=*# COMMIT;
	COMMIT
	postgres=# SELECT * FROM bt_metap('gtt_preserve_pkey');
	 magic  | version | root | level | fastroot | fastlevel | last_cleanup_num_delpages | last_cleanup_num_tuples | allequalimage
	--------+---------+------+-------+----------+-----------+---------------------------+-------------------------+---------------
	 340322 |       4 |    3 |     1 |        3 |         1 |                         0 |                      -1 | t
	(1 row)
	
	postgres=# SELECT * FROM bt_page_items('gtt_preserve_pkey', 1) limit 2;
	 itemoffset | ctid  | itemlen | nulls | vars |          data           | dead | htid  | tids
	------------+-------+---------+-------+------+-------------------------+------+-------+------
	          1 | (1,1) |      16 | f     | f    | 6f 01 00 00 00 00 00 00 |      |       |
	          2 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00 | f    | (0,1) |
	(2 rows)


Is this expected behavior?

Since the index data for gtt_delete is cleared upon transaction commit, I would
expect its reloncommit flag to be 'd'.

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

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-07-03T23:32:42Z

On Thu, 2 Jul 2026 at 00:20, Nico Williams <nico@cryptonector.com> wrote:
>
> 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.

Wow, we really will be out of a job in a few years :-)

Thanks for doing that!

It managed to find a lot that I'd overlooked. I'm attaching v6, with
fixes for most of those issues plus a couple more.

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

Fixed in v6

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

Yes, it needed to use the SetEffective_* methods for all those.

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

v6 adds new SetEffective_relfrozenxid/relminmxid() methods and uses those.

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

Fixed in v6.

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

Fixed in v6

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

Yes, that's a very accurate description of the design choice I made.
Having thought more about it, I think the other choice is better,
because it's simpler, and it reduces churn on pg_class.

So v6 now sets pg_class.relfrozenxid and pg_class.relminmxid to 0 for
all global temporary relations, and only the pg_temp_class values are
ever updated. That seems more logical, because a GTT has no storage
until it is used by some session, so it makes sense for the frozen
XIDs in pg_class to be 0.

Also, DROP and TRUNCATE now cause tempfrozenxid and tempminmxid to be
updated. There was another bug there -- things like DROP, REPACK, and
TRUNCATE that advance tempfrozenxid and tempminmxid should only do so
if the transaction commits. Each of those (but not VACUUM) can be
rolled back, in which case tempfrozenxid and tempminmxid should not be
advanced.

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

Actually, it's InitGlobalTempRelation() that does this, not
InsertPgTempIndexTuple(). InitGlobalTempRelation() is only called from
one function in relcache.c, as the final part of setting up the
relcache entry. In an early prototype, I had InitGlobalTempRelation()
in relcache.c, and I guess it could be moved back, but I don't think
it's particularly bad the way it is.

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

Yeah, when I originally started writing that, I intended that it would
be repeated on the next commit, but in the end, I decided maybe that
wouldn't be a good idea, because if it failed for some reason, it
might keep failing, making every subsequent transaction fail. So I've
just updated that comment, and left that code as it was.

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

This was a nice catch. I fixed it in v6 by saving the relkind on the
usage entry, so it doesn't need to look it up again.

In addition to those, I also fixed a couple of things in ON COMMIT
DELETE ROWS processing:

1. PreCommit_GlobalTempRelation() needs to be called *before* ON
COMMIT handling, in case another session drops a GTT with ON COMMIT
DELETE ROWS -- we need to remove it from the on_commits list before ON
COMMIT processing happens.

2. The ON COMMIT DELETE ROWS processing now does its work with a
RowExclusiveLock for GTTs, preventing blocking/deadlock risks.

I'll try and post another update soon, addressing other people's
review comments.

Regards,
Dean

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-07-03T23:37:12Z

On Wed, 1 Jul 2026 at 07:24, Japin Li <japinli@hotmail.com> wrote:
>
> 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?

Thanks for testing!

This is fixed in v6.

Regards,
Dean

Re: Global temporary tables

Nico Williams <nico@cryptonector.com> — 2026-07-04T01:00:49Z

On Sat, Jul 04, 2026 at 12:32:42AM +0100, Dean Rasheed wrote:
> On Thu, 2 Jul 2026 at 00:20, Nico Williams <nico@cryptonector.com> wrote:
> >
> > 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.
> 
> Wow, we really will be out of a job in a few years :-)

I seriously doubt it.  And I'm guessing you coded w/o assistance, so
hats off because it looks pretty good.

I only used an LLM for the review because I'm not sufficiently familiar
with PG internals to do a solid review myself, and I want to help this
along.

> >    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.
> 
> Yes, that's a very accurate description of the design choice I made.
> Having thought more about it, I think the other choice is better,
> because it's simpler, and it reduces churn on pg_class.

Good.  I think seeing some other session's frozenxid would be weird, and
a bit of a leak.

> So v6 now sets pg_class.relfrozenxid and pg_class.relminmxid to 0 for
> all global temporary relations, and only the pg_temp_class values are
> ever updated. That seems more logical, because a GTT has no storage
> until it is used by some session, so it makes sense for the frozen
> XIDs in pg_class to be 0.

Yes.

> I'll try and post another update soon, addressing other people's
> review comments.

I'll drive an LLM through this too sometime, especially with an eye to
testing.

I'm super excited to have GTTs in PG.  It's going to make all sorts of
things much easier for me and many many others, so thank you so much!

Nico
--

Re: Global temporary tables

John Naylor <johncnaylorls@gmail.com> — 2026-07-05T08:16:48Z

On Sat, Jul 4, 2026 at 6:33 AM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> So v6 now sets pg_class.relfrozenxid and pg_class.relminmxid to 0 for
> all global temporary relations, and only the pg_temp_class values are
> ever updated. That seems more logical, because a GTT has no storage
> until it is used by some session, so it makes sense for the frozen
> XIDs in pg_class to be 0.

Unfortunately, the cure seems to be worse than the disease:

-- Session 1
CREATE GLOBAL TEMPORARY TABLE g (a int);
-- Session 1 is no longer needed.

-- Session 2 (a fresh session that has never touched g)

BEGIN;
  SELECT count(*) FROM g;    -- first access here: queues g's pg_temp_class row
                             -- (still unflushed inside this transaction)
  SAVEPOINT s1;
    DROP TABLE g;            -- removes that pending queue entry
  ROLLBACK TO s1;            -- pg_class row is restored by MVCC;
                             -- the queue entry is not restored
  INSERT INTO g VALUES (1);  -- the table still works normally
COMMIT;

-- The table is live and holds committed data:
SELECT count(*) AS live_rows FROM g;
--  live_rows
-- -----------
--          1

-- The table cannot be vacuumed.
-- On assert builds it hits an assert. On production builds...
VACUUM g;
-- WARNING:  bypassing nonessential maintenance of table
"john.public.g" as a failsafe after 0 index scans
-- DETAIL:  The table's relfrozenxid or relminmxid is too far in the past.
-- HINT:  Consider increasing configuration parameter
"maintenance_work_mem" or "autovacuum_work_mem".
-- You might also need to consider other ways for VACUUM to keep up
with the allocation of transaction IDs.
-- ERROR:  cache lookup failed for global temp relation 16385

-- The error is because there is no matching row in pg_temp_class
SELECT count(*) AS temp_class_rows
FROM   pg_temp_class
WHERE  oid = 'g'::regclass;
--  temp_class_rows
-- -----------------
--                0

-- ...so dropping doesn't work either:
DROP TABLE g;
-- ERROR:  cache lookup failed for global temp relation 16385

I'm starting to think the queue mechanism needs serious attention, if
not a complete re-think.


On Mon, Jun 22, 2026 at 8:43 PM Andrew Dunstan <andrew@dunslane.net> wrote:
> > On Jun 22, 2026, at 4:57 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> > 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.

I was actually hoping to hear more about how the approaches differ so
we can weigh the architectural tradeoffs from both operational and
maintenance standpoints. (I suppose I just volunteered myself for
that, didn't I...)

--
John Naylor
Amazon Web Services

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-07-05T09:58:44Z

On Sun, 5 Jul 2026 at 09:17, John Naylor <johncnaylorls@gmail.com> wrote:
>
> BEGIN;
>   SELECT count(*) FROM g;    -- first access here: queues g's pg_temp_class row
>                              -- (still unflushed inside this transaction)
>   SAVEPOINT s1;
>     DROP TABLE g;            -- removes that pending queue entry
>   ROLLBACK TO s1;            -- pg_class row is restored by MVCC;
>                              -- the queue entry is not restored
>   INSERT INTO g VALUES (1);  -- the table still works normally
> COMMIT;
>
> I'm starting to think the queue mechanism needs serious attention, if
> not a complete re-think.

Thanks for looking!

Yes, you're right. In that example, the pg_temp_class tuple gets lost
entirely. It really needs the same sort of sub XID tracking as the
usage/storage code, to ensure that they never get lost. I'll take a
look.

Regards,
Dean

Re: Global temporary tables

Japin Li <japinli@hotmail.com> — 2026-07-06T08:35:06Z

Hi, Dean

Thanks for updating the v6 patch.

On Sat, 04 Jul 2026 at 00:37, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> On Wed, 1 Jul 2026 at 07:24, Japin Li <japinli@hotmail.com> wrote:
>>
>> 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?
>
> Thanks for testing!
>
> This is fixed in v6.
>

I tested the v6 patch, and the blocked is fixed.

During testing of the v6 patch, I observed that sequences belonging to global
temporary tables defined with ON COMMIT DELETE ROWS are not reset after the
transaction commits.

This is similar to the index I found in [1].  Here is the example:

    INSERT INTO gtt_delete (info) VALUES ('row 1');
    postgres=# CREATE GLOBAL TEMPORARY TABLE gtt_delete (id serial primary key, info text) ON COMMIT DELETE ROWS;
    CREATE TABLE
    postgres=# SELECT relname, reloncommit FROM pg_class WHERE relname ~ '^gtt_delete.*';
          relname      | reloncommit
    -------------------+-------------
     gtt_delete        | d
     gtt_delete_id_seq | p      <-- The sequence is marked as PRESERVE.
     gtt_delete_pkey   | p      
    (3 rows)
    
    postgres=# SELECT currval('gtt_delete_id_seq');
    ERROR:  currval of sequence "gtt_delete_id_seq" is not yet defined in this session
    postgres=# BEGIN;
    BEGIN
    postgres=*# INSERT INTO gtt_delete (info) VALUES ('row 1');
    INSERT 0 1
    postgres=*# SELECT * FROM gtt_delete;
     id | info
    ----+-------
      1 | row 1
    (1 row)
    
    postgres=*# SELECT currval('gtt_delete_id_seq');
     currval
    ---------
           1
    (1 row)
    
    postgres=*# END;
    COMMIT
    postgres=# SELECT currval('gtt_delete_id_seq');  -- The sequence still exists.
     currval
    ---------
           1
    (1 row)
    postgres=*# END;
    COMMIT
    postgres=# SELECT currval('gtt_delete_id_seq');  -- The sequence doesn't reset.
     currval
    ---------
           2
    (1 row)

The sequence doesn't reset after commit – is this intended?

[1] https://postgr.es/m/SY7PR01MB1092159B3ED3CA6F4401E465EB6F42@SY7PR01MB10921.ausprd01.prod.outlook.com

> Regards,
> Dean

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

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-07-06T09:43:42Z

On Sun, 5 Jul 2026 at 10:58, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> On Sun, 5 Jul 2026 at 09:17, John Naylor <johncnaylorls@gmail.com> wrote:
> >
> > I'm starting to think the queue mechanism needs serious attention, if
> > not a complete re-think.
>
> Yes, you're right. In that example, the pg_temp_class tuple gets lost
> entirely. It really needs the same sort of sub XID tracking as the
> usage/storage code

Attached is v7, doing that.

Locally, I have been stress-testing this using a hacked-together
python script that does random sequences of CREATE, DROP, BEGIN,
SAVEPOINT, RELEASE, ROLLBACK TO, INSERT, SELECT, COMMIT, ROLLBACK, and
TRUNCATE. I'm somewhat disappointed that it didn't pick up the
original problem, so I'll go back and look at it again.

v7 now also includes tempfrozenxid and tempminmxid in the
pg_stat_activity view, allowing those to be monitored.

Regards,
Dean

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-07-06T10:12:32Z

On Mon, 6 Jul 2026 at 09:35, Japin Li <japinli@hotmail.com> wrote:
>
> During testing of the v6 patch, I observed that sequences belonging to global
> temporary tables defined with ON COMMIT DELETE ROWS are not reset after the
> transaction commits.
>
>     postgres=# BEGIN;
>     postgres=*# INSERT INTO gtt_delete (info) VALUES ('row 1');
>     postgres=*# SELECT * FROM gtt_delete;
>     postgres=*# SELECT currval('gtt_delete_id_seq');
>     postgres=*# END;
>     postgres=# SELECT currval('gtt_delete_id_seq');  -- The sequence still exists.
>      currval
>     ---------
>            1
>     (1 row)
>     postgres=*# END;
>     COMMIT
>     postgres=# SELECT currval('gtt_delete_id_seq');  -- The sequence doesn't reset.
>      currval
>     ---------
>            2
>     (1 row)
>
> The sequence doesn't reset after commit – is this intended?
>

I think that's what I would expect. This matches what happens with
normal (local) temp tables -- sequences are not reset after commit (or
rollback, for that matter).

Regarding pg_class.reloncommit, 'p' (preserve) seems correct for a
sequence attached to a GTT with ON COMMIT DELETE ROWS, because the
sequence data is preserved. For indexes, not so much, because the
index data is deleted on commit. I guess it could be NULL for anything
that's not a table.

Andrew's patch uses a reloption to store the table's on-commit state,
but to me that doesn't seem quite right, and it requires special code
to mark it as a "private" reloption that is non-user-editable. It does
simplify the question of what to do for non-tables though. What do
others think?

Regards,
Dean

Re: Global temporary tables

Zsolt Parragi <zsolt.parragi@percona.com> — 2026-07-06T21:53:51Z

Hello!

This is a great feature, I did some testing of it.

1. If a table has an ON COMMIT clause:

CREATE GLOBAL TEMPORARY TABLE gtt_del (id int) ON COMMIT DELETE ROWS

Then pg_dump forgets that.

2. Even an aborted transaction pins frozenxid:

CREATE GLOBAL TEMPORARY TABLE gtt (id int);

In one session

BEGIN; SELECT count(*) FROM gtt; ABORT;
-- and keep session alive

Then in another session,
SELECT datfrozenxid FROM pg_database WHERE datname=current_database();
-- do some work
VACUUM (FREEZE);
SELECT datfrozenxid FROM pg_database WHERE datname=current_database();
-- ...

And repeat the above a few times for the second session, datfrozenxid
will remain the same until the first session is closed.

3. in AtEOSubXact_UsageCleanup and also in AtEOSubXact_StorageCleanup

+		else
+			gtr_remove_usage(entry->relid);
+	}
+
+	/* Update the usage stopped subid */
+	if (entry->stopped_subid == mySubid)
+	{
+		if (isCommit)
+			entry->stopped_subid = parentSubid;
+		else
+			entry->stopped_subid = InvalidSubTransactionId;
+	}

if we follow the else branch with gtr_remove_usage, shouldn't the
function immediately return?

4. in AtEOSubXact_PendingInsertCleanup

+		else
+			hash_search(pending_inserts, &entry->relid, HASH_REMOVE, NULL);
+	}
+
+	/* Update the inserted subid (may rollback flush) */
+	if (entry->inserted_subid == mySubid)
+	{
+		if (isCommit)
+			entry->inserted_subid = parentSubid;
+		else
+		{
+			entry->inserted_subid = InvalidSubTransactionId;
+			have_inserts_to_flush = true;
+		}
+	}

same question, isn't the else branch missing a return?


5. in gtr_remove_usage / gtr_remove_all_usage_on_exit

+	shared_entry = dshash_find(gtr_shared_usage, &key, true);
+
+	if (shared_entry->usage_count > 1)

and in gtr_record_usage

+	/* Flag the usage entry for eoxact cleanup */
+	EOXactUsageListAdd(relid);
+
+	/* Add/update shared usage entry */
+	key.dbid = MyDatabaseId;
+	key.relid = relid;
+	shared_entry = dshash_find_or_insert(gtr_shared_usage, &key, &found);

Can't this crash with a null pointer access in the above if
dshash_find_or_insert fails with OOM?

Re: Global temporary tables

Japin Li <japinli@hotmail.com> — 2026-07-07T01:09:44Z

On Mon, 06 Jul 2026 at 11:12, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> On Mon, 6 Jul 2026 at 09:35, Japin Li <japinli@hotmail.com> wrote:
>>
>> During testing of the v6 patch, I observed that sequences belonging to global
>> temporary tables defined with ON COMMIT DELETE ROWS are not reset after the
>> transaction commits.
>>
>>     postgres=# BEGIN;
>>     postgres=*# INSERT INTO gtt_delete (info) VALUES ('row 1');
>>     postgres=*# SELECT * FROM gtt_delete;
>>     postgres=*# SELECT currval('gtt_delete_id_seq');
>>     postgres=*# END;
>>     postgres=# SELECT currval('gtt_delete_id_seq');  -- The sequence still exists.
>>      currval
>>     ---------
>>            1
>>     (1 row)
>>     postgres=*# END;
>>     COMMIT
>>     postgres=# SELECT currval('gtt_delete_id_seq');  -- The sequence doesn't reset.
>>      currval
>>     ---------
>>            2
>>     (1 row)
>>
>> The sequence doesn't reset after commit – is this intended?
>>
>
> I think that's what I would expect. This matches what happens with
> normal (local) temp tables -- sequences are not reset after commit (or
> rollback, for that matter).
>

Thanks for the explanation!  I hadn't noticed this before.

> Regarding pg_class.reloncommit, 'p' (preserve) seems correct for a
> sequence attached to a GTT with ON COMMIT DELETE ROWS, because the
> sequence data is preserved. For indexes, not so much, because the
> index data is deleted on commit. I guess it could be NULL for anything
> that's not a table.
>

+1 for setting it to NULL for non‑table objects, since CREATE INDEX and
CREATE SEQUENCE do not support the ON COMMIT clause.

> Andrew's patch uses a reloption to store the table's on-commit state,
> but to me that doesn't seem quite right, and it requires special code
> to mark it as a "private" reloption that is non-user-editable. It does
> simplify the question of what to do for non-tables though. What do
> others think?
>

I'm on board with your approach to the on-commit state.

> Regards,
> Dean

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

Re: Global temporary tables

John Naylor <johncnaylorls@gmail.com> — 2026-07-07T09:06:01Z

On Mon, Jul 6, 2026 at 4:43 PM Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>
> On Sun, 5 Jul 2026 at 10:58, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> > Yes, you're right. In that example, the pg_temp_class tuple gets lost
> > entirely. It really needs the same sort of sub XID tracking as the
> > usage/storage code
>
> Attached is v7, doing that.

Okay, I followed up with various attempts to get the new sub XID
tracking to do something dire, and couldn't find anything.

I did find something else, however:

set statement_timeout  = '3s';
set debug_discard_caches = 1;

create global temporary table g (a int);

This enters an infinite loop after passing the point where the timeout
would have done any good, and it looks like
gtr_process_invalidated_gtrs() didn't take into account whole-cache
invalidation -- here's a partial backtrace:

(BTW, I forgot to mention I set this WoA in my last message -- I've
left it like that for now)

#21 0x00000000009c0d04 in RelationCacheInvalidate
(debug_discard=debug_discard@entry=true) at
../src/backend/utils/cache/relcache.c:3209
#22 0x00000000009b0c4e in InvalidateSystemCachesExtended
(debug_discard=debug_discard@entry=true) at
../src/backend/utils/cache/inval.c:793
#23 0x00000000009b0d5b in AcceptInvalidationMessages () at
../src/backend/utils/cache/inval.c:975
#24 0x0000000000858f80 in LockRelationOid (relid=2662, lockmode=1) at
../src/backend/storage/lmgr/lmgr.c:136
#25 0x00000000004c3dfd in relation_open
(relationId=relationId@entry=2662, lockmode=lockmode@entry=1) at
../src/backend/access/common/relation.c:56
#26 0x0000000000528579 in index_open
(relationId=relationId@entry=2662, lockmode=lockmode@entry=1) at
../src/backend/access/index/indexam.c:138
#27 0x0000000000527cf7 in systable_beginscan
(heapRelation=heapRelation@entry=0x7f2f5efbeeb8,
indexId=indexId@entry=2662, indexOK=indexOK@entry=true,
    snapshot=snapshot@entry=0x0, nkeys=nkeys@entry=1,
key=key@entry=0x7ffc1b7043b0) at
../src/backend/access/index/genam.c:400
#28 0x00000000009b89fe in ScanPgRelation (targetRelId=1259,
indexOK=indexOK@entry=true,
force_non_historic=force_non_historic@entry=false)
    at ../src/backend/utils/cache/relcache.c:397
#29 0x00000000009c060d in RelationReloadNailed
(relation=0x7f2f5efbeeb8) at
../src/backend/utils/cache/relcache.c:2540
#30 RelationRebuildRelation (relation=0x7f2f5efbeeb8) at
../src/backend/utils/cache/relcache.c:2738
#31 0x00000000009c0e76 in RelationIdGetRelation (relationId=<optimized
out>, relationId@entry=1259) at
../src/backend/utils/cache/relcache.c:2197
#32 0x00000000004c3e05 in relation_open (relationId=1259,
lockmode=lockmode@entry=1) at
../src/backend/access/common/relation.c:59
#33 0x0000000000561579 in table_open (relationId=<optimized out>,
lockmode=lockmode@entry=1) at ../src/backend/access/table/table.c:44
#34 0x00000000009add60 in SearchCatCacheMiss
(cache=cache@entry=0xaedd380, nkeys=nkeys@entry=1,
hashValue=628204292, hashIndex=4, v1=v1@entry=24576,
    v2=v2@entry=0, v3=0, v4=0) at ../src/backend/utils/cache/catcache.c:1577
#35 0x00000000009ae039 in SearchCatCacheInternal (cache=0xaedd380,
nkeys=1, v1=24576, v2=0, v3=0, v4=0) at
../src/backend/utils/cache/catcache.c:1522
#36 0x00000000009c4159 in SearchSysCacheExists
(cacheId=cacheId@entry=RELOID, key1=key1@entry=24576,
key2=key2@entry=0, key3=key3@entry=0,
    key4=key4@entry=0) at ../src/backend/utils/cache/syscache.c:434
#37 0x00000000005a9a43 in gtr_process_invalidated_gtrs () at
../src/backend/catalog/global_temp.c:706
#38 PreCommit_GlobalTempRelation () at ../src/backend/catalog/global_temp.c:1071
#39 0x0000000000577556 in CommitTransaction () at
../src/backend/access/transam/xact.c:2360

--
John Naylor
Amazon Web Services

Re: Global temporary tables

Dean Rasheed <dean.a.rasheed@gmail.com> — 2026-07-07T22:37:11Z

On Tue, 7 Jul 2026 at 10:06, John Naylor <johncnaylorls@gmail.com> wrote:
>
> Okay, I followed up with various attempts to get the new sub XID
> tracking to do something dire, and couldn't find anything.
>

Unfortunately, it still wasn't working properly. In a fresh session,
doing VACUUM FREEZE on a GTT with ON COMMIT DELETE ROWS would fail
because the VACUUM FREEZE would update a new pg_temp_class tuple,
flushing it to the database, and then the ON COMMIT code would fail to
find it, because there was no intervening CommandCounterIncrement() to
make it visible.

I don't want to go round adding CommandCounterIncrement()'s to fix
things like this -- part of the reason for having the pending inserts
was to make that unnecessary. So instead, in v8, I have made it so
that the pending insert entries are kept up-to-date all the way to the
end of the transaction, even if they're flushed to the database before
that. This turns out to make the code far simpler, and easier to
reason about, as well as hopefully being more robust. So I feel much
better about this formulation of pending inserts.

> I did find something else, however:
>
> set statement_timeout  = '3s';
> set debug_discard_caches = 1;
>
> create global temporary table g (a int);
>
> This enters an infinite loop
>

Doh! Fixed in v8.

Regards,
Dean