Re: PG 16 draft release notes ready - Mailing list pgsql-hackers

Bruce Momjian <bruce@momjian.us> writes:

> I have completed the first draft of the PG 16 release notes.  You can
> see the output here:
>
>     https://momjian.us/pgsql_docs/release-16.html
>
> I will adjust it to the feedback I receive;  that URL will quickly show
> all updates.

The bit about auto_explain and query parameters says:

> Allow auto_explain to log query parameters used in executing prepared
> statements (Dagfinn Ilmari Mannsåker)
>
> This is controlled by auto_explain.log_parameter_max_length, and by
> default query parameters will be logged with no length
> restriction. SHOULD THIS BE MORE CLEARLY IDENTIFIED AS CONTROLLING THE
> EXECUTION OF PREPARED STATEMENTS?

This is wrong, the logging applies to all query parameters, not just for
prepared statements (and has nothing to do with controlling the
execution thereof).  That was just the only way to test it when it was
written, because psql's \bind command exist yet then.

Should we perhaps add some tests for that, like the attached?

- ilmari

From d3630f299fc2d2d9f9eb3addd426f98e5196100d Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Thu, 25 May 2023 21:13:11 +0100
Subject: [PATCH] Test auto_explain parameter logging with protocol-level bind
 parameters

When auto_explain.log_parameter_max_length was added, psql didn't have
the \bind command for extended query protocol yet, so the test could
only use prepared statements.
---
 contrib/auto_explain/t/001_auto_explain.pl | 31 ++++++++++++++++++++++
 1 file changed, 31 insertions(+)

diff --git a/contrib/auto_explain/t/001_auto_explain.pl b/contrib/auto_explain/t/001_auto_explain.pl
index abb422f8de..d2a0078546 100644
--- a/contrib/auto_explain/t/001_auto_explain.pl
+++ b/contrib/auto_explain/t/001_auto_explain.pl
@@ -106,6 +106,21 @@ sub query_log
     qr/Query Parameters:/,
     "query parameters not logged when disabled, text mode");
 
+# bind parameters
+$log_contents = query_log($node,
+    q{SELECT * FROM pg_proc WHERE proname = $1 AND prokind = $2 \bind ascii f \g}
+);
+
+like(
+    $log_contents,
+    qr/Query Text: SELECT \* FROM pg_proc WHERE proname = \$1 AND prokind = \$2/,
+    "query text with parameters logged, text mode");
+
+like(
+    $log_contents,
+    qr/Query Parameters: \$1 = 'ascii', \$2 = 'f'/,
+    "query parameters logged, text mode");
+
 # Query Identifier.
 # Logging enabled.
 $log_contents = query_log(
@@ -172,6 +187,22 @@ sub query_log
     qr/"Node Type": "Index Scan"[^}]*"Index Name": "pg_class_relname_nsp_index"/s,
     "index scan logged, json mode");
 
+# query with bind parameters in JSON format.
+$log_contents = query_log(
+    $node,
+    q{SELECT * FROM pg_class WHERE relname = $1 AND relkind = $2 \bind pg_proc r \x},
+    { "auto_explain.log_format" => "json" });
+
+like(
+    $log_contents,
+    qr/"Query Text": "SELECT \* FROM pg_class WHERE relname = \$1 AND relkind = \$2 "/,
+    "query text with parameters logged, json mode");
+
+like(
+    $log_contents,
+    qr/"Query Parameters": "\$1 = 'pg_proc', \$2 = 'r'"/,
+    "query parameters logged, json mode");
+
 # Check that PGC_SUSET parameters can be set by non-superuser if granted,
 # otherwise not
 
-- 
2.39.2