# Research: SQL Sequences in Oxibase

## Technical Context & Findings

### 1. Standard SQL Sequence Syntax
The SQL standard dictates that sequences are standalone schema objects.

**Decision**: The syntax parsed must support:
```sql
CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
    [ START [ WITH ] start_value ]
    [ INCREMENT [ BY ] increment_value ]
    [ MINVALUE min_value | NO MINVALUE ]
    [ MAXVALUE max_value | NO MAXVALUE ]
    [ CYCLE | NO CYCLE ]

ALTER SEQUENCE [ IF EXISTS ] sequence_name [ ...options ]

DROP SEQUENCE [ IF EXISTS ] sequence_name
```
**Rationale**: This aligns with ANSI SQL and PostgreSQL semantics, ensuring compatibility and expected behavior for users.

### 2. Sequence Functions Behavior
Sequence values are manipulated via specific functions that interact directly with the sequence state.

**Decision**:
- `NEXTVAL('seq_name')`: Atomically increments the sequence and returns the new value. The operation is independent of the calling transaction (cannot be rolled back).
- `CURRVAL('seq_name')`: Returns the last value generated by `NEXTVAL` within the *current session*.
- `SETVAL('seq_name', value, [is_called])`: Updates the current state of the sequence globally.

**Rationale**: 
`NEXTVAL` bypassing the standard MVCC write rules prevents write-write conflicts across concurrent transactions trying to insert primary keys. `CURRVAL` must be session-local to avoid race conditions between different connections.

### 3. Catalog and Concurrency (The MVCC Bypass)
Because Oxibase utilizes strict MVCC, standard updates would cause write conflicts if multiple transactions updated the sequence's catalog entry concurrently.

**Decision**:
Sequences will be tracked in an in-memory `DashMap<String, AtomicI64>` (or similar concurrent structure) within the catalog. `NEXTVAL` operations will execute atomic hardware operations (`fetch_add`) to get the ID without blocking.
The session context (`SessionState` or similar execution context) will maintain a local map of sequence names to the latest `currval` generated by that session.

**Alternatives Considered**:
- Creating a separate transaction for every `NEXTVAL` call: Rejected due to significant disk I/O and transaction log bloat overhead.
- Normal MVCC row update: Rejected because two concurrent inserts into a table using a sequence would always result in a serialization/conflict abort for one of them.

### 4. Integration with Information Schema
**Decision**: 
The `information_schema.sequences` table generator will query the catalog's in-memory sequence dictionary to populate rows on the fly, rendering the current active configuration of each sequence.
