Обсуждение: Try adding type cast with tablespace

Hi all,

Good day!

I am a newbee to PostgreSQL and recently came across an idea about type-casting tablespace OID.

The motibation is that when I have to upgrade a PostgreSQL database, we need to join other tables to 

track tablespace name. I have just created a simple patch to resolve this.

Hope you can take a look with this.

My Execution Sample:

# After Patch:

------------------------------------------------------------------------

postgres=# SELECT oid,oid::regtablespace,spcname from pg_tablespace ;

 oid  |    oid     |  spcname

------+------------+------------

 1663 | pg_default | pg_default

 1664 | pg_global  | pg_global

(2 rows)

------------------------------------------------------------------------

# Before Patch

------------------------------------------------------------------------

postgres-# SELECT oid,oid::regtablespace,spcname from pg_tablespace ;

ERROR:  syntax error at or near "oid"

LINE 1: oid  |    oid     |  spcname

        ^

------------------------------------------------------------------------

I added the "::regtablespace" part to source.

Note: While developing, I also had to add several rows to pgcatalog tables.

      Please point out if any OID newly assigned is not appropriate.

Kind Regards,

Yuki Tei

Hello,Yuki.

My understanding is that your patch is aimed to enrich an alias type for oid.
There are already some alias types for oid so I think it is good to
add regtablespace for convenience.

https://www.postgresql.org/docs/16/datatype-oid.html#DATATYPE-OID-TABLE

Actually,I also felt it is a bit of a hassle to join tables to find
tablespace name from pg_database,
it is convenient if I can use regtablespace alias.

Therefore I think it is good to add regtablespace alias,but I’m also
newbie pgsql-hackers.
We need some senior hackers’s opinions.

Kind Regards,
Kenichiro Tanaka
>
> Hi all,
>
>
> Good day!
>
>
> I am a newbee to PostgreSQL and recently came across an idea about type-casting tablespace OID.
>
> The motibation is that when I have to upgrade a PostgreSQL database, we need to join other tables to
>
> track tablespace name. I have just created a simple patch to resolve this.
>
>
> Hope you can take a look with this.
>
>
> My Execution Sample:
>
> # After Patch:
>
> ------------------------------------------------------------------------
>
> postgres=# SELECT oid,oid::regtablespace,spcname from pg_tablespace ;
>
>  oid  |    oid     |  spcname
>
> ------+------------+------------
>
>  1663 | pg_default | pg_default
>
>  1664 | pg_global  | pg_global
>
> (2 rows)
>
> ------------------------------------------------------------------------
>
>
> # Before Patch
>
> ------------------------------------------------------------------------
>
> postgres-# SELECT oid,oid::regtablespace,spcname from pg_tablespace ;
>
> ERROR:  syntax error at or near "oid"
>
> LINE 1: oid  |    oid     |  spcname
>
>         ^
>
> ------------------------------------------------------------------------
>
>
> I added the "::regtablespace" part to source.
>
> Note: While developing, I also had to add several rows to pgcatalog tables.
>
>       Please point out if any OID newly assigned is not appropriate.
>
>
> Kind Regards,
>
> Yuki Tei

Kenichiro Tanaka <kenichirotanakapg@gmail.com> writes:
> Therefore I think it is good to add regtablespace alias,but I’m also
> newbie pgsql-hackers.
> We need some senior hackers’s opinions.

Well ... for my two cents, I'm kind of down on this, mainly because
I don't understand where we'd stop.  I don't want to end up in a
scenario where every system catalog is expected to have a reg*
type to go with it, because that'd create a lot of make-work.

The original idea of the reg* types was to create an easy way to do
OID lookups in catalogs where the correct lookup rule is more
complicated than
    (SELECT oid FROM some_catalog WHERE name = 'foo')
So that motivates making reg* types for objects with
schema-qualified names, and even more so for functions and
types which have specialized syntax.  There was also some
consideration of which object types frequently need lookups.
IIRC, regrole got in partly because unprivileged users can't
select from pg_authid.

I don't really see that tablespaces meet the bar of any of these
past criteria: they don't have complex lookup rules nor are they
all that commonly used (IME anyway).  So if we accept this patch,
we're essentially saying that every catalog should have a reg*
type, and that's not a conclusion I want to reach.  We have 11
reg* types at the moment (only 9 if you discount the legacy
regproc and regoper ones), whereas there are about 30 catalogs
that have name columns.  Do we really want to open those
floodgates?

            regards, tom lane