Обсуждение: [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

On 04/11/2025 00:49, Manni Wood wrote:
> == 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.

You're right. The source of my confusion is that I was approaching the
tablespace name as if it were a relation:

postgres=# CREATE TABLE "T"();
CREATE TABLE
postgres=# SELECT '"T"'::regclass::oid;
  oid
-------
 47766
(1 row)

postgres=# SELECT 'T'::regclass::oid;
ERROR:  relation "t" does not exist
LINE 1: SELECT 'T'::regclass::oid;

But I see that other functions behave similarly, e.g. pg_tablespace_size:

postgres=# SELECT pg_tablespace_size('My TS');
 pg_tablespace_size
--------------------
                  0
(1 row)

postgres=# SELECT pg_tablespace_size('"My TS"');
ERROR:  tablespace ""My TS"" does not exist
postgres=#

Sorry for the noise.

Do you think that an overload in pg_proc.dat with oid as parameter would
make sense here? e.g.

{ oid => '2322',
  descr => 'total disk space usage for the specified tablespace',
  proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',
  proargtypes => 'oid', prosrc => 'pg_tablespace_size_oid' },
{ oid => '2323',
  descr => 'total disk space usage for the specified tablespace',
  proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',
  proargtypes => 'name', prosrc => 'pg_tablespace_size_name' },


> 
> == 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.


Nice. It now shows the options without precision loss:

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
(seq_page_cost=1.12345678910, random_page_cost=1.12345678910,
effective_io_concurrency=17, maintenance_io_concurrency=18);
(1 row)

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,maintenance_io_concurrency=18}
| 0 bytes |
(1 row)


> == permissions ==
> 
> I'm not sure what to think of this. psql's "\db+" does not let me show
> the tablespace.
> 

Right. I guess the difference here is that \db+ also shows the
tablespace's size, which requires the user to actually read it.

postgres=# CREATE TABLESPACE ts OWNER jim LOCATION '/tmp/ts';
CREATE TABLESPACE
postgres=# SELECT pg_tablespace_size('ts');
 pg_tablespace_size
--------------------
                  0
(1 row)

postgres=# SET ROLE u1;
SET
postgres=> SELECT pg_tablespace_size('ts');
ERROR:  permission denied for tablespace ts

Since pg_get_tablespace_ddl doesn't display size, I believe it's fine as-is.

Thanks.

Best, Jim

Hi Nishant

On 04/11/2025 11:37, Nishant Sharma wrote:
> Using name as parameter is more user friendly than OID.
> Because users usually do not know the oids. Constructing
> the DDL from the name appears better as it contains a name
> in it. So, no gain in having an OID version of
> pg_get_tablespace_ddl.

Would you also say that having a pg_tablespace_size(oid) has no benefit?

I took a look at similar functions, and the only pattern I could
identify is that all of them take an oid parameter.

pg_tablespace_size:       oid and name
pg_tablespace_location:   oid
has_tablespace_privilege: oid, name, and text
pg_tablespace_databases:  oid
...
pg_get_tablespace_ddl:    name

I'm definitely not opposed to having just a name parameter, but I
thought it would be worth mentioning.

Best, Jim