Thread

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

Manni Wood <manni.wood@enterprisedb.com> — 2025-11-03T23:49:23Z

On Fri, Oct 31, 2025 at 10:36 AM Jim Jones <jim.jones@uni-muenster.de>
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