PostgreSQL에서 방금 만든 임시 테이블이 없다면 자동 커밋을 확인한다

CREATE TEMP TABLE ... ON COMMIT DROP은 성공했는데 바로 다음 조회에서 relation does not exist가 나온다면, 생성문이 끝날 때 이미 커밋됐는지 확인한다. 자동 커밋으로 생성문을 따로 실행하면 그 문장의 트랜잭션이 끝나면서 임시 테이블도 삭제될 수 있다. 같은 연결에서 조회해도 마찬가지다.

한 작업 안에서만 쓸 테이블이면 생성과 사용을 BEGIN부터 COMMIT 사이에 넣는다. 커밋을 넘겨 같은 세션에서 계속 써야 한다면 ON COMMIT PRESERVE ROWS가 맞다. PostgreSQL의 임시 테이블은 세션이 끝나면 삭제되며, ON COMMIT DROP은 그보다 짧은 트랜잭션 수명을 지정한다. PostgreSQL 18 CREATE TABLE 문서↗ (새 탭에서 열림)

먼저 생성과 조회가 어느 연결에서 실행됐는지 본다

문제가 난 생성문과 조회문을 실행하는 연결에서 다음 SQL을 각각 실행해 DB 이름과 서버 PID를 비교한다.

SELECT current_database() AS db, pg_backend_pid() AS pid;

pg_backend_pid()는 현재 세션을 담당하는 서버 프로세스의 ID다. 같은 서버에서 연속 실행했는데 PID가 달라졌다면 생성과 조회 사이에 연결이 바뀐 것이다. DB 이름도 다르면 접속 대상부터 맞춘다. SQL 편집기의 탭 이름만으로 연결이 유지된다고 판단하지 않는다. 세션 정보 함수↗ (새 탭에서 열림)

이어서 해당 연결의 자동 커밋 설정을 본다. psql에서는 \echo :AUTOCOMMIT으로 확인할 수 있으며 기본값은 on이다. 다른 도구에서는 그 연결의 자동 커밋 설정과 실행기가 보낸 BEGIN·COMMIT 기록을 확인한다. psql 변수 조회를 일반 SQL처럼 실행할 수는 없다. psql의 AUTOCOMMIT 설명↗ (새 탭에서 열림)

아래 비교는 PostgreSQL 서버와 psql 모두 18.6인 별도 실습 DB에서 실행했다. 처음부터 끝까지 연결 A의 PID는 112였다. 자동 커밋은 on이었고, 처음에는 temp_batch라는 테이블이 없었다.

같은 조건을 확인하려면 임시 테이블 생성 권한이 있는 실습 DB의 새 psql 세션에서 다음 두 문장을 하나씩 실행한다. 별도의 BEGIN은 열지 않는다.

CREATE TEMP TABLE temp_batch (id integer) ON COMMIT DROP;
SELECT * FROM temp_batch;

생성 응답은 CREATE TABLE이었지만 조회에서는 다음 오류가 났다. SQLSTATE는 실패 직후 psql의 \echo :SQLSTATE로 확인하면 42P01이다.

CREATE TABLE
ERROR:  relation "temp_batch" does not exist

이 코드의 뜻은 테이블을 찾지 못했다는 것이다. 오류 코드만으로 자동 커밋이 원인이라고 확정할 수는 없다. 이번 비교에서는 연결이 바뀌지 않았고, 생성 성공 직후부터 테이블이 없다는 점을 함께 확인했다. SQLSTATE 42P01: undefined_table↗ (새 탭에서 열림)

PostgreSQL은 모든 SQL 문장을 트랜잭션 안에서 실행한다. 클라이언트가 트랜잭션을 따로 열어 두지 않았다면, 성공한 개별 문장의 실행도 커밋으로 끝난다. 따라서 이 조건에서는 생성문의 커밋이 곧 ON COMMIT DROP의 삭제 시점이다. 트랜잭션 튜토리얼↗ (새 탭에서 열림)

