MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="=_fc562c6c67b9ba821d7c219174127cd7"
Content-Transfer-Encoding: 7bit
Date: Fri, 19 Jul 2024 10:37:47 +0900
From: torikoshia <torikoshia@oss.nttdata.com>
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Add on_error and log_verbosity options to file_fdw
In-Reply-To: <ab59dad10490ea3734cf022b16c24cfd@oss.nttdata.com>
References: <ab59dad10490ea3734cf022b16c24cfd@oss.nttdata.com>
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <841721f3ca9a69574a72eb141c83933c@oss.nttdata.com>
Archived-At: <https://www.postgresql.org/message-id/841721f3ca9a69574a72eb141c83933c%40oss.nttdata.com>
Precedence: bulk

--=_fc562c6c67b9ba821d7c219174127cd7
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII;
 format=flowed

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
--=_fc562c6c67b9ba821d7c219174127cd7
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
 name=v2-0001-PoC-patch-for-adding-on_error-and-log_verbosity-o.patch
Content-Disposition: attachment;
 filename=v2-0001-PoC-patch-for-adding-on_error-and-log_verbosity-o.patch;
 size=6177

RnJvbSBiNjI5NTU5MDk3NzQ3OWZmYjYwMWM5ODQ4YzYxOTRhNTFkNzVlOTMyIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBdHN1c2hpIFRvcmlrb3NoaSA8dG9yaWtvc2hpYUBvc3MubnR0
ZGF0YS5jb20+CkRhdGU6IEZyaSwgMTkgSnVsIDIwMjQgMTA6MTI6MjAgKzA5MDAKU3ViamVjdDog
W1BBVENIIHYyXSBBZGQgb25fZXJyb3IgYW5kIGxvZ192ZXJib3NpdHkgb3B0aW9ucyB0byBmaWxl
X2ZkdwoKOWUyZDg3MCwgYjcyNWI3ZSBhbmQgZjVhMjI3OCBpbnRyb2R1Y2VkIG9uX2Vycm9yIGFu
ZCBsb2dfdmVyYm9zaXR5IHRvIENPUFkuClNpbmNlIHRoZXNlIG9wdGlvbnMgc2VlbXMgdXNlZnVs
IHRvIGZpbGVfZmR3IHdoZW4gYWNjZXNzaW5nIGRpcnR5IGRhdGEsCnRoaXMgcGF0Y2ggYWRkcyB0
aGVtIHRvIGZpbGVfZmR3LgoKLS0tCiBjb250cmliL2ZpbGVfZmR3L2V4cGVjdGVkL2ZpbGVfZmR3
Lm91dCB8IDIwICsrKysrKysrKwogY29udHJpYi9maWxlX2Zkdy9maWxlX2Zkdy5jICAgICAgICAg
ICAgfCA2MCArKysrKysrKysrKysrKysrKysrKysrKy0tLQogY29udHJpYi9maWxlX2Zkdy9zcWwv
ZmlsZV9mZHcuc3FsICAgICAgfCAgNiArKysKIDMgZmlsZXMgY2hhbmdlZCwgNzkgaW5zZXJ0aW9u
cygrKSwgNyBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9jb250cmliL2ZpbGVfZmR3L2V4cGVj
dGVkL2ZpbGVfZmR3Lm91dCBiL2NvbnRyaWIvZmlsZV9mZHcvZXhwZWN0ZWQvZmlsZV9mZHcub3V0
CmluZGV4IDg2YzE0OGE4NmIuLjFhZjc5YWYyMGYgMTAwNjQ0Ci0tLSBhL2NvbnRyaWIvZmlsZV9m
ZHcvZXhwZWN0ZWQvZmlsZV9mZHcub3V0CisrKyBiL2NvbnRyaWIvZmlsZV9mZHcvZXhwZWN0ZWQv
ZmlsZV9mZHcub3V0CkBAIC0yMDYsNiArMjA2LDI2IEBAIFNFTEVDVCAqIEZST00gYWdnX2NzdiBj
IEpPSU4gYWdnX3RleHQgdCBPTiAodC5hID0gYy5hKSBPUkRFUiBCWSBjLmE7CiBTRUxFQ1QgKiBG
Uk9NIGFnZ19iYWQ7ICAgICAgICAgICAgICAgLS0gRVJST1IKIEVSUk9SOiAgaW52YWxpZCBpbnB1
dCBzeW50YXggZm9yIHR5cGUgcmVhbDogImFhYSIKIENPTlRFWFQ6ICBDT1BZIGFnZ19iYWQsIGxp
bmUgMywgY29sdW1uIGI6ICJhYWEiCistLSBvbl9lcnJvciBhbmQgbG9nX3ZlcmJvc2l0eSB0ZXN0
cworQUxURVIgRk9SRUlHTiBUQUJMRSBhZ2dfYmFkIE9QVElPTlMgKEFERCBvbl9lcnJvciAnaWdu
b3JlJyk7CitTRUxFQ1QgKiBGUk9NIGFnZ19iYWQ7CitOT1RJQ0U6ICAxIHJvdyB3YXMgc2tpcHBl
ZCBkdWUgdG8gZGF0YSB0eXBlIGluY29tcGF0aWJpbGl0eQorICBhICB8ICAgYiAgICAKKy0tLS0t
Ky0tLS0tLS0tCisgMTAwIHwgOTkuMDk3CisgIDQyIHwgMzI0Ljc4CisoMiByb3dzKQorCitBTFRF
UiBGT1JFSUdOIFRBQkxFIGFnZ19iYWQgT1BUSU9OUyAoQUREIGxvZ192ZXJib3NpdHkgJ3ZlcmJv
c2UnKTsKK1NFTEVDVCAqIEZST00gYWdnX2JhZDsKK05PVElDRTogIHNraXBwaW5nIHJvdyBkdWUg
dG8gZGF0YSB0eXBlIGluY29tcGF0aWJpbGl0eSBhdCBsaW5lIDMgZm9yIGNvbHVtbiBiOiAiYWFh
IgorTk9USUNFOiAgMSByb3cgd2FzIHNraXBwZWQgZHVlIHRvIGRhdGEgdHlwZSBpbmNvbXBhdGli
aWxpdHkKKyAgYSAgfCAgIGIgICAgCistLS0tLSstLS0tLS0tLQorIDEwMCB8IDk5LjA5NworICA0
MiB8IDMyNC43OAorKDIgcm93cykKKwogLS0gbWlzYyBxdWVyeSB0ZXN0cwogXHQgb24KIFNFTEVD
VCBleHBsYWluX2ZpbHRlcignRVhQTEFJTiAoVkVSQk9TRSwgQ09TVFMgRkFMU0UpIFNFTEVDVCAq
IEZST00gYWdnX2NzdicpOwpkaWZmIC0tZ2l0IGEvY29udHJpYi9maWxlX2Zkdy9maWxlX2Zkdy5j
IGIvY29udHJpYi9maWxlX2Zkdy9maWxlX2Zkdy5jCmluZGV4IDI0OWQ4MmQzYTAuLjJkYzI3NDQz
OTMgMTAwNjQ0Ci0tLSBhL2NvbnRyaWIvZmlsZV9mZHcvZmlsZV9mZHcuYworKysgYi9jb250cmli
L2ZpbGVfZmR3L2ZpbGVfZmR3LmMKQEAgLTIyLDggKzIyLDEwIEBACiAjaW5jbHVkZSAiY2F0YWxv
Zy9wZ19hdXRoaWQuaCIKICNpbmNsdWRlICJjYXRhbG9nL3BnX2ZvcmVpZ25fdGFibGUuaCIKICNp
bmNsdWRlICJjb21tYW5kcy9jb3B5LmgiCisjaW5jbHVkZSAiY29tbWFuZHMvY29weWZyb21faW50
ZXJuYWwuaCIKICNpbmNsdWRlICJjb21tYW5kcy9kZWZyZW0uaCIKICNpbmNsdWRlICJjb21tYW5k
cy9leHBsYWluLmgiCisjaW5jbHVkZSAiY29tbWFuZHMvcHJvZ3Jlc3MuaCIKICNpbmNsdWRlICJj
b21tYW5kcy92YWN1dW0uaCIKICNpbmNsdWRlICJmb3JlaWduL2Zkd2FwaS5oIgogI2luY2x1ZGUg
ImZvcmVpZ24vZm9yZWlnbi5oIgpAQCAtMzQsNiArMzYsNyBAQAogI2luY2x1ZGUgIm9wdGltaXpl
ci9wbGFubWFpbi5oIgogI2luY2x1ZGUgIm9wdGltaXplci9yZXN0cmljdGluZm8uaCIKICNpbmNs
dWRlICJ1dGlscy9hY2wuaCIKKyNpbmNsdWRlICJ1dGlscy9iYWNrZW5kX3Byb2dyZXNzLmgiCiAj
aW5jbHVkZSAidXRpbHMvbWVtdXRpbHMuaCIKICNpbmNsdWRlICJ1dGlscy9yZWwuaCIKICNpbmNs
dWRlICJ1dGlscy9zYW1wbGluZy5oIgpAQCAtNzQsNiArNzcsOCBAQCBzdGF0aWMgY29uc3Qgc3Ry
dWN0IEZpbGVGZHdPcHRpb24gdmFsaWRfb3B0aW9uc1tdID0gewogCXsibnVsbCIsIEZvcmVpZ25U
YWJsZVJlbGF0aW9uSWR9LAogCXsiZGVmYXVsdCIsIEZvcmVpZ25UYWJsZVJlbGF0aW9uSWR9LAog
CXsiZW5jb2RpbmciLCBGb3JlaWduVGFibGVSZWxhdGlvbklkfSwKKwl7Im9uX2Vycm9yIiwgRm9y
ZWlnblRhYmxlUmVsYXRpb25JZH0sCisJeyJsb2dfdmVyYm9zaXR5IiwgRm9yZWlnblRhYmxlUmVs
YXRpb25JZH0sCiAJeyJmb3JjZV9ub3RfbnVsbCIsIEF0dHJpYnV0ZVJlbGF0aW9uSWR9LAogCXsi
Zm9yY2VfbnVsbCIsIEF0dHJpYnV0ZVJlbGF0aW9uSWR9LAogCkBAIC03MjQsMTIgKzcyOSwxMyBA
QCBmaWxlSXRlcmF0ZUZvcmVpZ25TY2FuKEZvcmVpZ25TY2FuU3RhdGUgKm5vZGUpCiAJRXhwckNv
bnRleHQgKmVjb250ZXh0OwogCU1lbW9yeUNvbnRleHQgb2xkY29udGV4dDsKIAlUdXBsZVRhYmxl
U2xvdCAqc2xvdCA9IG5vZGUtPnNzLnNzX1NjYW5UdXBsZVNsb3Q7Ci0JYm9vbAkJZm91bmQ7CisJ
Q29weUZyb21TdGF0ZSBjc3RhdGUgPSBmZXN0YXRlLT5jc3RhdGU7CisJaW50NjQJc2tpcHBlZCA9
IDA7CiAJRXJyb3JDb250ZXh0Q2FsbGJhY2sgZXJyY2FsbGJhY2s7CiAKIAkvKiBTZXQgdXAgY2Fs
bGJhY2sgdG8gaWRlbnRpZnkgZXJyb3IgbGluZSBudW1iZXIuICovCiAJZXJyY2FsbGJhY2suY2Fs
bGJhY2sgPSBDb3B5RnJvbUVycm9yQ2FsbGJhY2s7Ci0JZXJyY2FsbGJhY2suYXJnID0gKHZvaWQg
KikgZmVzdGF0ZS0+Y3N0YXRlOworCWVycmNhbGxiYWNrLmFyZyA9ICh2b2lkICopIGNzdGF0ZTsK
IAllcnJjYWxsYmFjay5wcmV2aW91cyA9IGVycm9yX2NvbnRleHRfc3RhY2s7CiAJZXJyb3JfY29u
dGV4dF9zdGFjayA9ICZlcnJjYWxsYmFjazsKIApAQCAtNzUwLDEwICs3NTYsNDAgQEAgZmlsZUl0
ZXJhdGVGb3JlaWduU2NhbihGb3JlaWduU2NhblN0YXRlICpub2RlKQogCSAqIHN3aXRjaCBpbiBj
YXNlIHdlIGFyZSBkb2luZyB0aGF0LgogCSAqLwogCW9sZGNvbnRleHQgPSBNZW1vcnlDb250ZXh0
U3dpdGNoVG8oR2V0UGVyVHVwbGVNZW1vcnlDb250ZXh0KGVzdGF0ZSkpOwotCWZvdW5kID0gTmV4
dENvcHlGcm9tKGZlc3RhdGUtPmNzdGF0ZSwgZWNvbnRleHQsCi0JCQkJCQkgc2xvdC0+dHRzX3Zh
bHVlcywgc2xvdC0+dHRzX2lzbnVsbCk7Ci0JaWYgKGZvdW5kKQorCisJZm9yKDs7KQorCXsKKwkJ
aWYgKCFOZXh0Q29weUZyb20oY3N0YXRlLCBlY29udGV4dCwKKwkJCQkJIHNsb3QtPnR0c192YWx1
ZXMsIHNsb3QtPnR0c19pc251bGwpKQorCQkJYnJlYWs7CisKKwkJaWYgKGNzdGF0ZS0+b3B0cy5v
bl9lcnJvciAhPSBDT1BZX09OX0VSUk9SX1NUT1AgJiYKKwkJCWNzdGF0ZS0+ZXNjb250ZXh0LT5l
cnJvcl9vY2N1cnJlZCkKKwkJeworCQkJLyoKKwkJCSAqIFNvZnQgZXJyb3Igb2NjdXJyZWQsIHNr
aXAgdGhpcyB0dXBsZSBhbmQgZGVhbCB3aXRoIGVycm9yCisJCQkgKiBpbmZvcm1hdGlvbiBhY2Nv
cmRpbmcgdG8gT05fRVJST1IuCisJCQkgKi8KKwkJCWlmIChjc3RhdGUtPm9wdHMub25fZXJyb3Ig
PT0gQ09QWV9PTl9FUlJPUl9JR05PUkUpCisKKwkJCQkvKgorCQkJCSAqIEp1c3QgbWFrZSBFcnJv
clNhdmVDb250ZXh0IHJlYWR5IGZvciB0aGUgbmV4dCBOZXh0Q29weUZyb20uCisJCQkJICogU2lu
Y2Ugd2UgZG9uJ3Qgc2V0IGRldGFpbHNfd2FudGVkIGFuZCBlcnJvcl9kYXRhIGlzIG5vdCB0bwor
CQkJCSAqIGJlIGZpbGxlZCwganVzdCByZXNldHRpbmcgZXJyb3Jfb2NjdXJyZWQgaXMgZW5vdWdo
LgorCQkJCSAqLworCQkJCWNzdGF0ZS0+ZXNjb250ZXh0LT5lcnJvcl9vY2N1cnJlZCA9IGZhbHNl
OworCisJCQkvKiBSZXBvcnQgdGhhdCB0aGlzIHR1cGxlIHdhcyBza2lwcGVkIGJ5IHRoZSBPTl9F
UlJPUiBjbGF1c2UgKi8KKwkJCXBnc3RhdF9wcm9ncmVzc191cGRhdGVfcGFyYW0oUFJPR1JFU1Nf
Q09QWV9UVVBMRVNfU0tJUFBFRCwKKwkJCQkJCQkJCQkgKytza2lwcGVkKTsKKworCQkJLyogUmVw
ZWF0IE5leHRDb3B5RnJvbSgpIHVudGlsIG5vIHNvZnQgZXJyb3Igb2NjdXJzICovCisJCQljb250
aW51ZTsKKwkJfQorCiAJCUV4ZWNTdG9yZVZpcnR1YWxUdXBsZShzbG90KTsKKwkJYnJlYWs7CisJ
fQogCiAJLyogU3dpdGNoIGJhY2sgdG8gb3JpZ2luYWwgbWVtb3J5IGNvbnRleHQgKi8KIAlNZW1v
cnlDb250ZXh0U3dpdGNoVG8ob2xkY29udGV4dCk7CkBAIC03OTUsOCArODMxLDE4IEBAIGZpbGVF
bmRGb3JlaWduU2NhbihGb3JlaWduU2NhblN0YXRlICpub2RlKQogCUZpbGVGZHdFeGVjdXRpb25T
dGF0ZSAqZmVzdGF0ZSA9IChGaWxlRmR3RXhlY3V0aW9uU3RhdGUgKikgbm9kZS0+ZmR3X3N0YXRl
OwogCiAJLyogaWYgZmVzdGF0ZSBpcyBOVUxMLCB3ZSBhcmUgaW4gRVhQTEFJTjsgbm90aGluZyB0
byBkbyAqLwotCWlmIChmZXN0YXRlKQotCQlFbmRDb3B5RnJvbShmZXN0YXRlLT5jc3RhdGUpOwor
CWlmICghZmVzdGF0ZSkKKwkJcmV0dXJuOworCisJaWYgKGZlc3RhdGUtPmNzdGF0ZS0+b3B0cy5v
bl9lcnJvciAhPSBDT1BZX09OX0VSUk9SX1NUT1AgJiYKKwkJZmVzdGF0ZS0+Y3N0YXRlLT5udW1f
ZXJyb3JzID4gMCkKKwkJZXJlcG9ydChOT1RJQ0UsCisJCQkJZXJybXNnX3BsdXJhbCgiJWxsdSBy
b3cgd2FzIHNraXBwZWQgZHVlIHRvIGRhdGEgdHlwZSBpbmNvbXBhdGliaWxpdHkiLAorCQkJCQkJ
CSAgIiVsbHUgcm93cyB3ZXJlIHNraXBwZWQgZHVlIHRvIGRhdGEgdHlwZSBpbmNvbXBhdGliaWxp
dHkiLAorCQkJCQkJCSAgKHVuc2lnbmVkIGxvbmcgbG9uZykgZmVzdGF0ZS0+Y3N0YXRlLT5udW1f
ZXJyb3JzLAorCQkJCQkJCSAgKHVuc2lnbmVkIGxvbmcgbG9uZykgZmVzdGF0ZS0+Y3N0YXRlLT5u
dW1fZXJyb3JzKSk7CisKKwlFbmRDb3B5RnJvbShmZXN0YXRlLT5jc3RhdGUpOwogfQogCiAvKgpk
aWZmIC0tZ2l0IGEvY29udHJpYi9maWxlX2Zkdy9zcWwvZmlsZV9mZHcuc3FsIGIvY29udHJpYi9m
aWxlX2Zkdy9zcWwvZmlsZV9mZHcuc3FsCmluZGV4IGYwNTQ4ZTE0ZTEuLjVlYWUwMWQwZjIgMTAw
NjQ0Ci0tLSBhL2NvbnRyaWIvZmlsZV9mZHcvc3FsL2ZpbGVfZmR3LnNxbAorKysgYi9jb250cmli
L2ZpbGVfZmR3L3NxbC9maWxlX2Zkdy5zcWwKQEAgLTE1MCw2ICsxNTAsMTIgQEAgU0VMRUNUICog
RlJPTSBhZ2dfY3N2IGMgSk9JTiBhZ2dfdGV4dCB0IE9OICh0LmEgPSBjLmEpIE9SREVSIEJZIGMu
YTsKIC0tIGVycm9yIGNvbnRleHQgcmVwb3J0IHRlc3RzCiBTRUxFQ1QgKiBGUk9NIGFnZ19iYWQ7
ICAgICAgICAgICAgICAgLS0gRVJST1IKIAorLS0gb25fZXJyb3IgYW5kIGxvZ192ZXJib3NpdHkg
dGVzdHMKK0FMVEVSIEZPUkVJR04gVEFCTEUgYWdnX2JhZCBPUFRJT05TIChBREQgb25fZXJyb3Ig
J2lnbm9yZScpOworU0VMRUNUICogRlJPTSBhZ2dfYmFkOworQUxURVIgRk9SRUlHTiBUQUJMRSBh
Z2dfYmFkIE9QVElPTlMgKEFERCBsb2dfdmVyYm9zaXR5ICd2ZXJib3NlJyk7CitTRUxFQ1QgKiBG
Uk9NIGFnZ19iYWQ7CisKIC0tIG1pc2MgcXVlcnkgdGVzdHMKIFx0IG9uCiBTRUxFQ1QgZXhwbGFp
bl9maWx0ZXIoJ0VYUExBSU4gKFZFUkJPU0UsIENPU1RTIEZBTFNFKSBTRUxFQ1QgKiBGUk9NIGFn
Z19jc3YnKTsKLS0gCjIuMzkuMgoK
--=_fc562c6c67b9ba821d7c219174127cd7--