Cross-partition UPDATE and foreign table partitions - Mailing list pgsql-hackers

I was wondering why ExecCrossPartitionUpdateForeignKey() has an unused
argument "oldslot" and wanted to suggest its removal. However, before I did,
it occurred to me that callers may want to pass the whole slot when the
partition is a foreign table, i.e. when the "tupleid" argument cannot be
used. (In that case the problem would be that the function implementation is
incomplete.)

However, when checking how cross-partition UPDATE works internally for foreign
tables, I saw surprising behavior. The attached script creates partitioned
table "a" with foreign table partitions "a1" and "a2". If you then run the
following commands

INSERT INTO a VALUES (1), (10);
UPDATE a SET i=11 WHERE i=1;
TABLE a1;

you'll see that the tuples are correctly routed into the partitions, but the
UPDATE is simply executed on the "a1" partition. Instead, I'd expect it to
delete the tuple from "a1" and insert it into "a2". That looks like a bug.

-- 
Antonin Houska
Web: https://www.cybertec-postgresql.com

CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA public;

CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    dbname 'postgres',
    host 'localhost',
    port '5432'
);

CREATE USER MAPPING FOR CURRENT_ROLE SERVER s1;

CREATE TABLE public.a (
    i integer NOT NULL
)
PARTITION BY RANGE (i);


CREATE TABLE public.a1 (
    i integer NOT NULL
);

CREATE FOREIGN TABLE public.a1_loc (
    i integer NOT NULL
)
SERVER s1
OPTIONS (
    table_name 'a1'
);

CREATE TABLE public.a2 (
    i integer NOT NULL
);

CREATE FOREIGN TABLE public.a2_loc (
    i integer NOT NULL
)
SERVER s1
OPTIONS (
    table_name 'a2'
);

ALTER TABLE ONLY public.a ATTACH PARTITION public.a1_loc FOR VALUES FROM (0) TO (10);
ALTER TABLE ONLY public.a ATTACH PARTITION public.a2_loc FOR VALUES FROM (10) TO (20);

ALTER TABLE ONLY public.a1
    ADD CONSTRAINT a1_pkey PRIMARY KEY (i);

ALTER TABLE ONLY public.a2
    ADD CONSTRAINT a2_pkey PRIMARY KEY (i);