How to identify origin of worker sessions

[begriffs]

Communication goals (e.g. detailed howto vs orientation)

Sai asked, "is there a way to identify the worker sessions (generated by say a router query) given a coordinator session?"

Good locations for content in docs structure

https://docs.citusdata.com/en/v7.2/admin_guide/cluster_management.html?highlight=diagnostic#useful-diagnostic-queries

How does this work? (devs)

Example sql

Sai cooked up these queries:

CREATE TEMP TABLE transactions_on_workers
AS
SELECT
    nodename,
    split_part(txn_on_worker, '$', 1) process_id,
    split_part(txn_on_worker, '$', 2) initiator_node_identifier,
    split_part(txn_on_worker, '$', 3) transaction_number,
    split_part(txn_on_worker, '$', 4) transaction_stamp
FROM (
    SELECT
        UNNEST(result::text [ ]) txn_on_worker,
        nodename
    FROM
        run_command_on_workers ($cmd$
            SELECT
                array_agg(process_id || '$' || initiator_node_identifier || '$' || transaction_number || '$' || transaction_stamp)
            FROM
                get_all_active_transactions () $cmd$)) a;

SELECT
    tw.nodename,
    tm.process_id coordinator_process,
    tw.process_id worker_process
FROM
    get_all_active_transactions () tm
    JOIN transactions_on_workers tw ON tm.initiator_node_identifier::text = tw.initiator_node_identifier::text
    AND tm.transaction_number::text = tw.transaction_number::text
    AND tm.transaction_stamp::text = tw.transaction_stamp::text;

-- If you want it more realtime you could remove the TEMP TABLE and do a subquery join.

Corner cases, gotchas

[saicitus]

The first command should be updated as below: Handled it with the below change to the first command:

CREATE temp TABLE transactions_on_workers AS 
  SELECT nodename, 
         Split_part(txn_on_worker, '$', 1) process_id, Split_part(txn_on_worker, 
'$', 2) initiator_node_identifier, Split_part(txn_on_worker, '$', 3) 
transaction_number, Split_part(txn_on_worker, '$', 4) transaction_stamp FROM ( 
SELECT unnest(RESULT::text[]) txn_on_worker, nodename FROM (SELECT CASE WHEN 
result ='' THEN '{}' ELSE result end result, nodename FROM 
run_command_on_workers($cmd$select array_agg(process_id||'$'|| 
initiator_node_identifier||'$'||transaction_number||'$'||transaction_stamp) FROM 
get_all_active_transactions()$cmd$))b)a;