Hi Jakub, On Wed, Oct 28, 2026 at 10:12 AM Jakub Wartak <[email protected]> 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
-- 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;
