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 1ltUmK-00032c-2U for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Jun 2021 12:36:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ltUmJ-0005WZ-1w for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Jun 2021 12:36:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ltUmI-0005WQ-KV for pgsql-hackers@lists.postgresql.org; Wed, 16 Jun 2021 12:36:18 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ltUmE-0001yG-Lo for pgsql-hackers@postgresql.org; Wed, 16 Jun 2021 12:36:18 +0000 Received: from mail.postgrespro.ru (cyclops.postgrespro.ru [93.174.131.138]) (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 85C8921C880C; Wed, 16 Jun 2021 15:36:13 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1623846973; bh=Sn6R48OfeJfo7fCDnB7nVfmOKZbYhlD7VwHD2VMtBdU=; h=Date:From:To:Cc:Subject:In-Reply-To:References; b=qenBCOWCw6nf+sptD+khIgiFXLK0/u68oHCa/9+i9XvZokRXbV7Mt7ieJfTXdQQnX vGJHQB2yFnu5yh+5tK49Q37mkYbWWoVnMwYs7rN3Hoxm3xEOBACKAa0h7Ig5VLIDto J64t/baBe92XAdJH0V6a2IfJt80l5Zemr1o4xW1I= MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=_f35c1c26acf4d70a7454ee8cda25ef02" Content-Transfer-Encoding: 8bit Date: Wed, 16 Jun 2021 15:36:13 +0300 From: Alexander Pyhalov To: Tomas Vondra Cc: Bharath Rupireddy , Tom Lane , Andres Freund , PostgreSQL-development Subject: Re: postgres_fdw batching vs. (re)creating the tuple slots In-Reply-To: References: <20210530205831.vib4p7mt23cymhpc@alap3.anarazel.de> <2399204.1622409059@sss.pgh.pa.us> <20210530212616.5k5nxnbunwnwet2a@alap3.anarazel.de> <2433405.1622416631@sss.pgh.pa.us> <4e7ebff8-59ac-c90c-3ecd-8d9dfdfc75ad@enterprisedb.com> <6dc1c07b-87e6-63e6-10b3-cca4698361fb@enterprisedb.com> User-Agent: Roundcube Webmail/1.4.0 Message-ID: <7a8cf8d56b3d18e5c0bccd6cd42d04ac@postgrespro.ru> X-Sender: a.pyhalov@postgrespro.ru List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_f35c1c26acf4d70a7454ee8cda25ef02 Content-Transfer-Encoding: 8bit Content-Type: text/plain; charset=UTF-8; format=flowed Tomas Vondra писал 2021-06-12 00:01: > On 6/9/21 1:08 PM, Tomas Vondra wrote: >> >> >> On 6/9/21 12:50 PM, Bharath Rupireddy wrote: >>> On Wed, Jun 9, 2021 at 4:00 PM Tomas Vondra >>> wrote: >>>> >>>> Hi, >>>> >>>> Here's a v2 fixing a silly bug with reusing the same variable in two >>>> nested loops (worked for simple postgres_fdw cases, but "make check" >>>> failed). >>> >>> I applied these patches and ran make check in postgres_fdw contrib >>> module, I saw a server crash. Is it the same failure you were saying >>> above? >>> >> >> Nope, that was causing infinite loop. This is jut a silly mistake on >> my >> side - I forgot to replace the i/j variable inside the loop. Here's >> v3. >> >> regards >> > > FWIW I've pushed this, after improving the comments a little bit. > > > regards Hi. It seems this commit commit b676ac443b6a83558d4701b2dd9491c0b37e17c4 Author: Tomas Vondra Date: Fri Jun 11 20:19:48 2021 +0200 Optimize creation of slots for FDW bulk inserts has broken batch insert for partitions with unique indexes. Earlier the case worked as expected, inserting 1000 tuples. Now it exits with ERROR: duplicate key value violates unique constraint "p0_pkey" DETAIL: Key (x)=(1) already exists. CONTEXT: remote SQL command: INSERT INTO public.batch_table_p0(x, field1, field2) VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9), ($10, $11, $12), ($13, $14, $15), ($16, $17, $18), ($19, $20, $21), ($22, $23, $24), ($25, $26, $27), ($28, $29, $30), ($31, $32, $33), ($34, $35, $36), ($37, $38, $39), ($40, $41, $42), ($43, $44, $45), ($46, $47, $48), ($49, $50, $51), ($52, $53, $54), ($55, $56, $57), ($58, $59, $60), ($61, $62, $63), ($64, $65, $66), ($67, $68, $69), ($70, $71, $72), ($73, $74, $75), ($76, $77, $78), ($79, $80, $81), ($82, $83, $84), ($85, $86, $87), ($88, $89, $90), ($91, $92, $93), ($94, $95, $96), ($97, $98, $99), ($100, $101, $102), ($103, $104, $105), ($106, $107, $108), ($109, $110, $111), ($112, $113, $114), ($115, $116, $117), ($118, $119, $120), ($121, $122, $123), ($124, $125, $126), ($127, $128, $129), ($130, $131, $132), ($133, $134, $135), ($136, $137, $138), ($139, $140, $141), ($142, $143, $144), ($145, $146, $147), ($148, $149, $150), ($151, $152, $153), ($154, $155, $156), ($157, $158, $159), ($160, $161, $162), ($163, $164, $165), ($166, $167, $168), ($169, $170, $171), ($172, $173, $174), ($175, $176, $177), ($178, $179, $180), ($181, $182, $183), ($184, $185, $186), ($187, $188, $189), ($190, $191, $192), ($193, $194, $195), ($196, $197, $198), ($199, $200, $201), ($202, $203, $204), ($205, $206, $207), ($208, $209, $210), ($211, $212, $213), ($214, $215, $216), ($217, $218, $219), ($220, $221, $222), ($223, $224, $225), ($226, $227, $228), ($229, $230, $231), ($232, $233, $234), ($235, $236, $237), ($238, $239, $240), ($241, $242, $243), ($244, $245, $246), ($247, $248, $249), ($250, $251, $252), ($253, $254, $255), ($256, $257, $258), ($259, $260, $261), ($262, $263, $264), ($265, $266, $267), ($268, $269, $270), ($271, $272, $273), ($274, $275, $276), ($277, $278, $279), ($280, $281, $282), ($283, $284, $285), ($286, $287, $288), ($289, $290, $291), ($292, $293, $294), ($295, $296, $297), ($298, $299, $300) -- Best regards, Alexander Pyhalov, Postgres Professional --=_f35c1c26acf4d70a7454ee8cda25ef02 Content-Transfer-Encoding: base64 Content-Type: text/x-diff; name=batch_check_vanilla.patch Content-Disposition: attachment; filename=batch_check_vanilla.patch; size=2019 ZGlmZiAtLWdpdCBhL2NvbnRyaWIvcG9zdGdyZXNfZmR3L3NxbC9wb3N0Z3Jlc19mZHcuc3FsIGIv Y29udHJpYi9wb3N0Z3Jlc19mZHcvc3FsL3Bvc3RncmVzX2Zkdy5zcWwKaW5kZXggOGNiMjE0OGYx ZjYuLjRjMjgwZjFlNzc3IDEwMDY0NAotLS0gYS9jb250cmliL3Bvc3RncmVzX2Zkdy9zcWwvcG9z dGdyZXNfZmR3LnNxbAorKysgYi9jb250cmliL3Bvc3RncmVzX2Zkdy9zcWwvcG9zdGdyZXNfZmR3 LnNxbApAQCAtMzA4Myw3ICszMDgzLDQ2IEBAIFVQREFURSBiYXRjaF9jcF91cGRfdGVzdCB0IFNF VCBhID0gMSBGUk9NIChWQUxVRVMgKDEpLCAoMikpIHMoYSkgV0hFUkUgdC5hID0gcy5hCiBTRUxF Q1QgdGFibGVvaWQ6OnJlZ2NsYXNzLCAqIEZST00gYmF0Y2hfY3BfdXBkX3Rlc3Q7CiAKIC0tIENs ZWFuIHVwCi1EUk9QIFRBQkxFIGJhdGNoX3RhYmxlLCBiYXRjaF9jcF91cGRfdGVzdCBDQVNDQURF OworRFJPUCBUQUJMRSBiYXRjaF90YWJsZSwgYmF0Y2hfY3BfdXBkX3Rlc3QsIGJhdGNoX3RhYmxl X3AwLCBiYXRjaF90YWJsZV9wMSBDQVNDQURFOworCistLSBVc2UgcGFydGl0aW9uaW5nCitBTFRF UiBTRVJWRVIgbG9vcGJhY2sgT1BUSU9OUyAoQUREIGJhdGNoX3NpemUgJzEwMCcpOworCitDUkVB VEUgVEFCTEUgYmF0Y2hfdGFibGUgKCB4IGludCwgZmllbGQxIHRleHQsIGZpZWxkMiB0ZXh0KSBQ QVJUSVRJT04gQlkgSEFTSCAoeCk7CisKK0NSRUFURSBUQUJMRSBiYXRjaF90YWJsZV9wMCAoTElL RSBiYXRjaF90YWJsZSk7CitBTFRFUiBUQUJMRSBiYXRjaF90YWJsZV9wMCBBREQgQ09OU1RSQUlO VCBwMF9wa2V5IFBSSU1BUlkgS0VZICh4KTsKK0NSRUFURSBGT1JFSUdOIFRBQkxFIGJhdGNoX3Rh YmxlX3AwZgorCVBBUlRJVElPTiBPRiBiYXRjaF90YWJsZQorCUZPUiBWQUxVRVMgV0lUSCAoTU9E VUxVUyA0LCBSRU1BSU5ERVIgMCkKKwlTRVJWRVIgbG9vcGJhY2sKKwlPUFRJT05TICh0YWJsZV9u YW1lICdiYXRjaF90YWJsZV9wMCcpOworCitDUkVBVEUgVEFCTEUgYmF0Y2hfdGFibGVfcDEgKExJ S0UgYmF0Y2hfdGFibGUpOworQUxURVIgVEFCTEUgYmF0Y2hfdGFibGVfcDEgQUREIENPTlNUUkFJ TlQgcDFfcGtleSBQUklNQVJZIEtFWSAoeCk7CitDUkVBVEUgRk9SRUlHTiBUQUJMRSBiYXRjaF90 YWJsZV9wMWYKKwlQQVJUSVRJT04gT0YgYmF0Y2hfdGFibGUKKwlGT1IgVkFMVUVTIFdJVEggKE1P RFVMVVMgNCwgUkVNQUlOREVSIDEpCisJU0VSVkVSIGxvb3BiYWNrCisJT1BUSU9OUyAodGFibGVf bmFtZSAnYmF0Y2hfdGFibGVfcDEnKTsKKworQ1JFQVRFIFRBQkxFIGJhdGNoX3RhYmxlX3AyCisJ UEFSVElUSU9OIE9GIGJhdGNoX3RhYmxlCisJRk9SIFZBTFVFUyBXSVRIIChNT0RVTFVTIDQsIFJF TUFJTkRFUiAyKTsKK0FMVEVSIFRBQkxFIGJhdGNoX3RhYmxlX3AyIEFERCBDT05TVFJBSU5UIHAy X3BrZXkgUFJJTUFSWSBLRVkgKHgpOworCitDUkVBVEUgVEFCTEUgYmF0Y2hfdGFibGVfcDMgKExJ S0UgYmF0Y2hfdGFibGUpOworQUxURVIgVEFCTEUgYmF0Y2hfdGFibGVfcDMgQUREIENPTlNUUkFJ TlQgcDNfcGtleSBQUklNQVJZIEtFWSAoeCk7CitDUkVBVEUgRk9SRUlHTiBUQUJMRSBiYXRjaF90 YWJsZV9wM2YKKwlQQVJUSVRJT04gT0YgYmF0Y2hfdGFibGUKKwlGT1IgVkFMVUVTIFdJVEggKE1P RFVMVVMgNCwgUkVNQUlOREVSIDMpCisJU0VSVkVSIGxvb3BiYWNrCisJT1BUSU9OUyAodGFibGVf bmFtZSAnYmF0Y2hfdGFibGVfcDMnKTsKKworSU5TRVJUIElOVE8gYmF0Y2hfdGFibGUgU0VMRUNU IGksICd0ZXN0J3x8aSwgJ3Rlc3QnfHwgaSBGUk9NIGdlbmVyYXRlX3NlcmllcygxLCAxMDAwKSBp OworU0VMRUNUIENPVU5UKCopIEZST00gYmF0Y2hfdGFibGU7CisKK0FMVEVSIFNFUlZFUiBsb29w YmFjayBPUFRJT05TIChEUk9QIGJhdGNoX3NpemUpOwogCiAtLSA9PT09PT09PT09PT09PT09PT09 PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09CiAtLSB0ZXN0 IGFzeW5jaHJvbm91cyBleGVjdXRpb24K --=_f35c1c26acf4d70a7454ee8cda25ef02--