같은 사용자·캠페인에 쿠폰 한 건만 두는 테이블을 생각해 보자. (user_id, campaign_id) 일반 인덱스만 있을 때 다음 upsert를 실행하면 어떻게 될까.

INSERT INTO coupon_grant(user_id, campaign_id, coupon_id, issued_at)
VALUES (:user_id, :campaign_id, :coupon_id, now())
ON CONFLICT (user_id, campaign_id)
DO UPDATE SET coupon_id = EXCLUDED.coupon_id,
              issued_at = EXCLUDED.issued_at;

일반 인덱스는 중복을 허용한다. PostgreSQL이 이 충돌 대상을 판정할 unique arbiter를 찾지 못하면 다음 오류를 반환한다.

there is no unique or exclusion constraint matching the ON CONFLICT specification
SQLSTATE: 42P10

이 글의 질문은 ON CONFLICT의 대상과 실행 DB의 고유성 규칙이 실제로 일치하는가다. PostgreSQL 17을 기준으로 읽기 전용 진단부터 시작하고 제약 변경과 보정 실행은 별도의 판단으로 다룬다.

모델과 마이그레이션은 catalog를 대신하지 않는다

ORM에서 다음 선언을 발견해도 기존 테이블에 제약이 존재한다는 증거는 아니다.

__table_args__ = (
    UniqueConstraint(
        "user_id", "campaign_id",
        name="uq_coupon_grant_user_campaign",
    ),
)

이 조각은 모델의 의도를 표현한다. 마이그레이션 파일은 적용하려는 변경이고 실제 catalog는 지금 존재하는 스키마다. 서로 다른 DB·schema, 누락된 배포나 실패한 DDL, 수동 변경 때문에 셋이 달라질 수 있다.

ORM의 UNIQUE 선언과 마이그레이션이 있어도 실제 DB catalog에서 일치하는 arbiter가 없으면 42P10이 반환되는 흐름

그림의 진단 대상은 SQL을 받은 DB다. 모델부터 다시 고치기 전에 연결과 테이블을 확인한다.

SELECT current_database(), current_schema(), current_user;
SHOW search_path;
SELECT to_regclass('public.coupon_grant');

예상 database·schema인지 확인하고 동명 테이블을 구분한다. 이어서 제약과 인덱스 정의를 읽는다.

SELECT con.conname, con.contype,
       pg_get_constraintdef(con.oid) AS definition,
       con.condeferrable, con.condeferred
FROM pg_constraint con
JOIN pg_class rel ON rel.oid = con.conrelid
JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace
WHERE nsp.nspname = 'public' AND rel.relname = 'coupon_grant';

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'coupon_grant';

고유성, 컬럼·표현식과 predicate, 지연 검사 여부를 SQL target과 비교한다. 제약 개수만 비교하면 규칙 차이를 놓친다. 필요한 경우 pg_index의 valid·ready 상태와 pg_get_indexdef도 확인한다. 마이그레이션 적용 기록은 실제 정의와 함께 읽는다.

충돌 대상은 중복을 정의하는 규칙이다

ON CONFLICT는 컬럼·표현식 집합과 선택적 predicate로 unique index를 추론하거나 제약 이름을 지정한다. DO UPDATE에는 exclusion constraint를 arbiter로 사용할 수 없고 지연 가능한 제약도 사용하지 못한다. PostgreSQL INSERT의 충돌 대상.

정말 사용자·캠페인 전체에 한 행만 허용하는 정책이라면 다음 둘 중 한 방식의 고유성이 필요하다.

-- 정책·기존 데이터·변경 절차를 확인한 뒤 선택할 DDL 예제다.
ALTER TABLE coupon_grant
ADD CONSTRAINT uq_coupon_grant_user_campaign
UNIQUE (user_id, campaign_id);

-- 대안: 동등한 unique index
CREATE UNIQUE INDEX ux_coupon_grant_user_campaign
ON coupon_grant (user_id, campaign_id);

두 명령을 모두 실행하라는 절차가 아니다. 일반 인덱스와 unique index의 차이를 보여주는 대안이다. 오류가 사라지는 고유 키를 만들기보다 업무에서 허용할 재발급·취소의 의미부터 확인한다.

활성 행에만 한 건을 허용하면 partial 규칙을 사용한다.

CREATE UNIQUE INDEX ux_coupon_grant_active
ON coupon_grant (user_id, campaign_id)
WHERE revoked_at IS NULL;

