Hi!
Unfortunately, the issue is still relevant, so I'm updating the first
post and patches. I got an Assert when executing an "INSERT ... ON
CONFLICT ... UPDATE ..." query on partitioned table.
Reproduction order.
-------------------
1) Apply the patch
[v1-0001-Triggering-Assert-on-query-with-ON-CONFLICT.no_cfbot]
to "master" branch:
git am v1-0001-Triggering-Assert-on-query-with-ON-CONFLICT.no_cfbot
2) Build postgres with "--enable-injection-points --enable-cassert",
for example:
./configure --enable-injection-points --enable-debug --enable-cassert
>/dev/null && make -j4 -s
3) Run isolation test onconflict.spec:
make check -C src/test/modules/injection_points
Assert is triggered in postgres, with part of stack:
...
#3 0x0000624cb170e05f in ExceptionalCondition (
conditionName=conditionName@entry=0x624cb17fcbf0
"!ItemPointerIndicatesMovedPartitions(&tmfd.ctid)",
fileName=fileName@entry=0x624cb1785ea7 "nodeModifyTable.c",
lineNumber=lineNumber@entry=2819) at assert.c:65
#4 0x0000624cb13f1412 in ExecOnConflictUpdate (returning=<synthetic
pointer>, canSetTag=true, excludedSlot=0x624cb2a61750,
conflictTid=0x7fff95cd827a, resultRelInfo=0x624cb2a62420,
context=0x7fff95cd8340) at nodeModifyTable.c:2819
...
Clarification.
--------------
In the query "INSERT ... ON CONFLICT ... UPDATE ..." when executing
INSERT, a conflict is triggered. But when trying to execute UPDATE, our
tuple has already been moved to another partition and Assert is
triggered. I think this is a correct situation and in this case we
should generate an error instead of Assert.
Fixing.
-------
Patch [v1-0001-Replace-invalid-Assert-with-ereport-ERROR.patch].
For testing need to apply
[v1-0001-Replace-invalid-Assert-with-ereport-ERROR.patch]:
git am v1-0001-Replace-invalid-Assert-with-ereport-ERROR.patch
rebuild postgres (2) and run test again (3),
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
From 8780e66fc759872e06746c9c214dec52644b2e93 Mon Sep 17 00:00:00 2001
From: Koval Dmitry <[email protected]>
Date: Wed, 19 Feb 2025 13:09:33 +0300
Subject: [PATCH v1] Triggering Assert on query with ON CONFLICT
---
src/backend/executor/nodeModifyTable.c | 1 +
src/test/modules/injection_points/Makefile | 6 +++-
.../injection_points/expected/onconflict.out | 23 ++++++++++++++
.../injection_points/specs/onconflict.spec | 30 +++++++++++++++++++
4 files changed, 59 insertions(+), 1 deletion(-)
create mode 100644 src/test/modules/injection_points/expected/onconflict.out
create mode 100644 src/test/modules/injection_points/specs/onconflict.spec
diff --git a/src/backend/executor/nodeModifyTable.c
b/src/backend/executor/nodeModifyTable.c
index 874b71e660..d131abcd67 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1148,6 +1148,7 @@ ExecInsert(ModifyTableContext *context,
*/
TupleTableSlot *returning = NULL;
+
INJECTION_POINT("before-on-conflict-update", NULL);
if (ExecOnConflictUpdate(context,
resultRelInfo,
&conflictTid, slot, canSetTag,
&returning))
diff --git a/src/test/modules/injection_points/Makefile
b/src/test/modules/injection_points/Makefile
index bfdb3f5337..5420850b39 100644
--- a/src/test/modules/injection_points/Makefile
+++ b/src/test/modules/injection_points/Makefile
@@ -9,7 +9,8 @@ EXTENSION = injection_points
DATA = injection_points--1.0.sql
PGFILEDESC = "injection_points - facility for injection points"
-REGRESS = injection_points hashagg reindex_conc vacuum
+# Disable regress tests
+#REGRESS = injection_points hashagg reindex_conc vacuum
REGRESS_OPTS = --dlpath=$(top_builddir)/src/test/regress
ISOLATION = basic \
@@ -24,6 +25,9 @@ ISOLATION = basic \
# reindex-concurrently-upsert-on-constraint \
# reindex-concurrently-upsert-partitioned
+# Enable only one isolation test
+ISOLATION = onconflict
+
# The injection points are cluster-wide, so disable installcheck
NO_INSTALLCHECK = 1
diff --git a/src/test/modules/injection_points/expected/onconflict.out
b/src/test/modules/injection_points/expected/onconflict.out
new file mode 100644
index 0000000000..ee89965592
--- /dev/null
+++ b/src/test/modules/injection_points/expected/onconflict.out
@@ -0,0 +1,23 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1 s1ipa s1i s2 s2u s2ipw s2c s1c
+step s1: BEGIN;
+step s1ipa: SELECT injection_points_attach('before-on-conflict-update',
'wait');
+injection_points_attach
+-----------------------
+
+(1 row)
+
+step s1i: INSERT INTO t_int VALUES (1, 11, 111) ON CONFLICT (i) DO UPDATE SET
x = excluded.x; <waiting ...>
+step s2: BEGIN;
+step s2u: UPDATE t_int SET i = i + 150 WHERE i = 1;
+step s2ipw: SELECT injection_points_wakeup('before-on-conflict-update');
+injection_points_wakeup
+-----------------------
+
+(1 row)
+
+step s2c: COMMIT;
+step s1i: <... completed>
+ERROR: tuple to be updated was already moved to another partition due to
concurrent update
+step s1c: COMMIT;
diff --git a/src/test/modules/injection_points/specs/onconflict.spec
b/src/test/modules/injection_points/specs/onconflict.spec
new file mode 100644
index 0000000000..495449264b
--- /dev/null
+++ b/src/test/modules/injection_points/specs/onconflict.spec
@@ -0,0 +1,30 @@
+setup
+{
+ CREATE EXTENSION injection_points;
+
+ CREATE TABLE t_int (i int PRIMARY KEY, v int, x int) PARTITION BY RANGE (i);
+ CREATE TABLE t_int_1 PARTITION OF t_int FOR VALUES FROM (1) TO (100);
+ CREATE TABLE t_int_2 PARTITION OF t_int FOR VALUES FROM (100) TO (200);
+
+ INSERT INTO t_int VALUES (1, 10, 100);
+}
+
+teardown
+{
+ DROP TABLE t_int;
+ DROP EXTENSION injection_points;
+}
+
+session s1
+step s1 { BEGIN; }
+step s1ipa { SELECT injection_points_attach('before-on-conflict-update',
'wait'); }
+step s1i { INSERT INTO t_int VALUES (1, 11, 111) ON CONFLICT (i) DO
UPDATE SET x = excluded.x; }
+step s1c { COMMIT; }
+
+session s2
+step s2 { BEGIN; }
+step s2ipw { SELECT injection_points_wakeup('before-on-conflict-update'); }
+step s2u { UPDATE t_int SET i = i + 150 WHERE i = 1; }
+step s2c { COMMIT; }
+
+permutation s1 s1ipa s1i(s2c) s2 s2u s2ipw s2c s1c
--
2.43.0
From 50fe893680665226982ffa67bbde6c2cf6017260 Mon Sep 17 00:00:00 2001
From: Koval Dmitry <[email protected]>
Date: Fri, 19 Dec 2025 01:51:07 +0300
Subject: [PATCH v1] Replace invalid Assert with ereport(ERROR, ...)
At the time Assert was added, the UPDATE of INSERT ON CONFLICT operation
was not supported for partitioned tables. However, support has been
added, so need to replace Assert with ereport(ERROR, ...).
---
src/backend/executor/nodeModifyTable.c | 11 ++++-------
1 file changed, 4 insertions(+), 7 deletions(-)
diff --git a/src/backend/executor/nodeModifyTable.c
b/src/backend/executor/nodeModifyTable.c
index d131abcd67..f51ffbfc1e 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -2810,13 +2810,10 @@ ExecOnConflictUpdate(ModifyTableContext *context,
(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
errmsg("could not serialize
access due to concurrent update")));
- /*
- * As long as we don't support an UPDATE of INSERT ON
CONFLICT for
- * a partitioned table we shouldn't reach to a case
where tuple to
- * be lock is moved to another partition due to
concurrent update
- * of the partition key.
- */
-
Assert(!ItemPointerIndicatesMovedPartitions(&tmfd.ctid));
+ if (ItemPointerIndicatesMovedPartitions(&tmfd.ctid))
+ ereport(ERROR,
+
errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
+ errmsg("tuple to be updated was
already moved to another partition due to concurrent update"));
/*
* Tell caller to try again from the very start.
--
2.43.0
