I am currently looking into a partition constraint violation that occurs on update of a partition key on a foreign server. To reproduce it you can run:
On server 1 using port 5432: create extension postgres_fdw; create table players (id integer, name text) partition by list(name); create table players_0 partition of players for values in ('name1'); create server neighbor foreign data wrapper postgres_fdw options (host 'localhost', port '5433', dbname 'postgres'); create foreign table players_1 partition of players for values in ('name2') server neighbor ; create user mapping for current_user server neighbor;
On server 2 using port 5433: create extension postgres_fdw; create server neighbor foreign data wrapper postgres_fdw options (host 'localhost', port '5432', dbname 'postgres'); create table players (id integer, name text) partition by list(name); create table players_1 partition of players for values in ('name2'); create user mapping for current_user server neighbor; create foreign table players_0 partition of players for values in ('name1') server neighbor;
insert into players values (1, 'name1'); update players set name='name2' where name='name1';
ERROR: new row for relation "players_0" violates partition constraint DETAIL: Failing row contains (1, name2). CONTEXT: remote SQL command: UPDATE public.players_0 SET name = 'name2'::text WHERE ((name = 'name1'::text))
From what I have read on the mailing list, I understand that this is a known problem, but I haven't found any thread discussing it in particular. Is this something that needs fixing? If it is, I want to try to do it, but I’m wondering if there are any known caveats and looking for any tips on how to implement it.
My understanding is that this should be implemented in a similar way to how the row is routed from a local partition in ExecCrossPartitionUpdate, so the update should be replaced with a foreign delete + local/foreign insert. In addition, a direct update should be forbidden when the query modifies the partition key. I’m probably missing a lot of details (feel free to point out), but is the general idea correct? I will be grateful for any feedback.
