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 1vEKTO-00AMGA-UM for pgsql-bugs@arkaria.postgresql.org; Thu, 30 Oct 2025 04:41:18 +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 1vEKTN-0060a7-RV for pgsql-bugs@arkaria.postgresql.org; Thu, 30 Oct 2025 04:41:16 +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 1vEKTN-0060Zy-I7 for pgsql-bugs@lists.postgresql.org; Thu, 30 Oct 2025 04:41:16 +0000 Received: from mail-ed1-x530.google.com ([2a00:1450:4864:20::530]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vEKTK-0050Rd-0v for pgsql-bugs@lists.postgresql.org; Thu, 30 Oct 2025 04:41:16 +0000 Received: by mail-ed1-x530.google.com with SMTP id 4fb4d7f45d1cf-63c09141cabso864777a12.0 for ; Wed, 29 Oct 2025 21:41:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761799273; x=1762404073; darn=lists.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=Ew9axHOMu3fssQUiM2rdLUpM2j0TBe5WXULt0zdfkgk=; b=ex084M6UdEgDfhHqNRhYlfS6aVprFgcB+xQF3bICV0pBjRUy9lMAanm7u6R4MYqVlv P9oNL2eQaHqO9JFzRZ9jDekytVed8e/gEoPYmNjHoXcXHvQz28EonsEF1Ut6ndf5FN7F bRfdMq5yIbHkUijtidYaGGeW6M5p0H4PiNU/3FWH4q/BfAdL7SSgM+uXcCZsZQtPwUb5 ca4QXANQ94/0kwXRsGHU7pfcOOBQpxfZRJC/P7CSvBuotPlZao9gayNsvkkytAlhE2Kx gVNZimnyntOxBRDHDv8eQnuwPvAlrz5j1NI8hH9GkYwZ33AwSkAe00DrROjQ2UYTJCap ePOg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761799273; x=1762404073; 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=Ew9axHOMu3fssQUiM2rdLUpM2j0TBe5WXULt0zdfkgk=; b=bf3slK11uY3DW46Co1YvDLlA5dcBWY7N7yg9aH4OVf6Yd7vSVqGsfiCP0Cbotoe+uE kbPvKxKh7PKuBAEt1LJ4kXvQV7i1+Y2UFGkjcUKSOStZ8wBl8CiFYxL6I7NIjV4ah/cT /mvHJgI77fW8ipGJONHPs0eFZRxO4LTA0WoIatGyOOPTtMEu4dL9tEuoiHTZhAUPfB1Q 9DOPdCtiEsi7SND3gpjuwQS9ugbTz8NsaeU1eprG8Ry/ikn014i/xtfPKF3Rje+uCjMU e0V1NnGBLqSL/KHeyNq+Mc5PWXf+Y5uwlyeU4ivC6GUzf73j6zt726bgkMa1YZDcTeWd iKXA== X-Forwarded-Encrypted: i=1; AJvYcCXPXd3UoCZuQJEFJRQH1DSwKi9ClomVBRNXjDEIVjM/W7uLHlAYBq9Nqxt+F2I83DnOXIBH3R0RlUV2@lists.postgresql.org X-Gm-Message-State: AOJu0YwJI0JJz29lBTUPmGZG5SnR4qAKRJQ0t1XB6dtRq0vXnM0vE9OB mqxZgsnsR0rBeoeqhu876FroC9oDCVZlyHtCNSwmTAGUCyagcHAq45Y/cTlTjEM/E+ljzXlkDAs 90c6UzqQ+KeS3up4fXvGQmwbTvqUpm0g= X-Gm-Gg: ASbGncsAzO5YbARG9xZlUwA6oPHbwhEm5MgMPvWGWLbvUK+Cz/oeHFuzpM/n2BVTFBC gd0FWTNO3GGMIERQ9TbsPSXZgILUHvIg+C+qhAk0zFbXtkhGEB9QRpaqd8eARoZ2PS+WhdgnHGw ctgbKVFfd95GkojWxgJumExNb76nVraj1yeU4dLfVzJIcj8oD5UH00JRC0Abv9snnnUNivimZ0E l79U3Nb0BfMHqY29l95EnNI01DrrGNAUHLJ9hVMcLLYYy7wYkmCjt/qk/B4 X-Google-Smtp-Source: AGHT+IGS4K7U1qo7M58zgDMncRKIWjcBOiBDNWRFLAPjr3DxfUy4DfqeIjEnk7HP8i0XlPgd4+uNRfpxduyShXZs8cM= X-Received: by 2002:a05:6402:848:b0:63c:ea6:5608 with SMTP id 4fb4d7f45d1cf-64044375735mr3666948a12.36.1761799273066; Wed, 29 Oct 2025 21:41:13 -0700 (PDT) MIME-Version: 1.0 References: <19099-e05dcfa022fe553d@postgresql.org> In-Reply-To: From: Tender Wang Date: Thu, 30 Oct 2025 12:41:01 +0800 X-Gm-Features: AWmQ_bksuW39Gp1cYiXUabBBHkFmj-n5u1tes-WWXns2ORC5fW2ZExgVTmxHtYo Message-ID: Subject: Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error To: jian he Cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org, Tom Lane , Amit Langote Content-Type: multipart/alternative; boundary="000000000000d63219064258db08" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d63219064258db08 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable jian he =E4=BA=8E2025=E5=B9=B410=E6=9C=8830= =E6=97=A5=E5=91=A8=E5=9B=9B 12:07=E5=86=99=E9=81=93=EF=BC=9A > On Thu, Oct 30, 2025 at 9:02=E2=80=AFAM PG Bug reporting form > wrote: > > > > The following bug has been logged on the website: > > > > Bug reference: 19099 > > Logged by: Alexander Lakhin > > Email address: exclusion@gmail.com > > PostgreSQL version: 18.0 > > Operating system: Ubuntu 24.04 > > Description: > > > > The following script: > > CREATE EXTENSION file_fdw; > > CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; > > CREATE TABLE pt (a int, b text) partition by list (a); > > CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER > file_server > > OPTIONS (format 'csv', filename '/tmp/1.csv'); > > SET enable_partition_pruning =3D 'off'; > > EXPLAIN DELETE FROM pt WHERE false; > > > > raises: > > ERROR: XX000: could not find junk ctid column > > LOCATION: ExecInitModifyTable, nodeModifyTable.c:4867 > > (Discovered with SQLsmith.) > > > > Reproduced starting from 86dc9005. > > > > On 86dc9005~1 or with enable_partition_pruning =3D 'on', EXPLAIN output= s > the > > query plan and "DELETE FROM pt WHERE false;" completes with no error. > > > > we can add a postgresAddForeignUpdateTargets(postgres_fdw) equivalent > function for file_fdw even though we do not support UPDATE/DELETE in > file_fdw. > After applying your patch, I got a different output if I enable verbose in EXPLAIN: postgres=3D# EXPLAIN verbose DELETE FROM pt WHERE false; QUERY PLAN ------------------------------------------------------- Delete on public.pt (cost=3D0.00..0.00 rows=3D0 width=3D0) -> Result (cost=3D0.00..0.00 rows=3D0 width=3D0) Output: ctid Replaces: Scan on pt One-Time Filter: false (5 rows) postgres=3D# set enable_partition_pruning =3D 'off'; SET postgres=3D# EXPLAIN verbose DELETE FROM pt WHERE false; QUERY PLAN ------------------------------------------------------- Delete on public.pt (cost=3D0.00..0.00 rows=3D0 width=3D0) -> Result (cost=3D0.00..0.00 rows=3D0 width=3D0) Output: NULL::oid, NULL::tid Replaces: Scan on pt One-Time Filter: false (5 rows) Output: ctid (enable_partition_pruning =3D on) vs Output: NULL::oid, NULL::tid(enable_partition_pruning =3D off) I try add childrte->relkind !=3D RELKIND_PARTITIONED_TABLE && childrte->relkind !=3D RELKIND_FOREIGN_TABLE) to avoid adding "tableoid" for foreign table in expand_single_inheritance_child(). It works, but the file_fdw regression test failed. I added Tom and Amit to the cc list. Any thoughts? --=20 Thanks, Tender Wang --000000000000d63219064258db08 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


jian he <jian.universality@gmail.com= > =E4=BA=8E2025=E5=B9=B410=E6=9C=8830=E6=97=A5=E5=91=A8=E5=9B=9B 12:07= =E5=86=99=E9=81=93=EF=BC=9A
On Thu, Oct 30, 2025 at 9:02=E2=80=AFAM PG Bug reporting form <noreply@pos= tgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:=C2=A0 =C2=A0 =C2=A0 19099
> Logged by:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Alexander Lakhin
> Email address:=C2=A0 =C2=A0 =C2=A0 exclusion@gmail.com
> PostgreSQL version: 18.0
> Operating system:=C2=A0 =C2=A0Ubuntu 24.04
> Description:
>
> The following script:
> CREATE EXTENSION file_fdw;
> CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
> CREATE TABLE pt (a int, b text) partition by list (a);
> CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_= server
> OPTIONS (format 'csv', filename '/tmp/1.csv');
> SET enable_partition_pruning =3D 'off';
> EXPLAIN DELETE FROM pt WHERE false;
>
> raises:
> ERROR:=C2=A0 XX000: could not find junk ctid column
> LOCATION:=C2=A0 ExecInitModifyTable, nodeModifyTable.c:4867
> (Discovered with SQLsmith.)
>
> Reproduced starting from 86dc9005.
>
> On 86dc9005~1 or with enable_partition_pruning =3D 'on', EXPLA= IN outputs the
> query plan and "DELETE FROM pt WHERE false;" completes with = no error.
>

we can add a postgresAddForeignUpdateTargets(postgres_fdw) equivalent
function for file_fdw even though we do not support UPDATE/DELETE in file_f= dw.

After applying your pa= tch,=C2=A0 I got a different output if I enable verbose in EXPLAIN:
postgres=3D# EXPLAIN verbose DELETE FROM pt WHERE false;
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PL= AN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0
-------------------------------------------------------
=C2= =A0Delete on public.pt =C2=A0(cost=3D0.00.= .0.00 rows=3D0 width=3D0)
=C2=A0 =C2=A0-> =C2=A0Result =C2=A0(cost=3D= 0.00..0.00 rows=3D0 width=3D0)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output:= ctid
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Replaces: Scan on pt
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0One-Time Filter: false
(5 rows)

postgr= es=3D# set enable_partition_pruning =3D 'off';
SET
postgres= =3D# EXPLAIN verbose DELETE FROM pt WHERE false;
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
-= ------------------------------------------------------
=C2=A0Delete on <= a href=3D"http://public.pt">public.pt =C2=A0(cost=3D0.00..0.00 rows=3D0= width=3D0)
=C2=A0 =C2=A0-> =C2=A0Result =C2=A0(cost=3D0.00..0.00 row= s=3D0 width=3D0)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: NULL::oid, NU= LL::tid
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Replaces: Scan on pt
=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0One-Time Filter: false
(5 rows)
=C2=A0Output: ctid (enable_partition_pruning =3D on)
vs=C2=A0
Output: NULL::oid, NULL::tid(enable_partition_pruning = =3D off)

I try add=C2=A0childrte->relkind !=3D = RELKIND_PARTITIONED_TABLE &&=C2=A0childrte->relkind !=3D=C2=A0RE= LKIND_FOREIGN_TABLE)
to avoid adding "tableoid" for for= eign table in=C2=A0expand_single_inheritance_child().
It works, b= ut the file_fdw regression test failed.

I added To= m and Amit to the cc list.
Any thoughts?
--
Thanks,
Tender Wang
--000000000000d63219064258db08--