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.
>

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);