[aphyr]

Yes, you can recover from lost acknowledgements by asking for the transaction ID from postgres before committing--or by making up your own flake ID and writing it to a table. Given a queue with at-least-once delivery (which includes, say, durable storage on the client), you can check for the presence of that ID at a later time and re-apply the transaction to recover from network errors safely.

The transaction ID does wrap around, so there's a time limit depending on your transaction throughput. You can also ask for certain transactional properties on rows, though this won't allow you to recover in all (most?) cases.

[fdr]

Database constraints usually catch these problems in event of re-submission, especially if the client can assign primary keys (e.g., a UUIDv4) a-priori, but this also tends to be true in simpler cases, too.

All in all, I am not sure if anyone should find this surprising: if anyone has ever had a network stall when clicking the 'confirm' button at a web-based store, they are familiar with the uncertainty as to whether the order has been submitted or not (resolved typically by browsing the history or waiting for an email, or no).

I would guess modern e-commerce vendors would send you a UUID or moral equivalent to de-dup cart resubmissions these days...but if not, it'd be interesting to know why not.

[aphyr]

Correct; if your writes are idempotent, retrying is safe. I cover this in the post as well. My above comment shows that it's possible to recover consistency even for writes which are not idempotent--though depending on the semantics of your retries, there may be some locking required.

[pygy_]

Hey! I didn't expect you to chime in right here.

Thanks for the explanation.