Re: [PATCH] Add pg_get_tablespace_ddl() function to reconstruct CREATE TABLESPACE statement

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