Re: UPDATE of partition key

On Fri, May 12, 2017 at 9:27 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Thu, May 11, 2017 at 5:45 PM, Amit Khandekar <amitdkhan(dot)pg(at)gmail(dot)com> wrote:
>> On 11 May 2017 at 17:24, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>> Few comments:
>>> 1.
>>> Operating directly on partition doesn't allow update to move row.
>>> Refer below example:
>>> create table t1(c1 int) partition by range(c1);
>>> create table t1_part_1 partition of t1 for values from (1) to (100);
>>> create table t1_part_2 partition of t1 for values from (100) to (200);
>>> insert into t1 values(generate_series(1,11));
>>> insert into t1 values(generate_series(110,120));
>>>
>>> postgres=# update t1_part_1 set c1=122 where c1=11;
>>> ERROR: new row for relation "t1_part_1" violates partition constraint
>>> DETAIL: Failing row contains (122).
>>
>> Yes, as Robert said, this is expected behaviour. We move the row only
>> within the partition subtree that has the update table as its root. In
>> this case, it's the leaf partition.
>>
>
> Okay, but what is the technical reason behind it? Is it because the
> current design doesn't support it or is it because of something very
> fundamental to partitions?
>

One plausible theory is that as Select's on partitions just returns
the rows of that partition, the update should also behave in same way.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com