0010-Global-temporary-tables-pg_dump-psql-and-replication.patch

From 823eaee770c9a394a6d3f53e98040a67e73b9461 Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <andrew@dunslane.net>
Date: Fri, 13 Mar 2026 13:23:00 -0400
Subject: [PATCH 10/12] Global temporary tables: pg_dump, psql, and replication
 support

pg_dump:
- Never dump data for GTTs (data is per-session and ephemeral).
- Emit CREATE GLOBAL TEMPORARY TABLE with proper ON COMMIT clause
  (DELETE ROWS or PRESERVE ROWS) instead of leaking the internal
  on_commit_delete reloption into the WITH(...) clause.
- Extract on_commit_delete from reloptions in the catalog query,
  following the same array_remove pattern used for check_option.
- Emit CREATE GLOBAL TEMPORARY SEQUENCE for sequences with
  RELPERSISTENCE_GLOBAL_TEMP, mirroring the UNLOGGED prefix path.

psql:
- Show "Global temporary table" and "Global temporary index" labels
  in \d output, matching the existing "Unlogged table" pattern.
- The \dt+ verbose listing already showed "global temporary" in the
  Persistence column.

Replication:
- FOR ALL TABLES publications already exclude GTTs because
  is_publishable_class() requires RELPERSISTENCE_PERMANENT.
- Explicit CREATE PUBLICATION FOR TABLE and ALTER PUBLICATION ADD
  TABLE now reject GTTs in check_publication_add_relation, matching
  the existing treatment of TEMP and UNLOGGED relations.
- Physical replication only sees the catalog definition since GTT
  data uses local buffers and generates no WAL.

- pg_dump skips relation/attribute statistics for GTTs (their
  statistics are per-session; the shared pg_class/pg_statistic rows
  are never populated, and pg_restore_relation_stats() refuses to
  write shared statistics for them on restore).
- pg_upgrade excludes GTTs from the file transfer map: they have no
  files at their catalog locators, and their definitions travel with
  the pg_dump schema restore.  Per-session storage is recreated
  lazily in the new cluster.
---
 src/backend/catalog/pg_publication.c | 10 ++++-
 src/bin/pg_dump/pg_dump.c            | 67 +++++++++++++++++++++++-----
 src/bin/pg_dump/pg_dump.h            |  1 +
 src/bin/pg_upgrade/info.c            |  2 +
 src/bin/psql/describe.c              |  6 +++
 src/bin/psql/tab-complete.in.c       | 28 +++++++++---
 6 files changed, 97 insertions(+), 17 deletions(-)

diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 5c457d9aca8..9717267273a 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -92,7 +92,10 @@ check_publication_add_relation(PublicationRelInfo *pri)
 				 errmsg(errormsg, relname),
 				 errdetail("This operation is not supported for system tables.")));
 
-	/* UNLOGGED and TEMP relations cannot be part of publication. */
+	/*
+	 * UNLOGGED, TEMP, and GLOBAL TEMP relations cannot be part of
+	 * publication.
+	 */
 	if (targetrel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -103,6 +106,11 @@ check_publication_add_relation(PublicationRelInfo *pri)
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg(errormsg, relname),
 				 errdetail("This operation is not supported for unlogged tables.")));
+	else if (RelationIsGlobalTemp(targetrel))
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg(errormsg, RelationGetRelationName(targetrel)),
+				errdetail("This operation is not supported for global temporary tables."));
 }
 
 /*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 0c42d81a0be..d97352e1306 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3050,6 +3050,10 @@ makeTableDataInfo(DumpOptions *dopt, TableInfo *tbinfo)
 	if (tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
 		return;
 
+	/* Never dump data for global temporary tables (data is per-session) */
+	if (tbinfo->relpersistence == RELPERSISTENCE_GLOBAL_TEMP)
+		return;
+
 	/* Don't dump data in unlogged tables, if so requested */
 	if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED &&
 		dopt->no_unlogged_table_data)
@@ -7315,6 +7319,7 @@ getTables(Archive *fout, int *numTables)
 	int			i_toastminmxid;
 	int			i_reloptions;
 	int			i_checkoption;
+	int			i_gtt_on_commit_delete;
 	int			i_toastreloptions;
 	int			i_reloftype;
 	int			i_foreignserver;
