Re: Update does not move row across foreign partitions in v11

On Tue, 5 Mar 2019 at 03:01, Derek Hans <derek(dot)hans(at)gmail(dot)com> wrote:
> Based on a reply to reporting this as a bug, moving rows out of foreign partitions is not yet implemented so this is behaving as expected. There's a mention of this limitation in the Notes section of the Update docs.

(Moving this discussion to -Hackers)

In [1], Derek reports that once a row is inserted into a foreign
partition that an UPDATE does not correctly route it back out into the
correct partition.

I didn't really follow the foreign partition code when it went in, but
do recall being involved in the documentation about the limitations of
partitioned tables in table 5.10.2.3 in [2]. Unfortunately, table
5.10.2.3 does not seem to mention this limitation at all. As Derek
mentions, there is a brief mention in [3] in the form of:

"Currently, rows cannot be moved from a partition that is a foreign
table to some other partition, but they can be moved into a foreign
table if the foreign data wrapper supports it."

I don't quite understand what a "foreign table to some other
partition" is meant to mean. Partitions don't have foreign tables,
they can only be one themselves.

I've tried to put all this right again in the attached. However, I was
a bit unsure of what "but they can be moved into a foreign table if
the foreign data wrapper supports it." is referring to. Copying Robert
and Etsuro as this was all added in 3d956d9562aa. Hopefully, they can
confirm what is meant by this.

[1] https://www.postgresql.org/message-id/CAGrP7a3Xc1Qy_B2WJcgAD8uQTS_NDcJn06O5mtS_Ne1nYhBsyw@mail.gmail.com
[2] https://www.postgresql.org/docs/devel/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-LIMITATIONS
[3] https://www.postgresql.org/docs/devel/sql-update.html

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services