Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o9pc3-0003FI-Fp for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Jul 2022 15:09:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1o9pc2-0002hu-DN for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Jul 2022 15:09:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1o9pc1-0002ev-T6 for pgsql-hackers@lists.postgresql.org; Fri, 08 Jul 2022 15:09:46 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1o9pbu-0000A5-IU for pgsql-hackers@postgresql.org; Fri, 08 Jul 2022 15:09:44 +0000 Received: from [192.168.8.203] (unknown [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 1490E21C07C5; Fri, 8 Jul 2022 18:09:36 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1657292976; bh=t7fetDHT8saGkCV7yWYc6GbubIVtol1cqhgqpifKb50=; h=Date:Subject:To:Cc:References:From:In-Reply-To; b=aei+tYmAuowWbq8V1uoGxoMbJ5y5vin0zM2P8eGbd+B2ZOwSh0p+uZ3jTBIAdeygn jx1ZIrKLgnt54GdQOIkx0rK4BLmhSiS+GksI/fe51/p4+aJepxAtQAUCSJCvSaDgeE ebMqgy4i02nMIkfZHaGxnu26LLXkZwZ0TefjzWsI= Content-Type: multipart/mixed; boundary="------------30bCXt3aebDtJaZc0z25b300" Message-ID: Date: Fri, 8 Jul 2022 18:09:35 +0300 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0) Gecko/20100101 Thunderbird/91.11.0 Subject: Re: Fast COPY FROM based on batch insert Content-Language: en-US To: Ian Barwick Cc: Justin Pryzby , Zhihong Yu , Amit Langote , tanghy.fnst@fujitsu.com, Ashutosh Bapat , Pg Hackers , Tomas Vondra , Etsuro Fujita , houzj.fnst@fujitsu.com References: <20210304203949.GQ29832@telsasoft.com> <20210323032322.GW4203@telsasoft.com> <20210409004956.GN6592@telsasoft.com> <45ad1b94-339b-a841-8885-67ebe6fc5bc1@postgrespro.ru> <065fde84-f688-c411-28eb-a3b9084b38b7@enterprisedb.com> <811b36f0-8888-7036-f4c5-0bfbdfad0bbf@postgrespro.ru> <17be446f-65a5-b49e-e57c-5629431e26b1@enterprisedb.com> From: Andrey Lepikhov Organization: Postgres Professional In-Reply-To: <17be446f-65a5-b49e-e57c-5629431e26b1@enterprisedb.com> 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. --------------30bCXt3aebDtJaZc0z25b300 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 8/7/2022 05:12, Ian Barwick wrote: >     ERROR:  bind message supplies 0 parameters, but prepared statement > "pgsql_fdw_prep_178" requires 6 >     CONTEXT:  remote SQL command: INSERT INTO public.foo_part_1(t, v1, > v2, v3, v4, v5) VALUES ($1, $2, $3, $4, $5, $6) >     COPY foo, line 88160 Thanks, I got it. MultiInsertBuffer are created on the first non-zero flush of tuples into the partition and isn't deleted from the buffers list until the end of COPY. And on a subsequent flush in the case of empty buffer we catch the error. Your fix is correct, but I want to propose slightly different change (see in attachment). -- regards, Andrey Lepikhov Postgres Professional --------------30bCXt3aebDtJaZc0z25b300 Content-Type: text/plain; charset=UTF-8; name="flush_nonzero_buffer.txt" Content-Disposition: attachment; filename="flush_nonzero_buffer.txt" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2NvcHlmcm9tLmMgYi9zcmMvYmFj a2VuZC9jb21tYW5kcy9jb3B5ZnJvbS5jCmluZGV4IDI0NWEyNjA5ODIuLjIwMzI4OWY3ZjIg MTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2NvcHlmcm9tLmMKKysrIGIvc3Jj L2JhY2tlbmQvY29tbWFuZHMvY29weWZyb20uYwpAQCAtMzI5LDcgKzMyOSw4IEBAIENvcHlN dWx0aUluc2VydEJ1ZmZlckZsdXNoKENvcHlNdWx0aUluc2VydEluZm8gKm1paW5mbywKICAg ICAgICAgICAgICAgICAgICAgICAgICAgcmVzdWx0UmVsSW5mby0+cmlfRmR3Um91dGluZS0+ R2V0Rm9yZWlnbk1vZGlmeUJhdGNoU2l6ZSAhPSBOVUxMKTsKIAogICAgICAgICAgICAgICAg LyogRmx1c2ggaW50byBmb3JlaWduIHRhYmxlIG9yIHBhcnRpdGlvbiAqLwotICAgICAgICAg ICAgICAgZG8geworICAgICAgICAgICAgICAgd2hpbGUoc2VudCA8IG51c2VkKQorICAgICAg ICAgICAgICAgewogICAgICAgICAgICAgICAgICAgICAgICBpbnQgc2l6ZSA9IChyZXN1bHRS ZWxJbmZvLT5yaV9CYXRjaFNpemUgPCBudXNlZCAtIHNlbnQpID8KICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgcmVzdWx0UmVsSW5mby0+cmlfQmF0 Y2hTaXplIDogKG51c2VkIC0gc2VudCk7CiAgICAgICAgICAgICAgICAgICAgICAgIGludCBp bnNlcnRlZCA9IHNpemU7CkBAIC0zNDAsNyArMzQxLDcgQEAgQ29weU11bHRpSW5zZXJ0QnVm ZmVyRmx1c2goQ29weU11bHRpSW5zZXJ0SW5mbyAqbWlpbmZvLAogICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICBOVUxMLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAmaW5zZXJ0ZWQpOwogICAgICAgICAg ICAgICAgICAgICAgICBzZW50ICs9IHNpemU7Ci0gICAgICAgICAgICAgICB9IHdoaWxlIChz ZW50IDwgbnVzZWQpOworICAgICAgICAgICAgICAgfQogICAgICAgIH0KICAgICAgICBlbHNl CiAgICAgICAgew== --------------30bCXt3aebDtJaZc0z25b300--