Обсуждение: 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

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

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!