@@ -7409,12 +7414,24 @@ getTables(Archive *fout, int *numTables)
 
 	if (fout->remoteVersion >= 90300)
 		appendPQExpBufferStr(query,
-							 "array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, "
+
+		/*
+		 * Filter out reloptions that are internal to the server and shouldn't
+		 * be emitted as user-visible WITH(...) items.  Keyed by prefix so
+		 * that a future change to how these options are serialised (different
+		 * value spellings, added keys) doesn't silently break pg_dump.
+		 */
+							 "ARRAY(SELECT r FROM unnest(c.reloptions) AS r "
+							 "WHERE r NOT LIKE 'check_option=%' "
+							 "  AND r NOT LIKE 'on_commit_delete=%') "
+							 "AS reloptions, "
 							 "CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text "
-							 "WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, ");
+							 "WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, "
+							 "('on_commit_delete=true' = ANY (c.reloptions)) AS gtt_on_commit_delete, ");
 	else
 		appendPQExpBufferStr(query,
-							 "c.reloptions, NULL AS checkoption, ");
+							 "c.reloptions, NULL AS checkoption, "
+							 "false AS gtt_on_commit_delete, ");
 
 	if (fout->remoteVersion >= 90600)
 		appendPQExpBufferStr(query,
@@ -7540,6 +7557,7 @@ getTables(Archive *fout, int *numTables)
 	i_toastminmxid = PQfnumber(res, "tminmxid");
 	i_reloptions = PQfnumber(res, "reloptions");
 	i_checkoption = PQfnumber(res, "checkoption");
+	i_gtt_on_commit_delete = PQfnumber(res, "gtt_on_commit_delete");
 	i_toastreloptions = PQfnumber(res, "toast_reloptions");
 	i_reloftype = PQfnumber(res, "reloftype");
 	i_foreignserver = PQfnumber(res, "foreignserver");
@@ -7621,6 +7639,8 @@ getTables(Archive *fout, int *numTables)
 			tblinfo[i].checkoption = NULL;
 		else
 			tblinfo[i].checkoption = pg_strdup(PQgetvalue(res, i, i_checkoption));
+		tblinfo[i].gtt_on_commit_delete =
+			(strcmp(PQgetvalue(res, i, i_gtt_on_commit_delete), "t") == 0);
 		tblinfo[i].toast_reloptions = pg_strdup(PQgetvalue(res, i, i_toastreloptions));
 		tblinfo[i].reloftype = atooid(PQgetvalue(res, i, i_reloftype));
 		tblinfo[i].foreign_server = atooid(PQgetvalue(res, i, i_foreignserver));
@@ -7668,8 +7688,14 @@ getTables(Archive *fout, int *numTables)
 			tblinfo[i].dobj.components |= DUMP_COMPONENT_ACL;
 		tblinfo[i].hascolumnACLs = false;	/* may get set later */
 
-		/* Add statistics */
-		if (tblinfo[i].interesting)
+		/*
+		 * Add statistics.  Global temporary tables are skipped: their
+		 * statistics are per-session (the shared pg_class/pg_statistic rows
+		 * are never populated), and pg_restore_relation_stats() refuses to
+		 * write shared statistics for them on restore.
+		 */
+		if (tblinfo[i].interesting &&
+			tblinfo[i].relpersistence != RELPERSISTENCE_GLOBAL_TEMP)
 		{
 			RelStatsInfo *stats;
 
@@ -8240,10 +8266,14 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 					pg_fatal("could not parse %s array", "indattnames");
 			}
 
-			relstats = getRelationStatistics(fout, &indxinfo[j].dobj, relpages,
-											 PQgetvalue(res, j, i_reltuples),
-											 relallvisible, relallfrozen, indexkind,
-											 indAttNames, nindAttNames);
+			/* as in getTables, no statistics for global temp tables */
+			if (tbinfo->relpersistence != RELPERSISTENCE_GLOBAL_TEMP)
+				relstats = getRelationStatistics(fout, &indxinfo[j].dobj, relpages,
+												 PQgetvalue(res, j, i_reltuples),
+												 relallvisible, relallfrozen, indexkind,
+												 indAttNames, nindAttNames);
+			else
+				relstats = NULL;
 
 			contype = *(PQgetvalue(res, j, i_contype));
 			if (contype == 'p' || contype == 'u' || contype == 'x')
@@ -17712,6 +17742,15 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 		if (ftoptions && ftoptions[0])
 			appendPQExpBuffer(q, "\nOPTIONS (\n    %s\n)", ftoptions);
 
+		/* Emit ON COMMIT clause for global temporary tables */
+		if (tbinfo->relpersistence == RELPERSISTENCE_GLOBAL_TEMP)
+		{
+			if (tbinfo->gtt_on_commit_delete)
+				appendPQExpBufferStr(q, "\nON COMMIT DELETE ROWS");
+			else
+				appendPQExpBufferStr(q, "\nON COMMIT PRESERVE ROWS");
+		}
+
 		/*
 		 * For materialized views, create the AS clause just like a view. At
 		 * this point, we always mark the view as not populated.
@@ -19606,10 +19645,16 @@ dumpSequence(Archive *fout, const TableInfo *tbinfo)
 	}
 	else
 	{
+		const char *persistence_prefix = "";
+
+		if (tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED)
+			persistence_prefix = "UNLOGGED ";
+		else if (tbinfo->relpersistence == RELPERSISTENCE_GLOBAL_TEMP)
+			persistence_prefix = "GLOBAL TEMPORARY ";
+
 		appendPQExpBuffer(query,
 						  "CREATE %sSEQUENCE %s\n",
-						  tbinfo->relpersistence == RELPERSISTENCE_UNLOGGED ?
-						  "UNLOGGED " : "",
+						  persistence_prefix,
 						  fmtQualifiedDumpable(tbinfo));
 
 		if (seq->seqtype != SEQTYPE_BIGINT)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 5a6726d8b12..258a461c05f 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -315,6 +315,7 @@ typedef struct _tableInfo
 	char	   *reloptions;		/* options specified by WITH (...) */
 	char	   *checkoption;	/* WITH CHECK OPTION, if any */
 	char	   *toast_reloptions;	/* WITH options for the TOAST table */
+	bool		gtt_on_commit_delete;	/* GTT: ON COMMIT DELETE ROWS? */
 	bool		hasindex;		/* does it have any indexes? */
 	bool		hasrules;		/* does it have any rules? */
 	bool		hastriggers;	/* does it have any triggers? */
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index 37fff93892f..fa2bb736cee 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -503,6 +503,8 @@ get_rel_infos_query(void)
 					  "         ON c.relnamespace = n.oid "
 					  "  WHERE relkind IN (" CppAsString2(RELKIND_RELATION) ", "
 					  CppAsString2(RELKIND_MATVIEW) "%s) AND "
+	/* global temp tables have no persistent storage to transfer */
+					  "    c.relpersistence != " CppAsString2(RELPERSISTENCE_GLOBAL_TEMP) " AND "
 	/* exclude possible orphaned temp tables */
 					  "    ((n.nspname !~ '^pg_temp_' AND "
 					  "      n.nspname !~ '^pg_toast_temp_' AND "
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index a33b6a0bcab..a5b3b021d56 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2138,6 +2138,9 @@ describeOneTableDetails(const char *schemaname,
 			if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
 				printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
 								  schemaname, relationname);
+			else if (tableinfo.relpersistence == RELPERSISTENCE_GLOBAL_TEMP)
+				printfPQExpBuffer(&title, _("Global temporary table \"%s.%s\""),
+								  schemaname, relationname);
 			else
 				printfPQExpBuffer(&title, _("Table \"%s.%s\""),
 								  schemaname, relationname);
@@ -2154,6 +2157,9 @@ describeOneTableDetails(const char *schemaname,
 			if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
 				printfPQExpBuffer(&title, _("Unlogged index \"%s.%s\""),
 								  schemaname, relationname);
+			else if (tableinfo.relpersistence == RELPERSISTENCE_GLOBAL_TEMP)
+				printfPQExpBuffer(&title, _("Global temporary index \"%s.%s\""),
+								  schemaname, relationname);
 			else
 				printfPQExpBuffer(&title, _("Index \"%s.%s\""),
 								  schemaname, relationname);
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index de547a8cb37..4db222bfeb2 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1332,6 +1332,7 @@ static const pgsql_thing_t words_after_create[] = {
 	{"EXTENSION", Query_for_list_of_extensions},
 	{"FOREIGN DATA WRAPPER", NULL, NULL, NULL},
 	{"FOREIGN TABLE", NULL, NULL, NULL},
+	{"GLOBAL", NULL, NULL, NULL, NULL, THING_NO_DROP | THING_NO_ALTER},	/* for CREATE GLOBAL TEMP TABLE ... */
 	{"FUNCTION", NULL, NULL, Query_for_list_of_functions},
 	{"GROUP", Query_for_list_of_roles},
 	{"INDEX", NULL, NULL, &Query_for_list_of_indexes},
@@ -3861,6 +3862,12 @@ match_previous_words(int pattern_id,
 	/* Complete "CREATE TEMP/TEMPORARY" with the possible temp objects */
 	else if (TailMatches("CREATE", "TEMP|TEMPORARY"))
 		COMPLETE_WITH("SEQUENCE", "TABLE", "VIEW");
+	/* Complete "CREATE GLOBAL" with TEMP or TEMPORARY */
+	else if (TailMatches("CREATE", "GLOBAL"))
+		COMPLETE_WITH("TEMP", "TEMPORARY");
+	/* Complete "CREATE GLOBAL TEMP/TEMPORARY" with TABLE */
+	else if (TailMatches("CREATE", "GLOBAL", "TEMP|TEMPORARY"))
+		COMPLETE_WITH("TABLE");
 	/* Complete "CREATE UNLOGGED" with TABLE or SEQUENCE */
 	else if (TailMatches("CREATE", "UNLOGGED"))
 		COMPLETE_WITH("TABLE", "SEQUENCE");
@@ -3875,17 +3882,21 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH("FOR VALUES", "DEFAULT");
 	/* Complete CREATE TABLE <name> with '(', AS, OF or PARTITION OF */
 	else if (TailMatches("CREATE", "TABLE", MatchAny) ||
-			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny))
+			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny) ||
+			 TailMatches("CREATE", "GLOBAL", "TEMP|TEMPORARY", "TABLE", MatchAny))
 		COMPLETE_WITH("(", "AS", "OF", "PARTITION OF");
 	/* Complete CREATE TABLE <name> OF with list of composite types */
 	else if (TailMatches("CREATE", "TABLE", MatchAny, "OF") ||
-			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "OF"))
+			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "OF") ||
+			 TailMatches("CREATE", "GLOBAL", "TEMP|TEMPORARY", "TABLE", MatchAny, "OF"))
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_composite_datatypes);
 	/* Complete CREATE TABLE <name> [ (...) ] AS with list of keywords */
 	else if (TailMatches("CREATE", "TABLE", MatchAny, "AS") ||
 			 TailMatches("CREATE", "TABLE", MatchAny, "(*)", "AS") ||
 			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "AS") ||
