On Thu, Mar 16, 2017 at 9:54 AM, Thomas Munro
<thomas.mu...@enterprisedb.com> wrote:
> On Thu, Mar 16, 2017 at 10:40 PM, Dave Page <dp...@pgadmin.org> wrote:
>>> + const int n = snprintf(NULL, 0, "%lld", attrib.st_size);
>
> I wonder what the portable printf directive is for off_t. Maybe
> better to use INT64_FORMAT and cast to int64?
Hmm, good point. Google seems to be saying there isn't one. Patch
updated as you suggest (and I've added back in a function declaration
that got lost in the rebasing of the last version).
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a521912317..e15ad77dec 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19646,7 +19646,8 @@ postgres=# SELECT * FROM
pg_walfile_name_offset(pg_stop_backup());
database cluster directory and the <varname>log_directory</> can be
accessed. Use a relative path for files in the cluster directory,
and a path matching the <varname>log_directory</> configuration setting
- for log files. Use of these functions is restricted to superusers.
+ for log files. Use of these functions is restricted to superusers
+ except where stated otherwise.
</para>
<table id="functions-admin-genfile-table">
@@ -19669,6 +19670,26 @@ postgres=# SELECT * FROM
pg_walfile_name_offset(pg_stop_backup());
</row>
<row>
<entry>
+ <literal><function>pg_ls_logdir()</function></literal>
+ </entry>
+ <entry><type>setof record</type></entry>
+ <entry>
+ List the name, last modification time and size of files in the log
directory.
+ Execute permission may be granted to non-superuser roles.
+ </entry>
+ </row>
+ <row>
+ <entry>
+ <literal><function>pg_ls_waldir()</function></literal>
+ </entry>
+ <entry><type>setof record</type></entry>
+ <entry>
+ List the name, last modification time and size of files in the WAL
directory.
+ Execute permission may be granted to non-superuser roles.
+ </entry>
+ </row>
+ <row>
+ <entry>
<literal><function>pg_read_file(<parameter>filename</> <type>text</>
[, <parameter>offset</> <type>bigint</>, <parameter>length</> <type>bigint</>
[, <parameter>missing_ok</> <type>boolean</>] ])</function></literal>
</entry>
<entry><type>text</type></entry>
@@ -19699,7 +19720,7 @@ postgres=# SELECT * FROM
pg_walfile_name_offset(pg_stop_backup());
</table>
<para>
- All of these functions take an optional <parameter>missing_ok</> parameter,
+ Some of these functions take an optional <parameter>missing_ok</>
parameter,
which specifies the behavior when the file or directory does not exist.
If <literal>true</literal>, the function returns NULL (except
<function>pg_ls_dir</>, which returns an empty result set). If
@@ -19720,6 +19741,26 @@ postgres=# SELECT * FROM
pg_walfile_name_offset(pg_stop_backup());
</para>
<indexterm>
+ <primary>pg_ls_logdir</primary>
+ </indexterm>
+ <para>
+ <function>pg_ls_logdir</> returns the last modified time (mtime), size
+ and names of all the file in the log directory. By default only superusers
+ can use this function, however additional roles may be granted execute
+ permission if required.
+ </para>
+
+ <indexterm>
+ <primary>pg_ls_waldir</primary>
+ </indexterm>
+ <para>
+ <function>pg_ls_waldir</> returns the last modified time (mtime), size
+ and names of all the file in the write ahead log (WAL) directory. By
+ default only superusers can use this function, however additional roles
+ may be granted execute permission if required.
+ </para>
+
+ <indexterm>
<primary>pg_read_file</primary>
</indexterm>
<para>
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 0bce20914e..b6552da4b0 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1102,3 +1102,6 @@ REVOKE EXECUTE ON FUNCTION pg_stat_reset() FROM public;
REVOKE EXECUTE ON FUNCTION pg_stat_reset_shared(text) FROM public;
REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM
public;
REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM
public;
+
+REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 1ec7f32470..ad75d18a2f 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -15,12 +15,14 @@
#include "postgres.h"
#include <sys/file.h>
+#include <sys/stat.h>
#include <signal.h>
#include <dirent.h>
#include <math.h>
#include <unistd.h>
#include "access/sysattr.h"
+#include "access/xlog_internal.h"
#include "catalog/pg_authid.h"
#include "catalog/catalog.h"
#include "catalog/pg_tablespace.h"
@@ -44,6 +46,8 @@
#include "utils/builtins.h"
#include "utils/timestamp.h"
+/* Generic function to return a directory listing of files */
+Datum pg_ls_dir_files(FunctionCallInfo fcinfo, char *dir);
/*
* Common subroutine for num_nulls() and num_nonnulls().
@@ -982,3 +986,111 @@ pg_current_logfile_1arg(PG_FUNCTION_ARGS)
{
return pg_current_logfile(fcinfo);
}
+
+
+typedef struct
+{
+ char *location;
+ DIR *dirdesc;
+} directory_fctx;
+
+/* Generic function to return a directory listing of files */
+Datum
+pg_ls_dir_files(FunctionCallInfo fcinfo, char *dir)
+{
+ FuncCallContext *funcctx;
+ struct dirent *de;
+ directory_fctx *fctx;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ TupleDesc tupdesc;
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext =
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ fctx = palloc(sizeof(directory_fctx));
+
+ tupdesc = CreateTemplateTupleDesc(3, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "mtime",
+ TIMESTAMPTZOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "size",
+ INT8OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "file",
+ TEXTOID, -1, 0);
+
+ funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc);
+
+ fctx->location = pstrdup(dir);
+ fctx->dirdesc = AllocateDir(fctx->location);
+
+ if (!fctx->dirdesc)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not read directory
\"%s\": %m",
+ fctx->location)));
+
+ funcctx->user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ fctx = (directory_fctx *) funcctx->user_fctx;
+
+ while ((de = ReadDir(fctx->dirdesc, fctx->location)) != NULL)
+ {
+ char *values[3];
+ char *path;
+ char mtime[25];
+ HeapTuple tuple;
+ struct stat attrib;
+ int size_len;
+ char *size;
+
+ /*
+ * Skip hidden files
+ */
+ if (de->d_name[0] == '.')
+ continue;
+
+ /* Get the file info */
+ path = psprintf("%s/%s", fctx->location, de->d_name);
+ stat(path, &attrib);
+
+ /* Ignore anything but regular files */
+ if (!S_ISREG(attrib.st_mode))
+ continue;
+
+ /* Get the mtime and size */
+ strftime(mtime, 25, "%Y-%m-%d %H:%M:%S %Z",
localtime(&(attrib.st_ctime)));
+ size_len = snprintf(NULL, 0, INT64_FORMAT, (int64)
attrib.st_size);
+ size = palloc(size_len+1);
+ snprintf(size, size_len+1, INT64_FORMAT, (int64)
attrib.st_size);
+
+ values[0] = mtime;
+ values[1] = size;
+ values[2] = de->d_name;
+
+ tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
+
+ SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
+ }
+
+ FreeDir(fctx->dirdesc);
+ SRF_RETURN_DONE(funcctx);
+}
+
+/* Function to return the list of files in the log directory */
+Datum
+pg_ls_logdir(PG_FUNCTION_ARGS)
+{
+ return pg_ls_dir_files(fcinfo, Log_directory);
+}
+
+/* Function to return the list of files in the WAL directory */
+Datum
+pg_ls_waldir(PG_FUNCTION_ARGS)
+{
+ return pg_ls_dir_files(fcinfo, XLOGDIR);
+}
\ No newline at end of file
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 3d5d866071..bda4b1190a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5398,6 +5398,12 @@ DESCR("pg_controldata init state information as a
function");
DATA(insert OID = 3445 ( pg_import_system_collations PGNSP PGUID 12 100 0 0 0
f f f f t f v r 2 0 2278 "16 4089" _null_ _null_ "{if_not_exists,schema}"
_null_ _null_ pg_import_system_collations _null_ _null_ _null_ ));
DESCR("import collations from operating system");
+/* system management/monitoring related functions */
+DATA(insert OID = 3353 ( pg_ls_logdir PGNSP PGUID 12 10 20 0 0
f f f f t t v s 0 0 2249 "" "{1184,20,25}" "{o,o,o}" "{mtime,size,file}" _null_
_null_ pg_ls_logdir _null_ _null_ _null_ ));
+DESCR("list files in the log directory");
+DATA(insert OID = 3354 ( pg_ls_waldir PGNSP PGUID 12 10 20 0 0
f f f f t t v s 0 0 2249 "" "{1184,20,25}" "{o,o,o}" "{mtime,size,file}" _null_
_null_ pg_ls_waldir _null_ _null_ _null_ ));
+DESCR("list of files in the WAL directory");
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
