Feature Request: Support SqlComemnter

[StevenACoffman]

A while back, OTEL merged SqlCommenter (seeSqlCommenter merged into OpenTelemetry).

SqlCommenter works on the 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 Cloud SQL Insights, a proprietary Google Cloud offering). I am not aware of OSS solutions that do this ingestion and expose this data to the user yet, but I'm sure with this becoming part of otel we will begin seeing something like that.

I would like otelpgx to add the de facto standardized fields traceparent and tracestate to the SQL as comments sent to the database. For example:

select * from widgets /*traceparent='00-5bd66ef5095369c7b0d1f8f4bd33716a-c532cb4098ac3dd2-01',tracestate='congo%3Dt61rcWkgMzE%2Crojo%3D00f067aa0ba902b7'*/

I want to be able to use both Cloud SQL Insights and Cloud Trace in my application, and I think otelpgx could do it for me.

Currently, XSAM/otelsql offers this as an experimental 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'*/

[StevenACoffman]

Also worth mentioning that https://github.com/jbub/sqlcommenter allows sqlcommeter support for pgx driver.

[mfrister]

In addition to cloud services supporting this directly, this would also be useful for self-managed Postgres instances to associate queries in Postgres server logs (e.g. for deadlocks and slow queries) with traces.

[StevenACoffman]

Google implemented Go support for SQLCommenter here https://github.com/google/sqlcommenter/tree/master/go/core

Currently, the pgx driver does support adding a QueryRewriter interface which gets passed as an arg. 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.

See more here: jackc/pgx#1935 (comment)