Two orders approved at dawn, one still eligible by noon

한국어 원문으로 표시합니다.

목표

승인받은 두 건을 SQL 로만 취소합니다. 승인 당시의 관측을 표로 남기고, 고객·번호·버전·수량·상태가 모두 그대로일 때만 바뀌는 UPDATE 를 만들고, 그 사이 다른 담당자가 바꾼 한 건에서 0행이 바뀌는 것을 실제 데이터베이스에서 확인합니다.

왜 중요한가

읽기에서 본 계약(승인 대상은 id·revision·qty 이고 고객 구분 tenant 는 별도)을 여기서는 표와 함수로 만듭니다. 승인 화면을 본 시각과 실행하는 시각 사이에는 언제나 시간이 흐릅니다. 그 사이를 테이블 잠금으로 막으면 다른 업무가 멈추므로, 대신 승인 시점의 관측을 작은 데이터로 남기고 적용할 때 그 관측이 아직 유효한지 조건으로 비교합니다. 비교가 어긋나면 값을 현재 값으로 조용히 바꾸는 것이 아니라 그 건을 남겨 두고 다시 판단받아야 합니다. 이 실습의 표는 기본 키가 (tenant, id) 라서, 조건에서 고객을 빼면 남의 고객 주문이 함께 취소된다는 것도 같은 자리에서 드러납니다.

바로 다음 모듈의 fde-revision-lab 은 같은 계약을 Python 으로 여덟 함수로 구현합니다. 이 실습은 그 앞에서, 계약 자체가 데이터베이스 안에서 어떻게 생겼는지를 SQL 한 겹으로 먼저 봅니다.

예상 70분입니다. 만료 전에 ＋시간을 눌러 세션을 연장하세요. 세션이 끝나면 /root 아래 파일이 모두 사라집니다.

환경

PostgreSQL 16 이 파드 안에서 이미 돌고 있습니다. 접속은 psql -X -U lab -d labdb 이며 호스트는 127.0.0.1 입니다(export PGHOST=127.0.0.1). 인터넷이나 추가 설치는 필요 없습니다.

이 실습은 스키마 scope 안에서만 작업합니다. public 의 기존 실습 데이터나 다른 스키마를 고치지 마세요. 산출물은 모두 /root/scope 아래에 둡니다. 채점기는 파일에 적힌 숫자를 살아 있는 표에 다시 물어 맞춰 보고, 함수 단계는 자기 트랜잭션 안에 표본을 깔고 여러분의 함수를 부른 뒤 롤백합니다 — 그래서 여러 번 채점해도 데이터가 달라지지 않습니다.

단계

