Category: Uncategorized

/*
================================================================================
pg_whoisactive - PostgreSQL Session Monitoring Tool
================================================================================

DESCRIPTION:
    A comprehensive PostgreSQL inspired by Adam Machanic's sp_whoisactive stored 
    procedure used on SQL server. This function provides detailed real-time information about active 
    database sessions, including:
    
    - Current executing queries and their duration
    - Session blocking and lock information  
    - I/O statistics and resource usage
    - Connection details and client information
    - Transaction status and isolation levels
    - Wait states and performance metrics

CAPABILITIES:
    ✓ Monitor active and idle database sessions
    ✓ Identify blocking chains and deadlock scenarios
    ✓ Track query execution times and resource consumption
    ✓ Display I/O statistics (reads, writes, temp usage)
    ✓ Show connection information (host, application, user)
    ✓ Filter sessions by various criteria
    ✓ Format output for easy analysis
    ✓ Help system with parameter documentation

PARAMETERS:
    show_own_spid (BOOLEAN, default: FALSE)
        - Include the current session in results
        
    show_system_spids (BOOLEAN, default: FALSE)  
        - Include system/background processes
        
    show_sleeping_spids (INTEGER, default: 1)
        - 0: Show only active sessions
        - 1: Show active + idle sessions with open transactions
        - 2: Show all sessions (including completely idle)
        
    get_full_inner_text (BOOLEAN, default: FALSE)
        - TRUE: Display complete SQL query text
        - FALSE: Truncate long queries to 100 characters
        
    get_plans (BOOLEAN, default: FALSE)
        - Reserved for future query plan integration
        
    get_locks (BOOLEAN, default: FALSE)
        - Include detailed lock information in wait_info column
        
    get_avg_time (BOOLEAN, default: FALSE)
        - Reserved for future average execution time tracking
        
    get_additional_info (BOOLEAN, default: FALSE)
        - Reserved for additional session metadata
        
    find_block_leaders (BOOLEAN, default: TRUE)
        - Identify and report blocking relationships between sessions
        
    delta_interval (INTEGER, default: 5)
        - Reserved for future delta calculations between samples
        
    format_output (INTEGER, default: 1)
        - 1: Normal output format
        - 2: Reserved for detailed format
        
    destination_table (TEXT, default: NULL)
        - Reserved for future table output functionality
        
    help (BOOLEAN, default: FALSE)
        - Display help information and parameter descriptions

OUTPUT COLUMNS:
    dd_hh_mm_ss              - Session duration (DD:HH:MM:SS or HH:MM:SS)
    session_id               - PostgreSQL process ID (PID)
    sql_text                 - Current SQL statement being executed
    sql_command              - SQL command type (SELECT, INSERT, UPDATE, etc.)
    login_name               - Database username
    wait_info                - Current wait state or blocking information
    tran_log_writes          - Transaction information and duration
    cpu_time                 - CPU time used (reserved for future use)
    tempdb_allocations       - Temporary file count
    tempdb_current           - Current temporary space usage
    blocking_session_id      - PID of session blocking this one
    blocked_session_count    - Number of sessions this one is blocking
    reads                    - Read I/O statistics
    writes                   - Write I/O statistics  
    context_switches         - Context switch information (reserved)
    physical_io              - Physical I/O metrics (reserved)
    query_cost               - Query execution cost (reserved)
    status                   - Current session status
    isolation_level          - Transaction isolation level (reserved)
    open_tran_count          - Number of open transactions
    percent_complete         - Query completion percentage (reserved)
    host_name                - Client hostname or IP address
    database_name            - Current database name
    program_name             - Client application name
    start_time               - When the current query started
    login_time               - When the session was established
    request_id               - Request identifier (uses PID)
    collection_time          - Timestamp when data was collected

USAGE EXAMPLES:
    -- Basic monitoring (most common use case)
    SELECT * FROM pg_whoisactive();
    
    -- Show all sessions including idle ones
    SELECT * FROM pg_whoisactive(show_sleeping_spids := 2);
    
    -- Focus on blocking issues
    SELECT * FROM pg_whoisactive(find_block_leaders := true, get_locks := true)
    WHERE blocking_session_id IS NOT NULL OR blocked_session_count > 0;
    
    -- Detailed analysis of active sessions
    SELECT * FROM pg_whoisactive(
        show_sleeping_spids := 0,
        get_full_inner_text := true,
        get_locks := true
    );
    
    -- Monitor specific session
    SELECT * FROM pg_whoisactive() WHERE session_id = 12345;
    
    -- Get help
    SELECT * FROM pg_whoisactive(help := true);

COMPANION FUNCTIONS:
    pg_blocking_tree() - Shows hierarchical blocking relationships

COMPATIBILITY:
    - PostgreSQL 9.6+
    - Requires pg_stat_activity and pg_locks system views
    - Works with all PostgreSQL editions

AUTHOR: Database Monitoring Tool
VERSION: 1.0
CREATED: 2024
================================================================================
*/

CREATE OR REPLACE FUNCTION public.pg_whoisactive(
    show_own_spid BOOLEAN DEFAULT FALSE,           -- Show current session
    show_system_spids BOOLEAN DEFAULT FALSE,       -- Show system processes  
    show_sleeping_spids INTEGER DEFAULT 1,         -- 0=no idle, 1=sleeping with open trans, 2=all sleeping
    get_full_inner_text BOOLEAN DEFAULT FALSE,     -- Show full query text
    get_plans BOOLEAN DEFAULT FALSE,               -- Get query plans (if available)
    get_locks BOOLEAN DEFAULT FALSE,               -- Show lock information
    get_avg_time BOOLEAN DEFAULT FALSE,            -- Show average execution times
    get_additional_info BOOLEAN DEFAULT FALSE,     -- Show additional session info
    find_block_leaders BOOLEAN DEFAULT TRUE,       -- Identify blocking chain leaders
    delta_interval INTEGER DEFAULT 5,              -- Seconds between samples for delta calculations
    format_output INTEGER DEFAULT 1,               -- 1=normal, 2=detailed
    destination_table TEXT DEFAULT NULL,           -- Future: output to table
    help BOOLEAN DEFAULT FALSE                     -- Show help information
)
RETURNS TABLE (
    dd_hh_mm_ss TEXT,                    -- Session duration
    session_id INTEGER,                  -- Process ID
    sql_text TEXT,                       -- Current SQL statement
    sql_command TEXT,                    -- Command type (SELECT, INSERT, etc.)
    login_name TEXT,                     -- Username
    wait_info TEXT,                      -- What the session is waiting for
    tran_log_writes TEXT,               -- Transaction info
    cpu_time TEXT,                      -- CPU time (if available)
    tempdb_allocations TEXT,            -- Temp space usage
    tempdb_current TEXT,                -- Current temp usage
    blocking_session_id INTEGER,        -- Session that's blocking this one
    blocked_session_count INTEGER,      -- How many sessions this one is blocking
    reads TEXT,                         -- Read statistics
    writes TEXT,                        -- Write statistics
    context_switches TEXT,              -- Context switch info
    physical_io TEXT,                   -- Physical I/O
    query_cost TEXT,                    -- Query cost (if available)
    status TEXT,                        -- Session status
    isolation_level TEXT,               -- Transaction isolation level
    open_tran_count INTEGER,            -- Number of open transactions
    percent_complete TEXT,              -- Progress (if available)
    host_name TEXT,                     -- Client hostname/IP
    database_name TEXT,                 -- Current database
    program_name TEXT,                  -- Application name
    start_time TIMESTAMP WITH TIME ZONE, -- When session started
    login_time TIMESTAMP WITH TIME ZONE, -- When user logged in
    request_id INTEGER,                  -- Request ID
    collection_time TIMESTAMP WITH TIME ZONE -- When this data was collected
) 
LANGUAGE plpgsql
AS $function$

-- pg_whoisactive - PostgreSQL equivalent of SQL Server's sp_whoisactive
-- A comprehensive session monitoring tool for PostgreSQL

DECLARE
    rec RECORD;
    blocking_info RECORD;
    blocked_count INTEGER;
    lock_info TEXT;
    wait_description TEXT;
    query_text TEXT;
    cmd_type TEXT;
    duration_formatted TEXT;
    days INTEGER;
    hours INTEGER;
    minutes INTEGER;
    seconds INTEGER;
    total_seconds NUMERIC;
    reads_value TEXT;
    writes_value TEXT;
    temp_files_value TEXT;
    temp_bytes_value TEXT;
