Re: [HACKERS] UPDATE of partition key - Mailing list pgsql-hackers

From Rajkumar Raghuwanshi
Subject Re: [HACKERS] UPDATE of partition key
Date
Msg-id CAKcux6kH9MrU-SjK4qxYKHFoREv4j_hrpGo21OKdNqODsEUXiw@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] UPDATE of partition key  (Amit Khandekar <amitdkhan.pg@gmail.com>)
Responses Re: [HACKERS] UPDATE of partition key
List pgsql-hackers
On Mon, Jul 24, 2017 at 11:23 AM, Amit Khandekar <amitdkhan.pg@gmail.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

pgsql-hackers by date:

Previous
From: Kyotaro HORIGUCHI
Date:
Subject: Re: [HACKERS] asynchronous execution
Next
From: Petr Jelinek
Date:
Subject: Re: [HACKERS] More race conditions in logical replication