/root/scope/01-schema.sql 을 만들어 스키마 scope 와 표 두 개를 만드세요. scope.orders 는 tenant·id·qty·state·revision 다섯 열이고 기본 키가 (tenant, id) 입니다. state 는 pending·paid·cancelled 만, qty 는 1 부터 1000, revision 은 0 이상입니다. 여섯 행을 넣습니다 — blue/1 은 7개 pending revision 3, blue/2 는 4개 pending revision 1, blue/3 은 9개 pending revision 0, blue/4 는 5개 paid revision 2, green/1 은 7개 pending revision 3, green/2 는 4개 pending revision 1 입니다. scope.baseline 은 같은 다섯 열에 같은 기본 키를 두고, 방금 만든 scope.orders 를 그대로 복사해 채웁니다 — 아침에 조회 화면이 보여 준 그 순간의 관측입니다. SQL 을 적용한 뒤 /root/scope/01-schema.txt 에 rows·tenants·pk·baseline_rows 네 줄을 남기세요.
/root/scope/02-approval.sql 로 표 scope.approval 을 만드세요 — change_id·tenant·id·revision·qty 다섯 열에 기본 키는 (change_id, tenant, id) 입니다. 변경 ID chg-rain-01 로, 고객 blue 의 1번·2번 주문 중 아침 관측(scope.baseline)에서 pending 이던 것만 골라 그때의 revision·qty 와 함께 넣으세요. 넣기 전에 같은 change_id 의 기존 행을 지워 몇 번을 실행해도 결과가 같게 합니다. 그다음 /root/scope/02-approval.txt 에 change_id·targets·digest 세 줄을 남기세요. digest 는 승인 행들을 id 순서로 id:revision:qty 로 적고 콤마로 이은 문자열입니다.
/root/scope/03-validate.sql 로 함수 scope.validate_targets(p jsonb) returns jsonb 를 만드세요. 입력은 승인 대상 배열입니다. 배열이 아니거나, 원소가 0개이거나 16개를 넘거나, 원소가 객체가 아니거나, 키가 id·revision·qty 세 개가 아니거나, 세 값 중 하나라도 JSON 숫자가 아니거나(참·거짓과 문자열은 숫자가 아닙니다), 정수가 아니거나, id 가 1 부터 2147483647 밖이거나, revision 이 2147483646 을 넘거나, qty 가 1 부터 1000 밖이거나, 같은 id 가 두 번 나오면 SQLSTATE 22023 으로 예외를 던집니다. 통과하면 id 오름차순으로 정렬한 새 배열을 돌려주고 각 원소는 id·revision·qty 세 키만 갖습니다.
/root/scope/04-apply.sql 로 함수 scope.apply_change(p_change_id text) returns table(changed integer, skipped integer) 를 만드세요. 그 변경 ID 의 승인 행이 하나도 없으면 SQLSTATE 22023 으로 예외를 던집니다. 있으면 scope.orders 를 갱신하되 tenant·id·revision·qty 가 승인 당시 값과 같고 state 가 pending 인 행만 바꿉니다. 바뀐 행은 state 가 cancelled 가 되고 revision 이 1 늘어납니다. changed 는 실제로 바뀐 행 수, skipped 는 승인 대상 수에서 changed 를 뺀 값입니다. 이 단계에서는 함수만 만들고 chg-rain-01 에 적용하지 마세요.
점심 사이에 다른 담당자가 blue 의 2번 주문 수량을 5 로 고쳤습니다. 정상 업무 변경이므로 revision 도 1 늘어납니다. /root/scope/05-drift.sql 로 그 변경을 만들되, revision 이 아직 1 일 때만 적용되도록 조건을 붙여 몇 번을 실행해도 결과가 같게 하세요. 그다음 /root/scope/05-drift.txt 에 tenant·id·new_qty·new_revision·approved_qty·approved_revision 여섯 줄을 남기세요. 앞의 셋은 지금 scope.orders 에서, 뒤의 둘은 scope.approval 에서 뽑습니다.
chg-rain-01 을 실제로 적용하세요 — select * from scope.apply_change('chg-rain-01') 입니다. 결과를 /root/scope/06-apply.txt 에 changed·skipped·applied_id·blocked_id·other_tenant_changed 다섯 줄로 남기세요. applied_id 는 실제로 취소된 주문 번호, blocked_id 는 승인 대상이지만 바뀌지 않은 주문 번호입니다. other_tenant_changed 는 green 고객의 행 중 아침 관측과 달라진 행의 수이며, scope.baseline 과 scope.orders 를 맞춰 보고 세어야 합니다.
/root/scope/07-reconcile.sql 로 함수 scope.reconcile(p_change_id text) returns table(id integer, verdict text) 를 만드세요. 승인 행마다 지금 scope.orders 를 보고 판정을 붙입니다 — 같은 고객·번호의 행이 없으면 missing, 있고 state 가 cancelled 이며 revision 이 승인 값 + 1 이고 qty 가 승인 값과 같으면 matching, 나머지는 drifted 입니다. 결과는 id 오름차순이고 승인에 없는 번호는 넣지 않습니다. 읽기만 하는 함수여야 합니다. 만든 뒤 chg-rain-01 로 불러 /root/scope/07-reconcile.txt 에 matching·drifted·missing·verdicts 네 줄을 남기세요. verdicts 는 판정을 id 순서로 id:판정 으로 적고 콤마로 이은 문자열입니다.
마지막으로 고객에게 보낼 대조표를 /root/scope/08-report.txt 에 여섯 줄로 만드세요 — approved_targets·applied·drifted·missing·changed_rows_total·changed_outside_approval 입니다. 앞의 넷은 7단계 함수에서, changed_rows_total 은 scope.baseline 과 scope.orders 를 맞춰 qty·state·revision 중 하나라도 달라진 행의 수로, changed_outside_approval 은 그 달라진 행 중 chg-rain-01 의 승인 대상이 아닌 행의 수로 뽑습니다. 여섯 값 모두 손으로 적지 말고 질의 결과를 그대로 넣으세요.

참고

SQL 파일은 psql -X -U lab -d labdb -q -f 파일이름 으로 적용합니다. 증거 파일의 숫자는 손으로 적지 말고 질의 결과를 리다이렉트하세요.
흔한 실수 하나: 조건에 고객을 빼먹는 것. 기본 키가 (tenant, id) 이므로 번호만 맞추면 두 고객의 행이 함께 걸립니다.
흔한 실수 둘: 바뀐 행 수를 UPDATE 앞의 SELECT 로 세는 것. 그 사이에 값이 또 바뀔 수 있습니다. RETURNING 을 CTE 로 감싸 세세요.
공식 문서: UPDATE, JSON 함수, 오류 코드.

변경 대상 표와 아침 관측을 함께 남긴다

