Feedback-ID: i0fe9450f:Fastmail
Date: Wed, 8 Mar 2023 15:19:33 +0900
From: Michael Paquier <michael@paquier.xyz>
To: "Drouvot, Bertrand" <bertranddrouvot.pg@gmail.com>
Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Normalization of utility queries in pg_stat_statements
Message-ID: <ZAgo9QEPa4JWghVX@paquier.xyz>
References: <Y+MRdEq9W9XVa2AB@paquier.xyz>
 <Y+16K+q9L0yjV3zA@paquier.xyz>
 <0e067034-e92c-302b-24ed-492bca228e26@gmail.com>
 <Y+7n+uz6XkzJpB7z@paquier.xyz>
 <14965f63-6d78-45ff-d029-daafe8eddb40@gmail.com>
 <Y/Lbt12YUHSU2fYl@paquier.xyz>
 <Y/LcU/u8CbD+nxYp@paquier.xyz>
 <Y/7Y9U/y/keAW3qH@paquier.xyz>
 <1f84685f-1ffd-4937-de63-aaa1513a0b7e@gmail.com>
 <ZAFBR1cwIq39ywX+@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="gjltmvdgsKx5c7K1"
Content-Disposition: inline
In-Reply-To: <ZAFBR1cwIq39ywX+@paquier.xyz>
Archived-At: <https://www.postgresql.org/message-id/ZAgo9QEPa4JWghVX%40paquier.xyz>
Precedence: bulk


--gjltmvdgsKx5c7K1
Content-Type: multipart/mixed; boundary="ngBh6IzZBy5NJv7V"
Content-Disposition: inline


--ngBh6IzZBy5NJv7V
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Fri, Mar 03, 2023 at 09:37:27AM +0900, Michael Paquier wrote:
> Thanks for double-checking, applied 0001 to finish this part of the
> work.  I am attaching the remaining bits as of the attached, combined
> into a single patch.

Doing so as a single patch was not feeling right as this actually
fixes issues with the location calculations for the Const node, so I
have split that into three commits and finally applied the whole.

As a bonus, please see attached a patch to apply the normalization to
CALL statements using the new automated infrastructure.  OUT
parameters can be passed to a procedure, hence I guess that these had
better be silenced as well.  This is not aimed at being integrated,
just for reference.
--
Michael

--ngBh6IzZBy5NJv7V
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
	filename="0001-Apply-normalization-to-CALL-statements.patch"
Content-Transfer-Encoding: quoted-printable

=46rom 7fff41b050b8da4b7a006071b51ae5fa43bc7c0b Mon Sep 17 00:00:00 2001
=46rom: Michael Paquier <michael@paquier.xyz>
Date: Wed, 8 Mar 2023 15:14:15 +0900
Subject: [PATCH] Apply normalization to CALL statements

---
 src/include/nodes/parsenodes.h                |  6 ++--
 src/backend/nodes/queryjumblefuncs.c          | 12 +++++++
 .../pg_stat_statements/expected/utility.out   | 33 ++++++++++++++++---
 contrib/pg_stat_statements/sql/utility.sql    |  9 +++++
 4 files changed, 53 insertions(+), 7 deletions(-)

diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 259e814253..32e5f535c1 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3227,12 +3227,14 @@ typedef struct InlineCodeBlock
  */
 typedef struct CallStmt
 {
+	pg_node_attr(custom_query_jumble)
+
 	NodeTag		type;
 	FuncCall   *funccall;		/* from the parser */
 	/* transformed call, with only input args */
-	FuncExpr   *funcexpr pg_node_attr(query_jumble_ignore);
+	FuncExpr   *funcexpr;
 	/* transformed output-argument expressions */
-	List	   *outargs pg_node_attr(query_jumble_ignore);
+	List	   *outargs;
 } CallStmt;
=20
 typedef struct CallContext
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/query=
jumblefuncs.c
index d7fd72d70f..709f91ab0e 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -52,6 +52,7 @@ static void _jumbleNode(JumbleState *jstate, Node *node);
 static void _jumbleA_Const(JumbleState *jstate, Node *node);
 static void _jumbleList(JumbleState *jstate, Node *node);
 static void _jumbleRangeTblEntry(JumbleState *jstate, Node *node);
