pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

Поиск
Список
Период
Сортировка
Искать

pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
Вложения:
Hi,

When pg_stat_statements normalizes a query that uses an ON EMPTY or ON
ERROR clause of the SQL/JSON query functions, the behavior keyword is
replaced with a parameter symbol. On 18.6:

    CREATE TABLE jt (js jsonb);
    SELECT JSON_QUERY(js, '$.b' EMPTY ARRAY ON EMPTY) FROM jt;
    SELECT JSON_EXISTS(js, 'strict $.b' TRUE ON ERROR) FROM jt;
    SELECT JSON_EXISTS(js, 'strict $.b' FALSE ON ERROR) FROM jt;
    SELECT query FROM pg_stat_statements WHERE query LIKE 'SELECT JSON%';
                            query
    -----------------------------------------------------
     SELECT JSON_EXISTS(js, $1 $2 ON ERROR) FROM jt
     SELECT JSON_EXISTS(js, $1 $2 ON ERROR) FROM jt
     SELECT JSON_QUERY(js, $1 $2 ARRAY ON EMPTY) FROM jt

The same happens with EMPTY OBJECT and NULL. DEFAULT  is
normalized correctly. The normalized text is not valid SQL, so it can't
be used with EXPLAIN (GENERIC_PLAN):

    EXPLAIN (GENERIC_PLAN) SELECT JSON_QUERY(js, $1 $2 ARRAY ON EMPTY) FROM jt;
    ERROR:  syntax error at or near "$2"

and the entries for TRUE ON ERROR and FALSE ON ERROR, which do opposite
things, show the same text.

The documentation says that a constant is replaced by a parameter
symbol "when a constant's value has been ignored for purposes of
matching the query to other queries". That's not the case here: these
keywords are not constants in the query text, and the behavior is part
of the query ID. TRUE ON ERROR and FALSE ON ERROR, EMPTY ARRAY and
EMPTY OBJECT, NULL ON EMPTY and ERROR ON EMPTY all give different query
IDs, unlike, say, SELECT true and SELECT false, which are merged and
shown as SELECT $1.

The cause is in GetJsonBehaviorConst(), which builds the Const for such
a behavior and sets its location to that of the behavior clause,
although the Const doesn't correspond to a constant in the query text.
Query jumbling records the locations of Consts, so pg_stat_statements
takes the keyword at that location for a constant. This comes from
6185c9737cf4, so 17 and later are affected. Master, as of 57926d757,
behaves the same.

The attached patch leaves the location of the Const unset. The only
other use of it was the error position when coercing the Const fails,
in transformJsonBehavior(), which now uses the location of the behavior
clause directly, so error messages don't change. With the patch, the
queries above are normalized as

    SELECT JSON_QUERY(js, $1 EMPTY ARRAY ON EMPTY) FROM jt
    SELECT JSON_EXISTS(js, $1 TRUE ON ERROR) FROM jt

and EXPLAIN (GENERIC_PLAN) works on them. Query IDs don't change,
since the locations of Consts are only recorded, not hashed, so
existing pg_stat_statements entries keep their IDs after a minor
upgrade. The patch adds a pg_stat_statements test, which fails without
the fix. It applies to REL_18_STABLE and REL_19_STABLE. For
REL_17_STABLE, the code applies, but the test has to go elsewhere in
select.sql, since the file ends differently there.

--
Ilya Staroverov

Re: pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
Вложения:
Hi Sami,

Thanks for the review.

Sorry about the threading: my mail client didn't add In-Reply-To to
that message, and our mail server added the [MASSMAIL] tag, which I've
removed here. The start of the thread is [1].

> That does not mean the text can always be passed directly to EXPLAIN
> (GENERIC_PLAN), or executed as a prepared statement.

Agreed, I shouldn't have used that as an argument. The problem 0001
fixes is that the keyword is lost: TRUE ON ERROR and FALSE ON ERROR get
separate entries with the same text. I've reworded the commit message
accordingly, and changed the tests to use '{}'::jsonb, except for one
with a comment, as you suggested. That one uses UNKNOWN ON ERROR, which
wasn't covered before.

> With 0002 they get different query IDs, AFAICS because
> JsonExpr.column_name is included through TableFunc.colvalexprs. That
> does not look like a different query to me.

Agreed. The column names in RangeTblEntry.eref are not jumbled either,
so my comparison with table aliases wasn't a good one. In v3, neither
the column names nor the path names (AS p1) are part of the query ID;
the path names are only used to refer to paths in the PLAN clause and
when deparsing.

There is one exception: a column without PATH gets the path '$."name"'
derived from its name. That path doesn't appear in the query text, so
it has no location, and since Const values are not jumbled, the name
is the only thing that tells such columns apart. If it were ignored,
these two would be merged, although they read different keys, and the
entry would show "a int" for both:

    SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]'::jsonb, '$[*]'
      COLUMNS (a int)) jt;
    SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]'::jsonb, '$[*]'
      COLUMNS (b int)) jt;

So v3 gives JsonExpr.column_name a custom jumble function, which
includes the name only when the path has no location, that is, for a
column without PATH. As a result, a column without PATH and the same
column with the equivalent explicit PATH get different query IDs, which
seems harmless. Also, the other SQL/JSON query functions no longer
jumble their NULL column_name, so their query IDs change too; the
commit message mentions that.

> I also find the TableFunc.plan part a bit hard to get comfortable
> with.

TableFunc.plan is built by parse analysis, in transformJsonTable(), not
by the planner. It's the only place in the Query that records the
NESTED PATH structure and the PLAN clause: which columns belong to
which path, OUTER or INNER, UNION or CROSS. With the path names left
out, everything that remains in these nodes affects the result:

    JsonTablePath:        value (the path Const, which is normalized)
    JsonTablePathScan:    path, errorOnError, child, outerJoin,
                          colMin, colMax
    JsonTableSiblingJoin: lplan, rplan, cross

(outerJoin and cross exist only on master.) And since it is the
resolved plan, equivalent spellings still get the same query ID. On
master, a query without a PLAN clause and the same query with PLAN (p0
OUTER p1), which is the default, are merged, also with different path
names, while PLAN (p0 INNER p1) gets a separate entry. So I think
jumbling the plan without the path names is about as targeted as it
gets, but if you'd leave out some of these fields, I can change that.

A correction to my previous message: it's not the number of columns
that was part of the query ID, but the references to the columns from
the rest of the query, such as the expansion of SELECT *. The commit
message now says that.

The test in 0002 now also checks that queries which differ only in
column names or path names are merged, and that columns without PATH
and with different names are not. The PLAN clause doesn't exist in
REL_19_STABLE, so the test doesn't use it; I checked those cases on
master by hand. The series applies to master and REL_19_STABLE, and
I've tested it on both.

[1] https://www.postgresql.org/message-id/flat/086A90EE-9771-4387-8B46-BF84A610F04F%40localhost.localdomain

--
Ilya Staroverov

Re: pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
On Mon, Oct 19, 2026 at 05:16:42AM +0000, Staroverov Ilja wrote:
> A correction to my previous message: it's not the number of columns
> that was part of the query ID, but the references to the columns from
> the rest of the query, such as the expansion of SELECT *. The commit
> message now says that.
> 
> The test in 0002 now also checks that queries which differ only in
> column names or path names are merged, and that columns without PATH
> and with different names are not. The PLAN clause doesn't exist in
> REL_19_STABLE, so the test doesn't use it; I checked those cases on
> master by hand. The series applies to master and REL_19_STABLE, and
> I've tested it on both.

When it comes to such problems, I'd suggest to implement the tests as
a first patch, then do the jumbling logic in a second patch that shows
the changes in PGSS.  It's easier to see what you think is the "wrong"
information and what becomes the "right" information ("wrong" and
"right" mean what you think is OK or not OK in terms of PGSS
monitoring).

+     2 | SELECT * FROM JSON_TABLE($1::jsonb, $2                                +
+       |   COLUMNS (a int PATH $3 DEFAULT $4 ON EMPTY)) jt
+     1 | SELECT * FROM JSON_TABLE($1::jsonb, $2                                +
+       |   COLUMNS (a int PATH $3)) jt
+     2 | SELECT * FROM JSON_TABLE($1::jsonb, $2                                +
+       |   COLUMNS (a int PATH $3,                                             +
+       |            NESTED PATH $4 AS p1 COLUMNS (b int PATH $5))) jt
+     1 | SELECT * FROM JSON_TABLE($1::jsonb, $2                                +
+       |   COLUMNS (a int)) jt
+     1 | SELECT * FROM JSON_TABLE($1::jsonb, $2                                +
+       |   COLUMNS (a text PATH $3)) jt
+     1 | SELECT * FROM JSON_TABLE($1::jsonb, $2                                +
+       |   COLUMNS (b int)) jt

