pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query jumbling ignores XML names, ROLLUP/CUBE, VARIADIC and more
2+ messages / 2 participants
[nested] [flat]

* Query jumbling ignores XML names, ROLLUP/CUBE, VARIADIC and more
@ 2026-10-26 10:45  Staroverov Ilja <i.staroverov@ftdata.ru>
  0 siblings, 1 reply; 2+ messages in thread

From: Staroverov Ilja @ 2026-10-26 10:45 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: michael@paquier.xyz <michael@paquier.xyz>

Hi,

While working on the SQL/JSON patterns in pg_stat_statements [1], I
looked at the other fields marked query_jumble_ignore, and some of them
change the results of the queries.  So queries that differ in more than
their constants get the same query ID and are merged into a single
entry, although the documentation says that queries are considered the
same if they are semantically equivalent except for the values of
literal constants.  For example, on master as of 022a80fe6c:

    SELECT XMLELEMENT(NAME foo, 'x');
    SELECT XMLELEMENT(NAME bar, 'x');
    SELECT calls, query FROM pg_stat_statements
      WHERE query LIKE '%XMLELEMENT%';
     calls |              query
    -------+---------------------------------
         2 | SELECT XMLELEMENT(NAME foo, $1)

although the first query returns <foo>x</foo> and the second one
<bar>x</bar>.  The same happens with:

- 0002: the names in XMLATTRIBUTES, XMLFOREST and XMLPI, and DOCUMENT
  or CONTENT in XMLPARSE and XMLSERIALIZE;
- 0003: the column types, defaults, NOT NULL and FOR ORDINALITY of
  XMLTABLE, the number of its namespaces, and the names of its columns
  without PATH;
- 0004: GROUP BY ROLLUP (a, b), CUBE (a, b) and GROUPING SETS (a, b);
- 0005: the SEARCH and CYCLE clauses of recursive CTEs;
- 0006: the column definition lists of functions in FROM, like
  json_to_record(...) AS t(a int) and AS t(a text);
- 0007: VARIADIC with functions taking VARIADIC "any", like
  format('%s', VARIADIC ARRAY['a', 'b']), which returns "a", and the
  same without VARIADIC, which returns "{a,b}";
- 0008: the fields of a composite column assigned by INSERT or UPDATE,
  like UPDATE t SET c.x = 1 and UPDATE t SET c.y = 1;
- 0009: the composite type that a ROW() constructor is cast to;
- 0010: the operators of row comparisons, like < and ~<~ for text;
- 0011: OVERRIDING in INSERT and MERGE.

None of these has ever been part of the query ID, so this is not a
regression, and since fixing it changes query IDs, the attached patches
are for master only.  As Michael suggested in [1], 0001 adds the tests,
showing the current behavior, and each of 0002-0011 fixes one of these
cases, so that its diff of the expected output shows the entries being
split.  The SQL/XML tests go into a new file, xml.sql, with an alternate
output for builds without libxml, as in the main regression test
suite.

OVERRIDING was mentioned by Julien Rouhaud in 2020 [2], and Alvaro
Herrera thought in 2022 that it doesn't matter, as the column list is
part of the query ID [3].  But with OVERRIDING USER VALUE, the values
given for an identity column are ignored:

    CREATE TABLE t (a int GENERATED BY DEFAULT AS IDENTITY, b int);
    INSERT INTO t VALUES (100, 1);
    INSERT INTO t OVERRIDING USER VALUE VALUES (100, 1);
    TABLE t;
      a  | b
    -----+---
     100 | 1
       1 | 1

and both INSERTs are in the same entry.  Since Query.override is set
for all queries, 0011 changes all query IDs.  It comes last, so it can
be left out without touching the others.

As in the JSON_TABLE patch in [1], column names and namespace prefixes
are left out, for example the names given by SET and USING in SEARCH
and CYCLE, except where they change the results: XMLTABLE uses the name
of a column without PATH as its path, and json_to_record() and similar
functions use the names in the column definition list as the keys to
look up.

0003 also splits JSON_TABLE entries with different column types, as
JSON_TABLE sets TableFunc.coltypes too.  It overlaps with the JSON_TABLE
patch in [1], so whichever goes in second needs a rebase, which I can
do.

Two other kinds of merged queries are not addressed here, as they would
need a different approach.  The tests show the ones about SQL/XML.

First, some keywords are turned into Consts by the parser, and the
values of Consts are not part of the query ID.  This is the case of
STRIP or PRESERVE WHITESPACE in XMLPARSE, and of VERSION NO VALUE and
the STANDALONE option of XMLROOT, whose Consts have no location, so the
text of the entry shows the first query.  It is also the case of the
field of EXTRACT and of the normal form of IS NORMALIZED and
NORMALIZE(), whose Consts have the location of the keyword, so the
entry shows it as a parameter symbol, as reported in [4].

Second, type modifiers of casts are not part of the query ID either:
CAST(x AS varchar(10)) and CAST(x AS varchar(20)) are merged, as the
type modifier is only kept in a Const argument of the length coercion
function, or in Const.consttypmod for a literal.  The same goes for
XMLSERIALIZE(... AS varchar(10)).

[1] https://postgr.es/m/457969A9-5D24-422F-B09E-F388053DE028@localhost.localdomain
[2] https://postgr.es/m/CAOBaU_bt3-CwkAQLu7kUp-Yy7dyjhTntNG7Q30CssJHjaPYKEQ@mail.gmail.com
[3] https://postgr.es/m/20220926114629.fmdh4sblhpcrlnoo@alvherre.pgsql
[4] https://postgr.es/m/2a0a943877fe79eaf79f1ad752765823d95dce7e.camel@postgrespro.ru

--
Ilya Staroverov



Attachments: download all (11 files, .tar.gz)

