On Tue, Oct 7, 2025 at 11:47 AM Euler Taveira <[email protected]> wrote:
> 1. The database query is wrong because it is considering that all databases
> are
> in the default tablespace. If you create a database in a different tablespace
> you will notice the mistake.
Thank you for your feedback.
Indeed, the path is wrong if the default tablespace is not used.
I did not find a simple way to retrieve the database path (to my
knowledge there is
no function like pg_database_location() or something similar), so I have removed
this part from the patch for now.
>
> 2. I suggest that you change one of the examples (maybe the last one) to
> illustrate this feature.
Here is a new example by adding a table in a different tablespace :
$ oid2name -p 5435 -d b1 -t t2 -x
>From database "b1":
Filenode Table Name Oid Schema Tablespace
Filepath
----------------------------------------------------------------------------------------------
16403 t2 16403 public tblspc1
pg_tblspc/16393/PG_19_202510082/16384/16403
> Since you are in this area, you could create a separate patch for show the
> tablespace location (-s option). Use pg_tablespace_location function.
>
I attached a new patch to add a column Tablespace Location to the -s option.
$ oid2name -p 5435 -s
All tablespaces:
Oid Tablespace Name Tablespace Location
---------------------------------------------
1663 pg_default
1664 pg_global
16393 tblspc1 /mnt/tblspc1/pg
> Register your patch for the next commitfest so we don't lose track of it. [1]
> If you don't have an account, the 'Log In' link will redirect you to the page
> to create a new one or even allow you to use a third party sign in.
>
> [1] https://commitfest.postgresql.org/56/
>
Done.
https://commitfest.postgresql.org/patch/6111/
Regards
David Bidoc
diff --git a/contrib/oid2name/oid2name.c b/contrib/oid2name/oid2name.c
index 51802907138..15a9d5e5308 100644
--- a/contrib/oid2name/oid2name.c
+++ b/contrib/oid2name/oid2name.c
@@ -469,7 +469,7 @@ void
sql_exec_dumpalltables(PGconn *conn, struct options *opts)
{
char todo[1024];
- char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
+ char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\", pg_relation_filepath(c.oid) as \"Filepath\"";
snprintf(todo, sizeof(todo),
"SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s "
@@ -507,7 +507,7 @@ sql_exec_searchtables(PGconn *conn, struct options *opts)
*comma_filenumbers,
*comma_tables;
bool written = false;
- char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
+ char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\", pg_relation_filepath(c.oid) as \"Filepath\" ";
/* get tables qualifiers, whether names, filenumbers, or OIDs */
comma_oids = get_comma_elts(opts->oids);
diff --git a/doc/src/sgml/oid2name.sgml b/doc/src/sgml/oid2name.sgml
index 54cc9be2b82..411950ea0e5 100644
--- a/doc/src/sgml/oid2name.sgml
+++ b/doc/src/sgml/oid2name.sgml
@@ -118,7 +118,7 @@
<term><option>-x</option></term>
<term><option>--extended</option></term>
<listitem><para>display more information about each object shown: tablespace name,
- schema name, and OID.
+ schema name, OID and file path.
</para></listitem>
</varlistentry>
diff --git a/contrib/oid2name/oid2name.c b/contrib/oid2name/oid2name.c
index 51802907138..c7fdc3234b9 100644
--- a/contrib/oid2name/oid2name.c
+++ b/contrib/oid2name/oid2name.c
@@ -573,7 +573,7 @@ sql_exec_dumpalltbspc(PGconn *conn, struct options *opts)
char todo[1024];
snprintf(todo, sizeof(todo),
- "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
+ "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\", pg_catalog.pg_tablespace_location(oid) AS \"Tablespace Location\"\n"
"FROM pg_catalog.pg_tablespace");
sql_exec(conn, todo, opts->quiet);
diff --git a/doc/src/sgml/oid2name.sgml b/doc/src/sgml/oid2name.sgml
index 54cc9be2b82..63704ee258c 100644
--- a/doc/src/sgml/oid2name.sgml
+++ b/doc/src/sgml/oid2name.sgml
@@ -86,7 +86,7 @@
<varlistentry>
<term><option>-s</option></term>
<term><option>--tablespaces</option></term>
- <listitem><para>show tablespace OIDs.</para></listitem>
+ <listitem><para>show tablespace OIDs and tablespaces paths.</para></listitem>
</varlistentry>
<varlistentry>
