mcp_postgres/actions/
transactions.rs

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

mcp_postgres/actions/
transactions.rs
