Skip to content
HeliosDB HeliosDB Nano

AS OF Quick Reference Guide

AS OF Quick Reference Guide

Usage Examples

Basic Time-Travel Queries

use heliosdb_nano::sql::{LogicalPlan, AsOfClause};
use std::sync::Arc;


// AS OF TIMESTAMP
let plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Timestamp("2025-11-19 12:00:00".to_string())),
};


// AS OF TRANSACTION
let plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Transaction(12345)),
};


// AS OF SCN
let plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Scn(67890)),
};


// AS OF NOW
let plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Now),
};


// Execute
let mut executor = Executor::with_storage(&engine);
let results = executor.execute(&plan)?;

SQL Syntax (Future Parser Integration)

-- Timestamp-based
SELECT * FROM orders AS OF TIMESTAMP '2025-11-19 12:00:00';


-- Transaction-based
SELECT * FROM orders AS OF TRANSACTION 12345;


-- SCN-based
SELECT * FROM orders AS OF SCN 67890;


-- Current time
SELECT * FROM orders AS OF NOW;

AsOfClause Definition

pub enum AsOfClause {
    /// AS OF NOW - current time
    Now,
    /// AS OF TIMESTAMP '2025-11-15 06:00:00'
    Timestamp(String),
    /// AS OF TRANSACTION 987654
    Transaction(u64),
    /// AS OF SCN 123456789 (System Change Number)
    Scn(u64),
}

Resolution Flow

AsOfClause
    ↓
SnapshotManager::resolve_as_of()
    ↓
    ├─ Timestamp → resolve_timestamp() → u64 (snapshot_ts)
    ├─ Transaction → resolve_transaction() → u64 (snapshot_ts)
    └─ Scn → resolve_scn() → u64 (snapshot_ts)
    ↓
StorageEngine::scan_table_at_snapshot(table_name, snapshot_ts)
    ↓
Vec<Tuple> (historical data)

Error Handling

// Invalid timestamp format
AsOfClause::Timestamp("invalid")
// → Error: "Invalid timestamp format: ..."


// Non-existent transaction
AsOfClause::Transaction(99999)
// → Error: "Transaction 99999 not found or has been garbage collected"


// Non-existent SCN
AsOfClause::Scn(99999)
// → Error: "SCN 99999 not found or has been garbage collected"


// No snapshot at timestamp
AsOfClause::Timestamp("1970-01-01 00:00:00")
// → Error: "No snapshot found for timestamp '1970-01-01 00:00:00'"

Timestamp Formats Supported

// PostgreSQL format
"2025-11-19 12:00:00"


// ISO 8601 format
"2025-11-19T12:00:00"

Complex Query Examples

With Filter

let scan_plan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(schema),
    projection: None,
    as_of: Some(AsOfClause::Transaction(100)),
};


let filter_plan = LogicalPlan::Filter {
    input: Box::new(scan_plan),
    predicate: LogicalExpr::BinaryExpr {
        left: Box::new(LogicalExpr::Column { name: "customer".to_string() }),
        op: BinaryOperator::Eq,
        right: Box::new(LogicalExpr::Literal(Value::String("Alice".to_string()))),
    },
};

With Join

let orders_scan = LogicalPlan::Scan {
    table_name: "orders".to_string(),
    schema: Arc::new(orders_schema),
    projection: None,
    as_of: Some(AsOfClause::Scn(5000)),
};


let customers_scan = LogicalPlan::Scan {
    table_name: "customers".to_string(),
    schema: Arc::new(customers_schema),
    projection: None,
    as_of: Some(AsOfClause::Scn(5000)), // Same snapshot for consistency
};


let join_plan = LogicalPlan::Join {
    left: Box::new(orders_scan),
    right: Box::new(customers_scan),
    join_type: JoinType::Inner,
    on: Some(join_condition),
};

Getting Snapshot Information

// Get snapshot manager
let snapshot_mgr = engine.snapshot_manager();


// Get snapshot by timestamp
let metadata = snapshot_mgr.get_snapshot_metadata(timestamp_value)?;
println!("Transaction ID: {}", metadata.transaction_id);
println!("SCN: {}", metadata.scn);
println!("Wall clock time: {}", metadata.wall_clock_time);


// Get current SCN
let current_scn = snapshot_mgr.current_scn();


// Get current transaction ID
let current_txn = snapshot_mgr.current_transaction_id();


// Get snapshot count
let count = snapshot_mgr.snapshot_count();

Creating Versioned Data

// Insert with automatic versioning
let row_id = engine.insert_tuple_versioned("orders", tuple)?;
// This automatically:
// 1. Creates a new snapshot
// 2. Assigns transaction ID and SCN
// 3. Stores versioned copy for time-travel

Performance Tips

  1. Use SCN or Transaction ID when possible - Faster than timestamp parsing
  2. Coordinate AS OF across tables in JOINs - Ensures consistent snapshot view
  3. Normal queries have zero overhead - Don’t use AS OF if not needed
  4. Monitor snapshot count - Large retention can increase memory usage
  5. Use AS OF NOW for explicit current-time semantics

Debugging

Enable tracing to see AS OF resolution:

tracing::subscriber::set_global_default(
    tracing_subscriber::fmt()
        .with_max_level(tracing::Level::DEBUG)
        .finish()
).unwrap();

Output:

DEBUG Time-travel query on table 'orders' with AS OF clause: Transaction(12345)
DEBUG Resolved AS OF clause to snapshot timestamp 100 for table 'orders'
DEBUG Time-travel scan returned 42 tuples from table 'orders' at snapshot 100

Testing

Terminal window
# Run all AS OF tests
cargo test test_executor_as_of -- --nocapture


# Run specific AS OF test
cargo test test_executor_as_of_timestamp -- --nocapture


# Run integration tests
cargo test --test time_travel_integration_tests -- --nocapture

Common Pitfalls

  1. Different snapshots in JOIN - Use same AS OF for all tables
  2. Garbage collected snapshots - Old snapshots may be removed
  3. Timestamp precision - Only second-level granularity
  4. Memory usage - Large retention policies increase memory

API Reference

Executor Methods

impl Executor {
    // Execute plan with AS OF support
    pub fn execute(&mut self, plan: &LogicalPlan) -> Result<Vec<Tuple>>;
}

SnapshotManager Methods

impl SnapshotManager {
    // Resolve AS OF clause to snapshot timestamp
    pub fn resolve_as_of(&self, as_of: &AsOfClause) -> Result<u64>;


    // Get snapshot metadata
    pub fn get_snapshot_metadata(&self, timestamp: u64) -> Option<SnapshotMetadata>;


    // Get current SCN
    pub fn current_scn(&self) -> Scn;


    // Get current transaction ID
    pub fn current_transaction_id(&self) -> TransactionId;


    // Get snapshot count
    pub fn snapshot_count(&self) -> usize;
}

StorageEngine Methods

impl StorageEngine {
    // Get snapshot manager
    pub fn snapshot_manager(&self) -> &SnapshotManager;


    // Scan table at historical snapshot
    pub fn scan_table_at_snapshot(&self, table_name: &str, snapshot_ts: u64) -> Result<Vec<Tuple>>;


    // Insert versioned tuple
    pub fn insert_tuple_versioned(&self, table_name: &str, tuple: Tuple) -> Result<u64>;
}

Quick Links: