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.

initdb forced

6 files changed, 130 insertions, 8 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3c23f43a59f..8a624614e8d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.211 2004/06/25 17:20:21 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.212 2004/07/02 18:59:20 joe Exp $
 PostgreSQL documentation
 -->
 
@@ -7232,6 +7232,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
     <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,6 +7329,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
        <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>
@@ -7362,6 +7371,16 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
    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">
     <primary>obj_description</primary>
    </indexterm>
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c
index 847985456f7..875fb1cae8a 100644
--- a/src/backend/commands/tablespace.c
+++ b/src/backend/commands/tablespace.c
@@ -45,7 +45,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.4 2004/06/25 21:55:53 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.5 2004/07/02 18:59:22 joe Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -315,7 +315,7 @@ CreateTableSpace(CreateTableSpaceStmt *stmt)
 	/*
 	 * All seems well, create the symlink
 	 */
-	linkloc = (char *) palloc(strlen(DataDir) + 16 + 10 + 1);
+	linkloc = (char *) palloc(strlen(DataDir) + 11 + 10 + 1);
 	sprintf(linkloc, "%s/pg_tblspc/%u", DataDir, tablespaceoid);
 
 	if (symlink(location, linkloc) < 0)
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 8ab6953cb02..0cc315c6205 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/misc.c,v 1.34 2004/06/02 21:29:29 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/misc.c,v 1.35 2004/07/02 18:59:22 joe Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -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"
 
 
 /*
@@ -103,3 +108,97 @@ 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);
+}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index b6f98778628..98e554e7599 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.241 2004/07/01 00:51:39 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.242 2004/07/02 18:59:24 joe Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200406261
+#define CATALOG_VERSION_NO	200407021
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 489fa9ca9ba..8d2485c168d 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.339 2004/06/25 17:20:28 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.340 2004/07/02 18:59:24 joe Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -3595,6 +3595,9 @@ DESCR("bitwise-and bit aggregate");
 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,
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 99974cdec12..c18ad551e48 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.244 2004/06/25 17:20:29 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.245 2004/07/02 18:59:25 joe Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -356,6 +356,7 @@ extern Datum nonnullvalue(PG_FUNCTION_ARGS);
 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);