On Fri, Oct 31, 2025 at 10:36β―AM Jim Jones <[email protected]>
wrote:
> Hi Manni,
>
> Thanks for the patch!
>
> On 29/10/2025 02:23, Manni Wood wrote:
> > This patch creates a function pg_get_tablespace_ddl, designed to
> > retrieve the full DDL statement for a tablespace. Users can obtain the
> > DDL by providing the tablespace name, like so:
> >
> > 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);
>
>
> Here my first comments regarding usability:
>
> == quoted identifier ==
>
> Tablespace names containing quoted identifiers cannot be parsed:
>
> postgres=# CREATE TABLESPACE "My TS" LOCATION '/tmp/ts';
> CREATE TABLESPACE
> postgres=# SELECT pg_get_tablespace_ddl('"My TS"');
> ERROR: tablespace ""My TS"" does not exist
>
> The following works, but I guess it shouldn't:
>
> postgres=# SELECT pg_get_tablespace_ddl('My TS');
> pg_get_tablespace_ddl
> -----------------------------------------------
> CREATE TABLESPACE "My TS" LOCATION '/tmp/ts';
> (1 row)
>
> The same applies for unicode characters:
>
> postgres=# CREATE TABLESPACE "π" LOCATION '/tmp/ts';
> CREATE TABLESPACE
> postgres=# SELECT pg_get_tablespace_ddl('"π"');
> ERROR: tablespace ""π"" does not exist
> postgres=# SELECT pg_get_tablespace_ddl('π');
> pg_get_tablespace_ddl
> --------------------------------------------
> CREATE TABLESPACE "π" LOCATION '/tmp/ts';
> (1 row)
>
>
> == option precision ==
>
> There is a precision loss in the options:
>
> postgres=# CREATE TABLESPACE ts OWNER u1 LOCATION '/tmp/ts' WITH
> (seq_page_cost = 1.12345678910, random_page_cost = 1.12345678910,
> effective_io_concurrency = 17, maintenance_io_concurrency = 18);
> CREATE TABLESPACE
> postgres=# SELECT pg_get_tablespace_ddl('ts');
>
> pg_get_tablespace_ddl
>
>
> ---------------------------------------------------------------------------------------------------------------------------------------------
> ---------------------------------
> CREATE TABLESPACE ts OWNER u1 LOCATION '/tmp/ts' WITH (random_page_cost
> = 1.12346, seq_page_cost = 1.12346, effective_io_concurrency = 17, m
> aintenance_io_concurrency = 18);
> (1 row)
>
> \db shows it as in the CREATE TABLESPACE statement:
>
> postgres=# \db+ ts
>
> List of tablespaces
> Name | Owner | Location | Access privileges |
> Options
> | Size | Description
>
> ------+-------+----------+-------------------+-----------------------------------------------------------------------------------------------
> -------------------------+---------+-------------
> ts | u1 | /tmp/ts | |
>
> {seq_page_cost=1.12345678910,random_page_cost=1.12345678910,effective_io_concurrency=17,mainte
> nance_io_concurrency=18} | 0 bytes |
> (1 row)
>
>
> == permissions ==
>
> Is it supposed to be visible to all users?
>
> postgres=# CREATE USER u1;
> CREATE ROLE
> postgres=# CREATE TABLESPACE ts LOCATION '/tmp/ts';
> CREATE TABLESPACE
> postgres=# SET ROLE u1;
> SET
> postgres=> SELECT pg_get_tablespace_ddl('ts');
> pg_get_tablespace_ddl
> ----------------------------------------------------
> CREATE TABLESPACE ts OWNER jim LOCATION '/tmp/ts';
> (1 row)
>
> Note that \db does not allow it:
>
> postgres=> SELECT CURRENT_USER;
> current_user
> --------------
> u1
> (1 row)
>
> postgres=> \db+ ts
> ERROR: permission denied for tablespace ts
>
>
> Best, Jim
>
>
> Hi, Jim
Thanks for reviewing my very first patch!
== quoted identifier ==
I see that Postgres already has the SQL function has_tablespace_privilege
that behaves the same way as this patch's pg_get_tablespace_ddl.
# create tablespace "My TS" location '/tmp/has_space';
CREATE TABLESPACE
# select has_tablespace_privilege('My TS', 'create'); rollback;
ββββββββββββββββββββββββββββ
β has_tablespace_privilege β
ββββββββββββββββββββββββββββ€
β t β
ββββββββββββββββββββββββββββ
(1 row)
# select has_tablespace_privilege('"My TS"', 'create'); rollback;
ERROR: 42704: tablespace ""My TS"" does not exist
# create tablespace "π" location '/tmp/has_elephant';
CREATE TABLESPACE
# select has_tablespace_privilege('π', 'create'); rollback;
ββββββββββββββββββββββββββββ
β has_tablespace_privilege β
ββββββββββββββββββββββββββββ€
β t β
ββββββββββββββββββββββββββββ
(1 row)
# select has_tablespace_privilege('"π"', 'create'); rollback;
ERROR: 42704: tablespace ""π"" does not exist
Does the existence of this behavior in an existing function make the same
behavior less surprising for this patch's function?
== option precision ==
Thanks for pointing this out.
I have attached a v2 of the patch that just uses the original text the user
entered for the spcoptions.
This is much better, and it made the code smaller.
I have added "1.1234567890" to one of the tests to show that this works.
== permissions ==
I'm not sure what to think of this. psql's "\db+" does not let me show the
tablespace.
But if, as user 'u1', I select from pg_tablespace directly, I have the
permissions to do so:
postgres> select current_user; rollback;
ββββββββββββββββ
β current_user β
ββββββββββββββββ€
β u1 β
ββββββββββββββββ
(1 row)
postgres> select * from pg_catalog.pg_tablespace; rollback;
βββββββββ¬βββββββββββββ¬βββββββββββ¬βββββββββ¬βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
β oid β spcname β spcowner β spcacl β
spcoptions
β
βββββββββΌβββββββββββββΌβββββββββββΌβββββββββΌβββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ€
β 1663 β pg_default β 10 β [NULL] β [NULL]
β
β 1664 β pg_global β 10 β [NULL] β [NULL]
β
β 19971 β ts β 10 β [NULL] β
{seq_page_cost=1.12345678910,random_page_cost=1.12345678910,effective_io_concurrency=17,maintenance_io_concurrency=18}
β
βββββββββ΄βββββββββββββ΄βββββββββββ΄βββββββββ΄βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
(3 rows)
So if the information is obtainable by selecting from
pg_catalog.pg_tablespace, it seems defensible to make the same data
available via pg_get_tablespace_ddl.
Thoughts?
Thanks again for reviewing my patch,
-Manni
From 7a230ef1cac34988dbbae2b9ba6c401e149caad5 Mon Sep 17 00:00:00 2001
From: Manni Wood <[email protected]>
Date: Mon, 3 Nov 2025 17:11:00 -0600
Subject: [PATCH v2] Adds pg_get_tablespace_ddl function
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.
---
doc/src/sgml/func/func-info.sgml | 45 +++++++++
src/backend/utils/adt/ruleutils.c | 113 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/test/regress/expected/tablespace.out | 68 ++++++++++++++
src/test/regress/sql/tablespace.sql | 46 +++++++++
5 files changed, 275 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..79ef14c6b2f 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 79ec136231b..85b55810ddf 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -22,6 +22,7 @@
#include "access/amapi.h"
#include "access/htup_details.h"
#include "access/relation.h"
+#include "access/reloptions.h"
#include "access/table.h"
#include "catalog/pg_aggregate.h"
#include "catalog/pg_am.h"
@@ -35,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"
@@ -67,6 +69,7 @@
#include "utils/rel.h"
#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
+#include "utils/spccache.h"
#include "utils/syscache.h"
#include "utils/typcache.h"
#include "utils/varlena.h"
@@ -13738,3 +13741,113 @@ get_range_partbound_string(List *bound_datums)
return buf->data;
}
+
+/*
+ * pg_get_tablespace_ddl - Get CREATE TABLESPACE statement for a tablespace
+ */
+Datum
+pg_get_tablespace_ddl(PG_FUNCTION_ARGS)
+{
+ Name tspname = PG_GETARG_NAME(0);
+ char *path;
+ char *spcowner;
+ bool isNull;
+ Oid tspaceoid;
+ Oid tspowneroid;
+ Datum datumLocation;
+ Datum datum;
+ HeapTuple tuple;
+ StringInfoData buf;
+ Form_pg_tablespace tspForm;
+ ArrayType *optArray;
+ Datum *optDatums;
+ int optCount;
+ char *optCStr;
+
+ /* Get the OID of the tablespace; we need it to find the tablespace */
+ tspaceoid = get_tablespace_oid(NameStr(*tspname), false);
+
+ /* Look up the tablespace in pg_tablespace */
+ tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tspaceoid));
+
+ Assert(HeapTupleIsValid(tuple));
+
+ initStringInfo(&buf);
+
+ /* Start building the CREATE TABLESPACE statement */
+ appendStringInfo(&buf, "CREATE TABLESPACE %s",
+ quote_identifier(NameStr(*tspname)));
+
+ /* Get the OID of the owner of the tablespace name */
+ tspForm = (Form_pg_tablespace) GETSTRUCT(tuple);
+ 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 */
+ datumLocation = DirectFunctionCall1(pg_tablespace_location, tspaceoid);
+ path = text_to_cstring(DatumGetTextP(datumLocation));
+ /* 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);
+
+ // NEW
+ if (!isNull)
+ {
+ optArray = DatumGetArrayTypeP(datum);
+
+ /* parseRelOptionsInternal() provided inspiration here */
+ deconstruct_array_builtin(optArray, TEXTOID,
+ &optDatums, NULL, &optCount);
+
+ if (optCount > 0)
+ {
+ appendStringInfoString(&buf, " WITH (");
+ for (int i = 0; i < optCount; i++)
+ {
+ optCStr = TextDatumGetCString(optDatums[i]);
+ appendStringInfoString(&buf, optCStr);
+ appendStringInfoString(&buf, ", ");
+ }
+ /* buf ends up with unwanted trailing comma and space; remove them */
+ buf.len -= 2;
+ buf.data[buf.len] = '\0'; /* Null-terminate the modified string */
+
+ appendStringInfoChar(&buf, ')');
+ }
+
+ pfree(optDatums);
+ }
+
+ ReleaseSysCache(tuple);
+
+ /* Finally add semicolon to the statement */
+ appendStringInfoChar(&buf, ';');
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f76..022c3493542 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8515,6 +8515,9 @@
{ 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' },
{ oid => '2509',
descr => 'deparse an encoded expression with pretty-print option',
proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..ef472f8adb9 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -971,3 +971,71 @@ 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)
+
+-- 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..db3a3705065 100644
--- a/src/test/regress/sql/tablespace.sql
+++ b/src/test/regress/sql/tablespace.sql
@@ -437,3 +437,49 @@ 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');
+
+-- 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.43.0
