Is there a way to extract UUID value from crdb_internal.transaction_contention_events.contending_key?

[ilslv]

Related to #61625

I'm trying to debug contention using crdb_internal.transaction_contention_events table and struggling to extract concrete key on which contention occurred. The closest I've got is manual extraction:

SELECT contending_key,
       crdb_internal.pretty_key(contending_key, 0),
       crdb_internal.pretty_key(contending_key, 2)
FROM crdb_internal.transaction_contention_events

contending_key	pretty_key(contending_key, 0)	pretty_key(contending_key, 2)
0xF718A38912EB8756533B934E979C231A3537F2AEB9000188	/6307/1/"\xeb\x87VS;\x93N\x97\x9c#\x1a57\xf2\xae\xb9"/0	/"\xeb\x87VS;\x93N\x97\x9c#\x1a57\xf2\xae\xb9"/0

SELECT b'\xeb\x87VS;\x93N\x97\x9c#\x1a57\xf2\xae\xb9'::UUID

uuid
eb875653-3b93-4e97-9c23-1a3537f2aeb9

Manually doing it seems really suboptimal. Is there a way to extract UUID value right inside the sql?

---

From what I an tell from signature of crdb_internal.encode_key(table_id: int, index_id: int, row_tuple: anyelement) → bytes, contending_key consists of table_id, index_id and row, so extracting third value from key with crdb_internal.pretty_key(contending_key, 2) already puts us closely to the desired value. But I couldn't figure out a way to transform /"\xeb\x87VS;\x93N\x97\x9c#\x1a57\xf2\xae\xb9"/0 string into a eb875653-3b93-4e97-9c23-1a3537f2aeb9 uuid.

[matthewtodd]

Hi, @ilslv, I dug in a bit and we don't have a very satisfying answer for you, since we're generally designing around these keys being opaque values. And crdb_internal.pretty_key formats raw bytes with Go's "%q" specifier, which we don't have any SQL function support for parsing.

But if I put this ALL CAPS DON'T DO THIS IN PRODUCTION warning here, for debugging purposes, you might have better luck pulling the bytes you want directly out of the key. The general format of the keys you're looking at is a couple of variable-length-encoded identifiers, then an 0x12 byte array marker, then the 16 bytes of the uuid, then maybe more stuff. The tricky part is not knowing how many bytes those leading identifiers will take. But once you figure out how you'd like to handle them, you could do something like this:

SELECT substring(x'F718A38912EB8756533B934E979C231A3537F2AEB9000188', 6, 16)::UUID;

[ilslv]

@matthewtodd thanks, I've stumbled myself into this kind of solution, but it would be nice for crdb_internal.pretty_key to interpret UUIDs and other common primary key types, as it does with TIMESTAMPTZ for example. Or at least have more documentation around this part.

For anyone wondering with sql I ended up with, here is very hacky way to get more info about contention events:

WITH tx AS (
    SELECT tx_statements.fingerprint_id,
           array_agg(st_stats.metadata ->> 'query') AS queries
    FROM (
        SELECT encode(fingerprint_id, 'hex') AS fingerprint_id,
               jsonb_array_elements_text(
                   metadata -> 'stmtFingerprintIDs'
               ) AS st_fingerprint_ids
        FROM (
            SELECT DISTINCT ON (fingerprint_id) *
            FROM crdb_internal.transaction_statistics
        ) AS distinct_tx
    ) AS tx_statements
    JOIN (
        SELECT DISTINCT ON (fingerprint_id) *
        FROM crdb_internal.statement_statistics
    ) AS st_stats ON encode(st_stats.fingerprint_id, 'hex')
                     = tx_statements.st_fingerprint_ids
    GROUP BY tx_statements.fingerprint_id
)
SELECT blocking.queries AS blocking_queries,
waiting.queries AS  waiting_queries,
ctk.table_name,
ctk.index_name,
ctk.key,
IF(crdb_internal.pretty_key(contending_key, 3) = '/0'
   AND strpos(crdb_internal.pretty_key(contending_key, 2), '\x') IS NOT NULL,
   from_uuid(left(right(contending_key, 19), 16)),
   IF(crdb_internal.pretty_key(contending_key, 4) = '/0'
      AND strpos(crdb_internal.pretty_key(contending_key, 2), '\x') IS NOT NULL
      AND strpos(crdb_internal.pretty_key(contending_key, 3), '\x') IS NOT NULL,
      '(' || from_uuid(left(right(contending_key, 38), 16))
          || ', '
          || from_uuid(left(right(contending_key, 19), 16))
          || ')',
      NULL)) AS pretty_key,
tx_ev.contention_duration,
tx_ev.collection_ts AS at
FROM crdb_internal.transaction_contention_events AS tx_ev
JOIN tx AS blocking
ON encode(tx_ev.blocking_txn_fingerprint_id, 'hex')
   = blocking.fingerprint_id
JOIN tx AS waiting
ON encode(tx_ev.waiting_txn_fingerprint_id,  'hex')
    = waiting.fingerprint_id
JOIN crdb_internal.cluster_contended_keys AS ctk
ON ctk.key = crdb_internal.pretty_key(tx_ev.contending_key, 0)
ORDER BY tx_ev.contention_duration DESC

It tries to interpret contention key as a single or pair of UUIDs.