Re: Add on_error and log_verbosity options to file_fdw

On 2024-07-05 00:27, torikoshia wrote:
> Hi,
> 
> With the current file_fdw, if even one line of data conversion fails,
> the contents of the file cannot be referenced at all:
> 
>   =# \! cat data/test.data
>   1,a
>   2,b
>   a,c
>   =# create foreign table f_fdw_test_1 (i int, t text) server f_fdw
> options (filename 'test.data', format 'csv');
>   CREATE FOREIGN TABLE
> 
>   =# table f_fdw_test_1;
>   ERROR:  invalid input syntax for type integer: "a"
>   CONTEXT:  COPY f_fdw_test, line 3, column i: "a"
> 
> Since we'll support ON_ERROR option which tolerates data conversion
> errors in COPY FROM and LOG_VERBOSITY option at v17[1], how about
> supporting them on file_fdw?
> 
> This idea comes from Fujii-san[2], and I think it'd be useful when
> reading a bit dirty data.
> 
> Attached PoC patch works like below:
> 
>   =# create foreign table f_fdw_test_2 (i int, t text) server f_fdw
> options (filename 'test.data', format 'csv', on_error 'ignore');
>   CREATE FOREIGN TABLE
> 
>   =# table f_fdw_test_2;
>   NOTICE:  1 row was skipped due to data type incompatibility
>    i | t
>   ---+---
>    1 | a
>    2 | b
>   (2 rows)
> 
> 
>   =# create foreign table f_fdw_test_3 (i int, t text) server f_fdw
> options (filename 'test.data', format 'csv', on_error 'ignore',
> log_verbosity 'verbose');
> CREATE FOREIGN TABLE
> 
>   =# table f_fdw_test_3 ;
>   NOTICE:  skipping row due to data type incompatibility at line 3 for
> column i: "a"
>   NOTICE:  1 row was skipped due to data type incompatibility
>    i | t
>   ---+---
>    1 | a
>    2 | b
>   (2 rows)
> 
> 
> I'm going to continue developing the patch(e.g. add doc, measure
> performance degradation) when people also think this feature is worth
> adding.
> 
> 
> What do you think?
> 
> 
> [1] https://www.postgresql.org/docs/devel/sql-copy.html
> [2] https://x.com/fujii_masao/status/1808178032219509041

Update the patch since v1 patch caused compiler warning.

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA Group Corporation