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

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