pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.c index 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)

Message-ID: <7992298c-6f88-4784-bef7-94a860e3a706@interdb.jp>
Permalink:  ../7992298c-6f88-4784-bef7-94a860e3a706@interdb.jp/
Also on:    postgresql.org/message-id/7992298c-6f88-4784-bef7-94a860e3a706@interdb.jp

 ·  · 

reply

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