Re: pg_ls_tmpdir to show directories and shared filesets (and pg_ls_*)

[Justin Pryzby]

On Fri, Dec 04, 2020 at 12:23:23PM -0500, Tom Lane wrote:
> Justin Pryzby <pry...@telsasoft.com> writes:
> [ v24-0001-Document-historic-behavior-of-links-to-directori.patch ]
> 
> The cfbot is unhappy with one of the test cases you added:

> Maybe it could be salvaged by reversing the sense of the WHERE condition
> so that instead of trying to blacklist stuff, you whitelist just a small
> number of files that should certainly be there.

Yes, I had noticed this one.

-- 
Justin

>From 00d390ac3754db140bb98c5b09bc38e3e9b0e45c Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 16 Mar 2020 14:12:55 -0500
Subject: [PATCH v25 01/11] Document historic behavior of links to
 directories..

Backpatch to 9.5: pg_stat_file
---
 doc/src/sgml/func.sgml | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index df29af6371..7cafdb9107 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25897,7 +25897,7 @@ SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');
         Returns a record containing the file's size, last access time stamp,
         last modification time stamp, last file status change time stamp (Unix
         platforms only), file creation time stamp (Windows only), and a flag
-        indicating if it is a directory.
+        indicating if it is a directory (or a symbolic link to a directory).
        </para>
        <para>
         This function is restricted to superusers by default, but other users
-- 
2.17.0


>From 9ac2f2cf76a53348c505482f8df27ab0464006d1 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 30 Mar 2020 18:59:16 -0500
Subject: [PATCH v25 02/11] pg_stat_file and pg_ls_dir_* to use lstat()..

pg_ls_dir_* will now skip (no longer show) symbolic links, same as other
non-regular file types, as we advertize we do since 8b6d94cf6.  That seems to
be the intented behavior, since irregular file types are 1) less portable; and,
2) we don't currently show a file's type except for "bool is_dir".

pg_stat_file will now 1) show metadata of links themselves, rather than their
target; and, 2) specifically, show links to directories with "is_dir=false";
and, 3) not error on broken symlinks.
---
 doc/src/sgml/func.sgml          | 2 +-
 src/backend/utils/adt/genfile.c | 4 ++--
 2 files changed, 3 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7cafdb9107..df29af6371 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25897,7 +25897,7 @@ SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');
         Returns a record containing the file's size, last access time stamp,
         last modification time stamp, last file status change time stamp (Unix
         platforms only), file creation time stamp (Windows only), and a flag
-        indicating if it is a directory (or a symbolic link to a directory).
+        indicating if it is a directory.
        </para>
        <para>
         This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index d34182a7b0..9f4927220b 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -406,7 +406,7 @@ pg_stat_file(PG_FUNCTION_ARGS)
 
 	filename = convert_and_check_filename(filename_t);
 
-	if (stat(filename, &fst) < 0)
+	if (lstat(filename, &fst) < 0)
 	{
 		if (missing_ok && errno == ENOENT)
 			PG_RETURN_NULL();
@@ -632,7 +632,7 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
 
 		/* Get the file info */
 		snprintf(path, sizeof(path), "%s/%s", dir, de->d_name);
-		if (stat(path, &attrib) < 0)
+		if (lstat(path, &attrib) < 0)
 		{
 			/* Ignore concurrently-deleted files, else complain */
 			if (errno == ENOENT)
-- 
2.17.0


>From a7729e28c3361fad3adc09388a59d47bdce46eb1 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 17 Mar 2020 13:16:24 -0500
Subject: [PATCH v25 03/11] Add tests on pg_ls_dir before changing it

---
 src/test/regress/expected/misc_functions.out | 24 ++++++++++++++++++++
 src/test/regress/sql/misc_functions.sql      |  8 +++++++
 2 files changed, 32 insertions(+)

diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index d3acb98d04..edbfd9abc1 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -201,6 +201,30 @@ select count(*) > 0 from
  t
 (1 row)
 
+select * from (select pg_ls_dir('.', false, true) as name) as ls where ls.name='.'; -- include_dot_dirs=true
+ name 
+------
+ .
+(1 row)
+
+select * from (select pg_ls_dir('.', false, false) as name) as ls where ls.name='.'; -- include_dot_dirs=false
+ name 
+------
+(0 rows)
+
+select pg_ls_dir('does not exist', true, false); -- ok with missingok=true
+ pg_ls_dir 
+-----------
+(0 rows)
+
+select pg_ls_dir('does not exist'); -- fails with missingok=false
+ERROR:  could not open directory "does not exist": No such file or directory
+-- Check that expected columns are present
+select * from pg_stat_file('.') limit 0;
+ size | access | modification | change | creation | isdir 
+------+--------+--------------+--------+----------+-------
+(0 rows)
+
 --
 -- Test adding a support function to a subject function
 --
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 094e8f8296..b32a3a4b74 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -60,6 +60,14 @@ select count(*) > 0 from
    where spcname = 'pg_default') pts
   join pg_database db on pts.pts = db.oid;
 
+select * from (select pg_ls_dir('.', false, true) as name) as ls where ls.name='.'; -- include_dot_dirs=true
+select * from (select pg_ls_dir('.', false, false) as name) as ls where ls.name='.'; -- include_dot_dirs=false
+select pg_ls_dir('does not exist', true, false); -- ok with missingok=true
+select pg_ls_dir('does not exist'); -- fails with missingok=false
+
+-- Check that expected columns are present
+select * from pg_stat_file('.') limit 0;
+
 --
 -- Test adding a support function to a subject function
 --
-- 
2.17.0


>From e3adc14a91c3ae80281b2d34855d98efdc7ecd7b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 9 Mar 2020 22:40:24 -0500
Subject: [PATCH v25 04/11] Add pg_ls_dir_metadata to list a dir with file
 metadata..

Generalize pg_ls_dir_files and retire pg_ls_dir

Need catversion bumped?
---
 doc/src/sgml/func.sgml                       |  21 ++
 src/backend/catalog/system_views.sql         |   1 +
 src/backend/utils/adt/genfile.c              | 231 +++++++++++--------
 src/include/catalog/pg_proc.dat              |  12 +
 src/test/regress/expected/misc_functions.out |  24 ++
 src/test/regress/input/tablespace.source     |   5 +
 src/test/regress/output/tablespace.source    |   8 +
 src/test/regress/sql/misc_functions.sql      |  11 +
 8 files changed, 221 insertions(+), 92 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index df29af6371..226923a134 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25750,6 +25750,27 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_ls_dir_metadata</primary>
+        </indexterm>
+        <function>pg_ls_dir_metadata</function> ( <parameter>dirname</parameter> <type>text</type>
+        <optional>, <parameter>missing_ok</parameter> <type>boolean</type>,
+        <parameter>include_dot_dirs</parameter> <type>boolean</type> </optional> )
+        <returnvalue>setof record</returnvalue>
+        ( <parameter>filename</parameter> <type>text</type>,
+        <parameter>size</parameter> <type>bigint</type>,
+        <parameter>modification</parameter> <type>timestamp with time zone</type> )
+       </para>
+       <para>
+        For each file in the specified directory, list the file and its
+        metadata.
+        Restricted to superusers by default, but other users can be granted
+        EXECUTE to run the function.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index b140c210bc..93d4f1e4a6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1506,6 +1506,7 @@ REVOKE EXECUTE ON FUNCTION pg_stat_file(text,boolean) FROM public;
 
 REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_dir_metadata(text,boolean,boolean) FROM public;
 
 --
 -- We also set up some things as accessible to standard roles.
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 9f4927220b..7351615f8a 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -36,6 +36,21 @@
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
 
+static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags);
+
+#define	LS_DIR_ISDIR				(1<<0) /* Show column: isdir */
+#define	LS_DIR_METADATA				(1<<1) /* Show columns: mtime, size */
+#define	LS_DIR_MISSING_OK			(1<<2) /* Ignore ENOENT if the toplevel dir is missing */
+#define	LS_DIR_SKIP_DOT_DIRS		(1<<3) /* Do not show . or .. */
+#define	LS_DIR_SKIP_HIDDEN			(1<<4) /* Do not show anything begining with . */
+#define	LS_DIR_SKIP_DIRS			(1<<5) /* Do not show directories */
+#define	LS_DIR_SKIP_SPECIAL			(1<<6) /* Do not show special file types */
+
+/*
+ * Shortcut for the historic behavior of the pg_ls_* functions (not including
+ * pg_ls_dir, which skips different files and doesn't show metadata).
+ */
+#define LS_DIR_HISTORIC				(LS_DIR_SKIP_DIRS | LS_DIR_SKIP_HIDDEN | LS_DIR_SKIP_SPECIAL | LS_DIR_METADATA)
 
 /*
  * Convert a "text" filename argument to C string, and check it's allowable.
@@ -449,6 +464,11 @@ pg_stat_file(PG_FUNCTION_ARGS)
 	values[4] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_ctime));
 #endif
 	values[5] = BoolGetDatum(S_ISDIR(fst.st_mode));
+#ifdef WIN32
+	/* Links should have isdir=false */
+	if (pgwin32_is_junction(filename))
+		values[5] = BoolGetDatum(false);
+#endif
 
 	tuple = heap_form_tuple(tupdesc, values, isnull);
 
@@ -476,79 +496,9 @@ pg_stat_file_1arg(PG_FUNCTION_ARGS)
 Datum
 pg_ls_dir(PG_FUNCTION_ARGS)
 {
-	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
-	char	   *location;
-	bool		missing_ok = false;
-	bool		include_dot_dirs = false;
-	bool		randomAccess;
-	TupleDesc	tupdesc;
-	Tuplestorestate *tupstore;
-	DIR		   *dirdesc;
-	struct dirent *de;
-	MemoryContext oldcontext;
-
-	location = convert_and_check_filename(PG_GETARG_TEXT_PP(0));
-
-	/* check the optional arguments */
-	if (PG_NARGS() == 3)
-	{
-		if (!PG_ARGISNULL(1))
-			missing_ok = PG_GETARG_BOOL(1);
-		if (!PG_ARGISNULL(2))
-			include_dot_dirs = PG_GETARG_BOOL(2);
-	}
-
-	/* check to see if caller supports us returning a tuplestore */
-	if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
-		ereport(ERROR,
-				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-				 errmsg("set-valued function called in context that cannot accept a set")));
-	if (!(rsinfo->allowedModes & SFRM_Materialize))
-		ereport(ERROR,
-				(errcode(ERRCODE_SYNTAX_ERROR),
-				 errmsg("materialize mode required, but it is not allowed in this context")));
-
-	/* The tupdesc and tuplestore must be created in ecxt_per_query_memory */
-	oldcontext = MemoryContextSwitchTo(rsinfo->econtext->ecxt_per_query_memory);
-
-	tupdesc = CreateTemplateTupleDesc(1);
-	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "pg_ls_dir", TEXTOID, -1, 0);
-
-	randomAccess = (rsinfo->allowedModes & SFRM_Materialize_Random) != 0;
-	tupstore = tuplestore_begin_heap(randomAccess, false, work_mem);
-	rsinfo->returnMode = SFRM_Materialize;
-	rsinfo->setResult = tupstore;
-	rsinfo->setDesc = tupdesc;
-
-	MemoryContextSwitchTo(oldcontext);
-
-	dirdesc = AllocateDir(location);
-	if (!dirdesc)
-	{
-		/* Return empty tuplestore if appropriate */
-		if (missing_ok && errno == ENOENT)
-			return (Datum) 0;
-		/* Otherwise, we can let ReadDir() throw the error */
-	}
-
-	while ((de = ReadDir(dirdesc, location)) != NULL)
-	{
-		Datum		values[1];
-		bool		nulls[1];
-
-		if (!include_dot_dirs &&
-			(strcmp(de->d_name, ".") == 0 ||
-			 strcmp(de->d_name, "..") == 0))
-			continue;
-
-		values[0] = CStringGetTextDatum(de->d_name);
-		nulls[0] = false;
-
-		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
-	}
-
-	FreeDir(dirdesc);
-	return (Datum) 0;
+	text	*filename_t = PG_GETARG_TEXT_PP(0);
+	char	*filename = convert_and_check_filename(filename_t);
+	return pg_ls_dir_files(fcinfo, filename, LS_DIR_SKIP_DOT_DIRS);
 }
 
 /*
@@ -561,17 +511,19 @@ pg_ls_dir(PG_FUNCTION_ARGS)
 Datum
 pg_ls_dir_1arg(PG_FUNCTION_ARGS)
 {
-	return pg_ls_dir(fcinfo);
+	text	*filename_t = PG_GETARG_TEXT_PP(0);
+	char	*filename = convert_and_check_filename(filename_t);
+	return pg_ls_dir_files(fcinfo, filename, LS_DIR_SKIP_DOT_DIRS);
 }
 
 /*
  * Generic function to return a directory listing of files.
  *
- * If the directory isn't there, silently return an empty set if missing_ok.
+ * If the directory isn't there, silently return an empty set if MISSING_OK.
  * Other unreadable-directory cases throw an error.
  */
 static Datum
-pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
+pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 {
 	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
 	bool		randomAccess;
@@ -580,6 +532,32 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
 	DIR		   *dirdesc;
 	struct dirent *de;
 	MemoryContext oldcontext;
+	TypeFuncClass	tuptype ;
+
+	/* isdir depends on metadata */
+	Assert(!(flags&LS_DIR_ISDIR) || (flags&LS_DIR_METADATA));
+	/* Unreasonable to show isdir and skip dirs */
+	Assert(!(flags&LS_DIR_ISDIR) || !(flags&LS_DIR_SKIP_DIRS));
+
+	/* check the optional arguments */
+	if (PG_NARGS() == 3)
+	{
+		if (!PG_ARGISNULL(1))
+		{
+			if (PG_GETARG_BOOL(1))
+				flags |= LS_DIR_MISSING_OK;
+			else
+				flags &= ~LS_DIR_MISSING_OK;
+		}
+
+		if (!PG_ARGISNULL(2))
+		{
+			if (PG_GETARG_BOOL(2))
+				flags &= ~LS_DIR_SKIP_DOT_DIRS;
+			else
+				flags |= LS_DIR_SKIP_DOT_DIRS;
+		}
+	}
 
 	/* check to see if caller supports us returning a tuplestore */
 	if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
@@ -594,8 +572,20 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
 	/* The tupdesc and tuplestore must be created in ecxt_per_query_memory */
 	oldcontext = MemoryContextSwitchTo(rsinfo->econtext->ecxt_per_query_memory);
 
-	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
-		elog(ERROR, "return type must be a row type");
+	tuptype = get_call_result_type(fcinfo, NULL, &tupdesc);
+	if (flags & LS_DIR_METADATA)
+	{
+		if (tuptype != TYPEFUNC_COMPOSITE)
+			elog(ERROR, "return type must be a row type");
+	}
+	else
+	{
+		/* pg_ls_dir returns a simple scalar */
+		if (tuptype != TYPEFUNC_SCALAR)
+			elog(ERROR, "return type must be a scalar type");
+		tupdesc = CreateTemplateTupleDesc(1);
+		TupleDescInitEntry(tupdesc, (AttrNumber) 1, "column", TEXTOID, -1, 0);
+	}
 
 	randomAccess = (rsinfo->allowedModes & SFRM_Materialize_Random) != 0;
 	tupstore = tuplestore_begin_heap(randomAccess, false, work_mem);
@@ -614,20 +604,27 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
 	if (!dirdesc)
 	{
 		/* Return empty tuplestore if appropriate */
-		if (missing_ok && errno == ENOENT)
+		if (flags & LS_DIR_MISSING_OK && errno == ENOENT)
 			return (Datum) 0;
 		/* Otherwise, we can let ReadDir() throw the error */
 	}
 
 	while ((de = ReadDir(dirdesc, dir)) != NULL)
 	{
-		Datum		values[3];
-		bool		nulls[3];
+		Datum		values[4];
+		bool		nulls[4];
 		char		path[MAXPGPATH * 2];
 		struct stat attrib;
 
-		/* Skip hidden files */
-		if (de->d_name[0] == '.')
+		/* Skip dot dirs? */
+		if (flags & LS_DIR_SKIP_DOT_DIRS &&
+			(strcmp(de->d_name, ".") == 0 ||
+			 strcmp(de->d_name, "..") == 0))
+			continue;
+
+		/* Skip hidden files? */
+		if (flags & LS_DIR_SKIP_HIDDEN &&
+			de->d_name[0] == '.')
 			continue;
 
 		/* Get the file info */
@@ -642,13 +639,34 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
 					 errmsg("could not stat file \"%s\": %m", path)));
 		}
 
-		/* Ignore anything but regular files */
-		if (!S_ISREG(attrib.st_mode))
-			continue;
+		/* Skip dirs or special files? */
+		if (S_ISDIR(attrib.st_mode))
+		{
+			if (flags & LS_DIR_SKIP_DIRS)
+				continue;
+		}
+		else if (!S_ISREG(attrib.st_mode))
+		{
+			if (flags & LS_DIR_SKIP_SPECIAL)
+				continue;
+		}
 
 		values[0] = CStringGetTextDatum(de->d_name);
-		values[1] = Int64GetDatum((int64) attrib.st_size);
-		values[2] = TimestampTzGetDatum(time_t_to_timestamptz(attrib.st_mtime));
+		if (flags & LS_DIR_METADATA)
+		{
+			values[1] = Int64GetDatum((int64) attrib.st_size);
+			values[2] = TimestampTzGetDatum(time_t_to_timestamptz(attrib.st_mtime));
+			if (flags & LS_DIR_ISDIR)
+			{
+				values[3] = BoolGetDatum(S_ISDIR(attrib.st_mode));
+#ifdef WIN32
+				/* Links should have isdir=false */
+				if (pgwin32_is_junction(path))
+					values[3] = BoolGetDatum(false);
+#endif
+			}
+		}
+
 		memset(nulls, 0, sizeof(nulls));
 
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
@@ -662,14 +680,14 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, bool missing_ok)
 Datum
 pg_ls_logdir(PG_FUNCTION_ARGS)
 {
-	return pg_ls_dir_files(fcinfo, Log_directory, false);
+	return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_HISTORIC);
 }
 
 /* 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, false);
+	return pg_ls_dir_files(fcinfo, XLOGDIR, LS_DIR_HISTORIC);
 }
 
 /*
@@ -687,7 +705,8 @@ pg_ls_tmpdir(FunctionCallInfo fcinfo, Oid tblspc)
 						tblspc)));
 
 	TempTablespacePath(path, tblspc);
-	return pg_ls_dir_files(fcinfo, path, true);
+	return pg_ls_dir_files(fcinfo, path,
+			LS_DIR_HISTORIC | LS_DIR_MISSING_OK);
 }
 
 /*
@@ -716,5 +735,33 @@ pg_ls_tmpdir_1arg(PG_FUNCTION_ARGS)
 Datum
 pg_ls_archive_statusdir(PG_FUNCTION_ARGS)
 {
-	return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status", true);
+	return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status",
+			LS_DIR_HISTORIC | LS_DIR_MISSING_OK);
+}
+
+/*
+ * Return the list of files and metadata in an arbitrary directory.
+ */
+Datum
+pg_ls_dir_metadata(PG_FUNCTION_ARGS)
+{
+	char	*dirname = convert_and_check_filename(PG_GETARG_TEXT_PP(0));
+
+	return pg_ls_dir_files(fcinfo, dirname,
+			LS_DIR_METADATA | LS_DIR_SKIP_SPECIAL | LS_DIR_ISDIR);
+}
+
+/*
+ * Return the list of files and metadata in an arbitrary directory.
+ * note: this wrapper is necessary to pass the sanity check in opr_sanity,
+ * which checks that all built-in functions that share the implementing C
+ * function take the same number of arguments.
+ */
+Datum
+pg_ls_dir_metadata_1arg(PG_FUNCTION_ARGS)
+{
+	char	*dirname = convert_and_check_filename(PG_GETARG_TEXT_PP(0));
+
+	return pg_ls_dir_files(fcinfo, dirname,
+			LS_DIR_METADATA | LS_DIR_SKIP_SPECIAL | LS_DIR_ISDIR);
 }
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fc2202b843..8e88c549d9 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10976,6 +10976,18 @@
   proallargtypes => '{oid,text,int8,timestamptz}', proargmodes => '{i,o,o,o}',
   proargnames => '{tablespace,name,size,modification}',
   prosrc => 'pg_ls_tmpdir_1arg' },
+{ oid => '9979', descr => 'list directory with metadata',
+  proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
+  provolatile => 'v', prorettype => 'record', proargtypes => 'text bool bool',
+  proallargtypes => '{text,bool,bool,text,int8,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o}',
+  proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,modification,isdir}',
+  prosrc => 'pg_ls_dir_metadata' },
+{ oid => '9980', descr => 'list directory with metadata',
+  proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
+  provolatile => 'v', prorettype => 'record', proargtypes => 'text',
+  proallargtypes => '{text,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}',
+  proargnames => '{dirname,filename,size,modification,isdir}',
+  prosrc => 'pg_ls_dir_metadata_1arg' },
 
 # hash partitioning constraint function
 { oid => '5028', descr => 'hash partition CHECK constraint',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index edbfd9abc1..fd7c3c791f 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -225,6 +225,30 @@ select * from pg_stat_file('.') limit 0;
 ------+--------+--------------+--------+----------+-------
 (0 rows)
 
+-- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+select * from pg_ls_tmpdir() where name='Does not exist';
+ name | size | modification 
+------+------+--------------
+(0 rows)
+
+select filename, isdir from pg_ls_dir_metadata('.') where filename='.';
+ filename | isdir 
+----------+-------
+ .        | t
+(1 row)
+
+select filename, isdir from pg_ls_dir_metadata('.', false, false) where filename='.'; -- include_dot_dirs=false
+ filename | isdir 
+----------+-------
+(0 rows)
+
+-- Check that expected columns are present
+select * from pg_ls_dir_metadata('.') limit 0;
+ filename | size | modification | isdir 
+----------+------+--------------+-------
+(0 rows)
+
 --
 -- Test adding a support function to a subject function
 --
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index a5f61a35dc..0b9cfe615e 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -11,6 +11,11 @@ DROP TABLESPACE regress_tblspacewith;
 -- create a tablespace we can use
 CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
 
+-- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+-- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
+SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
+
 -- try setting and resetting some properties for the new tablespace
 ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
 ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true);  -- fail
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 162b591b31..a42714bf40 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -13,6 +13,14 @@ SELECT spcoptions FROM pg_tablespace WHERE spcname = 'regress_tblspacewith';
 DROP TABLESPACE regress_tblspacewith;
 -- create a tablespace we can use
 CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
