mcp_postgres/actions/
transactions.rs

1use serde_json::{json, Value};
2use tokio_postgres::Client;
3use crate::errors::Result as MCPResult;
4
5/// 41. Show active transactions
6pub async fn show_active_transactions(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
7    let rows = client
8        .query(
9            "SELECT pid, usename, application_name, state, xact_start, query_start, query
10             FROM pg_stat_activity
11             WHERE xact_start IS NOT NULL AND pid != pg_backend_pid()
12             ORDER BY xact_start ASC",
13            &[],
14        )
15        .await?;
16
17    let transactions: Vec<Value> = rows
18        .iter()
19        .map(|row| {
20            json!({
21                "pid": row.get::<_, i32>(0),
22                "user": row.get::<_, String>(1),
23                "application": row.get::<_, Option<String>>(2),
24                "state": row.get::<_, String>(3),
25                "xact_start": row.get::<_, String>(4),
26                "query_start": row.get::<_, String>(5),
27                "query": row.get::<_, Option<String>>(6),
28            })
29        })
30        .collect();
31
32    Ok(json!({ "transactions": transactions }))
33}
34
35/// 42. Show locks
36pub async fn show_locks(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
37    let rows = client
38        .query(
39            "SELECT l.pid, a.usename, a.application_name, l.mode, l.granted, l.fastpath,
40                    a.query_start, a.query
41             FROM pg_locks l
42             JOIN pg_stat_activity a ON l.pid = a.pid
43             WHERE l.pid != pg_backend_pid()
44             ORDER BY l.pid, l.mode",
45            &[],
46        )
47        .await?;
48
49    let locks: Vec<Value> = rows
50        .iter()
51        .map(|row| {
52            json!({
53                "pid": row.get::<_, i32>(0),
54                "user": row.get::<_, String>(1),
55                "application": row.get::<_, Option<String>>(2),
56                "lock_type": row.get::<_, String>(3),
57                "granted": row.get::<_, bool>(4),
58                "fastpath": row.get::<_, bool>(5),
59                "query_start": row.get::<_, Option<String>>(6),
60                "query": row.get::<_, Option<String>>(7),
61            })
62        })
63        .collect();
64
65    Ok(json!({ "locks": locks }))
66}
67
68/// 43. Show waiting locks
69pub async fn show_waiting_locks(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
70    let rows = client
71        .query(
72            "SELECT l.pid, a.usename, l.mode, a.query_start, a.query
73             FROM pg_locks l
74             JOIN pg_stat_activity a ON l.pid = a.pid
75             WHERE NOT l.granted AND l.pid != pg_backend_pid()
76             ORDER BY a.query_start ASC",
77            &[],
78        )
79        .await?;
80
81    let waiting: Vec<Value> = rows
82        .iter()
83        .map(|row| {
84            json!({
85                "pid": row.get::<_, i32>(0),
86                "user": row.get::<_, String>(1),
87                "lock_type": row.get::<_, String>(2),
88                "query_start": row.get::<_, String>(3),
89                "query": row.get::<_, Option<String>>(4),
90            })
91        })
92        .collect();
93
94    Ok(json!({ "waiting_locks": waiting }))
95}
96
97/// 44. Begin transaction
98/// 45. Show transaction isolation levels
99pub async fn show_transaction_isolation(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
100    let rows = client
101        .query("SHOW transaction_isolation", &[])
102        .await?;
103
104    let level = rows[0].get::<_, String>(0);
105
106    Ok(json!({
107        "isolation_level": level,
108        "available_levels": ["serializable", "repeatable read", "read committed", "read uncommitted"]
109    }))
110}
111
112/// 48. Show blocked processes (potential deadlock situations)
113///
114/// PostgreSQL's deadlock detector runs every `deadlock_timeout` (default 1s)
115/// and automatically cancels one transaction when a deadlock cycle is detected.
116/// By the time a deadlock is logged, it has already been resolved.
117/// This view shows processes that are currently blocked by other processes,
118/// which represents potential deadlock or lock contention situations.
119pub async fn show_deadlocks(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
120    // Show processes blocked by others, with the blocking PID identified
121    let rows = client
122        .query(
123            "SELECT a.pid, a.usename::text, a.application_name, a.state,
124                    a.query_start::text, a.query,
125                    pg_blocking_pids(a.pid) AS blocked_by,
126                    (SELECT count(*) FROM pg_stat_activity
127                     WHERE pid = ANY(pg_blocking_pids(a.pid))) AS blockers_count
128             FROM pg_stat_activity a
129             WHERE cardinality(pg_blocking_pids(a.pid)) > 0
130               AND a.pid != pg_backend_pid()
131             ORDER BY a.query_start ASC",
132            &[],
133        )
134        .await?;
135
136    let blocked: Vec<Value> = rows
137        .iter()
138        .map(|row| {
139            json!({
140                "pid": row.get::<_, i32>(0),
141                "user": row.get::<_, String>(1),
142                "application": row.get::<_, Option<String>>(2),
143                "state": row.get::<_, String>(3),
144                "query_start": row.get::<_, String>(4),
145                "query": row.get::<_, Option<String>>(5),
146                "blocked_by": row.get::<_, Vec<i32>>(6),
147                "blocker_count": row.get::<_, i64>(7),
148                "advisory": "Deadlocks are auto-detected and resolved within deadlock_timeout (default 1s). These are currently blocked processes — potential deadlock situations."
149            })
150        })
151        .collect();
152
153    Ok(json!({ "blocked_processes": blocked, "count": blocked.len() }))
154}
155
156/// 49. Show auto commit status
157///
158/// PostgreSQL's `autocommit` GUC was removed in version 7.4 (2003).
159/// Autocommit is always-on in the PostgreSQL wire protocol and cannot be
160/// disabled server-side. Client libraries (psycopg2, JDBC, etc.) implement
161/// auto-commit-off at the client level by wrapping statements in BEGIN/COMMIT.
162/// This tool reports `always_on` because the server always uses autocommit.
163pub async fn show_autocommit_status(_client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
164    Ok(json!({
165        "autocommit": true,
166        "status": "always_on",
167        "detail": "PostgreSQL always operates in autocommit mode at the wire protocol level. Client-side autocommit control is implemented by your database driver, not the server.",
168        "note": "The autocommit GUC was removed in PostgreSQL 7.4 (2003) and is not available in any supported version."
169    }))
170}
171
172/// 50. Show transaction timeout
173pub async fn show_transaction_timeout(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
174    let rows = client
175        .query("SHOW statement_timeout", &[])
176        .await?;
177
178    let timeout = rows[0].get::<_, String>(0);
179
180    Ok(json!({
181        "statement_timeout": timeout
182    }))
183}
mcp_postgres/actions/transactions.rs

mcp_postgres/actions/
transactions.rs
