On Fri, Nov 7, 2025 at 10:16 AM Jim Jones <[email protected]> wrote:
>
>
> On 07/11/2025 02:27, Manni Wood wrote:
> > Attached v7 fixes problems in tests due to whitespace.
>
>
> Since get_tablespace_loc_string returns a palloc'd string, I guess you
> could pfree it after the if block. The same applies for spcowner, since
> you're calling GetUserNameFromId() with noerr = false.
>
> For reference, see pg_get_indexdef_worker():
>
> ...
> /*
> * If it has options, append "WITH (options)"
> */
> str = flatten_reloptions(indexrelid);
> if (str)
> {
> appendStringInfo(&buf, " WITH (%s)", str);
> pfree(str);
> }
> ...
>
>
> Thanks
>
> Best, Jim
>
Hello, Álvaro and Jim!
I have incorporated both of your suggestions into this pair of v8 patches.
Let me know what you think.
--
-- Manni Wood EDB: https://www.enterprisedb.com
From 29e92da55e817c7938cf0be5bdda4bc2d420c7ca Mon Sep 17 00:00:00 2001
From: Manni Wood <[email protected]>
Date: Fri, 7 Nov 2025 15:59:14 -0600
Subject: [PATCH v8 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 <[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/utils/adt/ruleutils.c | 153 +++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/test/regress/expected/tablespace.out | 69 ++++++++++
src/test/regress/sql/tablespace.sql | 47 +++++++
5 files changed, 320 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 c8023e83f4b..31ddd2ea49a 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"
@@ -13744,3 +13745,155 @@ get_range_partbound_string(List *bound_datums)
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));
+ pfree(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);
+ }
+ /* Done with path */
+ pfree(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 5cf9e12fcb9..0fcd0f4fa8f 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/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
From b3e23e59ff74f9def70416a65a4ebb59052c49bf Mon Sep 17 00:00:00 2001
From: Manni Wood <[email protected]>
Date: Fri, 7 Nov 2025 15:12:18 -0600
Subject: [PATCH v8 1/2] Supporting changes for pg_get_tablespace_ddl function
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Moving core logic of pg_tablespace_location() to new generic function
get_tablespace_loc_string()
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
---
src/backend/catalog/Makefile | 1 +
src/backend/catalog/pg_tablespace.c | 151 ++++++++++++++++++++++++++++
src/backend/utils/adt/misc.c | 62 +-----------
src/backend/utils/adt/ruleutils.c | 1 +
src/include/catalog/pg_tablespace.h | 2 +
5 files changed, 159 insertions(+), 58 deletions(-)
create mode 100644 src/backend/catalog/pg_tablespace.c
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index c090094ed08..8e40e1b8189 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -44,6 +44,7 @@ OBJS = \
pg_range.o \
pg_shdepend.o \
pg_subscription.o \
+ pg_tablespace.o \
pg_type.o \
storage.o \
toasting.o
diff --git a/src/backend/catalog/pg_tablespace.c b/src/backend/catalog/pg_tablespace.c
new file mode 100644
index 00000000000..95f7c3bf6d0
--- /dev/null
+++ b/src/backend/catalog/pg_tablespace.c
@@ -0,0 +1,151 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_tablespace.c
+ * routines to support tablespaces
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/catalog/pg_tablespace.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include <ctype.h>
+#include <unistd.h>
+#include <fcntl.h>
+#include <sys/stat.h>
+
+#include "access/amapi.h"
+#include "access/htup_details.h"
+#include "access/relation.h"
+#include "access/table.h"
+#include "catalog/pg_aggregate.h"
+#include "catalog/pg_am.h"
+#include "catalog/pg_authid.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_constraint.h"
+#include "catalog/pg_depend.h"
+#include "catalog/pg_language.h"
+#include "catalog/pg_opclass.h"
+#include "catalog/pg_operator.h"
+#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"
+#include "commands/tablespace.h"
+#include "common/keywords.h"
+#include "executor/spi.h"
+#include "funcapi.h"
+#include "mb/pg_wchar.h"
+#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pathnodes.h"
+#include "optimizer/optimizer.h"
+#include "parser/parse_agg.h"
+#include "parser/parse_func.h"
+#include "parser/parse_oper.h"
+#include "parser/parse_relation.h"
+#include "parser/parser.h"
+#include "parser/parsetree.h"
+#include "rewrite/rewriteHandler.h"
+#include "rewrite/rewriteManip.h"
+#include "rewrite/rewriteSupport.h"
+#include "utils/array.h"
+#include "utils/builtins.h"
+#include "utils/fmgroids.h"
+#include "utils/guc.h"
+#include "utils/hsearch.h"
+#include "utils/lsyscache.h"
+#include "utils/partcache.h"
+#include "utils/rel.h"
+#include "utils/ruleutils.h"
+#include "utils/snapmgr.h"
+#include "utils/syscache.h"
+#include "utils/typcache.h"
+#include "utils/varlena.h"
+#include "utils/xml.h"
+
+/*
+ * get_tablespace_loc_string - Get a tablespace's location as a C-string.
+ */
+char *
+get_tablespace_loc_string(Oid tablespaceOid)
+{
+ char sourcepath[MAXPGPATH] = {'\0'};
+ char targetpath[MAXPGPATH] = {'\0'};
+ int rllen;
+ struct stat st;
+ StringInfoData buf;
+
+ initStringInfo(&buf);
+ appendStringInfoString(&buf, "");
+
+ /*
+ * It's useful to apply this function to pg_class.reltablespace, wherein
+ * zero means "the database's default tablespace". So, rather than
+ * throwing an error for zero, we choose to assume that's what is meant.
+ */
+ if (tablespaceOid == InvalidOid)
+ tablespaceOid = MyDatabaseTableSpace;
+
+ /*
+ * Return empty string for the cluster's default tablespaces
+ */
+ if (tablespaceOid == DEFAULTTABLESPACE_OID ||
+ tablespaceOid == GLOBALTABLESPACE_OID)
+ return buf.data;
+
+ /*
+ * Find the location of the tablespace by reading the symbolic link that
+ * is in pg_tblspc/<oid>.
+ */
+ snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
+
+ /*
+ * Before reading the link, check if the source path is a link or a
+ * junction point. Note that a directory is possible for a tablespace
+ * created with allow_in_place_tablespaces enabled. If a directory is
+ * found, a relative path to the data directory is returned.
+ */
+ if (lstat(sourcepath, &st) < 0)
+ {
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not stat file \"%s\": %m",
+ sourcepath)));
+ }
+
+ if (!S_ISLNK(st.st_mode))
+ {
+ appendStringInfoString(&buf, sourcepath);
+ return buf.data;
+ }
+
+ /*
+ * In presence of a link or a junction point, return the path pointing to.
+ */
+ rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
+ if (rllen < 0)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not read symbolic link \"%s\": %m",
+ sourcepath)));
+ if (rllen >= sizeof(targetpath))
+ ereport(ERROR,
+ (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("symbolic link \"%s\" target is too long",
+ sourcepath)));
+ targetpath[rllen] = '\0';
+
+ appendStringInfoString(&buf, targetpath);
+
+ return buf.data;
+}
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index fa1cb675027..cb99d7435eb 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -315,66 +315,12 @@ Datum
pg_tablespace_location(PG_FUNCTION_ARGS)
{
Oid tablespaceOid = PG_GETARG_OID(0);
- char sourcepath[MAXPGPATH];
- char targetpath[MAXPGPATH];
- int rllen;
- struct stat st;
+ char *tablespaceLoc;
- /*
- * It's useful to apply this function to pg_class.reltablespace, wherein
- * zero means "the database's default tablespace". So, rather than
- * throwing an error for zero, we choose to assume that's what is meant.
- */
- if (tablespaceOid == InvalidOid)
- tablespaceOid = MyDatabaseTableSpace;
-
- /*
- * Return empty string for the cluster's default tablespaces
- */
- if (tablespaceOid == DEFAULTTABLESPACE_OID ||
- tablespaceOid == GLOBALTABLESPACE_OID)
- PG_RETURN_TEXT_P(cstring_to_text(""));
-
- /*
- * Find the location of the tablespace by reading the symbolic link that
- * is in pg_tblspc/<oid>.
- */
- snprintf(sourcepath, sizeof(sourcepath), "%s/%u", PG_TBLSPC_DIR, tablespaceOid);
-
- /*
- * Before reading the link, check if the source path is a link or a
- * junction point. Note that a directory is possible for a tablespace
- * created with allow_in_place_tablespaces enabled. If a directory is
- * found, a relative path to the data directory is returned.
- */
- if (lstat(sourcepath, &st) < 0)
- {
- ereport(ERROR,
- (errcode_for_file_access(),
- errmsg("could not stat file \"%s\": %m",
- sourcepath)));
- }
-
- if (!S_ISLNK(st.st_mode))
- PG_RETURN_TEXT_P(cstring_to_text(sourcepath));
-
- /*
- * In presence of a link or a junction point, return the path pointing to.
- */
- rllen = readlink(sourcepath, targetpath, sizeof(targetpath));
- if (rllen < 0)
- ereport(ERROR,
- (errcode_for_file_access(),
- errmsg("could not read symbolic link \"%s\": %m",
- sourcepath)));
- if (rllen >= sizeof(targetpath))
- ereport(ERROR,
- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
- errmsg("symbolic link \"%s\" target is too long",
- sourcepath)));
- targetpath[rllen] = '\0';
+ /* Get LOCATION string from its OID */
+ tablespaceLoc = get_tablespace_loc_string(tablespaceOid);
- PG_RETURN_TEXT_P(cstring_to_text(targetpath));
+ PG_RETURN_TEXT_P(cstring_to_text(tablespaceLoc));
}
/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..c8023e83f4b 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -18,6 +18,7 @@
#include <ctype.h>
#include <unistd.h>
#include <fcntl.h>
+#include <sys/stat.h>
#include "access/amapi.h"
#include "access/htup_details.h"
diff --git a/src/include/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index 5293488c630..f065cff9ddc 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -54,4 +54,6 @@ DECLARE_UNIQUE_INDEX(pg_tablespace_spcname_index, 2698, TablespaceNameIndexId, p
MAKE_SYSCACHE(TABLESPACEOID, pg_tablespace_oid_index, 4);
+extern char *get_tablespace_loc_string(Oid tablespaceOid);
+
#endif /* PG_TABLESPACE_H */
--
2.51.2
