sql: expose always-on tracing inflight registry using vtable and builtins

[tbg]

Is your feature request related to a problem? Please describe.
As part of always-on tracing, a node-local registry of inflight operations will be introduced (#55592). This registry would be most useful if it could be queried from SQL.

This would improve our troubleshooting story as we could filter on long-running KV trace spans in that table to easily detect hanging operations (unavailable range, heavy contention) in a self-service fashion.

Describe the solution you'd like
Introduce a virtual table crdb_internal.tracing_inflight (big question mark on the name) that pulls the node-local registry from all nodes in the cluster.

Add a view on that table that also translate (where possible) KV txn id -> SQL txn ID to allow filtering efficiently.

Describe alternatives you've considered

Additional context

[tbg]

For starters, we should probably align closely with the schema of crdb_internal.session_tracing:

cockroach/pkg/sql/crdb_internal.go

Lines 1103 to 1132 in f4b475a

	var crdbInternalSessionTraceTable = virtualSchemaTable{
	comment: `session trace accumulated so far (RAM)`,
	schema: `
	CREATE TABLE crdb_internal.session_trace (
	span_idx INT NOT NULL, -- The span's index.
	message_idx INT NOT NULL, -- The message's index within its span.
	timestamp TIMESTAMPTZ NOT NULL,-- The message's timestamp.
	duration INTERVAL, -- The span's duration. Set only on the first
	-- (dummy) message on a span.
	-- NULL if the span was not finished at the time
	-- the trace has been collected.
	operation STRING NULL, -- The span's operation.
	loc STRING NOT NULL, -- The file name / line number prefix, if any.
	tag STRING NOT NULL, -- The logging tag, if any.
	message STRING NOT NULL, -- The logged message.
	age INTERVAL NOT NULL -- The age of this message relative to the beginning of the trace.
	)`,
	populate: func(ctx context.Context, p *planner, _ *dbdesc.Immutable, addRow func(...tree.Datum) error) error {
	rows, err := p.ExtendedEvalContext().Tracing.getSessionTrace()
	if err != nil {
	return err
	}
	for _, r := range rows {
	if err := addRow(r[:]...); err != nil {
	return err
	}
	}
	return nil
	},
	}

However, since all spans are unfinished, we will want the duration column to be populated with time.Since(spanStart). This might mean that we need to change (Span).GetRecording(); I believe at the moment duration is returned as -1 for unfinished Spans.
We want to do this since a common expected usage pattern is to check for long-running spans via a filter on duration > 1h or something like that.

[tbg]

Discussed in pod: we'll go with a vtable that lists only the spans + some of their metadata (not their recordings) that in particular allows identifying a span as long-running, and then add a builtin that renders a session tracing like table for a particular traceID.

[tbg]

#59370 shows that there is value in adding a check on this vtable to roachtest. We'd fail the test if the vtable shows any long-running local root spans that have children attached to them.

[irfansharif]

For posterity, what's remaining here are built-ins that:

1. get the traceid/spanid of the currently open txn (if any)
2. renders the payloads (just payloads) given a traceid/spanid

[angelapwen]

Status update here:

@tbg added a builtin that retrieves the current trace ID as part of #59992.
We are holding off on adding a builtin that retrieves the current span ID until we see a use case for it.

I added a builtin that, given a span ID, displays a table representing all its payloads with columns for payload type and entire payload as JSONB #60784

What's left is adding functionality (via a SQL query delegate mechanism) to display a table representing all payloads for a given trace ID as that will be easier to use. Also, we want to improve the logic test for contention events

[angelapwen]

The builtin displaying all payloads for a given trace ID has been added (#60992) and logic test improved as part of the same PR. Closing out this issue for now 🎉