v7-0002-Adds-pg_get_tablespace_ddl-function.patch
text/x-patch
Filename: v7-0002-Adds-pg_get_tablespace_ddl-function.patch
Type: text/x-patch
Part: 0
From 84dabefcda2a5d7026280bdbaa91b3b44dd8784e Mon Sep 17 00:00:00 2001
From: Manni Wood <manni.wood@enterprisedb.com>
Date: Thu, 6 Nov 2025 19:19:50 -0600
Subject: [PATCH v7 2/2] Adds pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Currently, there exist ways of "wholesale" dumping the DDL for an entire
database or even cluster; this function will ideally be part of a suite
of similar "retail" functions for dumping the DDL of various database
objects.
Authors: Manni Wood <manni.wood@enterprisedb.com> and Nishant Sharma
<nishant.sharma@enterprisedb.com>
Reviewers: Vaibhav Dalvi, Ian Barwick, Jim Jones, Álvaro Herrera
Discussion:
https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com
---
doc/src/sgml/func/func-info.sgml | 45 +++++++
src/backend/utils/adt/ruleutils.c | 151 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/include/utils/ruleutils.h | 1 +
src/test/regress/expected/tablespace.out | 69 +++++++++++
src/test/regress/sql/tablespace.sql | 47 +++++++
6 files changed, 319 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..74ca0ed1066 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,49 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-get-object-ddl">
+ <title>Get Object DDL Functions</title>
+
+ <para>
+ The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+ print the DDL statements for various database objects.
+ (This is a decompiled reconstruction, not the original text
+ of the command.)
+ </para>
+
+ <table id="functions-get-object-ddl-table">
+ <title>Get Object DDL Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_tablespace_ddl</primary>
+ </indexterm>
+ <function>pg_get_tablespace_ddl</function>
+ ( <parameter>tablespace</parameter> <type>name</type> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the creating command for a tablespace.
+ The result is a complete <command>CREATE TABLESPACE</command> statement.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index b15d85c5477..6d1aa3b20ab 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -36,6 +36,7 @@
#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_tablespace.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
@@ -13822,3 +13823,153 @@ get_tablespace_loc_string(Oid tablespaceOid)
return buf.data;
}
+
+
+/*
+ * build_tablespace_ddl_string - Build CREATE TABLESPACE statement for
+ * a tablespace from its OID. This is internal version which helps
+ * pg_get_tablespace_ddl_name() and pg_get_tablespace_ddl_oid().
+ */
+char *
+build_tablespace_ddl_string(const Oid tspaceoid)
+{
+ char *path;
+ char *spcowner;
+ bool isNull;
+ Oid tspowneroid;
+ Datum datum;
+ HeapTuple tuple;
+ StringInfoData buf;
+ Form_pg_tablespace tspForm;
+
+ /* Look up the tablespace in pg_tablespace */
+ tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tspaceoid));
+
+ /* Confirm if tablespace OID was valid */
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("tablespace with oid %d does not exist",
+ tspaceoid)));
+
+ /* Get tablespace's details from its tuple */
+ tspForm = (Form_pg_tablespace) GETSTRUCT(tuple);
+
+ initStringInfo(&buf);
+
+ /* Start building the CREATE TABLESPACE statement */
+ appendStringInfo(&buf, "CREATE TABLESPACE %s",
+ quote_identifier(NameStr(tspForm->spcname)));
+
+ /* Get the OID of the owner of the tablespace name */
+ tspowneroid = tspForm->spcowner;
+
+ /* Add OWNER clause, if the owner is not the current user */
+ if (GetUserId() != tspowneroid)
+ {
+ /* Get the owner name */
+ spcowner = GetUserNameFromId(tspowneroid, false);
+
+ appendStringInfo(&buf, " OWNER %s",
+ quote_identifier(spcowner));
+ }
+
+ /* Find tablespace directory path */
+ path = get_tablespace_loc_string(tspaceoid);
+
+ /* Add directory LOCATION (path), if it exists */
+ if (path[0] != '\0')
+ {
+ /*
+ * Special case: if the tablespace was created with GUC
+ * "allow_in_place_tablespaces = true" and "LOCATION ''", path will
+ * begin with "pg_tblspc/". In that case, show "LOCATION ''" as the
+ * user originally specified.
+ */
+ if (strncmp(PG_TBLSPC_DIR_SLASH, path, strlen(PG_TBLSPC_DIR_SLASH)) == 0)
+ appendStringInfoString(&buf, " LOCATION ''");
+ else
+ appendStringInfo(&buf, " LOCATION '%s'", path);
+ }
+
+ /* Get tablespace's options datum from the tuple */
+ datum = SysCacheGetAttr(TABLESPACEOID,
+ tuple,
+ Anum_pg_tablespace_spcoptions,
+ &isNull);
+
+ if (!isNull)
+ {
+ ArrayType *optarray;
+ Datum *optdatums;
+ int optcount;
+ int i;
+
+ optarray = DatumGetArrayTypeP(datum);
+
+ deconstruct_array_builtin(optarray, TEXTOID,
+ &optdatums, NULL, &optcount);
+
+ Assert(optcount);
+
+ /* Start WITH clause */
+ appendStringInfoString(&buf, " WITH (");
+
+ for (i = 0; i < (optcount - 1); i++) /* Skipping last option */
+ {
+ /* Add the options in WITH clause */
+ appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+ appendStringInfoString(&buf, ", ");
+ }
+
+ /* Adding the last remaining option */
+ appendStringInfoString(&buf, TextDatumGetCString(optdatums[i]));
+ /* Closing WITH clause */
+ appendStringInfoChar(&buf, ')');
+ /* Cleanup the datums found */
+ pfree(optdatums);
+ }
+
+ ReleaseSysCache(tuple);
+
+ /* Finally add semicolon to the statement */
+ appendStringInfoChar(&buf, ';');
+
+ return buf.data;
+}
+
+/*
+ * pg_get_tablespace_ddl_name - Get CREATE TABLESPACE statement for a
+ * tablespace. This takes name as parameter for pg_get_tablespace_ddl().
+ */
+Datum
+pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
+{
+ Name tspname = PG_GETARG_NAME(0);
+ Oid tspaceoid;
+ char *ddl_stmt;
+
+ /* Get the OID of the tablespace from its name */
+ tspaceoid = get_tablespace_oid(NameStr(*tspname), false);
+
+ /* Get the CREATE TABLESPACE DDL statement from its OID */
+ ddl_stmt = build_tablespace_ddl_string(tspaceoid);
+
+ PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * pg_get_tablespace_ddl_oid - Get CREATE TABLESPACE statement for a
+ * tablespace. This takes oid as parameter for pg_get_tablespace_ddl().
+ */
+Datum
+pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS)
+{
+ Oid tspaceoid = PG_GETARG_OID(0);
+ char *ddl_stmt;
+
+ /* Get the CREATE TABLESPACE DDL statement from its OID */
+ ddl_stmt = build_tablespace_ddl_string(tspaceoid);
+
+ PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 34b7fddb0e7..ece0610b558 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8515,6 +8515,12 @@
{ oid => '2508', descr => 'constraint description with pretty-print option',
proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
proargtypes => 'oid bool', prosrc => 'pg_get_constraintdef_ext' },
+{ oid => '8758', descr => 'get CREATE statement for tablespace',
+ proname => 'pg_get_tablespace_ddl', prorettype => 'text',
+ proargtypes => 'name', prosrc => 'pg_get_tablespace_ddl_name' },
+{ oid => '8759', descr => 'get CREATE statement for tablespace',
+ proname => 'pg_get_tablespace_ddl', prorettype => 'text',
+ proargtypes => 'oid', prosrc => 'pg_get_tablespace_ddl_oid' },
{ oid => '2509',
descr => 'deparse an encoded expression with pretty-print option',
proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 6d74485f32a..e709eafccfd 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -55,5 +55,6 @@ extern char *get_range_partbound_string(List *bound_datums);
extern char *pg_get_statisticsobjdef_string(Oid statextid);
extern char *get_tablespace_loc_string(Oid tablespaceOid);
+extern char *build_tablespace_ddl_string(const Oid tspaceoid);
#endif /* RULEUTILS_H */
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..6b77e3323d1 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -971,3 +971,72 @@ drop cascades to materialized view testschema.amv
drop cascades to table testschema.tablespace_acl
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+-- Test pg_get_tablespace_ddl() by creating tablespaces with various
+-- configurations and checking the DDL.
+SET allow_in_place_tablespaces = true;
+-- create a tablespace using no options
+CREATE TABLESPACE regress_noopt_tblsp LOCATION ''; -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_noopt_tblsp');
+ pg_get_tablespace_ddl
+----------------------------------------------------
+ CREATE TABLESPACE regress_noopt_tblsp LOCATION '';
+(1 row)
+
+DROP TABLESPACE regress_noopt_tblsp;
+-- create a tablespace with a space in the name
+CREATE TABLESPACE "regress_ tblsp" LOCATION ''; -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_ tblsp');
+ pg_get_tablespace_ddl
+-------------------------------------------------
+ CREATE TABLESPACE "regress_ tblsp" LOCATION '';
+(1 row)
+
+DROP TABLESPACE "regress_ tblsp";
+-- create a tablespace using one option
+CREATE TABLESPACE regress_oneopt_tblsp LOCATION '' WITH (random_page_cost = 3.0); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_oneopt_tblsp');
+ pg_get_tablespace_ddl
+---------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_oneopt_tblsp LOCATION '' WITH (random_page_cost=3.0);
+(1 row)
+
+DROP TABLESPACE regress_oneopt_tblsp;
+-- create tablespace owned by a particular user
+CREATE USER regress_user;
+CREATE TABLESPACE regress_owner_tblsp OWNER regress_user LOCATION '' WITH (random_page_cost = 3.0); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_owner_tblsp');
+ pg_get_tablespace_ddl
+---------------------------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_owner_tblsp OWNER regress_user LOCATION '' WITH (random_page_cost=3.0);
+(1 row)
+
+DROP TABLESPACE regress_owner_tblsp;
+DROP USER regress_user;
+-- create a tablespace using all the options
+CREATE TABLESPACE regress_allopt_tblsp LOCATION '' WITH (seq_page_cost = 1.5, random_page_cost = 1.1234567890, effective_io_concurrency = 17, maintenance_io_concurrency = 18); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_allopt_tblsp');
+ pg_get_tablespace_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TABLESPACE regress_allopt_tblsp LOCATION '' WITH (seq_page_cost=1.5, random_page_cost=1.1234567890, effective_io_concurrency=17, maintenance_io_concurrency=18);
+(1 row)
+
+DROP TABLESPACE regress_allopt_tblsp;
+-- see the pg_get_tablespace_ddl error for a dropped 'regress_allopt_tblsp'
+-- tablespace name
+SELECT pg_get_tablespace_ddl('regress_allopt_tblsp');
+ERROR: tablespace "regress_allopt_tblsp" does not exist
+-- see the pg_get_tablespace_ddl error for an empty input
+SELECT pg_get_tablespace_ddl('');
+ERROR: tablespace "" does not exist
+-- see the pg_get_tablespace_ddl output for a NULL input
+SELECT pg_get_tablespace_ddl(NULL);
+ pg_get_tablespace_ddl
+-----------------------
+
+(1 row)
+
diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tablespace.sql
index dfe3db096e2..6a49100ca14 100644
--- a/src/test/regress/sql/tablespace.sql
+++ b/src/test/regress/sql/tablespace.sql
@@ -437,3 +437,50 @@ DROP SCHEMA testschema CASCADE;
DROP ROLE regress_tablespace_user1;
DROP ROLE regress_tablespace_user2;
+
+-- Test pg_get_tablespace_ddl() by creating tablespaces with various
+-- configurations and checking the DDL.
+
+SET allow_in_place_tablespaces = true;
+
+-- create a tablespace using no options
+CREATE TABLESPACE regress_noopt_tblsp LOCATION ''; -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_noopt_tblsp');
+DROP TABLESPACE regress_noopt_tblsp;
+
+-- create a tablespace with a space in the name
+CREATE TABLESPACE "regress_ tblsp" LOCATION ''; -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_ tblsp');
+DROP TABLESPACE "regress_ tblsp";
+
+-- create a tablespace using one option
+CREATE TABLESPACE regress_oneopt_tblsp LOCATION '' WITH (random_page_cost = 3.0); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_oneopt_tblsp');
+DROP TABLESPACE regress_oneopt_tblsp;
+
+-- create tablespace owned by a particular user
+CREATE USER regress_user;
+CREATE TABLESPACE regress_owner_tblsp OWNER regress_user LOCATION '' WITH (random_page_cost = 3.0); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_owner_tblsp');
+DROP TABLESPACE regress_owner_tblsp;
+DROP USER regress_user;
+
+-- create a tablespace using all the options
+CREATE TABLESPACE regress_allopt_tblsp LOCATION '' WITH (seq_page_cost = 1.5, random_page_cost = 1.1234567890, effective_io_concurrency = 17, maintenance_io_concurrency = 18); -- ok
+-- see that the tablespace ddl is correctly returned
+SELECT pg_get_tablespace_ddl('regress_allopt_tblsp');
+DROP TABLESPACE regress_allopt_tblsp;
+
+-- see the pg_get_tablespace_ddl error for a dropped 'regress_allopt_tblsp'
+-- tablespace name
+SELECT pg_get_tablespace_ddl('regress_allopt_tblsp');
+
+-- see the pg_get_tablespace_ddl error for an empty input
+SELECT pg_get_tablespace_ddl('');
+
+-- see the pg_get_tablespace_ddl output for a NULL input
+SELECT pg_get_tablespace_ddl(NULL);
--
2.51.2