[PostgreSQL] DEFERRABLE INITIALLY DEFERRED — 순환 FK를 커밋 시점에 검증한다

설치·접속: PostgreSQL 설치와 접속

부제: 두 행이 서로를 FK로 참조(A↔B)해서 어느 쪽을 먼저 INSERT해도 막힐 때

-- 즉시 검사되는 일반 FK: 순환 참조를 못 넣는다
CREATE TABLE node_immediate (
    id         integer PRIMARY KEY,
    name       text,
    partner_id integer REFERENCES node_immediate(id)
);

BEGIN;
INSERT INTO node_immediate (id, name, partner_id) VALUES (1, 'A', 2);  -- 2가 아직 없음
INSERT INTO node_immediate (id, name, partner_id) VALUES (2, 'B', 1);
COMMIT;

BEGIN
ERROR:  insert or update on table "node_immediate" violates foreign key constraint "node_immediate_partner_id_fkey"
DETAIL:  Key (partner_id)=(2) is not present in table "node_immediate".

A는 B(id=2)를 참조하고 B는 A(id=1)를 참조한다. 일반 FK는 매 문장 직후 검사하므로, 먼저 넣는 행은 상대가 아직 없어 항상 위반이다. INSERT 순서를 아무리 바꿔도 첫 행에서 막힌다.

FK를 DEFERRABLE INITIALLY DEFERRED로 선언하면 검사 시점이 트랜잭션 종료(COMMIT)로 미뤄진다. 중간 상태가 위반이어도 커밋 시점에 전체가 정합적이면 통과한다.

CREATE TABLE node (
    id         integer PRIMARY KEY,
    name       text,
    partner_id integer REFERENCES node(id) DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO node (id, name, partner_id) VALUES (1, 'A', 2);  -- 중간 위반이지만 통과
INSERT INTO node (id, name, partner_id) VALUES (2, 'B', 1);
COMMIT;
SELECT * FROM node ORDER BY id;

BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
 id | name | partner_id
----+------+------------
  1 | A    |          2
  2 | B    |          1

첫 INSERT 시점엔 id=2가 없어 위반이지만 검사를 미뤄뒀으니 통과하고, COMMIT 시점엔 둘 다 존재해 정합적이라 커밋된다. 지연 가능한 제약은 UNIQUE·PK·EXCLUDE·FK뿐이고 NOT NULL·CHECK는 안 된다. INITIALLY DEFERRED는 처음부터 지연, INITIALLY IMMEDIATE(기본)는 즉시 검사하되 트랜잭션 안에서 SET CONSTRAINTS ... DEFERRED로 바꿀 수 있다. 지연 제약은 커밋 직전까지 위반 상태를 들고 있어야 하므로 그만큼 트랜잭션이 길어지는 비용이 있다.

이렇게도 쓴다

제약은 즉시(IMMEDIATE)로 두고 필요한 트랜잭션에서만 지연시킨다.

-- FK를 DEFERRABLE INITIALLY IMMEDIATE로 만든 뒤
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT ...; INSERT ...;
COMMIT;

 

정렬 순번(UNIQUE)을 여러 행에서 서로 맞바꾼다. 즉시 검사면 스왑 중간에 중복이라 막힌다. (조합: 지연 UNIQUE)

ALTER TABLE items ADD CONSTRAINT uq_pos UNIQUE (list_id, pos) DEFERRABLE INITIALLY DEFERRED;
BEGIN;
UPDATE items SET pos=2 WHERE id=10;  -- 잠깐 pos=2가 둘
UPDATE items SET pos=1 WHERE id=11;
COMMIT;

 

특정 제약만 이름으로 지목해 지연한다.

SET CONSTRAINTS node_partner_id_fkey DEFERRED;

 

EXCLUDE 제약도 지연 가능하다. 예약 구간을 일괄 재배치할 때 커밋 시점에만 겹침을 검사. (조합: EXCLUDE)

EXCLUDE USING GIST (during WITH &&) DEFERRABLE INITIALLY DEFERRED