Subject: Enhance file_fdw to report processed and skipped tuples in COPY progress

From: Fujii Masao <[email-masked]>
Date: Thu, 3 Oct 2024 18:23:43 +0900

Hi,

Currently, file_fdw updates several columns in the pg_stat_progress_copy view,
like relid and bytes_processed, but it doesn't track tuples_processed or
tuples_skipped. Monitoring these would be particularly useful when handling
large data sets via file_fdw, as it helps track the progress of scan.

The attached patch updates file_fdw to add support for reporting
the number of tuples processed and skipped (due to on_error = 'ignore')
in the pg_stat_progress_copy view. What are your thoughts?

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
From 9309195910d3b77614f330af8a80c38fcbc5c532 Mon Sep 17 00:00:00 2001
From: Fujii Masao <[email-masked]>
Date: Thu, 3 Oct 2024 17:59:11 +0900
Subject: [PATCH v1] file_fdw: Report tuples processed and skipped for COPY
 progress.

This commit enhances file_fdw to report the number of tuples processed
and skipped (due to on_error = 'ignore') in the pg_stat_progress_copy view.
These are shown in the tuples_processed and tuples_skipped columns,
respectively. Previously, they were not reported while other columns like
bytes_processed and bytes_total were updated.

Discussion: https://postgr.es/m/[email-masked]
---
 contrib/file_fdw/file_fdw.c | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 043204c3e7..6b6bf7ff18 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -25,6 +25,7 @@
 #include "commands/copyfrom_internal.h"
 #include "commands/defrem.h"
 #include "commands/explain.h"
+#include "commands/progress.h"
 #include "commands/vacuum.h"
 #include "foreign/fdwapi.h"
 #include "foreign/foreign.h"
@@ -35,6 +36,7 @@
 #include "optimizer/planmain.h"
 #include "optimizer/restrictinfo.h"
 #include "utils/acl.h"
+#include "utils/backend_progress.h"
 #include "utils/memutils.h"
 #include "utils/rel.h"
 #include "utils/sampling.h"
@@ -773,6 +775,10 @@ retry:
 			 */
 			cstate->escontext->error_occurred = false;
 
+			/* Report that this tuple was skipped due to ON_ERROR = ignore */
+			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
+										 cstate->num_errors);
+
 			/* Switch back to original memory context */
 			MemoryContextSwitchTo(oldcontext);
 
@@ -801,6 +807,9 @@ retry:
 	/* Remove error callback. */
 	error_context_stack = errcallback.previous;
 
+	/* Update the processed tuple count for COPY progress reporting */
+	pgstat_progress_incr_param(PROGRESS_COPY_TUPLES_PROCESSED, 1);
+
 	return slot;
 }
 
@@ -1253,6 +1262,10 @@ file_acquire_sample_rows(Relation onerel, int elevel,
 			 */
 			cstate->escontext->error_occurred = false;
 
+			/* Report that this tuple was skipped due to ON_ERROR = ignore */
+			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
+										 cstate->num_errors);
+
 			/* Repeat NextCopyFrom() until no soft error occurs */
 			continue;
 		}
@@ -1294,6 +1307,9 @@ file_acquire_sample_rows(Relation onerel, int elevel,
 		}
 
 		*totalrows += 1;
+
+		/* Update the processed tuple count for COPY progress reporting */
+		pgstat_progress_update_param(PROGRESS_COPY_TUPLES_PROCESSED, *totalrows);
 	}
 
 	/* Remove error callback. */
-- 
2.45.2