On Thu, Mar 8, 2018 at 4:55 PM, Amit Khandekar <amitdkhan...@gmail.com> wrote: > (Mail subject changed; original thread : [1]) > > On 8 March 2018 at 11:57, Amit Khandekar <amitdkhan...@gmail.com> wrote: >>> Clearly, ExecUpdate() while moving rows between partitions is missing out on >>> re-constructing the to-be-updated tuple, based on the latest tuple in the >>> update chain. Instead, it's simply deleting the latest tuple and inserting a >>> new tuple in the new partition based on the old tuple. That's simply wrong. >> >> You are right. This need to be fixed. This is a different issue than >> the particular one that is being worked upon in this thread, and both >> these issues have different fixes. >> > > As discussed above, there is a concurrency bug where during UPDATE of > a partition-key, another transaction T2 updates the same row. >
We have one more behavior related to concurrency that would be impacted due to row movement. Basically, now Select .. for Key Share will block the Update that routes the tuple to a different partition even if the update doesn't update the key (primary/unique key) column. Example, create table foo (a int2, b text) partition by list (a); create table foo1 partition of foo for values IN (1); create table foo2 partition of foo for values IN (2); insert into foo values(1, 'Initial record'); Session-1 --------------- begin; select * from foo where a=1 for key share; Session-2 --------------- begin; update foo set a=1, b= b|| '-> update 1' where a=1; update foo set a=2, b= b|| '-> update 2' where a=1; --this will block You can see when the update moves the row to a different partition, it will block as internally it performs delete. I think as we have already documented that such an update is internally Delete+Insert, this behavior is expected, but I think it is better if we update the docs (Row-level locks section) as well. In particular, I am talking about below text in Row-level locks section [1]. FOR KEY SHARE Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE, [1] - https://www.postgresql.org/docs/devel/static/explicit-locking.html#LOCKING-ROWS -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com