---
title: "Session Management Quick Reference"
---

# Session Management Quick Reference

Quick reference guide for HeliosDB Nano session management and monitoring.

## Quick Commands

### View All Sessions

```sql
-- Generic (all protocols)
SELECT * FROM helios_sessions;

-- PostgreSQL style
SELECT * FROM pg_stat_activity;

-- Oracle style
SELECT * FROM v$session;
```

### View Active Queries

```sql
-- All active queries
SELECT session_id, username, current_query
FROM helios_sessions
WHERE state = 'active';

-- PostgreSQL style
SELECT pid, usename, query
FROM pg_stat_activity
WHERE state = 'active';

-- Oracle style
SELECT sid, username, sql_text
FROM v$session
WHERE status = 'ACTIVE';
```

### Count Sessions by Protocol

```sql
SELECT protocol, COUNT(*) AS session_count
FROM helios_sessions
GROUP BY protocol;
```

### Find Idle Sessions

```sql
-- Sessions idle for more than 5 minutes
SELECT session_id, username, last_activity
FROM helios_sessions
WHERE state = 'idle'
AND (CURRENT_TIMESTAMP - last_activity) > INTERVAL '5 minutes';
```

## Session States

| State | Description |
|-------|-------------|
| `active` | Currently executing a query |
| `idle` | Connected but not executing |
| `idle_in_transaction` | Idle within a transaction block |

## System Table Schema

```sql
CREATE TABLE helios_sessions (
    session_id INT8 PRIMARY KEY,
    protocol TEXT NOT NULL,
    username TEXT NOT NULL,
    client_address TEXT NOT NULL,
    client_port INT4 NOT NULL,
    connect_time TIMESTAMP NOT NULL,
    last_activity TIMESTAMP NOT NULL,
    current_query TEXT,
    state TEXT NOT NULL
);
```

## Rust API

### Register Session

```rust
let session_id = registry.register_session(
    ProtocolType::PostgreSQL,
    "username".to_string(),
    "127.0.0.1".to_string(),
    5432,
)?;
```

### Update Query

```rust
registry.update_session_query(session_id, "SELECT * FROM users".to_string())?;
```

### Clear Query

```rust
registry.clear_session_query(session_id)?;
```

### Unregister Session

```rust
registry.unregister_session(session_id)?;
```

### Get All Sessions

```rust
let sessions = registry.get_all_sessions()?;
for session in sessions {
    println!("{:?}", session);
}
```

## Python Client Examples

### PostgreSQL

```python
import psycopg2

conn = psycopg2.connect(
    host='localhost',
    port=5432,
    database='heliosdb',
    user='myuser',
    password='mypass'
)

cursor = conn.cursor()

# View sessions
cursor.execute("SELECT * FROM pg_stat_activity")
sessions = cursor.fetchall()

# View my session
cursor.execute("SELECT pg_backend_pid()")
my_pid = cursor.fetchone()[0]
```

### Oracle

```python
import oracledb

conn = oracledb.connect(
    user='myuser',
    password='mypass',
    dsn='localhost:1521/heliosdb'
)

cursor = conn.cursor()

# View sessions
cursor.execute("SELECT * FROM v$session WHERE username IS NOT NULL")
sessions = cursor.fetchall()
```

## Monitoring Queries

### Session Activity Report

```sql
SELECT
    protocol,
    COUNT(*) AS total_sessions,
    SUM(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active,
    SUM(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle
FROM helios_sessions
GROUP BY protocol;
```

### Top Users by Session Count

```sql
SELECT
    username,
    COUNT(*) AS session_count,
    MAX(last_activity) AS last_seen
FROM helios_sessions
GROUP BY username
ORDER BY session_count DESC
LIMIT 10;
```

### Long-Running Queries

```sql
SELECT
    session_id,
    username,
    current_query,
    (CURRENT_TIMESTAMP - last_activity) AS duration
FROM helios_sessions
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 10;
```

## Troubleshooting

### Problem: No sessions showing

**Check**: Is the server running?
```bash
nc -z localhost 5432  # PostgreSQL
nc -z localhost 1521  # Oracle
```

**Check**: Are connections authenticated?
```sql
SELECT COUNT(*) FROM helios_sessions;
```

### Problem: Sessions stuck in 'active'

**Solution**: Clear stale sessions
```sql
-- Identify stale sessions (active > 1 hour)
SELECT session_id, username, current_query
FROM helios_sessions
WHERE state = 'active'
AND (CURRENT_TIMESTAMP - last_activity) > INTERVAL '1 hour';
```

### Problem: Too many idle sessions

**Solution**: Configure connection timeout
```toml
[session]
idle_timeout_seconds = 300  # 5 minutes
```

## Common Patterns

### Session Lifecycle

```rust
// 1. Register on connect
let session_id = registry.register_session(...)?;

// 2. Track queries
loop {
    let query = receive_query();
    registry.update_session_query(session_id, query.clone())?;
    execute_query(query)?;
    registry.clear_session_query(session_id)?;
}

// 3. Unregister on disconnect
registry.unregister_session(session_id)?;
```

### Query Tracking Pattern

```rust
pub async fn execute_with_tracking(
    &mut self,
    query: &str,
    session_id: i64,
    registry: &SessionRegistry,
) -> Result<ExecutionResult> {
    registry.update_session_query(session_id, query.to_string())?;
    let result = self.execute(query).await;
    registry.clear_session_query(session_id)?;
    result
}
```

## View Mappings

### PostgreSQL → HeliosDB

| PostgreSQL | HeliosDB |
|------------|----------|
| `pg_stat_activity.pid` | `helios_sessions.session_id` |
| `pg_stat_activity.usename` | `helios_sessions.username` |
| `pg_stat_activity.state` | `helios_sessions.state` |
| `pg_stat_activity.query` | `helios_sessions.current_query` |

### Oracle → HeliosDB

| Oracle | HeliosDB |
|--------|----------|
| `v$session.sid` | `helios_sessions.session_id` |
| `v$session.username` | `helios_sessions.username` |
| `v$session.status` | `helios_sessions.state` (mapped) |
| `v$session.sql_text` | `helios_sessions.current_query` |

## Configuration

```toml
[session]
# Maximum sessions per user
max_sessions_per_user = 10

# Maximum total sessions
max_total_sessions = 1000

# Idle timeout in seconds
idle_timeout_seconds = 3600

# Enable query history tracking
track_query_history = true

[monitoring]
# Enable session tracking
session_tracking = true

# Log session events
log_session_events = true

# Log query execution
log_query_execution = true
```

## Performance Tips

1. **Use indexes**: System tables should have indexes on frequently queried columns
2. **Limit results**: Always use LIMIT when querying session tables
3. **Batch updates**: Update session state in batches when possible
4. **Monitor locks**: Watch for lock contention on session registry
5. **Clean idle sessions**: Regularly clean up idle sessions

## Testing

Run protocol tests with session tracking:

```bash
cd tests/protocol_tests
./run_tests.sh
```

Test individual protocols:

```bash
python3 test_postgres.py  # PostgreSQL
python3 test_oracle.py    # Oracle
```