/root/scope/01-schema.sql 을 만들어 스키마 scope 와 표 두 개를 만드세요. scope.orders 는 tenant·id·qty·state·revision 다섯 열이고 기본 키가 (tenant, id) 입니다. state 는 pending·paid·cancelled 만, qty 는 1 부터 1000, revision 은 0 이상입니다. 여섯 행을 넣습니다 — blue/1 은 7개 pending revision 3, blue/2 는 4개 pending revision 1, blue/3 은 9개 pending revision 0, blue/4 는 5개 paid revision 2, green/1 은 7개 pending revision 3, green/2 는 4개 pending revision 1 입니다. scope.baseline 은 같은 다섯 열에 같은 기본 키를 두고, 방금 만든 scope.orders 를 그대로 복사해 채웁니다 — 아침에 조회 화면이 보여 준 그 순간의 관측입니다. SQL 을 적용한 뒤 /root/scope/01-schema.txt 에 rows·tenants·pk·baseline_rows 네 줄을 남기세요.

기본 키를 id 하나로 잡으면 green 의 1번 주문을 아예 넣을 수 없습니다. 고객이 다르면 같은 주문 번호가 존재할 수 있다는 것이 이 표의 전제입니다. 다시 실행해도 같은 결과가 되도록 create table if not exists 와 on conflict do nothing 을 쓰세요. pk 줄에는 기본 키 열 이름을 순서대로 콤마로 이어 적습니다.

승인 당시의 관측을 표로 못 박는다

/root/scope/02-approval.sql 로 표 scope.approval 을 만드세요 — change_id·tenant·id·revision·qty 다섯 열에 기본 키는 (change_id, tenant, id) 입니다. 변경 ID chg-rain-01 로, 고객 blue 의 1번·2번 주문 중 아침 관측(scope.baseline)에서 pending 이던 것만 골라 그때의 revision·qty 와 함께 넣으세요. 넣기 전에 같은 change_id 의 기존 행을 지워 몇 번을 실행해도 결과가 같게 합니다. 그다음 /root/scope/02-approval.txt 에 change_id·targets·digest 세 줄을 남기세요. digest 는 승인 행들을 id 순서로 id:revision:qty 로 적고 콤마로 이은 문자열입니다.

승인 스냅샷은 지금 값이 아니라 승인한 순간의 값을 담습니다. 그래서 읽는 곳이 scope.orders 가 아니라 scope.baseline 입니다. digest 는 string_agg 에 order by 를 붙여 만들면 행 순서가 바뀌어도 같은 문자열이 나옵니다.

중복·빈 목록·숫자가 아닌 것을 쓰기 전에 거른다

/root/scope/03-validate.sql 로 함수 scope.validate_targets(p jsonb) returns jsonb 를 만드세요. 입력은 승인 대상 배열입니다. 배열이 아니거나, 원소가 0개이거나 16개를 넘거나, 원소가 객체가 아니거나, 키가 id·revision·qty 세 개가 아니거나, 세 값 중 하나라도 JSON 숫자가 아니거나(참·거짓과 문자열은 숫자가 아닙니다), 정수가 아니거나, id 가 1 부터 2147483647 밖이거나, revision 이 2147483646 을 넘거나, qty 가 1 부터 1000 밖이거나, 같은 id 가 두 번 나오면 SQLSTATE 22023 으로 예외를 던집니다. 통과하면 id 오름차순으로 정렬한 새 배열을 돌려주고 각 원소는 id·revision·qty 세 키만 갖습니다.

jsonb_typeof 가 참·거짓을 boolean 으로, 따옴표 친 숫자를 string 으로 구분해 줍니다. 소수점은 typeof 로는 걸리지 않으니 문자열로 뽑아 정수인지 한 번 더 보세요. 예외를 던질 때 SQLSTATE 를 지정하려면 raise exception using errcode 를 씁니다.

승인한 값이 전부 그대로일 때만 바꾸는 함수

/root/scope/04-apply.sql 로 함수 scope.apply_change(p_change_id text) returns table(changed integer, skipped integer) 를 만드세요. 그 변경 ID 의 승인 행이 하나도 없으면 SQLSTATE 22023 으로 예외를 던집니다. 있으면 scope.orders 를 갱신하되 tenant·id·revision·qty 가 승인 당시 값과 같고 state 가 pending 인 행만 바꿉니다. 바뀐 행은 state 가 cancelled 가 되고 revision 이 1 늘어납니다. changed 는 실제로 바뀐 행 수, skipped 는 승인 대상 수에서 changed 를 뺀 값입니다. 이 단계에서는 함수만 만들고 chg-rain-01 에 적용하지 마세요.

UPDATE 에 FROM 절로 승인 표를 붙이면 조건 다섯 개를 한 문장에 넣을 수 있습니다. 실제로 몇 행이 바뀌었는지는 RETURNING 을 CTE 로 감싸 세는 것이 정확합니다 — 먼저 SELECT 로 세어 두고 UPDATE 하면 그 사이의 변경을 놓칩니다. 채점기는 자기 트랜잭션에 표본을 깔고 이 함수를 부른 뒤 롤백하므로, 함수는 스키마 이름을 고정해 쓰면 됩니다.

승인 뒤 다른 담당자가 한 건을 바꾼다

점심 사이에 다른 담당자가 blue 의 2번 주문 수량을 5 로 고쳤습니다. 정상 업무 변경이므로 revision 도 1 늘어납니다. /root/scope/05-drift.sql 로 그 변경을 만들되, revision 이 아직 1 일 때만 적용되도록 조건을 붙여 몇 번을 실행해도 결과가 같게 하세요. 그다음 /root/scope/05-drift.txt 에 tenant·id·new_qty·new_revision·approved_qty·approved_revision 여섯 줄을 남기세요. 앞의 셋은 지금 scope.orders 에서, 뒤의 둘은 scope.approval 에서 뽑습니다.

승인 스냅샷은 이 단계에서 한 글자도 바뀌면 안 됩니다. 바뀐 것은 현재 행뿐입니다. 두 숫자가 서로 달라졌다는 사실 자체가 다음 단계에서 0행이 바뀌는 이유입니다.

적용하면 한 건만 바뀐다

chg-rain-01 을 실제로 적용하세요 — select * from scope.apply_change('chg-rain-01') 입니다. 결과를 /root/scope/06-apply.txt 에 changed·skipped·applied_id·blocked_id·other_tenant_changed 다섯 줄로 남기세요. applied_id 는 실제로 취소된 주문 번호, blocked_id 는 승인 대상이지만 바뀌지 않은 주문 번호입니다. other_tenant_changed 는 green 고객의 행 중 아침 관측과 달라진 행의 수이며, scope.baseline 과 scope.orders 를 맞춰 보고 세어야 합니다.

승인 대상은 두 건인데 하나는 아침 관측과 값이 달라졌습니다. 조건이 다섯 개 모두 일치해야 바뀌므로 이 행은 그냥 지나갑니다 — 오류가 아니라 0행입니다. green 의 1번 주문은 blue 의 1번 주문과 번호도 버전도 수량도 같습니다. 조건에 고객이 들어 있는지 여기서 드러납니다.

건수가 아니라 대상으로 대조한다

/root/scope/07-reconcile.sql 로 함수 scope.reconcile(p_change_id text) returns table(id integer, verdict text) 를 만드세요. 승인 행마다 지금 scope.orders 를 보고 판정을 붙입니다 — 같은 고객·번호의 행이 없으면 missing, 있고 state 가 cancelled 이며 revision 이 승인 값 + 1 이고 qty 가 승인 값과 같으면 matching, 나머지는 drifted 입니다. 결과는 id 오름차순이고 승인에 없는 번호는 넣지 않습니다. 읽기만 하는 함수여야 합니다. 만든 뒤 chg-rain-01 로 불러 /root/scope/07-reconcile.txt 에 matching·drifted·missing·verdicts 네 줄을 남기세요. verdicts 는 판정을 id 순서로 id:판정 으로 적고 콤마로 이은 문자열입니다.

승인 행을 왼쪽에 두고 현재 행을 LEFT JOIN 하면 없어진 대상이 NULL 로 남아 missing 을 구분할 수 있습니다. 조인 조건에 고객과 번호를 모두 넣어야 다른 고객의 같은 번호가 붙지 않습니다. 수량이 같아도 버전이 다르면 그것은 이후의 다른 변경입니다.

승인 범위와 실제 변경을 나란히 적는다

마지막으로 고객에게 보낼 대조표를 /root/scope/08-report.txt 에 여섯 줄로 만드세요 — approved_targets·applied·drifted·missing·changed_rows_total·changed_outside_approval 입니다. 앞의 넷은 7단계 함수에서, changed_rows_total 은 scope.baseline 과 scope.orders 를 맞춰 qty·state·revision 중 하나라도 달라진 행의 수로, changed_outside_approval 은 그 달라진 행 중 chg-rain-01 의 승인 대상이 아닌 행의 수로 뽑습니다. 여섯 값 모두 손으로 적지 말고 질의 결과를 그대로 넣으세요.

마지막 줄이 이 실습의 결론입니다. 승인 범위 밖에서 아무것도 바뀌지 않았다는 것을 건수가 아니라 대상으로 보여 주는 숫자입니다. 아침 관측과 지금을 비교할 때 NULL 이 섞일 수 있으니 is distinct from 을 쓰면 안전합니다.