Thread: Update does not move row across foreign partitions in v11
I've set up 2 instances of PostgreSQL 11. On instance A, I created a table with 2 local partitions and 2 partitions on instance B using foreign data wrappers, following https://pgdash.io/blog/postgres-11-sharding.html. Inserting rows into this table works as expected, with rows ending up in the appropriate partition. However, updating those rows only moves them across partitions in some of the situations:
- From local partition to local partition
- From local partition to foreign partition
Rows are not moved
- From foreign partition to local partition
- From foreign partition to foreign partition
Is this the expected behavior? Am I missing something or configured something incorrectly?
Thanks,
Derek
Hi all,
This behavior makes the new data sharding functionality in v11 only marginally useful as you can't shard across database instances.
Considering data sharding appeared to be one of the key improvements in v11, I'm confused - am I misunderstanding the expected functionality?
Thanks!
On Fri, Feb 22, 2019 at 9:44 AM Derek Hans <derek.hans@gmail.com> wrote:
I've set up 2 instances of PostgreSQL 11. On instance A, I created a table with 2 local partitions and 2 partitions on instance B using foreign data wrappers, following https://pgdash.io/blog/postgres-11-sharding.html. Inserting rows into this table works as expected, with rows ending up in the appropriate partition. However, updating those rows only moves them across partitions in some of the situations:
- From local partition to local partition
- From local partition to foreign partition
Rows are not moved
- From foreign partition to local partition
- From foreign partition to foreign partition
Is this the expected behavior? Am I missing something or configured something incorrectly?Thanks,Derek
Derek
On 2019-Feb-22, Derek Hans wrote: > I've set up 2 instances of PostgreSQL 11. On instance A, I created a table > with 2 local partitions and 2 partitions on instance B using foreign data > wrappers, following https://pgdash.io/blog/postgres-11-sharding.html. > Inserting rows into this table works as expected, with rows ending up in > the appropriate partition. However, updating those rows only moves them > across partitions in some of the situations: > > - From local partition to local partition > - From local partition to foreign partition > > Rows are not moved > > - From foreign partition to local partition > - From foreign partition to foreign partition > > Is this the expected behavior? Am I missing something or configured > something incorrectly? Sounds like a bug to me. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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.
On Wed, Feb 27, 2019 at 6:12 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
On 2019-Feb-22, Derek Hans wrote:
> I've set up 2 instances of PostgreSQL 11. On instance A, I created a table
> with 2 local partitions and 2 partitions on instance B using foreign data
> wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
> Inserting rows into this table works as expected, with rows ending up in
> the appropriate partition. However, updating those rows only moves them
> across partitions in some of the situations:
>
> - From local partition to local partition
> - From local partition to foreign partition
>
> Rows are not moved
>
> - From foreign partition to local partition
> - From foreign partition to foreign partition
>
> Is this the expected behavior? Am I missing something or configured
> something incorrectly?
Sounds like a bug to me.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Derek