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.94.2) (envelope-from ) id 1tI8VJ-00CPbr-1n for pgsql-bugs@arkaria.postgresql.org; Mon, 02 Dec 2024 15:38:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tI8VG-000nOV-6o for pgsql-bugs@arkaria.postgresql.org; Mon, 02 Dec 2024 15:38:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tI8VF-000nOM-DJ for pgsql-bugs@lists.postgresql.org; Mon, 02 Dec 2024 15:38:27 +0000 Received: from fout-b6-smtp.messagingengine.com ([202.12.124.149]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tI8VC-000fx0-Jq for pgsql-bugs@lists.postgresql.org; Mon, 02 Dec 2024 15:38:26 +0000 Received: from phl-compute-01.internal (phl-compute-01.phl.internal [10.202.2.41]) by mailfout.stl.internal (Postfix) with ESMTP id E801511401BF; Mon, 2 Dec 2024 10:38:19 -0500 (EST) Received: from phl-imap-09 ([10.202.2.99]) by phl-compute-01.internal (MEProxy); Mon, 02 Dec 2024 10:38:20 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm2; t=1733153899; x=1733240299; bh=QoxGjOho55 MyzLbIgMdLWCNuF0SP5u2mhlnzWVaRg+k=; b=scE622ouIzePIvVo83PJpy99Hg 7qEj9dWXTJucugktSJKDmaO73xpMOS9wRJ3wuzNEd1nyyacVqZd5r9/Fi1emC+c0 UYmjrG4L0T65YOaA1OBAuGbFs1GXZ6GYri/2uYskJBzghs5nSDvVyyYgT6dTK9PI rPAOaAvMBJiRGJsxsMoRp4aRra6WrJI5bSSaspLRnbwGYcywv0jbb+uHQ3a3oWtQ IJfeaVkLXZGIxoBjEvtpE+OQh7yG5lbjz62+tUqsajPPxikCrjYbbKzGhAO4THkU 56fu46WQfxTLzaA6FTNgC3HN5EG2DWmSJRC/jqzY2L1JkUr8EGeooKy31/Cw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t= 1733153899; x=1733240299; bh=QoxGjOho55MyzLbIgMdLWCNuF0SP5u2mhln zWVaRg+k=; b=qKvJgsbWQAcegedQe+AHcAi0hKoQaV7h7Drp9eu5UKViDSOJcBG RohoK+pHHLqSUoDSLwHaySaE59EENcB7dGgx4BKdfxKroKGKKbSRalkVYY+oIk0c uvUyq8Wi32CraueovmLFWDSI4YR2L4xsf7c3cXzRIVE0kOMpeucTUy/Jgl0evo9+ TkKg7kWE1IROmu4HFaZY9itlysVrQS3CMRU5wo6i6emGleFe25rrKINLGngIKoGe yM8i4DYTussqTS0lCvNBNOavvhAH6FMNAwRNlzO7RgeLk9OZaGgO47qIAMlmY/Iq lFWtyqqCZnn3SkqFij+lxkAz0FF2eDJAQ5A== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefuddrheelgdejjecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpggftfghnshhusghstghrihgsvgdpuffr tefokffrpgfnqfghnecuuegrihhlohhuthemuceftddtnecunecujfgurhepofggfffhvf fkjghfufgtsegrtderreertdejnecuhfhrohhmpedfgfhulhgvrhcuvfgrvhgvihhrrgdf uceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecuggftrfgrthhtvghrnhepkeeike etkedtueeluddvheevieduhefhleeuieejkeeufeevieehteetuefgkeeunecuffhomhgr ihhnpegvnhhtvghrphhrihhsvggusgdrtghomhenucevlhhushhtvghrufhiiigvpedtne curfgrrhgrmhepmhgrihhlfhhrohhmpegvuhhlvghrsegvuhhlvghrthhordgtohhmpdhn sggprhgtphhtthhopedvpdhmohguvgepshhmthhpohhuthdprhgtphhtthhopehpghhsqh hlqdgsuhhgsheslhhishhtshdrphhoshhtghhrvghsqhhlrdhorhhgpdhrtghpthhtohep fedtfeeijeejfeeiheesqhhqrdgtohhm X-ME-Proxy: Feedback-ID: i0c21471d:Fastmail Received: by mailuser.phl.internal (Postfix, from userid 501) id 669D0780068; Mon, 2 Dec 2024 10:38:19 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface MIME-Version: 1.0 Date: Mon, 02 Dec 2024 12:37:59 -0300 From: "Euler Taveira" To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Message-Id: <14c14680-63a7-41b3-b45a-eff4b4d7e24f@app.fastmail.com> In-Reply-To: <18729-9bd4c9ec46c39848@postgresql.org> References: <18729-9bd4c9ec46c39848@postgresql.org> Subject: Re: BUG #18729: update with multiple subpartitions return error: too many range table entries Content-Type: multipart/alternative; boundary=09b42495696943b095ff90c13954e648 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --09b42495696943b095ff90c13954e648 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable On Mon, Dec 2, 2024, at 4:07 AM, PG Bug reporting form wrote: > UPDATE rqg_table3 T2 > SET c2 =3D T2.c2 || '=E8=AF=A5=E8=A1=8C=E5=BA=8F=E5=8F=B7=E4=B8=8D=E6=AD= =A3=E7=A1=AE;' > WHERE NOT EXISTS ( > SELECT NULL > FROM ( > SELECT c1, c0, c2 AS RIGHT_NUM > FROM rqg_table3 > ORDER BY c1 > ) T3 > WHERE T3.RIGHT_NUM =3D T2.c2 > AND T3.c0 =3D T2.c0 > ); It works for v14+. That's because there were some partitioning improveme= nts. In particular, the commit 86dc90056 and follow up commits. The release notes describe it as "Improve the performance of updates and deletes on partit= ioned tables with many partitions". I would say that it is time to move from v13 because it is now the oldest supported version. -- Euler Taveira EDB https://www.enterprisedb.com/ --09b42495696943b095ff90c13954e648 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
On Mon, Dec 2, = 2024, at 4:07 AM, PG Bug reporting form wrote:
UPDATE rqg_table3 T2
S= ET c2 =3D T2.c2 || '=E8=AF=A5=E8=A1=8C=E5=BA=8F=E5=8F=B7=E4=B8=8D=E6=AD=A3= =E7=A1=AE;'
WHERE NOT EXISTS (
SELECT NULL
FROM (
SELECT c1, c0, c2 AS RIGHT_NUM
FROM rqg_table3
ORDER BY c1
) T3
<= /div>
WHERE T3.RIGHT_NUM =3D T2.c2
AND T3.c0 =3D T2.c0=
);

It works for= v14+. That's because there were some partitioning improvements. In
<= /div>
particular, the commit 86dc90056 and follow up commits. The re= lease notes
describe= it as "Improve the performance of updates and deletes on partitioned
tables with many parti= tions".

I would say that it is time to = move from v13 because it is now the oldest
supported version.


=
--
Euler Tave= ira
EDB   https://www.enterprisedb.com/

--09b42495696943b095ff90c13954e648--