Index: doc/src/sgml/func.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.276 diff -c -c -r1.276 func.sgml *** doc/src/sgml/func.sgml 2 Aug 2005 16:11:56 -0000 1.276 --- doc/src/sgml/func.sgml 11 Aug 2005 04:21:37 -0000 *************** *** 9061,9066 **** --- 9061,9069 ---- pg_cancel_backend + + pg_reload_conf + signal *************** *** 9068,9074 **** ! The function shown in sends control signals to other server processes. Use of this function is restricted to superusers. --- 9071,9077 ---- ! The functions shown in sends control signals to other server processes. Use of this function is restricted to superusers. *************** *** 9090,9110 **** int Cancel a backend's current query ! This function returns 1 if successful, 0 if not successful. The process ID (pid) of an active backend can be found from the procpid column in the pg_stat_activity view, or by listing the postgres processes on the server with ps. ! ! ! pg_start_backup ! pg_stop_backup --- 9093,9121 ---- int Cancel a backend's current query + + + pg_reload_conf() + + int + Triggers the server processes to reload configuration files + ! These functions return 1 if successful, 0 if not successful. The process ID (pid) of an active backend can be found from the procpid column in the pg_stat_activity view, or by listing the postgres processes on the server with ps. ! ! pg_reload_conf sends a SIGHUP event to the ! postmaster, and thus triggers a reload of the configuration files ! in all backend processes. ! pg_stop_backup *************** *** 9309,9314 **** --- 9320,9457 ---- appropriate. + + The functions shown in provide native file access to + files on the machine hosting the server. They are restricted to + the cluster directory or the logfile directory. + Use of these functions is restricted to superusers. + + + + Generic File Access Functions + + + Name Return Type Description + + + + + + + pg_file_stat(filename_text) + + record + Retrieves file stats + + + + pg_file_length(filename_text) + + int8 + Returns the file length + + + + pg_file_read(filename_text, + offset_int8,length_int8) + + text + Returns the contents of a text file + + + + pg_dir_ls(dirname_text,fullpath_bool) + + setof text + Returns the file length + + + +
+ + + pg_file_stat + + + + pg_file_stat() returns a record that contains the + length, creation timestamp, last accessed timestamp, last modified + timestamp and and a flag indicating a directory. + + + + pg_file_length + + + pg_file_length() returns the length of the given file. + + + + pg_file_read + + + pg_file_read() returns a part of a textfile, starting + at the offset giving length bytes. + + + pg_dir_ls lists all filenames in the named directory. + + + + The functions shown in allow access to the server + logfile, if the stderr log output is redirected. + Use of these functions is restricted to superusers. + + + + Backend Logfile Functions + + + Name Return Type Description + + + + + + + pg_logfile_rotate() + + int + Trigger logfile rotation + + + + pg_logdir_ls() + + setof record + lists all logfiles in the pg_log subdirectory + + + +
+ + + pg_logfile_rotate + + + pg_logfile_rotate issues a logfile rotation trigger, + which forces the server to close the current logfile and open a + fresh one. + + + + pg_logdir_ls + + + pg_logdir_ls lists all files in the pg_log + subdirectory. For each log file, a record consisting of a + timestamp of its creation time and a complete path to the file is + returned. For convenience, the view pg_logdir_ls wraps the + function. + + Index: src/backend/catalog/system_views.sql =================================================================== RCS file: /cvsroot/pgsql/src/backend/catalog/system_views.sql,v retrieving revision 1.18 diff -c -c -r1.18 system_views.sql *** src/backend/catalog/system_views.sql 31 Jul 2005 17:19:17 -0000 1.18 --- src/backend/catalog/system_views.sql 11 Aug 2005 04:21:37 -0000 *************** *** 331,333 **** --- 331,338 ---- pg_stat_get_db_blocks_hit(D.oid) AS blks_read, pg_stat_get_db_blocks_hit(D.oid) AS blks_hit FROM pg_database D; + + CREATE VIEW pg_logdir_ls AS + SELECT * + FROM pg_logdir_ls() AS A + (filetime timestamp, filename text); Index: src/backend/postmaster/postmaster.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v retrieving revision 1.461 diff -c -c -r1.461 postmaster.c *** src/backend/postmaster/postmaster.c 29 Jul 2005 19:30:04 -0000 1.461 --- src/backend/postmaster/postmaster.c 11 Aug 2005 04:21:44 -0000 *************** *** 3393,3398 **** --- 3393,3403 ---- } } + if (CheckPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE) && SysLoggerPID != 0) + { + kill(SysLoggerPID, SIGUSR1); + } + PG_SETMASK(&UnBlockSig); errno = save_errno; Index: src/backend/postmaster/syslogger.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/postmaster/syslogger.c,v retrieving revision 1.18 diff -c -c -r1.18 syslogger.c *** src/backend/postmaster/syslogger.c 21 Jul 2005 18:06:12 -0000 1.18 --- src/backend/postmaster/syslogger.c 11 Aug 2005 04:21:45 -0000 *************** *** 101,106 **** --- 101,107 ---- * Flags set by interrupt handlers for later service in the main loop. */ static volatile sig_atomic_t got_SIGHUP = false; + static volatile sig_atomic_t rotation_requested = false; /* Local subroutines */ *************** *** 117,122 **** --- 118,124 ---- static char *logfile_getname(pg_time_t timestamp); static void set_next_rotation_time(void); static void sigHupHandler(SIGNAL_ARGS); + static void sigUsr1Handler(SIGNAL_ARGS); /* *************** *** 200,206 **** pqsignal(SIGQUIT, SIG_IGN); pqsignal(SIGALRM, SIG_IGN); pqsignal(SIGPIPE, SIG_IGN); ! pqsignal(SIGUSR1, SIG_IGN); pqsignal(SIGUSR2, SIG_IGN); /* --- 202,208 ---- pqsignal(SIGQUIT, SIG_IGN); pqsignal(SIGALRM, SIG_IGN); pqsignal(SIGPIPE, SIG_IGN); ! pqsignal(SIGUSR1, sigUsr1Handler); /* request log rotation */ pqsignal(SIGUSR2, SIG_IGN); /* *************** *** 235,241 **** /* main worker loop */ for (;;) { - bool rotation_requested = false; bool time_based_rotation = false; #ifndef WIN32 --- 237,242 ---- *************** *** 726,731 **** --- 727,734 ---- char *filename; FILE *fh; + rotation_requested = false; + /* * When doing a time-based rotation, invent the new logfile name based * on the planned rotation time, not current time, to avoid "slippage" *************** *** 876,878 **** --- 879,888 ---- { got_SIGHUP = true; } + + /* SIGUSR1: set flag to rotate logfile */ + static void + sigUsr1Handler(SIGNAL_ARGS) + { + rotation_requested = true; + } Index: src/backend/utils/adt/Makefile =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/Makefile,v retrieving revision 1.58 diff -c -c -r1.58 Makefile *** src/backend/utils/adt/Makefile 29 Jul 2005 14:46:57 -0000 1.58 --- src/backend/utils/adt/Makefile 11 Aug 2005 04:21:45 -0000 *************** *** 24,30 **** tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \ network.o mac.o inet_net_ntop.o inet_net_pton.o \ ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \ ! ascii.o quote.o pgstatfuncs.o encode.o dbsize.o like.o: like.c like_match.c --- 24,30 ---- tid.o timestamp.o varbit.o varchar.o varlena.o version.o xid.o \ network.o mac.o inet_net_ntop.o inet_net_pton.o \ ri_triggers.o pg_lzcompress.o pg_locale.o formatting.o \ ! ascii.o quote.o pgstatfuncs.o encode.o dbsize.o genfile.o like.o: like.c like_match.c Index: src/backend/utils/adt/misc.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/misc.c,v retrieving revision 1.45 diff -c -c -r1.45 misc.c *** src/backend/utils/adt/misc.c 4 Jul 2005 04:51:50 -0000 1.45 --- src/backend/utils/adt/misc.c 11 Aug 2005 04:21:45 -0000 *************** *** 17,34 **** --- 17,46 ---- #include #include #include + #include + #include #include "commands/dbcommands.h" #include "miscadmin.h" #include "storage/procarray.h" + #include "storage/pmsignal.h" #include "storage/fd.h" #include "utils/builtins.h" + #include "utils/elog.h" + #include "utils/datetime.h" #include "funcapi.h" #include "catalog/pg_type.h" #include "catalog/pg_tablespace.h" + #include "postmaster/syslogger.h" #define atooid(x) ((Oid) strtoul((x), NULL, 10)) + typedef struct + { + char *location; + DIR *dirdesc; + } directory_fctx; + /* * Check if data is Null *************** *** 107,112 **** --- 119,273 ---- PG_RETURN_INT32(pg_signal_backend(PG_GETARG_INT32(0), SIGINT)); } + + Datum + pg_reload_conf(PG_FUNCTION_ARGS) + { + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + (errmsg("only superuser can signal the postmaster")))); + + if (kill(PostmasterPid, SIGHUP)) + { + ereport(WARNING, + (errmsg("failed to send signal to postmaster: %m"))); + + PG_RETURN_INT32(0); + } + + PG_RETURN_INT32(1); + } + + + /* + * Rotate log file + */ + Datum + pg_logfile_rotate(PG_FUNCTION_ARGS) + { + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + (errmsg("only superuser can rotate log files")))); + + if (!Redirect_stderr) + { + ereport(NOTICE, + (errcode(ERRCODE_WARNING), + errmsg("no logfile configured; rotation not supported"))); + PG_RETURN_INT32(0); + } + + SendPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE); + + PG_RETURN_INT32(0); + } + + /* + * scan log directory for log files + */ + Datum pg_logdir_ls(PG_FUNCTION_ARGS) + { + FuncCallContext *funcctx; + struct dirent *de; + directory_fctx *fctx; + + if (!superuser()) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + (errmsg("only superuser can list the log directory")))); + + if (memcmp(Log_filename, "postgresql-%Y-%m-%d_%H%M%S.log", 30) != 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + (errmsg("the log_filename parameter must equal 'postgresql-%%Y-%%m-%%d_%%H%%M%%S.log'")))); + + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + TupleDesc tupdesc; + + funcctx = SRF_FIRSTCALL_INIT(); + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + fctx = palloc(sizeof(directory_fctx)); + if (is_absolute_path(Log_directory)) + fctx->location = Log_directory; + else + { + fctx->location = palloc(strlen(DataDir) + strlen(Log_directory) + 2); + sprintf(fctx->location, "%s/%s", DataDir, Log_directory); + } + tupdesc = CreateTemplateTupleDesc(2, false); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "starttime", + TIMESTAMPOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "filename", + TEXTOID, -1, 0); + + funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); + + fctx->dirdesc = AllocateDir(fctx->location); + + if (!fctx->dirdesc) + ereport(ERROR, + (errcode_for_file_access(), + errmsg("%s is not browsable: %m", fctx->location))); + + funcctx->user_fctx = fctx; + MemoryContextSwitchTo(oldcontext); + } + + funcctx = SRF_PERCALL_SETUP(); + fctx = (directory_fctx*) funcctx->user_fctx; + + if (!fctx->dirdesc) /* not a readable directory */ + SRF_RETURN_DONE(funcctx); + + while ((de = ReadDir(fctx->dirdesc, fctx->location)) != NULL) + { + char *values[2]; + HeapTuple tuple; + char *field[MAXDATEFIELDS]; + char lowstr[MAXDATELEN + 1]; + int dtype; + int nf, ftype[MAXDATEFIELDS]; + fsec_t fsec; + int tz = 0; + struct pg_tm date; + + /* + * Default format: + * postgresql-YYYY-MM-DD_HHMMSS.log + */ + if (strlen(de->d_name) != 32 || memcmp(de->d_name, "postgresql-", 11) || + de->d_name[21] != '_' || strcmp(de->d_name + 28, ".log")) + continue; + + de->d_name[17] = '\0'; + values[0] = de->d_name + 11; /* timestamp */ + + values[1] = palloc(strlen(fctx->location) + strlen(de->d_name) + 2); + sprintf(values[1], "%s/%s", fctx->location, de->d_name); + + /* parse and decode expected timestamp */ + if (ParseDateTime(values[0], lowstr, sizeof(lowstr), field, ftype, MAXDATEFIELDS, &nf)) + continue; + + if (DecodeDateTime(field, ftype, nf, &dtype, &date, &fsec, &tz)) + continue; + + /* Seems the format fits the expected format; feed it into the tuple */ + + tuple = BuildTupleFromCStrings(funcctx->attinmeta, values); + + SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple)); + } + + FreeDir(fctx->dirdesc); + SRF_RETURN_DONE(funcctx); + } + #ifdef NOT_USED /* Disabled in 8.0 due to reliability concerns; FIXME someday */ Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.380 diff -c -c -r1.380 pg_proc.h *** src/include/catalog/pg_proc.h 2 Aug 2005 16:11:57 -0000 1.380 --- src/include/catalog/pg_proc.h 11 Aug 2005 04:21:54 -0000 *************** *** 3049,3054 **** --- 3049,3073 ---- DATA(insert OID = 2173 ( pg_stop_backup PGNSP PGUID 12 f f t f v 0 25 "" _null_ _null_ _null_ pg_stop_backup - _null_ )); DESCR("Finish taking an online backup"); + DATA(insert OID = 2621 ( pg_reload_conf PGNSP PGUID 12 f f t f v 0 23 "" _null_ _null_ _null_ pg_reload_conf - _null_ )); + DESCR("Reload configuration files"); + + DATA(insert OID = 2622 ( pg_logfile_rotate PGNSP PGUID 12 f f t f v 0 23 "" _null_ _null_ _null_ pg_logfile_rotate - _null_ )); + DESCR("rotate log file"); + DATA(insert OID = 2623 ( pg_logdir_ls PGNSP PGUID 12 f f t t v 0 2249 "" _null_ _null_ _null_ pg_logdir_ls - _null_ )); + DESCR("list all available log files"); + + + DATA(insert OID = 2624 ( pg_file_stat PGNSP PGUID 12 f f t f v 1 2249 "25" _null_ _null_ _null_ pg_file_stat - _null_ )); + DESCR("retrieve file stats"); + DATA(insert OID = 2625 ( pg_file_length PGNSP PGUID 14 f f t f v 1 20 "25" _null_ _null_ _null_ "SELECT len FROM pg_file_stat($1) AS s(len int8, c timestamp, a timestamp, m timestamp, i bool)" - _null_ )); + DESCR("returns length of a file"); + DATA(insert OID = 2626 ( pg_file_read PGNSP PGUID 12 f f t f v 3 25 "25 20 20" _null_ _null_ _null_ pg_file_read - _null_ )); + DESCR("reads text from a file"); + DATA(insert OID = 2627 ( pg_dir_ls PGNSP PGUID 12 f f t t v 2 25 "25 16" _null_ _null_ _null_ pg_dir_ls - _null_ )); + DESCR("list all file in a directory"); + + /* Aggregates (moved here from pg_aggregate for 7.3) */ DATA(insert OID = 2100 ( avg PGNSP PGUID 12 t f f f i 1 1700 "20" _null_ _null_ _null_ aggregate_dummy - _null_ )); Index: src/include/storage/pmsignal.h =================================================================== RCS file: /cvsroot/pgsql/src/include/storage/pmsignal.h,v retrieving revision 1.12 diff -c -c -r1.12 pmsignal.h *** src/include/storage/pmsignal.h 28 Jun 2005 19:51:25 -0000 1.12 --- src/include/storage/pmsignal.h 11 Aug 2005 04:21:54 -0000 *************** *** 25,30 **** --- 25,31 ---- PMSIGNAL_PASSWORD_CHANGE, /* pg_auth file has changed */ PMSIGNAL_WAKEN_CHILDREN, /* send a SIGUSR1 signal to all backends */ PMSIGNAL_WAKEN_ARCHIVER, /* send a NOTIFY signal to xlog archiver */ + PMSIGNAL_ROTATE_LOGFILE, /* send SIGUSR1 to syslogger to rotate logfile */ NUM_PMSIGNALS /* Must be last value of enum! */ } PMSignalReason; Index: src/include/utils/builtins.h =================================================================== RCS file: /cvsroot/pgsql/src/include/utils/builtins.h,v retrieving revision 1.262 diff -c -c -r1.262 builtins.h *** src/include/utils/builtins.h 29 Jul 2005 14:47:04 -0000 1.262 --- src/include/utils/builtins.h 11 Aug 2005 04:21:54 -0000 *************** *** 374,385 **** --- 374,393 ---- extern Datum pg_complete_relation_size_name(PG_FUNCTION_ARGS); extern Datum pg_size_pretty(PG_FUNCTION_ARGS); + /* genfile.c */ + extern Datum pg_file_stat(PG_FUNCTION_ARGS); + extern Datum pg_file_read(PG_FUNCTION_ARGS); + extern Datum pg_dir_ls(PG_FUNCTION_ARGS); + /* misc.c */ extern Datum nullvalue(PG_FUNCTION_ARGS); extern Datum nonnullvalue(PG_FUNCTION_ARGS); extern Datum current_database(PG_FUNCTION_ARGS); extern Datum pg_cancel_backend(PG_FUNCTION_ARGS); + extern Datum pg_reload_conf(PG_FUNCTION_ARGS); extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS); + extern Datum pg_logfile_rotate(PG_FUNCTION_ARGS); + extern Datum pg_logdir_ls(PG_FUNCTION_ARGS); /* not_in.c */ extern Datum int4notin(PG_FUNCTION_ARGS); Index: src/test/regress/expected/rules.out =================================================================== RCS file: /cvsroot/pgsql/src/test/regress/expected/rules.out,v retrieving revision 1.106 diff -c -c -r1.106 rules.out *** src/test/regress/expected/rules.out 31 Jul 2005 17:19:22 -0000 1.106 --- src/test/regress/expected/rules.out 11 Aug 2005 04:21:56 -0000 *************** *** 1280,1285 **** --- 1280,1286 ---- pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS "tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); pg_locks | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid, l.objid, l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database" oid, relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint, "transaction" xid, pid integer, "mode" text, "granted" boolean); + pg_logdir_ls | SELECT a.filetime, a.filename FROM pg_logdir_ls() a(filetime timestamp without time zone, filename text); pg_prepared_xacts | SELECT p."transaction", p.gid, p."prepared", u.rolname AS "owner", d.datname AS "database" FROM ((pg_prepared_xact() p("transaction" xid, gid text, "prepared" timestamp with time zone, ownerid oid, dbid oid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid; pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); *************** *** 1320,1326 **** shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp; ! (44 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; --- 1321,1327 ---- shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp; ! (45 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename;