nautechsystems/nautilus_trader · error

Failed to insert into position table: {e}

Error message

Failed to insert into position table: {e}

What it means

Raised by `add_position_snapshot` on the second step: the INSERT into the `"position"` table fails. The trader-table insert succeeded earlier in the transaction, but the pending transaction is rolled back, leaving the database unchanged. The sqlx error is wrapped in `anyhow` with this message.

Source

Thrown at crates/infrastructure/src/sql/queries.rs:547

            .bind(snapshot.quote_currency.to_string())
            .bind(snapshot.base_currency.map(|x| x.to_string()))
            .bind(snapshot.settlement_currency.to_string())
            .bind(snapshot.avg_px_open)
            .bind(snapshot.avg_px_close)
            .bind(snapshot.realized_return)
            .bind(snapshot.realized_pnl.map(|x| x.to_string()))
            .bind(snapshot.unrealized_pnl.map(|x| x.to_string()))
            .bind(snapshot.commissions.iter().map(ToString::to_string).collect::<Vec<String>>())
            .bind(snapshot.duration_ns.map(|x| x.to_string()))
            .bind(snapshot.ts_opened.to_string())
            .bind(snapshot.ts_closed.map(|x| x.to_string()))
            .bind(snapshot.ts_init.to_string())
            .bind(snapshot.ts_last.to_string())
            .bind(snapshot.replay_state)
            .execute(&mut *transaction)
            .await
            .map(|_| ())
            .map_err(|e| anyhow::anyhow!("Failed to insert into position table: {e}"))?;
        transaction
            .commit()
            .await
            .map_err(|e| anyhow::anyhow!("Failed to commit transaction: {e}"))
    }

    /// Loads a `PositionSnapshot` entry by `position_id` via the provided `pool`.
    ///
    /// # Errors
    ///
    /// Returns an error if the SQL SELECT or deserialization fails.
    pub async fn load_position_snapshot(
        pool: &PgPool,
        position_id: &PositionId,
    ) -> anyhow::Result<Option<PositionSnapshot>> {
        sqlx::query_as::<_, PositionSnapshotRow>(r#"SELECT * FROM "position" WHERE id = $1"#)
            .bind(position_id.to_string())
            .fetch_optional(pool)

View on GitHub (pinned to 18893faf8b)

Solutions

1. Read the wrapped `{e}` for the specific constraint or type mismatch.
2. Run migrations so the `"position"` table matches the crate's expectations.
3. Skip or upsert when the position_id already exists (check a prior snapshot load if available).
4. Confirm numeric fields serialize into columns with sufficient precision/type.

Example fix

// before: duplicates crash ingestion batches
for snap in snapshots {
    add_position_snapshot(&pool, &snap).await?;
}

// after: tolerate re-ingestion of the same position
for snap in snapshots {
    if let Err(e) = add_position_snapshot(&pool, &snap).await {
        if !format!("{e:#}").contains("duplicate key") { return Err(e); }
    }
}

// Skip positions that were already persisted (duplicate position_id)
let dup = sqlx::query_scalar::<_, i64>(
    "SELECT COUNT(*) FROM \"position\" WHERE id = $1")
    .bind(snapshot.id.to_string())
    .fetch_one(pool).await? > 0;
if dup { return Ok(()); }

match add_position_snapshot(&pool, &snapshot).await {
    Ok(()) => (),
    Err(e) if format!("{e:#}").contains("duplicate key") => {
        tracing::debug!("position {} already persisted", snapshot.id);
    }
    Err(e) => return Err(e),
}

When it happens

Trigger: Calling `add_position_snapshot` when: the `"position"` table is missing; constraints on position_id / opening_order_id are violated; a numeric or enum-typed column rejects the bound string value; or a unique constraint is hit by a duplicate position snapshot.

Common situations: Re-running replay ingestion and re-writing existing position IDs; unmigrated schema; values like `realized_return` or `signed_qty` not matching column precision; crate and schema version mismatch causing bind/count errors.

Related errors

• Failed to insert into trader table: {e}
• Failed to insert into order table: {e}
• Failed to delete position_event rows: {e}
• Failed to start replacement transaction persistence: {e}
• Failed to retire replaced execution hash: {e}

AI-assisted analysis of nautechsystems/nautilus_trader@18893faf8b (2026-09-08). Data as JSON: /api/errors/a12710461e593dad. Report an issue: GitHub.