Patch: Log parameter types in detailed query logging
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
Attachments:
0001-add-type-info-logging.patchtext/x-patch; charset=US-ASCII; name=0001-add-type-info-logging.patchDownload+0-38
=?UTF-8?B?0KHRgtC10L/QsNC9?= <slpmcf@gmail.com> writes:
This patch adds the ability to log the types of parameters used in queries
when detailed query logging is enabled.
If there's a patch in what you sent, I'm not seeing it. Looks like a
webpage dump or something.
However, I suppose that it must add some catalog lookup operations to
the logging operations, and I'm feeling resistant to that, because of
the added cycles and risk-of-failure. I think you need to make a much
stronger case for the value of this information than you've done here.
regards, tom lane
On Fri, Feb 14, 2025 at 1:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
=?UTF-8?B?0KHRgtC10L/QsNC9?= <slpmcf@gmail.com> writes:
This patch adds the ability to log the types of parameters used in
queries
when detailed query logging is enabled.
If there's a patch in what you sent, I'm not seeing it. Looks like a
webpage dump or something.However, I suppose that it must add some catalog lookup operations to
the logging operations, and I'm feeling resistant to that, because of
the added cycles and risk-of-failure. I think you need to make a much
stronger case for the value of this information than you've done here.regards, tom lane
My apologies, it seems I am still experiencing some difficulty in
accurately transmitting the code change details. I am re-submitting the
diff.patch prepared by Stepan Neretin now, and will ensure the formatting
is correct.
Regarding your concerns, I understand your hesitation about adding catalog
lookups due to potential performance impacts and failure risks. However, I
believe that the benefits of including parameter types in detailed query
logging will significantly outweigh the costs. Often, when analyzing
problematic queries, we lack crucial information about the parameter types
used. This lack of information forces us to request details from the
client, which adds unnecessary delays and complexity to the debugging
process. This patch addresses that directly.
Best regards,
Stepan Neretin