There are many more keyword patterns than the ones tested here, like
PASSING, PLAN (UNION, OUTER ..), FORMAT JSON, etc.  None of them are
covered.  I guess that you may want to worry about them.

Instead of bloating more select.sql, let's move the JSON tests into a
different input file.  They can be isolated, and there are way more
patterns than what you are showing here.

Note that none of this is backpatchable, as this impacts the Node
definitions and the query IDs computed.
--
Michael

Re: pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
Вложения:
Hi Michael,

Thanks for the review.

> When it comes to such problems, I'd suggest to implement the tests as
> a first patch, then do the jumbling logic in a second patch that shows
> the changes in PGSS.

Done in the attached v4. 0001 adds a separate test file, sqljson.sql,
so select.sql is no longer touched. Besides the cases from v3, it
covers:

- the SQL/JSON query functions: ON EMPTY and ON ERROR behaviors,
  RETURNING, PASSING, WRAPPER, QUOTES and FORMAT JSON;
- JSON_TABLE: column types and behaviors, EXISTS, FORMAT JSON and FOR
  ORDINALITY columns, column and path names, NESTED PATH, PLAN with
  OUTER/INNER and UNION/CROSS, PLAN DEFAULT, table-level ON ERROR and
  PASSING;
- the SQL/JSON constructors and predicates: JSON_OBJECT, JSON_ARRAY,
  JSON_OBJECTAGG, JSON_ARRAYAGG, JSON(), JSON_SCALAR, JSON_SERIALIZE
  and IS JSON.

Its expected output shows the current behavior. 0002 is the behavior
keyword fix, and its diff of the expected output shows, for example,
"$2 ARRAY ON EMPTY" becoming "EMPTY ARRAY ON EMPTY", and three
identical "JSON_EXISTS(js, $1 $2 ON ERROR)" entries becoming TRUE,
FALSE and UNKNOWN ON ERROR. The new tests showed that the table-level
ON ERROR of JSON_TABLE was affected too, and 0002 fixes that as well.
0003 is the JSON_TABLE jumbling, and its diff shows the merged entries
being split: for example, eight queries with various column types and
behaviors were in a single entry, and so were PLAN (p0 OUTER p1) and
PLAN (p0 INNER p1). Each patch passes the tests on its own.

The constructors and predicates are fine as they are: queries that
differ in ABSENT ON NULL, WITH UNIQUE KEYS, RETURNING or FORMAT JSON
already get different query IDs, and the keywords are kept in the
normalized text.

> Note that none of this is backpatchable, as this impacts the Node
> definitions and the query IDs computed.

Agreed for 0003. 0002 changes neither: it only leaves the location of
the behavior Consts unset, and locations are recorded, not hashed, so
the query IDs stay the same. But it's your call whether that's worth
back-patching.

--
Ilya Staroverov

Re: pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
On Wed, Oct 21, 2026 at 09:04:09AM +0000, Staroverov Ilja wrote:
> Agreed for 0003. 0002 changes neither: it only leaves the location of
> the behavior Consts unset, and locations are recorded, not hashed, so
> the query IDs stay the same. But it's your call whether that's worth
> back-patching.

v1 offers a nice coverage.  I have noticed a couple of holes here and
there, but globally that was fine.

+     2 | SELECT JSON_VALUE(js, $1 $2 ON EMPTY ERROR ON ERROR) FROM pgss_sqljson_tab

Urg, yeah.  In this one $2 is a DEFAULT keyword.  That's confusing..
Sami has mentioned that on another thread.  It does not prevent doing
something with the tests first; query jumbing can sometimes feel
tricky to feel (not be!) right.

+     2 | SELECT JSON_VALUE(js, $1 DEFAULT $2 ON EMPTY) FROM pgss_sqljson_tab
+     1 | SELECT JSON_VALUE(js, $1 DEFAULT $2 ON ERROR) FROM pgss_sqljson_tab

