Re: pg_tablespace_databases - Mailing list pgsql-patches

Tom Lane wrote:
> [ shrug... ]  The name is not going to change again.  I have never cared
> for the practice of writing strlen("foo") as if it were a compile-time
> constant.  But certainly it would be entirely pointless to define such a
> macro and then use it in only one place.
>

Fair enough. If there are no objections, I'll apply the attached patch
in a few hours.

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 15:54:45 -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/commands/tablespace.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/commands/tablespace.c,v
retrieving revision 1.4
diff -c -r1.4 tablespace.c
*** src/backend/commands/tablespace.c    25 Jun 2004 21:55:53 -0000    1.4
--- src/backend/commands/tablespace.c    2 Jul 2004 15:54:46 -0000
***************
*** 315,321 ****
      /*
       * All seems well, create the symlink
       */
!     linkloc = (char *) palloc(strlen(DataDir) + 16 + 10 + 1);
      sprintf(linkloc, "%s/pg_tblspc/%u", DataDir, tablespaceoid);

      if (symlink(location, linkloc) < 0)
--- 315,321 ----
      /*
       * All seems well, create the symlink
       */
!     linkloc = (char *) palloc(strlen(DataDir) + 11 + 10 + 1);
      sprintf(linkloc, "%s/pg_tblspc/%u", DataDir, tablespaceoid);

      if (symlink(location, linkloc) < 0)
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 15:54:46 -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,204 ----
  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));
+
+         /*
+          * size = path length + tablespace dirname length
+          *        + 2 dir sep chars + oid + terminator
+          */
+         fctx->location = (char*) palloc(strlen(DataDir) + 11 + 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;
+
+         /* 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 15:54:46 -0000
***************
*** 53,58 ****
   */

  /*                            yyyymmddN */
! #define CATALOG_VERSION_NO    200406261

  #endif
--- 53,58 ----
   */

  /*                            yyyymmddN */
! #define CATALOG_VERSION_NO    200407021

  #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 15:54:46 -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 15:54:47 -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);