[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-hackers
Subject: Re: logical decoding/replication: new functions pg_ls_logicaldir and pg_ls_replslotdir
From: Bharath Rupireddy <bharath.rupireddyforpostgres () gmail ! com>
Date: 2021-10-30 9:43:42
Message-ID: CALj2ACUOgGte_HjHK+Ho+g7ef9Rs0O8y-UWQ-FC=E7FsH297HQ () mail ! gmail ! com
[Download RAW message or body]
On Sat, Oct 23, 2021 at 11:10 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> On Fri, Oct 22, 2021 at 9:26 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> > I concluded that it's better to add a function to list metadata of an arbitrary
> > dir, rather than adding more functions to handle specific, hardcoded dirs:
> > https://www.postgresql.org/message-id/flat/20191227170220.GE12890@telsasoft.com
>
> I just had a quick look at the pg_ls_dir_metadata() patch(I didn't
> look at the other patches). While it's a good idea to have a single
> function for all the PGDATA directories, I'm not sure if one would
> ever need the info like type, change, creation path etc. If we do
> this, the function will become the linux equivalent command. I don't
> see the difference between modification and change time stamps. For
> debugging or analytical purposes in production environments, one would
> majorly look at the file name, it's size on the disk, modification
> time (to decide whether the file is stale or not, creation time (to
> decide how old is the file), file/directory(maybe?). I'm not sure if
> your patch has a recursive option for pg_ls_dir_metadata(), if it has,
> I think it's more complex from a usability perspective.
>
> And the functions like pg_ls_tmpdir, pg_ls_tmpdir, pg_ls_waldir etc.
> (existing) and pg_ls_logicaldir, pg_ls_replslotdir (yet to have) will
> provide the better usability compared to a generic function. Having
> said this, I don't oppose having a generic function returning the file
> name, file size, modification time, creation time, but not other info,
> please. If one is interested in knowing the other information file
> type, path etc. they can go run linux/windows/OS commands.
>
> In summary what I think at this point is:
> 1) pg_ls_logicaldir, pg_ls_replslotdir - better for usability and
> serving special purpose like their peers
I've added 3 functions pg_ls_logicalsnapdir, pg_ls_logicalmapdir,
pg_ls_replslotdir, and attached the patch. The sample output looks
like [1]. Please review it further.
Here's the CF entry - https://commitfest.postgresql.org/35/3390/
[1]
postgres=# select pg_ls_logicalsnapdir();
pg_ls_logicalsnapdir
-----------------------------------------------
(0-14A50C0.snap,128,"2021-10-30 09:15:56+00")
(0-14C46D8.snap,128,"2021-10-30 09:16:05+00")
(0-14C97C8.snap,132,"2021-10-30 09:16:20+00")
postgres=# select pg_ls_logicalmapdir();
pg_ls_logicalmapdir
---------------------------------------------------------------
(map-31d5-4eb-0_CDDDE88-2d9-2db,108,"2021-10-30 09:24:34+00")
(map-31d5-4eb-0_CDDDE88-2da-2db,108,"2021-10-30 09:24:34+00")
(map-31d5-4eb-0_CE48038-2dc-2de,108,"2021-10-30 09:24:35+00")
(map-31d5-4eb-0_CE6BAF0-2dd-2df,108,"2021-10-30 09:24:35+00")
(map-31d5-4eb-0_CD97DE0-2d9-2d9,36,"2021-10-30 09:24:30+00")
(map-31d5-4eb-0_CE24808-2da-2dd,108,"2021-10-30 09:24:35+00")
(map-31d5-4eb-0_CE01200-2dc-2dc,36,"2021-10-30 09:24:34+00")
(map-31d5-4eb-0_CDDDE88-2db-2db,36,"2021-10-30 09:24:34+00")
(map-31d5-4eb-0_CE6BAF0-2dc-2df,108,"2021-10-30 09:24:35+00")
(map-31d5-4eb-0_CDBA920-2d9-2da,108,"2021-10-30 09:24:32+00")
(map-31d5-4eb-0_CE01200-2da-2dc,108,"2021-10-30 09:24:34+00")
(map-31d5-4eb-0_CE6BAF0-2d9-2df,108,"2021-10-30 09:24:35+00")
(map-31d5-4eb-0_CE24808-2db-2dd,108,"2021-10-30 09:24:35+00")
(map-31d5-4eb-0_CE6BAF0-2db-2df,108,"2021-10-30 09:24:35+00")
(map-31d5-4eb-0_CE24808-2dd-2dd,36,"2021-10-30 09:24:35+00")
(map-31d5-4eb-0_CE24808-2dc-2dd,108,"2021-10-30 09:24:35+00")
(map-31d5-4eb-0_CD74E48-2d8-2d8,36,"2021-10-30 09:24:25+00")
(map-31d5-4eb-0_CE24808-2d9-2dd,108,"2021-10-30 09:24:35+00")
postgres=# select pg_ls_replslotdir('mysub');
pg_ls_replslotdir
-----------------------------------------------------------------
(xid-722-lsn-0-2000000.spill,36592640,"2021-10-30 09:18:29+00")
(xid-722-lsn-0-5000000.spill,4577860,"2021-10-30 09:18:32+00")
(state,200,"2021-10-30 09:18:25+00")
(xid-722-lsn-0-1000000.spill,25644220,"2021-10-30 09:18:29+00")
(xid-722-lsn-0-4000000.spill,36592640,"2021-10-30 09:18:32+00")
(xid-722-lsn-0-3000000.spill,36592640,"2021-10-30 09:18:32+00")
Regards,
Bharath Rupireddy.
["v1-0001-Add-pg_ls_logicalsnapdir-pg_ls_logicalmapdir-and-.patch" (application/octet-stream)]
From 428681ee0a66b98136445d607bd51026b8a204b4 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sat, 30 Oct 2021 09:27:36 +0000
Subject: [PATCH v1] Add pg_ls_logicalsnapdir, pg_ls_logicalmapdir and
pg_ls_replslotdir functions
These functions lists the contents of the respective directories,
and are intended to be used by monitoring tools. Unlike pg_ls_dir(),
access to it can be granted to non-superusers so that those monitoring
tools can observe the principle of least privilege. Access is also
given by default to members of pg_monitor.
---
doc/src/sgml/func.sgml | 72 ++++++++++++++++++++++++
src/backend/catalog/system_functions.sql | 12 ++++
src/backend/utils/adt/genfile.c | 34 +++++++++++
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 15 +++++
5 files changed, 134 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4b49dff2ff..51825edb01 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27407,6 +27407,78 @@ SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');
can be granted EXECUTE to run the function.
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_ls_logicalsnapdir</primary>
+ </indexterm>
+ <function>pg_ls_logicalsnapdir</function> ()
+ <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> )
+ </para>
+ <para>
+ Returns the name, size, and last modification time (mtime) of each
+ ordinary file in the server's pg_logical/snapshots directory.
+ Filenames beginning with a dot, directories, and other special files
+ are excluded.
+ </para>
+ <para>
+ This function is restricted to superusers and members of
+ the <literal>pg_monitor</literal> role 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>
+ <primary>pg_ls_logicalmapdir</primary>
+ </indexterm>
+ <function>pg_ls_logicalmapdir</function> ()
+ <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> )
+ </para>
+ <para>
+ Returns the name, size, and last modification time (mtime) of each
+ ordinary file in the server's pg_logical/mappings directory.
+ Filenames beginning with a dot, directories, and other special files
+ are excluded.
+ </para>
+ <para>
+ This function is restricted to superusers and members of
+ the <literal>pg_monitor</literal> role 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>
+ <primary>pg_ls_replslotdir</primary>
+ </indexterm>
+ <function>pg_ls_replslotdir</function> ( <parameter>slot_name</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> )
+ </para>
+ <para>
+ Returns the name, size, and last modification time (mtime) of each
+ ordinary file in the server's pg_replslot/<parameter>slot_name</parameter> directory.
+ Filenames beginning with a dot, directories, and other special files
+ are excluded.
+ </para>
+ <para>
+ This function is restricted to superusers and members of
+ the <literal>pg_monitor</literal> role by default, but other users can
+ be granted EXECUTE to run the function.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 54c93b16c4..f6789025a5 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -701,6 +701,12 @@ REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer) FROM PUBLIC;
+REVOKE EXECUTE ON FUNCTION pg_ls_logicalsnapdir() FROM PUBLIC;
+
+REVOKE EXECUTE ON FUNCTION pg_ls_logicalmapdir() FROM PUBLIC;
+
+REVOKE EXECUTE ON FUNCTION pg_ls_replslotdir(text) FROM PUBLIC;
+
--
-- We also set up some things as accessible to standard roles.
--
@@ -715,6 +721,12 @@ GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor;
GRANT EXECUTE ON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_ls_logicalsnapdir() TO pg_monitor;
+
+GRANT EXECUTE ON FUNCTION pg_ls_logicalmapdir() TO pg_monitor;
+
+GRANT EXECUTE ON FUNCTION pg_ls_replslotdir(text) TO pg_monitor;
+
GRANT pg_read_all_settings TO pg_monitor;
GRANT pg_read_all_stats TO pg_monitor;
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index c436d9318b..a83700c576 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -720,3 +720,37 @@ pg_ls_archive_statusdir(PG_FUNCTION_ARGS)
{
return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status", true);
}
+
+/*
+ * Function to return the list of files in the pg_logical/snapshots directory.
+ */
+Datum
+pg_ls_logicalsnapdir(PG_FUNCTION_ARGS)
+{
+ return pg_ls_dir_files(fcinfo, "pg_logical/snapshots", false);
+}
+
+/*
+ * Function to return the list of files in the pg_logical/mappings directory.
+ */
+Datum
+pg_ls_logicalmapdir(PG_FUNCTION_ARGS)
+{
+ return pg_ls_dir_files(fcinfo, "pg_logical/mappings", false);
+}
+
+/*
+ * Function to return the list of files in the pg_replslot/<replication_slot>
+ * directory.
+ */
+Datum
+pg_ls_replslotdir(PG_FUNCTION_ARGS)
+{
+ text *filename_t = PG_GETARG_TEXT_PP(0);
+ char path[MAXPGPATH + 11];
+ char *filename;
+
+ filename = text_to_cstring(filename_t);
+ snprintf(path, sizeof(path), "%s/%s", "pg_replslot", filename);
+ return pg_ls_dir_files(fcinfo, path, false);
+}
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 9faf017457..f1c2c4d1e9 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202110272
+#define CATALOG_VERSION_NO 202110300
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d068d6532e..ca0d6ca1d1 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11619,6 +11619,21 @@
proallargtypes => '{oid,text,int8,timestamptz}', proargmodes => '{i,o,o,o}',
proargnames => '{tablespace,name,size,modification}',
prosrc => 'pg_ls_tmpdir_1arg' },
+{ oid => '4642', descr => 'list of files in the pg_logical/snapshots directory',
+ proname => 'pg_ls_logicalsnapdir', 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_logicalsnapdir' },
+{ oid => '4643', descr => 'list of files in the pg_logical/mappings directory',
+ proname => 'pg_ls_logicalmapdir', 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_logicalmapdir' },
+{ oid => '4644', descr => 'list of files in the pg_logical/mappings directory',
+ proname => 'pg_ls_replslotdir', procost => '10', prorows => '20', proretset => 't',
+ provolatile => 'v', prorettype => 'record', proargtypes => 'text',
+ proallargtypes => '{text,text,int8,timestamptz}', proargmodes => '{i,o,o,o}',
+ proargnames => '{slot_name,name,size,modification}', prosrc => 'pg_ls_replslotdir' },
# hash partitioning constraint function
{ oid => '5028', descr => 'hash partition CHECK constraint',
--
2.25.1
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic