public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: pg_overexplain produces invalid JSON with RANGE_TABLE option
Date: Wed, 15 Apr 2026 14:35:46 -0700
Message-ID: <CAHg+QDdDrdqMr98a_OBYDYmK3RaT7XwCEShZfvDYKZpZTfOEjQ@mail.gmail.com> (raw)
Hi Hackers,
It appears that pg_overexplain produces invalid JSON output when
EXPLAIN (FORMAT JSON, RANGE_TABLE) is used. The "Unprunable RTIs" and
"Result RTIs" properties are emitted as key:value pairs directly inside
the "Range Table" JSON array, which is structurally invalid.
Attempted to fix this by moving ExplainCloseGroup() before the two
overexplain_bitmapset()
calls, so the properties are emitted as siblings of "Range Table" in the
parent object rather
than inside the array.
Attached a patch to address this which also includes a test.
Repro:
LOAD 'pg_overexplain';
CREATE TABLE t1 (id serial PRIMARY KEY, val text);
CREATE TABLE t2 (id serial PRIMARY KEY, a_id int REFERENCES t1(id), data
text);
INSERT INTO t1 VALUES (1, 'x'), (2, 'y');
INSERT INTO t2 VALUES (1, 1, 'd1'), (2, 2, 'd2');
EXPLAIN (FORMAT JSON, RANGE_TABLE)
EXPLAIN (FORMAT JSON, RANGE_TABLE)
SELECT * FROM t1 a JOIN t2 b ON a.id = b.a_id WHERE a.id = 1; "
| tail -n +2 | python3 -c "import json,sys; json.loads(sys.stdin.read());
print('VALID JSON')"
Thanks,
Satya
Attachments:
[application/octet-stream] 0001-Fix-pg_overexplain-invalid-JSON-with-RANGE_TABLE.patch (10.4K, 3-0001-Fix-pg_overexplain-invalid-JSON-with-RANGE_TABLE.patch)
download | inline diff:
diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out
index 05c6686d..12ab9262 100644
--- a/contrib/pg_overexplain/expected/pg_overexplain.out
+++ b/contrib/pg_overexplain/expected/pg_overexplain.out
@@ -294,13 +294,131 @@ $$);
<Security-Barrier>false</Security-Barrier> +
<Lateral>false</Lateral> +
</Range-Table-Entry> +
- <Unprunable-RTIs>1 3 4</Unprunable-RTIs> +
- <Result-RTIs>none</Result-RTIs> +
</Range-Table> +
+ <Unprunable-RTIs>1 3 4</Unprunable-RTIs> +
+ <Result-RTIs>none</Result-RTIs> +
</Query> +
</explain>
(1 row)
+-- Test JSON format with RANGE_TABLE to verify valid JSON structure.
+SELECT explain_filter($$
+EXPLAIN (RANGE_TABLE, FORMAT JSON, COSTS OFF)
+SELECT genus, array_agg(name ORDER BY name) FROM vegetables GROUP BY genus
+$$);
+ explain_filter
+----------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Aggregate", +
+ "Strategy": "Sorted", +
+ "Partial Mode": "Simple", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Group Key": ["vegetables.genus"], +
+ "Plans": [ +
+ { +
+ "Node Type": "Sort", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Sort Key": ["vegetables.genus", "vegetables.name"],+
+ "Plans": [ +
+ { +
+ "Node Type": "Append", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Append RTIs": "1", +
+ "Child Append RTIs": "none", +
+ "Subplans Removed": 0, +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Member", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "brassica", +
+ "Alias": "vegetables_1", +
+ "Disabled": false, +
+ "Scan RTI": 3 +
+ }, +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Member", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "daucus", +
+ "Alias": "vegetables_2", +
+ "Disabled": false, +
+ "Scan RTI": 4 +
+ } +
+ ] +
+ } +
+ ] +
+ } +
+ ] +
+ }, +
+ "Range Table": [ +
+ { +
+ "RTI": 1, +
+ "Kind": "relation", +
+ "Inherited": true, +
+ "In From Clause": true, +
+ "Eref": "vegetables (id, name, genus)", +
+ "Relation": "vegetables", +
+ "Relation Kind": "partitioned_table", +
+ "Relation Lock Mode": "AccessShareLock", +
+ "Permission Info Index": 1, +
+ "Security Barrier": false, +
+ "Lateral": false +
+ }, +
+ { +
+ "RTI": 2, +
+ "Kind": "group", +
+ "Inherited": false, +
+ "In From Clause": false, +
+ "Eref": "\"*GROUP*\" (genus)", +
+ "Security Barrier": false, +
+ "Lateral": false +
+ }, +
+ { +
+ "RTI": 3, +
+ "Kind": "relation", +
+ "Inherited": false, +
+ "In From Clause": true, +
+ "Alias": "vegetables (id, name, genus)", +
+ "Eref": "vegetables (id, name, genus)", +
+ "Relation": "brassica", +
+ "Relation Kind": "relation", +
+ "Relation Lock Mode": "AccessShareLock", +
+ "Security Barrier": false, +
+ "Lateral": false +
+ }, +
+ { +
+ "RTI": 4, +
+ "Kind": "relation", +
+ "Inherited": false, +
+ "In From Clause": true, +
+ "Alias": "vegetables (id, name, genus)", +
+ "Eref": "vegetables (id, name, genus)", +
+ "Relation": "daucus", +
+ "Relation Kind": "relation", +
+ "Relation Lock Mode": "AccessShareLock", +
+ "Security Barrier": false, +
+ "Lateral": false +
+ } +
+ ], +
+ "Unprunable RTIs": "1 3 4", +
+ "Result RTIs": "none" +
+ } +
+ ]
+(1 row)
+
-- Test just the DEBUG option. Verify that it shows information about
-- disabled nodes, parallel safety, and the parallelModeNeeded flag.
SET enable_seqscan = false;
diff --git a/contrib/pg_overexplain/pg_overexplain.c b/contrib/pg_overexplain/pg_overexplain.c
index 715eda8d..fb277e02 100644
--- a/contrib/pg_overexplain/pg_overexplain.c
+++ b/contrib/pg_overexplain/pg_overexplain.c
@@ -776,7 +776,14 @@ overexplain_range_table(PlannedStmt *plannedstmt, ExplainState *es)
ExplainCloseGroup("Range Table Entry", NULL, true, es);
}
- /* Print PlannedStmt fields that contain RTIs. */
+ /* Close the Range Table array before emitting PlannedStmt-level fields. */
+ ExplainCloseGroup("Range Table", "Range Table", false, es);
+
+ /*
+ * Print PlannedStmt fields that contain RTIs. These are properties of
+ * the PlannedStmt, not of individual RTEs, so they belong outside the
+ * Range Table array.
+ */
if (es->format != EXPLAIN_FORMAT_TEXT ||
!bms_is_empty(plannedstmt->unprunableRelids))
overexplain_bitmapset("Unprunable RTIs", plannedstmt->unprunableRelids,
@@ -785,9 +792,6 @@ overexplain_range_table(PlannedStmt *plannedstmt, ExplainState *es)
!bms_is_empty(plannedstmt->resultRelationRelids))
overexplain_bitmapset("Result RTIs", plannedstmt->resultRelationRelids,
es);
-
- /* Close group, we're all done */
- ExplainCloseGroup("Range Table", "Range Table", false, es);
}
/*
diff --git a/contrib/pg_overexplain/sql/pg_overexplain.sql b/contrib/pg_overexplain/sql/pg_overexplain.sql
index d07f9368..3f17b61a 100644
--- a/contrib/pg_overexplain/sql/pg_overexplain.sql
+++ b/contrib/pg_overexplain/sql/pg_overexplain.sql
@@ -66,6 +66,12 @@ EXPLAIN (DEBUG, RANGE_TABLE, FORMAT XML, COSTS OFF)
SELECT genus, array_agg(name ORDER BY name) FROM vegetables GROUP BY genus
$$);
+-- Test JSON format with RANGE_TABLE to verify valid JSON structure.
+SELECT explain_filter($$
+EXPLAIN (RANGE_TABLE, FORMAT JSON, COSTS OFF)
+SELECT genus, array_agg(name ORDER BY name) FROM vegetables GROUP BY genus
+$$);
+
-- Test just the DEBUG option. Verify that it shows information about
-- disabled nodes, parallel safety, and the parallelModeNeeded flag.
SET enable_seqscan = false;
view thread (5+ messages) latest in thread
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: pg_overexplain produces invalid JSON with RANGE_TABLE option
In-Reply-To: <CAHg+QDdDrdqMr98a_OBYDYmK3RaT7XwCEShZfvDYKZpZTfOEjQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox