다시 처리했는데 지연 목록에 남았다

배송 지연 목록에 같은 주문이 계속 올라왔다. 확인해 보면 이미 재발송 처리가 끝난 건이다. 처리했는데 목록에서 안 빠진다.

Open Table of contents

목록을 만드는 조건
원인 — 재처리가 시각을 안 건드렸다
행 하나에 두 사건이 들어 있었다
고칠 방법이 둘이었다
검증 — 남은 데이터와 임시 조건
정리

목록을 만드는 조건

지연 판정 쿼리는 이랬다.

SELECT order_no, invoice_no, created_at
FROM order_delivery
WHERE invoice_no IS NOT NULL
  AND delivered_at IS NULL
  AND created_at < DATE_SUB(NOW(), INTERVAL 3 DAY)

송장이 나갔는데 3일이 지나도 delivered_at 이 안 찍힌 것을 지연으로 본다. 기준으로 삼는 시각은 created_at 이다.

원인 — 재처리가 시각을 안 건드렸다

재발송 처리는 이렇게 돼 있었다.

UPDATE order_delivery
SET invoice_no = ?, courier = ?
WHERE order_no = ?

invoice_no 와 courier 만 바꾸고 created_at 은 그대로다.

그러면 새 송장은 오늘 나갔는데 기준으로 삼는 시각은 처음 출고한 날이다. 처음 출고가 열흘 전이면 재발송 직후부터 지연으로 잡힌다. 재처리한 건이 재처리하자마자 다시 걸린다.

행 하나에 두 사건이 들어 있었다

근본 문제는 한 행이 두 번의 출고를 담고 있다는 것이다.

처음 출고: 송장 A, 12월 5일
재출고: 송장 B, 12월 16일

행은 하나고 invoice_no 도 하나다. 덮어쓰는 순간 첫 번째 출고 기록이 사라지고 남는 것은 첫 번째의 created_at 과 두 번째의 송장이라는 섞인 상태다.

이 상태에서는 어떤 쿼리를 써도 맞는 답이 안 나온다. 데이터 자체가 두 사건을 구분하지 않으니까.

고칠 방법이 둘이었다

하나는 출고 시각 컬럼을 따로 두는 것이다.

ALTER TABLE order_delivery
  ADD COLUMN shipped_at DATETIME NULL AFTER invoice_no;

지연으로 보는 기준을 created_at 에서 shipped_at 으로 바꾸고 송장을 바꿀 때 이 값도 같이 갱신한다.

UPDATE order_delivery
SET invoice_no = ?, courier = ?, shipped_at = NOW()
WHERE order_no = ?

다른 하나는 출고를 행으로 쌓는 것이다.

CREATE TABLE order_shipment (
  shipment_no INT NOT NULL AUTO_INCREMENT,
  order_no    INT NOT NULL,
  invoice_no  VARCHAR(30) NOT NULL,
  courier     VARCHAR(20) NOT NULL,
  shipped_at  DATETIME NOT NULL,
  PRIMARY KEY (shipment_no),
  KEY idx_order (order_no, shipped_at)
);

재출고가 order_shipment 의 새 행이 되므로 첫 출고 기록이 남는다.

앞의 것이 손이 덜 가고 뒤의 것이 이력을 보존한다. 재발송이 얼마나 자주 일어나는지와 그 이력을 볼 일이 있는지에 따라 갈린다. 운영 중이라 손대는 범위를 좁혀 컬럼을 늘리는 쪽으로 갔다.

검증 — 남은 데이터와 임시 조건

컬럼을 새로 만들면 기존 행은 그 값이 비어 있다. 매뉴얼은 NULL 과의 비교가 참도 거짓도 아닌 NULL 을 낸다고 적어 두고 있어서 판정 쿼리의 WHERE 에서 그 행들이 통째로 빠진다.

기존 값으로 채웠다.

UPDATE order_delivery
SET shipped_at = created_at
WHERE shipped_at IS NULL
  AND invoice_no IS NOT NULL;

정확하지는 않다. 이미 재발송한 건은 첫 출고 시각으로 들어간다. 그래도 비어 있는 것보다는 낫고 이후 재발송부터는 shipped_at 이 제대로 찍힌다.

채운 뒤에 개수를 확인했다.

SELECT COUNT(*) FROM order_delivery WHERE invoice_no IS NOT NULL AND shipped_at IS NULL;

0이 나와야 한다.

원인을 찾기 전에 그 주문들을 목록에서 빼려고 예외 조건을 넣어 뒀던 것도 있었다.

AND order_no NOT IN (10231, 10244, 10250)

이런 NOT IN 조건은 원인을 고친 뒤에 지워야 하는데 안 지우면 그 주문들은 영영 목록에 안 나온다. 임시 조건을 넣을 때 그 자리에 왜 넣었는지 한 줄을 같이 남겨 뒀고 지울 때 그것이 근거가 됐다.

정리

지연으로 보는 기준 시각을 재처리에서 갱신하지 않으면 정상 건이 즉시 다시 걸린다
invoice_no 만 바꾸고 created_at 을 안 건드리면 기준이 옛날 값이다
한 행이 두 사건을 담으면 덮어쓰는 순간 섞인 상태가 된다
고치는 방향은 둘이고 shipped_at 컬럼을 두거나 order_shipment 로 쌓는다
이력을 볼 일이 있으면 행으로 쌓는 쪽이다
NULL 과의 비교는 참도 거짓도 아니라 그 행이 조회에서 통째로 빠진다
새 컬럼을 채운 뒤 COUNT(*) 로 남은 것이 0인지 본다
임시로 넣은 NOT IN 은 원인을 고친 뒤 지우고 넣을 때 이유를 같이 남긴다