These ones are separated due to the different last keywords, and
perhaps more arguable, but it's confusing to replace the
NULL/DEFAULT/EMPTY by dollar parameters.  And we have quite a few more
of these incorrect patterns.

These SQL/JSON patterns are so various that adding a mention of the
defaults is a good idea.  I've expanded that a bit more.

The ON ERROR and ON EMPTY patterns can also be combined together in a
single query.  I've added more of them.

under NESTED, PATH is optional.  Having patterns where it's gone seems
necessary to me.  Similarly, no NESTED in a PLAN is the same a no
PLAN.

There were a few more patterns that were not treated in plans with
combinations of UNION, INNER, CROSS..  Things like UNION/UNION and
CROSS/CROSS included.

And a few more notes for things missing:
* WITHOUT UNIQUE
* JSON_ARRAYAGG(ORDER BY [DESC])
* JSON_TABLE(UNKNOWN ON ERROR)
* JSON_SERIALIZE(FORMAT)
* PASSING in JSON_QUERY

No backpatch can happen on that, I am afraid.  The query ID stability
matters on stable branches, even if some if the keywords are currently
eaten due to the locations.
--
Michael

Re: pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
Вложения:
Hi Michael,

Thanks for expanding and committing the tests.

> No backpatch can happen on that, I am afraid.

Understood.

Attached is v5, rebased on top of ae75d12b5, without the tests patch.
The only code change since v4 is in makeJsonTablePathScan() in 0002,
which now gets the location of the path from pathspec->location.

> but it's confusing to replace the
> NULL/DEFAULT/EMPTY by dollar parameters.  And we have quite a few more
> of these incorrect patterns.

0001 fixes all of them in the new tests, for example:

    $1 $2 ON EMPTY ERROR ON ERROR
 -> $1 NULL ON EMPTY ERROR ON ERROR

    $1 $2 ARRAY ON EMPTY $3 OBJECT ON ERROR
 -> $1 EMPTY ARRAY ON EMPTY EMPTY OBJECT ON ERROR

and the three "JSON_EXISTS(js, $1 $2 ON ERROR)" entries become TRUE,
FALSE and UNKNOWN ON ERROR. The entries and their call counts stay the
same, only the texts change. After 0001, the only parameter symbols
left in the ON EMPTY and ON ERROR clauses are the DEFAULT values, as
in "DEFAULT $2 ON EMPTY", where $2 is the value itself (1 or 2 in the
tests), a constant in the query text.

0002 is the JSON_TABLE jumbling. Its diff shows the merged entries
being split: for example, the ten queries with different column types
and behaviors were in a single entry, and so were the thirteen with
EXISTS, FORMAT JSON and FOR ORDINALITY columns. Queries with
equivalent plans are still merged: the one without a PLAN clause with
PLAN (p0 OUTER (p1 UNION p2)), and PLAN (p0 INNER (p1 UNION p2)) with
PLAN DEFAULT (INNER) and PLAN DEFAULT (UNION, INNER). So are queries
that differ only in column names, path names, or PATH after NESTED.
The exception is columns without PATH, as in "COLUMNS (a int)" and
"COLUMNS (b int)": their paths are derived from the names, so the
names are part of the query ID in that case.

One split in that diff may look unnecessary: "a jsonb PATH" and
"a jsonb FORMAT JSON PATH". Their JsonExprs differ only in the format
of the RETURNING clause, as do JSON_QUERY(js, '$' RETURNING jsonb) and
the same with FORMAT JSON, which already get different query IDs, so
I left that alone.

--
Ilya Staroverov

Re: pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
On Thu, Oct 22, 2026 at 10:52:28AM +0000, Staroverov Ilja wrote:
> 0001 fixes all of them in the new tests, for example:

      errmsg("cannot cast behavior expression of type %s to %s",
             format_type_be(exprType(expr)),
             format_type_be(returning->typid)),
-     parser_errposition(pstate, exprLocation(expr)));
+     parser_errposition(pstate, location));

While looking at v5-0001, I have been puzzled by this piece, because
it is actually not reachable with any normal means or even SQL.
Anyway, I get that Amit wanted this part to be defensive, so your
suggestion of using the behavior's location sounds good to me.

Applied this part.
--
Michael

Re: pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Sami Imseih <samimseih.pg@gmail.com>
Дата:
Hi,

