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
Attachments:
partition_update_doc_fix.patchapplication/octet-stream; name=partition_update_doc_fix.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 85e4358988..beb44b9b9d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3858,21 +3858,16 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
<listitem>
<para>
When an <command>UPDATE</command> causes a row to move from one
- partition to another, there is a chance that another concurrent
- <command>UPDATE</command> or <command>DELETE</command> misses this row.
- Suppose session 1 is performing an <command>UPDATE</command> on a
- partition key, and meanwhile a concurrent session 2 for which this row
- is visible performs an <command>UPDATE</command> or
- <command>DELETE</command> 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
- <command>UPDATE</command> or <command>DELETE</command>, 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 <command>UPDATE</command>/<command>DELETE</command> on this new row
- version.
+ partition to another, a concurrent <command>UPDATE</command> or
+ <command>DELETE</command> may wish to update or delete the same row.
+ Normally in these circumstances, <productname>PostgreSQL</productname>
+ would find the updated version of the row and operate on that
+ (providing it still matched the query's <literal>WHERE</literal>
+ clause). However, with partitioning when the row is moved to another
+ partition <productname>PostgreSQL</productname> will instead raise a
+ serialization failure error (which always returns with an SQLSTATE
+ value of '40001'). Applications may wish to retry the transaction if
+ this occurs.
</para>
</listitem>
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
Attachments:
partition_update_doc_fix_2.patchapplication/octet-stream; name=partition_update_doc_fix_2.patchDownload
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 85e4358..ef713a5 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3859,19 +3859,19 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
<para>
When an <command>UPDATE</command> causes a row to move from one
partition to another, there is a chance that another concurrent
- <command>UPDATE</command> or <command>DELETE</command> misses this row.
- Suppose session 1 is performing an <command>UPDATE</command> on a
- partition key, and meanwhile a concurrent session 2 for which this row
- is visible performs an <command>UPDATE</command> or
- <command>DELETE</command> 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
- <command>UPDATE</command> or <command>DELETE</command>, 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 <command>UPDATE</command>/<command>DELETE</command> on this new row
+ <command>UPDATE</command> or <command>DELETE</command> will get a
+ serialization failure error. Suppose session 1 is performing an
+ <command>UPDATE</command> on a partition key, and meanwhile a concurrent
+ session 2 for which this row is visible performs an
+ <command>UPDATE</command> or <command>DELETE</command> operation on this
+ row. In such case, session 2's <command>UPDATE</command> or
+ <command>DELETE</command>, 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
+ <command>UPDATE</command>/<command>DELETE</command> on this new row
version.
</para>
</listitem>
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!