Re: Update of partition key on foreign server

> 2 авг. 2021 г., в 15:29, Илья Гладышев <i(dot)gladyshev(at)postgrespro(dot)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] 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] https://www.postgresql.org/message-id/5A93F487.4080602%40lab.ntt.co.jp <https://www.postgresql.org/message-id/5A93F487.4080602@lab.ntt.co.jp>