Thread: Tracing in PostgreSQL with Extended Query Protocol

Hi,

I'm working on improving observability at my workplace and looking to add tracing at the database level. We use an observability provider that connects to our PostgreSQL instance and can extract traces from SQLCommenter-compliant comments.

However, we make heavy use of the extended query protocol, and from my understanding, SQL comments don’t work in that context. Given this, I was wondering:

• Is there a way to attach tracing information when using the extended query protocol?
• Are there any best practices or tools that can help with this?
• Is there a plan to add more observability to Postgresql, alined maybe with the OpenTelemetry Project?

Any advice or pointers would be greatly appreciated!

Thanks in advance,

Hugo Sousa <hugos.18@hotmail.com> writes:
> However, we make heavy use of the extended query protocol, and from
> my understanding, SQL comments don’t work in that context.

Why do you think that?

            regards, tom lane

From my understanding when we use the Extended Query Protocol we are parsing a query only once and then we bind them to
specificparameter values to them execute aka prepared statements. To use a standard like SQLCommenter, for my purpose
wewould need to pass specific trace values for every execution of the query as every query execution is correlated to a
differenttrace (a static comment should work).
 

Most documentation and examples I can find validate that it’s not possible to pass tracing data if the underlying sql
commandis done using a prepared statement.
 
To validate this I tried creating a prepared statement that would allow a dynamic trace id in a comment:
PREPARE example_statement AS /* trace=$1::text */ SELECT * FROM users WHERE id=$2;
But I got the following error "ERROR:  could not determine data type of parameter $1" and other variations of the
preparedstatement ended in similar problems.
 

We wouldn’t want to lose the performance benefits and the added security of using prepared statements, but this ability
totrace all the way to the database layer and reverse would be very beneficial to us (and I hope to more people). I
justwant to know is this possible, if yes can I help in any way to add this capability?
 


> On 18 Feb 2025, at 04:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Hugo Sousa <hugos.18@hotmail.com> writes:
>> However, we make heavy use of the extended query protocol, and from
>> my understanding, SQL comments don’t work in that context.
> 
> Why do you think that?
> 
>             regards, tom lane