Documentation and code don't agree about partitioned table UPDATEs

From: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Andres Freund <andres(at)anarazel(dot)de>
Subject: Documentation and code don't agree about partitioned table UPDATEs
Date: 2019-02-05 08:44:33
Message-ID: CAKJS1f-iVhGD4-givQWpSROaYvO3c730W8yoRMTF9Gc3craY3w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 Content-Type Size
partition_update_doc_fix.patch application/octet-stream 2.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Antonin Houska 2019-02-05 09:40:15 Re: [HACKERS] WIP: Aggregation push-down
Previous Message Michael Paquier 2019-02-05 07:26:18 Re: Tighten up a few overly lax regexes in pg_dump's tap tests