[PATCHES] serverlog rotation/functions
The attached patch includes serverlog rotation with minimal shared memory usage as discussed and functions to access it. Regards, Andreas Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v retrieving revision 1.211 diff -u -r1.211 func.sgml --- doc/src/sgml/func.sgml 25 Jun 2004 17:20:21 - 1.211 +++ doc/src/sgml/func.sgml 28 Jun 2004 10:35:09 - @@ -7430,6 +7430,80 @@ /para indexterm zone=functions-misc + primarypg_logfile_get/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_length/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_name/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_rotate/primary + /indexterm + para +The functions shown in xref linkend=functions-misc-logfile + deal with the server log file if configured with log_destination + quotefile/quote. + /para + + table id=functions-misc-logfile +titleServer Logfile Functions/title +tgroup cols=3 + thead + rowentryName/entry entryReturn Type/entry entryDescription/entry/row + /thead + + tbody + row + entryliteralfunctionpg_logfile_get/function(parametersize_int4/parameter, + parameteroffset_int4/parameter,parameterfilename_text/parameter)/literal/entry + entrytypecstring/type/entry + entryget a part of the current server log file/entry + /row + row + entryliteralfunctionpg_logfile_length/function(paramaterfilename_text/parameter)/literal/entry + entrytypeint4/type/entry + entryreturn the current length of the server log file/entry + /row + row + entryliteralfunctionpg_logfile_rotate/function()/literal/entry + entrytypecstring/type/entry + entryrotates the server log file and returns the new log file + name/entry + /row + row + entryliteralfunctionpg_logfile_name/function()/literal/entry + entrytypecstring/type/entry + entryreturns the current server log file name/entry + /row + row + entryliteralfunctionpg_logfile_rotate/function()/literal/entry + entrytypecstring/type/entry + entryrotates the server log file and returns the previous log file + name/entry + /row + /tbody +/tgroup +/table +para +The functionpg_logfile_get/function function will return the + contents of the current server log file, limited by the size + parameter. If size is NULL, a server internal limit (currently + 5) is applied. The position parameter specifies the + starting position of the server log chunk to be returned. A + positive number or 0 will be counted from the start of the file, + a negative number from the end; if NULL, -size is assumed + (i.e. the tail of the log file). +/para +para +Both functionpg_logfile_get/function and + functionpg_logfile_length/function have a filename + parameter which may specify the logfile to examine or the + current logfile if NULL. +/para + + indexterm zone=functions-misc primarypg_cancel_backend/primary /indexterm Index: doc/src/sgml/runtime.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.268 diff -u -r1.268 runtime.sgml --- doc/src/sgml/runtime.sgml 27 Jun 2004 22:58:19 - 1.268 +++ doc/src/sgml/runtime.sgml 28 Jun 2004 10:35:19 - @@ -1721,14 +1721,25 @@ listitem para productnamePostgreSQL/productname supports several methods -for loggning, including systemitemstderr/systemitem and -systemitemsyslog/systemitem. On Windows, -systemitemeventlog/systemitem is also supported. Set this +for logging, including systemitemstderr/systemitem, +systemitemfile/systemitem and systemitemsyslog/systemitem. + On Windows, systemitemeventlog/systemitem is also supported. Set this option to a list of desired log destinations separated by a comma. The default is to log to systemitemstderr/systemitem only. This option must be set at server start. /para /listitem + /varlistentry + + varlistentry id=guc-syslog-facility xreflabel=log_filename + termvarnamelog_filename/varname (typestring/type)/term + listitem +para + This option sets the target filename for the log destination + quotefile/quote option. It may be specified as absolute + path or relative to the applicationcluster directory/application. +/para + /listitem /varlistentry varlistentry id=guc-syslog-facility xreflabel=syslog_facility Index: src/backend/postmaster/postmaster.c
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
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 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 - 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_tablespace_databases - _null_
Re: [PATCHES] plperl patch
Andrew Dunstan wrote: I wrote: I know it's late in the day, but ... Attached is a patch and 2 replacement files for plperl. The work has been done under the auspices of the plperlng project on pgfoundry. The code (which has been through several iterations) comes from CommandPrompt, and has had some minor editorializing by me (spelling, indentation, function heading comments). It has also been reviewed somewhat by Abhijit Menon-Sen, who supplied a small optimization. It has been tested by me and by David Fetter. My apologies. I should have tested more. It appears that the optimization Abhijit sent us causes a memory error, at least onn my machine. I have therefore reverted it. Please ignore the patch file previously sent and use this one instead. The other files in my previous post are still relevant - to save space I have not reattached them. This time it's attached ... cheers andrew Index: GNUmakefile === RCS file: /projects/cvsroot/pgsql-server/src/pl/plperl/GNUmakefile,v retrieving revision 1.12 diff -c -w -r1.12 GNUmakefile *** GNUmakefile 21 Jan 2004 19:04:11 - 1.12 --- GNUmakefile 27 Jun 2004 20:51:24 - *** *** 15,21 # The code isn't clean with regard to these warnings. ifeq ($(GCC),yes) ! override CFLAGS := $(filter-out -Wall -Wmissing-declarations -Wmissing-prototypes, $(CFLAGS)) endif override CPPFLAGS := -I$(srcdir) -I$(perl_archlibexp)/CORE $(CPPFLAGS) --- 15,21 # The code isn't clean with regard to these warnings. ifeq ($(GCC),yes) ! override CFLAGS := $(filter-out -Wall -Wmissing-declarations -Wmissing-prototypes, $(CFLAGS), -Wl,-rpath,$(perl_archlibexp)/CORE) endif override CPPFLAGS := -I$(srcdir) -I$(perl_archlibexp)/CORE $(CPPFLAGS) *** *** 25,31 SO_MAJOR_VERSION = 0 SO_MINOR_VERSION = 0 ! OBJS = plperl.o eloglvl.o SPI.o SHLIB_LINK = $(perl_embed_ldflags) $(BE_DLLLIBS) include $(top_srcdir)/src/Makefile.shlib --- 25,31 SO_MAJOR_VERSION = 0 SO_MINOR_VERSION = 0 ! OBJS = plperl.o spi_internal.o SPI.o SHLIB_LINK = $(perl_embed_ldflags) $(BE_DLLLIBS) include $(top_srcdir)/src/Makefile.shlib Index: SPI.xs === RCS file: /projects/cvsroot/pgsql-server/src/pl/plperl/SPI.xs,v retrieving revision 1.5 diff -c -w -r1.5 SPI.xs *** SPI.xs 4 Sep 2002 22:49:37 - 1.5 --- SPI.xs 27 Jun 2004 20:51:24 - *** *** 6,22 #include perl.h #include XSUB.h ! #include eloglvl.h ! MODULE = SPI PREFIX = elog_ PROTOTYPES: ENABLE VERSIONCHECK: DISABLE void ! elog_elog(level, message) int level char* message CODE: --- 6,22 #include perl.h #include XSUB.h ! #include spi_internal.h ! MODULE = SPI PREFIX = spi_ PROTOTYPES: ENABLE VERSIONCHECK: DISABLE void ! spi_elog(level, message) int level char* message CODE: *** *** 24,44 int ! elog_DEBUG() int ! elog_LOG() int ! elog_INFO() int ! elog_NOTICE() int ! elog_WARNING() int ! elog_ERROR() ! --- 24,56 int ! spi_DEBUG() int ! spi_LOG() int ! spi_INFO() int ! spi_NOTICE() int ! spi_WARNING() int ! spi_ERROR() + SV* + spi_spi_exec_query(query, ...) + char* query; + PREINIT: + HV *ret_hash; + int limit=0; + CODE: + if (items2) Perl_croak(aTHX_ Usage: spi_exec_query(query, limit) or spi_exec_query(query)); + if (items == 2) limit = SvIV(ST(1)); + ret_hash=plperl_spi_exec(query, limit); + RETVAL = newRV_noinc((SV*)ret_hash); + OUTPUT: + RETVAL Index: plperl.c === RCS file: /projects/cvsroot/pgsql-server/src/pl/plperl/plperl.c,v retrieving revision 1.44 diff -c -w -r1.44 plperl.c *** plperl.c6 Jun 2004 00:41:28 - 1.44 --- plperl.c27 Jun 2004 20:51:24 - *** *** 49,54 --- 49,55 #include catalog/pg_language.h #include catalog/pg_proc.h #include catalog/pg_type.h + #include funcapi.h /* need for SRF support */ #include commands/trigger.h #include executor/spi.h #include fmgr.h *** *** 78,83 --- 79,86 TransactionId fn_xmin; CommandId fn_cmin; boollanpltrusted; + boolfn_retistuple; /* true, if function returns tuple */ + Oid ret_oid;/* Oid of returning type */ FmgrInforesult_in_func; Oid result_typioparam; int nargs; *** *** 94,99 --- 97,105 static intplperl_firstcall = 1; static PerlInterpreter
Re: [PATCHES] Include tablespace information in psql \d footers
On Fri, 25 Jun 2004, Tom Lane wrote: Gavin Sherry [EMAIL PROTECTED] writes: Attached. Couple of problems with this: 1. Don't #ifndef WIN32 it. In the first place, we might have tablespace support on Windows later, and in the second place, even if psql is running on Windows that doesn't mean the server it's talking to is. Oops. 2. Printing pg_default when reltblspace is zero is wrong. Get the database's default tablespace and print that. Or perhaps better, don't print anything at all; that would avoid cluttering the display for people who don't use tablespaces. Ahhh yes of course. Attached is a patch with docs which excludes reporting of the default tablespace. GavinIndex: doc/src/sgml/ref/psql-ref.sgml === RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v retrieving revision 1.116 diff -2 -c -r1.116 psql-ref.sgml *** doc/src/sgml/ref/psql-ref.sgml 18 Jun 2004 06:13:05 - 1.116 --- doc/src/sgml/ref/psql-ref.sgml 28 Jun 2004 17:59:31 - *** *** 785,789 For each relation (table, view, index, or sequence) matching the replaceable class=parameterpattern/replaceable, show all ! columns, their types, and any special attributes such as literalNOT NULL/literal or defaults, if any. Associated indexes, constraints, rules, and triggers are --- 785,790 For each relation (table, view, index, or sequence) matching the replaceable class=parameterpattern/replaceable, show all ! columns, their types, the tablespace they reside in (if this differs ! from the database default) and any special attributes such as literalNOT NULL/literal or defaults, if any. Associated indexes, constraints, rules, and triggers are Index: src/bin/psql/common.h === RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/common.h,v retrieving revision 1.35 diff -2 -c -r1.35 common.h *** src/bin/psql/common.h 19 Apr 2004 17:42:58 - 1.35 --- src/bin/psql/common.h 24 Jun 2004 03:16:02 - *** *** 21,24 --- 21,27 #endif + #define atooid(x) ((Oid) strtoul((x), NULL, 10)) + + /* * Safer versions of some standard C library functions. If an Index: src/bin/psql/describe.c === RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/describe.c,v retrieving revision 1.99 diff -2 -c -r1.99 describe.c *** src/bin/psql/describe.c 18 Jun 2004 06:14:04 - 1.99 --- src/bin/psql/describe.c 28 Jun 2004 18:02:08 - *** *** 40,43 --- 40,46 const char *altnamevar, const char *visibilityrule); + static void add_tablespace_footer(char relkind, Oid tablespace, + char **footers, int *count, PQExpBufferData buf); + /* * Handlers for various slash commands displaying some sort of list *** *** 683,686 --- 686,690 boolhasrules; boolhasoids; + Oid tablespace; } tableinfo; boolshow_modifiers = false; *** *** 695,699 /* Get general table info */ printfPQExpBuffer(buf, !SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, relhasoids\n FROM pg_catalog.pg_class WHERE oid = '%s', oid); --- 699,704 /* Get general table info */ printfPQExpBuffer(buf, !SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, \n ! relhasoids, reltablespace \n FROM pg_catalog.pg_class WHERE oid = '%s', oid); *** *** 718,721 --- 723,727 tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), t) == 0; tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), t) == 0; + tableinfo.tablespace = atooid(PQgetvalue(res, 0, 6)); PQclear(res); *** *** 869,873 /* Make footers */ ! if (tableinfo.relkind == 'i') { /* Footer information about an index */ --- 875,893 /* Make footers */ ! if(tableinfo.relkind == 'S' || tableinfo.relkind == 't') ! { ! /* !* We can handle TOAST and sequences together, as the only !* footer is tablespace !*/ ! int count_footers = 0; ! ! footers = pg_malloc_zero(3 * sizeof(*footers)); ! ! add_tablespace_footer(tableinfo.relkind,
[PATCHES] pg_dump owner and acl fix #2
OK, This version works wonderfully on my production database. Chris pg_dump_owners2.txt.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster