Date: Fri, 11 Oct 2024 15:36:45 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: Fujii Masao <masao.fujii@oss.nttdata.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Pgsql Hackers
 <pgsql-hackers@postgresql.org>
Subject: Re: Enhance file_fdw to report processed and skipped tuples in COPY
 progress
Message-Id: <20241011153645.a348de1576a3f57092c68355@sraoss.co.jp>
In-Reply-To: <47780072-032c-4c33-a8cf-384d50545b8a@oss.nttdata.com>
References: <2ffa9bfa-41f6-4180-a32a-2c693ec1eeed@oss.nttdata.com>
	<CAD21AoDDJiJRZapB+mbyvetPp_jmYh3bbhu-ntn+911cmWdiyA@mail.gmail.com>
	<47780072-032c-4c33-a8cf-384d50545b8a@oss.nttdata.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?utf-8?B?MGpULzJoa1ROSTJkYlBCbmpwdzJZSXBxaFhOeVU5ZWpZb0VhYmZUeUZrQ0Jy?=
 =?utf-8?B?Zm1FSENrR1ZaU3I0VmdRdVBVRVRCc2VseFhmQkRGY0tucUVEd1oyVEhxNHFB?=
 =?utf-8?B?MXJ1UkhKUGhodDZralVXeEhhR3RkU2xJL1AyYmNocVcySXYrSzM4dUMzMFFY?=
 =?utf-8?B?dmhpblNibVFUY1YvK0dBbUdCZit2d3hhb0oyMnJ1YTI3ZW5GcDk0ZUNhNTJt?=
 =?utf-8?B?WHdENFFFNVIzWGlTMUtqSVN2d1czRXlybUpxamdaY1hxRjFNbmNKQkdCU1hH?=
 =?utf-8?B?VXlDeFF1cVhSV29CQkY1cEZDSXFYdmxHSDZsenlyR3dNZXBGajdUR0t0UjNG?=
 =?utf-8?B?VFk0M0piRDBHdkxNczJPNG9SS0MrdWJRWHZhY1A0SDhrUGJ5THYyRTBUM0l0?=
 =?utf-8?B?WFJCeWp5QlZERnpVeVNvUERKcGJ3RlBvWjRYYS9ncHorTzNrcHp6UjIzbmpB?=
 =?utf-8?B?Tjh1dENlb2hlRmN1aFcyeHF6S1JtZG9NUXlqWks4WVNMa29meE4yMW14c1ln?=
 =?utf-8?B?VHRLeFB3VVY3WUpJNXgvQVFqNW5yNzBlNTV2MEdGSHFwcVlYZzJTRUM2WFFm?=
 =?utf-8?B?eFM4aG1jMnNuSFNIN1N6Qy9TK3pRcmhad2FwZGMwd2lWR1FxNkx5T0R1SXo5?=
 =?utf-8?B?cjdFRWcxV0dXSWx0YXBjeXBNaWQ2dHFPVWlMTXNMOW1lZW9qdjh2YkJSTTYv?=
 =?utf-8?B?U2h6ZjZ2Z0I4Y0ZGVitSbUI0K1FzaXEvdTdJQndhclBkYmp3MXFHZi9yTVhE?=
 =?utf-8?B?eUxzdzdtSEtleitHUmZrMFNsM2h2VGZDdkdOQ052V080YUpyTXFsV2R5T3Jl?=
 =?utf-8?B?RG5WUDQxcjFQWEF2dVV4UW5CNlVTbFdtakRBSTQ3VFk4SXpwMzZRZVJHbk5s?=
 =?utf-8?B?VFlKNlhRNEZ5SEF4NzgwNG9YQVZhWWZEVFdPZ2xNWS95WWYwQVZ0QTBOa0xH?=
 =?utf-8?B?SG9kT3NtZE1VbEdnaUsvR2hqdzFFODJMRFYvM29nQU5QM3V6MU5BNkgrVm9S?=
 =?utf-8?B?N1RzRFVsU0VIaktPMlgxc2twVmJKWXU2Y1cxbEZBODNPaHZWVG1CbS9pUmxm?=
 =?utf-8?B?M3JJaGlsdFhYMGlZLzBsTDdnSUJCV2NXeUY1NmFnNjBza1lxQm1ra0lVRzB6?=
 =?utf-8?B?Sk8yQVl0RWpmZ3Q1R0g2SjBxc1hXUTRWdm9HTytDcEx3QTllK09KaDFyL0dm?=
 =?utf-8?B?MmxPanVZL3N6clFLLzJ3QmJrc2ZTMTRjN3VpVFB2MHMyVXRmUkh4cjUzZFkw?=
 =?utf-8?B?bUVQbE5ZMW1hRFRwYno4d2E4MnFwbm40WFpKZkJEaEoyOElLYjBvS2I5UGg0?=
 =?utf-8?B?aE5yK3hoNGY3am9EZTMySStBQjhJSEllaGpwVC95MFVEcWY2THJEQkIyblZL?=
 =?utf-8?B?VGNBSXg0d1d4Tnc0VlpSME5ON3hjb0xYMEdXdEVWMnZleDhCR2xxR2gxY0I5?=
 =?utf-8?B?K2E0bnBEOE5nb1FZRTRrYWNLRUNhSkNOTTN0UExtcG0wTUh2QUlQWE44RjdN?=
 =?utf-8?B?RHNZM0NGUFFwcTBxaE9UNVVxYjFmOGVjK2t1bDNYanZFM1NXSDZhblAyYUJN?=
 =?utf-8?B?ck4zZzFxTXdVN0s5MEp6b0F4UUdDdHM0TkFBUmJ0ZjdHRGxHL3BoQzFkaldn?=
 =?utf-8?B?eUFiWVRRdTA2QVhKSUJTTWpnbjFvVEN1OWMya1Nrd3c4SXJkZHFuZ2JoeVk2?=
 =?utf-8?B?Wk9xNlZIVHJKakplUkw0bmVSd1dDc0NmVVlNT0JvMk5BZEYwMHdQeVAzT3pK?=
 =?utf-8?B?R0xycjhPK1JGL2NKUkNNMVBaUUlGejREdUxPMTNKeHF0V3pHdEl3WEJqZ2x1?=
 =?utf-8?B?aDFRcXMwK0VEd0N0UGRkZEtqVjZYU1NJWmZoVVFWcjFWNTlxMlllSTVXdXdQ?=
 =?utf-8?B?WDI3NlgzUXdGZGJKajJNQ2s0RXpWSE0vcWRkRlZwajZ5QnpENklmeGw1N2R6?=
 =?utf-8?B?VTU1V2dYKzJKS2xUaStOOHJ4V1JFYlJONFozUzJ2SGR0ZG5zaUNwM05qb25W?=
 =?utf-8?B?MWZKUE4wdjhZV21PWWhLbm9lOFBIN2VnSWgzKzEzV0I3aG5LenczMXZRWUhS?=
 =?utf-8?B?SWpzRWRVOVZ1UFp4S1JmNmNTc1JaWFl6OTRMZW9xRHg4WXNBVzZiNFNzd29R?=
 =?utf-8?B?R09pUlF3dGtTY1hTUUVBaW1MenJBRGpTSDhhSEdhTjg4ZUlmR1AxaXRzRTM3?=
 =?utf-8?Q?rio0OwHJb8XIvwvcQcrUJ5kI9+AsRoFyZpQk78wvR47k?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: bdb4e75d-7f26-4970-a232-08dce9bf13dd
