What causes "Failed to delete position_event rows: {e}" in nautechsystems/nautilus_trader?

Inspect the wrapped sqlx error to identify the DELETE failure cause.

Failed to delete position_event rows

Error message

Failed to delete position_event rows: {e}

What it means

add_position opens a transaction that deletes existing position_event rows for the position_id before inserting the new snapshot event; when the DELETE fails the sqlx error is wrapped in this message. The transaction is then dropped (rolled back), leaving the position log unchanged.

Solutions

Inspect the wrapped sqlx error to identify the DELETE failure cause.
Run the nautilus migrations so the position_event table exists.
Ensure writers target the primary database and that concurrent add_position calls for the same position_id are serialized.
Check permissions for the DB user (DELETE on position_event).

Defensive patterns

Strategy: retry

Validate before calling

sqlx::query("SELECT 1 FROM position_event LIMIT 1").fetch_optional(&pool).await?;

Try / catch

match add_position(&pool, &pid, &event).await {
    Err(e) if is_transient_db_error(&e) => retry_with_backoff(3),
    other => other,
}

Prevention

Serialize add_position calls for the same position_id to avoid lock contention.
Always write to the primary, never a read replica.
Ensure the position_event table exists via migrations.

When it happens

Trigger: Calling add_position(pool, position_id, event) when the DELETE FROM position_event statement errors: table missing, connection lost, lock timeout, or insufficient privileges.

Common situations: Schema not migrated; concurrent writers holding row locks on the same position_id; database failover mid-transaction; read-only replica used by mistake.

Related errors

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

Appendix: source

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

    ) -> anyhow::Result<()> {
        let event_position_id = Self::event_position_id(event)?;
        if event_position_id != position_id {
            anyhow::bail!(
                "Cannot persist position event {} for mismatched position_id: expected {}, was {}",
                event.event_id,
                position_id,
                event_position_id
            );
        }

        let mut transaction = pool.begin().await?;

        sqlx::query(r#"DELETE FROM "position_event" WHERE position_id = $1"#)
            .bind(position_id.to_string())
            .execute(&mut *transaction)
            .await
            .map(|_| ())
            .map_err(|e| anyhow::anyhow!("Failed to delete position_event rows: {e}"))?;

        Self::insert_position_event(&mut transaction, event).await?;
        transaction
            .commit()
            .await
            .map_err(|e| anyhow::anyhow!("Failed to commit transaction: {e}"))
    }

    /// Appends a fill event for a `Position` via the provided `pool`.
    ///
    /// # Errors
    ///
    /// Returns an error if the fill is invalid or if the SQL operations fail.
    pub async fn update_position(pool: &PgPool, event: &OrderFilled) -> anyhow::Result<()> {
        let mut transaction = pool.begin().await?;

        Self::insert_position_event(&mut transaction, event).await?;
        transaction

View on GitHub (pinned to 18893faf8b)