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 1ltWSX-0001mv-Ay for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Jun 2021 14:24:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ltWS0-0000YS-1m for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Jun 2021 14:23:28 +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 1ltWRz-0000YK-Hr for pgsql-hackers@lists.postgresql.org; Wed, 16 Jun 2021 14:23:27 +0000 Received: from mail-ed1-x52c.google.com ([2a00:1450:4864:20::52c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ltWRw-0000XM-9r for pgsql-hackers@postgresql.org; Wed, 16 Jun 2021 14:23:26 +0000 Received: by mail-ed1-x52c.google.com with SMTP id n20so2943665edv.8 for ; Wed, 16 Jun 2021 07:23:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=B0EwB2UutOq+kfGmwEikhzEToviDGXE3IzyqB87heqM=; b=sSGsZgrpJeD751H4hjAO7GBkPU6GnRVqNDa97pO7nKDi2Kv4Iue1I1zulqHtBhGnn8 HTdUcXZfrlnxv4JFJ9x++4cxYSY5mbFVr/7GegpYnEKmNL7AA3g9DUzU9LHo5+gNva1z 8mopHqmWIm+Si4gGGcdt0SLXpV6pzB/pc565ohO3b6Ih9dwFPd811Et8vSyGv/9CugJl OPwjWSnydSMUWs+GuPcLDVo3kN0BGYd/c34zL6KY1OTR+a8kVhTKlatDrvIfvqqpln+P 4cbG58v5LoPGyfUIS+ptEnIEaaPW2PLijvxIL3u0lJv90flVvERfQgm8ADYz53aIv2Rg 7avQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=B0EwB2UutOq+kfGmwEikhzEToviDGXE3IzyqB87heqM=; b=pKYDAuSwtp90hvd4luB/hkr+azVH/JuVwwoE6pj4Uiu4mxHkxpW+yXV7vJayxVVXkT VY4236rOjMl3R2l/ugh5KIsSwpMT7fk+lWn2ad0Rhy1djxd6VTwq5oqvB/+MoFMIAZ6e K/2K7mxLI+UkZncZ+x7qdW8upIwB0IirCCjd1xi2GA+mW8eADdfpbpFjWUrsomC+kuXX xdKVypE1DzYdUg7e545ZnoyfuVkNyJBv2SNQy6qNow2DlvPTMpkZRH0ZHStKAY6NGMRQ EFw8/TNE8ajqmyY7iQXtMhqonPxQcqsEyYP5xHX2Xq41XRg17OUK+6cKtx/BDK7SmnDZ IQkQ== X-Gm-Message-State: AOAM531G5zGCJHb3T4JDZzsWvnMZt4TfJFgbFhbK57RjKslWo40OsmvS JRjFZY0yiT9H+Izsj3QzYucZm+M7skfUTWl6hAyubTnSxd/NH0Rh8iej+jUiy90MRmzjH6SMJ3o Mm6egmZKd2Zedka74ioLpVQHFgWMBc/o/o4owIzs7LATZHLThvnm4eNHgFoN1M+R51gUvljoZim IlHVZ3wEZj/4Jub/fy69+g9n8vuFFJHAHknjr3cg1pZo1Vlwm7LEpSx/euR6Tnr6K3zfygZowGu oR9RLzlHBGMv0EqHoQR3kcN/P3biOriAOVxk5KZ X-Google-Smtp-Source: ABdhPJzB/+8j80H6sdkET1cEYvuD7bbwvdCof83kKKx8PHiw7CSiaXtKieba9AgOBSL6JbReEpuCkw== X-Received: by 2002:a05:6402:366:: with SMTP id s6mr4565055edw.209.1623853402552; Wed, 16 Jun 2021 07:23:22 -0700 (PDT) Received: from [10.137.0.18] (ip-86-49-254-183.net.upcbroadband.cz. [86.49.254.183]) by smtp.gmail.com with ESMTPSA id i12sm1949861edx.13.2021.06.16.07.23.21 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 16 Jun 2021 07:23:21 -0700 (PDT) Subject: Re: postgres_fdw batching vs. (re)creating the tuple slots To: Alexander Pyhalov Cc: Bharath Rupireddy , Tom Lane , Andres Freund , PostgreSQL-development 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> <7a8cf8d56b3d18e5c0bccd6cd42d04ac@postgrespro.ru> From: Tomas Vondra Message-ID: Date: Wed, 16 Jun 2021 16:23:18 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.10.1 MIME-Version: 1.0 In-Reply-To: <7a8cf8d56b3d18e5c0bccd6cd42d04ac@postgrespro.ru> Content-Type: multipart/mixed; boundary="------------5A8CCE4D04129303F02A55B0" Content-Language: en-US X-CLOUD-SEC-AV-Info: enterprisedb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 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. --------------5A8CCE4D04129303F02A55B0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 6/16/21 2:36 PM, Alexander Pyhalov wrote: > > 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. > Thanks for the report and reproducer! Turns out this is a mind-bogglingly silly bug I made in b676ac443b :-( The data is copied into the slots only in the branch that initializes them, so the subsequent batches just insert the same data over and over. The attached patch fixes that, and adds a regression test (a bit smaller version of your reproducer). I'll get this committed shortly. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --------------5A8CCE4D04129303F02A55B0 Content-Type: text/x-patch; charset=UTF-8; name="batch-fix.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="batch-fix.patch" diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 1fb26639fc..858e5d4a38 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -9761,7 +9761,87 @@ SELECT tableoid::regclass, * FROM batch_cp_upd_test; (2 rows) -- Clean up -DROP TABLE batch_table, batch_cp_upd_test CASCADE; +DROP TABLE batch_table, batch_cp_upd_test, batch_table_p0, batch_table_p1 CASCADE; +-- Use partitioning +ALTER SERVER loopback OPTIONS (ADD batch_size '10'); +CREATE TABLE batch_table ( x int, field1 text, field2 text) PARTITION BY HASH (x); +CREATE TABLE batch_table_p0 (LIKE batch_table); +ALTER TABLE batch_table_p0 ADD CONSTRAINT p0_pkey PRIMARY KEY (x); +CREATE FOREIGN TABLE batch_table_p0f + PARTITION OF batch_table + FOR VALUES WITH (MODULUS 2, REMAINDER 0) + SERVER loopback + OPTIONS (table_name 'batch_table_p0'); +CREATE TABLE batch_table_p1 (LIKE batch_table); +ALTER TABLE batch_table_p1 ADD CONSTRAINT p1_pkey PRIMARY KEY (x); +CREATE FOREIGN TABLE batch_table_p1f + PARTITION OF batch_table + FOR VALUES WITH (MODULUS 2, REMAINDER 1) + SERVER loopback + OPTIONS (table_name 'batch_table_p1'); +INSERT INTO batch_table SELECT i, 'test'||i, 'test'|| i FROM generate_series(1, 50) i; +SELECT COUNT(*) FROM batch_table; + count +------- + 50 +(1 row) + +SELECT * FROM batch_table ORDER BY x; + x | field1 | field2 +----+--------+-------- + 1 | test1 | test1 + 2 | test2 | test2 + 3 | test3 | test3 + 4 | test4 | test4 + 5 | test5 | test5 + 6 | test6 | test6 + 7 | test7 | test7 + 8 | test8 | test8 + 9 | test9 | test9 + 10 | test10 | test10 + 11 | test11 | test11 + 12 | test12 | test12 + 13 | test13 | test13 + 14 | test14 | test14 + 15 | test15 | test15 + 16 | test16 | test16 + 17 | test17 | test17 + 18 | test18 | test18 + 19 | test19 | test19 + 20 | test20 | test20 + 21 | test21 | test21 + 22 | test22 | test22 + 23 | test23 | test23 + 24 | test24 | test24 + 25 | test25 | test25 + 26 | test26 | test26 + 27 | test27 | test27 + 28 | test28 | test28 + 29 | test29 | test29 + 30 | test30 | test30 + 31 | test31 | test31 + 32 | test32 | test32 + 33 | test33 | test33 + 34 | test34 | test34 + 35 | test35 | test35 + 36 | test36 | test36 + 37 | test37 | test37 + 38 | test38 | test38 + 39 | test39 | test39 + 40 | test40 | test40 + 41 | test41 | test41 + 42 | test42 | test42 + 43 | test43 | test43 + 44 | test44 | test44 + 45 | test45 | test45 + 46 | test46 | test46 + 47 | test47 | test47 + 48 | test48 | test48 + 49 | test49 | test49 + 50 | test50 | test50 +(50 rows) + +ALTER SERVER loopback OPTIONS (DROP batch_size); -- =================================================================== -- test asynchronous execution -- =================================================================== diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 8cb2148f1f..a5110fa863 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -3028,7 +3028,7 @@ DROP FOREIGN TABLE ftable; -- try if large batches exceed max number of bind parameters CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '100000' ); -INSERT INTO ftable SELECT * FROM generate_series(1, 70000) i; +-- INSERT INTO ftable SELECT * FROM generate_series(1, 70000) i; SELECT COUNT(*) FROM ftable; TRUNCATE batch_table; DROP FOREIGN TABLE ftable; @@ -3083,7 +3083,34 @@ UPDATE batch_cp_upd_test t SET a = 1 FROM (VALUES (1), (2)) s(a) WHERE t.a = s.a SELECT tableoid::regclass, * FROM batch_cp_upd_test; -- Clean up -DROP TABLE batch_table, batch_cp_upd_test CASCADE; +DROP TABLE batch_table, batch_cp_upd_test, batch_table_p0, batch_table_p1 CASCADE; + +-- Use partitioning +ALTER SERVER loopback OPTIONS (ADD batch_size '10'); + +CREATE TABLE batch_table ( x int, field1 text, field2 text) PARTITION BY HASH (x); + +CREATE TABLE batch_table_p0 (LIKE batch_table); +ALTER TABLE batch_table_p0 ADD CONSTRAINT p0_pkey PRIMARY KEY (x); +CREATE FOREIGN TABLE batch_table_p0f + PARTITION OF batch_table + FOR VALUES WITH (MODULUS 2, REMAINDER 0) + SERVER loopback + OPTIONS (table_name 'batch_table_p0'); + +CREATE TABLE batch_table_p1 (LIKE batch_table); +ALTER TABLE batch_table_p1 ADD CONSTRAINT p1_pkey PRIMARY KEY (x); +CREATE FOREIGN TABLE batch_table_p1f + PARTITION OF batch_table + FOR VALUES WITH (MODULUS 2, REMAINDER 1) + SERVER loopback + OPTIONS (table_name 'batch_table_p1'); + +INSERT INTO batch_table SELECT i, 'test'||i, 'test'|| i FROM generate_series(1, 50) i; +SELECT COUNT(*) FROM batch_table; +SELECT * FROM batch_table ORDER BY x; + +ALTER SERVER loopback OPTIONS (DROP batch_size); -- =================================================================== -- test asynchronous execution diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 88c479c6da..143517bc76 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -717,18 +717,20 @@ ExecInsert(ModifyTableState *mtstate, resultRelInfo->ri_Slots[resultRelInfo->ri_NumSlots] = MakeSingleTupleTableSlot(tdesc, slot->tts_ops); - ExecCopySlot(resultRelInfo->ri_Slots[resultRelInfo->ri_NumSlots], - slot); resultRelInfo->ri_PlanSlots[resultRelInfo->ri_NumSlots] = MakeSingleTupleTableSlot(tdesc, planSlot->tts_ops); - ExecCopySlot(resultRelInfo->ri_PlanSlots[resultRelInfo->ri_NumSlots], - planSlot); /* remember how many batch slots we initialized */ resultRelInfo->ri_NumSlotsInitialized++; } + ExecCopySlot(resultRelInfo->ri_Slots[resultRelInfo->ri_NumSlots], + slot); + + ExecCopySlot(resultRelInfo->ri_PlanSlots[resultRelInfo->ri_NumSlots], + planSlot); + resultRelInfo->ri_NumSlots++; MemoryContextSwitchTo(oldContext); --------------5A8CCE4D04129303F02A55B0--