Patch: Log parameter types in detailed query logging

Stepan Neretin <slpmcf@gmail.com>

From: Степан <slpmcf@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Date: 2025-02-13T17:45:05Z
Lists: pgsql-hackers

Attachments

Dear PostgreSQL Hackers,

This patch adds the ability to log the types of parameters used in queries
when detailed query logging is enabled. Currently, detailed logging only
shows the parameter values, which often requires further investigation or
asking the client to determine the data types. This enhancement will
significantly aid in debugging problematic queries, especially when data
type mismatches are suspected.

The patch modifies the detailed logging output to include the data type of
each parameter, making it easier to understand the context of the query and
diagnose potential issues without additional communication overhead.

Here's an example of the new logging format:

```
2025-02-10 21:05:42.622 +07 [3702286] LOG: duration: 0.008 ms execute P_1:
SELECT
    u.username,
    u.email,
    r.role_name,
    o.order_date,
    p.product_name,
    oi.quantity,
    ur.role_id,
    (p.price * oi.quantity) AS total_price
  FROM
    users u
  JOIN
    user_roles ur ON u.user_id = ur.user_id
  JOIN
    roles r ON ur.role_id = r.role_id
  JOIN
    orders o ON u.user_id = o.user_id
  JOIN
    order_items oi ON o.order_id = oi.order_id
  JOIN
    products p ON oi.product_id = p.product_id
  where ur.role_id = $1
   and u.user_id = $2
   and oi.order_id = $3::bigint
  ORDER BY
    o.order_date;
2025-02-10 21:05:42.622 +07 [3702286] DETAIL: Parameters: $1 =
(integer)'11', $2 = (integer)'86', $3 = (bigint)'14'
```

As you can see, the DETAIL log message now includes the data type in
parentheses before the parameter value.

I believe this addition will greatly improve the usefulness of detailed
query logging. I welcome your feedback and suggestions.

Thank you for your time and consideration.

Best regards,
Stepan Neretin