From: Manu <manuelreyesbravo@gmail.com>
To: Jakub Wartak <jakub.wartak@enterprisedb.com>
Cc: Ajit Awekar <ajitpostgres@gmail.com>
Cc: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: shihao zhong <zhong950419@gmail.com>
Cc: Nikita Malakhov <hukutoc@gmail.com>
Cc: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
Cc: pgsql-hackers@lists.postgresql.org
Cc: rhaas@postgresql.org
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
Date: Wed, 28 Oct 2026 15:32:44 -0300
Message-ID: <179321236488.520440.320467809486544701@gmail.com> (raw)
In-Reply-To: <CAKZiRmzpABcNuk+ZrhmV3=GtaA2S=C3=vKw_ggXCUwQUv8D4ag@mail.gmail.com>
References: <CAKZiRmzpABcNuk+ZrhmV3=GtaA2S=C3=vKw_ggXCUwQUv8D4ag@mail.gmail.com>
Hi Jakub,
On Wed, Oct 28, 2026 at 10:12 AM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
> postgres=# DELETE FROM fbig WHERE fbig.* IS NULL AND random() <= 1;
> ERROR: attribute 3 of type record has wrong type
Confirmed with V4 on master 126976a8ffc and fcfd2456078, and V3
already had it; master gives no error. A DELETE whose local qual uses
the whole row fails the same way without random() and on a foreign
partition. UPDATE works on a plain foreign table, but on a
foreign partition it fails with "record type has not been registered".
The ForeignScan's fdw_scan_tlist gets two whole-row entries, one typed
fbig and one RECORD: normalize_wholerow_vartype() retypes the qual's
Var (postgres_fdw.c:1515 with V4), so it no longer equal()s the one
from the reltarget and is appended (:1538). setrefs.c matches on
varno/varattno only and takes the first, the fbig one. The comment at
:1507 assumes core's RECORD Var is already there, which is true only
for UPDATE or with row DELETE triggers; with an AFTER DELETE row
trigger the RECORD entry comes first and the DELETE on the plain
foreign table works (on the partition it then fails like UPDATE).
Ajit, I'll set the entry to Ready for Committer once v5 covers this
and passes the eight cases plus the attached case_i_whole_row.sql.txt.
Regards,
Manu
Attachments:case_i_whole_row.sql.txt (text/plain, 3.6K) download
-- Case i: whole-row reference to the foreign target in a non-pushed
-- UPDATE/DELETE (Jakub Wartak, 2026-10-28). random() <= 1 keeps the
-- WHERE clause local, so the scan must return the whole row.
-- Needs postgres_fdw and a server "loopback" pointing at this database,
-- set up as in contrib/postgres_fdw/sql/postgres_fdw.sql.
CREATE TABLE big (id int primary key, a int, b text);
INSERT INTO big SELECT s, s, 'x'||s FROM generate_series(1,10) s;
ANALYZE big;
CREATE FOREIGN TABLE fbig (id int, a int, b text)
SERVER loopback OPTIONS (table_name 'big');
-- i1: Jakub's statement
EXPLAIN (VERBOSE, COSTS OFF)
DELETE FROM fbig WHERE fbig.* IS NULL AND random() <= 1;
DELETE FROM fbig WHERE fbig.* IS NULL AND random() <= 1;
-- i2: same with ROW(...) instead of fbig.*
DELETE FROM fbig WHERE ROW(id, a, b) IS NULL AND random() <= 1;
-- i3: UPDATE
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE fbig SET a = -a WHERE fbig.* IS NULL AND random() <= 1;
UPDATE fbig SET a = -a WHERE fbig.* IS NULL AND random() <= 1;
-- i4: rows that do match: one row must change, no other
-- (read through the FDW: the loopback's remote transaction is not
-- visible to a local SELECT on big until commit)
BEGIN;
DELETE FROM fbig WHERE fbig.* IS NOT NULL AND id = 2 AND random() <= 1;
SELECT * FROM fbig WHERE id <= 3 ORDER BY id;
ROLLBACK;
BEGIN;
UPDATE fbig SET a = -a WHERE fbig IS NOT NULL AND id = 3 AND random() <= 1;
SELECT * FROM fbig WHERE id <= 3 ORDER BY id;
ROLLBACK;
-- i5: whole row only in RETURNING
BEGIN;
UPDATE fbig SET a = -a WHERE id = 4 AND random() <= 1 RETURNING fbig;
DELETE FROM fbig WHERE id = 5 AND random() <= 1 RETURNING fbig;
ROLLBACK;
-- i6: no random(): whole row in WHERE
EXPLAIN (VERBOSE, COSTS OFF)
DELETE FROM fbig WHERE fbig.* IS NULL;
DELETE FROM fbig WHERE fbig.* IS NULL;
-- i7: foreign table as a partition
CREATE TABLE r1 (id int primary key, a int, b text);
CREATE TABLE r2 (id int primary key, a int, b text);
INSERT INTO r1 SELECT s, s, 'x'||s FROM generate_series(1,5) s;
INSERT INTO r2 SELECT s, s, 'x'||s FROM generate_series(6,10) s;
CREATE TABLE pt (id int, a int, b text) PARTITION BY RANGE (id);
CREATE FOREIGN TABLE pt1 PARTITION OF pt FOR VALUES FROM (1) TO (6)
SERVER loopback OPTIONS (table_name 'r1');
CREATE FOREIGN TABLE pt2 PARTITION OF pt FOR VALUES FROM (6) TO (11)
SERVER loopback OPTIONS (table_name 'r2');
ANALYZE pt;
EXPLAIN (VERBOSE, COSTS OFF)
DELETE FROM pt WHERE pt.* IS NULL AND random() <= 1;
DELETE FROM pt WHERE pt.* IS NULL AND random() <= 1;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE pt SET a = -a WHERE pt.* IS NULL AND random() <= 1;
UPDATE pt SET a = -a WHERE pt.* IS NULL AND random() <= 1;
BEGIN;
DELETE FROM pt WHERE pt IS NOT NULL AND id IN (2, 7) AND random() <= 1;
SELECT * FROM pt WHERE id IN (2, 3, 7, 8) ORDER BY id;
ROLLBACK;
BEGIN;
UPDATE pt SET a = -a WHERE pt IS NOT NULL AND id IN (3, 8)
AND random() <= 1 RETURNING pt;
SELECT * FROM pt WHERE id IN (2, 3, 7, 8) ORDER BY id;
ROLLBACK;
-- i8: same DELETEs once a row DELETE trigger exists, which makes core
-- add its own RECORD whole-row identity column (as it always does for
-- UPDATE)
CREATE FUNCTION trig_noop() RETURNS trigger LANGUAGE plpgsql
AS $$ BEGIN RETURN OLD; END $$;
CREATE TRIGGER fbig_del AFTER DELETE ON fbig
FOR EACH ROW EXECUTE FUNCTION trig_noop();
CREATE TRIGGER pt1_del AFTER DELETE ON pt1
FOR EACH ROW EXECUTE FUNCTION trig_noop();
CREATE TRIGGER pt2_del AFTER DELETE ON pt2
FOR EACH ROW EXECUTE FUNCTION trig_noop();
BEGIN;
DELETE FROM fbig WHERE fbig.* IS NULL AND random() <= 1;
DELETE FROM fbig WHERE fbig.* IS NOT NULL AND id = 2 AND random() <= 1;
SELECT * FROM fbig WHERE id <= 3 ORDER BY id;
ROLLBACK;
DELETE FROM pt WHERE pt.* IS NULL AND random() <= 1;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: manuelreyesbravo@gmail.com, jakub.wartak@enterprisedb.com, ajitpostgres@gmail.com, etsuro.fujita@gmail.com, michael@paquier.xyz, zhong950419@gmail.com, hukutoc@gmail.com, jgdr@dalibo.com, pgsql-hackers@lists.postgresql.org, rhaas@postgresql.org
Subject: Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table
In-Reply-To: <179321236488.520440.320467809486544701@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox