Re: pg_tablespace_databases

Andreas Pflug wrote:
>> From an idea of Bruce, the attached patch implements the function
>> pg_tablespace_databases(oid) RETURNS SETOF oid
>>
>> which delivers as set of database oids having objects in the selected
>> tablespace, enabling an admin to examine only the databases affecting
>> the tablespace for objects instead of scanning all of them.

Attached is the patch I plan to apply. There are a couple of changes
from what was posted.

1) You must have meant tablespace instead of namespace here:
------------------------------------------------------------
+ <row>
+
<entry><literal><function>pg_tablespace_databases</function>(<parameter>namespace_oid</parameter>)</literal></entry>
+ <entry><type>setof oid</type></entry>
+ <entry>get set of database oids that have objects in the
namespace</entry>
+ </row>

2) This allocation size was a bit ambigous and I think based on a once
longer tablespace directory name:
------------------------------------------------------------
+ fctx->location = (char*)palloc(strlen(DataDir)+16+10+1);

I take it that is (path len + '/' + strlen("pg_tablespaces") + '/' + oid
string length + terminator). I did this instead:

+ #define PG_TABLESPACE_DIR "pg_tblspc"
+ /* assumes unsigned, 10 digits */
+ #define OID_AS_STR 10

+ /*
+ * size = path length + tablespace dirname length
+ * + 2 dir sep chars + oid + terminator
+ */
+ fctx->location = (char*) palloc(strlen(DataDir)
+ + strlen(PG_TABLESPACE_DIR)
+ + 2 + OID_AS_STR + 1);

Usage looks like this:
regression=# select d.datname from pg_tablespace_databases(1663) as
t(oid) join pg_database d on t.oid = d.oid order by 1;
datname
------------
regression
template0
template1
(3 rows)

initdb forced.

Any objections?

Joe