+-- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+-- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
+SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
+ name | size | modification 
+------+------+--------------
+(0 rows)
+
 -- try setting and resetting some properties for the new tablespace
 ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
 ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true);  -- fail
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index b32a3a4b74..64d9d70874 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -68,6 +68,17 @@ select pg_ls_dir('does not exist'); -- fails with missingok=false
 -- Check that expected columns are present
 select * from pg_stat_file('.') limit 0;
 
+-- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+select * from pg_ls_tmpdir() where name='Does not exist';
+
+select filename, isdir from pg_ls_dir_metadata('.') where filename='.';
+
+select filename, isdir from pg_ls_dir_metadata('.', false, false) where filename='.'; -- include_dot_dirs=false
+
+-- Check that expected columns are present
+select * from pg_ls_dir_metadata('.') limit 0;
+
 --
 -- Test adding a support function to a subject function
 --
-- 
2.17.0


>From bbbf870852824f7d582cf8fef5fe95263d9473c6 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 8 Mar 2020 22:57:54 -0500
Subject: [PATCH v25 05/11] pg_ls_tmpdir to show directories and "isdir"
 argument..

similar to pg_stat_file().

It's worth breaking the function's return type, since core postgres creates
"shared filesets" underneath the temp dirs, and it's unreasonable to not show
them here, and the alternative query to show them is unreasaonably complicated.

See following commit which also adds these columns to the other pg_ls_*
functions.  Although I don't think it matters that they're easily UNIONed, it'd
still make great sense if they returned the same columns.

Need catversion bump
---
 doc/src/sgml/func.sgml                       | 17 +++++++++--------
 src/backend/utils/adt/genfile.c              |  4 ++--
 src/include/catalog/pg_proc.dat              |  8 ++++----
 src/test/regress/expected/misc_functions.out |  4 ++--
 src/test/regress/output/tablespace.source    |  4 ++--
 5 files changed, 19 insertions(+), 18 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 226923a134..4fb4ec9f33 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25829,16 +25829,17 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>name</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
-        <parameter>modification</parameter> <type>timestamp with time zone</type> )
+        <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
-        Returns the name, size, and last modification time (mtime) of each
-        ordinary file in the temporary file directory for the
-        specified <parameter>tablespace</parameter>.
-        If <parameter>tablespace</parameter> is not provided,
-        the <literal>pg_default</literal> tablespace is examined.  Filenames
-        beginning with a dot, directories, and other special files are
-        excluded.
+        For each file in the temporary directory within the given
+        <parameter>tablespace</parameter>, return the file's name, size, last
+        modification time (mtime) and a boolean indicating if the file is a directory.
+        Directories are used for temporary files shared by parallel processes.
+        If <parameter>tablespace</parameter> is not provided, the
+        <literal>pg_default</literal> tablespace is examined.
+        Filenames beginning with a dot and special file types are excluded.
        </para>
        <para>
         This function is restricted to superusers and members of
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 7351615f8a..da7d4a7c11 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -517,7 +517,7 @@ pg_ls_dir_1arg(PG_FUNCTION_ARGS)
 }
 
 /*
- * Generic function to return a directory listing of files.
+ * Generic function to return a directory listing of files (and optionally dirs).
  *
  * If the directory isn't there, silently return an empty set if MISSING_OK.
  * Other unreadable-directory cases throw an error.
@@ -706,7 +706,7 @@ pg_ls_tmpdir(FunctionCallInfo fcinfo, Oid tblspc)
 
 	TempTablespacePath(path, tblspc);
 	return pg_ls_dir_files(fcinfo, path,
-			LS_DIR_HISTORIC | LS_DIR_MISSING_OK);
+			LS_DIR_SKIP_HIDDEN | LS_DIR_SKIP_SPECIAL | LS_DIR_ISDIR | LS_DIR_METADATA | LS_DIR_MISSING_OK);
 }
 
 /*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 8e88c549d9..a0148e6130 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10968,13 +10968,13 @@
 { oid => '5029', descr => 'list files in the pgsql_tmp directory',
   proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
-  proargnames => '{name,size,modification}', prosrc => 'pg_ls_tmpdir_noargs' },
+  proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}',
+  proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_tmpdir_noargs' },
 { oid => '5030', descr => 'list files in the pgsql_tmp directory',
   proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => 'oid',
-  proallargtypes => '{oid,text,int8,timestamptz}', proargmodes => '{i,o,o,o}',
-  proargnames => '{tablespace,name,size,modification}',
+  proallargtypes => '{oid,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}',
+  proargnames => '{tablespace,name,size,modification,isdir}',
   prosrc => 'pg_ls_tmpdir_1arg' },
 { oid => '9979', descr => 'list directory with metadata',
   proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index fd7c3c791f..7279c83d89 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -228,8 +228,8 @@ select * from pg_stat_file('.') limit 0;
 -- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 select * from pg_ls_tmpdir() where name='Does not exist';
- name | size | modification 
-------+------+--------------
+ name | size | modification | isdir 
+------+------+--------------+-------
 (0 rows)
 
 select filename, isdir from pg_ls_dir_metadata('.') where filename='.';
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index a42714bf40..1c88e914e3 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -17,8 +17,8 @@ CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 -- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
 SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
- name | size | modification 
-------+------+--------------
+ name | size | modification | isdir 
+------+------+--------------+-------
 (0 rows)
 
 -- try setting and resetting some properties for the new tablespace
-- 
2.17.0


>From 8b7914717e25e6b01d6fe518d68d9e502c6e2746 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 9 Mar 2020 01:00:42 -0500
Subject: [PATCH v25 06/11] pg_ls_*dir to show directories and "isdir" column..

pg_ls_logdir, pg_ls_waldir, pg_ls_archive_statusdir

Need catversion bump
---
 doc/src/sgml/func.sgml                       | 36 ++++++++++++--------
 src/backend/utils/adt/genfile.c              | 15 ++++----
 src/include/catalog/pg_proc.dat              | 12 +++----
 src/test/regress/expected/misc_functions.out |  4 +--
 4 files changed, 35 insertions(+), 32 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4fb4ec9f33..c4a673c8eb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25736,12 +25736,14 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>name</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
-        <parameter>modification</parameter> <type>timestamp with time zone</type> )
+        <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
-        Returns the name, size, and last modification time (mtime) of each
-        ordinary file in the server's log directory.  Filenames beginning with
-        a dot, directories, and other special files are excluded.
+        For each file in the server's log directory,
+        return the file's name, size, last modification time (mtime), and a boolean
+        indicating if the file is a directory.
+        Filenames beginning with a dot and special file types are excluded.
        </para>
        <para>
         This function is restricted to superusers and members of
@@ -25761,7 +25763,8 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>filename</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
-        <parameter>modification</parameter> <type>timestamp with time zone</type> )
+        <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
         For each file in the specified directory, list the file and its
@@ -25780,13 +25783,14 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>name</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
-        <parameter>modification</parameter> <type>timestamp with time zone</type> )
+        <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
-        Returns the name, size, and last modification time (mtime) of each
-        ordinary file in the server's write-ahead log (WAL) directory.
-        Filenames beginning with a dot, directories, and other special files
-        are excluded.
+        For each file in the server's write-ahead log (WAL) directory, list the
+        file's name, size, last modification time (mtime), and a boolean
+        indicating if the file is a directory.
+        Filenames beginning with a dot and special files types are excluded.
        </para>
        <para>
         This function is restricted to superusers and members of
@@ -25804,13 +25808,15 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>name</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
-        <parameter>modification</parameter> <type>timestamp with time zone</type> )
+        <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
-        Returns the name, size, and last modification time (mtime) of each
-        ordinary file in the server's WAL archive status directory
-        (<filename>pg_wal/archive_status</filename>).  Filenames beginning
-        with a dot, directories, and other special files are excluded.
+        For each file in the server's WAL archive status directory
+        (<filename>pg_wal/archive_status</filename>), list the file's
+        name, size, last modification time (mtime), and a boolean indicating if
+        the file is a directory.
+        Filenames beginning with a dot and special file types are excluded.
        </para>
        <para>
         This function is restricted to superusers and members of
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index da7d4a7c11..d21a95aebe 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -46,11 +46,8 @@ static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags
 #define	LS_DIR_SKIP_DIRS			(1<<5) /* Do not show directories */
 #define	LS_DIR_SKIP_SPECIAL			(1<<6) /* Do not show special file types */
 
-/*
- * Shortcut for the historic behavior of the pg_ls_* functions (not including
- * pg_ls_dir, which skips different files and doesn't show metadata).
- */
-#define LS_DIR_HISTORIC				(LS_DIR_SKIP_DIRS | LS_DIR_SKIP_HIDDEN | LS_DIR_SKIP_SPECIAL | LS_DIR_METADATA)
+/* Shortcut for common behavior */
+#define LS_DIR_COMMON				(LS_DIR_SKIP_HIDDEN | LS_DIR_SKIP_SPECIAL | LS_DIR_METADATA)
 
 /*
  * Convert a "text" filename argument to C string, and check it's allowable.
@@ -680,14 +677,14 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 Datum
 pg_ls_logdir(PG_FUNCTION_ARGS)
 {
-	return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_HISTORIC);
+	return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_COMMON);
 }
 
 /* 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, LS_DIR_HISTORIC);
+	return pg_ls_dir_files(fcinfo, XLOGDIR, LS_DIR_COMMON);
 }
 
 /*
@@ -706,7 +703,7 @@ pg_ls_tmpdir(FunctionCallInfo fcinfo, Oid tblspc)
 
 	TempTablespacePath(path, tblspc);
 	return pg_ls_dir_files(fcinfo, path,
-			LS_DIR_SKIP_HIDDEN | LS_DIR_SKIP_SPECIAL | LS_DIR_ISDIR | LS_DIR_METADATA | LS_DIR_MISSING_OK);
+			LS_DIR_COMMON | LS_DIR_MISSING_OK);
 }
 
 /*
@@ -736,7 +733,7 @@ Datum
 pg_ls_archive_statusdir(PG_FUNCTION_ARGS)
 {
 	return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status",
-			LS_DIR_HISTORIC | LS_DIR_MISSING_OK);
+			LS_DIR_COMMON | LS_DIR_MISSING_OK);
 }
 
 /*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a0148e6130..fc6402d19b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10952,18 +10952,18 @@
 { oid => '3353', descr => 'list files in the log directory',
   proname => 'pg_ls_logdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
-  proargnames => '{name,size,modification}', prosrc => 'pg_ls_logdir' },
+  proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}',
+  proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_logdir' },
 { oid => '3354', descr => 'list of files in the WAL directory',
   proname => 'pg_ls_waldir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
-  proargnames => '{name,size,modification}', prosrc => 'pg_ls_waldir' },
+  proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}',
+  proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_waldir' },
 { oid => '5031', descr => 'list of files in the archive_status directory',
   proname => 'pg_ls_archive_statusdir', procost => '10', prorows => '20',
   proretset => 't', provolatile => 'v', prorettype => 'record',
-  proargtypes => '', proallargtypes => '{text,int8,timestamptz}',
-  proargmodes => '{o,o,o}', proargnames => '{name,size,modification}',
+  proargtypes => '', proallargtypes => '{text,int8,timestamptz,bool}',
+  proargmodes => '{o,o,o,o}', proargnames => '{name,size,modification,isdir}',
   prosrc => 'pg_ls_archive_statusdir' },
 { oid => '5029', descr => 'list files in the pgsql_tmp directory',
   proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 7279c83d89..800d12b4b6 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -157,8 +157,8 @@ select count(*) > 0 as ok from (select pg_ls_waldir()) ss;
 
 -- Test not-run-to-completion cases.
 select * from pg_ls_waldir() limit 0;
- name | size | modification 
-------+------+--------------
+ name | size | modification | isdir 
+------+------+--------------+-------
 (0 rows)
 
 select count(*) > 0 as ok from (select * from pg_ls_waldir() limit 1) ss;
-- 
2.17.0


>From 94a0f74730991359c0782f3bde7642ad3cea21e2 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Fri, 6 Mar 2020 17:23:51 -0600
Subject: [PATCH v25 07/11] pg_ls_logdir to ignore error if initial/top dir is
 missing..

..since ./log is created dynamically and not by initdb
---
 src/backend/utils/adt/genfile.c           | 2 +-
 src/test/regress/input/tablespace.source  | 4 ++++
 src/test/regress/output/tablespace.source | 7 +++++++
 3 files changed, 12 insertions(+), 1 deletion(-)

diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index d21a95aebe..07fce2a9bc 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -677,7 +677,7 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 Datum
 pg_ls_logdir(PG_FUNCTION_ARGS)
 {
-	return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_COMMON);
+	return pg_ls_dir_files(fcinfo, Log_directory, LS_DIR_COMMON | LS_DIR_MISSING_OK);
 }
 
 /* Function to return the list of files in the WAL directory */
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 0b9cfe615e..2a1268e17c 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -16,6 +16,10 @@ CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
 -- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
 SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
 
