Accidentally discovered a situation
Postgresql create a hash partitioning method of partition table
Performs the update statement has specified partition field values, execution plan according to scan all partitions, normal should only sweep the corresponding that a partition to be ah

The CREATE TABLE public. Wx_friends_new (
Friends_id int4 NOT NULL,
Merchant_id int4 NOT NULL,
Personal_wechat_id int4 NOT NULL,
Update_user varchar (512) NOT NULL
) partition by hash (merchant_id);
50 - to create a partition
The create table wx_friends_01 partition of wx_friends_new for values with (modulus of 50, remainder 0);
The create table wx_friends_02 partition of wx_friends_new for values with (modulus of 50, the remainder 1);
The create table wx_friends_03 partition of wx_friends_new for values with (modulus of 50, remainder 2);
The create table wx_friends_04 partition of wx_friends_new for values with (modulus of 50, remainder 3);
.

- after filling data, perform
The update wx_friends_new set
Update_user='FD_191028192115615983_877fdd689a13da43_112_100010'
WHERE merchant_id=100010 and wechat_id='wxid_sb13qm5ygakt22'
Execute the plan as shown in figure:


I don't know whether Postgresql bugs or didn't get to somewhere?

CodePudding user response:

I also encountered this problem, and check the along while, see website
this sentence"In the case of a partitioned table, updating a row took it to no longer cause the satisfy the partition the constraint of the containing partition. In that case, if there is some other partition In the partition tree for which this row satisfies its partition the constraint, then the row is version to that partition. If there is no to partition, an error will occur. Behind the scenes, the row movement is later a DELETE and INSERT operation.

There is a possibility that a concurrent UPDATE or DELETE on the row being moved will get a serialization failure error. Suppose session 1 is performing an UPDATE on a partition key, and meanwhile a concurrent session 2 for which this row is visible performs an UPDATE or DELETE operation on this row. In such case, session 2's UPDATE or DELETE will detect the row movement and raise a serialization failure error (which always returns with an SQLSTATE code '40001'). Applications may wish to retry the transaction if this occurs. In the usual case where the table is not partitioned, or where there is no row movement, session 2 would have identified the newly updated row and carried out the UPDATE/DELETE on this new row version."

But because there is no update the partitioning column, so I feel this sentence is futile,, may be to do optimization,,
I solve the problem of handling all the update operation change to upsert (insert on conflict) this, and are for reference only

CodePudding user response:

Then top up, don't sink,

CodePudding user response:

Scanning the entire table, because have updated the partitioning key, because can't confirm before, during and after the update will update record partition is changed, but actually perform, should the consumption of resources on each partition is different, you can use the explain analyze confirmation?