BEGIN
    -- Show help if requested
    IF help THEN
        RETURN QUERY
        SELECT 
            'HELP'::TEXT as dd_hh_mm_ss,
            0 as session_id,
            'pg_whoisactive - PostgreSQL equivalent of sp_whoisactive'::TEXT as sql_text,
            'HELP'::TEXT as sql_command,
            'show_own_spid: Include current session (default: false)'::TEXT as login_name,
            'show_system_spids: Include system processes (default: false)'::TEXT as wait_info,
            'show_sleeping_spids: 0=no idle, 1=idle with trans, 2=all idle (default: 1)'::TEXT as tran_log_writes,
            'get_full_inner_text: Show complete SQL text (default: false)'::TEXT as cpu_time,
            'get_locks: Include lock information (default: false)'::TEXT as tempdb_allocations,
            'find_block_leaders: Identify blocking leaders (default: true)'::TEXT as tempdb_current,
            NULL::INTEGER as blocking_session_id,
            NULL::INTEGER as blocked_session_count,
            'Usage: SELECT * FROM pg_whoisactive();'::TEXT as reads,
            'Advanced: SELECT * FROM pg_whoisactive(show_sleeping_spids := 2, get_locks := true);'::TEXT as writes,
            NULL::TEXT as context_switches,
            NULL::TEXT as physical_io,
            NULL::TEXT as query_cost,
            NULL::TEXT as status,
            NULL::TEXT as isolation_level,
            NULL::INTEGER as open_tran_count,
            NULL::TEXT as percent_complete,
            NULL::TEXT as host_name,
            NULL::TEXT as database_name,
            NULL::TEXT as program_name,
            NULL::TIMESTAMP WITH TIME ZONE as start_time,
            NULL::TIMESTAMP WITH TIME ZONE as login_time,
            NULL::INTEGER as request_id,
            now() as collection_time;
        RETURN;
    END IF;

    -- Main query logic
    FOR rec IN 
        SELECT 
            a.pid,
            a.usename,
            a.application_name,
            a.client_addr,
            a.client_hostname,
            a.backend_start,
            a.xact_start,
            a.query_start,
            a.state_change,
            a.state,
            a.query,
            a.datname,
            a.backend_type,
            -- Get I/O stats if available
            COALESCE(s.blks_read, 0) as blks_read,
            COALESCE(s.blks_hit, 0) as blks_hit,
            COALESCE(s.tup_returned, 0) as tup_returned,
            COALESCE(s.tup_fetched, 0) as tup_fetched,
            COALESCE(s.tup_inserted, 0) as tup_inserted,
            COALESCE(s.tup_updated, 0) as tup_updated,
            COALESCE(s.tup_deleted, 0) as tup_deleted,
            COALESCE(s.temp_files, 0) as temp_files,
            COALESCE(s.temp_bytes, 0) as temp_bytes
        FROM pg_stat_activity a
        LEFT JOIN pg_stat_database s ON s.datname = a.datname
        WHERE 
            -- Filter current session
            (show_own_spid = TRUE OR a.pid <> pg_backend_pid())
            -- Filter system processes  
            AND (show_system_spids = TRUE OR (a.datname IS NOT NULL AND a.backend_type = 'client backend'))
            -- Filter idle connections based on show_sleeping_spids parameter
            AND (
                show_sleeping_spids = 2 OR  -- Show all
                (show_sleeping_spids = 1 AND (a.state <> 'idle' OR a.xact_start IS NOT NULL)) OR  -- Show active or idle in transaction
                (show_sleeping_spids = 0 AND a.state = 'active')  -- Show only active
            )
        ORDER BY 
            CASE WHEN a.state = 'active' THEN 1 ELSE 2 END,  -- Active sessions first
            a.query_start DESC NULLS LAST
    LOOP
        -- Calculate and format session duration
        total_seconds := EXTRACT(EPOCH FROM (COALESCE(now() - rec.backend_start, INTERVAL '0')));
        days := FLOOR(total_seconds / 86400);
        hours := FLOOR((total_seconds % 86400) / 3600);
        minutes := FLOOR((total_seconds % 3600) / 60);
        seconds := FLOOR(total_seconds % 60);
        
        IF days > 0 THEN
            duration_formatted := LPAD(days::TEXT, 2, '0') || ':' || LPAD(hours::TEXT, 2, '0') || ':' || LPAD(minutes::TEXT, 2, '0') || ':' || LPAD(seconds::TEXT, 2, '0');
        ELSE
            duration_formatted := LPAD(hours::TEXT, 2, '0') || ':' || LPAD(minutes::TEXT, 2, '0') || ':' || LPAD(seconds::TEXT, 2, '0');
        END IF;

        -- Process SQL text
        IF get_full_inner_text THEN
            query_text := rec.query;
        ELSE
            query_text := CASE 
                WHEN rec.query IS NULL THEN NULL
                WHEN LENGTH(rec.query) > 100 THEN LEFT(rec.query, 97) || '...'
                ELSE rec.query 
            END;
        END IF;

        -- Extract command type
        IF rec.query IS NOT NULL AND TRIM(rec.query) <> '' THEN
            cmd_type := UPPER(SPLIT_PART(TRIM(rec.query), ' ', 1));
            IF cmd_type = '' THEN cmd_type := NULL; END IF;
        ELSE
            cmd_type := NULL;
        END IF;

        -- Determine wait info and status
        wait_description := CASE 
            WHEN rec.state = 'active' THEN 'RUNNABLE'
            WHEN rec.state = 'idle' THEN 'IDLE'
            WHEN rec.state = 'idle in transaction' THEN 'IDLE IN TRANSACTION'
            WHEN rec.state = 'idle in transaction (aborted)' THEN 'IDLE IN TRANSACTION (ABORTED)'
            WHEN rec.state = 'fastpath function call' THEN 'FASTPATH FUNCTION CALL'
            WHEN rec.state = 'disabled' THEN 'DISABLED'
            ELSE UPPER(rec.state)
        END;

        -- Find blocking information if requested
        IF find_block_leaders THEN
            -- Check if this session is blocked
            SELECT b.blocking_pid INTO blocking_info
            FROM (
                SELECT 
                    blocked_locks.pid as blocked_pid,
                    blocking_locks.pid as blocking_pid
                FROM pg_catalog.pg_locks blocked_locks
                    JOIN pg_catalog.pg_locks blocking_locks ON (
                        blocking_locks.locktype = blocked_locks.locktype
                        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
                        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
                        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
                        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
                        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
                        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
                        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
                        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
                        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
                        AND blocking_locks.virtualtransaction IS NOT DISTINCT FROM blocked_locks.virtualtransaction
                    )
                WHERE NOT blocked_locks.granted 
                    AND blocking_locks.granted
                    AND blocked_locks.pid <> blocking_locks.pid
            ) b
            WHERE b.blocked_pid = rec.pid;

            -- Count how many sessions this one is blocking
            SELECT COUNT(*) INTO blocked_count
            FROM (
                SELECT 
                    blocked_locks.pid as blocked_pid,
                    blocking_locks.pid as blocking_pid
                FROM pg_catalog.pg_locks blocked_locks
                    JOIN pg_catalog.pg_locks blocking_locks ON (
                        blocking_locks.locktype = blocked_locks.locktype
                        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
                        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
                        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
                        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
                        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
                        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
                        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
                        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
                        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
                        AND blocking_locks.virtualtransaction IS NOT DISTINCT FROM blocked_locks.virtualtransaction
                    )
                WHERE NOT blocked_locks.granted 
                    AND blocking_locks.granted
                    AND blocked_locks.pid <> blocking_locks.pid
            ) b
            WHERE b.blocking_pid = rec.pid;
        ELSE
            blocking_info := NULL;
            blocked_count := 0;
        END IF;

        -- Get lock information if requested
        IF get_locks THEN
            SELECT STRING_AGG(
                l.locktype || ':' || l.mode || 
                CASE WHEN l.granted THEN ':GRANTED' ELSE ':WAITING' END, 
                ', '
            ) INTO lock_info
            FROM pg_locks l
            WHERE l.pid = rec.pid;
        ELSE
            lock_info := NULL;
        END IF;

        -- Format read/write statistics
        reads_value := CASE 
            WHEN rec.blks_read > 0 OR rec.blks_hit > 0 THEN 
                'Logical: ' || (rec.blks_read + rec.blks_hit)::TEXT || ', Physical: ' || rec.blks_read::TEXT
            ELSE NULL 
        END;

        writes_value := CASE 
            WHEN (rec.tup_inserted + rec.tup_updated + rec.tup_deleted) > 0 THEN 
                'Rows: ' || (rec.tup_inserted + rec.tup_updated + rec.tup_deleted)::TEXT
            ELSE NULL 
        END;

        -- Format temp usage
        temp_files_value := CASE 
            WHEN rec.temp_files > 0 THEN rec.temp_files::TEXT || ' files'
            ELSE NULL 
        END;

        temp_bytes_value := CASE 
            WHEN rec.temp_bytes > 0 THEN pg_size_pretty(rec.temp_bytes)
            ELSE NULL 
        END;

        -- Return the row
        RETURN QUERY
        SELECT 
            duration_formatted,                                         -- dd_hh_mm_ss
            rec.pid,                                                   -- session_id
            query_text,                                                -- sql_text
            cmd_type,                                                  -- sql_command
            rec.usename::TEXT,                                         -- login_name
            CASE 
                WHEN blocking_info.blocking_pid IS NOT NULL THEN 
                    'BLOCKED BY: ' || blocking_info.blocking_pid::TEXT
                WHEN get_locks AND lock_info IS NOT NULL THEN lock_info
                ELSE wait_description 
            END,                                                       -- wait_info
            CASE 
                WHEN rec.xact_start IS NOT NULL THEN 
                    'Open: ' || EXTRACT(EPOCH FROM (now() - rec.xact_start))::INTEGER::TEXT || 's'
                ELSE NULL 
            END,                                                       -- tran_log_writes
            NULL::TEXT,                                                -- cpu_time (not available in PostgreSQL)
            temp_files_value,                                          -- tempdb_allocations
            temp_bytes_value,                                          -- tempdb_current
            blocking_info.blocking_pid,                                -- blocking_session_id
            CASE WHEN blocked_count > 0 THEN blocked_count ELSE NULL END, -- blocked_session_count
            reads_value,                                               -- reads
            writes_value,                                              -- writes
            NULL::TEXT,                                                -- context_switches (not available)
            NULL::TEXT,                                                -- physical_io
            NULL::TEXT,                                                -- query_cost (would need EXPLAIN)
            wait_description,                                          -- status
            NULL::TEXT,                                                -- isolation_level (would need transaction info)
            CASE WHEN rec.xact_start IS NOT NULL THEN 1 ELSE 0 END,   -- open_tran_count
            NULL::TEXT,                                                -- percent_complete (not available)
            COALESCE(rec.client_hostname, rec.client_addr::TEXT, 'local'), -- host_name
            rec.datname::TEXT,                                         -- database_name
            COALESCE(rec.application_name::TEXT, 'unknown'),           -- program_name
            rec.query_start,                                           -- start_time
            rec.backend_start,                                         -- login_time
            rec.pid,                                                   -- request_id (using pid)
            now();                                                     -- collection_time
    END LOOP;
