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

[Justin Pryzby]

On Mon, Nov 23, 2020 at 04:14:18PM -0500, Tom Lane wrote:
> * I don't think it's okay to change the existing signatures of
> pg_ls_logdir() et al.  Even if you can make an argument that it's
> not too harmful to add more output columns, replacing pg_stat_file's
> isdir output with something of a different name and datatype is most
> surely not OK --- there is no possible way that doesn't break existing
> user queries.
> 
> I think possibly a more acceptable approach is to leave these functions
> alone but add documentation explaining how to get the additional info.
> You could say things along the lines of "pg_ls_waldir() is the same as
> pg_ls_dir_metadata('pg_wal') except for showing fewer columns."

On Mon, Nov 23, 2020 at 06:06:19PM -0500, Tom Lane wrote:
> I'm mostly concerned about removing the isdir output of pg_stat_file().
> Maybe we could compromise to the extent of keeping that, allowing it
> to be partially duplicative of a file-type-code output column.

On Tue, Nov 24, 2020 at 11:53:22AM -0500, Stephen Frost wrote:
> I don't have any particular issue with keeping isdir as a convenience
> column.  I agree it'll now be a bit duplicative but that seems alright.

Maybe we should do what Tom said, and leave pg_ls_* unchanged, but also mark
them as deprecated in favour of:
| pg_ls_dir_metadata(dir), dir={'pg_wal/archive_status', 'log', 'pg_wal', 
'base/pgsql_tmp'}

However, pg_ls_tmpdir is special since it handles tablespace tmpdirs, which it
seems is not trivial to get from sql:

+SELECT * FROM (SELECT DISTINCT 
COALESCE(NULLIF(pg_tablespace_location(b.oid),'')||suffix, 'base/pgsql_tmp') AS 
dir
+FROM pg_tablespace b, pg_control_system() pcs,
+LATERAL format('/PG_%s_%s', left(current_setting('server_version_num'), 2), 
pcs.catalog_version_no) AS suffix) AS dir,
+LATERAL pg_ls_dir_recurse(dir) AS a;

For context, the line of reasoning that led me to this patch series was
something like this:

0) Why can't I list shared tempfiles (dirs) using pg_ls_tmpdir() ?
1) Implement recursion for pg_ls_tmpdir();
2) Eventually realize that it's silly to implement a function to recurse into
   one particular directory when no general feature exists;
3) Implement generic facility;

> * I noticed that you did s/stat/lstat/.  That's fine on Unix systems,
> but it won't have any effect on Windows systems (cf bed90759f),
> which means that we'll have to document a platform-specific behavioral
> difference.  Do we want to go there?
>
> Maybe this patch needs to wait on somebody fixing our lack of real lstat() on 
> Windows.

I think only the "top" patches depend on lstat (for the "type" column and
recursion, to avoid loops).  The initial patches are independently useful, and
resolve the original issue of hiding tmpdirs.  I've rebased and re-arranged the
patches to reflect this.

-- 
Justin

>From 1fffd1c7af05298295e96fff189caf6eb6cc1539 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 16 Mar 2020 14:12:55 -0500
Subject: [PATCH v26 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 2707e757ca..806a7b8a9a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26559,7 +26559,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 19c080b6163833323c53e48519f850d21d3a618d Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 30 Mar 2020 18:59:16 -0500
Subject: [PATCH v26 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 806a7b8a9a..2707e757ca 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26559,7 +26559,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 f48fe911cd4603407259b95df4e134d5c6a5ebae Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 17 Mar 2020 13:16:24 -0500
Subject: [PATCH v26 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 38ccc3f84e7d714e5fc8780308707daa1f6c36dd Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 9 Mar 2020 22:40:24 -0500
Subject: [PATCH v26 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              | 233 +++++++++++--------
 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, 222 insertions(+), 93 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2707e757ca..79d30a1568 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25799,6 +25799,27 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
        </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..198a99391a 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.
+ * 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.
+ * 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 22970f46cd..17b03d1abd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11266,6 +11266,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 9ecdaec032a4d6281f606dc4bc2bf68e26bcf4b0 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 8 Mar 2020 22:57:54 -0500
Subject: [PATCH v26 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              |  2 +-
 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, 18 insertions(+), 17 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 79d30a1568..c68c9d548b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26491,16 +26491,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 198a99391a..da7d4a7c11 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -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 17b03d1abd..d2594c2e45 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11258,13 +11258,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 34b808f2740b7d4d3deaf6dc184219b4c22582a1 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 9 Mar 2020 01:00:42 -0500
Subject: [PATCH v26 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 c68c9d548b..d154302172 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25810,7 +25810,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
         <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
@@ -26419,12 +26420,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
@@ -26442,13 +26445,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
@@ -26466,13 +26470,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 d2594c2e45..908283bd19 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11242,18 +11242,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 a6efbcc1e84135c052d8c1b82d14324250f738df Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Fri, 6 Mar 2020 17:23:51 -0600
Subject: [PATCH v26 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 3f513234a46dbd9f83d498c57457aa6f1439035b Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Mon, 9 Mar 2020 21:56:21 -0500
Subject: [PATCH v26 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 d154302172..017d761399 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25810,7 +25810,10 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
         <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>
@@ -26420,13 +26423,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>
@@ -26445,13 +26451,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>
@@ -26470,14 +26479,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>
@@ -26497,13 +26509,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..0bf148435f 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 values_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
+values_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));
+	values_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));
+			values_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 908283bd19..30c21a7c6d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11242,41 +11242,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 b1a5f7bf0434cb69cecb034e375cf981ec84b48f Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 31 Mar 2020 14:40:34 -0500
Subject: [PATCH v26 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 017d761399..46e7ff089d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25814,7 +25814,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
         <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
@@ -26427,13 +26427,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
@@ -26455,13 +26455,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
@@ -26483,14 +26483,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
@@ -26513,7 +26513,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
@@ -26523,7 +26523,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
@@ -26597,13 +26597,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 0bf148435f..0855f5e88e 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 values_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 @@ values_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 30c21a7c6d..5d80cfa6c1 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',
@@ -11242,41 +11242,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 342fb899b6007a2c126e720cb8ee396bdb8059d5 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sat, 28 Nov 2020 22:29:31 -0600
Subject: [PATCH v26 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 0855f5e88e..2ec44863b8 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));
 	values_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 5d80cfa6c1..ee0c5eccec 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 c7ed5d58606829c2c9504f0084238c8aed6db737 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Sun, 8 Mar 2020 22:52:14 -0500
Subject: [PATCH v26 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 46e7ff089d..04b602c32f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25806,7 +25806,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
         </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>,
@@ -25814,7 +25815,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
         <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 2ec44863b8..89ee751aea 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 values_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)
+		{
 			values_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 ee0c5eccec..1344db62d2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11268,17 +11268,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