Re: pg_tablespace_databases - Mailing list pgsql-patches

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