From: Hironobu SUZUKI <hironobu@interdb.jp>
To: pgsql-bugs@lists.postgresql.org
Subject: EXPLAIN (ANALYZE, FORMAT JSON) emits invalid output for MERGE on a partitioned table
Date: Fri, 6 Nov 2026 19:39:16 +0900
Message-ID: <7992298c-6f88-4784-bef7-94a860e3a706@interdb.jp> (raw)
Hi,
While testing, I noticed that EXPLAIN (ANALYZE, FORMAT JSON) of a MERGE
whose target is a partitioned table produces syntactically invalid JSON.
The MERGE tuple counters ("Tuples Inserted", etc.) are emitted inside
the "Target Tables" array, so a bare "key": value pair appears inside a
JSON array.
Reproducer:
CREATE TABLE t (id int) PARTITION BY RANGE (id);
CREATE TABLE t1 PARTITION OF t DEFAULT;
EXPLAIN (ANALYZE, FORMAT JSON)
MERGE INTO t USING (VALUES (1)) s(id) ON t.id = s.id
WHEN NOT MATCHED THEN INSERT VALUES (s.id);
Actual output (excerpt):
"Target Tables": [
{
"Relation Name": "t1",
"Alias": "t_1"
},
"Tuples Inserted": 1,
"Tuples Updated": 0,
"Tuples Deleted": 0,
"Tuples Skipped": 0
],
This is rejected by JSON parsers. With a non-partitioned target, "Target
Tables" is not emitted (labeltargets is false), so the counters appear
as ordinary properties of the Plan object and the output is valid.
I reproduced this on master (commit edbe7ffdc8), 19rc1, 18.6, 17.11,
16.16 and 15.19; the same code is present in all branches that have MERGE.
(Text format is unaffected.)
Cause:
In show_modifytable_info(), the "Target Tables" group is opened before
the loop over result relations but is only closed at the very end of the
function, after the ON CONFLICT and MERGE properties have been emitted.
Those properties belong to the ModifyTable node itself, not to the list
of target tables.
Patch:
The attached patch (against master) closes the "Target Tables" group
right after the loop over the target relations. With it, the output for
the same query becomes:
"Target Tables": [
{
"Relation Name": "t1",
"Alias": "t_1"
}
],
"Tuples Inserted": 1,
"Tuples Updated": 0,
"Tuples Deleted": 0,
"Tuples Skipped": 0,
I tested the patch on master, 19rc1, 18.6, 17.11, 16.16 and 15.19. It
applies to all of them with git apply without modification, the JSON
output of the reproducer is valid with it, and make check passes on each.
Since the same code is present in all of these branches, a back-patch
seems appropriate.
Regards,
Hironobu Suzuki
Attachments:v1-0001-Fix-EXPLAIN-output-of-MERGE-on-partitioned-tables.patch (text/plain, 1.9K) download
From 458feed315f7d1f7c1f938ffda7d41fa05303daa Mon Sep 17 00:00:00 2001
From: Hironobu Suzuki <hironobu@interdb.jp>
Date: Fri, 6 Nov 2026 17:51:13 +0900
Subject: [PATCH v1] Fix EXPLAIN output of MERGE on partitioned tables
When a ModifyTable node has multiple target relations, such as a
partitioned table, show_modifytable_info() opens a "Target Tables"
group but closed it only after emitting the ON CONFLICT and MERGE
properties. For MERGE, that includes the "Tuples Inserted", "Tuples
Updated", "Tuples Deleted" and "Tuples Skipped" counters shown by
EXPLAIN ANALYZE.
In the structured formats those properties therefore ended up inside
the "Target Tables" list. In JSON this is invalid output (a bare
"key": value pair inside an array); in YAML and XML the elements were
nested under the wrong parent. Text format was not affected.
Fix by closing the group right after the loop over the target
relations, so that these properties are emitted as siblings of
"Target Tables".
Backpatch-through: 15
---
src/backend/commands/explain.c | 10 +++++++---
1 file changed, 7 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.cindex 4b1ad4227ac..f534d1f63ff 100644--- a/src/backend/commands/explain.c+++ b/src/backend/commands/explain.c@@ -4876,6 +4876,13 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
}
}
+ /*+ * Close the "Target Tables" group before emitting the properties below;+ * they belong to the ModifyTable node itself, not to its target list.+ */+ if (labeltargets)+ ExplainCloseGroup("Target Tables", "Target Tables", false, es);+
/* Gather names of ON CONFLICT arbiter indexes */
foreach(lst, node->arbiterIndexes)
{
@@ -4999,9 +5006,6 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
}
}
}
-- if (labeltargets)- ExplainCloseGroup("Target Tables", "Target Tables", false, es);
}
/*
--
2.39.5 (Apple Git-154)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: hironobu@interdb.jp
Subject: Re: EXPLAIN (ANALYZE, FORMAT JSON) emits invalid output for MERGE on a partitioned table
In-Reply-To: <7992298c-6f88-4784-bef7-94a860e3a706@interdb.jp>
References: <7992298c-6f88-4784-bef7-94a860e3a706@interdb.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox