Re: [PATCHES] pg_tablespace_databases
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> 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 > charlocalarray[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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pg_tablespace_databases
Bruce Momjian <[EMAIL PROTECTED]> 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 charlocalarray[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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] pg_tablespace_databases
On Tue, 6 Jul 2004, Bruce Momjian wrote: > Tom Lane wrote: > > Andreas Pflug <[EMAIL PROTECTED]> 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 00movl $0x3,0xfffc(%ebp) It does look messy thought. Can't this be cleared by a comment? Thanks, Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] pg_tablespace_databases
Tom Lane wrote: > Andreas Pflug <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] pg_tablespace_databases
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] pg_tablespace_databases
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 - 1.211 --- doc/src/sgml/func.sgml 2 Jul 2004 15:54:45 - *** *** 7232,7237 --- 7232,7241 pg_get_serial_sequence + + pg_tablespace_databases + + lists functions that extract information from the system catalogs. *** *** 7325,7330 --- 7329,7339 get name of the sequence that a serial or bigserial column uses + +pg_tablespace_databases(tablespace_oid) +setof oid +get set of database oids that have objects in the tablespace + *** *** 7360,7365 --- 7369,7384 for passing to the sequence functions (see ). NULL is returned if the column does not have a sequence attached. + + + + pg_tablespace_databases 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 + pg_tablespace_databases to query pg_class. 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 - 1.4 --- src/backend/commands/tablespace.c 2 Jul 2004 15:54:46 - *** *** 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 - 1.34 --- src/backend/utils/adt/misc.c 2 Jul 2004 15:54:46 - *** *** 16,26 --- 16,31 #include #include + #include #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
Re: [PATCHES] pg_tablespace_databases
Andreas Pflug <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] pg_tablespace_databases
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: + + pg_tablespace_databases(namespace_oid) + setof oid 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] pg_tablespace_databases
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: + + pg_tablespace_databases(namespace_oid) + setof oid + get set of database oids that have objects in the 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); 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_STR10 + /* +* 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 - 1.211 --- doc/src/sgml/func.sgml 2 Jul 2004 05:12:56 - *** *** 7232,7237 --- 7232,7241 pg_get_serial_sequence + + pg_tablespace_databases + + lists functions that extract information from the system catalogs. *** *** 7325,7330 --- 7329,7339 get name of the sequence that a serial or bigserial column uses + +pg_tablespace_databases(tablespace_oid) +setof oid +get set of database oids that have objects in the tablespace + *** *** 7360,7365 --- 7369,7384 for passing to the sequence functions (see ). NULL is returned if the column does not have a sequence attached. + + + + pg_tablespace_databases 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 + pg_tablespace_databases to query pg_class. 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 - 1.34 --- src/backend/utils/adt/misc.c 2 Jul 2004 05:12:56 - *** *** 16,26 --- 16,31 #include #include + #include #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
Re: [PATCHES] pg_tablespace_databases
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] pg_tablespace_databases
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 - 1.211 +++ func.sgml 1 Jul 2004 18:38:34 - @@ -7232,6 +7232,10 @@ pg_get_serial_sequence + +pg_tablespace_databases + + lists functions that extract information from the system catalogs. @@ -7325,6 +7329,11 @@ get name of the sequence that a serial or bigserial column uses + + pg_tablespace_databases(namespace_oid) + setof oid + get set of database oids that have objects in the namespace + @@ -7360,6 +7369,16 @@ for passing to the sequence functions (see ). NULL is returned if the column does not have a sequence attached. + + + + pg_tablespace_databases 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 + pg_tablespace_databases to query pg_class. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] pg_tablespace_databases
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.c2 Jun 2004 21:29:29 - 1.34 +++ src/backend/utils/adt/misc.c28 Jun 2004 11:16:05 - @@ -16,11 +16,16 @@ #include #include +#include #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 - 1.339 +++ src/include/catalog/pg_proc.h 28 Jun 2004 11:16:32 - @@ -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_t
Re: [PATCHES] pg_tablespace_databases
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[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. Regards, Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly