Thread

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

Tender Wang <tndrwang@gmail.com> — 2025-10-30T04:41:01Z

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