agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v21 10/10] pg_ls_* to show file type and show special files 2+ messages / 2 participants [nested] [flat]
* [PATCH v21 10/10] pg_ls_* to show file type and show special files @ 2020-03-31 19:40 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Justin Pryzby @ 2020-03-31 19:40 UTC (permalink / raw) --- doc/src/sgml/func.sgml | 16 +++--- src/backend/utils/adt/genfile.c | 58 ++++++++++++++++---- src/bin/pg_rewind/libpq_fetch.c | 2 +- src/include/catalog/pg_proc.dat | 38 ++++++------- src/test/regress/expected/misc_functions.out | 38 ++++++------- src/test/regress/output/tablespace.source | 8 +-- src/test/regress/sql/misc_functions.sql | 6 +- 7 files changed, 103 insertions(+), 63 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 8c3e6f00dc..2a51da872f 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -25820,7 +25820,7 @@ LATERAL pg_ls_dir_recurse(dir) AS a; 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 @@ -25848,7 +25848,7 @@ LATERAL pg_ls_dir_recurse(dir) AS a; 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 @@ -25877,7 +25877,7 @@ LATERAL pg_ls_dir_recurse(dir) AS a; (<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 @@ -25910,7 +25910,7 @@ LATERAL pg_ls_dir_recurse(dir) AS a; 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 @@ -25984,13 +25984,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 d2f79d4ba9..53ea15d0f1 100644 --- a/src/backend/utils/adt/genfile.c +++ b/src/backend/utils/adt/genfile.c @@ -36,11 +36,12 @@ #include "utils/syscache.h" #include "utils/timestamp.h" +static char get_file_type(mode_t mode, const char *path); static void tuple_from_stat(struct stat *fst, const char *path, Datum *values, bool *isnull); 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 .. */ @@ -55,7 +56,7 @@ static Datum pg_ls_dir_files(FunctionCallInfo fcinfo, const char *dir, int flags #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) +#define LS_DIR_COMMON (LS_DIR_SKIP_HIDDEN|LS_DIR_METADATA) /* * Convert a "text" filename argument to C string, and check it's allowable. @@ -405,6 +406,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 isnull from fst and path. * Used for pg_stat_file() and pg_stat_dir_files() @@ -424,7 +462,7 @@ tuple_from_stat(struct stat *fst, const char *path, Datum *values, bool *isnull) isnull[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)); } /* @@ -474,7 +512,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(isnull, false, sizeof(isnull)); @@ -543,10 +581,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) @@ -744,7 +782,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); } /* @@ -759,5 +797,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/bin/pg_rewind/libpq_fetch.c b/src/bin/pg_rewind/libpq_fetch.c index 58f2bb9fd1..62aaf6714e 100644 --- a/src/bin/pg_rewind/libpq_fetch.c +++ b/src/bin/pg_rewind/libpq_fetch.c @@ -184,7 +184,7 @@ libpqProcessFileList(void) * directory, they won't be copied correctly. */ sql = - "SELECT COALESCE(NULLIF(path,'.')||'/','')||filename, size, isdir,\n" + "SELECT COALESCE(NULLIF(path,'.')||'/','')||filename, size, type='d' AS isdir,\n" " pg_tablespace_location(pg_tablespace.oid) AS link_target\n" "FROM pg_ls_dir_recurse('.') files\n" "LEFT OUTER JOIN pg_tablespace ON files.path = 'pg_tblspc'\n" diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index dfb2e7717e..b353f30ed0 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6168,16 +6168,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', @@ -10895,13 +10895,13 @@ { 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', @@ -10911,32 +10911,32 @@ { 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' }, { 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,timestamptz,timestamptz,timestamptz,bool}', - proargnames => '{dirname,path,filename,size,access,modification,change,creation,isdir}', 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.isdir from ls as parent, lateral pg_ls_dir_metadata(parent.path||'/'||parent.filename, false, false) as a where parent.isdir) select * from ls" }, + 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', diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index 4be27fe21e..9bfc445d99 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; @@ -222,38 +222,38 @@ ERROR: could not open directory "does not exist": No such file or directory -- 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) -- 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 +select path, filename, type from pg_ls_dir_recurse('.') where type='d' and path='pg_wal'; + path | filename | type +--------+----------------+------ + pg_wal | archive_status | d (1 row) -- Check that expected columns are present select * from pg_ls_dir_recurse('.') limit 0; - path | filename | size | access | modification | change | creation | isdir -------+----------+------+--------+--------------+--------+----------+------- + path | 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 fda95828d7..b87965a1bd 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -69,15 +69,15 @@ select pg_ls_dir('does not exist'); -- fails with missingok=false -- 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; -- 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'; +select path, filename, type from pg_ls_dir_recurse('.') where type='d' and path='pg_wal'; -- Check that expected columns are present select * from pg_ls_dir_recurse('.') limit 0; -- 2.17.0 --Tcb1KvpfnM4LxW2s-- ^ permalink raw reply [nested|flat] 2+ messages in thread
* [PATCH 6/8] Add system view tracking IO ops per backend type @ 2021-11-24 17:07 Melanie Plageman <[email protected]> 0 siblings, 0 replies; 2+ messages in thread From: Melanie Plageman @ 2021-11-24 17:07 UTC (permalink / raw) Add pg_stat_buffers, a system view which tracks the number of IO operations (allocs, writes, fsyncs, and extends) done through each IO path (e.g. shared buffers, local buffers, unbuffered IO) by each type of backend. Some of these should always be zero. For example, checkpointer does not use a BufferAccessStrategy (currently), so the "strategy" IO Path for checkpointer will be 0 for all IO operations (alloc, write, fsync, and extend). All possible combinations of IOPath and IOOp are enumerated in the view but not all are populated or even possible at this point. All backends increment a counter in an array of IO stat counters in their PgBackendStatus when performing an IO operation. On exit, backends send these stats to the stats collector to be persisted. When the pg_stat_buffers view is queried, one backend will sum live backends' stats with saved stats from exited backends and subtract saved reset stats, returning the total. Each row of the view is stats for a particular backend type for a particular IO Path (e.g. shared buffer accesses by checkpointer) and each column in the view is the total number of IO operations done (e.g. writes). So a cell in the view would be, for example, the number of shared buffers written by checkpointer since the last stats reset. Suggested by Andres Freund Author: Melanie Plageman <[email protected]> Reviewed-by: Justin Pryzby <[email protected]> Discussion: https://www.postgresql.org/message-id/flat/20200124195226.lth52iydq2n2uilq%40alap3.anarazel.de --- doc/src/sgml/monitoring.sgml | 119 +++++++++++++++- src/backend/catalog/system_views.sql | 11 ++ src/backend/postmaster/pgstat.c | 12 ++ src/backend/utils/activity/backend_status.c | 19 ++- src/backend/utils/adt/pgstatfuncs.c | 150 ++++++++++++++++++++ src/include/catalog/pg_proc.dat | 9 ++ src/include/pgstat.h | 11 ++ src/include/utils/backend_status.h | 1 + src/test/regress/expected/rules.out | 8 ++ 9 files changed, 332 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index dc07cc15f53..e73783fe116 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -435,6 +435,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser </entry> </row> + <row> + <entry><structname>pg_stat_buffers</structname><indexterm><primary>pg_stat_buffers</primary></indexterm></entry> + <entry>A row for each IO path for each backend type showing + statistics about backend IO operations. See + <link linkend="monitoring-pg-stat-buffers-view"> + <structname>pg_stat_buffers</structname></link> for details. + </entry> + </row> + <row> <entry><structname>pg_stat_wal</structname><indexterm><primary>pg_stat_wal</primary></indexterm></entry> <entry>One row only, showing statistics about WAL activity. See @@ -3581,7 +3590,102 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i <structfield>stats_reset</structfield> <type>timestamp with time zone</type> </para> <para> - Time at which these statistics were last reset + Time at which these statistics were last reset. + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + </sect2> + + <sect2 id="monitoring-pg-stat-buffers-view"> + <title><structname>pg_stat_buffers</structname></title> + + <indexterm> + <primary>pg_stat_buffers</primary> + </indexterm> + + <para> + The <structname>pg_stat_buffers</structname> view has a row for each backend + type for each possible IO path containing global data for the cluster for + that backend and IO path. + </para> + + <table id="pg-stat-buffers-view" xreflabel="pg_stat_buffers"> + <title><structname>pg_stat_buffers</structname> View</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>backend_type</structfield> <type>text</type> + </para> + <para> + Type of backend (e.g. background worker, autovacuum worker). + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>io_path</structfield> <type>text</type> + </para> + <para> + IO path taken (e.g. shared buffers, direct). + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>alloc</structfield> <type>bigint</type> + </para> + <para> + Number of buffers allocated. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>extend</structfield> <type>bigint</type> + </para> + <para> + Number of buffers extended. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>fsync</structfield> <type>bigint</type> + </para> + <para> + Number of buffers fsynced. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>write</structfield> <type>bigint</type> + </para> + <para> + Number of buffers written. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>stats_reset</structfield> <type>timestamp with time zone</type> + </para> + <para> + Time at which these statistics were last reset. </para></entry> </row> </tbody> @@ -5186,12 +5290,13 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i </para> <para> Resets some cluster-wide statistics counters to zero, depending on the - argument. The argument can be <literal>buffers</literal> to reset - all the counters shown in - the <structname>pg_stat_bgwriter</structname> - view, <literal>archiver</literal> to reset all the counters shown in - the <structname>pg_stat_archiver</structname> view or <literal>wal</literal> - to reset all the counters shown in the <structname>pg_stat_wal</structname> view. + argument. The argument can be <literal>archiver</literal> to reset all + the counters shown in the <structname>pg_stat_archiver</structname> + view, <literal>buffers</literal> to reset all the counters shown in + both the <structname>pg_stat_bgwriter</structname> view and + <structname>pg_stat_buffers</structname> view, or + <literal>wal</literal> to reset all the counters shown in the + <structname>pg_stat_wal</structname> view. </para> <para> This function is restricted to superusers by default, but other users diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 9eaa51df290..b6cfe3d3f93 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1103,6 +1103,17 @@ CREATE VIEW pg_stat_bgwriter AS pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; +CREATE VIEW pg_stat_buffers AS +SELECT + b.backend_type, + b.io_path, + b.alloc, + b.extend, + b.fsync, + b.write, + b.stats_reset +FROM pg_stat_get_buffers() b; + CREATE VIEW pg_stat_wal AS SELECT w.wal_records, diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 57a7f0fa7e9..a48998fe944 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -1363,6 +1363,18 @@ pgstat_send_inquiry(TimestampTz clock_time, TimestampTz cutoff_time, Oid databas pgstat_send(&msg, sizeof(msg)); } +/* + * Support function for SQL-callable pgstat* functions. Returns a pointer to + * the PgStat_BackendIOPathOps structure tracking IO operations statistics for + * both exited backends and reset arithmetic. + */ +PgStat_BackendIOPathOps * +pgstat_fetch_exited_backend_buffers(void) +{ + backend_read_statsfile(); + return &globalStats.buffers; +} + /* * Support function for the SQL-callable pgstat* functions. Returns * the collected statistics for one database or NULL. NULL doesn't mean diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c index 87b9d0fc0d8..c579014ec25 100644 --- a/src/backend/utils/activity/backend_status.c +++ b/src/backend/utils/activity/backend_status.c @@ -38,7 +38,7 @@ int pgstat_track_activity_query_size = 1024; PgBackendStatus *MyBEEntry = NULL; -static PgBackendStatus *BackendStatusArray = NULL; +PgBackendStatus *BackendStatusArray = NULL; static char *BackendAppnameBuffer = NULL; static char *BackendClientHostnameBuffer = NULL; static char *BackendActivityBuffer = NULL; @@ -241,6 +241,23 @@ CreateSharedBackendStatus(void) #endif } +const char * +GetIOPathDesc(IOPath io_path) +{ + switch (io_path) + { + case IOPATH_DIRECT: + return "direct"; + case IOPATH_LOCAL: + return "local"; + case IOPATH_SHARED: + return "shared"; + case IOPATH_STRATEGY: + return "strategy"; + } + return "unknown IO path"; +} + /* * Initialize pgstats backend activity state, and set up our on-proc-exit * hook. Called from InitPostgres and AuxiliaryProcessMain. For auxiliary diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index ce84525d402..e1213a9ad03 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -1739,6 +1739,156 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS) PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->buf_alloc); } +/* +* When adding a new column to the pg_stat_buffers view, add a new enum +* value here above BUFFERS_NUM_COLUMNS. +*/ +enum +{ + BUFFERS_COLUMN_BACKEND_TYPE, + BUFFERS_COLUMN_IO_PATH, + BUFFERS_COLUMN_ALLOCS, + BUFFERS_COLUMN_EXTENDS, + BUFFERS_COLUMN_FSYNCS, + BUFFERS_COLUMN_WRITES, + BUFFERS_COLUMN_RESET_TIME, + BUFFERS_NUM_COLUMNS, +}; + +/* + * Helper function to get the correct row in the pg_stat_buffers view. + */ +static inline Datum * +get_pg_stat_buffers_row(Datum all_values[BACKEND_NUM_TYPES][IOPATH_NUM_TYPES][BUFFERS_NUM_COLUMNS], + BackendType backend_type, IOPath io_path) +{ + return all_values[backend_type_get_idx(backend_type)][io_path]; +} + +Datum +pg_stat_get_buffers(PG_FUNCTION_ARGS) +{ + PgStat_BackendIOPathOps *backend_io_path_ops; + PgBackendStatus *beentry; + Datum reset_time; + + ReturnSetInfo *rsinfo; + TupleDesc tupdesc; + Tuplestorestate *tupstore; + MemoryContext per_query_ctx; + MemoryContext oldcontext; + + Datum all_values[BACKEND_NUM_TYPES][IOPATH_NUM_TYPES][BUFFERS_NUM_COLUMNS]; + bool all_nulls[BACKEND_NUM_TYPES][IOPATH_NUM_TYPES][BUFFERS_NUM_COLUMNS]; + + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + /* 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_FEATURE_NOT_SUPPORTED), + errmsg("materialize mode required, but it is not allowed in this context"))); + + /* Build a tuple descriptor for our result type */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; + oldcontext = MemoryContextSwitchTo(per_query_ctx); + tupstore = tuplestore_begin_heap((bool) (rsinfo->allowedModes & SFRM_Materialize_Random), + false, work_mem); + rsinfo->returnMode = SFRM_Materialize; + rsinfo->setResult = tupstore; + rsinfo->setDesc = tupdesc; + MemoryContextSwitchTo(oldcontext); + + memset(all_values, 0, sizeof(all_values)); + memset(all_nulls, 0, sizeof(all_nulls)); + + /* Loop through all live backends and count their IO Ops for each IO Path */ + beentry = BackendStatusArray; + + for (int i = 0; i < GetMaxBackends() + NUM_AUXPROCTYPES; i++, beentry++) + { + IOOpCounters *io_ops; + + /* + * Don't count dead backends. They will be added below. There are no + * rows in the view for BackendType B_INVALID, so skip those as well. + */ + if (beentry->st_procpid == 0 || beentry->st_backendType == B_INVALID) + continue; + + io_ops = beentry->io_path_stats; + + for (int i = 0; i < IOPATH_NUM_TYPES; i++) + { + Datum *row = get_pg_stat_buffers_row(all_values, beentry->st_backendType, i); + + /* + * BUFFERS_COLUMN_RESET_TIME, BUFFERS_COLUMN_BACKEND_TYPE, and + * BUFFERS_COLUMN_IO_PATH will all be set when looping through + * exited backends array + */ + row[BUFFERS_COLUMN_ALLOCS] += pg_atomic_read_u64(&io_ops->allocs); + row[BUFFERS_COLUMN_EXTENDS] += pg_atomic_read_u64(&io_ops->extends); + row[BUFFERS_COLUMN_FSYNCS] += pg_atomic_read_u64(&io_ops->fsyncs); + row[BUFFERS_COLUMN_WRITES] += pg_atomic_read_u64(&io_ops->writes); + io_ops++; + } + } + + /* Add stats from all exited backends */ + backend_io_path_ops = pgstat_fetch_exited_backend_buffers(); + + reset_time = TimestampTzGetDatum(backend_io_path_ops->stat_reset_timestamp); + + for (int i = 0; i < BACKEND_NUM_TYPES; i++) + { + BackendType backend_type = idx_get_backend_type(i); + + PgStatIOOpCounters *io_ops = + backend_io_path_ops->ops[i].io_path_ops; + PgStatIOOpCounters *resets = + backend_io_path_ops->resets[i].io_path_ops; + + Datum backend_type_desc = + CStringGetTextDatum(GetBackendTypeDesc(backend_type)); + + for (int j = 0; j < IOPATH_NUM_TYPES; j++) + { + Datum *row = get_pg_stat_buffers_row(all_values, backend_type, j); + + row[BUFFERS_COLUMN_BACKEND_TYPE] = backend_type_desc; + row[BUFFERS_COLUMN_IO_PATH] = CStringGetTextDatum(GetIOPathDesc(j)); + row[BUFFERS_COLUMN_RESET_TIME] = reset_time; + row[BUFFERS_COLUMN_ALLOCS] += io_ops->allocs - resets->allocs; + row[BUFFERS_COLUMN_EXTENDS] += io_ops->extends - resets->extends; + row[BUFFERS_COLUMN_FSYNCS] += io_ops->fsyncs - resets->fsyncs; + row[BUFFERS_COLUMN_WRITES] += io_ops->writes - resets->writes; + io_ops++; + resets++; + } + } + + for (int i = 0; i < BACKEND_NUM_TYPES; i++) + { + for (int j = 0; j < IOPATH_NUM_TYPES; j++) + { + Datum *values = all_values[i][j]; + bool *nulls = all_nulls[i][j]; + + tuplestore_putvalues(tupstore, tupdesc, values, nulls); + } + } + + return (Datum) 0; +} + /* * Returns statistics of WAL activity */ diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 25304430f44..2a3f11c26e9 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5641,6 +5641,15 @@ proname => 'pg_stat_get_buf_alloc', provolatile => 's', proparallel => 'r', prorettype => 'int8', proargtypes => '', prosrc => 'pg_stat_get_buf_alloc' }, +{ oid => '8459', descr => 'statistics: counts of all IO operations done through all IO paths by each type of backend.', + proname => 'pg_stat_get_buffers', provolatile => 's', proisstrict => 'f', + prorows => '52', proretset => 't', + proparallel => 'r', prorettype => 'record', proargtypes => '', + proallargtypes => '{text,text,int8,int8,int8,int8,timestamptz}', + proargmodes => '{o,o,o,o,o,o,o}', + proargnames => '{backend_type,io_path,alloc,extend,fsync,write,stats_reset}', + prosrc => 'pg_stat_get_buffers' }, + { oid => '1136', descr => 'statistics: information about WAL activity', proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => '', diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 9e93580f680..ca96f7c3e4c 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -1207,6 +1207,17 @@ extern void pgstat_sum_io_path_ops(PgStatIOOpCounters *dest, IOOpCounters *src); /* * Functions in pgstat_replslot.c */ +extern PgStat_BackendIOPathOps *pgstat_fetch_exited_backend_buffers(void); +extern PgStat_StatDBEntry *pgstat_fetch_stat_dbentry(Oid dbid); +extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid); +extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid); +extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void); +extern PgStat_BgWriterStats *pgstat_fetch_stat_bgwriter(void); +extern PgStat_CheckpointerStats *pgstat_fetch_stat_checkpointer(void); +extern PgStat_GlobalStats *pgstat_fetch_global(void); +extern PgStat_WalStats *pgstat_fetch_stat_wal(void); +extern PgStat_SLRUStats *pgstat_fetch_slru(void); +extern PgStat_StatReplSlotEntry *pgstat_fetch_replslot(NameData slotname); extern void pgstat_reset_replslot_counter(const char *name); extern void pgstat_report_replslot(const PgStat_StatReplSlotEntry *repSlotStat); diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h index 7e59c063b94..6d623ff7469 100644 --- a/src/include/utils/backend_status.h +++ b/src/include/utils/backend_status.h @@ -316,6 +316,7 @@ extern PGDLLIMPORT int pgstat_track_activity_query_size; * ---------- */ extern PGDLLIMPORT PgBackendStatus *MyBEEntry; +extern PGDLLIMPORT PgBackendStatus *BackendStatusArray; /* ---------- diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 423b9b99fb6..39d0ed45642 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1820,6 +1820,14 @@ pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; +pg_stat_buffers| SELECT b.backend_type, + b.io_path, + b.alloc, + b.extend, + b.fsync, + b.write, + b.stats_reset + FROM pg_stat_get_buffers() b(backend_type, io_path, alloc, extend, fsync, write, stats_reset); pg_stat_database| SELECT d.oid AS datid, d.datname, CASE -- 2.17.1 --wac7ysb48OaltWcw Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0007-Remove-superfluous-bgwriter-stats-code.patch" ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-11-24 17:07 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-03-31 19:40 [PATCH v21 10/10] pg_ls_* to show file type and show special files Justin Pryzby <[email protected]> 2021-11-24 17:07 [PATCH 6/8] Add system view tracking IO ops per backend type Melanie Plageman <[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