v8-0001-Save-temporary-table-ON-COMMIT-actions-to-pg_clas.patch

From 7bcdac5da6706f041c471d7d4e88e2743b937c98 Mon Sep 17 00:00:00 2001
From: Dean Rasheed <dean.a.rasheed@gmail.com>
Date: Fri, 26 Jun 2026 18:19:33 +0100
Subject: [PATCH v8 01/10] Save temporary table ON COMMIT actions to pg_class.

This adds a new column reloncommit to pg_class and uses it to save the
ON COMMIT actions of temporary tables. This is then used by psql's \d
meta-command to display the table's ON COMMIT action.

This will be required by global temporary tables in future commits.
---
 doc/src/sgml/catalogs.sgml         | 15 +++++++++++++
 src/backend/bootstrap/bootparse.y  |  1 +
 src/backend/catalog/heap.c         |  6 ++++-
 src/backend/catalog/index.c        |  1 +
 src/backend/utils/cache/relcache.c | 19 +++++++++++++++-
 src/bin/psql/describe.c            | 35 +++++++++++++++++++++++++++++-
 src/include/catalog/heap.h         |  1 +
 src/include/catalog/pg_class.h     |  8 +++++++
 src/include/utils/relcache.h       |  4 +++-
 src/test/regress/expected/temp.out | 18 +++++++++++++++
 src/test/regress/sql/temp.sql      |  6 +++++
 11 files changed, 110 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 4b474c13917..cd2be3ea3f2 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2171,6 +2171,21 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       </para></entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>reloncommit</structfield> <type>char</type>
+      </para>
+      <para>
+       The <literal>ON COMMIT</literal> behavior of the table:
+       <literal>p</literal> = preserve rows,
+       <literal>d</literal> = delete rows,
+       <literal>D</literal> = drop;
+       see <link linkend="sql-createtable"><command>CREATE TABLE</command></link>.
+       Preserve rows is the default; the other options are only supported for
+       temporary tables.
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>relnatts</structfield> <type>int2</type>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y
index 943ff4733d3..305a5654ff3 100644
--- a/src/backend/bootstrap/bootparse.y
+++ b/src/backend/bootstrap/bootparse.y
@@ -214,6 +214,7 @@ Boot_CreateStmt:
 												   RELPERSISTENCE_PERMANENT,
 												   shared_relation,
 												   mapped_relation,
+												   ONCOMMIT_NOOP,
 												   true,
 												   &relfrozenxid,
 												   &relminmxid,
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 88087654de9..684a44946fc 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -294,6 +294,7 @@ heap_create(const char *relname,
 			char relpersistence,
 			bool shared_relation,
 			bool mapped_relation,
+			OnCommitAction oncommit,
 			bool allow_system_table_mods,
 			TransactionId *relfrozenxid,
 			MultiXactId *relminmxid,
@@ -371,7 +372,8 @@ heap_create(const char *relname,
 									 shared_relation,
 									 mapped_relation,
 									 relpersistence,
-									 relkind);
+									 relkind,
+									 oncommit);
 
 	/*
 	 * Have the storage manager create the relation's disk file, if needed.
@@ -958,6 +960,7 @@ InsertPgClassTuple(Relation pg_class_desc,
 	values[Anum_pg_class_relisshared - 1] = BoolGetDatum(rd_rel->relisshared);
 	values[Anum_pg_class_relpersistence - 1] = CharGetDatum(rd_rel->relpersistence);
 	values[Anum_pg_class_relkind - 1] = CharGetDatum(rd_rel->relkind);
+	values[Anum_pg_class_reloncommit - 1] = CharGetDatum(rd_rel->reloncommit);
 	values[Anum_pg_class_relnatts - 1] = Int16GetDatum(rd_rel->relnatts);
 	values[Anum_pg_class_relchecks - 1] = Int16GetDatum(rd_rel->relchecks);
 	values[Anum_pg_class_relhasrules - 1] = BoolGetDatum(rd_rel->relhasrules);
@@ -1339,6 +1342,7 @@ heap_create_with_catalog(const char *relname,
 							   relpersistence,
 							   shared_relation,
 							   mapped_relation,
+							   oncommit,
 							   allow_system_table_mods,
 							   &relfrozenxid,
 							   &relminmxid,
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 9407c357f27..ffc7f6c254e 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -989,6 +989,7 @@ index_create(Relation heapRelation,
 								relpersistence,
 								shared_relation,
 								mapped_relation,
+								ONCOMMIT_NOOP,
 								allow_system_table_mods,
 								&relfrozenxid,
 								&relminmxid,
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index fb4e042be8a..1b20f1f82e2 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -1952,6 +1952,7 @@ formrdesc(const char *relationName, Oid relationReltype,
 	relation->rd_rel->relallvisible = 0;
 	relation->rd_rel->relallfrozen = 0;
 	relation->rd_rel->relkind = RELKIND_RELATION;
+	relation->rd_rel->reloncommit = RELONCOMMIT_PRESERVE_ROWS;
 	relation->rd_rel->relnatts = (int16) natts;
 
 	/*
@@ -3524,7 +3525,8 @@ RelationBuildLocalRelation(const char *relname,
 						   bool shared_relation,
 						   bool mapped_relation,
 						   char relpersistence,
-						   char relkind)
+						   char relkind,
+						   OnCommitAction oncommit)
 {
 	Relation	rel;
 	MemoryContext oldcxt;
@@ -3668,6 +3670,21 @@ RelationBuildLocalRelation(const char *relname,
 			break;
 	}
 
+	/* set up oncommit behavior */
+	switch (oncommit)
+	{
+		case ONCOMMIT_NOOP:
+		case ONCOMMIT_PRESERVE_ROWS:
+			rel->rd_rel->reloncommit = RELONCOMMIT_PRESERVE_ROWS;
+			break;
+		case ONCOMMIT_DELETE_ROWS:
+			rel->rd_rel->reloncommit = RELONCOMMIT_DELETE_ROWS;
+			break;
+		case ONCOMMIT_DROP:
+			rel->rd_rel->reloncommit = RELONCOMMIT_DROP;
+			break;
+	}
+
 	/* if it's a materialized view, it's not populated initially */
 	if (relkind == RELKIND_MATVIEW)
 		rel->rd_rel->relispopulated = false;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index fef86b4cca3..e710db8e102 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1580,6 +1580,7 @@ describeOneTableDetails(const char *schemaname,
 		char		relpersistence;
 		char		relreplident;
 		char	   *relam;
+		char		reloncommit;
 	}			tableinfo;
 	bool		show_column_details = false;
 