INSERT INTO coupon_grant(user_id, campaign_id, coupon_id, revoked_at)
VALUES (:user_id, :campaign_id, :coupon_id, NULL)
ON CONFLICT (user_id, campaign_id) WHERE revoked_at IS NULL
DO UPDATE SET coupon_id = EXCLUDED.coupon_id;

인덱스 predicate와 target이 추론 가능한 관계여야 한다. 별도 비부분 unique index가 같은 컬럼 집합에 있으면 그것도 추론될 수 있어 실제 전체 정의를 확인한다. 소문자 이메일을 고유 키로 삼는 경우에도 표현식을 맞춘다.

CREATE UNIQUE INDEX ux_member_email_lower ON member (lower(email));
INSERT INTO member(email, name) VALUES (:email, :name)
ON CONFLICT (lower(email)) DO UPDATE SET name = EXCLUDED.name;

email과 lower(email)은 같은 규칙이 아니다. INCLUDE 컬럼도 유일성 key 컬럼과 구별한다.

NULL 정책은 arbiter를 찾은 뒤에도 확인한다

UNIQUE(user_id, external_key)에서 external_key가 NULL이면 기본 규칙은 여러 NULL 행을 허용한다. NULL도 같은 비즈니스 키라면 NOT NULL이나 UNIQUE NULLS NOT DISTINCT를 검토한다. 후자는 PostgreSQL 15부터 제공된다. NULL 고유성 규칙, 15 버전 변경.

NULL을 빈 문자열로 치환하기 전에 null·빈 값·공백이 같은 의도인지 정한다. 오류 없는 upsert와 업무상 중복이 없다는 결과는 별개다.

기존 중복과 쓰기 대상은 먼저 조회한다

고유성이 없었던 테이블은 이미 중복을 포함할 수 있다.

SELECT user_id, campaign_id, COUNT(*) AS duplicate_count,
       array_agg(id ORDER BY created_at) AS row_ids
FROM coupon_grant
GROUP BY user_id, campaign_id
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

이 결과에서 한 행을 임의로 삭제하지 않는다. 발급·사용·취소의 참조와 허용된 재발급인지 확인하고 유지할 키, 보정·복구 계획을 정한다. 실제 키가 partial 규칙이면 조사 조건도 그 규칙에 맞춘다.

보정 source의 join을 읽기 전용으로 먼저 실행하면 신규·동일값·변경·정책 충돌을 분류할 수 있다.

SELECT s.user_id, s.campaign_id, s.coupon_id,
       g.id AS existing_grant_id, g.coupon_id AS existing_coupon_id
FROM correction_source s
LEFT JOIN coupon_grant g
  ON g.user_id = s.user_id AND g.campaign_id = s.campaign_id;

예상 행 수와 before·after, 참조와 rollback 기준을 확인한 뒤 쓰기 계획을 만든다. 잘못된 target은 42P10을 없애면서 기존 행을 잘못 갱신할 수 있다.

큰 테이블의 인덱스 변경에서는 일반 생성의 쓰기 차단과 CONCURRENTLY의 비용을 비교한다. concurrent 생성은 트랜잭션 블록 안에서 실행할 수 없고 실패 시 invalid index가 남을 수 있다. 일부 단계에서는 완료 전에 고유성을 검사하기도 한다. CREATE INDEX의 concurrent 제약을 실제 버전과 트래픽에 맞춰 검토한다.

오류별 검증으로 수정 범위를 확인한다

42P10 메시지는 사용 가능한 충돌 판정자를 찾지 못했다는 단서다. 23505는 실제 unique violation으로, 다른 제약 또는 UPDATE 결과에서 발생할 수도 있다. 모든 integrity 오류를 이미 발급됨으로 바꾸지 않고 SQLSTATE와 constraint name을 구별한다.

ON CONSTRAINT uq_coupon_grant_user_campaign은 참조 규칙이 명확하지만 이름 교체에 결합된다. 컬럼 기반 inference는 동등한 인덱스 교체에 유연할 수 있다. 이름·표현식·predicate의 유지 정책으로 선택한다.

통합 테스트 DB는 ORM 모델로 새 테이블을 만드는 방식과 마이그레이션을 적용하는 방식을 구분한다. 최초 삽입, 같은 키 반복, 동시 삽입, partial 조건 밖의 행, NULL, 다른 UNIQUE 충돌과 인덱스 교체를 확인한다. staging·production의 실제 catalog 비교도 별도 확인이다.

이 진단은 모델에 unique가 있는데 upsert가 실패하는 상황에서 유효하다. 다음 확인은 연결 대상과 catalog의 정의다. 고유성 변경이 필요하더라도 기존 중복의 의미와 보정 대상이 확인돼야 실행 계획을 정할 수 있다.