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.
It might be easier to review if I attach the file...
Regards,
Andreas
Index: src/backend/utils/adt/misc.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/misc.c,v
retrieving revision 1.34
diff -u -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 28 Jun 2004 11:16:05 -0000
@@ -16,11 +16,16 @@
#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,4 +107,92 @@
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;
+
+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));
+
+ fctx->location = (char*)palloc(strlen(DataDir)+16+10+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/pg_tblspc/%u", DataDir,
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;
+
+ 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/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.339
diff -u -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 28 Jun 2004 11:16:32 -0000
@@ -3595,6 +3595,9 @@
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: /projects/cvsroot/pgsql-server/src/include/utils/builtins.h,v
retrieving revision 1.244
diff -u -r1.244 builtins.h
--- src/include/utils/builtins.h 25 Jun 2004 17:20:29 -0000 1.244
+++ src/include/utils/builtins.h 28 Jun 2004 11:16:35 -0000
@@ -356,6 +356,7 @@
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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