I took a look at v5-0002.

+ /*
+  * JSON_TABLE() column name or NULL if this is not for a JSON_TABLE().
+  * Only jumbled for a column without PATH, see queryjumblefuncs.c.
+  */
+ char *column_name pg_node_attr(custom_query_jumble);

I don't think we should do this. I think column_name should just be ignored
for query jumbling.

With v5-0002 applied, I get this:

```
SELECT * FROM jt, JSON_TABLE(js, '$[*]' COLUMNS (a int PATH '$.a')) q;
SELECT * FROM jt, JSON_TABLE(js, '$[*]' COLUMNS (b int PATH '$.b')) q;
SELECT * FROM jt, JSON_TABLE(js, '$[*]' COLUMNS (a int)) q;
SELECT * FROM jt, JSON_TABLE(js, '$[*]' COLUMNS (b int)) q;
```

```
 calls |                            query
-------+--------------------------------------------------------------
     2 | SELECT * FROM jt, JSON_TABLE(js, $1 COLUMNS (a int PATH $2)) q
     2 | SELECT * FROM jt, JSON_TABLE(js, $1 COLUMNS (b int PATH $2)) q
(2 rows)
```

So when PATH is omitted, the column label becomes part of the query ID. I
don't think that is consistent with how we handle similar column definition
lists elsewhere.

For example:

```
SELECT * FROM pg_temp.pair_record() AS t(a int, b text);
SELECT * FROM pg_temp.pair_record() AS t(x int, y text);
```

```
 calls |                         query
-------+--------------------------------------------------------
     2 | SELECT * FROM pg_temp.pair_record() AS t(a int, b text)
(1 row)
```

The column names in the SRF column definition list don't make those separate
entries. I think JSON_TABLE column names should follow the same rule. Explicit
PATH values are not included in the query ID, so I don't think omitting PATH
should make the column name part of the query ID.

So I think `JsonExpr.column_name` should be marked `query_jumble_ignore`
instead of using a custom query jumble function.

--
Sami


Re: pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Michael Paquier <michael@paquier.xyz>
Дата:
Вложения:
On Mon, Oct 26, 2026 at 01:23:22PM -0500, Sami Imseih wrote:
> The column names in the SRF column definition list don't make those separate
> entries. I think JSON_TABLE column names should follow the same rule. Explicit
> PATH values are not included in the query ID, so I don't think omitting PATH
> should make the column name part of the query ID.
> 
> So I think `JsonExpr.column_name` should be marked `query_jumble_ignore`
> instead of using a custom query jumble function.

Okay.  How would this choice impact the PGSS outputs?  Perhaps we
should first expand the tests with PATH keywords touching multiple
columns first?  I was hesitant to do so while introducing the tests,
and only focused on the obviously-broken case with the ON ERROR/NULL
keywords handled as constants in the PGSS outputs.
--
Michael

Re: pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters

От:
Staroverov Ilja <i.staroverov@ftdata.ru>
Дата:
Вложения:
Hi,

On Tue, Oct 27, 2026 at 08:28:52AM +0900, Michael Paquier wrote:
> On Mon, Oct 26, 2026 at 01:23:22PM -0500, Sami Imseih wrote:
>> So I think `JsonExpr.column_name` should be marked `query_jumble_ignore`
>> instead of using a custom query jumble function.

Agreed, thanks.  This is consistent with the column definition lists
of functions in FROM, and it also merges a column without PATH with
the same column with an explicit PATH, which v5 kept apart.

> Okay.  How would this choice impact the PGSS outputs?  Perhaps we
> should first expand the tests with PATH keywords touching multiple
> columns first?

Attached is v6.  0001 adds tests with several columns, with and
without PATH, and with an explicit PATH equal to the one derived from
the column name.  On master, the queries of this test block with the
same number of columns are all merged into a single entry, as the
column definitions are not part of the query ID.

0002 is the JSON_TABLE jumbling, with JsonExpr.column_name marked
query_jumble_ignore instead of the custom jumble function.  For these
tests, its diff shows the same two entries as on master, with the
paths shown as parameter symbols.  Compared to v5, the only change in
the expected output of the existing tests is that COLUMNS (a int) and
COLUMNS (b int) are now merged with the other queries with one column.

--
Ilya Staroverov

FAQ