A Rejected PostgreSQL Import Row Needs a Recovery Path

Article · Interrupted execution

The import reports that twelve rows failed. The job says complete. The only detail was printed in a log that has already rotated.

Which rows need repair? Did the other rows commit? Will replaying the file duplicate what already succeeded? Those questions belong in the import design, before the first large file arrives.

This reproduction uses PostgreSQL 18 and SQLx 0.8.6. It accepts independent rows, keeps durable attempt outcomes, and repairs one rejected row under a new correction version. It also exposes a common database trap: catching a statement error does not restore an aborted transaction.

State the batch contract first

The lab has a business table keyed by source identity and an attempt ledger keyed by source identity plus correction version. Each call processes one row in its own outer transaction. A job can therefore make partial progress by design.

The accepted input contains quantity ten. Another quantity is negative and violates a check constraint. A third input is a synthetic non-integer value. The parser represents that last result as no valid integer and records a parse rejection.

The records use short fixture identities and integer quantities. The schema bounds source identities to 64 characters. The ledger keeps a stable reason category rather than copying raw database messages or whole private documents into logs.

If your product needs an all-or-nothing file, this row-by-row contract is different. Do not reuse it while promising atomic batch completion. The SQL Server bulk-loading guide explains why sending data, finishing a bulk stream, and committing a transaction are separate events. The new lab is a PostgreSQL example, not a tested SQL Server savepoint recipe.

Recover the transaction before storing the rejection

The importer first claims the attempt identity. It creates a savepoint before attempting the business insert. PostgreSQL rejects the negative quantity with code 23514.

The fixture deliberately issues another statement before rolling back to the savepoint. That statement must fail with 25P02, showing that the transaction is still in its failed state. Only then does the importer restore the savepoint and write the rejection outcome.

sqlx::query("SAVEPOINT one_row").execute(&mut *tx).await?;
        let inserted = sqlx::query("INSERT INTO imported_rows VALUES ($1, $2)")
            .bind(source)
            .bind(value)
            .execute(&mut *tx)
            .await;
        match inserted {
            Ok(_) => "accepted",
            Err(error)
                if error.as_database_error().and_then(|e| e.code()).as_deref() == Some("23514") =>
            {
                let failed = sqlx::query("SELECT 1").execute(&mut *tx).await.unwrap_err();
                assert_eq!(
                    failed.as_database_error().unwrap().code().as_deref(),
                    Some("25P02")
                );
                sqlx::query("ROLLBACK TO SAVEPOINT one_row")
                    .execute(&mut *tx)
                    .await?;
                "constraint_rejected"
            }
            Err(error) => return Err(error),
        }

The full import recovery fixture includes the outer transaction and ledger update. The savepoint is a database boundary, not a Rust exception handler. PostgreSQL's SAVEPOINT documentation describes how rollback restores the state established at that boundary.

This example handles its known check violation. Unrelated database errors propagate instead of becoming ordinary row rejections. A lost connection, unavailable database, or programming mistake should not silently turn a failed job into a pile of bad-data records.

Keep original and corrected attempts

The first negative attempt uses correction version zero. The corrected quantity twelve uses version one. The original rejection remains in the ledger. A later reader can tell what failed and which correction succeeded.

The fixture closes the pool after the first group and reconnects before replay. It retries the already accepted row, repairs the negative row, then repeats the same correction. The business table must contain only the accepted original row and the repaired row. The ledger must contain four attempt records, including both versions of the repaired input.

Reusing an attempt identity with a different quantity returns input_conflict. It must not overwrite the earlier attempt or alter the business row. The test checks this by sending thirteen under the correction identity that already stored twelve.

The small lab uses one implicit import identity through its isolated schema. A real ledger key should also include the import or source scope, trusted tenant, and immutable input identity. Record parser or mapping versions when they affect interpretation. Do not treat an untrusted filename as an authorization boundary.

Make repair an operation people can follow

Give an operator a way to list waiting rejections, inspect safe context, supply a correction, and see the resulting attempt. If intentionally discarding a row is allowed, record who made that decision and why. Discarded and corrected are different outcomes.

Retain private source data only in a protected store with appropriate access and retention. A row number can help someone find input, but it is fragile when a file changes order. Prefer stable source identities and explicit versions.

The lab checks a reconnect and repeated correction. It does not kill a process during commit, run competing repair workers, or prove a complete production credential policy. It also does not permit a new correction to edit a row that already succeeded. That requires a separate update contract rather than another insert hidden inside replay.

Check the final business rows and attempt ledger independently of the summary message. Counts alone can hide the wrong identities or values. An import is useful when every accepted input has a known outcome and every rejected input has a deliberate next step. A green job badge cannot supply that history after the fact.