-- 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;