Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hHoZ1-0001cs-BG for pgsql-committers@arkaria.postgresql.org; Sat, 20 Apr 2019 11:53:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hHoYy-0008V6-Q0 for pgsql-committers@arkaria.postgresql.org; Sat, 20 Apr 2019 11:53:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hHoYy-0008Uw-J3; Sat, 20 Apr 2019 11:53:44 +0000 Received: from mx2.mailbox.org ([80.241.60.215]) by magus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hHoYw-0008Sa-Do; Sat, 20 Apr 2019 11:53:43 +0000 Received: from smtp1.mailbox.org (smtp1.mailbox.org [IPv6:2001:67c:2050:105:465:1:1:0]) (using TLSv1.2 with cipher ECDHE-RSA-CHACHA20-POLY1305 (256/256 bits)) (No client certificate requested) by mx2.mailbox.org (Postfix) with ESMTPS id 42108A10CB; Sat, 20 Apr 2019 13:53:41 +0200 (CEST) X-Virus-Scanned: amavisd-new at heinlein-support.de Received: from smtp1.mailbox.org ([80.241.60.240]) by spamfilter04.heinlein-hosting.de (spamfilter04.heinlein-hosting.de [80.241.56.122]) (amavisd-new, port 10030) with ESMTP id i32_OJkcPJjr; Sat, 20 Apr 2019 13:53:28 +0200 (CEST) Message-ID: Subject: Re: pgsql: Allow insert and update tuple routing and COPY for foreign table From: Laurenz Albe To: Robert Haas , pgsql-committers@lists.postgresql.org, pgsql-hackers@lists.postgresql.org Date: Sat, 20 Apr 2019 13:53:26 +0200 In-Reply-To: References: Organization: Cybertec Content-Type: multipart/mixed; boundary="=-Up7eOvgK5AnFrIunPrA5" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=-Up7eOvgK5AnFrIunPrA5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 8bit On Fri, 2018-04-06 at 23:24 +0000, Robert Haas wrote: > Allow insert and update tuple routing and COPY for foreign tables. > > Also enable this for postgres_fdw. > > Etsuro Fujita, based on an earlier patch by Amit Langote. The larger > patch series of which this is a part has been reviewed by Amit > Langote, David Fetter, Maksim Milyutin, Álvaro Herrera, Stephen Frost, > and me. Minor documentation changes to the final version by me. > > Discussion: http://postgr.es/m/29906a26-da12-8c86-4fb9-d8f88442f2b9@lab.ntt.co.jp This commit makes life hard for foreign data wrappers that support data modifications. If a FDW implements ExecForeignInsert, this commit automatically assumes that it also supports COPY FROM. It will call ExecForeignInsert without calling PlanForeignModify and BeginForeignModify, and a FDW that does not expect that will probably fail. So this commit silently turns a functioning FDW into a broken FDW. That is not nice. Probably not every FDW is aware of this change, and maybe there are FDWs that support INSERT but don't want to support COPY for some reason. I propose that PostgreSQL only allows COPY FROM on a foreign table if the FDW implements BeginForeignInsert. The attached patch implements that. I think this should be backpatched to v11. Yours, Laurenz Albe --=-Up7eOvgK5AnFrIunPrA5 Content-Disposition: attachment; filename*0=0001-COPY-FROM-on-foreign-tables-requires-BeginForeignIns.pat; filename*1=ch Content-Type: text/x-patch; name="0001-COPY-FROM-on-foreign-tables-requires-BeginForeignIns.patch"; charset="UTF-8" Content-Transfer-Encoding: base64 RnJvbSBjNGIwZTg3MTY1OGM3NTcxMjRkYWQ5OTI1NzhkYTBiNjBmY2NmOTYyIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5h dD4KRGF0ZTogU2F0LCAyMCBBcHIgMjAxOSAxMzozNjo1NiArMDIwMApTdWJqZWN0OiBbUEFUQ0hd IENPUFkgRlJPTSBvbiBmb3JlaWduIHRhYmxlcyByZXF1aXJlcyBCZWdpbkZvcmVpZ25JbnNlcnQK CkNvbW1pdCAzZDk1NmQ5NTZhIGludHJvZHVjZWQgQ09QWSBGUk9NIHN1cHBvcnQgZm9yIGZvcmVp Z24gdGFibGVzLgoKSWYgYSBmb3JlaWduIGRhdGEgd3JhcHBlciBzdXBwb3J0cyBkYXRhIG1vZGlm aWNhdGlvbnMsIGJ1dCBlaXRoZXIgaGFzCm5vdCBhZGFwdGVkIHRvIHRoaXMgY2hhbmdlIHlldCBv ciBkb2Vzbid0IHdhbnQgdG8gc3VwcG9ydCBDT1BZIEZST00KZm9yIG90aGVyIHJlYXNvbnMsIGl0 IHByb2JhYmx5IGdvdCBicm9rZW4gYnkgdGhlIGFib3ZlIGNvbW1pdCwKYmVjYXVzZSBDT1BZIHdp bGwganVzdCBjYWxsIEV4ZWNGb3JlaWduSW5zZXJ0IGFueXdheSwgd2hpY2ggbWlnaHQgbm90Cndv cmsgYmVjYXVzZSBuZWl0aGVyIFBsYW5Gb3JlaWduTW9kaWZ5IG5vciBCZWdpbkZvcmVpZ25Nb2Rp ZnkgaGF2ZQpiZWVuIGNhbGxlZC4KClRvIGF2b2lkIGJyZWFraW5nIHRoaXJkLXBhcnR5IGZvcmVp Z24gZGF0YSB3cmFwcGVycyBpbiB0aGF0IHdheSwgYWxsb3cKQ09QWSBGUk9NIGZvciBmb3JlaWdu IHRhYmxlcyBvbmx5IGlmIHRoZSBmb3JlaWduIGRhdGEgd3JhcHBlciBpbXBsZW1lbnRzCkJlZ2lu Rm9yZWlnbkluc2VydC4KLS0tCiBkb2Mvc3JjL3NnbWwvZmR3aGFuZGxlci5zZ21sIHwgMiArKwog c3JjL2JhY2tlbmQvY29tbWFuZHMvY29weS5jICB8IDUgKysrKysKIDIgZmlsZXMgY2hhbmdlZCwg NyBpbnNlcnRpb25zKCspCgpkaWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21sL2Zkd2hhbmRsZXIuc2dt bCBiL2RvYy9zcmMvc2dtbC9mZHdoYW5kbGVyLnNnbWwKaW5kZXggMmMwN2E4NjYzNy4uOGNkMTc0 YTIwNCAxMDA2NDQKLS0tIGEvZG9jL3NyYy9zZ21sL2Zkd2hhbmRsZXIuc2dtbAorKysgYi9kb2Mv c3JjL3NnbWwvZmR3aGFuZGxlci5zZ21sCkBAIC03MjQsNiArNzI0LDggQEAgQmVnaW5Gb3JlaWdu SW5zZXJ0KE1vZGlmeVRhYmxlU3RhdGUgKm10c3RhdGUsCiAgICAgIHBlcmZvcm0gYW55IGluaXRp YWxpemF0aW9uIG5lZWRlZCBwcmlvciB0byB0aGUgYWN0dWFsIGluc2VydGlvbi4KICAgICAgU3Vi c2VxdWVudGx5LCA8ZnVuY3Rpb24+RXhlY0ZvcmVpZ25JbnNlcnQ8L2Z1bmN0aW9uPiB3aWxsIGJl IGNhbGxlZCBmb3IKICAgICAgZWFjaCB0dXBsZSB0byBiZSBpbnNlcnRlZCBpbnRvIHRoZSBmb3Jl aWduIHRhYmxlLgorICAgICBBbGwgZm9yZWlnbiBkYXRhIHdyYXBwZXJzIHRoYXQgc3VwcG9ydCA8 Y29tbWFuZD5DT1BZIEZST008L2NvbW1hbmQ+IGhhdmUKKyAgICAgdG8gcHJvdmlkZSB0aGlzIGNh bGxiYWNrIGZ1bmN0aW9uLgogICAgIDwvcGFyYT4KIAogICAgIDxwYXJhPgpkaWZmIC0tZ2l0IGEv c3JjL2JhY2tlbmQvY29tbWFuZHMvY29weS5jIGIvc3JjL2JhY2tlbmQvY29tbWFuZHMvY29weS5j CmluZGV4IGMzOTIxOGY4ZGIuLjk0NGQ1NThjZDQgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2Nv bW1hbmRzL2NvcHkuYworKysgYi9zcmMvYmFja2VuZC9jb21tYW5kcy9jb3B5LmMKQEAgLTI4NTcs NiArMjg1NywxMSBAQCBDb3B5RnJvbShDb3B5U3RhdGUgY3N0YXRlKQogCQlyZXN1bHRSZWxJbmZv LT5yaV9GZHdSb3V0aW5lLT5CZWdpbkZvcmVpZ25JbnNlcnQgIT0gTlVMTCkKIAkJcmVzdWx0UmVs SW5mby0+cmlfRmR3Um91dGluZS0+QmVnaW5Gb3JlaWduSW5zZXJ0KG10c3RhdGUsCiAJCQkJCQkJ CQkJCQkJCSByZXN1bHRSZWxJbmZvKTsKKwllbHNlCisJCWVyZXBvcnQoRVJST1IsCisJCQkJKGVy cmNvZGUoRVJSQ09ERV9GRUFUVVJFIE5PVCBTVVBQT1JURUQpLAorCQkJCSBlcnJtc2coImNhbm5v dCBjb3B5IHRvIGZvcmVpZ24gdGFibGUgXCIlc1wiIiwKKwkJCQkJCVJlbGF0aW9uR2V0UmVsYXRp b25OYW1lKGNzdGF0ZS0+cmVsKSkpKTsKIAogCS8qIFByZXBhcmUgdG8gY2F0Y2ggQUZURVIgdHJp Z2dlcnMuICovCiAJQWZ0ZXJUcmlnZ2VyQmVnaW5RdWVyeSgpOwotLSAKMi4yMC4xCgo= --=-Up7eOvgK5AnFrIunPrA5--