oid2name : add objects file path

Started by David Bidoc3 months ago8 messages

David Bidoc

dcbidoc@gmail.com

3 months ago

1 attachment(s)

Hello,

This is my first patch to the project.

I noticed that the oid2name tool does not display the file path of objects.

I thought this could be interesting and that others might find it useful,
so I made a little patch to display the full path of objects retrieved by
the oid2name tool. These will be displayed using the -x --extended option.

$ oid2name -p 5435 -x
All databases:
Oid Database Name Tablespace Filepath
----------------------------------------------
16392 b1 pg_default base/16392
5 postgres pg_default base/5
4 template0 pg_default base/4
1 template1 pg_default base/1

$ oid2name -p 5435 -d b1 -x
From database "b1":
Filenode Table Name Oid Schema Tablespace Filepath
-------------------------------------------------------------------
16393 t1 16393 public pg_default base/16392/16393

$ oid2name -p 5435 -d b1 -i -x
From database "b1":
Filenode Table Name Oid Schema Tablespace Filepath
-------------------------------------------------------------------
16393 t1 16393 public pg_default base/16392/16393
16396 t1_c1_idx 16396 public pg_default base/16392/16396

Regards

David Bidoc

Attachments:

oid2name_filepath.patchtext/x-patch; charset=US-ASCII; name=oid2name_filepath.patchDownload

diff --git a/contrib/oid2name/oid2name.c b/contrib/oid2name/oid2name.c
index 51802907138..4a985d579b6 100644
--- a/contrib/oid2name/oid2name.c
+++ b/contrib/oid2name/oid2name.c
@@ -452,12 +452,15 @@ void
 sql_exec_dumpalldbs(PGconn *conn, struct options *opts)
 {
 	char		todo[1024];
+	char       *addfields = ",'base/' ||d.oid as \"Filepath\"";
+
 
 	/* get the oid and database name from the system pg_database table */
 	snprintf(todo, sizeof(todo),
 			 "SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
-			 "spcname AS \"Tablespace\" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t ON "
-			 "(dattablespace = t.oid) ORDER BY 2");
+			 "spcname AS \"Tablespace\" %s FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t ON "
+			 "(dattablespace = t.oid) ORDER BY 2",
+			 opts->extended ? addfields : "");
 
 	sql_exec(conn, todo, opts->quiet);
 }
@@ -469,7 +472,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 +510,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>

Euler Taveira

euler@eulerto.com

3 months ago

