From: | torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> |
---|---|
To: | Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Add on_error and log_verbosity options to file_fdw |
Date: | 2024-07-04 15:27:30 |
Message-ID: | ab59dad10490ea3734cf022b16c24cfd@oss.nttdata.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
Attachment | Content-Type | Size |
---|---|---|
v1-0001-PoC-patch-for-adding-on_error-and-log_verbosity-o.patch | text/x-diff | 5.9 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2024-07-04 15:27:43 | Re: Improving PL/Tcl's error context reports |
Previous Message | Alvaro Herrera | 2024-07-04 15:19:22 | Re: Problem while installing PostgreSQL using make |