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 обсуждение исходный текст |
Ответы |
Re: Add on_error and log_verbosity options to file_fdw
|
Список | 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 по дате отправления: