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: {e}

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.

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)

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.