Skip to content

Commit 0b89d26

Browse files
jconwayjconway
committed
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
1 parent 6d6c8b0 commit 0b89d26

File tree

6 files changed

+130
-8
lines changed

6 files changed

+130
-8
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.211 2004/06/25 17:20:21 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.212 2004/07/02 18:59:20 joe Exp $
33
PostgreSQL documentation
44
-->
55

@@ -7232,6 +7232,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
72327232
<primary>pg_get_serial_sequence</primary>
72337233
</indexterm>
72347234

7235+
<indexterm zone="functions-misc">
7236+
<primary>pg_tablespace_databases</primary>
7237+
</indexterm>
7238+
72357239
<para>
72367240
<xref linkend="functions-misc-catalog-table"> lists functions that
72377241
extract information from the system catalogs.
@@ -7325,6 +7329,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
73257329
<entry>get name of the sequence that a serial or bigserial column
73267330
uses</entry>
73277331
</row>
7332+
<row>
7333+
<entry><literal><function>pg_tablespace_databases</function>(<parameter>tablespace_oid</parameter>)</literal></entry>
7334+
<entry><type>setof oid</type></entry>
7335+
<entry>get set of database oids that have objects in the tablespace</entry>
7336+
</row>
73287337
</tbody>
73297338
</tgroup>
73307339
</table>
@@ -7362,6 +7371,16 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
73627371
NULL is returned if the column does not have a sequence attached.
73637372
</para>
73647373

7374+
<para>
7375+
<function>pg_tablespace_databases</function> allows usage examination of a
7376+
tablespace. It will return a set of database oids, that have objects
7377+
stored in the tablespace. If this function returns any row, the
7378+
tablespace is assumed not to be empty and cannot be dropped. To
7379+
display the actual objects populating the tablespace, you will need
7380+
to connect to the databases returned by
7381+
<function>pg_tablespace_databases</function> to query pg_class.
7382+
</para>
7383+
73657384
<indexterm zone="functions-misc">
73667385
<primary>obj_description</primary>
73677386
</indexterm>

‎src/backend/commands/tablespace.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,7 @@
4545
*
4646
*
4747
* IDENTIFICATION
48-
* $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.4 2004/06/25 21:55:53 tgl Exp $
48+
* $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.5 2004/07/02 18:59:22 joe Exp $
4949
*
5050
*-------------------------------------------------------------------------
5151
*/
@@ -315,7 +315,7 @@ CreateTableSpace(CreateTableSpaceStmt *stmt)
315315
/*
316316
* All seems well, create the symlink
317317
*/
318-
linkloc = (char *) palloc(strlen(DataDir) + 16 + 10 + 1);
318+
linkloc = (char *) palloc(strlen(DataDir) + 11 + 10 + 1);
319319
sprintf(linkloc, "%s/pg_tblspc/%u", DataDir, tablespaceoid);
320320

321321
if (symlink(location, linkloc) < 0)

‎src/backend/utils/adt/misc.c

Lines changed: 100 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,19 +8,24 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/misc.c,v 1.34 2004/06/02 21:29:29 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/misc.c,v 1.35 2004/07/02 18:59:22 joe Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
1515
#include "postgres.h"
1616

1717
#include <sys/file.h>
1818
#include <signal.h>
19+
#include <dirent.h>
1920

2021
#include "commands/dbcommands.h"
2122
#include "miscadmin.h"
2223
#include "storage/sinval.h"
24+
#include "storage/fd.h"
2325
#include "utils/builtins.h"
26+
#include "funcapi.h"
27+
#include "catalog/pg_type.h"
28+
#include "catalog/pg_tablespace.h"
2429

2530

