[PostgreSQL] pq: current transaction is aborted, commands ignored until end of transaction block 에러 해결법

해당 오류는 트랜잭션 수행 중간에 쿼리가 오류를 생성되고 롤백을 실행하지 않고 다른 쿼리를 실행하려고 할 때 PostgreSQL이 발생시키는 에러입니다. 이 문제를 해결하려면 잘못된 쿼리가 실행되는 코드의 위치를 파악해야합니다. postgresql 서버에서 log_statement와 log_min_error_statement 옵션을 사용하면 도움이 됩니다. 또는 에러가 발생되는 해당 쿼리 부분의 데이터들을 전부 truncate 하면 해결되기도 합니다. 

문제점

트랜잭션 블록에서 쿼리 실행
열려있는 트랜잭션 블록에 잘못된 구문 또는 잘못된 쿼리가 실행되어 오류가 발생
오류로 인해 트랜잭션 블록이 유효하지 않아져 다음 트랜잭션부터 오류가 발생

이러한 문제가 발생되면 트랜잭션을 롤백하고 전체 데이터를 초기화 시키는 것 밖에 없습니다.

예시

gpadmin=# BEGIN; BEGIN gpadmin=# insert into Cant_Ignore values(1); INSERT 0 1 gpadmin=# insert into Cant_Ignore values(2); INSERT 0 1 gpadmin=# select * from Cant_Ignore; id ----   1   2 (2 rows) gpadmin=# insert into Cant_Ignore values(ERROR); ERROR:  column "error" does not exist LINE 1: insert into Cant_Ignore values(ERROR);                                        ^ gpadmin=# gpadmin=# insert into Cant_Ignore values(3); ERROR:  current transaction is aborted, commands ignored until end of transaction block gpadmin=# COMMIT; ROLLBACK gpadmin=# gpadmin=# select * from Cant_Ignore; id ---- (0 rows)

log_statement와 log_min_error_statement 세팅법

1. postgresql 서버의 postgresql.conf 접속

$ cd /etc/postgresql/9.5/main $ vi postgresql.conf   ... #------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ log_destination = 'stderr' #주석해제 # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: logging_collector = on #주석해제 # Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # (change requires restart) # These are only used if logging_collector is on: log_directory = 'pg_log' #주석해제 # directory where log files are written, # can be absolute or relative to PGDATA log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #주석해제 # log file name pattern, # can include strftime() escapes log_file_mode = 0600 #주석해제 # creation mode for log files,   ... log_min_error_statement = error # 주석해제 # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic (effectively off)   log_statement = 'mod' #주석 해제 후 none에서 mod로 변경 # none, ddl, mod, all

위와같이 postgresql.conf 파일을 수정한 다음 postgres 서버를 재실행합니다.

각 속성에 대해서는 https://www.postgresql.org/docs/current/static/runtime-config-logging.htm에서 확인할 수 있습니다.

다음 에러나는 (스크립트 또는 코드)를 실행하면 /etc/var/lib/postgres/9.5/main/pg_log 폴더에 로그가 쌓입니다. 해당 폴더로 접근한 다음 로그파일을 열어 어디서 에러가 났는지 확인한 다음, 수정하면 됩니다.

위의 에러가 났을 때, 전체 트랜잭션 롤백이 아닌 에러난 부분을 제외한 나머지를 commit하는 방법:

https://discuss.pivotal.io/hc/en-us/articles/205111468-HowTo-Overcome-Error-current-transaction-is-aborted-commands-ignored-until-end-of-transaction-block-