+-- This tests the missing_ok parameter.  If that's not functioning, this would ERROR if the logdir doesn't exist yet.
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+SELECT * FROM pg_ls_logdir() WHERE name='Does not exist';
+
 -- try setting and resetting some properties for the new tablespace
 ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
 ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true);  -- fail
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 1c88e914e3..ba9a3fe29a 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -21,6 +21,13 @@ SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspa
 ------+------+--------------+-------
 (0 rows)
 
+-- This tests the missing_ok parameter.  If that's not functioning, this would ERROR if the logdir doesn't exist yet.
+-- The name='' condition is never true, so the function runs to completion but returns zero rows.
+SELECT * FROM pg_ls_logdir() WHERE name='Does not exist';
+ name | size | modification | isdir 
+------+------+--------------+-------
+(0 rows)
+
 -- try setting and resetting some properties for the new tablespace
 ALTER TABLESPACE regress_tblspace SET (random_page_cost = 1.0, seq_page_cost = 1.1);
 ALTER TABLESPACE regress_tblspace SET (some_nonexistent_parameter = true);  -- fail
-- 
2.17.0


>From 11b2e087fa0a3c697f6f5e87f3d65b2a4d2d8157 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 9 Mar 2020 21:56:21 -0500
Subject: [PATCH v25 08/11] pg_ls_*dir to return all the metadata from
 pg_stat_file..

..but it doesn't seem worth factoring out the common bits, since stat_file
doesn't return a name, so all the field numbers are off by one.

NOTE, the atime is now shown where the mtime used to be.

Need catversion bump
---
 doc/src/sgml/func.sgml                       | 34 +++++++---
 src/backend/utils/adt/genfile.c              | 71 +++++++++-----------
 src/include/catalog/pg_proc.dat              | 28 ++++----
 src/test/regress/expected/misc_functions.out | 12 ++--
 src/test/regress/output/tablespace.source    |  8 +--
 5 files changed, 81 insertions(+), 72 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c4a673c8eb..f294730605 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25736,13 +25736,16 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>name</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
+        <parameter>access</parameter> <type>timestamp with time zone</type>,
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>change</parameter> <type>timestamp with time zone</type>,
+        <parameter>creation</parameter> <type>timestamp with time zone</type>,
         <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
         For each file in the server's log directory,
-        return the file's name, size, last modification time (mtime), and a boolean
-        indicating if the file is a directory.
+        return the file's name, along with the metadata columns returned by
+        <function>pg_stat_file</function>.
         Filenames beginning with a dot and special file types are excluded.
        </para>
        <para>
@@ -25763,7 +25766,10 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>filename</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
+        <parameter>access</parameter> <type>timestamp with time zone</type>,
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>change</parameter> <type>timestamp with time zone</type>,
+        <parameter>creation</parameter> <type>timestamp with time zone</type>,
         <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
@@ -25783,13 +25789,16 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>name</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
+        <parameter>access</parameter> <type>timestamp with time zone</type>,
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>change</parameter> <type>timestamp with time zone</type>,
+        <parameter>creation</parameter> <type>timestamp with time zone</type>,
         <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
         For each file in the server's write-ahead log (WAL) directory, list the
-        file's name, size, last modification time (mtime), and a boolean
-        indicating if the file is a directory.
+        file's name along with the metadata columns returned by
+        <function>pg_stat_file</function>.
         Filenames beginning with a dot and special files types are excluded.
        </para>
        <para>
@@ -25808,14 +25817,17 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>name</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
+        <parameter>access</parameter> <type>timestamp with time zone</type>,
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>change</parameter> <type>timestamp with time zone</type>,
+        <parameter>creation</parameter> <type>timestamp with time zone</type>,
         <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
         For each file in the server's WAL archive status directory
-        (<filename>pg_wal/archive_status</filename>), list the file's
-        name, size, last modification time (mtime), and a boolean indicating if
-        the file is a directory.
+        (<filename>pg_wal/archive_status</filename>), list the file's name
+        along with the metadata columns returned by
+        <function>pg_stat_file</function>.
         Filenames beginning with a dot and special file types are excluded.
        </para>
        <para>
@@ -25835,13 +25847,17 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <returnvalue>setof record</returnvalue>
         ( <parameter>name</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
+        <parameter>access</parameter> <type>timestamp with time zone</type>,
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
+        <parameter>change</parameter> <type>timestamp with time zone</type>,
+        <parameter>creation</parameter> <type>timestamp with time zone</type>,
         <parameter>isdir</parameter> <type>boolean</type> )
        </para>
        <para>
         For each file in the temporary directory within the given
-        <parameter>tablespace</parameter>, return the file's name, size, last
-        modification time (mtime) and a boolean indicating if the file is a directory.
+        <parameter>tablespace</parameter>, list the file's name
+        along with the metadata columns returned by
+        <function>pg_stat_file</function>.
         Directories are used for temporary files shared by parallel processes.
         If <parameter>tablespace</parameter> is not provided, the
         <literal>pg_default</literal> tablespace is examined.
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 07fce2a9bc..9ff06f4158 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -36,6 +36,8 @@
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
 
+static void tuple_from_stat(struct stat *fst, const char *path, Datum *values,
+		bool *nulls);
 static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags);
 
 #define	LS_DIR_ISDIR				(1<<0) /* Show column: isdir */
@@ -397,6 +399,28 @@ pg_read_binary_file_all(PG_FUNCTION_ARGS)
 	return pg_read_binary_file(fcinfo);
 }
 
+/*
+ * Populate values and nulls from fst and path.
+ * Used for pg_stat_file() and pg_ls_dir_files()
+ * nulls is assumed to have been zerod.
+ */
+static void
+tuple_from_stat(struct stat *fst, const char *path, Datum *values, bool *nulls)
+{
+	values[0] = Int64GetDatum((int64) fst->st_size);
+	values[1] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_atime));
+	values[2] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_mtime));
+	/* Unix has file status change time, while Win32 has creation time */
+#if !defined(WIN32) && !defined(__CYGWIN__)
+	values[3] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_ctime));
+	nulls[4] = true;
+#else
+	nulls[3] = true;
+	values[4] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_ctime));
+#endif
+	values[5] = BoolGetDatum(S_ISDIR(fst->st_mode));
+}
+
 /*
  * stat a file
  */
@@ -407,7 +431,7 @@ pg_stat_file(PG_FUNCTION_ARGS)
 	char	   *filename;
 	struct stat fst;
 	Datum		values[6];
-	bool		isnull[6];
+	bool		nulls[6];
 	HeapTuple	tuple;
 	TupleDesc	tupdesc;
 	bool		missing_ok = false;
@@ -447,27 +471,9 @@ pg_stat_file(PG_FUNCTION_ARGS)
 					   "isdir", BOOLOID, -1, 0);
 	BlessTupleDesc(tupdesc);
 
-	memset(isnull, false, sizeof(isnull));
-
-	values[0] = Int64GetDatum((int64) fst.st_size);
-	values[1] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_atime));
-	values[2] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_mtime));
-	/* Unix has file status change time, while Win32 has creation time */
-#if !defined(WIN32) && !defined(__CYGWIN__)
-	values[3] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_ctime));
-	isnull[4] = true;
-#else
-	isnull[3] = true;
-	values[4] = TimestampTzGetDatum(time_t_to_timestamptz(fst.st_ctime));
-#endif
-	values[5] = BoolGetDatum(S_ISDIR(fst.st_mode));
-#ifdef WIN32
-	/* Links should have isdir=false */
-	if (pgwin32_is_junction(filename))
-		values[5] = BoolGetDatum(false);
-#endif
-
-	tuple = heap_form_tuple(tupdesc, values, isnull);
+	memset(nulls, false, sizeof(nulls));
+	tuple_from_stat(&fst, filename, values, nulls);
+	tuple = heap_form_tuple(tupdesc, values, nulls);
 
 	pfree(filename);
 
@@ -608,8 +614,8 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 
 	while ((de = ReadDir(dirdesc, dir)) != NULL)
 	{
-		Datum		values[4];
-		bool		nulls[4];
+		Datum		values[7];
+		bool		nulls[7];
 		char		path[MAXPGPATH * 2];
 		struct stat attrib;
 
@@ -648,23 +654,10 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 				continue;
 		}
 
+		memset(nulls, false, sizeof(nulls));
 		values[0] = CStringGetTextDatum(de->d_name);
 		if (flags & LS_DIR_METADATA)
