Re: BUG #19099: Conditional DELETE from partitioned table with non-updatable partition raises internal error

On Thu, 30 Oct 2025 at 09:41, Tender Wang <tndrwang@gmail.com> wrote:
>
>
>
> jian he <jian.universality@gmail.com> 于2025年10月30日周四 12:07写道：
>>
>> On Thu, Oct 30, 2025 at 9:02 AM PG Bug reporting form
>> <noreply@postgresql.org> 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 = '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 = 'on', EXPLAIN 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_fdw.
>
>
> After applying your patch,  I got a different output if I enable verbose in EXPLAIN:
> postgres=# EXPLAIN verbose DELETE FROM pt WHERE false;
>                       QUERY PLAN
> -------------------------------------------------------
>  Delete on public.pt  (cost=0.00..0.00 rows=0 width=0)
>    ->  Result  (cost=0.00..0.00 rows=0 width=0)
>          Output: ctid
>          Replaces: Scan on pt
>          One-Time Filter: false
> (5 rows)
>
> postgres=# set enable_partition_pruning = 'off';
> SET
> postgres=# EXPLAIN verbose DELETE FROM pt WHERE false;
>                       QUERY PLAN
> -------------------------------------------------------
>  Delete on public.pt  (cost=0.00..0.00 rows=0 width=0)
>    ->  Result  (cost=0.00..0.00 rows=0 width=0)
>          Output: NULL::oid, NULL::tid
>          Replaces: Scan on pt
>          One-Time Filter: false
> (5 rows)
>
>  Output: ctid (enable_partition_pruning = on)
> vs
> Output: NULL::oid, NULL::tid(enable_partition_pruning = off)
>
> I try add childrte->relkind != RELKIND_PARTITIONED_TABLE && childrte->relkind != 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?
> --
> Thanks,
> Tender Wang


Hi!
Jian's fix WFM, I confirm 'EXPLAIN DELETE FROM pt WHERE false' now
works. Should we add this test case to the regression suite of
file_fdw?

But I also wonder if Jian's fix fixed the right thing. Should we
instead fail in the planning phase with a more user-friendly error
message? This will be a regression though, because 'DELETE FROM
file_fdw_table WHERE false' will no longer work...

As for EXPLAIN VERBOSE output, are they both confusing? Both for
enable_partition_pruning=on and enable_partition_pruning=off? I mean,
file_fdw does not have semantics of neither ctid nor tid?


--
Best regards,
Kirill Reshke