Patch: Log parameter types in detailed query logging

From: Степан <slpmcf(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Patch: Log parameter types in detailed query logging
Date: 2025-02-13 17:45:05
Message-ID: CA+Yyo5TCPQN6gLC8+bwS9jXQ+QqhH3TEr9swVzhe67RgP44HKQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-add-type-info-logging.patch text/x-patch 40.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jacob Champion 2025-02-13 17:53:52 Re: [PATCH] pg_stat_activity: make slow/hanging authentication more visible
Previous Message Andres Freund 2025-02-13 17:39:04 BackgroundPsql swallowing errors on windows