Re: UPDATE of partition key

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.

below are steps:
postgres=# create table part_upd (a int, b int) partition by range(a);
CREATE TABLE
postgres=# create table part_upd1 partition of part_upd for values from
(minvalue) to (-10);
CREATE TABLE
postgres=# create table part_upd2 partition of part_upd for values from
(-10) to (0);
CREATE TABLE
postgres=# create table part_upd3 partition of part_upd for values from (0)
to (10);
CREATE TABLE
postgres=# create table part_upd4 partition of part_upd for values from
(10) to (maxvalue);
CREATE TABLE
postgres=# insert into part_upd select i,i from generate_series(-30,30,3)i;
INSERT 0 21

*postgres=# select count(*) from part_upd; count ------- 21(1 row)*
postgres=#
postgres=# create table non_part_upd (a int);
CREATE TABLE
postgres=# insert into non_part_upd select i%2 from
generate_series(-30,30,5)i;
INSERT 0 13
postgres=# update part_upd t1 set a = (t2.a+10) from non_part_upd t2 where
t2.a = t1.b;
UPDATE 7

*postgres=# select count(*) from part_upd; count ------- 27(1 row)*
postgres=# select tableoid::regclass,* from part_upd;
tableoid | a | b
-----------+-----+-----
part_upd1 | -30 | -30
part_upd1 | -27 | -27
part_upd1 | -24 | -24
part_upd1 | -21 | -21
part_upd1 | -18 | -18
part_upd1 | -15 | -15
part_upd1 | -12 | -12
part_upd2 | -9 | -9
part_upd2 | -6 | -6
part_upd2 | -3 | -3
part_upd3 | 3 | 3
part_upd3 | 6 | 6
part_upd3 | 9 | 9
part_upd4 | 12 | 12
part_upd4 | 15 | 15
part_upd4 | 18 | 18
part_upd4 | 21 | 21
part_upd4 | 24 | 24
part_upd4 | 27 | 27
part_upd4 | 30 | 30

* part_upd4 | 10 | 0 part_upd4 | 10 | 0 part_upd4 | 10 |
0 part_upd4 | 10 | 0 part_upd4 | 10 | 0 part_upd4 | 10 |
0 part_upd4 | 10 | 0*(27 rows)

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation