On Wed, Nov 19, 2025 at 1:52 AM Jim Jones <[email protected]> wrote:
>
> Hi Chao
>
> On 19/11/2025 04:13, Chao Li wrote:
> > 1. Do we need to perform some privilege check? I just did a test:
> > ```
> > evantest=> \c
> > You are now connected to database "evantest" as user "evan".
> > evantest=> select pg_get_tablespace_ddl('pg_default');
> > pg_get_tablespace_ddl
> > -------------------------------------------
> > CREATE TABLESPACE pg_default OWNER chaol;
> > (1 row)
> > ```
> >
> > Where “evan” is a new user without grant any persuasion to it, but it
> can view the system default tablespace’s DDL. I don’t think that’s expected.
>
> It is expected. \db behaves similarly:
>
>
> CREATE TABLESPACE ts LOCATION '/tmp/ts';
> CREATE TABLESPACE
>
> postgres=# CREATE USER foo;
> CREATE ROLE
>
> postgres=# SET ROLE foo;
> SET
>
> postgres=> \db ts
> List of tablespaces
> Name | Owner | Location
> ------+-------+----------
> ts | jim | /tmp/ts
> (1 row)
>
>
> IIUC the user foo is just reading the catalog entry of the new
> tablespace, which is fine. Of course, accessing the tablespace itself is
> not allowed. See \db+ (calculates the tablespace's size)
>
>
> postgres=> \db+ ts
> ERROR: permission denied for tablespace ts
>
> Best, Jim
>
Hello, Chao.
Thanks as always for your ongoing help with improving this feature.
Instead of moving build_tablespace_ddl_string out of pg_tablespace.c, I
made get_reloptions visible outside of ruleutils.c.
Otherwise, I followed your advice on using get_reloptions to DRY up the
code.
Let me know what you think!
--
-- Manni Wood EDB: https://www.enterprisedb.com
From d5dbad978a5f3d4d31cab1e256c2108ed1714740 Mon Sep 17 00:00:00 2001
From: Manni Wood <[email protected]>
Date: Wed, 19 Nov 2025 16:01:36 -0600
Subject: [PATCH v14] 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 <[email protected]> and Nishant Sharma
<[email protected]>
Reviewers: Vaibhav Dalvi, Ian Barwick, Jim Jones, Álvaro Herrera
Discussion:
https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3scsnj02c0kobqjnbl2ajfpw...@mail.gmail.com
---
doc/src/sgml/func/func-info.sgml | 45 ++++++++++
src/backend/catalog/pg_tablespace.c | 100 +++++++++++++++++++++++
src/backend/utils/adt/ruleutils.c | 40 ++++++++-
src/include/catalog/pg_proc.dat | 6 ++
src/include/catalog/pg_tablespace.h | 1 +
src/include/utils/ruleutils.h | 2 +
src/test/regress/expected/tablespace.out | 69 ++++++++++++++++
src/test/regress/sql/tablespace.sql | 47 +++++++++++
8 files changed, 308 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..64c56e0a469 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> or <type>oid</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/catalog/pg_tablespace.c b/src/backend/catalog/pg_tablespace.c
index 6aca24c231e..efe6dbe51ca 100644
--- a/src/backend/catalog/pg_tablespace.c
+++ b/src/backend/catalog/pg_tablespace.c
@@ -17,9 +17,14 @@
#include <unistd.h>
#include <sys/stat.h>
+#include "access/htup_details.h"
#include "catalog/pg_tablespace.h"
#include "commands/tablespace.h"
#include "miscadmin.h"
+#include "utils/array.h"
+#include "utils/builtins.h"
+#include "utils/ruleutils.h"
+#include "utils/syscache.h"
/*
@@ -88,3 +93,98 @@ get_tablespace_location(Oid tablespaceOid)
return pstrdup(targetpath);
}
+
+/*
+ * build_tablespace_ddl_string - Build CREATE TABLESPACE statement as a
+ * C-string for a tablespace from its 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 %u 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));
+ pfree(spcowner);
+ }
+
+ /* Find tablespace directory path */
+ path = get_tablespace_location(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", quote_literal_cstr(path));
+ }
+ /* Done with path */
+ pfree(path);
+
+ /* Get tablespace's options datum from the tuple */
+ datum = SysCacheGetAttr(TABLESPACEOID,
+ tuple,
+ Anum_pg_tablespace_spcoptions,
+ &isNull);
+
+ if (!isNull)
+ {
+ /* Start WITH clause */
+ appendStringInfoString(&buf, " WITH (");
+
+ /* Append reloptions to buf */
+ get_reloptions(&buf, datum);
+
+ /* Close WITH clause */
+ appendStringInfoChar(&buf, ')');
+ }
+
+ ReleaseSysCache(tuple);
+
+ /* Finally add semicolon to the statement */
+ appendStringInfoChar(&buf, ';');
+
+ return buf.data;
+}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..1c9ef0a597e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -35,6 +35,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"
@@ -536,7 +537,6 @@ static void add_cast_to(StringInfo buf, Oid typid);
static char *generate_qualified_type_name(Oid typid);
static text *string_to_text(char *str);
static char *flatten_reloptions(Oid relid);
-static void get_reloptions(StringInfo buf, Datum reloptions);
static void get_json_path_spec(Node *path_spec, deparse_context *context,
bool showimplicit);
static void get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
@@ -13619,7 +13619,7 @@ string_to_text(char *str)
/*
* Generate a C string representing a relation options from text[] datum.
*/
-static void
+void
get_reloptions(StringInfo buf, Datum reloptions)
{
Datum *options;
@@ -13743,3 +13743,39 @@ get_range_partbound_string(List *bound_datums)
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 aaadfd8c748..77968d1b13f 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/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index 7816d779d8c..b70035832df 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -55,5 +55,6 @@ DECLARE_UNIQUE_INDEX(pg_tablespace_spcname_index, 2698, TablespaceNameIndexId, p
MAKE_SYSCACHE(TABLESPACEOID, pg_tablespace_oid_index, 4);
extern char *get_tablespace_location(Oid tablespaceOid);
+extern char *build_tablespace_ddl_string(const Oid tspaceoid);
#endif /* PG_TABLESPACE_H */
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..0a7d30de85a 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -54,4 +54,6 @@ extern char *get_range_partbound_string(List *bound_datums);
extern char *pg_get_statisticsobjdef_string(Oid statextid);
+extern void get_reloptions(StringInfo buf, Datum reloptions);
+
#endif /* RULEUTILS_H */
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..aa8ec8da506 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..6298ef1f37b 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.52.0
