Thread

1. Patch: Log parameter types in detailed query logging

   Stepan Neretin <slpmcf@gmail.com> — 2025-02-13T17:45:05Z

   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
   

2. Re: Patch: Log parameter types in detailed query logging

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-02-13T18:03:09Z

   =?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
   
   
   
   

3. Re: Patch: Log parameter types in detailed query logging

   Stepan Neretin <slpmcf@gmail.com> — 2025-02-13T18:47:37Z

   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