Define context propagation for SQL statements

[anuraaga]

sqlcommenter has defined a format for propagating context into SQL statements for propagation through DBs. As part of bringing it to OTel, we should probably start by speccing out propagation. I suspect with such a spec it would be fairly simple to implement in many existing OTel SQL instrumentation libraries.

Related discussion arguing against a snowflake approach to sqlcommenter instrumentation:
open-telemetry/community#783 (comment)

[StevenACoffman]

The docs for SQL Server describe context propagation here, but I don't believe anyone has discussed this for PostgreSQL, and especially in the context of prepared statements.

For PostgreSQL, there are only two viable trace context propagation mechanisms: SQLCommenter and GUC.

PostgreSQL setting parameters are often called GUC, short for the name of the ‘Grand Unified Configuration scheme’ project that introduced them. GUC can be per function via CREATE FUNCTION ... SET and per subtransaction with SET LOCAL, so they work within prepared statements.

The SQLCommenter spec currently acts as a post-processor that adds comments to queries. This is problematic for prepared statements in databases like PostgreSQL.

At least for PostgreSQL, when using prepared statements, the SQL text isn't sent to the server -- only the prepared statement name. In stored procedures, it is possible to bind variables in comments like:

select 1 /*tracespan=$1*/

such that it can still be prepared, and a trace context can then be added at query execution time.

SQL Commenter has an unaddressed proposal issue to add this to the spec: google/sqlcommenter#245

pg_tracing Trace Propagation

SQLCommenter

Trace context can be propagated through SQLCommenter. By default, all queries with a SQLCommenter with a sampled flag enabled will generate spans.

-- Query with trace context and sampled flag enable
/*traceparent='00-00000000000000000000000000000123-0000000000000123-01'*/ SELECT 1;

-- Check the generated spans
select trace_id, parent_id, span_id, span_start, span_end, span_type, span_operation from pg_tracing_consume_spans order by span_start;
             trace_id             |    parent_id     |     span_id      |          span_start           |           span_end            |  span_type   | span_operation
----------------------------------+------------------+------------------+-------------------------------+-------------------------------+--------------+----------------
 00000000000000000000000000000123 | 0000000000000123 | 4268a4281c5316dd | 2024-03-19 13:46:43.97958+00  | 2024-03-19 13:46:43.980121+00 | Select query | SELECT $1;
 00000000000000000000000000000123 | 4268a4281c5316dd | 87cb96b6459880a0 | 2024-03-19 13:46:43.979642+00 | 2024-03-19 13:46:43.979978+00 | Planner      | Planner
 00000000000000000000000000000123 | 4268a4281c5316dd | f5994f9159d8e80d | 2024-03-19 13:46:43.980081+00 | 2024-03-19 13:46:43.980111+00 | Executor     | ExecutorRun

trace_context GUC

The GUC variable pg_tracing.trace_context can also be used to propagate trace context.

BEGIN;
SET LOCAL pg_tracing.trace_context='traceparent=''00-00000000000000000000000000000005-0000000000000005-01''';
UPDATE pgbench_accounts SET abalance=1 where aid=1;
COMMIT;

-- Check generated span
select trace_id, span_start, span_end, span_type, span_operation from pg_tracing_consume_spans order by span_start;
             trace_id             |          span_start           |           span_end            |     span_type     |                      span_operation
----------------------------------+-------------------------------+-------------------------------+-------------------+-----------------------------------------------------------
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305234+00 | 2024-07-05 14:24:55.305988+00 | Update query      | UPDATE pgbench_accounts SET abalance=$1 where aid=$2;
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305266+00 | 2024-07-05 14:24:55.30552+00  | Planner           | Planner
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305586+00 | 2024-07-05 14:24:55.305906+00 | ExecutorRun       | ExecutorRun
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305591+00 | 2024-07-05 14:24:55.305903+00 | Update            | Update on pgbench_accounts
 00000000000000000000000000000005 | 2024-07-05 14:24:55.305593+00 | 2024-07-05 14:24:55.305806+00 | IndexScan         | IndexScan using pgbench_accounts_pkey on pgbench_accounts
 00000000000000000000000000000005 | 2024-07-05 14:24:55.649757+00 | 2024-07-05 14:24:55.649792+00 | Utility query     | COMMIT;
 00000000000000000000000000000005 | 2024-07-05 14:24:55.649787+00 | 2024-07-05 14:24:55.649792+00 | ProcessUtility    | ProcessUtility
 00000000000000000000000000000005 | 2024-07-05 14:24:55.649816+00 | 2024-07-05 14:24:55.650613+00 | TransactionCommit | TransactionCommit

[StevenACoffman]

@XSAM I moved most of my PostgreSQL context propagation comment from your open-telemetry/semantic-conventions#2749 over here

[svrnm]

@open-telemetry/technical-committee can you please take a look and decide whether guidance for databases ins general should be part of the spec, and if yes, provide some directions around context propagation for database

[marcalff]

All,

Disclosure: I am an Oracle employee, and work on OpenTelemetry for MySQL. Opinions are my own.

MySQL already supports context propagation for otel.

This is implemented by sending the trace context in a dedicated query attribute parameter, which is basically metadata associated with a request sent from the client to the server, separate from the SQL query text itself.

So, in short:

• Hiding the trace context for propagation in a SQL comment is what you do when the client server protocol can not be changed, and when there are no places in the protocol to put this data.
• vendors who define the database wire protocol may want (we do) to pass the trace context where they see fit, and not embed this inside a sql comment.

Also, keep in mind that adding a trace context inside a SQL comment makes the assumption that there is somewhere a SQL text to send with the query, which is not the case for prepared statements.

When a protocol is normalized (HTTP), it makes sense to define how propagation works for this protocol (headers), for interoperability.

This is not the case in the database world, where different vendors have different wire protocols.

I think various database vendors should have the freedom to implement context propagation as they see fit.

Client connectors, provided by database vendors, will know where to put the trace context when sending request on the wire, this is not up to opentelemetry-specifications to decide.

Likewise, on the receiving end, the vendor database server will know where to get the trace context from, this is not something up to opentelemetry-specifications to decide.

In a world where there is a unified sql wire protocol, adopted by all database vendors, defining how propagation works for this protocol would make sense, but there is no such a thing.

References:

• https://dev.mysql.com/doc/refman/9.4/en/telemetry.html
• https://dev.mysql.com/doc/connector-j/en/connector-j-opentelemetry.html

[marcalff]

Some additional comments on propagation:

For propagation to actually work, both the sender and receiver must agree on how the trace context is propagated in the wire protocol.

Writing a spec for SQL trace propagation assumes adoption from database vendors on the spec, but I have not seen database vendors in general represented in this discussion.

[tigrannajaryan]

We discussed this in the TC since it landed in our inbox. There is 2 parts that we see here:

1. We had SQLCommenter project open for a while but it did not get enough traction to become an active and sustained Otel project and that's why we archived it. If there is renewed interest we can revisit this decision but we need to see enough community interest and 2 or more contributors willing to actively maintain it.
2. There is a desire to standardize context propagation for database protocols (even when not using SQLCommenter). Otel is not planning to get into the business of defining wire protocols for databases. What Otel can do to help is define semantic conventions for representing the context in any medium that allows recording generic key/value lists. Database vendors then hopefully will be interested in adopting these conventions and apply them using whatever mechanism they have available in their protocol. This approach has a precedence, e.g. Otel defines context propagation via env variables. This work in Otel should happen within the Semconv SIG. If there are enough interested parties to work on this I suggest going to Semconv SIG and opening the discussion there.