2. Have query logging setup on your PG server. There are a bunch of ways to do this with minimal overhead. You can log slow queries (say queries >5ms), or log queries that fail, or sample queries.
Your query / log ends up getting something like:
2020-11-11 21:00:00 UTC:titusvpcservice@titusvpcservice:
[60294]:HINT: The transaction might succeed if retried.
2020-11-11 21:00:00 UTC:titusvpcservice@titusvpcservice:
[60294]:STATEMENT: /* md: {"spanID":"34c1a9f38fb44cad"} */
INSERT INTO assignments(branch_eni_association, assignment_id) VALUES ($1, $2) RETURNING id
You can then look at a Zipkin, and use the value within MD (spanID) to get the trace. I did this originally, because I wanted to transparently wrap the PG SQL Driver for Zipkin. Postgres can be oblivious to the fact there's "Zipkin Inside", because it's a terminal node. You can get the span ID from the BEGIN TRANSACTION / first query, and then tie that to the pid and timestamp, and then use that to go back and look through things with standard(ish) postgres introspection, since almost all of it has the pg_backend_pid + timestamp in it.
Have you considered to add the span ID to the application name instead? Combined with including the application name in the log_line_prefix that will make the logs easier to inspect in quite a few cases. Also makes querying pg_stat_activity etc easier, since you can see the current application_name for each connection in there.
Isn’t the application name set at connect time? Or can you change that after startup? (Re)connecting to the database and not having a pool is kind of a nightmare.
You need to:
1. Have an Opencensus tracing context
2. Have query logging setup on your PG server. There are a bunch of ways to do this with minimal overhead. You can log slow queries (say queries >5ms), or log queries that fail, or sample queries.
Your query / log ends up getting something like:
You can then look at a Zipkin, and use the value within MD (spanID) to get the trace. I did this originally, because I wanted to transparently wrap the PG SQL Driver for Zipkin. Postgres can be oblivious to the fact there's "Zipkin Inside", because it's a terminal node. You can get the span ID from the BEGIN TRANSACTION / first query, and then tie that to the pid and timestamp, and then use that to go back and look through things with standard(ish) postgres introspection, since almost all of it has the pg_backend_pid + timestamp in it.