-		{
-			values[1] = Int64GetDatum((int64) attrib.st_size);
-			values[2] = TimestampTzGetDatum(time_t_to_timestamptz(attrib.st_mtime));
-			if (flags & LS_DIR_ISDIR)
-			{
-				values[3] = BoolGetDatum(S_ISDIR(attrib.st_mode));
-#ifdef WIN32
-				/* Links should have isdir=false */
-				if (pgwin32_is_junction(path))
-					values[3] = BoolGetDatum(false);
-#endif
-			}
-		}
-
-		memset(nulls, 0, sizeof(nulls));
+			tuple_from_stat(&attrib, path, 1+values, 1+nulls);
 
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
 	}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fc6402d19b..3f43958423 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10952,41 +10952,41 @@
 { oid => '3353', descr => 'list files in the log directory',
   proname => 'pg_ls_logdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}',
-  proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_logdir' },
+  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}',
+  proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_logdir' },
 { oid => '3354', descr => 'list of files in the WAL directory',
   proname => 'pg_ls_waldir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}',
-  proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_waldir' },
+  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}',
+  proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_waldir' },
 { oid => '5031', descr => 'list of files in the archive_status directory',
   proname => 'pg_ls_archive_statusdir', procost => '10', prorows => '20',
   proretset => 't', provolatile => 'v', prorettype => 'record',
-  proargtypes => '', proallargtypes => '{text,int8,timestamptz,bool}',
-  proargmodes => '{o,o,o,o}', proargnames => '{name,size,modification,isdir}',
+  proargtypes => '', proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}',
+  proargmodes => '{o,o,o,o,o,o,o}', proargnames => '{name,size,access,modification,change,creation,isdir}',
   prosrc => 'pg_ls_archive_statusdir' },
 { oid => '5029', descr => 'list files in the pgsql_tmp directory',
   proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{text,int8,timestamptz,bool}', proargmodes => '{o,o,o,o}',
-  proargnames => '{name,size,modification,isdir}', prosrc => 'pg_ls_tmpdir_noargs' },
+  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}',
+  proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_tmpdir_noargs' },
 { oid => '5030', descr => 'list files in the pgsql_tmp directory',
   proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => 'oid',
-  proallargtypes => '{oid,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}',
-  proargnames => '{tablespace,name,size,modification,isdir}',
+  proallargtypes => '{oid,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,o,o,o,o,o,o,o}',
+  proargnames => '{tablespace,name,size,access,modification,change,creation,isdir}',
   prosrc => 'pg_ls_tmpdir_1arg' },
 { oid => '9979', descr => 'list directory with metadata',
   proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => 'text bool bool',
-  proallargtypes => '{text,bool,bool,text,int8,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o}',
-  proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,modification,isdir}',
+  proallargtypes => '{text,bool,bool,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o,o,o,o}',
+  proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,access,modification,change,creation,isdir}',
   prosrc => 'pg_ls_dir_metadata' },
 { oid => '9980', descr => 'list directory with metadata',
   proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => 'text',
-  proallargtypes => '{text,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}',
-  proargnames => '{dirname,filename,size,modification,isdir}',
+  proallargtypes => '{text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,o,o,o,o,o,o,o}',
+  proargnames => '{dirname,filename,size,access,modification,change,creation,isdir}',
   prosrc => 'pg_ls_dir_metadata_1arg' },
 
 # hash partitioning constraint function
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 800d12b4b6..1f4106cd9c 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -157,8 +157,8 @@ select count(*) > 0 as ok from (select pg_ls_waldir()) ss;
 
 -- Test not-run-to-completion cases.
 select * from pg_ls_waldir() limit 0;
- name | size | modification | isdir 
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir 
+------+------+--------+--------------+--------+----------+-------
 (0 rows)
 
 select count(*) > 0 as ok from (select * from pg_ls_waldir() limit 1) ss;
@@ -228,8 +228,8 @@ select * from pg_stat_file('.') limit 0;
 -- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 select * from pg_ls_tmpdir() where name='Does not exist';
- name | size | modification | isdir 
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir 
+------+------+--------+--------------+--------+----------+-------
 (0 rows)
 
 select filename, isdir from pg_ls_dir_metadata('.') where filename='.';
@@ -245,8 +245,8 @@ select filename, isdir from pg_ls_dir_metadata('.', false, false) where filename
 
 -- Check that expected columns are present
 select * from pg_ls_dir_metadata('.') limit 0;
- filename | size | modification | isdir 
-----------+------+--------------+-------
+ filename | size | access | modification | change | creation | isdir 
+----------+------+--------+--------------+--------+----------+-------
 (0 rows)
 
 --
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index ba9a3fe29a..1e1e02b589 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -17,15 +17,15 @@ CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 -- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
 SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
- name | size | modification | isdir 
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir 
+------+------+--------+--------------+--------+----------+-------
 (0 rows)
 
 -- This tests the missing_ok parameter.  If that's not functioning, this would ERROR if the logdir doesn't exist yet.
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 SELECT * FROM pg_ls_logdir() WHERE name='Does not exist';
- name | size | modification | isdir 
-------+------+--------------+-------
+ name | size | access | modification | change | creation | isdir 
+------+------+--------+--------------+--------+----------+-------
 (0 rows)
 
 -- try setting and resetting some properties for the new tablespace
-- 
2.17.0


>From d605c7f32e53a4b7131145a1f65b926534afe5eb Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 31 Mar 2020 14:40:34 -0500
Subject: [PATCH v25 09/11] pg_ls_*/pg_stat_file to show file *type*..

..not just "isdir"

Also show special file types, now that their type is shown.
---
 doc/src/sgml/func.sgml                       | 26 +++++----
 src/backend/utils/adt/genfile.c              | 58 ++++++++++++++++----
 src/include/catalog/pg_proc.dat              | 36 ++++++------
 src/test/regress/expected/misc_functions.out | 30 +++++-----
 src/test/regress/output/tablespace.source    |  8 +--
 src/test/regress/sql/misc_functions.sql      |  4 +-
 6 files changed, 101 insertions(+), 61 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f294730605..cd4375f75b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25740,13 +25740,13 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
         <parameter>change</parameter> <type>timestamp with time zone</type>,
         <parameter>creation</parameter> <type>timestamp with time zone</type>,
-        <parameter>isdir</parameter> <type>boolean</type> )
+        <parameter>type</parameter> <type>char</type> )
        </para>
        <para>
         For each file in the server's log directory,
         return the file's name, along with the metadata columns returned by
         <function>pg_stat_file</function>.
-        Filenames beginning with a dot and special file types are excluded.
+        Filenames beginning with a dot are excluded.
        </para>
        <para>
         This function is restricted to superusers and members of
@@ -25770,7 +25770,7 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
         <parameter>change</parameter> <type>timestamp with time zone</type>,
         <parameter>creation</parameter> <type>timestamp with time zone</type>,
-        <parameter>isdir</parameter> <type>boolean</type> )
+        <parameter>type</parameter> <type>char</type> )
        </para>
        <para>
         For each file in the specified directory, list the file and its
@@ -25793,13 +25793,13 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
         <parameter>change</parameter> <type>timestamp with time zone</type>,
         <parameter>creation</parameter> <type>timestamp with time zone</type>,
-        <parameter>isdir</parameter> <type>boolean</type> )
+        <parameter>type</parameter> <type>char</type> )
        </para>
        <para>
         For each file in the server's write-ahead log (WAL) directory, list the
         file's name along with the metadata columns returned by
         <function>pg_stat_file</function>.
-        Filenames beginning with a dot and special files types are excluded.
+        Filenames beginning with a dot are excluded.
        </para>
        <para>
         This function is restricted to superusers and members of
@@ -25821,14 +25821,14 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
         <parameter>change</parameter> <type>timestamp with time zone</type>,
         <parameter>creation</parameter> <type>timestamp with time zone</type>,
-        <parameter>isdir</parameter> <type>boolean</type> )
+        <parameter>type</parameter> <type>char</type> )
        </para>
        <para>
         For each file in the server's WAL archive status directory
         (<filename>pg_wal/archive_status</filename>), list the file's name
         along with the metadata columns returned by
         <function>pg_stat_file</function>.
-        Filenames beginning with a dot and special file types are excluded.
+        Filenames beginning with a dot are excluded.
        </para>
        <para>
         This function is restricted to superusers and members of
@@ -25851,7 +25851,7 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
         <parameter>change</parameter> <type>timestamp with time zone</type>,
         <parameter>creation</parameter> <type>timestamp with time zone</type>,
-        <parameter>isdir</parameter> <type>boolean</type> )
+        <parameter>type</parameter> <type>char</type> )
        </para>
        <para>
         For each file in the temporary directory within the given
@@ -25861,7 +25861,7 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         Directories are used for temporary files shared by parallel processes.
         If <parameter>tablespace</parameter> is not provided, the
         <literal>pg_default</literal> tablespace is examined.
-        Filenames beginning with a dot and special file types are excluded.
+        Filenames beginning with a dot are excluded.
        </para>
        <para>
         This function is restricted to superusers and members of
@@ -25935,13 +25935,15 @@ SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
         <parameter>change</parameter> <type>timestamp with time zone</type>,
         <parameter>creation</parameter> <type>timestamp with time zone</type>,
-        <parameter>isdir</parameter> <type>boolean</type> )
+        <parameter>type</parameter> <type>char</type> )
        </para>
        <para>
         Returns a record containing the file's size, last access time stamp,
         last modification time stamp, last file status change time stamp (Unix
-        platforms only), file creation time stamp (Windows only), and a flag
-        indicating if it is a directory.
+        platforms only), file creation time stamp (Windows only), and a
+        character representing the file's type: regular (-), directory (d), link
+        or junction (l), character device (c), block device (b), fifo (p),
+        socket (s) or other/unknown (?).
        </para>
        <para>
         This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 9ff06f4158..6e8898029f 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -36,11 +36,12 @@
 #include "utils/syscache.h"
 #include "utils/timestamp.h"
 
+static char get_file_type(mode_t mode, const char *path);
 static void tuple_from_stat(struct stat *fst, const char *path, Datum *values,
 		bool *nulls);
 static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags);
 
-#define	LS_DIR_ISDIR				(1<<0) /* Show column: isdir */
+#define	LS_DIR_TYPE					(1<<0) /* Show column: type */
 #define	LS_DIR_METADATA				(1<<1) /* Show columns: mtime, size */
 #define	LS_DIR_MISSING_OK			(1<<2) /* Ignore ENOENT if the toplevel dir is missing */
 #define	LS_DIR_SKIP_DOT_DIRS		(1<<3) /* Do not show . or .. */
@@ -49,7 +50,7 @@ static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags
 #define	LS_DIR_SKIP_SPECIAL			(1<<6) /* Do not show special file types */
 
 /* Shortcut for common behavior */
