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 1tmBNH-004lQ3-22 for pgsql-hackers@arkaria.postgresql.org; Sun, 23 Feb 2025 12:46:23 +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 1tmBNE-009zT5-CR for pgsql-hackers@arkaria.postgresql.org; Sun, 23 Feb 2025 12:46:20 +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 1tmBNE-009zSx-1D for pgsql-hackers@lists.postgresql.org; Sun, 23 Feb 2025 12:46:20 +0000 Received: from mail-ej1-x635.google.com ([2a00:1450:4864:20::635]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tmBNB-000Jrc-0x for pgsql-hackers@postgresql.org; Sun, 23 Feb 2025 12:46:19 +0000 Received: by mail-ej1-x635.google.com with SMTP id a640c23a62f3a-abb8d63b447so449935266b.0 for ; Sun, 23 Feb 2025 04:46:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740314775; x=1740919575; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=EClEMoIFaJqPBrZudyuCiFhqINE99m3cS0H18lu6KYs=; b=ityDHrcThs1XyZabw4xUXiWMdt+InCBiUr5zUeT325nuiOHMu/FVdMbVd2XMr9WeiD 41epaEVgxpva9jJwYIFWBTGgm4e1Rv+raOvn37Trr8LoMxJi4+I7HbODUx/6wLNpxIXX hdYUdsrR79fRY9QHqgXEHptYomgSazyn5DqIFa51CWVcO0NfYoC/Uc6IM2MaacqaCAhY lwN0eYjB6eR6HgGw42BoNH0U+CWDmCGpwPUsurG7a490D0dC09Qph0INXHTwJXXsWYgX MxKuRVLLhucghlIl+UimN80lpM0MXDszj8UYSiDGS4bvMXay5e922JjKCj1wZmNY9MyM hlrg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740314775; x=1740919575; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=EClEMoIFaJqPBrZudyuCiFhqINE99m3cS0H18lu6KYs=; b=FqRzit0h5tHCK9j7pu0k/eNlGaeMWJgMlIsGHA7NKzYo+E4OK2aR+ZxXvcyyKX1FOw rHkCkNyV5gBP4aATxoQzmVXmTqYpMFtdV5obxOksf3qVsJWUPERNk0hvoD2d1ujQDMj6 uEwgpqq4LP7tRuDUeHPDr2fkPMvfD00TPfXVnv9XBEGXPOm0zM4P5ua8SpGEd9u47ks3 wCJAKs1IJKbsEeYhZohvjctzKOgcpbe/0fwb38+0hYot6ZVbeT5YOKEGTNd8muOfJecz C7IPMSzUFL9Mc5RmiVW0wTC0Q7dJQiZEtTLHxXAAHxkrLy7Ytw0f8lGLAGzLnIJYXbAo QYVw== X-Forwarded-Encrypted: i=1; AJvYcCWUuQ1b8oT3DTGQ24tdJXDCpPAZfN+LH7E/SJKzRxHwGGNMskWCEDAErPGZx51PLG9SgEpUf3WqCOORFWMi@postgresql.org X-Gm-Message-State: AOJu0YyH948nDvjAgV7yjrHa5CLdgkHnaKkAJq9lIN1OaDJL2Hj8C8Co T0T+gw9WcoZKgWWgUzir2Vwnq3yspc/n/5XA+nStFkvnfS2hIeIfA2XzuCKuJ2/6FJn0H+Kmw49 o6vsYYcDfaJo2POWYw8TQ0ZvBC8s= X-Gm-Gg: ASbGnctj+8e4htYTA7Fb8Ia6858paQhQZphiI9jncImyeJPkFQYEA+mVBPZT8ncwds/ zaqeIILydWSIQCs14XxebEp6D6aBCUgClQMCkA4cqfjGuAjC/L4fOY1PDamMJATiyQnHmfUbKUC bCaxaeLhfNVhg= X-Google-Smtp-Source: AGHT+IHSIs1zPg7KhoeiJ1lH9stlusRXAUzb9blVLVh2hbD4YytcWakwn55NrXtmxOm0nmBUWtE+OiLOYysd1BxkZls= X-Received: by 2002:a17:906:6a0a:b0:ab7:e1d5:d0c3 with SMTP id a640c23a62f3a-abc09c28bf6mr1072920566b.51.1740314775174; Sun, 23 Feb 2025 04:46:15 -0800 (PST) MIME-Version: 1.0 References: <54c35fb9-da3a-4754-ab8c-46ed0b612465@vondra.me> <684c70d7-180e-461d-9377-600c2db581ba@vondra.me> <74839af6-aadc-4f60-ae77-ae65f94bf607@gmail.com> In-Reply-To: From: Tender Wang Date: Sun, 23 Feb 2025 20:46:03 +0800 X-Gm-Features: AWEUYZnC6K4DL5x6YCWVcJ6P-5nDyIMdYi52wFThy85Q8cvjEKggFa4Xx56mBuw Message-ID: Subject: Re: generic plans and "initial" pruning To: Amit Langote Cc: Alexander Lakhin , Tomas Vondra , Robert Haas , Alvaro Herrera , Andres Freund , Daniel Gustafsson , David Rowley , PostgreSQL Hackers , Thom Brown , Tom Lane Content-Type: multipart/alternative; boundary="000000000000f8c408062ece9b32" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f8c408062ece9b32 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Amit Langote =E4=BA=8E2025=E5=B9=B42=E6=9C=8823= =E6=97=A5=E5=91=A8=E6=97=A5 16:36=E5=86=99=E9=81=93=EF=BC=9A > On Sun, Feb 23, 2025 at 2:03=E2=80=AFAM Tender Wang = wrote: > > Alexander Lakhin =E4=BA=8E2025=E5=B9=B42=E6=9C=88= 22=E6=97=A5=E5=91=A8=E5=85=AD 23:00=E5=86=99=E9=81=93=EF=BC=9A > >> 21.02.2025 05:40, Amit Langote wrote: > >> > >> I pushed the final piece yesterday. > >> > >> > >> Please look at new error, produced by the following script, > >> starting from 525392d57: > >> CREATE TABLE t(id int) PARTITION BY RANGE (id); > >> CREATE INDEX idx on t(id); > >> CREATE TABLE tp_1 PARTITION OF t FOR VALUES FROM (10) TO (20); > >> CREATE TABLE tp_2 PARTITION OF t FOR VALUES FROM (20) TO (30) PARTITIO= N > BY RANGE(id); > >> CREATE TABLE tp_2_1 PARTITION OF tp_2 FOR VALUES FROM (21) to (22); > >> CREATE TABLE tp_2_2 PARTITION OF tp_2 FOR VALUES FROM (22) to (23); > >> CREATE FUNCTION stable_one() RETURNS INT AS $$ BEGIN RETURN 1; END; $$ > LANGUAGE plpgsql STABLE; > >> > >> SELECT min(id) OVER (PARTITION BY id ORDER BY id) FROM t WHERE id >=3D > stable_one(); > >> > >> ERROR: XX000: trying to open a pruned relation > >> LOCATION: ExecGetRangeTableRelation, execUtils.c:830 > >> > >> This issue was discovered with SQLsmith. > > Thanks for the report. > > > The error message was added in commit 525392d57. In this case, the > estate->es_unpruned_relids only includes 1, which is the offset of table = t. > > In register_partpruneinfo(), we collect glob->prunableRelids; in this > case, it contains 2,3,4,5. Then we will do: > > result->unprunableRelids =3D bms_difference(glob->allRelids, > > glob->prunableRelids); > > so the result->unprunableRelids only contains 1. > > > > But tp_2 is also partition table, and its partpruneinfo created by > create_append_plan() is put into the head of global list. > > So we first process it in ExecDoInitialPruning(). Then error reports > because we only contain 1 in estate->es_unpruned_relids. > > Thanks for checking. > > The RT index of tp_2 should appear in PlannedStmt.unprunableRelids, > because it needs to be opened in CreatePartitionPruneState() for > setting up its PartitionPruneInfo. We use ExecGetRangeTableRelation() > to open, which expects the relation to be locked, so the error. > > To ensure tp_2 appears in PlannedStmt.unprunableRelids, we should > prevent make_partitionedrel_pruneinfo() from placing the RT index into > leafpart_rti_map[], as the current condition for inclusion doesn=E2=80=99= t > account for whether the partition is itself partitioned. > > I've come up with the attached. > LGTM. --=20 Thanks, Tender Wang --000000000000f8c408062ece9b32 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


Amit Langote &l= t;amitlangote09@gmail.com>= ; =E4=BA=8E2025=E5=B9=B42=E6=9C=8823=E6=97=A5=E5=91=A8=E6=97=A5 16:36=E5=86= =99=E9=81=93=EF=BC=9A
On Sun, Feb 23, 2025 at 2:03=E2=80=AFAM Tender Wang <tndrwang@gmail.com> wrote= :
> Alexander Lakhin <exclusion@gmail.com> =E4=BA=8E2025=E5=B9=B42=E6=9C=8822=E6= =97=A5=E5=91=A8=E5=85=AD 23:00=E5=86=99=E9=81=93=EF=BC=9A
>> 21.02.2025 05:40, Amit Langote wrote:
>>
>> I pushed the final piece yesterday.
>>
>>
>> Please look at new error, produced by the following script,
>> starting from 525392d57:
>> CREATE TABLE t(id int) PARTITION BY RANGE (id);
>> CREATE INDEX idx on t(id);
>> CREATE TABLE tp_1 PARTITION OF t FOR VALUES FROM (10) TO (20);
>> CREATE TABLE tp_2 PARTITION OF t FOR VALUES FROM (20) TO (30) PART= ITION BY RANGE(id);
>> CREATE TABLE tp_2_1 PARTITION OF tp_2 FOR VALUES FROM (21) to (22)= ;
>> CREATE TABLE tp_2_2 PARTITION OF tp_2 FOR VALUES FROM (22) to (23)= ;
>> CREATE FUNCTION stable_one() RETURNS INT AS $$ BEGIN RETURN 1; END= ; $$ LANGUAGE plpgsql STABLE;
>>
>> SELECT min(id) OVER (PARTITION BY id ORDER BY id) FROM t WHERE id = >=3D stable_one();
>>
>> ERROR:=C2=A0 XX000: trying to open a pruned relation
>> LOCATION:=C2=A0 ExecGetRangeTableRelation, execUtils.c:830
>>
>> This issue was discovered with SQLsmith.

Thanks for the report.

> The error message was added in commit=C2=A0 525392d57. In this case, t= he estate->es_unpruned_relids only includes 1, which is the offset of ta= ble t.
> In register_partpruneinfo(), we collect glob->prunableRelids; in th= is case, it contains 2,3,4,5. Then we will do:
> result->unprunableRelids =3D bms_difference(glob->allRelids,
>=C2=A0 glob->prunableRelids);
> so the result->unprunableRelids only contains 1.
>
> But tp_2 is also partition table, and its partpruneinfo created by cre= ate_append_plan() is put into the head of global list.
> So we first process it in ExecDoInitialPruning().=C2=A0 Then error rep= orts because we only contain 1 in estate->es_unpruned_relids.

Thanks for checking.

The RT index of tp_2 should appear in PlannedStmt.unprunableRelids,
because it needs to be opened in CreatePartitionPruneState() for
setting up its PartitionPruneInfo. We use ExecGetRangeTableRelation()
to open, which expects the relation to be locked, so the error.

To ensure tp_2 appears in PlannedStmt.unprunableRelids, we should
prevent make_partitionedrel_pruneinfo() from placing the RT index into
leafpart_rti_map[], as the current condition for inclusion doesn=E2=80=99t<= br> account for whether the partition is itself partitioned.

I've come up with the attached.

LGT= M.=C2=A0

-= -
Thanks,
Tender Wang
--000000000000f8c408062ece9b32--