실행 단위도 맞춰야 한다. psql의 -c 한 문자열에 여러 SQL을 넣으면, 명시적 트랜잭션 제어가 없는 경우 그 요청은 하나의 트랜잭션으로 실행된다. 별도 대조에서는 생성·삽입·조회를 한 -c 요청으로 보내자 값 7을 읽을 수 있었다. 열린 psql에서 문장별로 실행한 위 결과와 달라지는 이유다. 편집기에서 여러 문장을 선택했다면 실제 전송 단위도 확인한다. psql의 -c 실행 방식↗ (새 탭에서 열림)

한 트랜잭션에서 쓰고 버릴 테이블이라면

중간 계산용으로 만들고 작업이 끝나면 버릴 테이블이라면 DROP을 유지하고, 생성부터 마지막 사용까지 같은 연결의 명시 트랜잭션에 넣는다. 앞의 두 문장을 실행한 실습 세션에서 다음처럼 비교할 수 있다.

BEGIN;
CREATE TEMP TABLE temp_batch (id integer) ON COMMIT DROP;
INSERT INTO temp_batch VALUES (7);
SELECT id FROM temp_batch;
COMMIT;
SELECT * FROM temp_batch;

COMMIT 전의 조회는 7을 반환했다. COMMIT 뒤의 조회는 다시 42P01로 실패했다. 이는 테이블을 사용할 마지막 문장이 커밋보다 앞에 있어야 한다는 뜻이다. 실제 작업에서는 마지막 조회나 결과를 옮기는 SQL까지 끝낸 다음 커밋한다. 위의 마지막 조회는 삭제 시점을 확인하기 위한 입력이다.

이 비교에서는 자동 커밋 설정을 끄지 않았다. on이어도 명시적으로 연 BEGIN 안의 문장들을 각각 커밋하지 않는다. 반대로 생성 후에 BEGIN을 실행해도 앞서 삭제된 테이블은 돌아오지 않는다. 트랜잭션 경계는 생성 전부터 잡아야 한다.

커밋 뒤에도 같은 세션에서 쓸 테이블이라면

여러 트랜잭션에 걸쳐 같은 임시 데이터를 써야 한다면, 생성할 때 커밋 뒤에도 테이블과 행을 유지하도록 지정한다. 앞선 테이블이 삭제된 같은 실습 세션에서 다음을 실행했다.

CREATE TEMP TABLE temp_batch (id integer) ON COMMIT PRESERVE ROWS;
INSERT INTO temp_batch VALUES (7);
SELECT id FROM temp_batch;

자동 커밋으로 생성과 삽입이 각각 끝난 뒤에도 조회 결과는 7이었다. 이어 명시 트랜잭션을 열었다가 커밋해도 같은 값이 남았다. PostgreSQL에서는 ON COMMIT 절을 생략했을 때도 PRESERVE ROWS가 기본값이다. 이미 DROP으로 사라진 테이블을 되살리는 설정은 아니므로 생성문을 바꿔 다시 만들어야 한다.

다만 행을 보존하는 범위는 같은 DB 세션까지다. 연결 A를 열어 둔 채 같은 DB에 연결 B를 추가하니 PID는 119였고, B의 SELECT * FROM temp_batch;는 42P01로 실패했다. 그 직후 A에서는 여전히 7을 읽었다. A가 커밋하지 않아 B에서 안 보인 것이 아니다. A의 생성과 삽입은 이미 커밋됐지만 임시 테이블은 세션마다 따로 사용한다.

A를 종료한 뒤에는 테이블 자체도 삭제된 것을 카탈로그에서 확인했다. 따라서 연결 풀에서 다른 연결을 다시 빌려 쓰는 흐름에 PRESERVE ROWS만 추가해서는 해결되지 않는다. 생성부터 사용까지 같은 물리 연결을 유지할 수 있는지 먼저 판단해야 한다. 여러 세션이 같은 결과를 읽어야 한다면 세션 전용 임시 테이블을 전제로 한 설계도 다시 검토한다.

이 글의 실행 범위는 직접 유지한 psql 연결 두 개와 별도의 단일 요청 비교다. JDBC·ORM·연결 풀이나 특정 SQL 편집기의 자동 커밋·연결 재사용 방식은 실행하지 않았다. 그런 환경에서는 이 결과를 특정 설정의 증거로 삼지 말고, 실제 연결 식별과 트랜잭션 경계를 확인하는 기준으로 사용한다.