-#define LS_DIR_COMMON				(LS_DIR_SKIP_HIDDEN | LS_DIR_SKIP_SPECIAL | LS_DIR_METADATA)
+#define LS_DIR_COMMON				(LS_DIR_SKIP_HIDDEN | LS_DIR_METADATA)
 
 /*
  * Convert a "text" filename argument to C string, and check it's allowable.
@@ -399,6 +400,43 @@ pg_read_binary_file_all(PG_FUNCTION_ARGS)
 	return pg_read_binary_file(fcinfo);
 }
 
+/* Return a character indicating the type of file, or '?' if unknown type */
+static char
+get_file_type(mode_t mode, const char *path)
+{
+	if (S_ISREG(mode))
+		return '-';
+
+	if (S_ISDIR(mode))
+		return 'd';
+#ifndef WIN32
+	if (S_ISLNK(mode))
+		return 'l';
+#else
+	if (pgwin32_is_junction(path))
+		return 'l';
+#endif
+
+#ifdef S_ISCHR
+	if (S_ISCHR(mode))
+		return 'c';
+#endif
+#ifdef S_ISBLK
+	if (S_ISBLK(mode))
+		return 'b';
+#endif
+#ifdef S_ISFIFO
+	if (S_ISFIFO(mode))
+		return 'p';
+#endif
+#ifdef S_ISSOCK
+	if (S_ISSOCK(mode))
+		return 's';
+#endif
+
+	return '?';
+}
+
 /*
  * Populate values and nulls from fst and path.
  * Used for pg_stat_file() and pg_ls_dir_files()
@@ -418,7 +456,7 @@ tuple_from_stat(struct stat *fst, const char *path, Datum *values, bool *nulls)
 	nulls[3] = true;
 	values[4] = TimestampTzGetDatum(time_t_to_timestamptz(fst->st_ctime));
 #endif
-	values[5] = BoolGetDatum(S_ISDIR(fst->st_mode));
+	values[5] = CharGetDatum(get_file_type(fst->st_mode, path));
 }
 
 /*
@@ -468,7 +506,7 @@ pg_stat_file(PG_FUNCTION_ARGS)
 	TupleDescInitEntry(tupdesc, (AttrNumber) 5,
 					   "creation", TIMESTAMPTZOID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 6,
-					   "isdir", BOOLOID, -1, 0);
+					   "type", CHAROID, -1, 0);
 	BlessTupleDesc(tupdesc);
 
 	memset(nulls, false, sizeof(nulls));
@@ -537,10 +575,10 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 	MemoryContext oldcontext;
 	TypeFuncClass	tuptype ;
 
-	/* isdir depends on metadata */
-	Assert(!(flags&LS_DIR_ISDIR) || (flags&LS_DIR_METADATA));
-	/* Unreasonable to show isdir and skip dirs */
-	Assert(!(flags&LS_DIR_ISDIR) || !(flags&LS_DIR_SKIP_DIRS));
+	/* type depends on metadata */
+	Assert(!(flags&LS_DIR_TYPE) || (flags&LS_DIR_METADATA));
+	/* Unreasonable to show type and skip dirs XXX */
+	Assert(!(flags&LS_DIR_TYPE) || !(flags&LS_DIR_SKIP_DIRS));
 
 	/* check the optional arguments */
 	if (PG_NARGS() == 3)
@@ -738,7 +776,7 @@ pg_ls_dir_metadata(PG_FUNCTION_ARGS)
 	char	*dirname = convert_and_check_filename(PG_GETARG_TEXT_PP(0));
 
 	return pg_ls_dir_files(fcinfo, dirname,
-			LS_DIR_METADATA | LS_DIR_SKIP_SPECIAL | LS_DIR_ISDIR);
+			LS_DIR_METADATA | LS_DIR_TYPE);
 }
 
 /*
@@ -753,5 +791,5 @@ pg_ls_dir_metadata_1arg(PG_FUNCTION_ARGS)
 	char	*dirname = convert_and_check_filename(PG_GETARG_TEXT_PP(0));
 
 	return pg_ls_dir_files(fcinfo, dirname,
-			LS_DIR_METADATA | LS_DIR_SKIP_SPECIAL | LS_DIR_ISDIR);
+			LS_DIR_METADATA | LS_DIR_TYPE);
 }
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3f43958423..414b087756 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6203,16 +6203,16 @@
 { oid => '2623', descr => 'get information about file',
   proname => 'pg_stat_file', provolatile => 'v', prorettype => 'record',
   proargtypes => 'text',
-  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}',
+  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}',
   proargmodes => '{i,o,o,o,o,o,o}',
-  proargnames => '{filename,size,access,modification,change,creation,isdir}',
+  proargnames => '{filename,size,access,modification,change,creation,type}',
   prosrc => 'pg_stat_file_1arg' },
 { oid => '3307', descr => 'get information about file',
   proname => 'pg_stat_file', provolatile => 'v', prorettype => 'record',
   proargtypes => 'text bool',
-  proallargtypes => '{text,bool,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}',
+  proallargtypes => '{text,bool,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}',
   proargmodes => '{i,i,o,o,o,o,o,o}',
-  proargnames => '{filename,missing_ok,size,access,modification,change,creation,isdir}',
+  proargnames => '{filename,missing_ok,size,access,modification,change,creation,type}',
   prosrc => 'pg_stat_file' },
 { oid => '2624', descr => 'read text from a file',
   proname => 'pg_read_file', provolatile => 'v', prorettype => 'text',
@@ -10952,41 +10952,41 @@
 { oid => '3353', descr => 'list files in the log directory',
   proname => 'pg_ls_logdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}',
-  proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_logdir' },
+  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}', proargmodes => '{o,o,o,o,o,o,o}',
+  proargnames => '{name,size,access,modification,change,creation,type}', prosrc => 'pg_ls_logdir' },
 { oid => '3354', descr => 'list of files in the WAL directory',
   proname => 'pg_ls_waldir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}',
-  proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_waldir' },
+  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}', proargmodes => '{o,o,o,o,o,o,o}',
+  proargnames => '{name,size,access,modification,change,creation,type}', prosrc => 'pg_ls_waldir' },
 { oid => '5031', descr => 'list of files in the archive_status directory',
   proname => 'pg_ls_archive_statusdir', procost => '10', prorows => '20',
   proretset => 't', provolatile => 'v', prorettype => 'record',
-  proargtypes => '', proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}',
-  proargmodes => '{o,o,o,o,o,o,o}', proargnames => '{name,size,access,modification,change,creation,isdir}',
+  proargtypes => '', proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}',
+  proargmodes => '{o,o,o,o,o,o,o}', proargnames => '{name,size,access,modification,change,creation,type}',
   prosrc => 'pg_ls_archive_statusdir' },
 { oid => '5029', descr => 'list files in the pgsql_tmp directory',
   proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => '',
-  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{o,o,o,o,o,o,o}',
-  proargnames => '{name,size,access,modification,change,creation,isdir}', prosrc => 'pg_ls_tmpdir_noargs' },
+  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}', proargmodes => '{o,o,o,o,o,o,o}',
+  proargnames => '{name,size,access,modification,change,creation,type}', prosrc => 'pg_ls_tmpdir_noargs' },
 { oid => '5030', descr => 'list files in the pgsql_tmp directory',
   proname => 'pg_ls_tmpdir', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => 'oid',
-  proallargtypes => '{oid,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,o,o,o,o,o,o,o}',
-  proargnames => '{tablespace,name,size,access,modification,change,creation,isdir}',
+  proallargtypes => '{oid,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}', proargmodes => '{i,o,o,o,o,o,o,o}',
+  proargnames => '{tablespace,name,size,access,modification,change,creation,type}',
   prosrc => 'pg_ls_tmpdir_1arg' },
 { oid => '9979', descr => 'list directory with metadata',
   proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => 'text bool bool',
-  proallargtypes => '{text,bool,bool,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o,o,o,o}',
-  proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,access,modification,change,creation,isdir}',
+  proallargtypes => '{text,bool,bool,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}', proargmodes => '{i,i,i,o,o,o,o,o,o,o}',
+  proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,access,modification,change,creation,type}',
   prosrc => 'pg_ls_dir_metadata' },
 { oid => '9980', descr => 'list directory with metadata',
   proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => 'text',
-  proallargtypes => '{text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool}', proargmodes => '{i,o,o,o,o,o,o,o}',
-  proargnames => '{dirname,filename,size,access,modification,change,creation,isdir}',
+  proallargtypes => '{text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}', proargmodes => '{i,o,o,o,o,o,o,o}',
+  proargnames => '{dirname,filename,size,access,modification,change,creation,type}',
   prosrc => 'pg_ls_dir_metadata_1arg' },
 
 # hash partitioning constraint function
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 1f4106cd9c..5b87ab4844 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -157,8 +157,8 @@ select count(*) > 0 as ok from (select pg_ls_waldir()) ss;
 
 -- Test not-run-to-completion cases.
 select * from pg_ls_waldir() limit 0;
- name | size | access | modification | change | creation | isdir 
-------+------+--------+--------------+--------+----------+-------
+ name | size | access | modification | change | creation | type 
+------+------+--------+--------------+--------+----------+------
 (0 rows)
 
 select count(*) > 0 as ok from (select * from pg_ls_waldir() limit 1) ss;
@@ -221,32 +221,32 @@ select pg_ls_dir('does not exist'); -- fails with missingok=false
 ERROR:  could not open directory "does not exist": No such file or directory
 -- Check that expected columns are present
 select * from pg_stat_file('.') limit 0;
- size | access | modification | change | creation | isdir 
-------+--------+--------------+--------+----------+-------
+ size | access | modification | change | creation | type 
+------+--------+--------------+--------+----------+------
 (0 rows)
 
 -- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 select * from pg_ls_tmpdir() where name='Does not exist';
- name | size | access | modification | change | creation | isdir 
-------+------+--------+--------------+--------+----------+-------
+ name | size | access | modification | change | creation | type 
+------+------+--------+--------------+--------+----------+------
 (0 rows)
 
-select filename, isdir from pg_ls_dir_metadata('.') where filename='.';
- filename | isdir 
-----------+-------
- .        | t
+select filename, type from pg_ls_dir_metadata('.') where filename='.';
+ filename | type 
+----------+------
+ .        | d
 (1 row)
 
-select filename, isdir from pg_ls_dir_metadata('.', false, false) where filename='.'; -- include_dot_dirs=false
- filename | isdir 
-----------+-------
+select filename, type from pg_ls_dir_metadata('.', false, false) where filename='.'; -- include_dot_dirs=false
+ filename | type 
+----------+------
 (0 rows)
 
 -- Check that expected columns are present
 select * from pg_ls_dir_metadata('.') limit 0;
- filename | size | access | modification | change | creation | isdir 
-----------+------+--------+--------------+--------+----------+-------
+ filename | size | access | modification | change | creation | type 
+----------+------+--------+--------------+--------+----------+------
 (0 rows)
 
 --
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 1e1e02b589..025c9709a1 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -17,15 +17,15 @@ CREATE TABLESPACE regress_tblspace LOCATION '@testtablespace@';
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 -- The query is written to ERROR if the tablespace doesn't exist, rather than silently failing to call pg_ls_tmpdir()
 SELECT c.* FROM (SELECT oid FROM pg_tablespace b WHERE b.spcname='regress_tblspace' UNION SELECT 0 ORDER BY 1 DESC LIMIT 1) AS b , pg_ls_tmpdir(oid) AS c WHERE c.name='Does not exist';
- name | size | access | modification | change | creation | isdir 
-------+------+--------+--------------+--------+----------+-------
+ name | size | access | modification | change | creation | type 
+------+------+--------+--------------+--------+----------+------
 (0 rows)
 
 -- This tests the missing_ok parameter.  If that's not functioning, this would ERROR if the logdir doesn't exist yet.
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 SELECT * FROM pg_ls_logdir() WHERE name='Does not exist';
- name | size | access | modification | change | creation | isdir 
-------+------+--------+--------------+--------+----------+-------
+ name | size | access | modification | change | creation | type 
+------+------+--------+--------------+--------+----------+------
 (0 rows)
 
 -- try setting and resetting some properties for the new tablespace
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 64d9d70874..0961cdc058 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -72,9 +72,9 @@ select * from pg_stat_file('.') limit 0;
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 select * from pg_ls_tmpdir() where name='Does not exist';
 
-select filename, isdir from pg_ls_dir_metadata('.') where filename='.';
+select filename, type from pg_ls_dir_metadata('.') where filename='.';
 
-select filename, isdir from pg_ls_dir_metadata('.', false, false) where filename='.'; -- include_dot_dirs=false
+select filename, type from pg_ls_dir_metadata('.', false, false) where filename='.'; -- include_dot_dirs=false
 
 -- Check that expected columns are present
 select * from pg_ls_dir_metadata('.') limit 0;
-- 
2.17.0


>From 1fa9c2d90c61830acd51433c1f3f272a32a665e8 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 28 Nov 2020 22:29:31 -0600
Subject: [PATCH v25 10/11] Preserve pg_stat_file() isdir..

..per Tom's suggestion
---
 src/backend/utils/adt/genfile.c              | 15 ++++++++++++---
 src/include/catalog/pg_proc.dat              | 12 ++++++------
 src/test/regress/expected/misc_functions.out |  4 ++--
 3 files changed, 20 insertions(+), 11 deletions(-)

diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 6e8898029f..5c4a7f748d 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -468,11 +468,12 @@ pg_stat_file(PG_FUNCTION_ARGS)
 	text	   *filename_t = PG_GETARG_TEXT_PP(0);
 	char	   *filename;
 	struct stat fst;
-	Datum		values[6];
-	bool		nulls[6];
+	Datum		values[7];
+	bool		nulls[7];
 	HeapTuple	tuple;
 	TupleDesc	tupdesc;
 	bool		missing_ok = false;
+	char		type;
 
 	/* check the optional argument */
 	if (PG_NARGS() == 2)
