Session Management Quick Reference

Session Management Quick Reference

Quick reference guide for HeliosDB Nano session management and monitoring.

Quick Commands

View All Sessions

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

-- PostgreSQL style
SELECT * FROM pg_stat_activity;

-- Oracle style
SELECT * FROM v$session;

View Active Queries

-- 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

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

Find Idle Sessions

-- 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

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

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

Update Query

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

Clear Query

registry.clear_session_query(session_id)?;

Unregister Session

registry.unregister_session(session_id)?;

Get All Sessions

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

Python Client Examples

PostgreSQL

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

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

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

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

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?

nc -z localhost 5432  # PostgreSQL
nc -z localhost 1521  # Oracle

Check: Are connections authenticated?

SELECT COUNT(*) FROM helios_sessions;

Problem: Sessions stuck in ‘active’

Solution: Clear stale sessions

-- 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

[session]
idle_timeout_seconds = 300  # 5 minutes

Common Patterns

Session Lifecycle

// 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

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

[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:

cd tests/protocol_tests
./run_tests.sh

Test individual protocols:

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

Related Documentation

• Session Management Implementation
• Protocol Integration Guide
• PostgreSQL Compatibility
• Oracle Compatibility