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 1lp8NR-0003GL-8X for pgsql-hackers@arkaria.postgresql.org; Fri, 04 Jun 2021 11:52:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lp8NQ-0005oB-8s for pgsql-hackers@arkaria.postgresql.org; Fri, 04 Jun 2021 11:52:36 +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 1lp8NP-0005o2-Oz for pgsql-hackers@lists.postgresql.org; Fri, 04 Jun 2021 11:52:36 +0000 Received: from mail-ej1-x631.google.com ([2a00:1450:4864:20::631]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lp8NM-00063g-Mj for pgsql-hackers@postgresql.org; Fri, 04 Jun 2021 11:52:34 +0000 Received: by mail-ej1-x631.google.com with SMTP id jt22so14059591ejb.7 for ; Fri, 04 Jun 2021 04:52:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb-com.20150623.gappssmtp.com; s=20150623; h=subject:from:to:cc:references:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=Pj9NuzZWhAK2niIiAfoVugEYDDckunkAZec3xRWnwhQ=; b=BGtczd4h+aoXx/oLm7ww3J4GBgOo8iiLqKeBoa8cqSNpIQCe6UZgC+ASqKokNqN1wr 7VZEQcqu0wVWlIEzBLktZzsFbQ7sTm4axNZbsAxbm+kpxQrBc8sMbk4dNRgNiPLmF+af RtoKvLGk+UR/9p1VPiRadvD3oO1r2kjrgnyDshP0XJVA89ihCM4BkZVI1gIPQrGG+zID TGqx7u4NgDwh3PmoayH7HGQcMOo9NxLkWwhxufLqxDPNif+tgTWYl0BTcPWluQyoZMzw uzTQh7pjL6WwnZQv8R5zLgwVNsc+QPD9MVDW0HI7djixeyw08PwpNzlj1/aAK2hH7M6Q 2hRA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:from:to:cc:references:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=Pj9NuzZWhAK2niIiAfoVugEYDDckunkAZec3xRWnwhQ=; b=RKNw5AZLsRqRBb0HLU5r4LVXT0RSaS/v43CoYtzOD7yL8duhpi+vjpy5pK8+DX45nw 2H1Q04i91ywoFirLJyY+vosk9jat8phDQ2FT8Fr3yBPl5uaEFLSncxV6tBG8PFGND0Py l74SacCY3qtDuSAPq2VkTK1gRihGmlLlYHJsJjlkuTs9aklQIh/YU7mBHGuIeU+GGtBd qyqKB4WNvsW+x7ADbw0qR2cLhNrIWUDgtknyOdORH6bFallnAzLRkrlTLuFQK6dz4NYP 74E1DcgVZYRotyF2w8254FhxSgSL98MRsUgKY51l8dzUUSxW6ZhcBoAT+O7d1+k4TrDS eW4g== X-Gm-Message-State: AOAM5335pI+5mSZWL3NJn1v+vAj/ZUMROJOG3fDxH5Hw+kNWSmuwJbn+ Qh8sPGNm9jSs0bU6+Gv7otXNPV8ueqrtKoayB48b8ot9uhuu1ZcrVzL7ocyOlrZImGLlpvOFMde t07VhEmPSZGvqLOfRo2SVwWXSQyJvkNfYQxKPXUqcY5Xonroeaoz80EExeiuaYw4RuNRUgVyCO5 hGUdqW+ceB3FwkU0BQ0taCMGEGHw9Suc3EAWKKxeADlVCkEnRWAb2n5JXq44rIG/bZcj3yQ6/O9 Opb92pynokePA4flgrfaofr4qWOmQ3M3enqqluK X-Google-Smtp-Source: ABdhPJx3tqz1XhdZmq8i+kKx4PuCyx43tvlQ5SI5Wq+9D9q8u1kAmM4eSR1+KH3NOLGh6a5Osi9z9w== X-Received: by 2002:a17:906:6bd8:: with SMTP id t24mr3832376ejs.501.1622807550996; Fri, 04 Jun 2021 04:52:30 -0700 (PDT) Received: from [10.137.0.19] (ip-86-49-254-183.net.upcbroadband.cz. [86.49.254.183]) by smtp.gmail.com with ESMTPSA id u17sm3078734edx.16.2021.06.04.04.52.29 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 04 Jun 2021 04:52:30 -0700 (PDT) Subject: Re: postgres_fdw batching vs. (re)creating the tuple slots From: Tomas Vondra To: Tom Lane , Andres Freund Cc: 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> Message-ID: <4e7ebff8-59ac-c90c-3ecd-8d9dfdfc75ad@enterprisedb.com> Date: Fri, 4 Jun 2021 13:52:28 +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: Content-Type: multipart/mixed; boundary="------------EBE7D507FCBCF19292A90A74" 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. --------------EBE7D507FCBCF19292A90A74 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Argh! I forgot the attachments, of course. On 6/4/21 1:48 PM, Tomas Vondra wrote: > Hi, > > Here's two WIP patches that fixes the regression for me. The first part > is from [1], so make large batches work, 0002 just creates a copy of the > tupledesc to not cause issues in resource owner, 0003 ensures we only > initialize the slots once (not per batch). > > With the patches applied, the timings look like this: > > batch timing > ---------------------- > 1 64194.942 ms > 10 7233.785 ms > 100 2244.255 ms > 32k 1372.175 ms > > which seems fine. I still need to get this properly tested etc. and make > sure nothing is left over. > > regards > > > [1] > https://postgr.es/m/OS0PR01MB571603973C0AC2874AD6BF2594299%40OS0PR01MB5716.jpnprd01.prod.outlook.com > -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --------------EBE7D507FCBCF19292A90A74 Content-Type: text/x-patch; charset=UTF-8; name="0001-Add-PQ_QUERY_PARAM_MAX_LIMIT.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-Add-PQ_QUERY_PARAM_MAX_LIMIT.patch" From f3bd790af94c89d5b61a9d9c23a4b05a309d73bf Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 4 Jun 2021 12:34:23 +0200 Subject: [PATCH 1/3] Add PQ_QUERY_PARAM_MAX_LIMIT --- contrib/postgres_fdw/postgres_fdw.c | 11 +++++++++-- doc/src/sgml/postgres-fdw.sgml | 11 +++++++++++ src/interfaces/libpq/fe-exec.c | 21 ++++++++++++--------- src/interfaces/libpq/libpq-fe.h | 2 ++ 4 files changed, 34 insertions(+), 11 deletions(-) diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index c48a421e88..ac86b06b8f 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -1979,7 +1979,7 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo) Assert(fmstate == NULL || fmstate->aux_fmstate == NULL); /* - * In EXPLAIN without ANALYZE, ri_fdwstate is NULL, so we have to lookup + * In EXPLAIN without ANALYZE, ri_FdwState is NULL, so we have to lookup * the option directly in server/table options. Otherwise just use the * value we determined earlier. */ @@ -1994,7 +1994,14 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo) resultRelInfo->ri_TrigDesc->trig_insert_after_row)) return 1; - /* Otherwise use the batch size specified for server/table. */ + /* + * Otherwise use the batch size specified for server/table. The number of + * parameters in a batch is limited to 64k (uint16), so make sure we don't + * exceed this limit by using the maximum batch_size possible. + */ + if (fmstate && fmstate->p_nums > 0) + batch_size = Min(batch_size, PQ_QUERY_PARAM_MAX_LIMIT / fmstate->p_nums); + return batch_size; } diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index fb87372bde..564651dfaa 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -372,6 +372,17 @@ OPTIONS (ADD password_required 'false'); overrides an option specified for the server. The default is 1. + + + Note the actual number of rows postgres_fdw inserts at + once depends on the number of columns and the provided + batch_size value. The batch is executed as a single + query, and the libpq protocol (which postgres_fdw + uses to connect to a remote server) limits the number of parameters in a + single query to 64k. When the number of columns * batch_size + exceeds the limit, the batch_size will be adjusted to + avoid an error. + diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c index 03592bdce9..832d61c544 100644 --- a/src/interfaces/libpq/fe-exec.c +++ b/src/interfaces/libpq/fe-exec.c @@ -1403,10 +1403,11 @@ PQsendQueryParams(PGconn *conn, libpq_gettext("command string is a null pointer\n")); return 0; } - if (nParams < 0 || nParams > 65535) + if (nParams < 0 || nParams > PQ_QUERY_PARAM_MAX_LIMIT) { - appendPQExpBufferStr(&conn->errorMessage, - libpq_gettext("number of parameters must be between 0 and 65535\n")); + appendPQExpBuffer(&conn->errorMessage, + libpq_gettext("number of parameters must be between 0 and %d\n"), + PQ_QUERY_PARAM_MAX_LIMIT); return 0; } @@ -1451,10 +1452,11 @@ PQsendPrepare(PGconn *conn, libpq_gettext("command string is a null pointer\n")); return 0; } - if (nParams < 0 || nParams > 65535) + if (nParams < 0 || nParams > PQ_QUERY_PARAM_MAX_LIMIT) { - appendPQExpBufferStr(&conn->errorMessage, - libpq_gettext("number of parameters must be between 0 and 65535\n")); + appendPQExpBuffer(&conn->errorMessage, + libpq_gettext("number of parameters must be between 0 and %d\n"), + PQ_QUERY_PARAM_MAX_LIMIT); return 0; } @@ -1548,10 +1550,11 @@ PQsendQueryPrepared(PGconn *conn, libpq_gettext("statement name is a null pointer\n")); return 0; } - if (nParams < 0 || nParams > 65535) + if (nParams < 0 || nParams > PQ_QUERY_PARAM_MAX_LIMIT) { - appendPQExpBufferStr(&conn->errorMessage, - libpq_gettext("number of parameters must be between 0 and 65535\n")); + appendPQExpBuffer(&conn->errorMessage, + libpq_gettext("number of parameters must be between 0 and %d\n"), + PQ_QUERY_PARAM_MAX_LIMIT); return 0; } diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h index 227adde5a5..113ab52ada 100644 --- a/src/interfaces/libpq/libpq-fe.h +++ b/src/interfaces/libpq/libpq-fe.h @@ -429,6 +429,8 @@ extern PGresult *PQexecPrepared(PGconn *conn, int resultFormat); /* Interface for multiple-result or asynchronous queries */ +#define PQ_QUERY_PARAM_MAX_LIMIT 65535 + extern int PQsendQuery(PGconn *conn, const char *query); extern int PQsendQueryParams(PGconn *conn, const char *command, -- 2.31.1 --------------EBE7D507FCBCF19292A90A74 Content-Type: text/x-patch; charset=UTF-8; name="0002-create-copy-of-a-descriptor-for-batching.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-create-copy-of-a-descriptor-for-batching.patch" From e1f083732a7f02b1093fc2435c40596d959549d5 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 4 Jun 2021 12:45:18 +0200 Subject: [PATCH 2/3] create copy of a descriptor for batching --- src/backend/executor/nodeModifyTable.c | 9 +++++++-- 1 file changed, 7 insertions(+), 2 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 379b056310..c287a371a1 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -678,6 +678,8 @@ ExecInsert(ModifyTableState *mtstate, */ if (resultRelInfo->ri_BatchSize > 1) { + TupleDesc tdesc; + /* * If a certain number of tuples have already been accumulated, or * a tuple has come for a different relation than that for the @@ -703,13 +705,16 @@ ExecInsert(ModifyTableState *mtstate, resultRelInfo->ri_BatchSize); } + tdesc = CreateTupleDescCopy(slot->tts_tupleDescriptor); + resultRelInfo->ri_Slots[resultRelInfo->ri_NumSlots] = - MakeSingleTupleTableSlot(slot->tts_tupleDescriptor, + MakeSingleTupleTableSlot(tdesc, slot->tts_ops); ExecCopySlot(resultRelInfo->ri_Slots[resultRelInfo->ri_NumSlots], slot); + resultRelInfo->ri_PlanSlots[resultRelInfo->ri_NumSlots] = - MakeSingleTupleTableSlot(planSlot->tts_tupleDescriptor, + MakeSingleTupleTableSlot(tdesc, planSlot->tts_ops); ExecCopySlot(resultRelInfo->ri_PlanSlots[resultRelInfo->ri_NumSlots], planSlot); -- 2.31.1 --------------EBE7D507FCBCF19292A90A74 Content-Type: text/x-patch; charset=UTF-8; name="0003-initialize-slots-only-once-for-batching.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0003-initialize-slots-only-once-for-batching.patch" From 605a29661db9f89dfb21692cc6c918a439d14252 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 4 Jun 2021 12:59:47 +0200 Subject: [PATCH 3/3] initialize slots only once for batching --- src/backend/executor/nodeModifyTable.c | 42 ++++++++++++++------------ src/include/nodes/execnodes.h | 1 + 2 files changed, 24 insertions(+), 19 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index c287a371a1..f16c17e8d4 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -678,8 +678,6 @@ ExecInsert(ModifyTableState *mtstate, */ if (resultRelInfo->ri_BatchSize > 1) { - TupleDesc tdesc; - /* * If a certain number of tuples have already been accumulated, or * a tuple has come for a different relation than that for the @@ -705,19 +703,25 @@ ExecInsert(ModifyTableState *mtstate, resultRelInfo->ri_BatchSize); } - tdesc = CreateTupleDescCopy(slot->tts_tupleDescriptor); + /* initialize the slot, if not already done */ + if (resultRelInfo->ri_NumSlots >= resultRelInfo->ri_BatchInitialized) + { + TupleDesc tdesc = CreateTupleDescCopy(slot->tts_tupleDescriptor); - resultRelInfo->ri_Slots[resultRelInfo->ri_NumSlots] = - MakeSingleTupleTableSlot(tdesc, - slot->tts_ops); - ExecCopySlot(resultRelInfo->ri_Slots[resultRelInfo->ri_NumSlots], - slot); + 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); + resultRelInfo->ri_PlanSlots[resultRelInfo->ri_NumSlots] = + MakeSingleTupleTableSlot(tdesc, + planSlot->tts_ops); + ExecCopySlot(resultRelInfo->ri_PlanSlots[resultRelInfo->ri_NumSlots], + planSlot); + + resultRelInfo->ri_BatchInitialized++; + } resultRelInfo->ri_NumSlots++; @@ -1039,12 +1043,6 @@ ExecBatchInsert(ModifyTableState *mtstate, if (canSetTag && numInserted > 0) estate->es_processed += numInserted; - - for (i = 0; i < numSlots; i++) - { - ExecDropSingleTupleTableSlot(slots[i]); - ExecDropSingleTupleTableSlot(planSlots[i]); - } } /* ---------------------------------------------------------------- @@ -3174,6 +3172,12 @@ ExecEndModifyTable(ModifyTableState *node) resultRelInfo->ri_FdwRoutine->EndForeignModify != NULL) resultRelInfo->ri_FdwRoutine->EndForeignModify(node->ps.state, resultRelInfo); + + for (i = 0; i < resultRelInfo->ri_NumSlots; i++) + { + ExecDropSingleTupleTableSlot(resultRelInfo->ri_Slots[i]); + ExecDropSingleTupleTableSlot(resultRelInfo->ri_PlanSlots[i]); + } } /* diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 7795a69490..1062a44ee1 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -463,6 +463,7 @@ typedef struct ResultRelInfo /* batch insert stuff */ int ri_NumSlots; /* number of slots in the array */ int ri_BatchSize; /* max slots inserted in a single batch */ + int ri_BatchInitialized; /* number of slots initialized */ TupleTableSlot **ri_Slots; /* input tuples for batch insert */ TupleTableSlot **ri_PlanSlots; -- 2.31.1 --------------EBE7D507FCBCF19292A90A74--