public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v17 07/10] Add pg_ls_dir_recurse to show dir recursively.. 2+ messages / 2 participants [nested] [flat]
* [PATCH v17 07/10] Add pg_ls_dir_recurse to show dir recursively.. @ 2020-03-09 03:52 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Justin Pryzby @ 2020-03-09 03:52 UTC (permalink / raw) ..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/include/catalog/pg_proc.dat | 6 ++++ src/test/regress/expected/misc_functions.out | 14 +++++++++ src/test/regress/sql/misc_functions.sql | 6 ++++ 5 files changed, 59 insertions(+) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 8e0de7c02d..334b6beb3a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -25738,6 +25738,38 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_ls_dir_recurse</primary> + </indexterm> + <function>pg_ls_dir_recurse</function> ( <parameter>dirname</parameter> <type>text</type> ) + <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>isdir</parameter> <type>boolean</type> ) + </para> + <para> + Recursively list each file in the specified directory, along with the + files' metadata. + </para> + <para> + Restricted to superusers by default, but other users can be granted + EXECUTE to run the function. + </para></entry> + </row> + + <!--para> + To recursively list temporary directories in all tablespaces: +<programlisting> +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; +</programlisting> + </para--> + <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 1c77430f0c..215fba3b7d 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1468,6 +1468,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/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index adfce45d1a..f94f403475 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10916,6 +10916,12 @@ proallargtypes => '{text,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}', proargnames => '{dirname,name,size,modification,isdir}', prosrc => 'pg_ls_dir_metadata_1arg' }, +{ oid => '5034', 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,int8,timestamptz,bool}', + proargnames => '{dirname,name,size,modification,isdir}', proargmodes => '{i,o,o,o,o}', + prolang => 'sql', prosrc => "with recursive ls as (select * from pg_ls_dir_metadata(dirname, true, false) union all select ls.name||'/'||a.name, a.size, a.modification, a.isdir from ls, lateral pg_ls_dir_metadata(dirname||'/'||ls.name, false, false)a where ls.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 64b1417fb8..4188d684f0 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -243,6 +243,20 @@ 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 name, isdir FROM pg_ls_dir_recurse('.') WHERE isdir AND name~'^pg_wal'; + name | isdir +-----------------------+------- + pg_wal | t + pg_wal/archive_status | t +(2 rows) + +-- Check that expected columns are present +SELECT * FROM pg_ls_dir_recurse('.') LIMIT 0; + name | 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 372345720d..6041c4f3dc 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -76,6 +76,12 @@ select name, isdir from pg_ls_dir_metadata('.', false, false) where name='.'; -- -- 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 name, isdir FROM pg_ls_dir_recurse('.') WHERE isdir AND name~'^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 --4LFBTxd4L5NLO6ly Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v17-0008-pg_ls_logdir-to-ignore-error-if-initial-top-dir-.patch" ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Removing more vacuumlazy.c special cases, relfrozenxid optimizations @ 2022-04-14 23:19 Jim Nasby <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Jim Nasby @ 2022-04-14 23:19 UTC (permalink / raw) To: Andres Freund <[email protected]>; Peter Geoghegan <[email protected]>; +Cc: Justin Pryzby <[email protected]>; Robert Haas <[email protected]>; Thomas Munro <[email protected]>; Masahiko Sawada <[email protected]>; PostgreSQL Hackers <[email protected]> On 4/3/22 12:05 PM, Andres Freund wrote: > While I was writing the above I, again, realized that it'd be awfully nice to > have some accumulated stats about (auto-)vacuum's effectiveness. For us to get > feedback about improvements more easily and for users to know what aspects > they need to tune. > > Knowing how many times a table was vacuumed doesn't really tell that much, and > requiring to enable log_autovacuum_min_duration and then aggregating those > results is pretty painful (and version dependent). > > If we just collected something like: > - number of heap passes > - time spent heap vacuuming > - number of index scans > - time spent index vacuuming > - time spent delaying The number of passes would let you know if maintenance_work_mem is too small (or to stop killing 187M+ tuples in one go). The timing info would give you an idea of the impact of throttling. > - percentage of non-yet-removable vs removable tuples This'd give you an idea how bad your long-running-transaction problem is. Another metric I think would be useful is the average utilization of your autovac workers. No spare workers means you almost certainly have tables that need vacuuming but have to wait. As a single number, it'd also be much easier for users to understand. I'm no stats expert, but one way to handle that cheaply would be to maintain an engineering-weighted-mean of the percentage of autovac workers that are in use at the end of each autovac launcher cycle (though that would probably not work great for people that have extreme values for launcher delay, or constantly muck with launcher_delay). > > it'd start to be a heck of a lot easier to judge how well autovacuum is > coping. > > If we tracked the related pieces above in the index stats (or perhaps > additionally there), it'd also make it easier to judge the cost of different > indexes. > > - Andres > > ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-04-14 23:19 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-03-09 03:52 [PATCH v17 07/10] Add pg_ls_dir_recurse to show dir recursively.. Justin Pryzby <[email protected]> 2022-04-14 23:19 Re: Removing more vacuumlazy.c special cases, relfrozenxid optimizations Jim Nasby <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox