Re: [MASSMAIL] pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters
Re: [MASSMAIL] pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters
Hi,
While testing the above, I noticed a related and more serious problem:
apart from the number of columns, the column definitions of a
JSON_TABLE are not part of the query ID at all. On 18.6, these get the
same query ID, so pg_stat_statements merges them into one entry:
SELECT * FROM JSON_TABLE(jsonb '[{"a":1}]', '$[*]'
COLUMNS (a int PATH '$.a')) jt;
SELECT * FROM JSON_TABLE(jsonb '[{"a":1}]', '$[*]'
COLUMNS (a text PATH '$.a')) jt;
SELECT * FROM JSON_TABLE(jsonb '[{"a":1}]', '$[*]'
COLUMNS (a int PATH '$.b' DEFAULT 0 ON EMPTY)) jt;
SELECT * FROM JSON_TABLE(jsonb '[{"a":1}]', '$[*]'
COLUMNS (a int PATH '$.b' ERROR ON EMPTY)) jt;
The same goes for nested columns, and on master for the PLAN clause:
PLAN (p0 OUTER p1) and PLAN (p0 INNER p1) get the same query ID,
although they return different rows. The pg_stat_statements
documentation says that merging is expected for semantically
equivalent queries, which these are not. Also, the column paths of the
merged queries may differ, but the entry shows the literal paths of the
first one rather than parameter symbols.
The cause is that TableFunc.colvalexprs and TableFunc.plan, which hold
the column definitions and the NESTED PATH structure of a JSON_TABLE,
are marked query_jumble_ignore, like the other TableFunc fields that
query jumbling has always ignored for XMLTABLE. But for XMLTABLE, at
least the column expressions (colexprs) are jumbled, while JSON_TABLE
doesn't use colexprs, so nothing about its columns is.
The attached 0002 removes query_jumble_ignore from these two fields.
Column paths and other literals are still constants, so queries that
differ only in those are still merged, and they are now shown as
parameter symbols. For that, it also gives the Const for a NESTED PATH
expression the location of its literal, as column paths already have.
Note that column names become part of the query ID too, since
JsonExpr.column_name is jumbled, much like table aliases already are.
It adds a pg_stat_statements test, which fails without the fix.
0002 depends on 0001, which is unchanged from v1: once the column
definitions are jumbled, the behavior Consts of the columns would
otherwise show up as parameter symbols too, e.g. "NULL ON EMPTY" would
become "$4 ON EMPTY".
Unlike 0001, 0002 changes the query IDs of JSON_TABLE queries, so it
can't go into 17 or 18. But v19 is not released yet, so perhaps it can
still go there as well as master; the series applies to REL_19_STABLE,
and I've tested it there too.
XMLTABLE has a similar, but long-standing, issue: column types and
DEFAULT expressions have never been part of the query ID, so XMLTABLE
queries that differ only in column types are merged too. I left that
alone, since it's the original behavior, but I can add it to 0002 if
that's wanted for master.
--
Ilya Staroverov
Re: [MASSMAIL] pg_stat_statements replaces SQL/JSON ON EMPTY / ON ERROR keywords with parameters
От:
Sami Imseih <samimseih.pg@gmail.com>
Дата:
Hi,
Note that this thread should be a continuation of [1], but in the archives,
it shows as a separate thread [2].
> 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
I agree that the behavior keywords should not be turned into constants here.
0001 fixes that part correctly, AFAICS. These Consts are created to represent
behavior keywords, not constants that appeared in the query text, so leaving
their location unset seems right.
> 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"
That fixes the behavior-keyword problem, but pg_stat_statements.query is
representative text as documented [3]:
```
When a constant's value has been ignored for purposes of matching the query
to other queries, the constant is replaced by a parameter symbol, such as $1,
in the pg_stat_statements display.
```
That does not mean the text can always be passed directly to EXPLAIN
(GENERIC_PLAN), or executed as a prepared statement. A lot of the time it can,
but I don't think that is guaranteed.
The tests in 0001 show this too if they use typed string literal syntax.
For example, `jsonb '{}'` can still be displayed as `jsonb $1`, which is not
valid SQL. That is a separate, pre-existing pg_stat_statements normalization
issue.
Maybe most of the 0001 tests should use `'{}'::jsonb`, so they stay focused on
the behavior keyword fix. One typed string literal case could remain with a
comment saying that a form such as `jsonb '{}'` cannot be replayed as-is.
As far as 0002 goes; this one gives me more pause. I see why the examples in
the email are bad. If two JSON_TABLE queries differ in column type, or in NULL
ON EMPTY vs ERROR ON EMPTY, merging them under one query ID does not look
right. But I don't think that means the whole column definition should become
part of the query ID.
For example, with an explicit PATH these two only differ in the output column
name:
```
SELECT *
FROM JSON_TABLE('[{"a": 1}]'::jsonb, '$[*]'
COLUMNS (a int PATH '$.a')) jt;
SELECT *
FROM JSON_TABLE('[{"a": 1}]'::jsonb, '$[*]'
COLUMNS (b int PATH '$.a')) jt;
```
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. It looks more like changing an output alias.
As a comparison, changing only the output column name for generate_series()
still merges into one entry:
```
SELECT pg_stat_statements_reset();
SELECT * FROM generate_series(1, 1) AS g(a);
SELECT * FROM generate_series(1, 1) AS g(b);
SELECT queryid, calls, query
FROM pg_stat_statements
WHERE query LIKE 'SELECT * FROM generate_series%'
ORDER BY query COLLATE "C";
```
which gives one query ID with calls = 2.
I also find the TableFunc.plan part a bit hard to get comfortable with. I
realize this is not a normal planner Plan node, but it is still generated
state for how JSON_TABLE is processed. AFAICS planner/path plan nodes, and even
SubPlan, are not query jumbled. So making pg_stat_statements walk all of that
feels broader than what this bug needs.
If we do want to do something here, maybe the fix needs to be more targeted.
For example, include the parts that change the result, such as the column type,
behavior clauses, and PLAN clause details, but not the output column name and
not every field in the generated JSON_TABLE state. Does that seem like the
right direction?
[1] https://www.postgresql.org/message-id/flat/086A90EE-9771-4387-8B46-BF84A610F04F%40localhost.localdomain
[2] https://www.postgresql.org/message-id/flat/DFE8E2CE-7A51-447C-B600-7D3979E38F1B%40localhost.localdomain
[3] https://www.postgresql.org/docs/current/pgstatstatements.html
--
Sami Imseih
Amazon Web Services (AWS)