Feature Request: SQLCommenter support? #20
Description
A while back, OTEL merged in SqlCommenter (seeSqlCommenter merged into OpenTelemetry).
SqlCommenter works on the database driver/ORM layer, by injecting metadata containing comments as part of the SQL statements sent to the database. These logs are then ingested on the database side with support from tools like Cloud SQL Insights, a proprietary Google Cloud offering, or equivalents at other vendors like DataDog. I'm not familiar with whether there are open-source self-hosted tools for this sort of data ingestion and exposing it meaningfully to the user yet, but I'm sure with this becoming part of otel we will begin seeing something like that.
I'd like this library amirsalarsafaei/sqlc-pgx-monitoring to have a configuration option to add the de facto standardized field traceparent to the SQL as comments sent to the database. For example:
select * from widgets /*traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01'*/
I want to be able to use both Cloud SQL Insights and Cloud Trace in my application, and I think amirsalarsafaei/sqlc-pgx-monitoring could do it for me.
By comparison, XSAM/otelsql offers this as a feature.
WithSQLCommenter will enable or disable context propagation for the database by injecting a comment into SQL statements.
e.g., a SQL query:
SELECT * from FOO
will become
SELECT * from FOO /*traceparent='00-4bf92f3577b34da6a3ce929d0e0e4736-00f067aa0ba902b7-01',tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7'*/
Google maintains a light core Go implementation of SQLCommenter that has some of the common keys here https://github.com/google/sqlcommenter/blob/master/go/core/core.go#L38
Currently, the pgx driver supports adding a QueryRewriter interface, which is passed as an argument. RewriteQuery is called after TraceQueryEnd as seen here so you could create a wrapper for pgx.Conn that inserts a QueryRewriter that injects trace information as the first arg to method calls to the underlying pgx.Conn.
However, if pgx QueryRewriter is used as is it could have a negative impact on performance. In particular, adding something like a trace ID to the SQL text as a comment would make the prepared statement cache counterproductive. You would have to fall back to an exec mode that took two round trips or forego the binary format.
An ideal pgx interface for this SQLCommenter functionality would be to have some way of working with QueryExecModeCacheDescribe such that the annotated query was sent to PostgreSQL but the unannotated query was used to find the query description.
I added a proposed pgx design in this comment jackc/pgx#1935 (comment) which if it is accepted, implemented and merged, I would like to return and make a PR here to add this functionality.
What do you think?