Re: Cross-partition UPDATE and foreign table partitions

Antonin Houska <ah(at)cybertec(dot)at> wrote:

> 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.

Well, as it usually happens, I found a related information as soon as I had
sent a report. The documentation of CREATE FOREIGN TABLE says:

"However it is not currently possible to move a row from a foreign-table
partition to another partition. An UPDATE that would require doing that will
fail due to the partitioning constraint, assuming that that is properly
enforced by the remote server."

So the remaining question is whether the "oldslot" argument of
ExecCrossPartitionUpdateForeignKey() will be used in the future or should be
removed. Note that the ExecUpdateAct() passes its "slot" variable for it,
which seems to contain the *new* version of the tuple rather than the
old. Some cleanup may be needed.

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