2631
/*
@@ -103,3 +108,97 @@ pg_cancel_backend(PG_FUNCTION_ARGS)
103108
{
104109
PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0),SIGINT));
105110
}
111+
112+
113+
typedef struct
114+
{
115+
char *location;
116+
DIR *dirdesc;
117+
} ts_db_fctx;
118+
119+
Datum pg_tablespace_databases(PG_FUNCTION_ARGS)
120+
{
121+
FuncCallContext *funcctx;
122+
struct dirent *de;
123+
ts_db_fctx *fctx;
124+
125+
if (SRF_IS_FIRSTCALL())
126+
{
127+
MemoryContext oldcontext;
128+
Oid tablespaceOid=PG_GETARG_OID(0);
129+
130+
funcctx=SRF_FIRSTCALL_INIT();
131+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
132+
133+
fctx = palloc(sizeof(ts_db_fctx));
134+
135+
/*
136+
* size = path length + tablespace dirname length
137+
* + 2 dir sep chars + oid + terminator
138+
*/
139+
fctx->location = (char*) palloc(strlen(DataDir) + 11 + 10 + 1);
140+
if (tablespaceOid == GLOBALTABLESPACE_OID)
141+
{
142+
fctx->dirdesc = NULL;
143+
ereport(NOTICE,
144+
(errcode(ERRCODE_WARNING),
145+
errmsg("global tablespace never has databases.")));
146+
}
147+
else
148+
{
149+
if (tablespaceOid == DEFAULTTABLESPACE_OID)
150+
sprintf(fctx->location, "%s/base", DataDir);
151+
else
152+
sprintf(fctx->location, "%s/pg_tblspc/%u", DataDir,
153+
tablespaceOid);
154+
155+
fctx->dirdesc = AllocateDir(fctx->location);
156+
157+
if (!fctx->dirdesc) /* not a tablespace */
158+
ereport(NOTICE,
159+
(errcode(ERRCODE_WARNING),
160+
errmsg("%d is no tablespace oid.", tablespaceOid)));
161+
}
162+
funcctx->user_fctx = fctx;
163+
MemoryContextSwitchTo(oldcontext);
164+
}
165+
166+
funcctx=SRF_PERCALL_SETUP();
167+
fctx = (ts_db_fctx*) funcctx->user_fctx;
168+
169+
if (!fctx->dirdesc) /* not a tablespace */
170+
SRF_RETURN_DONE(funcctx);
171+
172+
while ((de = readdir(fctx->dirdesc)) != NULL)
173+
{
174+
char *subdir;
175+
DIR *dirdesc;
176+
177+
Oid datOid = atol(de->d_name);
178+
if (!datOid)
179+
continue;
180+
181+
/* size = path length + dir sep char + file name + terminator */
182+
subdir = palloc(strlen(fctx->location) + 1 + strlen(de->d_name) + 1);
183+
sprintf(subdir, "%s/%s", fctx->location, de->d_name);
184+
dirdesc = AllocateDir(subdir);
185+
if (dirdesc)
186+
{
187+
while ((de = readdir(dirdesc)) != 0)
188+
{
189+
if (strcmp(de->d_name, ".") && strcmp(de->d_name, ".."))
190+
break;
191+
}
192+
pfree(subdir);
193+
FreeDir(dirdesc);
194+
195+
if (!de) /* database subdir is empty; don't report tablespace as used */
196+
continue;
197+
}
198+
199+
SRF_RETURN_NEXT(funcctx, ObjectIdGetDatum(datOid));
200+
}
201+
202+
FreeDir(fctx->dirdesc);
203+
SRF_RETURN_DONE(funcctx);
204+
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.241 2004/07/01 00:51:39 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.242 2004/07/02 18:59:24 joe Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200406261
56+
#define CATALOG_VERSION_NO 200407021
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.339 2004/06/25 17:20:28 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.340 2004/07/02 18:59:24 joe Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -3595,6 +3595,9 @@ DESCR("bitwise-and bit aggregate");
35953595
DATA(insert OID = 2243 ( bit_or PGNSP PGUID 12 t f f f i 1 1560 "1560" _null_ aggregate_dummy - _null_));
35963596
DESCR("bitwise-or bit aggregate");
35973597

3598+
DATA(insert OID = 2554( pg_tablespace_databases PGNSP PGUID 12 f f t t s 1 26 "26" _null_ pg_tablespace_databases - _null_));
3599+
DESCR("returns database oids in a tablespace");
3600+
35983601
/*
35993602
* Symbolic values for provolatile column: these indicate whether the result
36003603
* of a function is dependent *only* on the values of its explicit arguments,

‎src/include/utils/builtins.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.244 2004/06/25 17:20:29 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.245 2004/07/02 18:59:25 joe Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -356,6 +356,7 @@ extern Datum nonnullvalue(PG_FUNCTION_ARGS);
356356
extern Datum current_database(PG_FUNCTION_ARGS);
357357
extern Datum pg_terminate_backend(PG_FUNCTION_ARGS);
358358
extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
359+
extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
359360

360361
/* not_in.c */
361362
extern Datum int4notin(PG_FUNCTION_ARGS);

0 commit comments

Comments
 (0)