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

On Wed, Nov 5, 2025 at 12:56 AM Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:

> On 2025-Nov-04, Jim Jones wrote:
>
> > That's the way I see it too. Of course it's always easier to use the
> > tablespace's name, but there might be cases where you only have the oid
> > -- in which case you'd need to do a JOIN with pg_tablespace to find the
> > name. It's just for convenience.
>
> The other DDL-producing patches that are being posted, all depend on a
> reg* type for their argument, which means they will work correctly with
> either an OID or an object name. Tablespaces are one of the few object
> types for which no "regtablespace" exists, so I think it's fair to
> require both forms.
>
> --
> Álvaro Herrera PostgreSQL Developer —
> https://www.EnterpriseDB.com/
> […] indem ich in meinem Leben oft an euch gedacht, euch glücklich zu
> machen. Seyd es!
> A menudo he pensado en vosotros, en haceros felices. ¡Sedlo, pues!
> Heiligenstädter Testament, L. v. Beethoven, 1802
> https://de.wikisource.org/wiki/Heiligenstädter_Testament

My reasons why I thought only name form was sufficient:-
1. The use case that I had in my mind for this get DDL function was
getting covered with name as its parameter. As we are creating DDL
and name will be part of it. Hence using it as input to our function to
create its DDL.
2. As Álvaro mentioned, we don't have any regtablespace for
tablespaces, So, using <tablespacename>::regtablespace::oid is not
a case for this get_ddl. But is valid for other get_ddl funcs. And even
for them we use the name in the form <objectname>::reg<object>::oid
and internally the get_ddl gets OID. The user again here does not
worry about the OIDs of their <objectname>.
3. As Manni mentions, regarding casting names to oid. But that is not
valid for tablespaces currently. If I am not missing anything. I think
users would explicitly need to provide OID to this function as a value
or from some "select oid ...".
4. The list of other tablespaces functions shared by Jim has two
functions, pg_tablespace_location() & pg_tablespace_databases()
that takes only oid as parameter and not name or text (maybe would
have been better), why? I am not sure, maybe the use case at that
time needed only an oid variant?

But yeah, with the current panel we have a majority here for having the
OID variant for this function. And of course there is no harm with it.

So, PFA v4 patch set. I have included the OID variant in it.

Regards,
Nishant Sharma.
EDB, Pune.
https://www.enterprisedb.com