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 deadlocks
113pub async fn show_deadlocks(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
114    let rows = client
115        .query(
116            "SELECT pid, usename, application_name, state, query_start, query
117             FROM pg_stat_activity
118             WHERE state = 'disabled' OR wait_event = 'ProcArrayLock'
119             ORDER BY query_start ASC",
120            &[],
121        )
122        .await?;
123
124    let deadlocks: Vec<Value> = rows
125        .iter()
126        .map(|row| {
127            json!({
128                "pid": row.get::<_, i32>(0),
129                "user": row.get::<_, String>(1),
130                "application": row.get::<_, Option<String>>(2),
131                "state": row.get::<_, String>(3),
132                "query_start": row.get::<_, String>(4),
133                "query": row.get::<_, Option<String>>(5),
134            })
135        })
136        .collect();
137
138    Ok(json!({ "potential_deadlocks": deadlocks }))
139}
140
141/// 49. Show auto commit status
142///
143/// Note: PostgreSQL 17+ removed the `autocommit` GUC.
144/// Autocommit is always-on in the wire protocol and cannot be disabled.
145/// For PG < 17, we query `SHOW autocommit`; for PG >= 17, we return `true`.
146pub async fn show_autocommit_status(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
147    let autocommit = match client.query("SHOW autocommit", &[]).await {
148        Ok(rows) => rows[0].get::<_, String>(0) == "on",
149        Err(_) => true, // PG 17+ removed the setting; always-on
150    };
151
152    Ok(json!({
153        "autocommit": autocommit,
154        "value": if autocommit { "on" } else { "off" }
155    }))
156}
157
158/// 50. Show transaction timeout
159pub async fn show_transaction_timeout(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
160    let rows = client
161        .query("SHOW statement_timeout", &[])
162        .await?;
163
164    let timeout = rows[0].get::<_, String>(0);
165
166    Ok(json!({
167        "statement_timeout": timeout
168    }))
169}
mcp_postgres/actions/transactions.rs

mcp_postgres/actions/
transactions.rs
