Rerunning a failed French import job without duplicating rows

A nightly job pulls a supplier CSV from a partner in Lyon and upserts it into Postgres 15 on a small OVH VPS. About 180k rows, 14 columns, one of them a French product label with embedded semicolons. The job failed halfway on a Tuesday: connection reset after 90k rows, and the retry inserted everything again because the upsert key was wrong. We ended up with 90k duplicate rows and a support queue full of double stock counts.

My fix was not clever, just boring. I added a natural key and made the load idempotent per batch:

ALTER TABLE supplier_items
  ADD CONSTRAINT supplier_items_key UNIQUE (supplier_ref, valid_from);

INSERT INTO supplier_items (supplier_ref, valid_from, label, price_eur, raw_line)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (supplier_ref, valid_from)
DO UPDATE SET label = EXCLUDED.label,
              price_eur = EXCLUDED.price_eur,
              raw_line = EXCLUDED.raw_line;

The loader now commits every 5k rows and writes the last committed line number to a small checkpoint table. On retry it seeks to that offset. The CSV reader uses Python’s csv module with delimiter=';', quotechar='"', and newline='' so the semicolons inside quoted labels stay inside the field. I also log the raw line into raw_line for anything that fails parsing, which is how I found three rows where the partner had used a non-breaking space before the semicolon.

The trade-off is that the upsert is slower than a bulk insert. On this VPS it runs about 12 minutes for 180k rows instead of 4, and it holds more WAL. I accepted that because the job runs at 02:00 and nobody is watching it. The checkpoint table adds one more state to keep clean, and it assumes the CSV lines are stable between runs. They are not always: the partner regenerates the file with a different sort order on Mondays.

That is where it fails. The checkpoint offset is only safe if the file is byte-identical on retry. When the partner re-sorted, our resume point landed in the middle of a different row and we duplicated about 3k rows again. The honest fix would have been a content hash per row, but I have not built that yet. For now the loader compares file size and mtime and refuses to resume if either changed; it fails loudly instead of guessing.

What do people here do when the upstream file is not stable across retries and you still want a cheap resume? I am weighing a staging table with a full reload versus hashing every line before the upsert.