From 313c2c4128e70776e248afb973cca0af2125eeda Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sun, 8 Mar 2020 22:52:14 -0500 Subject: [PATCH v23 07/10] Add pg_ls_dir_recurse to show dir recursively.. ..possibly there's a better place to put this, like maybe a doc-only example ? Need catversion bumped ? --- doc/src/sgml/func.sgml | 32 ++++++++++++++++++++ src/backend/catalog/system_views.sql | 1 + src/bin/pg_rewind/libpq_source.c | 22 +++----------- src/bin/pg_rewind/t/RewindTest.pm | 7 ++++- src/include/catalog/pg_proc.dat | 6 ++++ src/test/regress/expected/misc_functions.out | 13 ++++++++ src/test/regress/sql/misc_functions.sql | 6 ++++ 7 files changed, 69 insertions(+), 18 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 729d201249..c95dab6e1d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -25760,6 +25760,38 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size + + + + pg_ls_dir_recurse + + pg_ls_dir_recurse ( dirname text ) + setof record + ( name text, + size bigint, + modification timestamp with time zone, + isdir boolean ) + + + Recursively list each file in the specified directory, along with the + files' metadata. + + + Restricted to superusers by default, but other users can be granted + EXECUTE to run the function. + + + + + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index e7647787cf..148dd7a8a8 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1504,6 +1504,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_recurse(text) FROM public; -- -- We also set up some things as accessible to standard roles. diff --git a/src/bin/pg_rewind/libpq_source.c b/src/bin/pg_rewind/libpq_source.c index c73e8bf470..46c8b7fcd5 100644 --- a/src/bin/pg_rewind/libpq_source.c +++ b/src/bin/pg_rewind/libpq_source.c @@ -205,30 +205,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 COALESCE(NULLIF(path,'.')||'/','')||filename, size, 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_recurse('.') 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..cacd58651c 100644 --- a/src/bin/pg_rewind/t/RewindTest.pm +++ b/src/bin/pg_rewind/t/RewindTest.pm @@ -160,7 +160,12 @@ 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) + TO rewind_user; + GRANT EXECUTE ON function pg_catalog.pg_ls_dir_recurse(text) + 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 344d6305e5..5e8f16d5b1 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10983,6 +10983,12 @@ proallargtypes => '{text,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}', proargnames => '{dirname,filename,size,modification,isdir}', prosrc => 'pg_ls_dir_metadata_1arg' }, +{ oid => '9981', descr => 'list all files in a directory recursively', + proname => 'pg_ls_dir_recurse', prorows => '10000', proretset => 't', + provolatile => 'v', prorettype => 'record', proargtypes => 'text', + proallargtypes => '{text,text,text,int8,timestamptz,bool}', + proargnames => '{dirname,path,filename,size,modification,isdir}', proargmodes => '{i,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.modification, a.isdir from ls as parent, lateral pg_ls_dir_metadata(parent.path||'/'||parent.filename, false, false) as a where parent.isdir) select * from ls" }, # 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 bb926a2cf4..302f348a6d 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -243,6 +243,19 @@ select * from pg_ls_dir_metadata('.') limit 0; ----------+------+--------------+------- (0 rows) +-- Check that we at least succeed in recursing once, and that we don't show the leading dir prefix +select path, filename, isdir from pg_ls_dir_recurse('.') where isdir and path='pg_wal'; + path | filename | isdir +--------+----------------+------- + pg_wal | archive_status | t +(1 row) + +-- Check that expected columns are present +select * from pg_ls_dir_recurse('.') limit 0; + path | filename | size | modification | 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 68e2bc6586..fda95828d7 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -76,6 +76,12 @@ 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; +-- Check that we at least succeed in recursing once, and that we don't show the leading dir prefix +select path, filename, isdir from pg_ls_dir_recurse('.') where isdir and path='pg_wal'; + +-- Check that expected columns are present +select * from pg_ls_dir_recurse('.') limit 0; + -- -- Test adding a support function to a subject function -- -- 2.17.0