Re: UPDATE of partition key

On Tue, Jul 25, 2017 at 3:54 PM, Amit Khandekar <amitdkhan(dot)pg(at)gmail(dot)com>
wrote:

> On 25 July 2017 at 15:02, Rajkumar Raghuwanshi
> <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
> > On Mon, Jul 24, 2017 at 11:23 AM, Amit Khandekar <amitdkhan(dot)pg(at)gmail(dot)com
> >
> > wrote:
> >>
> >>
> >> Attached update-partition-key_v13.patch now contains this
> >> make_resultrels_ordered.patch changes.
> >>
> >
> > I have applied attach patch and got below observation.
> >
> > Observation : if join producing multiple output rows for a given row to
> be
> > modified. I am seeing here it is updating a row and also inserting rows
> in
> > target table. hence after update total count of table got incremented.
>
> Thanks for catching this Rajkumar.
>
> So after the row to be updated is already moved to another partition,
> when the next join output row corresponds to the same row which is
> moved, that row is now deleted, so ExecDelete()=>heap_delete() gets
> HeapTupleSelfUpdated, and this is not handled. So even when
> ExecDelete() finds that the row is already deleted, we still call
> ExecInsert(), so a new row is inserted. In ExecDelete(), we should
> indicate that the row is already deleted. In the existing patch, there
> is a parameter concurrenty_deleted for ExecDelete() which indicates
> that the row is concurrently deleted. I think we can make this
> parameter for both of these purposes so as to avoid ExecInsert() for
> both these scenarios. Will work on a patch.
>

Thanks Amit.

Got one more observation : update... returning is not working with whole
row reference. please take a look.

postgres=# create table part (a int, b int) partition by range(a);
CREATE TABLE
postgres=# create table part_p1 partition of part for values from
(minvalue) to (0);
CREATE TABLE
postgres=# create table part_p2 partition of part for values from (0) to
(maxvalue);
CREATE TABLE
postgres=# insert into part values (10,1);
INSERT 0 1
postgres=# insert into part values (20,2);
INSERT 0 1
postgres=# update part t1 set a = b returning t1;
ERROR: unexpected whole-row reference found in partition key