Обсуждение: Update of partition key on foreign server
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.
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
Вложения
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 theupdate 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 tableas a target for the foreign update (FDW request would then be "UPDATE public.players …"), while this is possible, itrequires 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