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
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.211
diff -c -r1.211 func.sgml
*** doc/src/sgml/func.sgml 25 Jun 2004 17:20:21 -0000 1.211
--- doc/src/sgml/func.sgml 2 Jul 2004 05:12:56 -0000
***************
*** 7232,7237 ****
--- 7232,7241 ----
<primary>pg_get_serial_sequence</primary>
</indexterm>
+ <indexterm zone="functions-misc">
+ <primary>pg_tablespace_databases</primary>
+ </indexterm>
+
<para>
<xref linkend="functions-misc-catalog-table"> lists functions that
extract information from the system catalogs.
***************
*** 7325,7330 ****
--- 7329,7339 ----
<entry>get name of the sequence that a serial or bigserial column
uses</entry>
</row>
+ <row>
+ <entry><literal><function>pg_tablespace_databases</function>(<parameter>tablespace_oid</parameter>)</literal></entry>
+ <entry><type>setof oid</type></entry>
+ <entry>get set of database oids that have objects in the tablespace</entry>
+ </row>
</tbody>
</tgroup>
</table>
***************
*** 7360,7365 ****
--- 7369,7384 ----
for passing to the sequence functions (see <xref
linkend="functions-sequence">).
NULL is returned if the column does not have a sequence attached.
+ </para>
+
+ <para>
+ <function>pg_tablespace_databases</function> allows usage examination of a
+ tablespace. It will return a set of database oids, that have objects
+ stored in the tablespace. If this function returns any row, the
+ tablespace is assumed not to be empty and cannot be dropped. To
+ display the actual objects populating the tablespace, you will need
+ to connect to the databases returned by
+ <function>pg_tablespace_databases</function> to query pg_class.
</para>
<indexterm zone="functions-misc">
Index: src/backend/utils/adt/misc.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/misc.c,v
retrieving revision 1.34
diff -c -r1.34 misc.c
*** src/backend/utils/adt/misc.c 2 Jun 2004 21:29:29 -0000 1.34
--- src/backend/utils/adt/misc.c 2 Jul 2004 05:12:56 -0000
***************
*** 16,26 ****
--- 16,31 ----
#include <sys/file.h>
#include <signal.h>
+ #include <dirent.h>
#include "commands/dbcommands.h"
#include "miscadmin.h"
#include "storage/sinval.h"
+ #include "storage/fd.h"
#include "utils/builtins.h"
+ #include "funcapi.h"
+ #include "catalog/pg_type.h"
+ #include "catalog/pg_tablespace.h"
/*
***************
*** 102,105 ****
--- 107,210 ----
pg_cancel_backend(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0),SIGINT));
+ }
+
+
+ typedef struct
+ {
+ char *location;
+ DIR *dirdesc;
+ } ts_db_fctx;
+ #define PG_TABLESPACE_DIR "pg_tblspc"
+ /* assumes unsigned, 10 digits */
+ #define OID_AS_STR 10
+
+ Datum pg_tablespace_databases(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funcctx;
+ struct dirent *de;
+ ts_db_fctx *fctx;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid tablespaceOid=PG_GETARG_OID(0);
+
+ funcctx=SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ fctx = palloc(sizeof(ts_db_fctx));
+
+ /*
+ * 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);
+ if (tablespaceOid == GLOBALTABLESPACE_OID)
+ {
+ fctx->dirdesc = NULL;
+ ereport(NOTICE,
+ (errcode(ERRCODE_WARNING),
+ errmsg("global tablespace never has databases.")));
+ }
+ else
+ {
+ if (tablespaceOid == DEFAULTTABLESPACE_OID)
+ sprintf(fctx->location, "%s/base", DataDir);
+ else
+ sprintf(fctx->location, "%s/%s/%u", DataDir,
+ PG_TABLESPACE_DIR,
+ tablespaceOid);
+
+ fctx->dirdesc = AllocateDir(fctx->location);
+
+ if (!fctx->dirdesc) /* not a tablespace */
+ ereport(NOTICE,
+ (errcode(ERRCODE_WARNING),
+ errmsg("%d is no tablespace oid.", tablespaceOid)));
+ }
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx=SRF_PERCALL_SETUP();
+ fctx = (ts_db_fctx*) funcctx->user_fctx;
+
+ if (!fctx->dirdesc) /* not a tablespace */
+ SRF_RETURN_DONE(funcctx);
+
+ while ((de = readdir(fctx->dirdesc)) != NULL)
+ {
+ char *subdir;
+ DIR *dirdesc;
+
+ Oid datOid = atol(de->d_name);
+ if (!datOid)
+ continue;
+
+ /* size = path length + dir sep char + file name + terminator */
+ subdir = palloc(strlen(fctx->location) + 1 + strlen(de->d_name) + 1);
+ sprintf(subdir, "%s/%s", fctx->location, de->d_name);
+ dirdesc = AllocateDir(subdir);
+ if (dirdesc)
+ {
+ while ((de = readdir(dirdesc)) != 0)
+ {
+ if (strcmp(de->d_name, ".") && strcmp(de->d_name, ".."))
+ break;
+ }
+ pfree(subdir);
+ FreeDir(dirdesc);
+
+ if (!de) /* database subdir is empty; don't report tablespace as used */
+ continue;
+ }
+
+ SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(datOid));
+ }
+
+ FreeDir(fctx->dirdesc);
+ SRF_RETURN_DONE(funcctx);
}
Index: src/include/catalog/catversion.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/catalog/catversion.h,v
retrieving revision 1.241
diff -c -r1.241 catversion.h
*** src/include/catalog/catversion.h 1 Jul 2004 00:51:39 -0000 1.241
--- src/include/catalog/catversion.h 2 Jul 2004 05:12:56 -0000
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200406261
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200407011
#endif
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.339
diff -c -r1.339 pg_proc.h
*** src/include/catalog/pg_proc.h 25 Jun 2004 17:20:28 -0000 1.339
--- src/include/catalog/pg_proc.h 2 Jul 2004 05:12:57 -0000
***************
*** 3595,3600 ****
--- 3595,3603 ----
DATA(insert OID = 2243 ( bit_or PGNSP PGUID 12 t f f f i 1 1560 "1560" _null_ aggregate_dummy - _null_));
DESCR("bitwise-or bit aggregate");
+ DATA(insert OID = 2554( pg_tablespace_databases PGNSP PGUID 12 f f t t s 1 26 "26" _null_ pg_tablespace_databases - _null_));
+ DESCR("returns database oids in a tablespace");
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
Index: src/include/utils/builtins.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.244
diff -c -r1.244 builtins.h
*** src/include/utils/builtins.h 25 Jun 2004 17:20:29 -0000 1.244
--- src/include/utils/builtins.h 2 Jul 2004 05:12:57 -0000
***************
*** 356,361 ****
--- 356,362 ----
extern Datum current_database(PG_FUNCTION_ARGS);
extern Datum pg_terminate_backend(PG_FUNCTION_ARGS);
extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
+ extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
/* not_in.c */
extern Datum int4notin(PG_FUNCTION_ARGS);
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
