Thread: Documentation and code don't agree about partitioned table UPDATEs
The docs in PG11 and master both state: When an UPDATE causes a row to move from one partition to another, there is a chance that another concurrent UPDATE or DELETE misses this row. 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. Session 2 can silently miss the row if the row is deleted from the partition due to session 1's activity. In such case, session 2's UPDATE or DELETE, being unaware of the row movement thinks that the row has just been deleted and concludes that there is nothing to be done for this row. 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. Which was true when it was added by Robert in 2f178441044. However, f16241bef7c then added code to cause serialization failures when the update/delete process encountered a moved row. This seems to work, going by: CREATE TABLE listp (a INT, b INT) PARTITION BY LIST (a); CREATE TABLE listp1 PARTITION OF listp FOR VALUES IN(1); CREATE TABLE listp2 PARTITION OF listp FOR VALUES IN(2); INSERT INTO listp VALUES (1, 0); -- Session 1 BEGIN; SELECT * FROM listp WHERE a=1 FOR UPDATE; -- Session 2 BEGIN; SELECT * FROM listp WHERE b = 0 FOR UPDATE; -- Session 1 UPDATE listp SET a = 2 WHERE a = 1; COMMIT; -- Session 2 ERROR: tuple to be locked was already moved to another partition due to concurrent update So it appears that the documents need to be updated. I've attached a patch which is my attempt at fixing. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Attachment
On Tue, Feb 5, 2019 at 2:14 PM David Rowley <david.rowley@2ndquadrant.com> wrote: > > The docs in PG11 and master both state: > > When an UPDATE causes a row to move from one partition to another, > there is a chance that another concurrent UPDATE or DELETE misses this > row. 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. Session 2 can > silently miss the row if the row is deleted from the partition due to > session 1's activity. In such case, session 2's UPDATE or DELETE, > being unaware of the row movement thinks that the row has just been > deleted and concludes that there is nothing to be done for this row. > 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. > > > Which was true when it was added by Robert in 2f178441044. However, > f16241bef7c then added code to cause serialization failures when the > update/delete process encountered a moved row. > I agree that the docs need to be updated and this patch should be backpatched as well. However, I think the older wording was more descriptive and clear, so I have modified your patch a bit to retain part of old wording, see the result as attached. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Attachment
On Wed, 6 Feb 2019 at 16:20, Amit Kapila <amit.kapila16@gmail.com> wrote: > I agree that the docs need to be updated and this patch should be > backpatched as well. However, I think the older wording was more > descriptive and clear, so I have modified your patch a bit to retain > part of old wording, see the result as attached. I have to admit, I was quite fond of the original text, at least when it was true. Your alteration of it seems pretty good to me too. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Feb 6, 2019 at 4:57 PM David Rowley <david.rowley@2ndquadrant.com> wrote: > > On Wed, 6 Feb 2019 at 16:20, Amit Kapila <amit.kapila16@gmail.com> wrote: > > I agree that the docs need to be updated and this patch should be > > backpatched as well. However, I think the older wording was more > > descriptive and clear, so I have modified your patch a bit to retain > > part of old wording, see the result as attached. > > I have to admit, I was quite fond of the original text, at least when > it was true. Your alteration of it seems pretty good to me too. > Thanks, pushed! -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
On 2019-02-07 09:16:09 +0530, Amit Kapila wrote: > On Wed, Feb 6, 2019 at 4:57 PM David Rowley > <david.rowley@2ndquadrant.com> wrote: > > > > On Wed, 6 Feb 2019 at 16:20, Amit Kapila <amit.kapila16@gmail.com> wrote: > > > I agree that the docs need to be updated and this patch should be > > > backpatched as well. However, I think the older wording was more > > > descriptive and clear, so I have modified your patch a bit to retain > > > part of old wording, see the result as attached. > > > > I have to admit, I was quite fond of the original text, at least when > > it was true. Your alteration of it seems pretty good to me too. > > > > Thanks, pushed! Thanks David and Amit!