@@ -494,7 +495,7 @@ pg_stat_file(PG_FUNCTION_ARGS)
 	 * This record type had better match the output parameters declared for me
 	 * in pg_proc.h.
 	 */
-	tupdesc = CreateTemplateTupleDesc(6);
+	tupdesc = CreateTemplateTupleDesc(7);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 1,
 					   "size", INT8OID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 2,
@@ -506,11 +507,19 @@ pg_stat_file(PG_FUNCTION_ARGS)
 	TupleDescInitEntry(tupdesc, (AttrNumber) 5,
 					   "creation", TIMESTAMPTZOID, -1, 0);
 	TupleDescInitEntry(tupdesc, (AttrNumber) 6,
+					   "isdir", BOOLOID, -1, 0);
+	TupleDescInitEntry(tupdesc, (AttrNumber) 7,
 					   "type", CHAROID, -1, 0);
 	BlessTupleDesc(tupdesc);
 
 	memset(nulls, false, sizeof(nulls));
 	tuple_from_stat(&fst, filename, values, nulls);
+
+	/* For pg_stat_file, keep isdir column for backward compatibility */
+	type = DatumGetChar(values[5]);
+	values[5] = BoolGetDatum(type == 'd'); /* isdir */
+	values[6] = CharGetDatum(type); /* file type */
+
 	tuple = heap_form_tuple(tupdesc, values, nulls);
 
 	pfree(filename);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 414b087756..a75929cb92 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6203,16 +6203,16 @@
 { oid => '2623', descr => 'get information about file',
   proname => 'pg_stat_file', provolatile => 'v', prorettype => 'record',
   proargtypes => 'text',
-  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}',
-  proargmodes => '{i,o,o,o,o,o,o}',
-  proargnames => '{filename,size,access,modification,change,creation,type}',
+  proallargtypes => '{text,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool,char}',
+  proargmodes => '{i,o,o,o,o,o,o,o}',
+  proargnames => '{filename,size,access,modification,change,creation,isdir,type}',
   prosrc => 'pg_stat_file_1arg' },
 { oid => '3307', descr => 'get information about file',
   proname => 'pg_stat_file', provolatile => 'v', prorettype => 'record',
   proargtypes => 'text bool',
-  proallargtypes => '{text,bool,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}',
-  proargmodes => '{i,i,o,o,o,o,o,o}',
-  proargnames => '{filename,missing_ok,size,access,modification,change,creation,type}',
+  proallargtypes => '{text,bool,int8,timestamptz,timestamptz,timestamptz,timestamptz,bool,char}',
+  proargmodes => '{i,i,o,o,o,o,o,o,o}',
+  proargnames => '{filename,missing_ok,size,access,modification,change,creation,isdir,type}',
   prosrc => 'pg_stat_file' },
 { oid => '2624', descr => 'read text from a file',
   proname => 'pg_read_file', provolatile => 'v', prorettype => 'text',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 5b87ab4844..e1e9ced303 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -221,8 +221,8 @@ select pg_ls_dir('does not exist'); -- fails with missingok=false
 ERROR:  could not open directory "does not exist": No such file or directory
 -- Check that expected columns are present
 select * from pg_stat_file('.') limit 0;
- size | access | modification | change | creation | type 
-------+--------+--------------+--------+----------+------
+ size | access | modification | change | creation | isdir | type 
+------+--------+--------------+--------+----------+-------+------
 (0 rows)
 
 -- This tests the missing_ok parameter, which causes pg_ls_tmpdir to succeed even if the tmpdir doesn't exist yet
-- 
2.17.0


>From 10f0d4215493e0f44dfa7e399ccd9978f6cf7a94 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 8 Mar 2020 22:52:14 -0500
Subject: [PATCH v25 11/11] Add recursion option in pg_ls_dir_files..

Need catversion bumped ?
---
 doc/src/sgml/func.sgml                       |  6 +-
 src/backend/catalog/system_views.sql         |  2 +-
 src/backend/utils/adt/genfile.c              | 78 ++++++++++++++++----
 src/bin/pg_rewind/libpq_source.c             | 22 ++----
 src/bin/pg_rewind/t/RewindTest.pm            |  5 +-
 src/include/catalog/pg_proc.dat              | 17 +++--
 src/test/regress/expected/misc_functions.out | 26 ++++++-
 src/test/regress/sql/misc_functions.sql      |  8 +-
 8 files changed, 120 insertions(+), 44 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index cd4375f75b..7f7d674767 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25762,7 +25762,8 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         </indexterm>
         <function>pg_ls_dir_metadata</function> ( <parameter>dirname</parameter> <type>text</type>
         <optional>, <parameter>missing_ok</parameter> <type>boolean</type>,
-        <parameter>include_dot_dirs</parameter> <type>boolean</type> </optional> )
+        <parameter>include_dot_dirs</parameter> <type>boolean</type>,
+        <parameter>recurse</parameter> <type>boolean</type> </optional> )
         <returnvalue>setof record</returnvalue>
         ( <parameter>filename</parameter> <type>text</type>,
         <parameter>size</parameter> <type>bigint</type>,
@@ -25770,7 +25771,8 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         <parameter>modification</parameter> <type>timestamp with time zone</type>,
         <parameter>change</parameter> <type>timestamp with time zone</type>,
         <parameter>creation</parameter> <type>timestamp with time zone</type>,
-        <parameter>type</parameter> <type>char</type> )
+        <parameter>type</parameter> <type>char</type>,
+        <parameter>path</parameter> <type>text</type> )
        </para>
        <para>
         For each file in the specified directory, list the file and its
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 93d4f1e4a6..2ce4b27e0f 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1506,7 +1506,7 @@ REVOKE EXECUTE ON FUNCTION pg_stat_file(text,boolean) FROM public;
 
 REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
-REVOKE EXECUTE ON FUNCTION pg_ls_dir_metadata(text,boolean,boolean) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_dir_metadata(text,boolean,boolean,boolean) FROM public;
 
 --
 -- We also set up some things as accessible to standard roles.
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 5c4a7f748d..14ce7da3e4 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -40,6 +40,8 @@ static char get_file_type(mode_t mode, const char *path);
 static void tuple_from_stat(struct stat *fst, const char *path, Datum *values,
 		bool *nulls);
 static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags);
+void pg_ls_dir_files_internal(const char *dirname, DIR *dirdesc,
+		Tuplestorestate *tupstore, TupleDesc tupdesc, int flags);
 
 #define	LS_DIR_TYPE					(1<<0) /* Show column: type */
 #define	LS_DIR_METADATA				(1<<1) /* Show columns: mtime, size */
@@ -48,6 +50,7 @@ static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags
 #define	LS_DIR_SKIP_HIDDEN			(1<<4) /* Do not show anything begining with . */
 #define	LS_DIR_SKIP_DIRS			(1<<5) /* Do not show directories */
 #define	LS_DIR_SKIP_SPECIAL			(1<<6) /* Do not show special file types */
+#define	LS_DIR_RECURSE				(1<<7) /* Recurse into subdirs */
 
 /* Shortcut for common behavior */
 #define LS_DIR_COMMON				(LS_DIR_SKIP_HIDDEN | LS_DIR_METADATA)
@@ -580,7 +583,6 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 	TupleDesc	tupdesc;
 	Tuplestorestate *tupstore;
 	DIR		   *dirdesc;
-	struct dirent *de;
 	MemoryContext oldcontext;
 	TypeFuncClass	tuptype ;
 
@@ -590,9 +592,8 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 	Assert(!(flags&LS_DIR_TYPE) || !(flags&LS_DIR_SKIP_DIRS));
 
 	/* check the optional arguments */
-	if (PG_NARGS() == 3)
-	{
-		if (!PG_ARGISNULL(1))
+	if (PG_NARGS() > 1 &&
+		!PG_ARGISNULL(1))
 		{
 			if (PG_GETARG_BOOL(1))
 				flags |= LS_DIR_MISSING_OK;
@@ -600,14 +601,30 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 				flags &= ~LS_DIR_MISSING_OK;
 		}
 
-		if (!PG_ARGISNULL(2))
+	if (PG_NARGS() > 2 &&
+		!PG_ARGISNULL(2))
 		{
 			if (PG_GETARG_BOOL(2))
 				flags &= ~LS_DIR_SKIP_DOT_DIRS;
 			else
 				flags |= LS_DIR_SKIP_DOT_DIRS;
 		}
-	}
+
+	if (PG_NARGS() > 3 &&
+		!PG_ARGISNULL(3))
+		{
+			if (PG_GETARG_BOOL(3))
+				flags |= LS_DIR_RECURSE;
+			else
+				flags &= ~LS_DIR_RECURSE;
+		}
+
+	if ((flags & LS_DIR_RECURSE) != 0 &&
+			(flags & LS_DIR_SKIP_DOT_DIRS) == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_RECURSION), // ??
+				 errmsg("recursion requires skipping dot dirs")));
+
 
 	/* check to see if caller supports us returning a tuplestore */
 	if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
@@ -659,10 +676,20 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 		/* Otherwise, we can let ReadDir() throw the error */
 	}
 
