Session Management Quick Reference¶

Quick reference guide for HeliosDB-Lite 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¶

Use indexes: System tables should have indexes on frequently queried columns
Limit results: Always use LIMIT when querying session tables
Batch updates: Update session state in batches when possible
Monitor locks: Watch for lock contention on session registry
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