Skip to content
Permalink

Comparing changes

Choose two branches to see what’s changed or to start a new pull request. If you need to, you can also or learn more about diff comparisons.

Open a pull request

Create a new pull request by comparing changes across two branches. If you need to, you can also . Learn more about diff comparisons here.
base repository: postgresql-cfbot/postgresql
Failed to load repositories. Confirm that selected base ref is valid, then try again.
Loading
base: cf/5901~1
Choose a base ref
...
head repository: postgresql-cfbot/postgresql
Failed to load repositories. Confirm that selected head ref is valid, then try again.
Loading
compare: cf/5901
Choose a head ref
  • 2 commits
  • 3 files changed
  • 3 contributors

Commits on Oct 4, 2025

  1. Allow DELETE/UPDATE on tables with foreign partitions or inherited fo… 

    …reign tables

Currently, DELETE or UPDATE on a partitioned table with foreign partitions
fail with an error as below, if the FDW does not support the operation:

	`ERROR: cannot delete from foreign table`

This occurs because during executor initialization (ExecInitModifyTable),
PostgreSQL scans all partitions of the target table and checks whether each one
supports the requested operation. If any foreign partition's FDW lacks support
for DELETE or UPDATE, the operation is rejected outright, even if that
partition would not be affected by the query.

A similar issue arises with inherited foreign tables, where DELETE/UPDATEs
targeted on non-foreign tables are also blocked.

As a result, DELETE/UPDATE operations are blocked even when they only target
non-foreign relations. This means the system errors out without considering
whether foreign or inherited foreign tables are actually involved in the
operation. Even if no matching rows exist in a foreign partition, the operation
still fails.

This commit defers the FDW check for foreign partitions and inherited foreign tables from
`ExecInitModifyTable` to `ExecDelete` and `ExecUpdate`. This change ensures
that foreign child tables are checked only when they are actually targeted by the
operation.

However, if a DELETE or UPDATE is issued on the root table and it includes
foreign child tables that do not support the operation, it will still result in
an error. This is intentional because the responsibility for managing data in
foreign tables lies with the user. Only after the user has removed relevant
data from those foreign tables will such operations on the root table
succeed.

** Mini repro for partition table with foreign partition: **
```
CREATE EXTENSION file_fdw;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE TABLE pt (a int, b numeric) PARTITION BY RANGE(a);
CREATE TABLE pt_part1 PARTITION OF pt FOR VALUES FROM (0) TO (10);
INSERT INTO pt SELECT 5, 0.1;
INSERT INTO pt SELECT 6, 0.2;

CREATE FOREIGN TABLE ext (a int, b numeric) SERVER file_server OPTIONS (filename 'path-to-file', format 'csv', delimiter ',');
ALTER TABLE pt ATTACH PARTITION ext FOR VALUES FROM (10) TO (20);
postgres=# SELECT * FROM pt;
 a  |  b
----+-----
  5 | 0.1
  6 | 0.2
 15 | 0.3
 21 | 0.4
(4 rows)
```

** Before Fix: **
```
postgres=# DELETE FROM pt WHERE b = 0.2;
ERROR:  cannot delete from foreign table "ext"
postgres=# DELETE FROM pt;
ERROR:  cannot delete from foreign table "ext"

postgres=# UPDATE pt set b = 0.5 WHERE b = 0.1;
ERROR:  cannot update foreign table "ext"
postgres=# UPDATE pt SET b = 0.5;
ERROR:  cannot update foreign table "ext"
```

** After Fix: **
```
postgres=# DELETE FROM pt WHERE b = 0.2;
DELETE 1
postgres=# DELETE FROM pt;
ERROR:  cannot delete from foreign table "ext"

postgres=# UPDATE pt SET b = 0.5 WHERE b = 0.1;
UPDATE 1
postgres=# UPDATE pt SET b = 0.5;
ERROR:  cannot update foreign table "ext"
```

** Mini repro for table with inherited foreign table: **
```
CREATE TABLE pt (a text, b int);
INSERT INTO pt VALUES ('AAA', 42);
CREATE FOREIGN TABLE ft (a text, b int) server file_server OPTIONS (filename 'path-to-file', format 'csv', delimiter ',');
ALTER FOREIGN TABLE ft INHERIT pt;
SELECT * FROM pt;
  a  | b
-----+----
 AAA | 42
 BBB | 42
(2 rows)
```

** Before Fix: **
```
UPDATE pt SET b = b + 1000 WHERE a = 'AAA';
ERROR:  cannot update foreign table "ft"
DELETE FROM pt WHERE a = 'AAA';
ERROR:  cannot delete from foreign table "ft"
```

** After Fix: **
```
UPDATE pt SET b = b + 1000 WHERE a = 'AAA';
UPDATE 1
DELETE FROM pt WHERE a = 'AAA';
DELETE 1
```

Co-authored-by: Ashwin Agrawal <[email protected]>
    @ShirishaRao @ashwinstar
    2 people authored and Commitfest Bot committed Oct 4, 2025
    Configuration menu
    Copy the full SHA
    91bb1ec View commit details
    Browse the repository at this point in the history

  2. [CF 5901] v2 - Proposal to allow DELETE/UPDATE on partitioned tables … 

    …with unsupported foreign partitions

This branch was automatically generated by a robot using patches from an
email thread registered at:

https://commitfest.postgresql.org/patch/5901

The branch will be overwritten each time a new patch version is posted to
the thread, and also periodically to check for bitrot caused by changes
on the master branch.

Patch(es): https://www.postgresql.org/message-id/CAP3-t08DEZ+isXbWXBuJR9KDgbO=wyCg2po90kDAB62Z4YghZw@mail.gmail.com
Author(s): Shirisha V
    Commitfest Bot committed Oct 4, 2025
    Configuration menu
    Copy the full SHA
    f459283 View commit details
    Browse the repository at this point in the history
Loading