Prompt:
You are my personal tutor for the topic AI-900, Azure AI fundamentals. Please help me learn the topic by having a learning dialogue with me. Guide me through the content, create examples, ask questions and let me work out the concepts. Only if I cannot answer your questions and analogies help me with hints and more guidance.
That’s it, that’s all the post.
By Vinay Rahul Are, Python Enthusiast \& Improv Comedy Fan
Learning Python can feel intimidating—unless you approach it with a sense of play! Just like improv comedy, Python is about saying “yes, and…” to new ideas, experimenting, and having fun. In this post, I’ll walk you through a real-world Python script, breaking down each part so you can understand, explain, and even perform it yourself!
The script we’ll explore automates the process of running multiple SQL files against an Amazon Redshift database. For each SQL file, it:
It’s a practical tool for data engineers, but the structure and logic are great for any Python beginner to learn from.
At the top, you’ll find a docstring—a big comment block that tells you what the script does, what you need to run it, and how to use it.
"""
Batch Redshift SQL Script Executor with Per-Script Logging, Timing, and Post-Execution Archiving
Pre-requisites:
---------------
1. Python 3.x installed on your machine.
2. The following Python packages must be installed:
- psycopg2-binary
3. (Recommended) Use a virtual environment to avoid dependency conflicts.
4. Network access to your Amazon Redshift cluster.
Installation commands:
----------------------
python -m venv venv
venv\Scripts\activate # On Windows
pip install psycopg2-binary
Purpose:
--------
This script automates the execution of multiple .sql files against an Amazon Redshift cluster...
"""
Every show needs its cast. In Python, that means importing modules:
import os
import glob
import psycopg2
import getpass
import shutil
import time
os, glob, shutil: Handle files and folderspsycopg2: Talks to the Redshift databasegetpass: Securely prompts for passwordstime: Measures how long things takeBefore the curtain rises, set your stage:
HOST = '<redshift-endpoint>'
PORT = 5439
USER = '<your-username>'
DATABASE = '<your-database>'
SCRIPT_DIR = r'C:\redshift_scripts'
DONE_DIR = os.path.join(SCRIPT_DIR, 'Done')
The main function, run_sql_script, is like a well-rehearsed bit:
def run_sql_script(script_path, conn):
log_path = os.path.splitext(script_path)[0] + '.log'
with open(script_path, 'r', encoding='utf-8') as sql_file, open(log_path, 'w', encoding='utf-8') as log_file:
sql = sql_file.read()
log_file.write(f"Running script: {script_path}\n")
start_time = time.perf_counter()
try:
with conn.cursor() as cur:
cur.execute(sql)
end_time = time.perf_counter()
elapsed_time = end_time - start_time
rows_affected = cur.rowcount if cur.rowcount != -1 else 'Unknown'
log_file.write(f"Rows affected: {rows_affected}\n")
log_file.write(f"Execution time: {elapsed_time:.2f} seconds\n")
conn.commit()
log_file.write("Execution successful.\n")
except Exception as e:
end_time = time.perf_counter()
elapsed_time = end_time - start_time
log_file.write(f"Error: {str(e)}\n")
log_file.write(f"Execution time (until error): {elapsed_time:.2f} seconds\n")
conn.rollback()
This is the showrunner, making sure everything happens in order:
def main():
password = getpass.getpass("Enter your Redshift password: ")
if not os.path.exists(DONE_DIR):
os.makedirs(DONE_DIR)
sql_files = glob.glob(os.path.join(SCRIPT_DIR, '*.sql'))
conn = psycopg2.connect(
host=HOST,
port=PORT,
user=USER,
password=password,
dbname=DATABASE
)
for script_path in sql_files:
print(f"Running {script_path} ...")
run_sql_script(script_path, conn)
try:
shutil.move(script_path, DONE_DIR)
print(f"Moved {script_path} to {DONE_DIR}")
except Exception as move_err:
print(f"Failed to move {script_path}: {move_err}")
conn.close()
print("All scripts executed.")
.sql filesThis line ensures the main event only happens if you run the script directly:
if __name__ == "__main__":
main()
“This script automates running a bunch of SQL files against a Redshift database. For each file, it logs how many rows were affected, how long it took, and any errors. After running, it moves the file to a ‘Done’ folder so you know it’s finished. It’s organized with clear sections for setup, reusable functions, and the main execution flow.”
Just like improv, Python is best learned by doing. Try things out, make mistakes, and remember: if your code “crashes,” it’s just the computer’s way of saying, “Yes, and…let’s try that again!”
If you want to dig deeper into any part of this script, just ask in the comments below. Happy coding—and yes, and… keep learning!
Need to give a user full control (dbo/db_owner) across every database in your SQL Server? Here’s how you can do it quickly using PowerShell and dbatools.
Why db_owner?
Adding a user to the db_owner role in each database gives them broad permissions to manage all aspects of those databases—ideal for trusted developers or DBAs in non-production environments.
Quick Steps with dbatools
New-DbaLogin -SqlInstance "YourInstance" -Login "YourUser"
$instance = "YourInstance"
$login = "YourUser"
Get-DbaDatabase -SqlInstance $instance | Where-Object { -not $_.IsSystemObject } | ForEach-Object {
New-DbaDbUser -SqlInstance $instance -Database $_.Name -Login $login -User $login -Force
Add-DbaDbRoleMember -SqlInstance $instance -Database $_.Name -Role "db_owner" -User $login
}
db_owner role.T-SQL Alternative
You can also use T-SQL:
USE master;
GO
DECLARE @DatabaseName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @User NVARCHAR(128)
SET @User = 'YourUser'
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4 -- Exclude system DBs
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE [' + @DatabaseName + ']; ' +
'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + @User + ''') ' +
'CREATE USER [' + @User + '] FOR LOGIN [' + @User + ']; ' +
'EXEC sp_addrolemember N''db_owner'', [' + @User + '];'
EXEC sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @DatabaseName
END
CLOSE db_cursor
DEALLOCATE db_cursor
Loading millions of records into a cloud database can be a frustratingly slow task—unless you identify where your bottlenecks are. In this post, I will share how we significantly improved our insertion speeds on Azure Database for PostgreSQL Flexible Server by adjusting a single, often-overlooked setting: provisioned IOPS.
We were running a large data migration from Databricks to Azure Database for PostgreSQL Flexible Server. Our setup:
Despite this robust configuration, our insert speeds were disappointing. Monitoring showed:
Clearly, our CPU wasn’t the problem—disk I/O was.
Azure Database for PostgreSQL Flexible Server ties write performance directly to your provisioned IOPS (Input/Output Operations Per Second). PostgreSQL is forced to queue up write operations when your workload hits this limit, causing inserts to slow down dramatically.
Key signs you’re IOPS-bound:
We increased our provisioned IOPS from 1,100 to 5,000 using the Azure Portal:
Result:
Insert speeds improved immediately and dramatically. Disk performance no longer throttled the database, and we could fully utilize our CPU and memory resources.
Conclusion:
If your PostgreSQL inserts are slow on Azure, check your disk IOPS. Increasing provisioned IOPS can unlock the performance your hardware is capable of—sometimes, it’s the simplest tweak that makes the biggest difference.
/*
================================================================================
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();
================================================================================
*/
Database Blog 