This requires the previously added RELFILENODE syscache.
---
doc/src/sgml/func.sgml | 23 ++++++++++++-
src/backend/utils/adt/dbsize.c | 78 ++++++++++++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 2 ++
src/include/utils/builtins.h | 1 +
4 files changed, 103 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f8f63d8..708da35 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15170,7 +15170,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
<para>
The functions shown in <xref linkend="functions-admin-dblocation"> assist
- in identifying the specific disk files associated with database objects.
+ in identifying the specific disk files associated with database objects or doing the reverse.
</para>
<indexterm>
@@ -15179,6 +15179,9 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
<indexterm>
<primary>pg_relation_filepath</primary>
</indexterm>
+ <indexterm>
+ <primary>pg_relation_by_filenode</primary>
+ </indexterm>
<table id="functions-admin-dblocation">
<title>Database Object Location Functions</title>
@@ -15207,6 +15210,15 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
File path name of the specified relation
</entry>
</row>
+ <row>
+ <entry>
+ <literal><function>pg_relation_by_filenode(<parameter>tablespace</parameter> <type>oid</type>, <parameter>filenode</parameter> <type>oid</type>)</function></literal>
+ </entry>
+ <entry><type>regclass</type></entry>
+ <entry>
+ Find the associated relation of a filenode
+ </entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -15230,6 +15242,15 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
the relation.
</para>
+ <para>
+ <function>pg_relation_by_filenode</> is the reverse of
+ <function>pg_relation_filenode</>. Given a <quote>tablespace</> OID and
+ a <quote>filenode</> it returns the associated relation. The default
+ tablespace for user tables can be replaced with 0. Check the
+ documentation of <function>pg_relation_filenode</> for an explanation why
+ this cannot always easily answered by querying <structname>pg_class</>.
+ </para>
+
</sect2>
<sect2 id="functions-admin-genfile">
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index cd23334..841a445 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -741,6 +741,84 @@ pg_relation_filenode(PG_FUNCTION_ARGS)
}
/*
+ * Get the relation via (reltablespace, relfilenode)
+ *
+ * This is expected to be used when somebody wants to match an individual file
+ * on the filesystem back to its table. Thats not trivially possible via
+ * pg_class because that doesn't contain the relfilenodes of shared and nailed
+ * tables.
+ *
+ * We don't fail but return NULL if we cannot find a mapping.
+ *
+ * Instead of knowing DEFAULTTABLESPACE_OID you can pass 0.
+ */
+Datum
+pg_relation_by_filenode(PG_FUNCTION_ARGS)
+{
+ Oid reltablespace = PG_GETARG_OID(0);
+ Oid relfilenode = PG_GETARG_OID(1);
+ Oid lookup_tablespace = reltablespace;
+ Oid result = InvalidOid;
+ HeapTuple tuple;
+
+ if (reltablespace == 0)
+ reltablespace = DEFAULTTABLESPACE_OID;
+
+ /* pg_class stores 0 instead of DEFAULTTABLESPACE_OID */
+ if (reltablespace == DEFAULTTABLESPACE_OID)
+ lookup_tablespace = 0;
+
+ tuple = SearchSysCache2(RELFILENODE,
+ lookup_tablespace,
+ relfilenode);
+
+ /* found it in the system catalog, not be a shared/nailed table */
+ if (HeapTupleIsValid(tuple))
+ {
+ result = HeapTupleHeaderGetOid(tuple->t_data);
+ ReleaseSysCache(tuple);
+ }
+ else
+ {
+ if (reltablespace == GLOBALTABLESPACE_OID)
+ {
+ result = RelationMapFilenodeToOid(relfilenode, true);
+ }
+ else
+ {
+ Form_pg_class relform;
+
+ result = RelationMapFilenodeToOid(relfilenode, false);
+
+ if (result != InvalidOid)
+ {
+ /* check that we found the correct relation */
+ tuple = SearchSysCache1(RELOID,
+ result);
+
+ if (!HeapTupleIsValid(tuple))
+ {
+ elog(ERROR, "Couldn't refind previously looked up relation with oid %u",
+ result);
+ }
+
+ relform = (Form_pg_class) GETSTRUCT(tuple);
+
+ if (relform->reltablespace != reltablespace)
+ result = InvalidOid;
+
+ ReleaseSysCache(tuple);
+ }
+ }
+ }
+
+ if (!OidIsValid(result))
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_OID(result);
+}
+
+/*
* Get the pathname (relative to $PGDATA) of a relation
*
* See comments for pg_relation_filenode.
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index b5b886b..c8233cd 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3430,6 +3430,8 @@ DATA(insert OID = 2998 ( pg_indexes_size PGNSP PGUID 12 1 0 0 0 f f f f t f v 1
DESCR("disk space usage for all indexes attached to the specified table");
DATA(insert OID = 2999 ( pg_relation_filenode PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 26 "2205" _null_ _null_ _null_ _null_ pg_relation_filenode _null_ _null_ _null_ ));
DESCR("filenode identifier of relation");
+DATA(insert OID = 3170 ( pg_relation_by_filenode PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 2205 "26 26" _null_ _null_ _null_ _null_ pg_relation_by_filenode _null_ _null_ _null_ ));
+DESCR("filenode identifier of relation");
DATA(insert OID = 3034 ( pg_relation_filepath PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 25 "2205" _null_ _null_ _null_ _null_ pg_relation_filepath _null_ _null_ _null_ ));
DESCR("file path of relation");
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index c9c665d..8ee4c3c 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -458,6 +458,7 @@ extern Datum pg_table_size(PG_FUNCTION_ARGS);
extern Datum pg_indexes_size(PG_FUNCTION_ARGS);
extern Datum pg_relation_filenode(PG_FUNCTION_ARGS);
extern Datum pg_relation_filepath(PG_FUNCTION_ARGS);
+extern Datum pg_relation_by_filenode(PG_FUNCTION_ARGS);
/* genfile.c */
extern bytea *read_binary_file(const char *filename,
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
