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
* enhance the efficiency of migrating particularly large tables
@ 2024-04-08 21:52 David Zhang <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David Zhang @ 2024-04-08 21:52 UTC (permalink / raw)
To: Pgsql Hackers <[email protected]>
Hi Postgres hackers,
I'm reaching out to gather some comments on enhancing the efficiency of
migrating particularly large tables with significant data volumes in
PostgreSQL.
When migrating a particularly large table with a significant amount of
data, users sometimes tend to split the table into multiple segments and
utilize multiple sessions to process data from different segments in
parallel, aiming to enhance efficiency. When segmenting a large table,
it's challenging if the table lacks fields suitable for segmentation or
if the data distribution is uneven. I believe that the data volume in
each block should be relatively balanced when vacuum is enabled.
Therefore, the ctid can be used to segment a large table, and I am
thinking the entire process can be outlined as follows:
1) determine the minimum and maximum ctid.
2) calculate the number of data blocks based on the maximum and minimum
ctid.
3) generate multiple SQL queries, such as SELECT * FROM tbl WHERE ctid
>= '(xx,1)' AND ctid < '(xxx,1)'.
However, when executing SELECT min(ctid) and max(ctid), it performs a
Seq Scan, which can be slow for a large table. Is there a way to
retrieve the minimum and maximum ctid other than using the system
functions min() and max()?
Since the minimum and maximum ctid are in order, theoretically, it
should start searching from the first block and can stop as soon as it
finds the first available one when retrieving the minimum ctid.
Similarly, it should start searching in reverse order from the last
block and stop upon finding the first occurrence when retrieving the
maximum ctid. Here's a piece of code snippet:
/* scan the relation for minimum or maximum ctid */
if (find_max_ctid)
dir = BackwardScanDirection;
else
dir = ForwardScanDirection;
while ((tuple = heap_getnext(scan, dir)) != NULL)
...
The attached is a simple POC by referring to the extension pgstattuple.
Any feedback, suggestions, or alternative solutions from the community
would be greatly appreciated.
Thank you,
David
#include "postgres.h"
#include "fmgr.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/primnodes.h"
#include "utils/relcache.h"
#include "catalog/namespace.h"
#include "catalog/pg_am_d.h"
#include "utils/varlena.h"
#include "storage/bufmgr.h"
#include "utils/snapmgr.h"
#include "access/heapam.h"
#include "access/relscan.h"
#include "access/tableam.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(get_ctid);
Datum
get_ctid(PG_FUNCTION_ARGS)
{
bool find_max_ctid = 0;
text *relname;
RangeVar *relrv;
Relation rel;
char ctid[32] = {0};
if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("two parameters are required")));
relname = PG_GETARG_TEXT_PP(0);
find_max_ctid = PG_GETARG_UINT32(1);
/* open relation */
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
if (RELATION_IS_OTHER_TEMP(rel))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
if (RELKIND_HAS_TABLE_AM(rel->rd_rel->relkind) ||
rel->rd_rel->relkind == RELKIND_SEQUENCE)
{
TableScanDesc scan;
HeapScanDesc hscan;
HeapTuple tuple;
SnapshotData SnapshotDirty;
BlockNumber blockNumber;
OffsetNumber offsetNumber;
ScanDirection dir;
if (rel->rd_rel->relam != HEAP_TABLE_AM_OID)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("only heap AM is supported")));
/* Disable syncscan because we assume we scan from block zero upwards */
scan = table_beginscan_strat(rel, SnapshotAny, 0, NULL, true, false);
hscan = (HeapScanDesc) scan;
InitDirtySnapshot(SnapshotDirty);
/* scan the relation for minimum or maximum ctid */
if (find_max_ctid)
dir = BackwardScanDirection;
else
dir = ForwardScanDirection;
while ((tuple = heap_getnext(scan, dir)) != NULL)
{
CHECK_FOR_INTERRUPTS();
/* must hold a buffer lock to call HeapTupleSatisfiesVisibility */
LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
if (HeapTupleSatisfiesVisibility(tuple, &SnapshotDirty, hscan->rs_cbuf))
{
blockNumber = ItemPointerGetBlockNumberNoCheck(&tuple->t_self);
offsetNumber = ItemPointerGetOffsetNumberNoCheck(&tuple->t_self);
/* Perhaps someday we should output this as a record. */
snprintf(ctid, sizeof(ctid), "(%u,%u)", blockNumber, offsetNumber);
LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
break;
}
LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
}
table_endscan(scan);
relation_close(rel, AccessShareLock);
PG_RETURN_CSTRING(pstrdup(ctid));
}
else
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot get tuple-level statistics for relation \"%s\"",
RelationGetRelationName(rel)),
errdetail_relkind_not_supported(rel->rd_rel->relkind)));
}
PG_RETURN_CSTRING(pstrdup("(0,0)")); /* should not happen */
}
Attachments:
[text/plain] get_ctid.c (2.9K, ../../[email protected]/2-get_ctid.c)
download | inline:
#include "postgres.h"
#include "fmgr.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/primnodes.h"
#include "utils/relcache.h"
#include "catalog/namespace.h"
#include "catalog/pg_am_d.h"
#include "utils/varlena.h"
#include "storage/bufmgr.h"
#include "utils/snapmgr.h"
#include "access/heapam.h"
#include "access/relscan.h"
#include "access/tableam.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(get_ctid);
Datum
get_ctid(PG_FUNCTION_ARGS)
{
bool find_max_ctid = 0;
text *relname;
RangeVar *relrv;
Relation rel;
char ctid[32] = {0};
if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("two parameters are required")));
relname = PG_GETARG_TEXT_PP(0);
find_max_ctid = PG_GETARG_UINT32(1);
/* open relation */
relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
rel = relation_openrv(relrv, AccessShareLock);
if (RELATION_IS_OTHER_TEMP(rel))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot access temporary tables of other sessions")));
if (RELKIND_HAS_TABLE_AM(rel->rd_rel->relkind) ||
rel->rd_rel->relkind == RELKIND_SEQUENCE)
{
TableScanDesc scan;
HeapScanDesc hscan;
HeapTuple tuple;
SnapshotData SnapshotDirty;
BlockNumber blockNumber;
OffsetNumber offsetNumber;
ScanDirection dir;
if (rel->rd_rel->relam != HEAP_TABLE_AM_OID)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("only heap AM is supported")));
/* Disable syncscan because we assume we scan from block zero upwards */
scan = table_beginscan_strat(rel, SnapshotAny, 0, NULL, true, false);
hscan = (HeapScanDesc) scan;
InitDirtySnapshot(SnapshotDirty);
/* scan the relation for minimum or maximum ctid */
if (find_max_ctid)
dir = BackwardScanDirection;
else
dir = ForwardScanDirection;
while ((tuple = heap_getnext(scan, dir)) != NULL)
{
CHECK_FOR_INTERRUPTS();
/* must hold a buffer lock to call HeapTupleSatisfiesVisibility */
LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_SHARE);
if (HeapTupleSatisfiesVisibility(tuple, &SnapshotDirty, hscan->rs_cbuf))
{
blockNumber = ItemPointerGetBlockNumberNoCheck(&tuple->t_self);
offsetNumber = ItemPointerGetOffsetNumberNoCheck(&tuple->t_self);
/* Perhaps someday we should output this as a record. */
snprintf(ctid, sizeof(ctid), "(%u,%u)", blockNumber, offsetNumber);
LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
break;
}
LockBuffer(hscan->rs_cbuf, BUFFER_LOCK_UNLOCK);
}
table_endscan(scan);
relation_close(rel, AccessShareLock);
PG_RETURN_CSTRING(pstrdup(ctid));
}
else
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot get tuple-level statistics for relation \"%s\"",
RelationGetRelationName(rel)),
errdetail_relkind_not_supported(rel->rd_rel->relkind)));
}
PG_RETURN_CSTRING(pstrdup("(0,0)")); /* should not happen */
}
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-08 21:52 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]>
2024-04-08 21:52 enhance the efficiency of migrating particularly large tables David Zhang <[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