END;
$function$;

-- Create a helper function for quick blocking analysis
CREATE OR REPLACE FUNCTION pg_blocking_tree()
RETURNS TABLE (
    level INTEGER,
    blocking_pid INTEGER,
    blocked_pid INTEGER,
    blocked_user TEXT,
    blocked_query TEXT,
    blocked_duration TEXT
)
LANGUAGE sql
AS $$
/*
================================================================================
pg_blocking_tree - Hierarchical Blocking Chain Analysis
================================================================================

DESCRIPTION:
    Displays blocking relationships in a hierarchical tree format, showing
    which sessions are blocking others and the chain of dependencies.
    
RETURNS:
    level               - Depth in the blocking hierarchy
    blocking_pid        - Session causing the block
    blocked_pid         - Session being blocked
    blocked_user        - Username of blocked session
    blocked_query       - SQL being blocked
    blocked_duration    - How long the session has been blocked

USAGE:
    SELECT * FROM pg_blocking_tree();
================================================================================
*/
    WITH RECURSIVE blocking_tree AS (
        -- Find the root blockers (sessions that block others but aren't blocked themselves)
        SELECT 
            1 as level,
            blocking_locks.pid as blocking_pid,
            blocked_locks.pid as blocked_pid,
            blocked_activity.usename as blocked_user,
            blocked_activity.query as blocked_query,
            EXTRACT(EPOCH FROM (now() - blocked_activity.query_start))::INTEGER::TEXT || 's' as blocked_duration
        FROM pg_catalog.pg_locks blocked_locks
            JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
            JOIN pg_catalog.pg_locks blocking_locks ON (
                blocking_locks.locktype = blocked_locks.locktype
                AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
                AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
                AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
                AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
                AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
                AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
                AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
                AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
                AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
                AND blocking_locks.virtualtransaction IS NOT DISTINCT FROM blocked_locks.virtualtransaction
            )
        WHERE NOT blocked_locks.granted 
            AND blocking_locks.granted
            AND blocked_locks.pid <> blocking_locks.pid
    )
    SELECT * FROM blocking_tree
    ORDER BY level, blocking_pid, blocked_pid;
$$;

/*
================================================================================
USAGE EXAMPLES AND REFERENCE
================================================================================

-- BASIC MONITORING
--------------------------------------------------------------------------------
-- View all active sessions (most common usage)
SELECT * FROM pg_whoisactive();

-- Show only currently executing queries
SELECT * FROM pg_whoisactive(show_sleeping_spids := 0);

-- Include all sessions (even completely idle ones)
SELECT * FROM pg_whoisactive(show_sleeping_spids := 2);


-- BLOCKING ANALYSIS
--------------------------------------------------------------------------------
-- Quick check for blocking issues
SELECT * FROM pg_whoisactive(find_block_leaders := true) 
WHERE blocking_session_id IS NOT NULL OR blocked_session_count > 0;

-- Detailed blocking analysis with lock information
SELECT session_id, login_name, sql_text, wait_info, blocking_session_id, blocked_session_count
FROM pg_whoisactive(get_locks := true, find_block_leaders := true)
WHERE blocking_session_id IS NOT NULL OR blocked_session_count > 0;

-- Show complete blocking hierarchy
SELECT * FROM pg_blocking_tree();


-- DETAILED SESSION ANALYSIS  
--------------------------------------------------------------------------------
-- Full query text and detailed information
SELECT * FROM pg_whoisactive(
    get_full_inner_text := true,
    get_locks := true,
    find_block_leaders := true
);

-- Monitor specific session by PID
SELECT * FROM pg_whoisactive() WHERE session_id = 12345;

-- Long-running transactions
SELECT session_id, login_name, sql_command, tran_log_writes, dd_hh_mm_ss
FROM pg_whoisactive()
WHERE tran_log_writes IS NOT NULL
ORDER BY dd_hh_mm_ss DESC;


-- PERFORMANCE MONITORING
--------------------------------------------------------------------------------
-- Sessions with high I/O activity
SELECT session_id, login_name, sql_command, reads, writes, tempdb_current
FROM pg_whoisactive()
WHERE reads IS NOT NULL OR writes IS NOT NULL OR tempdb_current IS NOT NULL;

-- Currently active sessions sorted by duration
SELECT session_id, dd_hh_mm_ss, login_name, sql_text, status
FROM pg_whoisactive(show_sleeping_spids := 0)
ORDER BY dd_hh_mm_ss DESC;


-- TROUBLESHOOTING QUERIES
--------------------------------------------------------------------------------
-- Find sessions from specific application
SELECT * FROM pg_whoisactive() WHERE program_name LIKE '%myapp%';

-- Find sessions from specific user
SELECT * FROM pg_whoisactive() WHERE login_name = 'problem_user';

-- Find sessions connected from specific host
SELECT * FROM pg_whoisactive() WHERE host_name LIKE '%problemhost%';

-- Sessions in specific database
SELECT * FROM pg_whoisactive() WHERE database_name = 'production_db';


-- ADMINISTRATIVE TASKS
--------------------------------------------------------------------------------
-- Help and parameter reference
SELECT * FROM pg_whoisactive(help := true);

-- Include system processes for maintenance operations
SELECT * FROM pg_whoisactive(show_system_spids := true);

-- Monitor your own session activity
SELECT * FROM pg_whoisactive(show_own_spid := true) WHERE session_id = pg_backend_pid();

================================================================================
*/