Hi
On Wed, Mar 15, 2017 at 5:27 PM, Robert Haas <[email protected]> wrote:
> On Mon, Feb 20, 2017 at 6:21 AM, Dave Page <[email protected]> wrote:
>> Patch includes the code and doc updates.
>
> Review:
>
> + strftime(mtime, 25, "%Y-%m-%d %H:%M:%S %Z",
> localtime(&(attrib.st_ctime)));
> + const int n = snprintf(NULL, 0, "%lld", attrib.st_size);
> + char size[n+1];
> + snprintf(size, n+1, "%lld", attrib.st_size);
>
> We don't allow variable declarations in mid-block. You've been
> programming in C++ for too long!
Err, yeah. Ooops. Fixed.
> The documentation should be updated to say that access to
> pg_ls_logdir() and pg_ls_waldir() can be granted via the permissions
> system (see the paragraph above the table you updated).
>
> The four existing functions in the documentation table each have a
> corresponding paragraph below the table, but the two new functions you
> added don't.
Added.
> +1 for the concept.
Thanks, updated patch attached.
--
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..2fadad860b 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"
@@ -982,3 +984,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, "%lld", attrib.st_size);
+ size = palloc(size_len+1);
+ snprintf(size, size_len+1, "%lld", 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