-			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "(*)", "AS"))
+			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "(*)", "AS") ||
+			 TailMatches("CREATE", "GLOBAL", "TEMP|TEMPORARY", "TABLE", MatchAny, "AS") ||
+			 TailMatches("CREATE", "GLOBAL", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)", "AS"))
 		COMPLETE_WITH("EXECUTE", "SELECT", "TABLE", "VALUES", "WITH");
 	/* Complete CREATE TABLE name (...) with supported options */
 	else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)"))
@@ -3895,17 +3906,24 @@ match_previous_words(int pattern_id,
 	else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
 		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY", "USING",
 					  "TABLESPACE", "WITH (");
+	else if (TailMatches("CREATE", "GLOBAL", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)"))
+		COMPLETE_WITH("AS", "INHERITS (", "ON COMMIT", "PARTITION BY", "USING",
+					  "TABLESPACE", "WITH (");
 	/* Complete CREATE TABLE (...) USING with table access methods */
 	else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)", "USING") ||
-			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "(*)", "USING"))
+			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "(*)", "USING") ||
+			 TailMatches("CREATE", "GLOBAL", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)", "USING"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
 	/* Complete CREATE TABLE (...) WITH with storage parameters */
 	else if (TailMatches("CREATE", "TABLE", MatchAny, "(*)", "WITH", "(") ||
-			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "(*)", "WITH", "("))
+			 TailMatches("CREATE", "TEMP|TEMPORARY|UNLOGGED", "TABLE", MatchAny, "(*)", "WITH", "(") ||
+			 TailMatches("CREATE", "GLOBAL", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)", "WITH", "("))
 		COMPLETE_WITH_LIST(table_storage_parameters);
 	/* Complete CREATE TABLE ON COMMIT with actions */
 	else if (TailMatches("CREATE", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)", "ON", "COMMIT"))
 		COMPLETE_WITH("DELETE ROWS", "DROP", "PRESERVE ROWS");
+	else if (TailMatches("CREATE", "GLOBAL", "TEMP|TEMPORARY", "TABLE", MatchAny, "(*)", "ON", "COMMIT"))
+		COMPLETE_WITH("DELETE ROWS", "DROP", "PRESERVE ROWS");
 
 /* CREATE TABLESPACE */
 	else if (Matches("CREATE", "TABLESPACE", MatchAny))
-- 
2.43.0