@@ -1594,7 +1595,25 @@ describeOneTableDetails(const char *schemaname,
 	/* Get general table info */
 	printfPQExpBuffer(&buf, "/* %s */\n",
 					  _("Get general information about one relation"));
-	if (pset.sversion >= 120000)
+	if (pset.sversion >= 200000)
+	{
+		appendPQExpBuffer(&buf,
+						  "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
+						  "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
+						  "false AS relhasoids, c.relispartition, %s, c.reltablespace, "
+						  "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
+						  "c.relpersistence, c.relreplident, am.amname, c.reloncommit\n"
+						  "FROM pg_catalog.pg_class c\n "
+						  "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
+						  "LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)\n"
+						  "WHERE c.oid = '%s';",
+						  (verbose ?
+						   "pg_catalog.array_to_string(c.reloptions || "
+						   "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
+						   : "''"),
+						  oid);
+	}
+	else if (pset.sversion >= 120000)
 	{
 		appendPQExpBuffer(&buf,
 						  "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
@@ -1662,6 +1681,10 @@ describeOneTableDetails(const char *schemaname,
 			NULL : pg_strdup(PQgetvalue(res, 0, 14));
 	else
 		tableinfo.relam = NULL;
+	if (pset.sversion >= 200000)
+		tableinfo.reloncommit = *(PQgetvalue(res, 0, 15));
+	else
+		tableinfo.reloncommit = RELONCOMMIT_PRESERVE_ROWS;
 	PQclear(res);
 	res = NULL;
 
@@ -3670,6 +3693,16 @@ describeOneTableDetails(const char *schemaname,
 			printfPQExpBuffer(&buf, _("Access method: %s"), tableinfo.relam);
 			printTableAddFooter(&cont, buf.data);
 		}
+
+		/* On-commit action */
+		if (verbose && tableinfo.reloncommit != RELONCOMMIT_PRESERVE_ROWS)
+		{
+			printfPQExpBuffer(&buf, _("On-commit action: %s"),
+							  tableinfo.reloncommit == RELONCOMMIT_DELETE_ROWS ? "ON COMMIT DELETE ROWS" :
+							  tableinfo.reloncommit == RELONCOMMIT_DROP ? "ON COMMIT DROP" :
+							  "???");
+			printTableAddFooter(&cont, buf.data);
+		}
 	}
 
 	/* reloptions, if verbose */
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index 6c9ac812aa0..a622d703e97 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -59,6 +59,7 @@ extern Relation heap_create(const char *relname,
 							char relpersistence,
 							bool shared_relation,
 							bool mapped_relation,
+							OnCommitAction oncommit,
 							bool allow_system_table_mods,
 							TransactionId *relfrozenxid,
 							MultiXactId *relminmxid,
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index c4af599dc90..0e2c53591c4 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -88,6 +88,9 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,Relat
 	/* see RELKIND_xxx constants below */
 	char		relkind BKI_DEFAULT(r);
 
+	/* see RELONCOMMIT_xxx constants below */
+	char		reloncommit BKI_DEFAULT(p);
+
 	/* number of user attributes */
 	int16		relnatts BKI_DEFAULT(0);	/* genbki.pl will fill this in */
 
@@ -184,6 +187,11 @@ MAKE_SYSCACHE(RELNAMENSP, pg_class_relname_nsp_index, 128);
 #define		  RELPERSISTENCE_UNLOGGED	'u' /* unlogged permanent table */
 #define		  RELPERSISTENCE_TEMP		't' /* temporary table */
 
+/* on-commit action (only temporary tables support delete/drop) */
+#define		  RELONCOMMIT_PRESERVE_ROWS 'p' /* default: keep data on commit */
+#define		  RELONCOMMIT_DELETE_ROWS	'd' /* delete table rows on commit */
+#define		  RELONCOMMIT_DROP			'D' /* drop table on commit */
+
 /* default selection for replica identity (primary key or nothing) */
 #define		  REPLICA_IDENTITY_DEFAULT	'd'
 /* no replica identity is logged for this relation */
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 89c27aa1529..ed09a699e8b 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -17,6 +17,7 @@
 #include "access/tupdesc.h"
 #include "common/relpath.h"
 #include "nodes/bitmapset.h"
+#include "nodes/primnodes.h"
 
 
 /*
@@ -121,7 +122,8 @@ extern Relation RelationBuildLocalRelation(const char *relname,
 										   bool shared_relation,
 										   bool mapped_relation,
 										   char relpersistence,
-										   char relkind);
+										   char relkind,
+										   OnCommitAction oncommit);
 
 /*
  * Routines to manage assignment of new relfilenumber to a relation
diff --git a/src/test/regress/expected/temp.out b/src/test/regress/expected/temp.out
index a50c7ae88a9..d3542a60242 100644
--- a/src/test/regress/expected/temp.out
+++ b/src/test/regress/expected/temp.out
@@ -42,6 +42,12 @@ SELECT * FROM temptest;
 DROP TABLE temptest;
 -- test temp table deletion
 CREATE TEMP TABLE temptest(col int);
+SELECT reloncommit FROM pg_class WHERE oid = 'temptest'::regclass;
+ reloncommit 
+-------------
+ p
+(1 row)
+
 \c
 SELECT * FROM temptest;
 ERROR:  relation "temptest" does not exist
@@ -49,6 +55,12 @@ LINE 1: SELECT * FROM temptest;
                       ^
 -- Test ON COMMIT DELETE ROWS
 CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS;
+SELECT reloncommit FROM pg_class WHERE oid = 'temptest'::regclass;
+ reloncommit 
+-------------
+ d
+(1 row)
+
 -- while we're here, verify successful truncation of index with SQL function
 CREATE INDEX ON temptest(bit_length(''));
 BEGIN;
@@ -86,6 +98,12 @@ DROP TABLE temptest;
 -- Test ON COMMIT DROP
 BEGIN;
 CREATE TEMP TABLE temptest(col int) ON COMMIT DROP;
+SELECT reloncommit FROM pg_class WHERE oid = 'temptest'::regclass;
+ reloncommit 
+-------------
+ D
+(1 row)
+
 INSERT INTO temptest VALUES (1);
 INSERT INTO temptest VALUES (2);
 SELECT * FROM temptest;
diff --git a/src/test/regress/sql/temp.sql b/src/test/regress/sql/temp.sql
index d50472ddced..01a5399bc96 100644
--- a/src/test/regress/sql/temp.sql
+++ b/src/test/regress/sql/temp.sql
@@ -47,6 +47,8 @@ DROP TABLE temptest;
 
 CREATE TEMP TABLE temptest(col int);
 
+SELECT reloncommit FROM pg_class WHERE oid = 'temptest'::regclass;
+
 \c
 
 SELECT * FROM temptest;
@@ -55,6 +57,8 @@ SELECT * FROM temptest;
 
 CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS;
 
+SELECT reloncommit FROM pg_class WHERE oid = 'temptest'::regclass;
+
 -- while we're here, verify successful truncation of index with SQL function
 CREATE INDEX ON temptest(bit_length(''));
 
@@ -85,6 +89,8 @@ BEGIN;
 
 CREATE TEMP TABLE temptest(col int) ON COMMIT DROP;
 
+SELECT reloncommit FROM pg_class WHERE oid = 'temptest'::regclass;
+
 INSERT INTO temptest VALUES (1);
 INSERT INTO temptest VALUES (2);
 
-- 
2.51.0