Re: Global temporary tables

On Fri, Jun 26, 2026 at 12:37 PM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> On Fri, 26 Jun 2026 at 04:52, Kirk Wolak <wolakk(at)gmail(dot)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