Update of partition key on foreign server
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t44625psql -h localhost -U postgresBuilt from patchset v2 (message #2), July 27, 2026 at 04:19 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t44625_2 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t44625_2 && git checkout t44625_2Patchset v2 (message #2) is on t44625_2
Hi,
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.
Thanks,
Ilya Gladyshev
2 авг. 2021 г., в 15:29, Илья Гладышев <i.gladyshev@postgrespro.ru> написал(а):
Hi,
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.
Thanks,
Ilya Gladyshev
I have developed a simple patch to fix this, while I’m not fully satisfied with it, it gets the job done. From what I have read in the docs, partition constraints are currently not checked for foreign tables, because they must be enforced on the remote server anyway (because there might be many other ways to access the data source besides FDW), and thus there’s no point in doing that locally. However, in the case of foreign partition update, checking the constraints locally can be used to allow for routing from remote sources, so I don’t feel like this point is valid in this case.
In message [1]/messages/by-id/5A93F487.4080602@lab.ntt.co.jp </messages/by-id/5A93F487.4080602@lab.ntt.co.jp> there’s also mentioned possibility of existence of triggers on the foreign server, and transforming the update to delete+insert will cause these triggers to be omitted. While it is true, I feel like partition pruning already has a similar effect, as it allows to skip scanning foreign partitions. The only way to both fix the update and have the triggers work, that I came up with, is to use parent partitioned table as a target for the foreign update (FDW request would then be "UPDATE public.players …"), while this is possible, it requires the foreign server to have the same partitioned table, which seems quite restrictive to me. Please let me know if I’m missing something or there is a better way to do it.
Thanks,
Ilya Gladyshev
[1]: /messages/by-id/5A93F487.4080602@lab.ntt.co.jp </messages/by-id/5A93F487.4080602@lab.ntt.co.jp>
On Thu, Aug 26, 2021 at 11:10 PM Ilya Gladyshev
<i.gladyshev@postgrespro.ru> wrote:
I have developed a simple patch to fix this, while I’m not fully satisfied with it, it gets the job done.
Thanks for working on this!
In message [1] there’s also mentioned possibility of existence of triggers on the foreign server, and transforming the update to delete+insert will cause these triggers to be omitted.
Yeah, I still think so.
While it is true, I feel like partition pruning already has a similar effect, as it allows to skip scanning foreign partitions.
I don’t fully understand this part. Could you elaborate a bit more on it?
The only way to both fix the update and have the triggers work, that I came up with, is to use parent partitioned table as a target for the foreign update (FDW request would then be "UPDATE public.players …"), while this is possible, it requires the foreign server to have the same partitioned table, which seems quite restrictive to me.
Yeah, that would be too restrictive.
Best regards,
Etsuro Fujita