Add on_error and log_verbosity options to file_fdw

Поиск
Список
Период
Сортировка
От torikoshia
Тема Add on_error and log_verbosity options to file_fdw
Дата
Msg-id ab59dad10490ea3734cf022b16c24cfd@oss.nttdata.com
обсуждение исходный текст
Список pgsql-hackers
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

-- 
Regards,

--
Atsushi Torikoshi
NTT DATA Group Corporation
Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: Problem while installing PostgreSQL using make
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: Improving PL/Tcl's error context reports