-	while ((de = ReadDir(dirdesc, dir)) != NULL)
+	pg_ls_dir_files_internal(dir, dirdesc, tupstore, tupdesc, flags);
+	FreeDir(dirdesc);
+	return (Datum) 0;
+}
+
+void pg_ls_dir_files_internal(const char *dirname, DIR *dirdesc,
+		Tuplestorestate *tupstore, TupleDesc tupdesc, int flags)
+{
+	struct dirent *de;
+
+	while ((de = ReadDir(dirdesc, dirname)) != NULL)
 	{
-		Datum		values[7];
-		bool		nulls[7];
+		Datum		values[8];
+		bool		nulls[8];
 		char		path[MAXPGPATH * 2];
 		struct stat attrib;
 
@@ -678,7 +705,11 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 			continue;
 
 		/* Get the file info */
-		snprintf(path, sizeof(path), "%s/%s", dir, de->d_name);
+		if (strcmp(dirname, ".") != 0)
+			snprintf(path, sizeof(path), "%s/%s", dirname, de->d_name);
+		else
+			snprintf(path, sizeof(path), "%s", de->d_name);
+
 		if (lstat(path, &attrib) < 0)
 		{
 			/* Ignore concurrently-deleted files, else complain */
@@ -703,14 +734,33 @@ pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags)
 
 		memset(nulls, false, sizeof(nulls));
 		values[0] = CStringGetTextDatum(de->d_name);
-		if (flags & LS_DIR_METADATA)
+		if ((flags & (LS_DIR_RECURSE|LS_DIR_METADATA)) != 0)
+		{
 			tuple_from_stat(&attrib, path, 1+values, 1+nulls);
 
+			/*
+			 * path is only really useful for recursion, but this function
+			 * can't return different fields when recursing
+			 * XXX: return dirname (which is nice since it's the original,
+			 * unprocessed input to this recursion) or path (which is nice
+			 * since it's a "cooked" value without leading/duplicate slashes)
+			 */
+			values[7] = CStringGetTextDatum(path);
+		}
+
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);
-	}
 
-	FreeDir(dirdesc);
-	return (Datum) 0;
+		/* Recurse? */
+		if ((flags & LS_DIR_RECURSE) != 0 &&
+			S_ISDIR(attrib.st_mode))
+		{
+			DIR *newdir = AllocateDir(path);
+			/* Failure handled by ReadDir */
+			pg_ls_dir_files_internal(path, newdir, tupstore, tupdesc, flags);
+			Assert(newdir != NULL);
+			FreeDir(newdir);
+		}
+	}
 }
 
 /* Function to return the list of files in the log directory */
diff --git a/src/bin/pg_rewind/libpq_source.c b/src/bin/pg_rewind/libpq_source.c
index 47beba277a..9ebb31615c 100644
--- a/src/bin/pg_rewind/libpq_source.c
+++ b/src/bin/pg_rewind/libpq_source.c
@@ -237,30 +237,18 @@ libpq_traverse_files(rewind_source *source, process_file_callback_t callback)
 	/*
 	 * Create a recursive directory listing of the whole data directory.
 	 *
-	 * The WITH RECURSIVE part does most of the work. The second part gets the
-	 * targets of the symlinks in pg_tblspc directory.
+	 * Join to pg_tablespace to get the targets of the symlinks in
+	 * pg_tblspc directory.
 	 *
 	 * XXX: There is no backend function to get a symbolic link's target in
 	 * general, so if the admin has put any custom symbolic links in the data
 	 * directory, they won't be copied correctly.
 	 */
 	sql =
-		"WITH RECURSIVE files (path, filename, size, isdir) AS (\n"
-		"  SELECT '' AS path, filename, size, isdir FROM\n"
-		"  (SELECT pg_ls_dir('.', true, false) AS filename) AS fn,\n"
-		"        pg_stat_file(fn.filename, true) AS this\n"
-		"  UNION ALL\n"
-		"  SELECT parent.path || parent.filename || '/' AS path,\n"
-		"         fn, this.size, this.isdir\n"
-		"  FROM files AS parent,\n"
-		"       pg_ls_dir(parent.path || parent.filename, true, false) AS fn,\n"
-		"       pg_stat_file(parent.path || parent.filename || '/' || fn, true) AS this\n"
-		"       WHERE parent.isdir = 't'\n"
-		")\n"
-		"SELECT path || filename, size, isdir,\n"
+		"SELECT path, size, type='d' AS isdir,\n"
 		"       pg_tablespace_location(pg_tablespace.oid) AS link_target\n"
-		"FROM files\n"
-		"LEFT OUTER JOIN pg_tablespace ON files.path = 'pg_tblspc/'\n"
+		"FROM pg_ls_dir_metadata('.', true, false, true) files\n"
+		"LEFT OUTER JOIN pg_tablespace ON files.path = 'pg_tblspc'\n"
 		"                             AND oid::text = files.filename\n";
 	res = PQexec(conn, sql);
 
diff --git a/src/bin/pg_rewind/t/RewindTest.pm b/src/bin/pg_rewind/t/RewindTest.pm
index 41ed7d4b3b..d911b7de52 100644
--- a/src/bin/pg_rewind/t/RewindTest.pm
+++ b/src/bin/pg_rewind/t/RewindTest.pm
@@ -160,7 +160,10 @@ sub start_primary
 		GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text)
 		  TO rewind_user;
 		GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean)
-		  TO rewind_user;");
+		  TO rewind_user;
+		GRANT EXECUTE ON function pg_catalog.pg_ls_dir_metadata(text, bool, bool, bool)
+		  TO rewind_user;
+		");
 
 	#### Now run the test-specific parts to initialize the primary before setting
 	# up standby
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a75929cb92..b54be0bc63 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10978,17 +10978,24 @@
   prosrc => 'pg_ls_tmpdir_1arg' },
 { oid => '9979', descr => 'list directory with metadata',
   proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
-  provolatile => 'v', prorettype => 'record', proargtypes => 'text bool bool',
-  proallargtypes => '{text,bool,bool,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}', proargmodes => '{i,i,i,o,o,o,o,o,o,o}',
-  proargnames => '{dirname,missing_ok,include_dot_dirs,filename,size,access,modification,change,creation,type}',
+  provolatile => 'v', prorettype => 'record', proargtypes => 'text bool bool bool',
+  proallargtypes => '{text,bool,bool,bool,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char,text}', proargmodes => '{i,i,i,i,o,o,o,o,o,o,o,o}',
+  proargnames => '{dirname,missing_ok,include_dot_dirs,recurse,filename,size,access,modification,change,creation,type,path}',
   prosrc => 'pg_ls_dir_metadata' },
 { oid => '9980', descr => 'list directory with metadata',
   proname => 'pg_ls_dir_metadata', procost => '10', prorows => '20', proretset => 't',
   provolatile => 'v', prorettype => 'record', proargtypes => 'text',
-  proallargtypes => '{text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}', proargmodes => '{i,o,o,o,o,o,o,o}',
-  proargnames => '{dirname,filename,size,access,modification,change,creation,type}',
+  proallargtypes => '{text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char,text}', proargmodes => '{i,o,o,o,o,o,o,o,o}',
+  proargnames => '{dirname,filename,size,access,modification,change,creation,type,path}',
   prosrc => 'pg_ls_dir_metadata_1arg' },
 
+{ oid => '9981', descr => 'list all files in a directory recursively',
+  proname => 'pg_ls_dir_recurse_sql', prorows => '10000', proretset => 't',
+  provolatile => 'v', prorettype => 'record', proargtypes => 'text',
+  proallargtypes => '{text,text,text,int8,timestamptz,timestamptz,timestamptz,timestamptz,char}',
+  proargnames => '{dirname,path,filename,size,access,modification,change,creation,type}', proargmodes => '{i,o,o,o,o,o,o,o,o}',
+  prolang => 'sql', prosrc => "with recursive ls as (select dirname as path, * from pg_ls_dir_metadata(dirname, false, false) union all select coalesce(nullif(parent.path,'.')||'/','')||parent.filename, a.filename, a.size, a.access, a.modification, a.change, a.creation, a.type from ls as parent, lateral pg_ls_dir_metadata(parent.path||'/'||parent.filename, false, false) as a where parent.type='d') select * from ls" },
+
 # hash partitioning constraint function
 { oid => '5028', descr => 'hash partition CHECK constraint',
   proname => 'satisfies_hash_partition', provariadic => 'any',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index e1e9ced303..b14547c857 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -238,17 +238,37 @@ select filename, type from pg_ls_dir_metadata('.') where filename='.';
  .        | d
 (1 row)
 
-select filename, type from pg_ls_dir_metadata('.', false, false) where filename='.'; -- include_dot_dirs=false
+select filename, type from pg_ls_dir_metadata('.', false, false, false) where filename='.'; -- include_dot_dirs=false
  filename | type 
 ----------+------
 (0 rows)
 
 -- Check that expected columns are present
 select * from pg_ls_dir_metadata('.') limit 0;
- filename | size | access | modification | change | creation | type 
-----------+------+--------+--------------+--------+----------+------
+ filename | size | access | modification | change | creation | type | path 
+----------+------+--------+--------------+--------+----------+------+------
 (0 rows)
 
+-- Exercise recursion
+select lower(path), filename, type from pg_ls_dir_metadata('.', true, false, true) where
+path in ('base', 'base/pgsql_tmp', 'global', 'global/pg_control', 'global/pg_filenode.map', 'PG_VERSION', 'pg_multixact', 'pg_multixact/members', 'pg_multixact/offsets', 'pg_wal', 'pg_wal/archive_status')
+-- (type='d' or path~'^(global/.*|PG_VERSION|postmaster\.opts|postmaster\.pid|pg_logical/replorigin_checkpoint)$') and filename!~'[0-9]'
+order by 1;
+         lower          |    filename     | type 
+------------------------+-----------------+------
+ base                   | base            | d
+ base/pgsql_tmp         | pgsql_tmp       | d
+ global                 | global          | d
+ global/pg_control      | pg_control      | -
+ global/pg_filenode.map | pg_filenode.map | -
+ pg_multixact           | pg_multixact    | d
+ pg_multixact/members   | members         | d
+ pg_multixact/offsets   | offsets         | d
+ pg_version             | PG_VERSION      | -
+ pg_wal                 | pg_wal          | d
+ pg_wal/archive_status  | archive_status  | d
+(11 rows)
+
 --
 -- Test adding a support function to a subject function
 --
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 0961cdc058..328cf42e47 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -74,11 +74,17 @@ select * from pg_ls_tmpdir() where name='Does not exist';
 
 select filename, type from pg_ls_dir_metadata('.') where filename='.';
 
-select filename, type from pg_ls_dir_metadata('.', false, false) where filename='.'; -- include_dot_dirs=false
+select filename, type from pg_ls_dir_metadata('.', false, false, false) where filename='.'; -- include_dot_dirs=false
 
 -- Check that expected columns are present
 select * from pg_ls_dir_metadata('.') limit 0;
 
+-- Exercise recursion
+select lower(path), filename, type from pg_ls_dir_metadata('.', true, false, true) where
+path in ('base', 'base/pgsql_tmp', 'global', 'global/pg_control', 'global/pg_filenode.map', 'PG_VERSION', 'pg_multixact', 'pg_multixact/members', 'pg_multixact/offsets', 'pg_wal', 'pg_wal/archive_status')
+-- (type='d' or path~'^(global/.*|PG_VERSION|postmaster\.opts|postmaster\.pid|pg_logical/replorigin_checkpoint)$') and filename!~'[0-9]'
+order by 1;
+
 --
 -- Test adding a support function to a subject function
 --
-- 
2.17.0