에디터 교체 이후 발생한 json 데이터 오류 추적기
에디터 교체 이후 드러난 콘텐츠 오류, 콘텐츠 변경 이력을 따라가며 데이터 손실을 복구하고 타입을 일치시키기
몇몇 상품에서 의도치 않은 스타일 태그가 본문에 노출되는 문제가 발생했습니다. 하지만, 그 상품들을 수정한 적이 없었고 해당 스타일 태그는 원래도 존재하던 것이어서 의문이 들었습니다. 결국 이력 테이블을 뒤져가며 문제를 해결했습니다.
문제 상황: 스타일 태그가 콘텐츠 본문에 노츨됐다?
최근 운영 시스템의 변경사항을 생각해보니 몇개월 전 에디터를 교체한 적이 있었습니다. 에디터를 교체한 후 아래와 같은 CSS 스타일 정의가 콘텐츠 본문 안에 그대로 노출되는 현상이 발견됐습니다:
1
#vimeo-style {width: 100%;max-width: 720px;aspect-ratio: 1/0.6;}
해당 콘텐츠는 원래 iframe 또는 video 태그를 감싸는 내부 스타일에만 존재했어야 했습니다. 그러나 사용자 화면에 HTML 코드로 노출되면서 콘텐츠 퀄리티에 영향을 주는 UX 이슈로 번졌습니다.
문제 분석 1단계: 변경된 데이터 추적
문제를 추적하기 위해 우리는 먼저 PostgreSQL의 이력 테이블 예시(product_history) 을 조회했습니다. 이 테이블은 product 테이블의 수정 이력을 기록하며, product_id, revision_id, detail, updated_at 등을 갖고 있습니다.
가장 최근 2개의 이력을 비교해서 어떤 변경이 있었는지 보기 위한 쿼리는 다음과 같습니다:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH ranked_history AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY revision_id DESC) AS rn
FROM product_history
WHERE product_id IN (
SELECT product_id FROM product WHERE updated_by = 'editor_bot'
)
),
pivoted AS (
SELECT
curr.product_id,
curr.detail AS curr_detail,
prev.detail AS prev_detail
FROM ranked_history curr
JOIN ranked_history prev
ON curr.product_id = prev.product_id
AND curr.rn = 1 AND prev.rn = 2
)
SELECT
product_id,
curr_detail ->> 'what' AS current_what, -- 예시 json 키값
prev_detail ->> 'what' AS previous_what -- 예시 json 키값
FROM pivoted
WHERE curr_detail IS DISTINCT FROM prev_detail;
이 쿼리로 어떤 콘텐츠가 변경되었는지, 무엇이 어떻게 바뀌었는지 비교를 할 수 있습니다.
근데, 예상치 못한 이슈 발생! history 테이블의 detail 컬럼 타입이 jsonb가 아니라 text였습니다.
💥 JSON 컬럼이 text로 기록돼 있었다?
detail 컬럼은 운영 테이블에서는 jsonb 타입으로 사용 중인데, 이력 테이블에서는 text 타입으로 저장되고 있었습니다.
예전 product 테이블의 detail 컬럼은 text였으나, 콘텐츠 정보를 일관성 있는 형식으로 제공하기 위해 jsonb로 변경하였습니다. 히스토리 테이블은 데이터 타입 변경을 하지 않았습니다. (데이터 타입이 변경된 것도 남기려고 그랬던건가 추측..)
깨알 스키마 컬럼 정보 확인할 때 유용한 쿼리
1
2
3
4
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'product_history'
AND column_name = 'detail';
🔁 복구 작업: text → jsonb 변환 + 필드 복원
- text -> jsonb 변환 시도 먼저 text를 jsonb로 변환한 데이터를 넣을 걸럼
detail_json을 새로 만들었습니다. 그리고 변환.
1
2
3
UPDATE product_history
SET detail_json = detail::jsonb
WHERE detail_json IS NULL;
- 예외 처리: 파싱이 실패하는 경우 확인
1
2
3
SELECT detail
FROM product_history
WHERE jsonb_pretty(detail::jsonb) IS NULL;
일부 기록은 jsonb로 변환 전의 기록이 남아있어서, json으로 바로 파싱이 되지 않았습니다. 이 케이스들은 jsonb_build_object('legacy', detail) 형태로 감싸서 강제로 jsonb화하여 데이터 보정처리했습니다.
text -> jsonb로의 데이터 보정을 완료하고, 기존에 있던 detail 컬럼 삭제 detail_json의 이름은 detail로 변환하여 데이터 타입 변경 및 데이터 보정 작업까지 완료했습니다.
🔄 필드 복원: json 데이터에서 삭제된 key + value 복구
몇몇 콘텐츠에서 예전에는 사용했지만 정책 변경으로 더이상 사용하지 않게 된 데이터를 복구했습니다. 이는 콘텐츠팀에서 인지하지 못하고 데이터가 삭제된 경우라 복구 요청이 있었습니다. 이전 revision에서 값을 끌어와 복구했습니다.
1
2
3
4
5
6
7
8
WITH changed AS (
-- pivoted 쿼리 활용
)
UPDATE product_info p
SET detail = jsonb_set(p.detail, '{extra}', to_jsonb(c.prev_detail->>'extra'))
FROM changed c
WHERE p.product_id = c.product_id
AND (p.detail->>'extra') IS NULL;
(실제 데이터 key 값이 아닙니다. 예시일 뿐)
Json 함수를 만들자
두 jsonb 간의 변경점(diff)를 계산하는 내장 함수가 없어서 만들어 썼습니다.(있다면 알려주세요 ㅠ )
커스텀 함수 생성
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE FUNCTION jsonb_diff(old jsonb, new jsonb)
RETURNS jsonb AS $$
DECLARE
key text;
result jsonb := '{}';
BEGIN
FOR key IN SELECT jsonb_object_keys(old || new)
LOOP
IF old -> key IS DISTINCT FROM new -> key THEN
result := result || jsonb_build_object(key, jsonb_build_object(
'before', old -> key,
'after', new -> key
));
END IF;
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
사용 예시
1
2
3
4
5
SELECT
product_id,
jsonb_diff(prev_detail, curr_detail) AS changes
FROM pivoted
WHERE prev_detail IS DISTINCT FROM curr_detail;
배운점 & 실무 인사이트
이번 경험은 단순한 데이터 수정이 아닌, 다음을 포함한 전체 워크플로우를 실전에서 경험한 사례였습니다:
- 콘텐츠 변경 이력 추적
- 변경된 필드만 분석 및 복구
- 데이터 타입 불일치 문제 해결
- json 파싱 불가 사례 예외 처리
- 커스텀 JSON diff 함수 작성
변경 이력 추적 테이블을 만들고 데이터를 잘 쌓아왔기 때문에 복구 할 수 있었습니다.