From c82a8c634fa828d2ae1aebf9b8394b3a42e1fe65 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Thu, 19 Mar 2026 09:55:16 -0400 Subject: [PATCH 3/4] Add pg_get_tablespace_ddl() function MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Add a new SQL-callable function that returns the DDL statements needed to recreate a tablespace. It takes a tablespace name or OID and an optional VARIADIC text argument for options that are specified as alternating name/value pairs. The following option is supported: pretty (boolean) for formatted output. (It includes two variants because there is no regtablespace pseudotype.) The return is one or multiple rows where the first row is a CREATE TABLESPACE statement and subsequent rows are ALTER TABLESPACE statements to set some tablespace properties. get_reloptions() in ruleutils.c is made non-static so it can be called from the new ddlutils.c file. Author: Nishant Sharma Author: Manni Wood Co-authored-by: Andrew Dunstan Reviewed-by: Jim Jones Reviewed-by: Álvaro Herrera Reviewed-by: Chao Li Reviewed-by: Euler Taveira Discussion: https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com --- doc/src/sgml/func/func-info.sgml | 27 +++ src/backend/utils/adt/ddlutils.c | 190 ++++++++++++++++++- src/backend/utils/adt/ruleutils.c | 4 +- src/include/catalog/pg_proc.dat | 16 ++ src/include/utils/ruleutils.h | 1 + src/test/regress/expected/tablespace_ddl.out | 65 +++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/tablespace_ddl.sql | 43 +++++ 8 files changed, 344 insertions(+), 4 deletions(-) create mode 100644 src/test/regress/expected/tablespace_ddl.out create mode 100644 src/test/regress/sql/tablespace_ddl.sql diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml index acd1a7cfeed..f44bd0d0f8b 100644 --- a/doc/src/sgml/func/func-info.sgml +++ b/doc/src/sgml/func/func-info.sgml @@ -3908,6 +3908,33 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres} for pretty-printed output. + + + + pg_get_tablespace_ddl + + pg_get_tablespace_ddl + ( tablespace oid + , VARIADIC options + text ) + setof text + + + pg_get_tablespace_ddl + ( tablespace name + , VARIADIC options + text ) + setof text + + + Reconstructs the CREATE TABLESPACE statement for + the specified tablespace (by OID or name). If the tablespace has + options set, an ALTER TABLESPACE ... SET statement + is also returned. Each statement is returned as a separate row. + The following option is supported: pretty (boolean) + for formatted output. + + diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c index c067de0a3d2..65df3c723cc 100644 --- a/src/backend/utils/adt/ddlutils.c +++ b/src/backend/utils/adt/ddlutils.c @@ -20,10 +20,12 @@ #include "access/genam.h" #include "access/htup_details.h" -#include "access/relation.h" #include "access/table.h" #include "catalog/pg_authid.h" #include "catalog/pg_db_role_setting.h" +#include "catalog/pg_tablespace.h" +#include "commands/tablespace.h" +#include "common/relpath.h" #include "funcapi.h" #include "miscadmin.h" #include "utils/acl.h" @@ -34,6 +36,7 @@ #include "utils/guc.h" #include "utils/lsyscache.h" #include "utils/rel.h" +#include "utils/ruleutils.h" #include "utils/syscache.h" #include "utils/timestamp.h" #include "utils/varlena.h" @@ -67,6 +70,8 @@ static void append_ddl_option(StringInfo buf, bool pretty, int indent, const char *fmt,...) pg_attribute_printf(4, 5); static List *pg_get_role_ddl_internal(Oid roleid, bool pretty); +static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty); +static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull); /* @@ -541,3 +546,186 @@ pg_get_role_ddl(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } } + +/* + * pg_get_tablespace_ddl_internal + * Generate DDL statements to recreate a tablespace. + * + * Returns a List of palloc'd strings. The first element is the + * CREATE TABLESPACE statement; if the tablespace has reloptions, + * a second element with ALTER TABLESPACE SET (...) is appended. + */ +static List * +pg_get_tablespace_ddl_internal(Oid tsid, bool pretty) +{ + HeapTuple tuple; + Form_pg_tablespace tspForm; + StringInfoData buf; + char *spcname; + char *spcowner; + char *path; + bool isNull; + Datum datum; + List *statements = NIL; + + tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tsid)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("tablespace with OID %u does not exist", + tsid))); + + tspForm = (Form_pg_tablespace) GETSTRUCT(tuple); + spcname = pstrdup(NameStr(tspForm->spcname)); + + /* + * We don't support generating DDL for system tablespaces. The primary + * reason for this is that users shouldn't be recreating them. + */ + if (IsReservedName(spcname)) + ereport(ERROR, + (errcode(ERRCODE_RESERVED_NAME), + errmsg("tablespace name \"%s\" is reserved", spcname), + errdetail("Tablespace names starting with \"pg_\" are reserved for system tablespaces."))); + + initStringInfo(&buf); + + /* Start building the CREATE TABLESPACE statement */ + appendStringInfo(&buf, "CREATE TABLESPACE %s", quote_identifier(spcname)); + + /* Add OWNER clause */ + spcowner = GetUserNameFromId(tspForm->spcowner, false); + append_ddl_option(&buf, pretty, 4, "OWNER %s", + quote_identifier(spcowner)); + pfree(spcowner); + + /* Find tablespace directory path */ + path = get_tablespace_location(tsid); + + /* 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) + append_ddl_option(&buf, pretty, 4, "LOCATION ''"); + else + append_ddl_option(&buf, pretty, 4, "LOCATION %s", + quote_literal_cstr(path)); + } + pfree(path); + + appendStringInfoChar(&buf, ';'); + statements = lappend(statements, pstrdup(buf.data)); + + /* Check for tablespace options */ + datum = SysCacheGetAttr(TABLESPACEOID, tuple, + Anum_pg_tablespace_spcoptions, &isNull); + if (!isNull) + { + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER TABLESPACE %s SET (", + quote_identifier(spcname)); + get_reloptions(&buf, datum); + appendStringInfoString(&buf, ");"); + statements = lappend(statements, pstrdup(buf.data)); + } + + ReleaseSysCache(tuple); + pfree(buf.data); + + return statements; +} + +/* + * pg_get_tablespace_ddl_srf - common SRF logic for tablespace DDL + */ +static Datum +pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull) +{ + FuncCallContext *funcctx; + List *statements; + + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + DdlOption opts[] = {{"pretty", DDL_OPT_BOOL}}; + + funcctx = SRF_FIRSTCALL_INIT(); + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + if (isnull) + { + MemoryContextSwitchTo(oldcontext); + SRF_RETURN_DONE(funcctx); + } + + parse_ddl_options(fcinfo, 1, opts, lengthof(opts)); + + statements = pg_get_tablespace_ddl_internal(tsid, + opts[0].isset && opts[0].boolval); + funcctx->user_fctx = statements; + funcctx->max_calls = list_length(statements); + + MemoryContextSwitchTo(oldcontext); + } + + funcctx = SRF_PERCALL_SETUP(); + statements = (List *) funcctx->user_fctx; + + if (funcctx->call_cntr < funcctx->max_calls) + { + char *stmt; + + stmt = (char *) list_nth(statements, funcctx->call_cntr); + + SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt)); + } + else + { + list_free_deep(statements); + SRF_RETURN_DONE(funcctx); + } +} + +/* + * pg_get_tablespace_ddl_oid + * Return DDL to recreate a tablespace, taking OID. + */ +Datum +pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS) +{ + Oid tsid = InvalidOid; + bool isnull; + + isnull = PG_ARGISNULL(0); + tsid = PG_GETARG_OID(0); + + return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull); +} + +/* + * pg_get_tablespace_ddl_name + * Return DDL to recreate a tablespace, taking name. + */ +Datum +pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS) +{ + Oid tsid = InvalidOid; + Name tspname; + bool isnull; + + isnull = PG_ARGISNULL(0); + + if (!isnull) + { + tspname = PG_GETARG_NAME(0); + tsid = get_tablespace_oid(NameStr(*tspname), false); + } + + return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull); +} diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 7bc12589e40..1450c101e9e 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -545,7 +545,7 @@ 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); +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, @@ -14199,7 +14199,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; diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index e908146645f..d25b6966d5b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8596,6 +8596,22 @@ proallargtypes => '{regrole,text}', pronargdefaults => '1', proargdefaults => '{NULL}', prosrc => 'pg_get_role_ddl' }, +{ oid => '8758', descr => 'get DDL to recreate a tablespace', + proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f', + provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text', + proargtypes => 'oid text', + proargmodes => '{i,v}', + proallargtypes => '{oid,text}', + pronargdefaults => '1', proargdefaults => '{NULL}', + prosrc => 'pg_get_tablespace_ddl_oid' }, +{ oid => '8759', descr => 'get DDL to recreate a tablespace', + proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f', + provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text', + proargtypes => 'name text', + proargmodes => '{i,v}', + proallargtypes => '{name,text}', + pronargdefaults => '1', proargdefaults => '{NULL}', + prosrc => 'pg_get_tablespace_ddl_name' }, { 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 908b2708ed4..ac40d4c714e 100644 --- a/src/include/utils/ruleutils.h +++ b/src/include/utils/ruleutils.h @@ -51,6 +51,7 @@ extern char *get_window_frame_options_for_explain(int frameOptions, extern char *generate_collation_name(Oid collid); extern char *generate_opclass_name(Oid opclass); extern char *get_range_partbound_string(List *bound_datums); +extern void get_reloptions(StringInfo buf, Datum reloptions); extern char *pg_get_statisticsobjdef_string(Oid statextid); diff --git a/src/test/regress/expected/tablespace_ddl.out b/src/test/regress/expected/tablespace_ddl.out new file mode 100644 index 00000000000..993841a7de1 --- /dev/null +++ b/src/test/regress/expected/tablespace_ddl.out @@ -0,0 +1,65 @@ +-- +-- Tests for pg_get_tablespace_ddl() +-- +SET allow_in_place_tablespaces = true; +CREATE ROLE regress_tblspc_ddl_user; +-- error: non-existent tablespace by name +SELECT * FROM pg_get_tablespace_ddl('regress_nonexistent_tblsp'); +ERROR: tablespace "regress_nonexistent_tblsp" does not exist +-- error: non-existent tablespace by OID +SELECT * FROM pg_get_tablespace_ddl(0::oid); +ERROR: tablespace with OID 0 does not exist +-- NULL input returns no rows (name variant) +SELECT * FROM pg_get_tablespace_ddl(NULL::name); + pg_get_tablespace_ddl +----------------------- +(0 rows) + +-- NULL input returns no rows (OID variant) +SELECT * FROM pg_get_tablespace_ddl(NULL::oid); + pg_get_tablespace_ddl +----------------------- +(0 rows) + +-- tablespace name requiring quoting +CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION ''; +SELECT * FROM pg_get_tablespace_ddl('regress_ tblsp'); + pg_get_tablespace_ddl +------------------------------------------------------------------------------- + CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION ''; +(1 row) + +DROP TABLESPACE "regress_ tblsp"; +-- tablespace with multiple options +CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION '' + WITH (seq_page_cost = '1.5', random_page_cost = '1.1234567890', + effective_io_concurrency = '17', maintenance_io_concurrency = '18'); +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp'); + pg_get_tablespace_ddl +------------------------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; + ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost='1.1234567890', effective_io_concurrency='17', maintenance_io_concurrency='18'); +(2 rows) + +-- pretty-printed output +\pset format unaligned +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'pretty', 'true'); +pg_get_tablespace_ddl +CREATE TABLESPACE regress_allopt_tblsp + OWNER regress_tblspc_ddl_user + LOCATION ''; +ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost='1.1234567890', effective_io_concurrency='17', maintenance_io_concurrency='18'); +(2 rows) +\pset format aligned +DROP TABLESPACE regress_allopt_tblsp; +-- test by OID +CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; +SELECT oid AS tsid FROM pg_tablespace WHERE spcname = 'regress_oid_tblsp' \gset +SELECT * FROM pg_get_tablespace_ddl(:tsid); + pg_get_tablespace_ddl +-------------------------------------------------------------------------------- + CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; +(1 row) + +DROP TABLESPACE regress_oid_tblsp; +DROP ROLE regress_tblspc_ddl_user; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 7e059cef034..f3a01aecf04 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -130,7 +130,7 @@ test: partition_merge partition_split partition_join partition_prune reloptions # oidjoins is read-only, though, and should run late for best coverage test: oidjoins event_trigger -test: role_ddl +test: role_ddl tablespace_ddl # event_trigger_login cannot run concurrently with any other tests because # on-login event handling could catch connection of a concurrent test. diff --git a/src/test/regress/sql/tablespace_ddl.sql b/src/test/regress/sql/tablespace_ddl.sql new file mode 100644 index 00000000000..90ee6c1d703 --- /dev/null +++ b/src/test/regress/sql/tablespace_ddl.sql @@ -0,0 +1,43 @@ +-- +-- Tests for pg_get_tablespace_ddl() +-- + +SET allow_in_place_tablespaces = true; +CREATE ROLE regress_tblspc_ddl_user; + +-- error: non-existent tablespace by name +SELECT * FROM pg_get_tablespace_ddl('regress_nonexistent_tblsp'); + +-- error: non-existent tablespace by OID +SELECT * FROM pg_get_tablespace_ddl(0::oid); + +-- NULL input returns no rows (name variant) +SELECT * FROM pg_get_tablespace_ddl(NULL::name); + +-- NULL input returns no rows (OID variant) +SELECT * FROM pg_get_tablespace_ddl(NULL::oid); + +-- tablespace name requiring quoting +CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION ''; +SELECT * FROM pg_get_tablespace_ddl('regress_ tblsp'); +DROP TABLESPACE "regress_ tblsp"; + +-- tablespace with multiple options +CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION '' + WITH (seq_page_cost = '1.5', random_page_cost = '1.1234567890', + effective_io_concurrency = '17', maintenance_io_concurrency = '18'); +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp'); + +-- pretty-printed output +\pset format unaligned +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'pretty', 'true'); +\pset format aligned +DROP TABLESPACE regress_allopt_tblsp; + +-- test by OID +CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; +SELECT oid AS tsid FROM pg_tablespace WHERE spcname = 'regress_oid_tblsp' \gset +SELECT * FROM pg_get_tablespace_ddl(:tsid); +DROP TABLESPACE regress_oid_tblsp; + +DROP ROLE regress_tblspc_ddl_user; -- 2.43.0