Flow
Options for the save, plus a check and a test
1Check first
Catch missing IDs and bad numbers before the transaction ever opens.
2All or nothing
One transaction, no per-item catch, report the first real error and its item.
3Transaction per item
Real partial saves and short locks, but a crash can leave a session stuck in progress.
4Savepoint per item
Real partial saves with one commit, at the cost of extra round trips.
5Two-error test
Fail the third of ten items with a database error, then with a code error, and compare.
The save step in the ordering system keeps a list of warnings about failed items, and for the failures that matter most, it never gets to show them to me.
It came in when I broke up the big purchase-order page into a wizard with five steps: upload, check, match, review and save. Each step got its own small service. The problem lives in the last one.
The save runs inside one database transaction. It creates a session marked in progress, then goes through every store allocation. For each one it saves the product, the store's stock row and, if the store gets any units, an order line. Each item is wrapped in a try/catch, so a bad item adds a warning and the loop keeps going. At the end it marks the session complete or partial and commits.
Postgres doesn't care that I caught it
In Postgres, once one statement in a transaction fails, the transaction is dead. Every statement after that is refused until you roll back. Catching the error in your code doesn't bring it back to life.
BEGIN;
INSERT INTO bins (label) VALUES ('north'); -- fine
INSERT INTO bins (label) VALUES ('north'); -- error
INSERT INTO bins (label) VALUES ('south'); -- refused
So with one bad item, the first warning has the real reason. Every item after it fails with a warning that just says the transaction is dead. Then the last status update fails too, nothing catches it, and the whole save rolls back. I'd get a vague error about the aborted transaction, and the warnings would be thrown away with it. The partial status never gets written. The catch only helps when my own code fails before it talks to the database. At least the rollback is complete, so no half-saved plan is left behind.
I got all this from reading the code and the Postgres docs. I didn't see it fail, and the commit didn't come with a test for this step.
Three more problems in the same file
Order lines don't store which session made them. To reload a session, the code grabs lines created close to the session's time. Two saves made close together could mix their lines.
The stock write copies the same item-level quantity into every store's row, so each row has the same number and none is that store's own count.
The priority split rounds each store down and drops the leftover. Seven units across three stores becomes six.
What a save that knows its meaning looks like
Pick one meaning. Either the save is all or nothing, so you drop the per-item catch and report the first real error. Or it saves item by item, using a savepoint or a separate transaction for each one, so a failure only undoes that item. Run the checks that don't need the database first. Base the status on what happened to each item. I still have to choose between them.