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.96) (envelope-from ) id 1vc11L-000x4A-1N for pgsql-hackers@arkaria.postgresql.org; Sat, 03 Jan 2026 12:46:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vc11J-00CkD7-2L for pgsql-hackers@arkaria.postgresql.org; Sat, 03 Jan 2026 12:46:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vc11I-00CkCy-10 for pgsql-hackers@lists.postgresql.org; Sat, 03 Jan 2026 12:46:14 +0000 Received: from m16.mail.163.com ([220.197.31.3]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vc11A-003yLv-1d for pgsql-hackers@postgresql.org; Sat, 03 Jan 2026 12:46:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=163.com; s=s110527; h=Date:From:To:Subject:Mime-Version:Message-ID: Content-Type; bh=0F46/Ud0ErAeUb/ADLastoLj7nwS4SiNbAx7FiqDiPs=; b=VDZu1py/xNqM0NuASqQU0/Hynhsmh8ktPXoUuW7bCxYxYJWexYw1RnqD5K5BCT 7j1csCy6p7MYVaFGvM/YtQ3F8l3EntI6tEiFQ7dgwqp4u4zUFpe7zTR0d0oPbZM3 EIU3MHFaPRUGGOZp/t0y0icQmnjH7EIxZniKzL0AC3h2g= Received: from dwdai (unknown []) by gzga-smtp-mtada-g1-1 (Coremail) with SMTP id _____wAXQvd3D1lpVTjWDg--.8259S2; Sat, 03 Jan 2026 20:45:44 +0800 (CST) Date: Sat, 3 Jan 2026 20:45:44 +0800 From: "Dewei Dai" To: "Matheus Alcantara" , "Masahiko Sawada" Cc: "Andrew Dunstan" , "jian he" , "Tomas Vondra" , pgsql-hackers Subject: Re: Re: postgres_fdw: Use COPY to speed up batch inserts References: , , , , , , , , <5c52f4c7-74fa-4594-8378-53fd49f39329@dunslane.net>, , , , , , , , , X-Priority: 3 X-GUID: F0B2D569-47F1-4F43-9589-B46BD9C78694 X-Has-Attach: no X-Mailer: Foxmail 7.2.23.121[cn] Mime-Version: 1.0 Message-ID: <202601032045434521683@163.com> Content-Type: multipart/alternative; boundary="----=_001_NextPart838115130545_=----" X-CM-TRANSID: _____wAXQvd3D1lpVTjWDg--.8259S2 X-Coremail-Antispam: 1Uf129KBjvJXoW7ZF47CF47WFWkGr4UJF18Zrb_yoW8KFyxpr WDKryqkFyrJF1Iva4kZ3W0q3WfZws3tFyjkrn7Ja48A345J3WSqrn2kryjkayUZ397A3ya qFs8XF1Fq34kZ3DanT9S1TB71UUUUU7qnTZGkaVYY2UrUUUUjbIjqfuFe4nvWSU5nxnvy2 9KBjDUYxBIdaVFxhVjvjDU0xZFpf9x0zRkWrXUUUUU= X-Originating-IP: [2408:8207:3042:7990:5026:b23:e37f:d038] X-CM-SenderInfo: xgdlvv5zhlimixq6il2tof0z/xtbC3hqSRWlZD3oU4wAA3h List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. ------=_001_NextPart838115130545_=---- Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: base64 SGkgTWF0aGV1cywNCiAgIEkganVzdCByZXZpZXdlZCB0aGUgdjggcGF0Y2ggYW5kIGdvdCBhIGZl dyBjb21tZW50czoNCg0KMSAtIGluIGZ1bmN0aW9uIGBleGVjdXRlX2ZvcmVpZ25fbW9kaWZ5X3Vz aW5nX2NvcHlgDQogIFRoZSBgcmVzYCBvYmplY3Qgb2J0YWluZWQgZnJvbSB0aGUgZmlyc3QgY2Fs bCB0byBgcGdmZHdfZ2V0X3Jlc3VsdGANCiAgaXMgbm90IGZyZWVkLCBtYXliZSB5b3UgY2FuIHVz ZSBgUFFjbGVhcmAgdG8gcmVsZWFzZSBpdA0KDQoyIC0gaW4gZnVuY3Rpb24gYGV4ZWN1dGVfZm9y ZWlnbl9tb2RpZnlfdXNpbmdfY29weWANCiAgQWZ0ZXIgdXNpbmcgYGNvcHlfZGF0YWAsaXQgYXBw ZWFycyBpdCBjYW4gYmUgcmVsZWFzZWQgYnkNCiAgICBjYWxsaW5nIGBkZXN0cm95U3RyaW5nSW5m b2AuDQoNCjMgLSBpbiBmdW5jdGlvbiBgY29udmVydF9zbG90X3RvX2NvcHlfdGV4dGANCiAgVGhl IHZhbHVlIHJldHVybmVkIGJ5IHRoZSBPdXRwdXRGdW5jdGlvbkNhbGwgZnVuY3Rpb24gY2FuIGJl DQogICAgZnJlZWQgYnkgY2FsbGluZyBwZnJlZQ0KICANCjQgLSBpbiBmdW5jdGlvbiBgZXhlY3V0 ZV9mb3JlaWduX21vZGlmeV91c2luZ19jb3B5YA0KYGBgU2VuZCBpbml0aWFsIENPUFkgZGF0YSBp ZiB0aGUgYnVmZmVyIHJlYWNoIHRoZSBsaW1pdCB0byBhdm9pZCBsYXJnZQ0KYGBgDQpUeXBvOiBy ZWFjaCAtPiAgcmVhY2hlcyANCg0KQmVzdCByZWdhcmRz77yMDQpEZXdlaSBEYWkNCg0KDQoNCmRh aWRld2VpMTk3MEAxNjMuY29tDQogDQpGcm9tOiBNYXRoZXVzIEFsY2FudGFyYQ0KRGF0ZTogMjAy Ni0wMS0wMyAwNDozMw0KVG86IE1hc2FoaWtvIFNhd2FkYTsgTWF0aGV1cyBBbGNhbnRhcmENCkND OiBBbmRyZXcgRHVuc3RhbjsgamlhbiBoZTsgVG9tYXMgVm9uZHJhOyBwZ3NxbC1oYWNrZXJzQHBv c3RncmVzcWwub3JnDQpTdWJqZWN0OiBSZTogcG9zdGdyZXNfZmR3OiBVc2UgQ09QWSB0byBzcGVl ZCB1cCBiYXRjaCBpbnNlcnRzDQpPbiBGcmkgSmFuIDIsIDIwMjYgYXQgNToxNSBQTSAtMDMsIE1h c2FoaWtvIFNhd2FkYSB3cm90ZToNCj4gKw0KPiArICAgLyoNCj4gKyAgICAqIFNldCBiYXRjaF93 aXRoX2NvcHlfdGhyZXNob2xkIGZyb20gZm9yZWlnbiBzZXJ2ZXIvdGFibGUgb3B0aW9ucy4gV2Ug ZG8NCj4gKyAgICAqIHRoaXMgb3V0c2lkZSBvZiBjcmVhdGVfZm9yZWlnbl9tb2RpZnkoKSBiZWNh dXNlIHdlIG9ubHkgd2FudCB0byB1c2UNCj4gKyAgICAqIENPUFkgYXMgYSByZW1vdGUgU1FMIHdo ZW4gYSBDT1BZIEZST00gb24gYSBmb3JlaWduIHRhYmxlIGlzIGV4ZWN1dGVkIG9yDQo+ICsgICAg KiBhbiBpbnNlcnQgaXMgYmVpbmcgcGVyZm9ybWVkIG9uIGEgdGFibGUgcGFydGl0aW9uLiBJbiBi b3RoIGNhc2VzIHRoZQ0KPiArICAgICogQmVnaW5Gb3JlaWduSW5zZXJ0IGZkdyByb3V0aW5lIGlz IGNhbGxlZC4NCj4gKyAgICAqLw0KPiArICAgZm1zdGF0ZS0+YmF0Y2hfd2l0aF9jb3B5X3RocmVz aG9sZCA9IGdldF9iYXRjaF93aXRoX2NvcHlfdGhyZXNob2xkKHJlbCk7DQo+DQo+IERvZXMgaXQg bWVhbiB0aGF0IHdlIGNvdWxkIGVuZCB1cCB1c2luZyB0aGUgQ09QWSBtZXRob2Qgbm90IG9ubHkg d2hlbg0KPiBleGVjdXRpbmcgQ09QWSBGUk9NIGJ1dCBhbHNvIHdoZW4gZXhlY3V0aW5nIElOU0VS VCB3aXRoIHR1cGxlDQo+IHJvdXRpbmdzPyBJZiBzbywgaG93IGRvZXMgdGhlIEVYUExBSU4gY29t bWFuZCBzaG93IHRoZSByZW1vdGUgU1FMPw0KPg0KSXQgbWVhcyB0aGF0IHdlIGNvdWxkIGFsc28g dXNlIHRoZSBDT1BZIG1ldGhvZCB0byBpbnNlcnQgcm93cyBpbnRvIGENCnNwZWNpZmljIHRhYmxl IHBhcnRpdGlvbiB0aGF0IGlzIGEgZm9yZWlnbiB0YWJsZS4NCiANCkxldCdzIHNheSB0aGF0IGFu IHVzZXIgZXhlY3V0ZSBhbiBJTlNFUlQgSU5UTyBvbiBhIHBhcnRpdGlvbmVkIHRhYmxlDQp0aGF0 IGhhcyBwYXJ0aXRpb25zIHRoYXQgYXJlIHBvc3RncmVzX2ZkdyB0YWJsZXMsIHdpdGggdGhpcyBw YXRjaCB3ZQ0KY291bGQgdXNlIHRoZSBDT1BZIG1ldGhvZCB0byBpbnNlcnQgdGhlIHJvd3Mgb24g dGhlc2UgcGFydGl0aW9ucy4gT24NCnRoaXMgc2NlbmFyaW8gd2Ugd291bGQgbm90IGhhdmUgaXNz dWUgd2l0aCBFWFBMQUlOIG91dHB1dCBiZWNhdXNlDQpjdXJyZW50bHkgd2UgZG8gbm90IHNob3cg dGhlIHJlbW90ZSBTUUwgYmVpbmcgZXhlY3V0ZWQgb24gZWFjaCBwYXJ0aXRpb24NCnRoYXQgaXMg aW52b2x2ZWQgb24gdGhlIElOU0VSVCBzdGF0ZW1lbnQuDQogDQpJZiBhbiB1c2VyIGV4ZWN1dGUg YW4gSU5TRVJUIGRpcmVjdGx5IGludG8gYSBwb3N0Z3Jlc19mZHcgdGFibGUgd2Ugd2lsbA0KdXNl IHRoZSBub3JtYWwgSU5TRVJUIHN0YXRlbWVudCBhcyB3ZSB1c2UgdG9kYXkuDQogDQpUaGFua3Mg Zm9yIHRha2luZyBhIGxvb2sgYXQgdGhpcy4NCiANCi0tDQpNYXRoZXVzIEFsY2FudGFyYQ0KRURC OiBodHRwczovL3d3dy5lbnRlcnByaXNlZGIuY29tDQogDQogDQogDQogDQo= ------=_001_NextPart838115130545_=---- Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable = =0A
Hi Matheus,
   = I just reviewed the v8 patch and got a few comments:

<= div>1 - in function `execute_foreign_modify_using_copy`
  T= he `res` object obtained from the first call to `pgfdw_get_result`
  is not freed, maybe you can use `PQclear` to release it
2 - in function `execute_foreign_modify_using_copy`
=
  After using `copy_data`,it appears it can be released by
=
    calling `destroyStringInfo`.

3= - in function `convert_slot_to_copy_text`
  The value retu= rned by the OutputFunctionCall function can be
    fre= ed by calling pfree
  
4 - in function `exec= ute_foreign_modify_using_copy`
```Send initial COPY data if the = buffer reach the limit to avoid large
```
Typo: reach = ->  reaches 

Best regards=EF=BC=8C
Dewei Dai
=0A

=0A
<= div style=3D"MARGIN: 10px; FONT-FAMILY: verdana; FONT-SIZE: 10pt">
dai= dewei1970@163.com
=0A
 
Date:<= /b> 2026-01-03 04:33
Subject: Re: postgres_fdw: Use C= OPY to speed up batch inserts
On Fri Jan 2, 202= 6 at 5:15 PM -03, Masahiko Sawada wrote:
=0A
> +
=0A
= > +   /*
=0A
> +    * Set batch_wi= th_copy_threshold from foreign server/table options. We do
=0A
&g= t; +    * this outside of create_foreign_modify() because w= e only want to use
=0A
> +    * COPY as a remot= e SQL when a COPY FROM on a foreign table is executed or
=0A
>= +    * an insert is being performed on a table partition. = In both cases the
=0A
> +    * BeginForeignInse= rt fdw routine is called.
=0A
> +    */
= =0A
> +   fmstate->batch_with_copy_threshold =3D get_b= atch_with_copy_threshold(rel);
=0A
>
=0A
> Does it= mean that we could end up using the COPY method not only when
=0A> executing COPY FROM but also when executing INSERT with tuple
= =0A
> routings? If so, how does the EXPLAIN command show the remote= SQL?
=0A
>
=0A
It meas that we could also use the CO= PY method to insert rows into a
=0A
specific table partition that= is a foreign table.
=0A
 
=0A
Let's say that an us= er execute an INSERT INTO on a partitioned table
=0A
that has par= titions that are postgres_fdw tables, with this patch we
=0A
coul= d use the COPY method to insert the rows on these partitions. On
=0A<= div>this scenario we would not have issue with EXPLAIN output because=0A
currently we do not show the remote SQL being executed on each pa= rtition
=0A
that is involved on the INSERT statement.
=0A =0A
If an user execute an INSERT directly into a postgre= s_fdw table we will
=0A
use the normal INSERT statement as we use= today.
=0A
 
=0A
Thanks for taking a look at this.=
=0A
 
=0A
--
=0A
Matheus Alcantara
= =0A
EDB: https://www.enterprisedb.com
=0A
 
=0A =0A
 
=0A
 
=0A=0A ------=_001_NextPart838115130545_=------