X-MS-Exchange-CrossTenant-AuthSource: TYBP286MB0111.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 11 Oct 2024 06:36:46.2539
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: BbBRn9DPRmKQCfw0MK/JsM2W9mkVaHMDJslv1F7szhuOhEEyMp4LGQ40PtmmNc6oATLVEfTxrRa4svoqbWYmqA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TY3P286MB3669
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/20241011153645.a348de1576a3f57092c68355%40sraoss.co.jp>
Precedence: bulk

On Fri, 11 Oct 2024 10:53:10 +0900
Fujii Masao <masao.fujii@oss.nttdata.com> wrote:

> 
> 
> On 2024/10/04 2:12, Masahiko Sawada wrote:
> > Hi,
> > 
> > On Thu, Oct 3, 2024 at 2:23 AM Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
> >>
> >> 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?
> > 
> > While the patch works fine and looks good to me, in the first place,
> > it seems to me that the fact that file_fdw uses the COPY progress
> > itself doesn't work properly. For example, unlike COPY command,
> > queries could have multiple scans on one or more flie_fdw foreign
> > tables when joining tables. I found the discussion for that[1]: there
> > was a proposal of disabling COPY progress for file_fdw but the votes
> > are split. I think it would be better to consider if we really want to
> > support COPY progress for file_fdw before supporting more progress
> > information.
> 
> Yes, you're right. We need to address how to handle multiple commands
> that trigger progress reporting when executed concurrently, at first.
> 
> The current progress reporting mechanism assumes only one command
> triggering progress is running at a time, as each backend has just
> one memory area for progress reporting. If multiple commands run simultaneously,
> the progress data would be incorrect. As you mentioned, this could happen
> when querying multiple file_fdw foreign tables, where multiple COPY commands
> could execute concurrently.
> 
> However, this issue already exists without the proposed patch.
> Since file_fdw already reports progress partially, querying multiple
> file_fdw tables can lead to inaccurate or confusing progress reports.
> You can even observe this when analyzing a file_fdw table and also
> when copying to the table with a trigger that executes progress-reporting
> commands.
> 
> So, I don’t think this issue should block the proposed patch.
> In fact, progress reporting is already flawed in these scenarios,
> regardless of whether the patch is applied.
> 
> On the other hand, in many cases where a single file_fdw table is scanned,
> COPY progress reporting works correctly for file_fdw and is useful.
> Therefore, I believe it's still worth improving file_fdw’s progress reporting.

I think reporting tuples_processed and tuples_skipped columns additionally
in file_fdw is reasonable, since it already reports bytes_processed and bytes_total.

By the way, in the documentation of fild_fdw, it is not explicitly described
that file_fdw uses COPY internally, although I can find several wordings like "as COPY".
To prevent users to face unexpected experiences, how about explaining explicitly that
file_fdw uses COPY and updates pg_stat_progress_copy?


> To prevent misleading reports when multiple commands are run concurrently,
> just idea, we could consider displaying NULL columns in the progress report
> if this situation is detected, as a separate patch.

Or, could we add additional field to pg_stat_progress_copy to
show how much commands are running COPY?  It is also just idea.

Regards,
Yugo Nagata

-- 
Yugo Nagata <nagata@sraoss.co.jp>