Thread: pg_tablespace_databases
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. Regards, Andreas
> -----Original Message----- > From: pgsql-patches-owner@postgresql.org > [mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Andreas Pflug > Sent: 28 June 2004 12:25 > To: PostgreSQL Patches > Subject: [PATCHES] pg_tablespace_databases > > 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. Think you forgot the patch... :-) /D
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);
Joe Conway wrote:
>
> If there are no objections, I'll review and apply this tonight (west
> coast USA time).
>
> Andreas, please provide a corresponding documentation patch.
>
Here we are.
Since I don't have a SGML build environment, syntax is not checked.
Regards,
Andreas
Index: func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.211
diff -u -r1.211 func.sgml
--- func.sgml 25 Jun 2004 17:20:21 -0000 1.211
+++ func.sgml 1 Jul 2004 18:38:34 -0000
@@ -7232,6 +7232,10 @@
<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 @@
<entry>get name of the sequence that a serial or bigserial column
uses</entry>
</row>
+ <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>
</tbody>
</tgroup>
</table>
@@ -7360,6 +7369,16 @@
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">
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. If there are no objections, I'll review and apply this tonight (west coast USA time). Andreas, please provide a corresponding documentation patch. Thanks, Joe
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);
Joe Conway wrote: > > 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> Of course. I just call everything namespace :-) > 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); This size calculation originated (copy/paste) from commands/tablespace.c, should be clarified there too (and "pg_tblspc" is hardcoded in strings, could be extracted to a macro definition). Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Joe Conway wrote:
>> 2) This allocation size was a bit ambigous and I think based on a once
>> longer tablespace directory name:
> This size calculation originated (copy/paste) from
> commands/tablespace.c,
Yeah --- Bruce did not adjust the string length calculations when he
editorialized on the directory name. I'd been meaning to go back and
make them match.
> should be clarified there too (and "pg_tblspc" is
> hardcoded in strings, could be extracted to a macro definition).
[ 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.
regards, tom lane
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);
Joe Conway wrote:
> 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.
patch applied.
Joe
Tom Lane wrote:
> Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > Joe Conway wrote:
> >> 2) This allocation size was a bit ambigous and I think based on a once
> >> longer tablespace directory name:
>
> > This size calculation originated (copy/paste) from
> > commands/tablespace.c,
>
> Yeah --- Bruce did not adjust the string length calculations when he
> editorialized on the directory name. I'd been meaning to go back and
> make them match.
>
> > should be clarified there too (and "pg_tblspc" is
> > hardcoded in strings, could be extracted to a macro definition).
>
> [ 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.
I think with gcc strlen("foo") is a compile-time constant. At least I
remember that as a gcc optimization. What do you prefer?
sizeof("foo")-1? Certainly +3 is poorly documented, no?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, 6 Jul 2004, Bruce Momjian wrote:
> Tom Lane wrote:
> > Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > > Joe Conway wrote:
> > >> 2) This allocation size was a bit ambigous and I think based on a once
> > >> longer tablespace directory name:
> >
> > > This size calculation originated (copy/paste) from
> > > commands/tablespace.c,
> >
> > Yeah --- Bruce did not adjust the string length calculations when he
> > editorialized on the directory name. I'd been meaning to go back and
> > make them match.
> >
> > > should be clarified there too (and "pg_tblspc" is
> > > hardcoded in strings, could be extracted to a macro definition).
> >
> > [ 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.
>
> I think with gcc strlen("foo") is a compile-time constant. At least I
> remember that as a gcc optimization. What do you prefer?
> sizeof("foo")-1? Certainly +3 is poorly documented, no?
You're right about the gcc optimisation:
int i = strlen("foo");
8048304: c7 45 fc 03 00 00 00 movl $0x3,0xfffffffc(%ebp)
It does look messy thought. Can't this be cleared by a comment?
Thanks,
Gavin
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 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.
> I think with gcc strlen("foo") is a compile-time constant.
Portability is exactly the root of the problem. If you are in the habit
of doing this then you get led into unportable behaviors like
char localarray[strlen(foo) + 1];
which no compiler except gcc will take. (We just had to fix exactly
that mistake in someone's patch within the last week or two.)
> What do you prefer?
I use "3" ;-). As long as the size calculation and the filling of the
string are immediately adjacent, the purpose of the code is clear
enough.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > 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.
>
> > I think with gcc strlen("foo") is a compile-time constant.
>
> Portability is exactly the root of the problem. If you are in the habit
> of doing this then you get led into unportable behaviors like
> char localarray[strlen(foo) + 1];
> which no compiler except gcc will take. (We just had to fix exactly
> that mistake in someone's patch within the last week or two.)
One idea would be to create a CONST_STRLEN macro that uses sizeof()-1.
> > What do you prefer?
>
> I use "3" ;-). As long as the size calculation and the filling of the
> string are immediately adjacent, the purpose of the code is clear
> enough.
If it is on the same line, yea, it is clear, but often the size refers
to something declared several lines away.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073