v1-0001-pg_stat_statements-Add-tests-for-more-query-patte.patch (application/octet-stream, 56.1K) download From 1b1a9d08bafeeb02f0bca2b7595677779f17a189 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 12:40:42 +0300 Subject: [PATCH v1 01/11] pg_stat_statements: Add tests for more query patterns These tests show that some queries that differ in more than their constants are merged into a single entry, as what makes them different is not part of the query ID: - the names in XMLELEMENT, XMLATTRIBUTES, XMLFOREST and XMLPI, and DOCUMENT or CONTENT in XMLPARSE and XMLSERIALIZE; - the column types, defaults, NOT NULL and FOR ORDINALITY of XMLTABLE, its namespaces, and the names of its columns without PATH; - ROLLUP, CUBE and GROUPING SETS; - the SEARCH and CYCLE clauses of recursive CTEs; - the column definition lists of functions in FROM; - VARIADIC in calls of functions taking VARIADIC "any"; - the fields of a composite column assigned by INSERT or UPDATE; - the composite type that ROW() constructors are cast to, and the operators of row comparisons; - OVERRIDING in INSERT and MERGE. So are queries that differ in the WHITESPACE option of XMLPARSE, in VERSION NO VALUE and the STANDALONE option of XMLROOT, or in the type modifier of XMLSERIALIZE. The SQL/XML tests go into a new file, xml.sql, with an alternate output for builds without libxml, as in the main regression test suite. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/Makefile | 1 + contrib/pg_stat_statements/expected/dml.out | 67 +++ .../pg_stat_statements/expected/select.out | 380 ++++++++++++++ contrib/pg_stat_statements/expected/xml.out | 485 ++++++++++++++++++ contrib/pg_stat_statements/expected/xml_1.out | 445 ++++++++++++++++ contrib/pg_stat_statements/meson.build | 1 + contrib/pg_stat_statements/sql/dml.sql | 31 ++ contrib/pg_stat_statements/sql/select.sql | 93 ++++ contrib/pg_stat_statements/sql/xml.sql | 144 ++++++ 9 files changed, 1647 insertions(+) create mode 100644 contrib/pg_stat_statements/expected/xml.out create mode 100644 contrib/pg_stat_statements/expected/xml_1.out create mode 100644 contrib/pg_stat_statements/sql/xml.sql diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile index 2fe2a077ad..01ff05ea07 100644 --- a/contrib/pg_stat_statements/Makefile +++ b/contrib/pg_stat_statements/Makefile @@ -37,6 +37,7 @@ REGRESS = \ plancache \ squashing \ sqljson \ + xml \ cleanup \ oldextversions diff --git a/contrib/pg_stat_statements/expected/dml.out b/contrib/pg_stat_statements/expected/dml.out index 347cb8699e..b310a97b25 100644 --- a/contrib/pg_stat_statements/expected/dml.out +++ b/contrib/pg_stat_statements/expected/dml.out @@ -139,6 +139,73 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t (10 rows) +-- OVERRIDING +CREATE TEMP TABLE pgss_identity_tab (a int GENERATED BY DEFAULT AS IDENTITY, b int); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +INSERT INTO pgss_identity_tab VALUES (100, 1); +-- with OVERRIDING USER VALUE, a gets the next identity value +INSERT INTO pgss_identity_tab OVERRIDING USER VALUE VALUES (100, 1); +INSERT INTO pgss_identity_tab OVERRIDING SYSTEM VALUE VALUES (100, 1); +MERGE INTO pgss_identity_tab USING (SELECT 1 AS b) s ON false + WHEN NOT MATCHED THEN INSERT VALUES (100, s.b); +MERGE INTO pgss_identity_tab USING (SELECT 1 AS b) s ON false + WHEN NOT MATCHED THEN INSERT OVERRIDING USER VALUE VALUES (100, s.b); +MERGE INTO pgss_identity_tab USING (SELECT 1 AS b) s ON false + WHEN NOT MATCHED THEN INSERT OVERRIDING SYSTEM VALUE VALUES (100, s.b); +SELECT a, b FROM pgss_identity_tab ORDER BY a; + a | b +-----+--- + 1 | 1 + 2 | 1 + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 +(6 rows) + +DROP TABLE pgss_identity_tab; +SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+------------------------------------------------------------- + 3 | 3 | INSERT INTO pgss_identity_tab VALUES ($1, $2) + 3 | 3 | MERGE INTO pgss_identity_tab USING (SELECT $1 AS b) s ON $2+ + | | WHEN NOT MATCHED THEN INSERT VALUES ($3, s.b) + 1 | 6 | SELECT a, b FROM pgss_identity_tab ORDER BY a + 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(4 rows) + +-- assignments to fields of a composite column +CREATE TYPE pgss_point AS (x int, y int); +CREATE TEMP TABLE pgss_fieldstore_tab (c pgss_point); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +INSERT INTO pgss_fieldstore_tab (c.x) VALUES (1); +INSERT INTO pgss_fieldstore_tab (c.y) VALUES (2); +INSERT INTO pgss_fieldstore_tab (c.x, c.y) VALUES (3, 4); +UPDATE pgss_fieldstore_tab SET c.x = 5; +UPDATE pgss_fieldstore_tab SET c.y = 6; +UPDATE pgss_fieldstore_tab SET c.x = 7, c.y = 8; +DROP TABLE pgss_fieldstore_tab; +DROP TYPE pgss_point; +SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | rows | query +-------+------+------------------------------------------------------------ + 2 | 2 | INSERT INTO pgss_fieldstore_tab (c.x) VALUES ($1) + 1 | 1 | INSERT INTO pgss_fieldstore_tab (c.x, c.y) VALUES ($1, $2) + 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + 2 | 6 | UPDATE pgss_fieldstore_tab SET c.x = $1 + 1 | 3 | UPDATE pgss_fieldstore_tab SET c.x = $1, c.y = $2 +(5 rows) + -- check that [temp] table relation extensions are tracked as writes CREATE TABLE pgss_extend_tab (a int, b text); CREATE TEMP TABLE pgss_extend_temp_tab (a int, b text); diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index a069119c79..5961c894e9 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -889,3 +889,383 @@ SELECT pg_stat_statements_reset() IS NOT NULL AS t; t (1 row) +-- ROLLUP, CUBE and GROUPING SETS +CREATE TEMP TABLE pgss_empty_tab (a int, b int); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP (a, b); + a | b | count +---+---+------- + | | 0 +(1 row) + +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY CUBE (a, b); + a | b | count +---+---+------- + | | 0 +(1 row) + +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (a, b); + a | b | count +---+---+------- +(0 rows) + +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP ((a, b)); + a | b | count +---+---+------- + | | 0 +(1 row) + +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY CUBE ((a, b)); + a | b | count +---+---+------- + | | 0 +(1 row) + +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY a, ROLLUP (b); + a | b | count +---+---+------- +(0 rows) + +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY a, CUBE (b); + a | b | count +---+---+------- +(0 rows) + +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (ROLLUP (a), CUBE (b)); + a | b | count +---+---+------- + | | 0 + | | 0 +(2 rows) + +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (CUBE (a), ROLLUP (b)); + a | b | count +---+---+------- + | | 0 + | | 0 +(2 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | query +-------+----------------------------------------------------------------------------------------- + 2 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (ROLLUP (a), CUBE (b)) + 2 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP ((a, b)) + 3 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP (a, b) + 2 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY a, ROLLUP (b) + 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(6 rows) + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +-- WITH RECURSIVE ... SEARCH and CYCLE +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SELECT * FROM t; + a | b +---+--- +(0 rows) + +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY a SET seq SELECT * FROM t; + a | b | seq +---+---+----- +(0 rows) + +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH BREADTH FIRST BY a SET seq SELECT * FROM t; + a | b | seq +---+---+----- +(0 rows) + +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY b SET seq SELECT * FROM t; + a | b | seq +---+---+----- +(0 rows) + +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY a, b SET seq SELECT * FROM t; + a | b | seq +---+---+----- +(0 rows) + +-- only the name of the sequence column differs +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY a SET ord SELECT * FROM t; + a | b | ord +---+---+----- +(0 rows) + +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a SET is_cycle USING path SELECT * FROM t; + a | b | is_cycle | path +---+---+----------+------ +(0 rows) + +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE b SET is_cycle USING path SELECT * FROM t; + a | b | is_cycle | path +---+---+----------+------ +(0 rows) + +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a, b SET is_cycle USING path SELECT * FROM t; + a | b | is_cycle | path +---+---+----------+------ +(0 rows) + +-- TO true DEFAULT false is the default +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a SET is_cycle TO true DEFAULT false USING path SELECT * FROM t; + a | b | is_cycle | path +---+---+----------+------ +(0 rows) + +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a SET is_cycle TO 'Y' DEFAULT 'N' USING path SELECT * FROM t; + a | b | is_cycle | path +---+---+----------+------ +(0 rows) + +-- only the names of the mark and path columns differ +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a SET c USING p SELECT * FROM t; + a | b | c | p +---+---+---+--- +(0 rows) + +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY a SET seq CYCLE a SET is_cycle USING path SELECT * FROM t; + a | b | seq | is_cycle | path +---+---+-----+----------+------ +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | query +-------+----------------------------------------------------------------------------------------- + 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + 6 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | CYCLE a SET is_cycle USING path SELECT * FROM t + 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | SEARCH DEPTH FIRST BY a SET seq CYCLE a SET is_cycle USING path SELECT * FROM t + 5 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | SEARCH DEPTH FIRST BY a SET seq SELECT * FROM t + 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | SELECT * FROM t +(6 rows) + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +-- column definition lists +SELECT * FROM json_to_record('{"a": 1}') AS t(a int); + a +--- + 1 +(1 row) + +-- only the constant differs +SELECT * FROM json_to_record('{"a": 2}') AS t(a int); + a +--- + 2 +(1 row) + +SELECT * FROM json_to_record('{"a": 1}') AS t(a text); + a +--- + 1 +(1 row) + +SELECT * FROM json_to_record('{"a": 1}') AS t(a varchar(3)); + a +--- + 1 +(1 row) + +SELECT * FROM json_to_record('{"a": 1}') AS t(a varchar(4)); + a +--- + 1 +(1 row) + +SELECT * FROM json_to_record('{"a": 1}') AS t(a text COLLATE "C"); + a +--- + 1 +(1 row) + +-- the column names are the keys to look up +SELECT * FROM json_to_record('{"a": 1}') AS t(b int); + b +--- + +(1 row) + +SELECT * FROM json_to_record('{"a": 1}') AS t(a int, b int); + a | b +---+--- + 1 | +(1 row) + +SELECT * FROM ROWS FROM (json_to_record('{"a": 1}') AS (a int), + json_to_record('{"b": 1}') AS (b int)) AS t; + a | b +---+--- + 1 | 1 +(1 row) + +SELECT * FROM ROWS FROM (json_to_record('{"a": 1}') AS (a int), + json_to_record('{"b": 1}') AS (b text)) AS t; + a | b +---+--- + 1 | 1 +(1 row) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | query +-------+------------------------------------------------------------------------ + 2 | SELECT * FROM ROWS FROM (json_to_record($1) AS (a int), + + | json_to_record($2) AS (b int)) AS t + 7 | SELECT * FROM json_to_record($1) AS t(a int) + 1 | SELECT * FROM json_to_record($1) AS t(a int, b int) + 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(5 rows) + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +-- VARIADIC +SELECT format('%s', VARIADIC ARRAY['a', 'b']); + format +-------- + a +(1 row) + +SELECT format('%s', ARRAY['a', 'b']); + format +-------- + {a,b} +(1 row) + +SELECT concat(VARIADIC ARRAY['a', 'b']); + concat +-------- + ab +(1 row) + +SELECT concat(ARRAY['a', 'b']); + concat +-------- + {a,b} +(1 row) + +-- VARIADIC is the same as separate arguments, except for VARIADIC "any" +SELECT jsonb_extract_path('{}', a::text, b::text) FROM pgss_empty_tab; + jsonb_extract_path +-------------------- +(0 rows) + +SELECT jsonb_extract_path('{}', VARIADIC ARRAY[a::text, b::text]) + FROM pgss_empty_tab; + jsonb_extract_path +-------------------- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | query +-------+------------------------------------------------------------------------ + 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" + 2 | SELECT concat(VARIADIC ARRAY[$1 /*, ... */]) + 2 | SELECT format($1, VARIADIC ARRAY[$2 /*, ... */]) + 2 | SELECT jsonb_extract_path($1, a::text, b::text) FROM pgss_empty_tab + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(5 rows) + +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +-- row constructors and row comparisons +CREATE TYPE pgss_pair1 AS (x int, y int); +CREATE TYPE pgss_pair2 AS (p int, q int); +SELECT (ROW(1, 2)::pgss_pair1).*; + x | y +---+--- + 1 | 2 +(1 row) + +SELECT (ROW(1, 2)::pgss_pair2).*; + p | q +---+--- + 1 | 2 +(1 row) + +SELECT ROW(1, 2); + row +------- + (1,2) +(1 row) + +SELECT ROW(1, 2)::pgss_pair1; + row +------- + (1,2) +(1 row) + +SELECT ROW(s, t) < ROW('b', 'b') FROM (VALUES ('a', 'a')) AS v(s, t); + ?column? +---------- + t +(1 row) + +SELECT ROW(s, t) ~<~ ROW('b', 'b') FROM (VALUES ('a', 'a')) AS v(s, t); + ?column? +---------- + t +(1 row) + +SELECT ROW(s, t) <= ROW('b', 'b') FROM (VALUES ('a', 'a')) AS v(s, t); + ?column? +---------- + t +(1 row) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + calls | query +-------+------------------------------------------------------------------------ + 2 | SELECT (ROW($1, $2)::pgss_pair1).* + 2 | SELECT ROW($1, $2) + 2 | SELECT ROW(s, t) < ROW($1, $2) FROM (VALUES ($3, $4)) AS v(s, t) + 1 | SELECT ROW(s, t) <= ROW($1, $2) FROM (VALUES ($3, $4)) AS v(s, t) + 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(6 rows) + +DROP TYPE pgss_pair1, pgss_pair2; +DROP TABLE pgss_empty_tab; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + diff --git a/contrib/pg_stat_statements/expected/xml.out b/contrib/pg_stat_statements/expected/xml.out new file mode 100644 index 0000000000..6c4c34b2c5 --- /dev/null +++ b/contrib/pg_stat_statements/expected/xml.out @@ -0,0 +1,485 @@ +-- +-- SQL/XML +-- +SET pg_stat_statements.track_utility = FALSE; +CREATE TABLE pgss_xml_tab (x xml, t text); +-- +-- SQL/XML functions +-- +-- XMLELEMENT, XMLATTRIBUTES, XMLFOREST and XMLPI names +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT XMLELEMENT(NAME foo) FROM pgss_xml_tab; + xmlelement +------------ +(0 rows) + +SELECT XMLELEMENT(NAME bar) FROM pgss_xml_tab; + xmlelement +------------ +(0 rows) + +SELECT XMLELEMENT(NAME foo, t) FROM pgss_xml_tab; + xmlelement +------------ +(0 rows) + +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a)) FROM pgss_xml_tab; + xmlelement +------------ +(0 rows) + +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS b)) FROM pgss_xml_tab; + xmlelement +------------ +(0 rows) + +-- the attribute name defaults to the column name +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t)) FROM pgss_xml_tab; + xmlelement +------------ +(0 rows) + +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS t)) FROM pgss_xml_tab; + xmlelement +------------ +(0 rows) + +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a), t) FROM pgss_xml_tab; + xmlelement +------------ +(0 rows) + +SELECT XMLFOREST(t AS a) FROM pgss_xml_tab; + xmlforest +----------- +(0 rows) + +SELECT XMLFOREST(t AS b) FROM pgss_xml_tab; + xmlforest +----------- +(0 rows) + +-- the element name defaults to the column name +SELECT XMLFOREST(t) FROM pgss_xml_tab; + xmlforest +----------- +(0 rows) + +SELECT XMLFOREST(t AS t) FROM pgss_xml_tab; + xmlforest +----------- +(0 rows) + +SELECT XMLFOREST(t AS a, x AS b) FROM pgss_xml_tab; + xmlforest +----------- +(0 rows) + +SELECT XMLPI(NAME foo) FROM pgss_xml_tab; + xmlpi +------- +(0 rows) + +SELECT XMLPI(NAME bar) FROM pgss_xml_tab; + xmlpi +------- +(0 rows) + +SELECT XMLPI(NAME foo, t) FROM pgss_xml_tab; + xmlpi +------- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+------------------------------------------------------------------------- + 2 | SELECT XMLELEMENT(NAME foo) FROM pgss_xml_tab + 4 | SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a)) FROM pgss_xml_tab + 1 | SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a), t) FROM pgss_xml_tab + 1 | SELECT XMLELEMENT(NAME foo, t) FROM pgss_xml_tab + 4 | SELECT XMLFOREST(t AS a) FROM pgss_xml_tab + 1 | SELECT XMLFOREST(t AS a, x AS b) FROM pgss_xml_tab + 2 | SELECT XMLPI(NAME foo) FROM pgss_xml_tab + 1 | SELECT XMLPI(NAME foo, t) FROM pgss_xml_tab + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(9 rows) + +-- XMLPARSE and XMLSERIALIZE +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT XMLPARSE(DOCUMENT t) FROM pgss_xml_tab; + xmlparse +---------- +(0 rows) + +SELECT XMLPARSE(CONTENT t) FROM pgss_xml_tab; + xmlparse +---------- +(0 rows) + +-- STRIP WHITESPACE is the default +SELECT XMLPARSE(CONTENT t STRIP WHITESPACE) FROM pgss_xml_tab; + xmlparse +---------- +(0 rows) + +SELECT XMLPARSE(CONTENT t PRESERVE WHITESPACE) FROM pgss_xml_tab; + xmlparse +---------- +(0 rows) + +SELECT XMLSERIALIZE(DOCUMENT x AS text) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT XMLSERIALIZE(CONTENT x AS text) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +-- NO INDENT is the default +SELECT XMLSERIALIZE(CONTENT x AS text NO INDENT) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT XMLSERIALIZE(CONTENT x AS text INDENT) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT XMLSERIALIZE(CONTENT x AS varchar(10)) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT XMLSERIALIZE(CONTENT x AS varchar(20)) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+----------------------------------------------------------------- + 4 | SELECT XMLPARSE(DOCUMENT t) FROM pgss_xml_tab + 1 | SELECT XMLSERIALIZE(CONTENT x AS text INDENT) FROM pgss_xml_tab + 2 | SELECT XMLSERIALIZE(CONTENT x AS varchar(10)) FROM pgss_xml_tab + 3 | SELECT XMLSERIALIZE(DOCUMENT x AS text) FROM pgss_xml_tab + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(5 rows) + +-- XMLROOT +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT XMLROOT(x, VERSION '1.0') FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION '1.1') FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION NO VALUE) FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION '1.0', STANDALONE YES) FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION '1.0', STANDALONE NO) FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION '1.0', STANDALONE NO VALUE) FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+---------------------------------------------------- + 6 | SELECT XMLROOT(x, VERSION $1) FROM pgss_xml_tab + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +-- XMLCONCAT, IS DOCUMENT and XMLEXISTS +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT XMLCONCAT(x, x) FROM pgss_xml_tab; + xmlconcat +----------- +(0 rows) + +SELECT XMLCONCAT(x, x, x) FROM pgss_xml_tab; + xmlconcat +----------- +(0 rows) + +SELECT x IS DOCUMENT FROM pgss_xml_tab; + ?column? +---------- +(0 rows) + +SELECT x IS NOT DOCUMENT FROM pgss_xml_tab; + ?column? +---------- +(0 rows) + +SELECT XMLEXISTS('/r' PASSING x) FROM pgss_xml_tab; + xmlexists +----------- +(0 rows) + +-- BY REF and BY VALUE are noise words +SELECT XMLEXISTS('/r' PASSING BY REF x) FROM pgss_xml_tab; + xmlexists +----------- +(0 rows) + +SELECT XMLEXISTS('/r' PASSING BY VALUE x BY VALUE) FROM pgss_xml_tab; + xmlexists +----------- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+---------------------------------------------------- + 1 | SELECT XMLCONCAT(x, x) FROM pgss_xml_tab + 1 | SELECT XMLCONCAT(x, x, x) FROM pgss_xml_tab + 3 | SELECT XMLEXISTS($1 PASSING x) FROM pgss_xml_tab + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + 1 | SELECT x IS DOCUMENT FROM pgss_xml_tab + 1 | SELECT x IS NOT DOCUMENT FROM pgss_xml_tab +(6 rows) + +-- +-- XMLTABLE +-- +-- column types, defaults and NOT NULL +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a text PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a varchar(3) PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a varchar(4) PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' DEFAULT 1) xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' DEFAULT 2) xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' NOT NULL) xt; + x | t | a +---+---+--- +(0 rows) + +-- NULL is the default +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' NULL) xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' DEFAULT 1 NOT NULL) xt; + x | t | a +---+---+--- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+---------------------------------------------------- + 9 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2) xt + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +-- FOR ORDINALITY, column names and paths +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a', b int PATH 'b') xt; + x | t | a | b +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a FOR ORDINALITY, b int PATH 'b') xt; + x | t | a | b +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a', b FOR ORDINALITY) xt; + x | t | a | b +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS o FOR ORDINALITY, a int) xt; + x | t | o | a +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int, o FOR ORDINALITY) xt; + x | t | a | o +---+---+---+--- +(0 rows) + +-- only the column names and the paths differ +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS c int PATH 'c', d int PATH 'd') xt; + x | t | c | d +---+---+---+--- +(0 rows) + +-- without PATH, the path is the column name +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int, b int) xt; + x | t | a | b +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS c int, d int) xt; + x | t | c | d +---+---+---+--- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+--------------------------------------------------------------------- + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a FOR ORDINALITY, b int PATH $2) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2, b FOR ORDINALITY) xt + 2 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2, b int PATH $3) xt + 4 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS o FOR ORDINALITY, a int) xt + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(5 rows) + +-- XMLNAMESPACES and PASSING +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE(XMLNAMESPACES('http://x' AS n), '/n:r' + PASSING x COLUMNS a int PATH 'n:a') xt; + x | t | a +---+---+--- +(0 rows) + +-- only the namespace, its name and the paths differ +SELECT * FROM pgss_xml_tab, + XMLTABLE(XMLNAMESPACES('http://y' AS m), '/m:r' + PASSING x COLUMNS a int PATH 'm:a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE(XMLNAMESPACES('http://x' AS n, 'http://y' AS m), '/n:r' + PASSING x COLUMNS a int PATH 'm:a') xt; + x | t | a +---+---+--- +(0 rows) + +-- BY REF and BY VALUE are noise words +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING BY REF x COLUMNS a int PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING BY VALUE x BY VALUE COLUMNS a int PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+---------------------------------------------------- + 6 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2) xt + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +DROP TABLE pgss_xml_tab; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + diff --git a/contrib/pg_stat_statements/expected/xml_1.out b/contrib/pg_stat_statements/expected/xml_1.out new file mode 100644 index 0000000000..86152a84c8 --- /dev/null +++ b/contrib/pg_stat_statements/expected/xml_1.out @@ -0,0 +1,445 @@ +-- +-- SQL/XML +-- +SET pg_stat_statements.track_utility = FALSE; +CREATE TABLE pgss_xml_tab (x xml, t text); +-- +-- SQL/XML functions +-- +-- XMLELEMENT, XMLATTRIBUTES, XMLFOREST and XMLPI names +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT XMLELEMENT(NAME foo) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLELEMENT(NAME bar) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLELEMENT(NAME foo, t) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a)) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS b)) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +-- the attribute name defaults to the column name +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t)) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS t)) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a), t) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLFOREST(t AS a) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLFOREST(t AS b) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +-- the element name defaults to the column name +SELECT XMLFOREST(t) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLFOREST(t AS t) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLFOREST(t AS a, x AS b) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLPI(NAME foo) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLPI(NAME bar) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT XMLPI(NAME foo, t) FROM pgss_xml_tab; +ERROR: unsupported XML feature +DETAIL: This functionality requires the server to be built with libxml support. +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+---------------------------------------------------- + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(1 row) + +-- XMLPARSE and XMLSERIALIZE +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT XMLPARSE(DOCUMENT t) FROM pgss_xml_tab; + xmlparse +---------- +(0 rows) + +SELECT XMLPARSE(CONTENT t) FROM pgss_xml_tab; + xmlparse +---------- +(0 rows) + +-- STRIP WHITESPACE is the default +SELECT XMLPARSE(CONTENT t STRIP WHITESPACE) FROM pgss_xml_tab; + xmlparse +---------- +(0 rows) + +SELECT XMLPARSE(CONTENT t PRESERVE WHITESPACE) FROM pgss_xml_tab; + xmlparse +---------- +(0 rows) + +SELECT XMLSERIALIZE(DOCUMENT x AS text) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT XMLSERIALIZE(CONTENT x AS text) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +-- NO INDENT is the default +SELECT XMLSERIALIZE(CONTENT x AS text NO INDENT) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT XMLSERIALIZE(CONTENT x AS text INDENT) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT XMLSERIALIZE(CONTENT x AS varchar(10)) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT XMLSERIALIZE(CONTENT x AS varchar(20)) FROM pgss_xml_tab; + xmlserialize +-------------- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+----------------------------------------------------------------- + 4 | SELECT XMLPARSE(DOCUMENT t) FROM pgss_xml_tab + 1 | SELECT XMLSERIALIZE(CONTENT x AS text INDENT) FROM pgss_xml_tab + 2 | SELECT XMLSERIALIZE(CONTENT x AS varchar(10)) FROM pgss_xml_tab + 3 | SELECT XMLSERIALIZE(DOCUMENT x AS text) FROM pgss_xml_tab + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(5 rows) + +-- XMLROOT +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT XMLROOT(x, VERSION '1.0') FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION '1.1') FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION NO VALUE) FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION '1.0', STANDALONE YES) FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION '1.0', STANDALONE NO) FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT XMLROOT(x, VERSION '1.0', STANDALONE NO VALUE) FROM pgss_xml_tab; + xmlroot +--------- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+---------------------------------------------------- + 6 | SELECT XMLROOT(x, VERSION $1) FROM pgss_xml_tab + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +-- XMLCONCAT, IS DOCUMENT and XMLEXISTS +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT XMLCONCAT(x, x) FROM pgss_xml_tab; + xmlconcat +----------- +(0 rows) + +SELECT XMLCONCAT(x, x, x) FROM pgss_xml_tab; + xmlconcat +----------- +(0 rows) + +SELECT x IS DOCUMENT FROM pgss_xml_tab; + ?column? +---------- +(0 rows) + +SELECT x IS NOT DOCUMENT FROM pgss_xml_tab; + ?column? +---------- +(0 rows) + +SELECT XMLEXISTS('/r' PASSING x) FROM pgss_xml_tab; + xmlexists +----------- +(0 rows) + +-- BY REF and BY VALUE are noise words +SELECT XMLEXISTS('/r' PASSING BY REF x) FROM pgss_xml_tab; + xmlexists +----------- +(0 rows) + +SELECT XMLEXISTS('/r' PASSING BY VALUE x BY VALUE) FROM pgss_xml_tab; + xmlexists +----------- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+---------------------------------------------------- + 1 | SELECT XMLCONCAT(x, x) FROM pgss_xml_tab + 1 | SELECT XMLCONCAT(x, x, x) FROM pgss_xml_tab + 3 | SELECT XMLEXISTS($1 PASSING x) FROM pgss_xml_tab + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t + 1 | SELECT x IS DOCUMENT FROM pgss_xml_tab + 1 | SELECT x IS NOT DOCUMENT FROM pgss_xml_tab +(6 rows) + +-- +-- XMLTABLE +-- +-- column types, defaults and NOT NULL +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a text PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a varchar(3) PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a varchar(4) PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' DEFAULT 1) xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' DEFAULT 2) xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' NOT NULL) xt; + x | t | a +---+---+--- +(0 rows) + +-- NULL is the default +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' NULL) xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' DEFAULT 1 NOT NULL) xt; + x | t | a +---+---+--- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+---------------------------------------------------- + 9 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2) xt + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +-- FOR ORDINALITY, column names and paths +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a', b int PATH 'b') xt; + x | t | a | b +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a FOR ORDINALITY, b int PATH 'b') xt; + x | t | a | b +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a', b FOR ORDINALITY) xt; + x | t | a | b +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS o FOR ORDINALITY, a int) xt; + x | t | o | a +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int, o FOR ORDINALITY) xt; + x | t | a | o +---+---+---+--- +(0 rows) + +-- only the column names and the paths differ +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS c int PATH 'c', d int PATH 'd') xt; + x | t | c | d +---+---+---+--- +(0 rows) + +-- without PATH, the path is the column name +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int, b int) xt; + x | t | a | b +---+---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS c int, d int) xt; + x | t | c | d +---+---+---+--- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+--------------------------------------------------------------------- + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a FOR ORDINALITY, b int PATH $2) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2, b FOR ORDINALITY) xt + 2 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2, b int PATH $3) xt + 4 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS o FOR ORDINALITY, a int) xt + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(5 rows) + +-- XMLNAMESPACES and PASSING +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE(XMLNAMESPACES('http://x' AS n), '/n:r' + PASSING x COLUMNS a int PATH 'n:a') xt; + x | t | a +---+---+--- +(0 rows) + +-- only the namespace, its name and the paths differ +SELECT * FROM pgss_xml_tab, + XMLTABLE(XMLNAMESPACES('http://y' AS m), '/m:r' + PASSING x COLUMNS a int PATH 'm:a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE(XMLNAMESPACES('http://x' AS n, 'http://y' AS m), '/n:r' + PASSING x COLUMNS a int PATH 'm:a') xt; + x | t | a +---+---+--- +(0 rows) + +-- BY REF and BY VALUE are noise words +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING BY REF x COLUMNS a int PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING BY VALUE x BY VALUE COLUMNS a int PATH 'a') xt; + x | t | a +---+---+--- +(0 rows) + +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + calls | query +-------+---------------------------------------------------- + 6 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2) xt + 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t +(2 rows) + +DROP TABLE pgss_xml_tab; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + t +--- + t +(1 row) + diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build index a2f957836a..923f08f670 100644 --- a/contrib/pg_stat_statements/meson.build +++ b/contrib/pg_stat_statements/meson.build @@ -59,6 +59,7 @@ tests += { 'plancache', 'squashing', 'sqljson', + 'xml', 'cleanup', 'oldextversions', ], diff --git a/contrib/pg_stat_statements/sql/dml.sql b/contrib/pg_stat_statements/sql/dml.sql index 9986b0a22d..5d64e238af 100644 --- a/contrib/pg_stat_statements/sql/dml.sql +++ b/contrib/pg_stat_statements/sql/dml.sql @@ -74,6 +74,37 @@ DROP TABLE pgss_dml_tab; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +-- OVERRIDING +CREATE TEMP TABLE pgss_identity_tab (a int GENERATED BY DEFAULT AS IDENTITY, b int); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +INSERT INTO pgss_identity_tab VALUES (100, 1); +-- with OVERRIDING USER VALUE, a gets the next identity value +INSERT INTO pgss_identity_tab OVERRIDING USER VALUE VALUES (100, 1); +INSERT INTO pgss_identity_tab OVERRIDING SYSTEM VALUE VALUES (100, 1); +MERGE INTO pgss_identity_tab USING (SELECT 1 AS b) s ON false + WHEN NOT MATCHED THEN INSERT VALUES (100, s.b); +MERGE INTO pgss_identity_tab USING (SELECT 1 AS b) s ON false + WHEN NOT MATCHED THEN INSERT OVERRIDING USER VALUE VALUES (100, s.b); +MERGE INTO pgss_identity_tab USING (SELECT 1 AS b) s ON false + WHEN NOT MATCHED THEN INSERT OVERRIDING SYSTEM VALUE VALUES (100, s.b); +SELECT a, b FROM pgss_identity_tab ORDER BY a; +DROP TABLE pgss_identity_tab; +SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + +-- assignments to fields of a composite column +CREATE TYPE pgss_point AS (x int, y int); +CREATE TEMP TABLE pgss_fieldstore_tab (c pgss_point); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +INSERT INTO pgss_fieldstore_tab (c.x) VALUES (1); +INSERT INTO pgss_fieldstore_tab (c.y) VALUES (2); +INSERT INTO pgss_fieldstore_tab (c.x, c.y) VALUES (3, 4); +UPDATE pgss_fieldstore_tab SET c.x = 5; +UPDATE pgss_fieldstore_tab SET c.y = 6; +UPDATE pgss_fieldstore_tab SET c.x = 7, c.y = 8; +DROP TABLE pgss_fieldstore_tab; +DROP TYPE pgss_point; +SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; + -- check that [temp] table relation extensions are tracked as writes CREATE TABLE pgss_extend_tab (a int, b text); CREATE TEMP TABLE pgss_extend_temp_tab (a int, b text); diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql index a10d618c03..b8d2d457d7 100644 --- a/contrib/pg_stat_statements/sql/select.sql +++ b/contrib/pg_stat_statements/sql/select.sql @@ -269,3 +269,96 @@ DROP SCHEMA pgss_schema_1 CASCADE; DROP SCHEMA pgss_schema_2 CASCADE; DROP TABLE tab_search_same, tab_search_diff_1, tab_search_diff_2; SELECT pg_stat_statements_reset() IS NOT NULL AS t; + +-- ROLLUP, CUBE and GROUPING SETS +CREATE TEMP TABLE pgss_empty_tab (a int, b int); +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP (a, b); +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY CUBE (a, b); +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (a, b); +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP ((a, b)); +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY CUBE ((a, b)); +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY a, ROLLUP (b); +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY a, CUBE (b); +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (ROLLUP (a), CUBE (b)); +SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (CUBE (a), ROLLUP (b)); +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + +-- WITH RECURSIVE ... SEARCH and CYCLE +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SELECT * FROM t; +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY a SET seq SELECT * FROM t; +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH BREADTH FIRST BY a SET seq SELECT * FROM t; +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY b SET seq SELECT * FROM t; +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY a, b SET seq SELECT * FROM t; +-- only the name of the sequence column differs +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY a SET ord SELECT * FROM t; +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a SET is_cycle USING path SELECT * FROM t; +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE b SET is_cycle USING path SELECT * FROM t; +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a, b SET is_cycle USING path SELECT * FROM t; +-- TO true DEFAULT false is the default +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a SET is_cycle TO true DEFAULT false USING path SELECT * FROM t; +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a SET is_cycle TO 'Y' DEFAULT 'N' USING path SELECT * FROM t; +-- only the names of the mark and path columns differ +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + CYCLE a SET c USING p SELECT * FROM t; +WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + 1, b FROM t) + SEARCH DEPTH FIRST BY a SET seq CYCLE a SET is_cycle USING path SELECT * FROM t; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + +-- column definition lists +SELECT * FROM json_to_record('{"a": 1}') AS t(a int); +-- only the constant differs +SELECT * FROM json_to_record('{"a": 2}') AS t(a int); +SELECT * FROM json_to_record('{"a": 1}') AS t(a text); +SELECT * FROM json_to_record('{"a": 1}') AS t(a varchar(3)); +SELECT * FROM json_to_record('{"a": 1}') AS t(a varchar(4)); +SELECT * FROM json_to_record('{"a": 1}') AS t(a text COLLATE "C"); +-- the column names are the keys to look up +SELECT * FROM json_to_record('{"a": 1}') AS t(b int); +SELECT * FROM json_to_record('{"a": 1}') AS t(a int, b int); +SELECT * FROM ROWS FROM (json_to_record('{"a": 1}') AS (a int), + json_to_record('{"b": 1}') AS (b int)) AS t; +SELECT * FROM ROWS FROM (json_to_record('{"a": 1}') AS (a int), + json_to_record('{"b": 1}') AS (b text)) AS t; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + +-- VARIADIC +SELECT format('%s', VARIADIC ARRAY['a', 'b']); +SELECT format('%s', ARRAY['a', 'b']); +SELECT concat(VARIADIC ARRAY['a', 'b']); +SELECT concat(ARRAY['a', 'b']); +-- VARIADIC is the same as separate arguments, except for VARIADIC "any" +SELECT jsonb_extract_path('{}', a::text, b::text) FROM pgss_empty_tab; +SELECT jsonb_extract_path('{}', VARIADIC ARRAY[a::text, b::text]) + FROM pgss_empty_tab; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; + +-- row constructors and row comparisons +CREATE TYPE pgss_pair1 AS (x int, y int); +CREATE TYPE pgss_pair2 AS (p int, q int); +SELECT (ROW(1, 2)::pgss_pair1).*; +SELECT (ROW(1, 2)::pgss_pair2).*; +SELECT ROW(1, 2); +SELECT ROW(1, 2)::pgss_pair1; +SELECT ROW(s, t) < ROW('b', 'b') FROM (VALUES ('a', 'a')) AS v(s, t); +SELECT ROW(s, t) ~<~ ROW('b', 'b') FROM (VALUES ('a', 'a')) AS v(s, t); +SELECT ROW(s, t) <= ROW('b', 'b') FROM (VALUES ('a', 'a')) AS v(s, t); +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; +DROP TYPE pgss_pair1, pgss_pair2; +DROP TABLE pgss_empty_tab; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/contrib/pg_stat_statements/sql/xml.sql b/contrib/pg_stat_statements/sql/xml.sql new file mode 100644 index 0000000000..8eaa665e24 --- /dev/null +++ b/contrib/pg_stat_statements/sql/xml.sql @@ -0,0 +1,144 @@ +-- +-- SQL/XML +-- + +SET pg_stat_statements.track_utility = FALSE; + +CREATE TABLE pgss_xml_tab (x xml, t text); + +-- +-- SQL/XML functions +-- + +-- XMLELEMENT, XMLATTRIBUTES, XMLFOREST and XMLPI names +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT XMLELEMENT(NAME foo) FROM pgss_xml_tab; +SELECT XMLELEMENT(NAME bar) FROM pgss_xml_tab; +SELECT XMLELEMENT(NAME foo, t) FROM pgss_xml_tab; +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a)) FROM pgss_xml_tab; +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS b)) FROM pgss_xml_tab; +-- the attribute name defaults to the column name +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t)) FROM pgss_xml_tab; +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS t)) FROM pgss_xml_tab; +SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a), t) FROM pgss_xml_tab; +SELECT XMLFOREST(t AS a) FROM pgss_xml_tab; +SELECT XMLFOREST(t AS b) FROM pgss_xml_tab; +-- the element name defaults to the column name +SELECT XMLFOREST(t) FROM pgss_xml_tab; +SELECT XMLFOREST(t AS t) FROM pgss_xml_tab; +SELECT XMLFOREST(t AS a, x AS b) FROM pgss_xml_tab; +SELECT XMLPI(NAME foo) FROM pgss_xml_tab; +SELECT XMLPI(NAME bar) FROM pgss_xml_tab; +SELECT XMLPI(NAME foo, t) FROM pgss_xml_tab; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + +-- XMLPARSE and XMLSERIALIZE +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT XMLPARSE(DOCUMENT t) FROM pgss_xml_tab; +SELECT XMLPARSE(CONTENT t) FROM pgss_xml_tab; +-- STRIP WHITESPACE is the default +SELECT XMLPARSE(CONTENT t STRIP WHITESPACE) FROM pgss_xml_tab; +SELECT XMLPARSE(CONTENT t PRESERVE WHITESPACE) FROM pgss_xml_tab; +SELECT XMLSERIALIZE(DOCUMENT x AS text) FROM pgss_xml_tab; +SELECT XMLSERIALIZE(CONTENT x AS text) FROM pgss_xml_tab; +-- NO INDENT is the default +SELECT XMLSERIALIZE(CONTENT x AS text NO INDENT) FROM pgss_xml_tab; +SELECT XMLSERIALIZE(CONTENT x AS text INDENT) FROM pgss_xml_tab; +SELECT XMLSERIALIZE(CONTENT x AS varchar(10)) FROM pgss_xml_tab; +SELECT XMLSERIALIZE(CONTENT x AS varchar(20)) FROM pgss_xml_tab; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + +-- XMLROOT +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT XMLROOT(x, VERSION '1.0') FROM pgss_xml_tab; +SELECT XMLROOT(x, VERSION '1.1') FROM pgss_xml_tab; +SELECT XMLROOT(x, VERSION NO VALUE) FROM pgss_xml_tab; +SELECT XMLROOT(x, VERSION '1.0', STANDALONE YES) FROM pgss_xml_tab; +SELECT XMLROOT(x, VERSION '1.0', STANDALONE NO) FROM pgss_xml_tab; +SELECT XMLROOT(x, VERSION '1.0', STANDALONE NO VALUE) FROM pgss_xml_tab; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + +-- XMLCONCAT, IS DOCUMENT and XMLEXISTS +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT XMLCONCAT(x, x) FROM pgss_xml_tab; +SELECT XMLCONCAT(x, x, x) FROM pgss_xml_tab; +SELECT x IS DOCUMENT FROM pgss_xml_tab; +SELECT x IS NOT DOCUMENT FROM pgss_xml_tab; +SELECT XMLEXISTS('/r' PASSING x) FROM pgss_xml_tab; +-- BY REF and BY VALUE are noise words +SELECT XMLEXISTS('/r' PASSING BY REF x) FROM pgss_xml_tab; +SELECT XMLEXISTS('/r' PASSING BY VALUE x BY VALUE) FROM pgss_xml_tab; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + +-- +-- XMLTABLE +-- + +-- column types, defaults and NOT NULL +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a') xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a text PATH 'a') xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a varchar(3) PATH 'a') xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a varchar(4) PATH 'a') xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' DEFAULT 1) xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' DEFAULT 2) xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' NOT NULL) xt; +-- NULL is the default +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' NULL) xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a' DEFAULT 1 NOT NULL) xt; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + +-- FOR ORDINALITY, column names and paths +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a', b int PATH 'b') xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a FOR ORDINALITY, b int PATH 'b') xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a', b FOR ORDINALITY) xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS o FOR ORDINALITY, a int) xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int, o FOR ORDINALITY) xt; +-- only the column names and the paths differ +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS c int PATH 'c', d int PATH 'd') xt; +-- without PATH, the path is the column name +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int, b int) xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS c int, d int) xt; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + +-- XMLNAMESPACES and PASSING +SELECT pg_stat_statements_reset() IS NOT NULL AS t; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING x COLUMNS a int PATH 'a') xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE(XMLNAMESPACES('http://x' AS n), '/n:r' + PASSING x COLUMNS a int PATH 'n:a') xt; +-- only the namespace, its name and the paths differ +SELECT * FROM pgss_xml_tab, + XMLTABLE(XMLNAMESPACES('http://y' AS m), '/m:r' + PASSING x COLUMNS a int PATH 'm:a') xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE(XMLNAMESPACES('http://x' AS n, 'http://y' AS m), '/n:r' + PASSING x COLUMNS a int PATH 'm:a') xt; +-- BY REF and BY VALUE are noise words +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING BY REF x COLUMNS a int PATH 'a') xt; +SELECT * FROM pgss_xml_tab, + XMLTABLE('/r' PASSING BY VALUE x BY VALUE COLUMNS a int PATH 'a') xt; +SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; + +DROP TABLE pgss_xml_tab; +SELECT pg_stat_statements_reset() IS NOT NULL AS t; base-commit: 022a80fe6c1d82f7777bd52f9d3e5f9952533109 -- 2.43.0
v1-0002-Include-SQL-XML-names-and-DOCUMENT-or-CONTENT-in-.patch (application/octet-stream, 6.1K) download From 5b6ad1924392fc33e424b449d9b6edb25f0a0e18 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 12:44:29 +0300 Subject: [PATCH v1 02/11] Include SQL/XML names and DOCUMENT or CONTENT in query jumbling XmlExpr.name, XmlExpr.arg_names and XmlExpr.xmloption have not been part of the query ID since query jumbling was added to pg_stat_statements in 9.2. But they change the results: the names are those of the elements and attributes built by XMLELEMENT, XMLATTRIBUTES, XMLFOREST and XMLPI, and with DOCUMENT, XMLPARSE and XMLSERIALIZE check that the value is a document. So queries that differ only in these, like XMLELEMENT(NAME foo) and XMLELEMENT(NAME bar), were merged into a single pg_stat_statements entry. Include these fields in query jumbling. XmlExpr.type and XmlExpr.typmod are still left out: the result of XMLSERIALIZE is coerced to the target type by a separate node, the same way as for a cast. This changes the query IDs of the queries using any SQL/XML function implemented by XmlExpr, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/expected/xml.out | 24 ++++++++++++------- contrib/pg_stat_statements/expected/xml_1.out | 8 ++++--- src/include/nodes/primnodes.h | 6 ++--- 3 files changed, 24 insertions(+), 14 deletions(-) diff --git a/contrib/pg_stat_statements/expected/xml.out b/contrib/pg_stat_statements/expected/xml.out index 6c4c34b2c5..f1ba8c5f4e 100644 --- a/contrib/pg_stat_statements/expected/xml.out +++ b/contrib/pg_stat_statements/expected/xml.out @@ -98,16 +98,22 @@ SELECT XMLPI(NAME foo, t) FROM pgss_xml_tab; SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; calls | query -------+------------------------------------------------------------------------- - 2 | SELECT XMLELEMENT(NAME foo) FROM pgss_xml_tab - 4 | SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a)) FROM pgss_xml_tab + 1 | SELECT XMLELEMENT(NAME bar) FROM pgss_xml_tab + 1 | SELECT XMLELEMENT(NAME foo) FROM pgss_xml_tab + 1 | SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a)) FROM pgss_xml_tab 1 | SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS a), t) FROM pgss_xml_tab + 1 | SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t AS b)) FROM pgss_xml_tab + 2 | SELECT XMLELEMENT(NAME foo, XMLATTRIBUTES(t)) FROM pgss_xml_tab 1 | SELECT XMLELEMENT(NAME foo, t) FROM pgss_xml_tab - 4 | SELECT XMLFOREST(t AS a) FROM pgss_xml_tab + 1 | SELECT XMLFOREST(t AS a) FROM pgss_xml_tab 1 | SELECT XMLFOREST(t AS a, x AS b) FROM pgss_xml_tab - 2 | SELECT XMLPI(NAME foo) FROM pgss_xml_tab + 1 | SELECT XMLFOREST(t AS b) FROM pgss_xml_tab + 2 | SELECT XMLFOREST(t) FROM pgss_xml_tab + 1 | SELECT XMLPI(NAME bar) FROM pgss_xml_tab + 1 | SELECT XMLPI(NAME foo) FROM pgss_xml_tab 1 | SELECT XMLPI(NAME foo, t) FROM pgss_xml_tab 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(9 rows) +(15 rows) -- XMLPARSE and XMLSERIALIZE SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -171,12 +177,14 @@ SELECT XMLSERIALIZE(CONTENT x AS varchar(20)) FROM pgss_xml_tab; SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; calls | query -------+----------------------------------------------------------------- - 4 | SELECT XMLPARSE(DOCUMENT t) FROM pgss_xml_tab + 3 | SELECT XMLPARSE(CONTENT t) FROM pgss_xml_tab + 1 | SELECT XMLPARSE(DOCUMENT t) FROM pgss_xml_tab 1 | SELECT XMLSERIALIZE(CONTENT x AS text INDENT) FROM pgss_xml_tab + 2 | SELECT XMLSERIALIZE(CONTENT x AS text) FROM pgss_xml_tab 2 | SELECT XMLSERIALIZE(CONTENT x AS varchar(10)) FROM pgss_xml_tab - 3 | SELECT XMLSERIALIZE(DOCUMENT x AS text) FROM pgss_xml_tab + 1 | SELECT XMLSERIALIZE(DOCUMENT x AS text) FROM pgss_xml_tab 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) +(7 rows) -- XMLROOT SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/contrib/pg_stat_statements/expected/xml_1.out b/contrib/pg_stat_statements/expected/xml_1.out index 86152a84c8..860c875d91 100644 --- a/contrib/pg_stat_statements/expected/xml_1.out +++ b/contrib/pg_stat_statements/expected/xml_1.out @@ -131,12 +131,14 @@ SELECT XMLSERIALIZE(CONTENT x AS varchar(20)) FROM pgss_xml_tab; SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; calls | query -------+----------------------------------------------------------------- - 4 | SELECT XMLPARSE(DOCUMENT t) FROM pgss_xml_tab + 3 | SELECT XMLPARSE(CONTENT t) FROM pgss_xml_tab + 1 | SELECT XMLPARSE(DOCUMENT t) FROM pgss_xml_tab 1 | SELECT XMLSERIALIZE(CONTENT x AS text INDENT) FROM pgss_xml_tab + 2 | SELECT XMLSERIALIZE(CONTENT x AS text) FROM pgss_xml_tab 2 | SELECT XMLSERIALIZE(CONTENT x AS varchar(10)) FROM pgss_xml_tab - 3 | SELECT XMLSERIALIZE(DOCUMENT x AS text) FROM pgss_xml_tab + 1 | SELECT XMLSERIALIZE(DOCUMENT x AS text) FROM pgss_xml_tab 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) +(7 rows) -- XMLROOT SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 726c186c7d..b14cfcdfc7 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1611,15 +1611,15 @@ typedef struct XmlExpr /* xml function ID */ XmlExprOp op; /* name in xml(NAME foo ...) syntaxes */ - char *name pg_node_attr(query_jumble_ignore); + char *name; /* non-XML expressions for xml_attributes */ List *named_args; /* parallel list of String values */ - List *arg_names pg_node_attr(query_jumble_ignore); + List *arg_names; /* list of expressions */ List *args; /* DOCUMENT or CONTENT */ - XmlOptionType xmloption pg_node_attr(query_jumble_ignore); + XmlOptionType xmloption; /* INDENT option for XMLSERIALIZE */ bool indent; /* target type/typmod for XMLSERIALIZE */ -- 2.43.0
v1-0003-Include-XMLTABLE-column-definitions-in-query-jumb.patch (application/octet-stream, 16.4K) download From de8c881a5c59731b8d6b221ea10ea7920807163e Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 12:48:20 +0300 Subject: [PATCH v1 03/11] Include XMLTABLE column definitions in query jumbling Of the column definitions of XMLTABLE, only the paths, in TableFunc.colexprs, have been part of the query ID since XMLTABLE was added in 10. So queries that differ in the column types, defaults, NOT NULL or FOR ORDINALITY, in the names of the columns without PATH, or in the number of namespaces, were merged into a single pg_stat_statements entry. Include TableFunc.ns_uris, coltypes, coltypmods, coldefexprs, notnulls and ordinalitycol in query jumbling. notnulls is a Bitmapset, which can't be jumbled as a Node, so it gets a custom jumble function. The column names are still left out, as other column names are, except for a column without PATH, which uses its name as its path: TableFunc.colnames gets a custom jumble function that includes the name in this case. The namespace names are also left out, as they only have to match the prefixes in the paths, and so is colcollations, which is derived from the column types. JSON_TABLE also sets coltypes and coltypmods, so its queries that differ in column types are now split too. This changes the query IDs of the queries using XMLTABLE or JSON_TABLE, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- .../pg_stat_statements/expected/sqljson.out | 26 +++++--- contrib/pg_stat_statements/expected/xml.out | 40 ++++++++++--- contrib/pg_stat_statements/expected/xml_1.out | 40 ++++++++++--- src/backend/nodes/queryjumblefuncs.c | 59 +++++++++++++++++++ src/include/nodes/primnodes.h | 20 ++++--- 5 files changed, 153 insertions(+), 32 deletions(-) diff --git a/contrib/pg_stat_statements/expected/sqljson.out b/contrib/pg_stat_statements/expected/sqljson.out index 43f0a560c6..86fccb948e 100644 --- a/contrib/pg_stat_statements/expected/sqljson.out +++ b/contrib/pg_stat_statements/expected/sqljson.out @@ -511,12 +511,18 @@ SELECT * FROM pgss_sqljson_tab, (0 rows) SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; - calls | query --------+---------------------------------------------------- - 10 | SELECT * FROM pgss_sqljson_tab, + + calls | query +-------+----------------------------------------------------------- + 7 | SELECT * FROM pgss_sqljson_tab, + | JSON_TABLE(js, $1 COLUMNS (a int PATH '$.a')) jt + 1 | SELECT * FROM pgss_sqljson_tab, + + | JSON_TABLE(js, $1 COLUMNS (a text PATH '$.a')) jt + 1 | SELECT * FROM pgss_sqljson_tab, + + | JSON_TABLE(js, $1 COLUMNS (a varchar(3) PATH '$.a')) jt + 1 | SELECT * FROM pgss_sqljson_tab, + + | JSON_TABLE(js, $1 COLUMNS (a varchar(4) PATH '$.a')) jt 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) +(5 rows) -- EXISTS, FORMAT JSON and FOR ORDINALITY columns SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -607,12 +613,16 @@ SELECT * FROM pgss_sqljson_tab, (0 rows) SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; - calls | query --------+-------------------------------------------------------------------------- - 13 | SELECT * FROM pgss_sqljson_tab, + + calls | query +-------+----------------------------------------------------------------------------- + 6 | SELECT * FROM pgss_sqljson_tab, + | JSON_TABLE(js, $1 COLUMNS (a int EXISTS PATH '$.a' FALSE ON ERROR)) jt + 6 | SELECT * FROM pgss_sqljson_tab, + + | JSON_TABLE(js, $1 COLUMNS (a jsonb PATH '$.a')) jt + 1 | SELECT * FROM pgss_sqljson_tab, + + | JSON_TABLE(js, $1 COLUMNS (a text FORMAT JSON PATH '$.a' OMIT QUOTES)) jt 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) +(4 rows) -- column names and paths SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/contrib/pg_stat_statements/expected/xml.out b/contrib/pg_stat_statements/expected/xml.out index f1ba8c5f4e..3b1bc500f3 100644 --- a/contrib/pg_stat_statements/expected/xml.out +++ b/contrib/pg_stat_statements/expected/xml.out @@ -350,12 +350,24 @@ SELECT * FROM pgss_xml_tab, (0 rows) SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; - calls | query --------+---------------------------------------------------- - 9 | SELECT * FROM pgss_xml_tab, + + calls | query +-------+----------------------------------------------------------------------- + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2 DEFAULT $3 NOT NULL) xt + 2 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2 DEFAULT $3) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2 NOT NULL) xt + 2 | SELECT * FROM pgss_xml_tab, + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a text PATH $2) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a varchar(3) PATH $2) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a varchar(4) PATH $2) xt 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) +(8 rows) -- FOR ORDINALITY, column names and paths SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -423,10 +435,16 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; | XMLTABLE($1 PASSING x COLUMNS a int PATH $2, b FOR ORDINALITY) xt 2 | SELECT * FROM pgss_xml_tab, + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2, b int PATH $3) xt - 4 | SELECT * FROM pgss_xml_tab, + + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int, b int) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int, o FOR ORDINALITY) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS c int, d int) xt + 1 | SELECT * FROM pgss_xml_tab, + | XMLTABLE($1 PASSING x COLUMNS o FOR ORDINALITY, a int) xt 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) +(8 rows) -- XMLNAMESPACES and PASSING SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -479,10 +497,16 @@ SELECT * FROM pgss_xml_tab, SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; calls | query -------+---------------------------------------------------- - 6 | SELECT * FROM pgss_xml_tab, + + 3 | SELECT * FROM pgss_xml_tab, + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2) xt + 2 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE(XMLNAMESPACES($1 AS n), $2 + + | PASSING x COLUMNS a int PATH $3) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE(XMLNAMESPACES($1 AS n, $2 AS m), $3 + + | PASSING x COLUMNS a int PATH $4) xt 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) +(4 rows) DROP TABLE pgss_xml_tab; SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/contrib/pg_stat_statements/expected/xml_1.out b/contrib/pg_stat_statements/expected/xml_1.out index 860c875d91..afcd8fdbf7 100644 --- a/contrib/pg_stat_statements/expected/xml_1.out +++ b/contrib/pg_stat_statements/expected/xml_1.out @@ -304,12 +304,24 @@ SELECT * FROM pgss_xml_tab, (0 rows) SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; - calls | query --------+---------------------------------------------------- - 9 | SELECT * FROM pgss_xml_tab, + + calls | query +-------+----------------------------------------------------------------------- + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2 DEFAULT $3 NOT NULL) xt + 2 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2 DEFAULT $3) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2 NOT NULL) xt + 2 | SELECT * FROM pgss_xml_tab, + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a text PATH $2) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a varchar(3) PATH $2) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a varchar(4) PATH $2) xt 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) +(8 rows) -- FOR ORDINALITY, column names and paths SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -377,10 +389,16 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; | XMLTABLE($1 PASSING x COLUMNS a int PATH $2, b FOR ORDINALITY) xt 2 | SELECT * FROM pgss_xml_tab, + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2, b int PATH $3) xt - 4 | SELECT * FROM pgss_xml_tab, + + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int, b int) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS a int, o FOR ORDINALITY) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE($1 PASSING x COLUMNS c int, d int) xt + 1 | SELECT * FROM pgss_xml_tab, + | XMLTABLE($1 PASSING x COLUMNS o FOR ORDINALITY, a int) xt 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) +(8 rows) -- XMLNAMESPACES and PASSING SELECT pg_stat_statements_reset() IS NOT NULL AS t; @@ -433,10 +451,16 @@ SELECT * FROM pgss_xml_tab, SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", calls; calls | query -------+---------------------------------------------------- - 6 | SELECT * FROM pgss_xml_tab, + + 3 | SELECT * FROM pgss_xml_tab, + | XMLTABLE($1 PASSING x COLUMNS a int PATH $2) xt + 2 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE(XMLNAMESPACES($1 AS n), $2 + + | PASSING x COLUMNS a int PATH $3) xt + 1 | SELECT * FROM pgss_xml_tab, + + | XMLTABLE(XMLNAMESPACES($1 AS n, $2 AS m), $3 + + | PASSING x COLUMNS a int PATH $4) xt 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(2 rows) +(4 rows) DROP TABLE pgss_xml_tab; SELECT pg_stat_statements_reset() IS NOT NULL AS t; diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c index e5f645dc29..a2b42342d1 100644 --- a/src/backend/nodes/queryjumblefuncs.c +++ b/src/backend/nodes/queryjumblefuncs.c @@ -79,6 +79,12 @@ static void _jumbleVariableSetStmt(JumbleState *jstate, Node *node); static void _jumbleRangeTblEntry_eref(JumbleState *jstate, RangeTblEntry *rte, Alias *expr); +static void _jumbleTableFunc_colnames(JumbleState *jstate, + TableFunc *tf, + List *colnames); +static void _jumbleTableFunc_notnulls(JumbleState *jstate, + TableFunc *tf, + Bitmapset *notnulls); /* * Given a possibly multi-statement source string, confine our attention to the @@ -774,6 +780,59 @@ _jumbleRangeTblEntry_eref(JumbleState *jstate, JUMBLE_STRING(aliasname); } +/* + * Custom query jumble function for TableFunc.colnames. + */ +static void +_jumbleTableFunc_colnames(JumbleState *jstate, + TableFunc *tf, + List *colnames) +{ + ListCell *lc1; + ListCell *lc2; + int colno = 0; + + /* + * Like other column names, these are left out, except for an XMLTABLE + * column without PATH, which uses its name as its path. + */ + if (tf->functype != TFT_XMLTABLE) + return; + + forboth(lc1, colnames, lc2, tf->colexprs) + { + if (colno != tf->ordinalitycol && lfirst(lc2) == NULL) + { + char *colname = strVal(lfirst(lc1)); + + AppendJumble(jstate, (const unsigned char *) colname, + strlen(colname) + 1); + } + colno++; + } +} + +/* + * Custom query jumble function for TableFunc.notnulls. + */ +static void +_jumbleTableFunc_notnulls(JumbleState *jstate, + TableFunc *tf, + Bitmapset *notnulls) +{ + int colno = -1; + + /* A Bitmapset can't be jumbled as a Node, so jumble its members */ + if (notnulls == NULL) + { + AppendJumbleNull(jstate); + return; + } + + while ((colno = bms_next_member(notnulls, colno)) >= 0) + AppendJumble32(jstate, (const unsigned char *) &colno); +} + /* * CompLocation: comparator for qsorting LocationLen structs by location */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index b14cfcdfc7..a448f88861 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -114,35 +114,39 @@ typedef struct TableFunc /* XMLTABLE or JSON_TABLE */ TableFuncType functype; /* list of namespace URI expressions */ - List *ns_uris pg_node_attr(query_jumble_ignore); + List *ns_uris; /* list of namespace names or NULL */ List *ns_names pg_node_attr(query_jumble_ignore); /* input document expression */ Node *docexpr; /* row filter expression */ Node *rowexpr; - /* column names (list of String) */ - List *colnames pg_node_attr(query_jumble_ignore); + + /* + * column names (list of String); only jumbled for XMLTABLE columns + * without PATH, see queryjumblefuncs.c + */ + List *colnames pg_node_attr(custom_query_jumble); /* OID list of column type OIDs */ - List *coltypes pg_node_attr(query_jumble_ignore); + List *coltypes; /* integer list of column typmods */ - List *coltypmods pg_node_attr(query_jumble_ignore); + List *coltypmods; /* OID list of column collation OIDs */ List *colcollations pg_node_attr(query_jumble_ignore); /* list of column filter expressions */ List *colexprs; /* list of column default expressions */ - List *coldefexprs pg_node_attr(query_jumble_ignore); + List *coldefexprs; /* JSON_TABLE: list of column value expressions */ List *colvalexprs pg_node_attr(query_jumble_ignore); /* JSON_TABLE: list of PASSING argument expressions */ List *passingvalexprs pg_node_attr(query_jumble_ignore); /* nullability flag for each output column */ - Bitmapset *notnulls pg_node_attr(query_jumble_ignore); + Bitmapset *notnulls pg_node_attr(custom_query_jumble); /* JSON_TABLE plan */ Node *plan pg_node_attr(query_jumble_ignore); /* counts from 0; -1 if none specified */ - int ordinalitycol pg_node_attr(query_jumble_ignore); + int ordinalitycol; /* token location, or -1 if unknown */ ParseLoc location; } TableFunc; -- 2.43.0
v1-0004-Include-the-kind-of-grouping-sets-in-query-jumbli.patch (application/octet-stream, 3.2K) download From de9384cb7fb018098ef40b5f2cdab20ef705d8a9 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 12:52:01 +0300 Subject: [PATCH v1 04/11] Include the kind of grouping sets in query jumbling GroupingSet.kind, which tells ROLLUP, CUBE and GROUPING SETS apart, has not been part of the query ID since grouping sets were added in 9.5. So queries like GROUP BY ROLLUP (a, b), GROUP BY CUBE (a, b) and GROUP BY GROUPING SETS (a, b), which compute different groups, were merged into a single pg_stat_statements entry. Include GroupingSet.kind in query jumbling. This changes the query IDs of the queries using grouping sets, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/expected/select.out | 15 ++++++++++----- src/include/nodes/parsenodes.h | 2 +- 2 files changed, 11 insertions(+), 6 deletions(-) diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index 5961c894e9..bdc740d1c5 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -953,13 +953,18 @@ SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (CUBE (a), ROLL SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; calls | query -------+----------------------------------------------------------------------------------------- - 2 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (ROLLUP (a), CUBE (b)) - 2 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP ((a, b)) - 3 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP (a, b) - 2 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY a, ROLLUP (b) + 1 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY CUBE ((a, b)) + 1 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY CUBE (a, b) + 1 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (CUBE (a), ROLLUP (b)) + 1 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (ROLLUP (a), CUBE (b)) + 1 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY GROUPING SETS (a, b) + 1 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP ((a, b)) + 1 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY ROLLUP (a, b) + 1 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY a, CUBE (b) + 1 | SELECT a, b, COUNT(*) FROM pgss_empty_tab GROUP BY a, ROLLUP (b) 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(6 rows) +(11 rows) SELECT pg_stat_statements_reset() IS NOT NULL AS t; t diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 0debcd193a..6ac314f64c 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1539,7 +1539,7 @@ typedef enum GroupingSetKind typedef struct GroupingSet { NodeTag type; - GroupingSetKind kind pg_node_attr(query_jumble_ignore); + GroupingSetKind kind; List *content; ParseLoc location; } GroupingSet; -- 2.43.0
v1-0005-Include-SEARCH-and-CYCLE-clauses-in-query-jumblin.patch (application/octet-stream, 5.0K) download From d50a8e34eafcfc59503cd95ed4b39ae91542bef2 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 12:55:35 +0300 Subject: [PATCH v1 05/11] Include SEARCH and CYCLE clauses in query jumbling CommonTableExpr.search_clause and CommonTableExpr.cycle_clause have not been part of the query ID since these clauses were added in 14. They are only expanded by the rewriter, after query jumbling, so queries that differ in them, like SEARCH DEPTH FIRST and SEARCH BREADTH FIRST, or CYCLE on different columns, were merged into a single pg_stat_statements entry. Include both fields in query jumbling. The names of the columns that these clauses add, given by SET and USING, are left out, as other column names are. This changes the query IDs of the queries with a WITH clause, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/expected/select.out | 18 +++++++++++++++--- src/include/nodes/parsenodes.h | 10 +++++----- 2 files changed, 20 insertions(+), 8 deletions(-) diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index bdc740d1c5..1ea8c622ce 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -1059,15 +1059,27 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; -------+----------------------------------------------------------------------------------------- 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - 6 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | CYCLE a SET is_cycle TO $2 DEFAULT $3 USING path SELECT * FROM t + 3 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ | CYCLE a SET is_cycle USING path SELECT * FROM t + 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | CYCLE a, b SET is_cycle USING path SELECT * FROM t + 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | CYCLE b SET is_cycle USING path SELECT * FROM t + 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | SEARCH BREADTH FIRST BY a SET seq SELECT * FROM t 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ | SEARCH DEPTH FIRST BY a SET seq CYCLE a SET is_cycle USING path SELECT * FROM t - 5 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + 2 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ | SEARCH DEPTH FIRST BY a SET seq SELECT * FROM t + 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | SEARCH DEPTH FIRST BY a, b SET seq SELECT * FROM t + 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ + | SEARCH DEPTH FIRST BY b SET seq SELECT * FROM t 1 | WITH RECURSIVE t AS (SELECT a, b FROM pgss_empty_tab UNION ALL SELECT a + $1, b FROM t)+ | SELECT * FROM t -(6 rows) +(12 rows) SELECT pg_stat_statements_reset() IS NOT NULL AS t; t diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 6ac314f64c..26208eddb3 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1676,7 +1676,7 @@ typedef struct CTESearchClause NodeTag type; List *search_col_list; bool search_breadth_first; - char *search_seq_column; + char *search_seq_column pg_node_attr(query_jumble_ignore); ParseLoc location; } CTESearchClause; @@ -1684,10 +1684,10 @@ typedef struct CTECycleClause { NodeTag type; List *cycle_col_list; - char *cycle_mark_column; + char *cycle_mark_column pg_node_attr(query_jumble_ignore); Node *cycle_mark_value; Node *cycle_mark_default; - char *cycle_path_column; + char *cycle_path_column pg_node_attr(query_jumble_ignore); ParseLoc location; /* These fields are set during parse analysis: */ Oid cycle_mark_type; /* common type of _value and _default */ @@ -1710,8 +1710,8 @@ typedef struct CommonTableExpr CTEMaterialize ctematerialized; /* is this an optimization fence? */ /* SelectStmt/InsertStmt/etc before parse analysis, Query afterwards: */ Node *ctequery; /* the CTE's subquery */ - CTESearchClause *search_clause pg_node_attr(query_jumble_ignore); - CTECycleClause *cycle_clause pg_node_attr(query_jumble_ignore); + CTESearchClause *search_clause; + CTECycleClause *cycle_clause; ParseLoc location; /* token location, or -1 if unknown */ /* These fields are set during parse analysis: */ /* is this CTE actually recursive? */ -- 2.43.0
v1-0006-Include-column-definition-lists-in-query-jumbling.patch (application/octet-stream, 3.6K) download From fe37fac6c7483bb7ae10b0588c71fa0e0b711118 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 12:59:23 +0300 Subject: [PATCH v1 06/11] Include column definition lists in query jumbling The column definition list of a function in FROM, recorded in RangeTblFunction.funccolnames, funccoltypes, funccoltypmods and funccolcollations, has never been part of the query ID. But it gives the types of the result columns, and for functions like json_to_record() the column names are the keys to look up. So queries like SELECT * FROM json_to_record(...) AS t(a int) and the same with t(a text) or t(b int) were merged into a single pg_stat_statements entry. Include these fields in query jumbling. This changes the query IDs of the queries with functions in FROM, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/expected/select.out | 13 ++++++++++--- src/include/nodes/parsenodes.h | 8 ++++---- 2 files changed, 14 insertions(+), 7 deletions(-) diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index 1ea8c622ce..66a1524417 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -1155,13 +1155,20 @@ SELECT * FROM ROWS FROM (json_to_record('{"a": 1}') AS (a int), SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; calls | query -------+------------------------------------------------------------------------ - 2 | SELECT * FROM ROWS FROM (json_to_record($1) AS (a int), + + 1 | SELECT * FROM ROWS FROM (json_to_record($1) AS (a int), + | json_to_record($2) AS (b int)) AS t - 7 | SELECT * FROM json_to_record($1) AS t(a int) + 1 | SELECT * FROM ROWS FROM (json_to_record($1) AS (a int), + + | json_to_record($2) AS (b text)) AS t + 2 | SELECT * FROM json_to_record($1) AS t(a int) 1 | SELECT * FROM json_to_record($1) AS t(a int, b int) + 1 | SELECT * FROM json_to_record($1) AS t(a text COLLATE "C") + 1 | SELECT * FROM json_to_record($1) AS t(a text) + 1 | SELECT * FROM json_to_record($1) AS t(a varchar(3)) + 1 | SELECT * FROM json_to_record($1) AS t(a varchar(4)) + 1 | SELECT * FROM json_to_record($1) AS t(b int) 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) +(11 rows) SELECT pg_stat_statements_reset() IS NOT NULL AS t; t diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 26208eddb3..d753cd2388 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1354,13 +1354,13 @@ typedef struct RangeTblFunction int funccolcount pg_node_attr(query_jumble_ignore); /* These fields record the contents of a column definition list, if any: */ /* column names (list of String) */ - List *funccolnames pg_node_attr(query_jumble_ignore); + List *funccolnames; /* OID list of column type OIDs */ - List *funccoltypes pg_node_attr(query_jumble_ignore); + List *funccoltypes; /* integer list of column typmods */ - List *funccoltypmods pg_node_attr(query_jumble_ignore); + List *funccoltypmods; /* OID list of column collation OIDs */ - List *funccolcollations pg_node_attr(query_jumble_ignore); + List *funccolcollations; /* This is set during planning for use by the executor: */ /* PARAM_EXEC Param IDs affecting this func */ -- 2.43.0
v1-0007-Include-VARIADIC-in-query-jumbling.patch (application/octet-stream, 3.3K) download From b3dca7e2eaf3b74fceb4493f322b90c2321139f2 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 13:03:01 +0300 Subject: [PATCH v1 07/11] Include VARIADIC in query jumbling FuncExpr.funcvariadic and Aggref.aggvariadic, added in 9.3 and 9.4, have never been part of the query ID. For a function taking VARIADIC "any", like format() or concat(), they tell whether the last argument is an array of the variadic arguments or one of them. So queries like SELECT format('%s', VARIADIC ARRAY['a', 'b']), which returns "a", and the same without VARIADIC, which returns "{a,b}", were merged into a single pg_stat_statements entry. For other variadic functions, the parser combines the variadic arguments into an array and sets these fields with or without VARIADIC, so such calls are still merged. Include both fields in query jumbling. The built-in aggregates taking VARIADIC "any" are ordered-set aggregates, which can't be called with VARIADIC, so the tests only cover functions. This changes the query IDs of the queries with function calls or aggregates, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/expected/select.out | 8 +++++--- src/include/nodes/primnodes.h | 4 ++-- 2 files changed, 7 insertions(+), 5 deletions(-) diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index 66a1524417..9536a2f306 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -1217,11 +1217,13 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; calls | query -------+------------------------------------------------------------------------ 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" - 2 | SELECT concat(VARIADIC ARRAY[$1 /*, ... */]) - 2 | SELECT format($1, VARIADIC ARRAY[$2 /*, ... */]) + 1 | SELECT concat(ARRAY[$1 /*, ... */]) + 1 | SELECT concat(VARIADIC ARRAY[$1 /*, ... */]) + 1 | SELECT format($1, ARRAY[$2 /*, ... */]) + 1 | SELECT format($1, VARIADIC ARRAY[$2 /*, ... */]) 2 | SELECT jsonb_extract_path($1, a::text, b::text) FROM pgss_empty_tab 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(5 rows) +(7 rows) SELECT pg_stat_statements_reset() IS NOT NULL AS t; t diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index a448f88861..327359e7f7 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -508,7 +508,7 @@ typedef struct Aggref * true if variadic arguments have been combined into an array last * argument */ - bool aggvariadic pg_node_attr(query_jumble_ignore); + bool aggvariadic; /* aggregate kind (see pg_aggregate.h) */ char aggkind pg_node_attr(query_jumble_ignore); @@ -785,7 +785,7 @@ typedef struct FuncExpr * true if variadic arguments have been combined into an array last * argument */ - bool funcvariadic pg_node_attr(query_jumble_ignore); + bool funcvariadic; /* how to display this function call */ CoercionForm funcformat pg_node_attr(query_jumble_ignore); /* OID of collation of result */ -- 2.43.0
v1-0008-Include-the-fields-assigned-by-FieldStore-in-quer.patch (application/octet-stream, 2.7K) download From 3b60676a079c212a84a4d29fdd18ae02ab1f2b4d Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 13:06:39 +0300 Subject: [PATCH v1 08/11] Include the fields assigned by FieldStore in query jumbling FieldStore.fieldnums, the fields of a composite column assigned by an INSERT or an UPDATE, has not been part of the query ID since query jumbling was added to pg_stat_statements in 9.2. So queries like UPDATE t SET c.x = 1 and UPDATE t SET c.y = 1 were merged into a single pg_stat_statements entry. Include FieldStore.fieldnums in query jumbling. This changes the query IDs of the queries assigning fields of composite columns, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/expected/dml.out | 8 +++++--- src/include/nodes/primnodes.h | 2 +- 2 files changed, 6 insertions(+), 4 deletions(-) diff --git a/contrib/pg_stat_statements/expected/dml.out b/contrib/pg_stat_statements/expected/dml.out index b310a97b25..ae33c48e97 100644 --- a/contrib/pg_stat_statements/expected/dml.out +++ b/contrib/pg_stat_statements/expected/dml.out @@ -199,12 +199,14 @@ DROP TYPE pgss_point; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; calls | rows | query -------+------+------------------------------------------------------------ - 2 | 2 | INSERT INTO pgss_fieldstore_tab (c.x) VALUES ($1) + 1 | 1 | INSERT INTO pgss_fieldstore_tab (c.x) VALUES ($1) 1 | 1 | INSERT INTO pgss_fieldstore_tab (c.x, c.y) VALUES ($1, $2) + 1 | 1 | INSERT INTO pgss_fieldstore_tab (c.y) VALUES ($1) 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t - 2 | 6 | UPDATE pgss_fieldstore_tab SET c.x = $1 + 1 | 3 | UPDATE pgss_fieldstore_tab SET c.x = $1 1 | 3 | UPDATE pgss_fieldstore_tab SET c.x = $1, c.y = $2 -(5 rows) + 1 | 3 | UPDATE pgss_fieldstore_tab SET c.y = $1 +(7 rows) -- check that [temp] table relation extensions are tracked as writes CREATE TABLE pgss_extend_tab (a int, b text); diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 327359e7f7..8d83484ab2 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1183,7 +1183,7 @@ typedef struct FieldStore Expr *arg; /* input tuple value */ List *newvals; /* new value(s) for field(s) */ /* integer list of field attnums */ - List *fieldnums pg_node_attr(query_jumble_ignore); + List *fieldnums; /* type of result (same as type of arg) */ Oid resulttype pg_node_attr(query_jumble_ignore); /* Like RowExpr, we deliberately omit a typmod and collation here */ -- 2.43.0
v1-0009-Include-the-type-of-row-constructors-in-query-jum.patch (application/octet-stream, 2.6K) download From 6f4384ae03443abba12b0815ed16dbab8c316927 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 13:10:16 +0300 Subject: [PATCH v1 09/11] Include the type of row constructors in query jumbling RowExpr.row_typeid has not been part of the query ID since query jumbling was added to pg_stat_statements in 9.2. A ROW() constructor cast to a composite type gets that type, so a query with ROW(1, 2) cast to one composite type, the same query with ROW(1, 2) cast to another one, and the same query without a cast, which return values of different types, were merged into a single pg_stat_statements entry. Include RowExpr.row_typeid in query jumbling. This changes the query IDs of the queries with row constructors, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/expected/select.out | 8 +++++--- src/include/nodes/primnodes.h | 2 +- 2 files changed, 6 insertions(+), 4 deletions(-) diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index 9536a2f306..4ba573f019 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -1279,13 +1279,15 @@ SELECT ROW(s, t) <= ROW('b', 'b') FROM (VALUES ('a', 'a')) AS v(s, t); SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; calls | query -------+------------------------------------------------------------------------ - 2 | SELECT (ROW($1, $2)::pgss_pair1).* - 2 | SELECT ROW($1, $2) + 1 | SELECT (ROW($1, $2)::pgss_pair1).* + 1 | SELECT (ROW($1, $2)::pgss_pair2).* + 1 | SELECT ROW($1, $2) + 1 | SELECT ROW($1, $2)::pgss_pair1 2 | SELECT ROW(s, t) < ROW($1, $2) FROM (VALUES ($3, $4)) AS v(s, t) 1 | SELECT ROW(s, t) <= ROW($1, $2) FROM (VALUES ($3, $4)) AS v(s, t) 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(6 rows) +(8 rows) DROP TYPE pgss_pair1, pgss_pair2; DROP TABLE pgss_empty_tab; diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 8d83484ab2..f13f75b22d 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1439,7 +1439,7 @@ typedef struct RowExpr List *args; /* the fields */ /* RECORDOID or a composite type's ID */ - Oid row_typeid pg_node_attr(query_jumble_ignore); + Oid row_typeid; /* * row_typeid cannot be a domain over composite, only plain composite. To -- 2.43.0
v1-0010-Include-the-operators-of-row-comparisons-in-query.patch (application/octet-stream, 2.6K) download From dbd2ca43ba4d785e27655d3c51b5f001f6802c92 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 13:13:35 +0300 Subject: [PATCH v1 10/11] Include the operators of row comparisons in query jumbling RowCompareExpr.opnos, the operators of a row comparison, has not been part of the query ID since query jumbling was added to pg_stat_statements in 9.2, only the comparison type in RowCompareExpr.cmptype. But a row comparison can use the operators of any btree operator family, so queries comparing rows of text with < and with ~<~, which use different rules, were merged into a single pg_stat_statements entry. Include RowCompareExpr.opnos in query jumbling. The operator families and the input collations are still left out, as they are derived from the operators and the arguments. This changes the query IDs of the queries with row comparisons, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/expected/select.out | 5 +++-- src/include/nodes/primnodes.h | 2 +- 2 files changed, 4 insertions(+), 3 deletions(-) diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out index 4ba573f019..56fddfa875 100644 --- a/contrib/pg_stat_statements/expected/select.out +++ b/contrib/pg_stat_statements/expected/select.out @@ -1283,11 +1283,12 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; 1 | SELECT (ROW($1, $2)::pgss_pair2).* 1 | SELECT ROW($1, $2) 1 | SELECT ROW($1, $2)::pgss_pair1 - 2 | SELECT ROW(s, t) < ROW($1, $2) FROM (VALUES ($3, $4)) AS v(s, t) + 1 | SELECT ROW(s, t) < ROW($1, $2) FROM (VALUES ($3, $4)) AS v(s, t) 1 | SELECT ROW(s, t) <= ROW($1, $2) FROM (VALUES ($3, $4)) AS v(s, t) + 1 | SELECT ROW(s, t) ~<~ ROW($1, $2) FROM (VALUES ($3, $4)) AS v(s, t) 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C" 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(8 rows) +(9 rows) DROP TYPE pgss_pair1, pgss_pair2; DROP TABLE pgss_empty_tab; diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index f13f75b22d..4aaf51be56 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1483,7 +1483,7 @@ typedef struct RowCompareExpr /* LT LE GE or GT, never EQ or NE */ CompareType cmptype; /* OID list of pairwise comparison ops */ - List *opnos pg_node_attr(query_jumble_ignore); + List *opnos; /* OID list of containing operator families */ List *opfamilies pg_node_attr(query_jumble_ignore); /* OID list of collations for comparisons */ -- 2.43.0
v1-0011-Include-OVERRIDING-in-query-jumbling.patch (application/octet-stream, 4.1K) download From fa2c5879372d4cced150a6fbd97d25667d04d017 Mon Sep 17 00:00:00 2001 From: Ilya Staroverov <i.staroverov@ftdata.ru> Date: Mon, 26 Oct 2026 13:16:22 +0300 Subject: [PATCH v1 11/11] Include OVERRIDING in query jumbling Query.override has not been part of the query ID since OVERRIDING was added in 10, and MergeAction.override was left out when MERGE was added to query jumbling in 15, as OVERRIDING was not thought to matter when the column list is part of the query ID. But with OVERRIDING USER VALUE, the values given for an identity column are ignored, so INSERT ... OVERRIDING USER VALUE VALUES (...) inserts other values than the same INSERT without it, and both were merged into a single pg_stat_statements entry, as was OVERRIDING SYSTEM VALUE. Include both fields in query jumbling. As Query.override is set for all queries, this changes all query IDs, so it is for master only. Author: Ilya Staroverov <i.staroverov@ftdata.ru> --- contrib/pg_stat_statements/expected/dml.out | 16 +++++++++++----- src/include/nodes/parsenodes.h | 2 +- src/include/nodes/primnodes.h | 2 +- 3 files changed, 13 insertions(+), 7 deletions(-) diff --git a/contrib/pg_stat_statements/expected/dml.out b/contrib/pg_stat_statements/expected/dml.out index ae33c48e97..36507b14b5 100644 --- a/contrib/pg_stat_statements/expected/dml.out +++ b/contrib/pg_stat_statements/expected/dml.out @@ -170,14 +170,20 @@ SELECT a, b FROM pgss_identity_tab ORDER BY a; DROP TABLE pgss_identity_tab; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"; - calls | rows | query --------+------+------------------------------------------------------------- - 3 | 3 | INSERT INTO pgss_identity_tab VALUES ($1, $2) - 3 | 3 | MERGE INTO pgss_identity_tab USING (SELECT $1 AS b) s ON $2+ + calls | rows | query +-------+------+------------------------------------------------------------------------ + 1 | 1 | INSERT INTO pgss_identity_tab OVERRIDING SYSTEM VALUE VALUES ($1, $2) + 1 | 1 | INSERT INTO pgss_identity_tab OVERRIDING USER VALUE VALUES ($1, $2) + 1 | 1 | INSERT INTO pgss_identity_tab VALUES ($1, $2) + 1 | 1 | MERGE INTO pgss_identity_tab USING (SELECT $1 AS b) s ON $2 + + | | WHEN NOT MATCHED THEN INSERT OVERRIDING SYSTEM VALUE VALUES ($3, s.b) + 1 | 1 | MERGE INTO pgss_identity_tab USING (SELECT $1 AS b) s ON $2 + + | | WHEN NOT MATCHED THEN INSERT OVERRIDING USER VALUE VALUES ($3, s.b) + 1 | 1 | MERGE INTO pgss_identity_tab USING (SELECT $1 AS b) s ON $2 + | | WHEN NOT MATCHED THEN INSERT VALUES ($3, s.b) 1 | 6 | SELECT a, b FROM pgss_identity_tab ORDER BY a 1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t -(4 rows) +(8 rows) -- assignments to fields of a composite column CREATE TYPE pgss_point AS (x int, y int); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index d753cd2388..473d5d34f4 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -200,7 +200,7 @@ typedef struct Query List *targetList; /* target list (of TargetEntry) */ /* OVERRIDING clause */ - OverridingKind override pg_node_attr(query_jumble_ignore); + OverridingKind override; OnConflictExpr *onConflict; /* ON CONFLICT DO NOTHING/SELECT/UPDATE */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 4aaf51be56..6f56f9a4a6 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -2042,7 +2042,7 @@ typedef struct MergeAction MergeMatchKind matchKind; /* MATCHED/NOT MATCHED BY SOURCE/TARGET */ CmdType commandType; /* INSERT/UPDATE/DELETE/DO NOTHING */ /* OVERRIDING clause */ - OverridingKind override pg_node_attr(query_jumble_ignore); + OverridingKind override; Node *qual; /* transformed WHEN conditions */ List *targetList; /* the target list (of TargetEntry) */ /* target attribute numbers of an UPDATE */ -- 2.43.0
^ permalink raw reply [nested|flat] 2+ messages in thread

* Re: Query jumbling ignores XML names, ROLLUP/CUBE, VARIADIC and more
@ 2026-10-26 18:26  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Staroverov Ilja <i.staroverov@ftdata.ru>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2026-10-26 18:26 UTC (permalink / raw)
  To: Staroverov Ilja <i.staroverov@ftdata.ru>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; michael@paquier.xyz <michael@paquier.xyz>

Staroverov Ilja <i.staroverov@ftdata.ru> writes:
> While working on the SQL/JSON patterns in pg_stat_statements [1], I
> looked at the other fields marked query_jumble_ignore, and some of them
> change the results of the queries.

I don't believe that query jumbles have ever been intended to
distinguish every semantic detail of a query tree.  To the contrary,
merging together "sufficiently similar" queries has always been
precisely the intention.  So there is not one of these things that
deserves the label of bug.  We can have a discussion about whether
the original (and clearly intentional) choice to mark a field as
query_jumble_ignore was being too aggressive about what is "similar",
but in each case that's going to be a judgment call.

So rather than submitting a patch and expecting it to get pushed,
you need to try to create some consensus on the -hackers list about
whether these things should be changed.  To my mind the benefit of
the doubt should probably go to the existing behaviors.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-10-26 18:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-26 10:45 Query jumbling ignores XML names, ROLLUP/CUBE, VARIADIC and more Staroverov Ilja <i.staroverov@ftdata.ru>
2026-10-26 18:26 ` Tom Lane <tgl@sss.pgh.pa.us>

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