Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPON1-004NcX-H4 for pgsql-hackers@arkaria.postgresql.org; Thu, 04 Jul 2024 15:27:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sPOMz-000him-Am for pgsql-hackers@arkaria.postgresql.org; Thu, 04 Jul 2024 15:27:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPOMy-000hie-Vf for pgsql-hackers@lists.postgresql.org; Thu, 04 Jul 2024 15:27:37 +0000 Received: from oss.nttdata.com ([49.212.34.109]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPOMw-000POc-9n for pgsql-hackers@postgresql.org; Thu, 04 Jul 2024 15:27:37 +0000 Received: from oss.nttdata.com (localhost [127.0.0.1]) by oss.nttdata.com (Postfix) with ESMTPA id 53D6E60785 for ; Fri, 5 Jul 2024 00:27:30 +0900 (JST) X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 0.103.11 at oss.nttdata.com MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=_25f77e01cc29d3c1f4873c17786bb3e7" Content-Transfer-Encoding: 7bit Date: Fri, 05 Jul 2024 00:27:30 +0900 From: torikoshia To: Pgsql Hackers Subject: Add on_error and log_verbosity options to file_fdw User-Agent: Roundcube Webmail/1.4.11 Message-ID: X-Sender: torikoshia@oss.nttdata.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_25f77e01cc29d3c1f4873c17786bb3e7 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed 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 --=_25f77e01cc29d3c1f4873c17786bb3e7 Content-Transfer-Encoding: base64 Content-Type: text/x-diff; name=v1-0001-PoC-patch-for-adding-on_error-and-log_verbosity-o.patch Content-Disposition: attachment; filename=v1-0001-PoC-patch-for-adding-on_error-and-log_verbosity-o.patch; size=6030 RnJvbSBiNmVjNTk4YmZkZDY0ODMzZTBiZmZjODg5YTExYWRkYzVkNjc3YjUxIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBBdHN1c2hpIFRvcmlrb3NoaSA8dG9yaWtvc2hpYUBvc3MubnR0 ZGF0YS5jb20+CkRhdGU6IEZyaSwgNSBKdWwgMjAyNCAwMDowNzoyNiArMDkwMApTdWJqZWN0OiBb UEFUQ0ggdjFdIFBvQyBwYXRjaCBmb3IgYWRkaW5nIG9uX2Vycm9yIGFuZCBsb2dfdmVyYm9zaXR5 IG9wdGlvbnMgdG8KIGZpbGVfZmR3CgotLS0KIGNvbnRyaWIvZmlsZV9mZHcvZXhwZWN0ZWQvZmls ZV9mZHcub3V0IHwgMjAgKysrKysrKysrCiBjb250cmliL2ZpbGVfZmR3L2ZpbGVfZmR3LmMgICAg ICAgICAgICB8IDU4ICsrKysrKysrKysrKysrKysrKysrKysrLS0tCiBjb250cmliL2ZpbGVfZmR3 L3NxbC9maWxlX2Zkdy5zcWwgICAgICB8ICA2ICsrKwogMyBmaWxlcyBjaGFuZ2VkLCA3OCBpbnNl cnRpb25zKCspLCA2IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL2NvbnRyaWIvZmlsZV9mZHcv ZXhwZWN0ZWQvZmlsZV9mZHcub3V0IGIvY29udHJpYi9maWxlX2Zkdy9leHBlY3RlZC9maWxlX2Zk dy5vdXQKaW5kZXggODZjMTQ4YTg2Yi4uMWFmNzlhZjIwZiAxMDA2NDQKLS0tIGEvY29udHJpYi9m aWxlX2Zkdy9leHBlY3RlZC9maWxlX2Zkdy5vdXQKKysrIGIvY29udHJpYi9maWxlX2Zkdy9leHBl Y3RlZC9maWxlX2Zkdy5vdXQKQEAgLTIwNiw2ICsyMDYsMjYgQEAgU0VMRUNUICogRlJPTSBhZ2df Y3N2IGMgSk9JTiBhZ2dfdGV4dCB0IE9OICh0LmEgPSBjLmEpIE9SREVSIEJZIGMuYTsKIFNFTEVD VCAqIEZST00gYWdnX2JhZDsgICAgICAgICAgICAgICAtLSBFUlJPUgogRVJST1I6ICBpbnZhbGlk IGlucHV0IHN5bnRheCBmb3IgdHlwZSByZWFsOiAiYWFhIgogQ09OVEVYVDogIENPUFkgYWdnX2Jh ZCwgbGluZSAzLCBjb2x1bW4gYjogImFhYSIKKy0tIG9uX2Vycm9yIGFuZCBsb2dfdmVyYm9zaXR5 IHRlc3RzCitBTFRFUiBGT1JFSUdOIFRBQkxFIGFnZ19iYWQgT1BUSU9OUyAoQUREIG9uX2Vycm9y ICdpZ25vcmUnKTsKK1NFTEVDVCAqIEZST00gYWdnX2JhZDsKK05PVElDRTogIDEgcm93IHdhcyBz a2lwcGVkIGR1ZSB0byBkYXRhIHR5cGUgaW5jb21wYXRpYmlsaXR5CisgIGEgIHwgICBiICAgIAor LS0tLS0rLS0tLS0tLS0KKyAxMDAgfCA5OS4wOTcKKyAgNDIgfCAzMjQuNzgKKygyIHJvd3MpCisK K0FMVEVSIEZPUkVJR04gVEFCTEUgYWdnX2JhZCBPUFRJT05TIChBREQgbG9nX3ZlcmJvc2l0eSAn dmVyYm9zZScpOworU0VMRUNUICogRlJPTSBhZ2dfYmFkOworTk9USUNFOiAgc2tpcHBpbmcgcm93 IGR1ZSB0byBkYXRhIHR5cGUgaW5jb21wYXRpYmlsaXR5IGF0IGxpbmUgMyBmb3IgY29sdW1uIGI6 ICJhYWEiCitOT1RJQ0U6ICAxIHJvdyB3YXMgc2tpcHBlZCBkdWUgdG8gZGF0YSB0eXBlIGluY29t cGF0aWJpbGl0eQorICBhICB8ICAgYiAgICAKKy0tLS0tKy0tLS0tLS0tCisgMTAwIHwgOTkuMDk3 CisgIDQyIHwgMzI0Ljc4CisoMiByb3dzKQorCiAtLSBtaXNjIHF1ZXJ5IHRlc3RzCiBcdCBvbgog U0VMRUNUIGV4cGxhaW5fZmlsdGVyKCdFWFBMQUlOIChWRVJCT1NFLCBDT1NUUyBGQUxTRSkgU0VM RUNUICogRlJPTSBhZ2dfY3N2Jyk7CmRpZmYgLS1naXQgYS9jb250cmliL2ZpbGVfZmR3L2ZpbGVf ZmR3LmMgYi9jb250cmliL2ZpbGVfZmR3L2ZpbGVfZmR3LmMKaW5kZXggMjQ5ZDgyZDNhMC4uODZm YjY1NWRmMSAxMDA2NDQKLS0tIGEvY29udHJpYi9maWxlX2Zkdy9maWxlX2Zkdy5jCisrKyBiL2Nv bnRyaWIvZmlsZV9mZHcvZmlsZV9mZHcuYwpAQCAtMjIsOCArMjIsMTAgQEAKICNpbmNsdWRlICJj YXRhbG9nL3BnX2F1dGhpZC5oIgogI2luY2x1ZGUgImNhdGFsb2cvcGdfZm9yZWlnbl90YWJsZS5o IgogI2luY2x1ZGUgImNvbW1hbmRzL2NvcHkuaCIKKyNpbmNsdWRlICJjb21tYW5kcy9jb3B5ZnJv bV9pbnRlcm5hbC5oIgogI2luY2x1ZGUgImNvbW1hbmRzL2RlZnJlbS5oIgogI2luY2x1ZGUgImNv bW1hbmRzL2V4cGxhaW4uaCIKKyNpbmNsdWRlICJjb21tYW5kcy9wcm9ncmVzcy5oIgogI2luY2x1 ZGUgImNvbW1hbmRzL3ZhY3V1bS5oIgogI2luY2x1ZGUgImZvcmVpZ24vZmR3YXBpLmgiCiAjaW5j bHVkZSAiZm9yZWlnbi9mb3JlaWduLmgiCkBAIC0zNCw2ICszNiw3IEBACiAjaW5jbHVkZSAib3B0 aW1pemVyL3BsYW5tYWluLmgiCiAjaW5jbHVkZSAib3B0aW1pemVyL3Jlc3RyaWN0aW5mby5oIgog I2luY2x1ZGUgInV0aWxzL2FjbC5oIgorI2luY2x1ZGUgInV0aWxzL2JhY2tlbmRfcHJvZ3Jlc3Mu aCIKICNpbmNsdWRlICJ1dGlscy9tZW11dGlscy5oIgogI2luY2x1ZGUgInV0aWxzL3JlbC5oIgog I2luY2x1ZGUgInV0aWxzL3NhbXBsaW5nLmgiCkBAIC03NCw2ICs3Nyw4IEBAIHN0YXRpYyBjb25z dCBzdHJ1Y3QgRmlsZUZkd09wdGlvbiB2YWxpZF9vcHRpb25zW10gPSB7CiAJeyJudWxsIiwgRm9y ZWlnblRhYmxlUmVsYXRpb25JZH0sCiAJeyJkZWZhdWx0IiwgRm9yZWlnblRhYmxlUmVsYXRpb25J ZH0sCiAJeyJlbmNvZGluZyIsIEZvcmVpZ25UYWJsZVJlbGF0aW9uSWR9LAorCXsib25fZXJyb3Ii LCBGb3JlaWduVGFibGVSZWxhdGlvbklkfSwKKwl7ImxvZ192ZXJib3NpdHkiLCBGb3JlaWduVGFi bGVSZWxhdGlvbklkfSwKIAl7ImZvcmNlX25vdF9udWxsIiwgQXR0cmlidXRlUmVsYXRpb25JZH0s CiAJeyJmb3JjZV9udWxsIiwgQXR0cmlidXRlUmVsYXRpb25JZH0sCiAKQEAgLTcyNCwxMiArNzI5 LDEzIEBAIGZpbGVJdGVyYXRlRm9yZWlnblNjYW4oRm9yZWlnblNjYW5TdGF0ZSAqbm9kZSkKIAlF eHByQ29udGV4dCAqZWNvbnRleHQ7CiAJTWVtb3J5Q29udGV4dCBvbGRjb250ZXh0OwogCVR1cGxl VGFibGVTbG90ICpzbG90ID0gbm9kZS0+c3Muc3NfU2NhblR1cGxlU2xvdDsKLQlib29sCQlmb3Vu ZDsKKwlDb3B5RnJvbVN0YXRlIGNzdGF0ZSA9IGZlc3RhdGUtPmNzdGF0ZTsKKwlpbnQ2NAlza2lw cGVkID0gMDsKIAlFcnJvckNvbnRleHRDYWxsYmFjayBlcnJjYWxsYmFjazsKIAogCS8qIFNldCB1 cCBjYWxsYmFjayB0byBpZGVudGlmeSBlcnJvciBsaW5lIG51bWJlci4gKi8KIAllcnJjYWxsYmFj ay5jYWxsYmFjayA9IENvcHlGcm9tRXJyb3JDYWxsYmFjazsKLQllcnJjYWxsYmFjay5hcmcgPSAo dm9pZCAqKSBmZXN0YXRlLT5jc3RhdGU7CisJZXJyY2FsbGJhY2suYXJnID0gKHZvaWQgKikgY3N0 YXRlOwogCWVycmNhbGxiYWNrLnByZXZpb3VzID0gZXJyb3JfY29udGV4dF9zdGFjazsKIAllcnJv cl9jb250ZXh0X3N0YWNrID0gJmVycmNhbGxiYWNrOwogCkBAIC03NTAsMTAgKzc1Niw0MCBAQCBm aWxlSXRlcmF0ZUZvcmVpZ25TY2FuKEZvcmVpZ25TY2FuU3RhdGUgKm5vZGUpCiAJICogc3dpdGNo IGluIGNhc2Ugd2UgYXJlIGRvaW5nIHRoYXQuCiAJICovCiAJb2xkY29udGV4dCA9IE1lbW9yeUNv bnRleHRTd2l0Y2hUbyhHZXRQZXJUdXBsZU1lbW9yeUNvbnRleHQoZXN0YXRlKSk7Ci0JZm91bmQg PSBOZXh0Q29weUZyb20oZmVzdGF0ZS0+Y3N0YXRlLCBlY29udGV4dCwKLQkJCQkJCSBzbG90LT50 dHNfdmFsdWVzLCBzbG90LT50dHNfaXNudWxsKTsKLQlpZiAoZm91bmQpCisKKwlmb3IoOzspCisJ eworCQlpZiAoIU5leHRDb3B5RnJvbShjc3RhdGUsIGVjb250ZXh0LAorCQkJCQkgc2xvdC0+dHRz X3ZhbHVlcywgc2xvdC0+dHRzX2lzbnVsbCkpCisJCQlicmVhazsKKworCQlpZiAoY3N0YXRlLT5v cHRzLm9uX2Vycm9yICE9IENPUFlfT05fRVJST1JfU1RPUCAmJgorCQkJY3N0YXRlLT5lc2NvbnRl eHQtPmVycm9yX29jY3VycmVkKQorCQl7CisJCQkvKgorCQkJICogU29mdCBlcnJvciBvY2N1cnJl ZCwgc2tpcCB0aGlzIHR1cGxlIGFuZCBkZWFsIHdpdGggZXJyb3IKKwkJCSAqIGluZm9ybWF0aW9u IGFjY29yZGluZyB0byBPTl9FUlJPUi4KKwkJCSAqLworCQkJaWYgKGNzdGF0ZS0+b3B0cy5vbl9l cnJvciA9PSBDT1BZX09OX0VSUk9SX0lHTk9SRSkKKworCQkJCS8qCisJCQkJICogSnVzdCBtYWtl IEVycm9yU2F2ZUNvbnRleHQgcmVhZHkgZm9yIHRoZSBuZXh0IE5leHRDb3B5RnJvbS4KKwkJCQkg KiBTaW5jZSB3ZSBkb24ndCBzZXQgZGV0YWlsc193YW50ZWQgYW5kIGVycm9yX2RhdGEgaXMgbm90 IHRvCisJCQkJICogYmUgZmlsbGVkLCBqdXN0IHJlc2V0dGluZyBlcnJvcl9vY2N1cnJlZCBpcyBl bm91Z2guCisJCQkJICovCisJCQkJY3N0YXRlLT5lc2NvbnRleHQtPmVycm9yX29jY3VycmVkID0g ZmFsc2U7CisKKwkJCS8qIFJlcG9ydCB0aGF0IHRoaXMgdHVwbGUgd2FzIHNraXBwZWQgYnkgdGhl IE9OX0VSUk9SIGNsYXVzZSAqLworCQkJcGdzdGF0X3Byb2dyZXNzX3VwZGF0ZV9wYXJhbShQUk9H UkVTU19DT1BZX1RVUExFU19TS0lQUEVELAorCQkJCQkJCQkJCSArK3NraXBwZWQpOworCisJCQkv KiBSZXBlYXQgTmV4dENvcHlGcm9tKCkgdW50aWwgbm8gc29mdCBlcnJvciBvY2N1cnMgKi8KKwkJ CWNvbnRpbnVlOworCQl9CisKIAkJRXhlY1N0b3JlVmlydHVhbFR1cGxlKHNsb3QpOworCQlicmVh azsKKwl9CiAKIAkvKiBTd2l0Y2ggYmFjayB0byBvcmlnaW5hbCBtZW1vcnkgY29udGV4dCAqLwog CU1lbW9yeUNvbnRleHRTd2l0Y2hUbyhvbGRjb250ZXh0KTsKQEAgLTc5NSw3ICs4MzEsMTcgQEAg ZmlsZUVuZEZvcmVpZ25TY2FuKEZvcmVpZ25TY2FuU3RhdGUgKm5vZGUpCiAJRmlsZUZkd0V4ZWN1 dGlvblN0YXRlICpmZXN0YXRlID0gKEZpbGVGZHdFeGVjdXRpb25TdGF0ZSAqKSBub2RlLT5mZHdf c3RhdGU7CiAKIAkvKiBpZiBmZXN0YXRlIGlzIE5VTEwsIHdlIGFyZSBpbiBFWFBMQUlOOyBub3Ro aW5nIHRvIGRvICovCi0JaWYgKGZlc3RhdGUpCisJaWYgKCFmZXN0YXRlKQorCQlyZXR1cm47CisK KwlpZiAoZmVzdGF0ZS0+Y3N0YXRlLT5vcHRzLm9uX2Vycm9yICE9IENPUFlfT05fRVJST1JfU1RP UCAmJgorCQlmZXN0YXRlLT5jc3RhdGUtPm51bV9lcnJvcnMgPiAwKQorCQllcmVwb3J0KE5PVElD RSwKKwkJCQllcnJtc2dfcGx1cmFsKCIlbGx1IHJvdyB3YXMgc2tpcHBlZCBkdWUgdG8gZGF0YSB0 eXBlIGluY29tcGF0aWJpbGl0eSIsCisJCQkJCQkJICAiJWxsdSByb3dzIHdlcmUgc2tpcHBlZCBk dWUgdG8gZGF0YSB0eXBlIGluY29tcGF0aWJpbGl0eSIsCisJCQkJCQkJICAodW5zaWduZWQgbG9u ZyBsb25nKSBmZXN0YXRlLT5jc3RhdGUtPm51bV9lcnJvcnMsCisJCQkJCQkJICAodW5zaWduZWQg bG9uZyBsb25nKSBmZXN0YXRlLT5jc3RhdGUtPm51bV9lcnJvcnMpKTsKKwogCQlFbmRDb3B5RnJv bShmZXN0YXRlLT5jc3RhdGUpOwogfQogCmRpZmYgLS1naXQgYS9jb250cmliL2ZpbGVfZmR3L3Nx bC9maWxlX2Zkdy5zcWwgYi9jb250cmliL2ZpbGVfZmR3L3NxbC9maWxlX2Zkdy5zcWwKaW5kZXgg ZjA1NDhlMTRlMS4uNWVhZTAxZDBmMiAxMDA2NDQKLS0tIGEvY29udHJpYi9maWxlX2Zkdy9zcWwv ZmlsZV9mZHcuc3FsCisrKyBiL2NvbnRyaWIvZmlsZV9mZHcvc3FsL2ZpbGVfZmR3LnNxbApAQCAt MTUwLDYgKzE1MCwxMiBAQCBTRUxFQ1QgKiBGUk9NIGFnZ19jc3YgYyBKT0lOIGFnZ190ZXh0IHQg T04gKHQuYSA9IGMuYSkgT1JERVIgQlkgYy5hOwogLS0gZXJyb3IgY29udGV4dCByZXBvcnQgdGVz dHMKIFNFTEVDVCAqIEZST00gYWdnX2JhZDsgICAgICAgICAgICAgICAtLSBFUlJPUgogCistLSBv bl9lcnJvciBhbmQgbG9nX3ZlcmJvc2l0eSB0ZXN0cworQUxURVIgRk9SRUlHTiBUQUJMRSBhZ2df YmFkIE9QVElPTlMgKEFERCBvbl9lcnJvciAnaWdub3JlJyk7CitTRUxFQ1QgKiBGUk9NIGFnZ19i YWQ7CitBTFRFUiBGT1JFSUdOIFRBQkxFIGFnZ19iYWQgT1BUSU9OUyAoQUREIGxvZ192ZXJib3Np dHkgJ3ZlcmJvc2UnKTsKK1NFTEVDVCAqIEZST00gYWdnX2JhZDsKKwogLS0gbWlzYyBxdWVyeSB0 ZXN0cwogXHQgb24KIFNFTEVDVCBleHBsYWluX2ZpbHRlcignRVhQTEFJTiAoVkVSQk9TRSwgQ09T VFMgRkFMU0UpIFNFTEVDVCAqIEZST00gYWdnX2NzdicpOwoKYmFzZS1jb21taXQ6IDJlZjU3NWM3 ODAzYTU1MTAxMzUyYzBmNmNiOGY3NDVhZjA2M2E2NmMKLS0gCjIuMzkuMgoK --=_25f77e01cc29d3c1f4873c17786bb3e7--