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