In reply to: David Bidoc (#1)

Re: oid2name : add objects file path

On Tue, Oct 7, 2025, at 5:55 AM, David Bidoc wrote:

This is my first patch to the project.

Welcome to Postgres community.

I noticed that the oid2name tool does not display the file path of objects.

I thought this could be interesting and that others might find it
useful, so I made a little patch to display the full path of objects
retrieved by the oid2name tool. These will be displayed using the -x
--extended option.

I didn't review your patch in details but I noticed 2 things that should be
improved:

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.

2. I suggest that you change one of the examples (maybe the last one) to
illustrate this feature.

Since you are in this area, you could create a separate patch for show the
tablespace location (-s option). Use pg_tablespace_location function.

Register your patch for the next commitfest so we don't lose track of it. [1]https://commitfest.postgresql.org/56/
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/

--
Euler Taveira
EDB https://www.enterprisedb.com/

David Bidoc

dcbidoc@gmail.com

3 months ago

In reply to: Euler Taveira (#2)

2 attachment(s)

Re: oid2name : add objects file path

On Tue, Oct 7, 2025 at 11:47 AM Euler Taveira <euler@eulerto.com> 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

Attachments:

oid2name_filepathv2.patchtext/x-patch; charset=US-ASCII; name=oid2name_filepathv2.patchDownload

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>

oid2name_tblspclocation.patchtext/x-patch; charset=US-ASCII; name=oid2name_tblspclocation.patchDownload

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>

Guillaume Lelarge

guillaume.lelarge@dalibo.com

about 2 months ago

In reply to: David Bidoc (#3)

Re: oid2name : add objects file path

Hi David,

I just looked at the commit fest entry and the bot says your patch needs
a rebase. Can you do it? Thanks.

Regards.

On 08/10/2025 14:44, David Bidoc wrote:

On Tue, Oct 7, 2025 at 11:47 AM Euler Taveira <euler@eulerto.com> 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

--
Guillaume Lelarge
Consultant
https://dalibo.com

David Bidoc

dcbidoc@gmail.com

about 1 month ago

In reply to: Guillaume Lelarge (#4)

1 attachment(s)

Re: oid2name : add objects file path

Hi David,

Hi Guillaume,

I just looked at the commit fest entry and the bot says your patch needs
a rebase. Can you do it? Thanks.

I attached the rebased patch.
Any feedback would be appreciated.

Regards.

On Wed, Nov 26, 2025 at 9:02 PM Guillaume Lelarge
<guillaume.lelarge@dalibo.com> wrote:

Show quoted text

Hi David,

I just looked at the commit fest entry and the bot says your patch needs
a rebase. Can you do it? Thanks.

Regards.

On 08/10/2025 14:44, David Bidoc wrote:

On Tue, Oct 7, 2025 at 11:47 AM Euler Taveira <euler@eulerto.com> 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

--
Guillaume Lelarge
Consultant
https://dalibo.com

Attachments:

oid2name_filepathv3.patchapplication/octet-stream; name=oid2name_filepathv3.patchDownload

diff --git a/contrib/oid2name/oid2name.c b/contrib/oid2name/oid2name.c
index 51802907138..4a985d579b6 100644
--- a/contrib/oid2name/oid2name.c
+++ b/contrib/oid2name/oid2name.c
@@ -469,7 +472,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 +510,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>

Michael Paquier

michael@paquier.xyz

about 1 month ago

In reply to: David Bidoc (#5)

Re: oid2name : add objects file path

On Tue, Dec 02, 2025 at 11:36:39AM +0100, David Bidoc wrote:

I attached the rebased patch.
Any feedback would be appreciated.

Your patch still fails to apply on HEAD for all the changes of
contrib/oid2name/oid2name.c. Please see the following:
https://commitfest.postgresql.org/patch/6111/

It also seems like Euler's feedback has not been answered. I am
pretty sure that he was referring about the addition of an example in
the documentation, or at least refreshing the areas of the docs that
are impacted with your change (no clear idea about all that without a
rebased patch).
--
Michael

David Bidoc

dcbidoc@gmail.com

28 days ago

In reply to: Michael Paquier (#6)

1 attachment(s)

Re: oid2name : add objects file path

Hi Michael

On Thu, Dec 4, 2025 at 1:46 AM Michael Paquier <michael@paquier.xyz> wrote:

On Tue, Dec 02, 2025 at 11:36:39AM +0100, David Bidoc wrote:

I attached the rebased patch.
Any feedback would be appreciated.

Your patch still fails to apply on HEAD for all the changes of
contrib/oid2name/oid2name.c. Please see the following:
https://commitfest.postgresql.org/patch/6111/

Thank you for your feedback.
I attached the rebased and updated patch.

It also seems like Euler's feedback has not been answered. I am
pretty sure that he was referring about the addition of an example in
the documentation, or at least refreshing the areas of the docs that
are impacted with your change (no clear idea about all that without a
rebased patch).

I updated the documentation example about the -x option :

$ # you can mix the options, and get more details with -x
$ oid2name -d alvherre -t accounts -f 1155291 -x
From database "alvherre":
Filenode Table Name Oid Schema Tablespace Filepath
--------------------------------------------------------------------------
155173 accounts 155173 public pg_default base/17228/155173
1155291 accounts_pkey 1155291 public pg_default base/17228/1155291

I modified the example already used to maintain consistency.

--
David Bidoc

Attachments:

oid2name_filepathv4.patchtext/x-patch; charset=US-ASCII; name=oid2name_filepathv4.patchDownload

diff --git a/contrib/oid2name/oid2name.c b/contrib/oid2name/oid2name.c
index 51802907138..e0b1aa0d31a 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..7a595866425 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>
 
@@ -299,10 +299,10 @@ From database "alvherre":
 $ # you can mix the options, and get more details with -x
 $ oid2name -d alvherre -t accounts -f 1155291 -x
 From database "alvherre":
-  Filenode     Table Name      Oid  Schema  Tablespace
-------------------------------------------------------
-    155173       accounts   155173  public  pg_default
-   1155291  accounts_pkey  1155291  public  pg_default
+  Filenode     Table Name      Oid  Schema  Tablespace            Filepath
+--------------------------------------------------------------------------
+    155173       accounts   155173  public  pg_default   base/17228/155173
+   1155291  accounts_pkey  1155291  public  pg_default  base/17228/1155291
 
 $ # show disk space for every db object
 $ du [0-9]* |

Euler Taveira

euler@eulerto.com

27 days ago

In reply to: David Bidoc (#7)

Re: oid2name : add objects file path

On Mon, Dec 15, 2025, at 11:30 AM, David Bidoc wrote:

Thank you for your feedback.
I attached the rebased and updated patch.

This patch was in the previous CF, I'm wondering that's why the "Emails" are
not reflecting the latest patches and the CF bot was not trying it. I moved
this patch to the next CF (PG19-4). Hopefully, the CF entry will catch up in a
couple of hours.

https://commitfest.postgresql.org/patch/6111/

--
Euler Taveira
EDB https://www.enterprisedb.com/