+static void _jumbleCallStmt(JumbleState *jstate, Node *node);
=20
 /*
  * Given a possibly multi-statement source string, confine our attention t=
o the
@@ -395,3 +396,14 @@ _jumbleRangeTblEntry(JumbleState *jstate, Node *node)
 			break;
 	}
 }
+
+static void
+_jumbleCallStmt(JumbleState *jstate, Node *node)
+{
+	CallStmt *expr =3D (CallStmt *) node;
+	FuncExpr *func =3D expr->funcexpr;
+
+	JUMBLE_FIELD_SINGLE(func->funcid);
+	_jumbleNode(jstate, (Node *) func->args);
+	_jumbleNode(jstate, (Node *) expr->outargs);
+}
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_s=
tat_statements/expected/utility.out
index 0047aba5d1..b5a8c6937c 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -240,6 +240,12 @@ DECLARE
 BEGIN
   SELECT (i + i)::int INTO r;
 END; $$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE sum_out(IN i int, IN j int, OUT k int, OUT l i=
nt) AS $$
+DECLARE
+  r int;
+BEGIN
+  SELECT (i + i)::int INTO r;
+END; $$ LANGUAGE plpgsql;
 CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$
 DECLARE
   r int;
@@ -256,15 +262,32 @@ CALL sum_one(3);
 CALL sum_one(199);
 CALL sum_two(1,1);
 CALL sum_two(1,2);
+CALL sum_out(1,1,1,1);
+ k | l=20
+---+---
+   | =20
+(1 row)
+
+CALL sum_out(2,2,1,1);
+ k | l=20
+---+---
+   | =20
+(1 row)
+
+CALL sum_out(2,3,4,5);
+ k | l=20
+---+---
+   | =20
+(1 row)
+
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "=
C";
  calls | rows |               query              =20
 -------+------+-----------------------------------
-     1 |    0 | CALL sum_one(199)
-     1 |    0 | CALL sum_one(3)
-     1 |    0 | CALL sum_two(1,1)
-     1 |    0 | CALL sum_two(1,2)
+     2 |    0 | CALL sum_one($1)
+     3 |    0 | CALL sum_out($1,$2,$3,$4)
+     2 |    0 | CALL sum_two($1,$2)
      1 |    1 | SELECT pg_stat_statements_reset()
-(5 rows)
+(4 rows)
=20
 -- COPY
 CREATE TABLE copy_stats (a int, b int);
diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_s=
tatements/sql/utility.sql
index 225d30a62a..dbf38c31bc 100644
--- a/contrib/pg_stat_statements/sql/utility.sql
+++ b/contrib/pg_stat_statements/sql/utility.sql
@@ -131,6 +131,12 @@ DECLARE
 BEGIN
   SELECT (i + i)::int INTO r;
 END; $$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE sum_out(IN i int, IN j int, OUT k int, OUT l i=
nt) AS $$
+DECLARE
+  r int;
+BEGIN
+  SELECT (i + i)::int INTO r;
+END; $$ LANGUAGE plpgsql;
 CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$
 DECLARE
   r int;
@@ -142,6 +148,9 @@ CALL sum_one(3);
 CALL sum_one(199);
 CALL sum_two(1,1);
 CALL sum_two(1,2);
+CALL sum_out(1,1,1,1);
+CALL sum_out(2,2,1,1);
+CALL sum_out(2,3,4,5);
 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "=
C";
=20
 -- COPY
--=20
2.39.2


--ngBh6IzZBy5NJv7V--

--gjltmvdgsKx5c7K1
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmQIKPUACgkQnvQgOdby
QH17LRAAiLWCyDIih6rwv6JtjjNB75TBopjBEK65JJEis9JEmZtDb6TAE+Bq1EEB
wzi+qe++WHl++xtD1GnTLWvGawT8gxqhbw+dktoc49S8Nkp8bSvcD95j+boJdAzL
fR1DQ2LLKo0kQiA0/29PwVULe//t5aUPH+fs2OMvpvT/b+4gqhsY7UnoQa4Skuax
ZRZ3y8hrZeMa5w44WHnawUATZuKry+GoJrwKPaNY7fg2EuDk0hNc1dC8C14ocWbE
gg6wu5icc8JR86FrxcM/4g28zTb6/e8ST2eG07M/nflt/4iZlf2aFWsFIyepyxrI
glsVMsvFOxJo0PvFUxZQeHx7/EVf6aPF0ng/qNmwYnf5J3XwpzRzkhaty9Qsqfhh
qlFJQiBTpIopVyDrt7R9biXTzog9OFASBS+6wKTLv6Aj3je3Ul9ir9B+dWU4Y6oV
22Q8LrT40yEpf6YMyJIIihJZsJVmzzqub+iF7FCML0Q0twAQZ3RcEX77O4FkpPcT
Gq9TpxzrsoIKJ7ZbhbH9j0PCmfY2oo77Idm7HW/qWGMLvtiKF3FrMpcqIRG9MP+r
F1fkPhG1kH3NjK5iQ3zSlx19UhS8eRrUlgSOoHeDRtYAmyCZdPOzA/Mb/kYVjXqI
Z8d+eYLTWYiQ/I/9pwLhvWbr/a2nU+NXX4Bko5PFLd4OSioKn6g=
=RBd1
-----END PGP SIGNATURE-----

--gjltmvdgsKx5c7K1--