[Postgresql] Psycopg2란? 트랜잭션 ID 추출방법은?

🚀  들어가며...

• Python에서 PostgreSQL을 사용하기 위해서는 psycopg2 라이브러리를 사용합니다. 오늘은 psycopg2 라이브러리의 간단한 사용법과 트랜잭션 ID 추출방법에 대해 알아보겠습니다.

 

📑 내용

Psycopg2란?

Python에서 PostgreSQL을 사용하기 위해서는 psycopg2 라이브러리를 사용합니다.

해당 라이브러리의 공식 사이트와 GitHub은 다음과 같습니다.

• https://www.psycopg.org/docs/
• https://github.com/psycopg/psycopg2/

JDBC와 같은 Database 커넥터이기 때문에 다음과 같은 기능을 제공합니다.

• Database 연결과 해제
• SQL 실행과 트랜잭션 발생
• 결과 반환

Psycopg2은 Pyhton에서 사용되는 PostgreSQL DB 어댑터로, Python DB API 2.0 사양을 완전히 구현했습니다.

libpq 위에서 동작하며, 멀티스레드 프로그램에서 스레드 안정성을 가지고 효율적인 자원 관리가 가능합니다.

Psycopg2는 PyPI로 설치 가능합니다.

 

설치가 끝나면 connect 함수를 이용해 DB에 연결합니다.

import psycopg2

conn = psycopg2.connect(dbname='db', user='user', password='pw1234', host='localhost', port=5432)
cur = conn.cursor()

cur.close()
conn.close()

host의 기본값은 UNIX 소켓이며, port의 기본값은 5432입니다.

Python의 with 문법을 이용하면, close 함수 호출을 생략할 수 있습니다.

with psycopg2.connect(dbname='db', user='user', password='pw1234', host='localhost', port=5432) as conn:
    with conn.cursor() as cur:
        # ...

execute 함수를 이용해서 SQL을 실행하고, fetch류의 함수를 이용해 결과를 가져올 수 있습니다.

• fetchone
• fetchmany
• fetchall

SQL을 이용해 DB를 수정한 이후에는 commit 함수를 호출해 수정사항을 저장해야 합니다.

conn.commit()

만약 with문을 이용해서 연결을 관리하고 있다면, with가 정상적으로 종료되었을 때 자동으로 commit을 호출합니다.

rollback 함수는 반대로 수정사항을 되돌리는 함수입니다.

마찬가지로 with를 사용하는 중이라면, with에서 오류가 발생했을 때는 자동으로 rollback 함수를 호출합니다.

 

DB 트랜잭션 ID는 어떻게 추출해야 할까?

저는 pg_stat_activity에 backend_xid값을 select하고 where문에 pid를 조건으로 걸어줌으로써 트랜잭션 ID를 추출하였습니다.

cur = conn.cursor()
cur.execute("select backend_xid from pg_stat_activity where pid = {};".format(self.connection.info.backend_pid))

 

pg_stat_activity 테이블명세서

열	유형	설명
datid	oid	이 백엔드가 연결된 데이터베이스의 OID
datname	name	이 백엔드가 연결된 데이터베이스의 이름
pid	integer	이 백엔드의 프로세스 ID
usesysid	oid	이 백엔드에 로그인한 사용자의 OID
usename	name	이 백엔드에 로그인한 사용자의 이름
application_name	text	이 백엔드에 연결된 애플리케이션의 이름
client_addr	inet	이 백엔드에 연결된 클라이언트의 IP 주소입니다. 이 필드가 null이면 클라이언트가 서버 시스템의 Unix 소켓을 통해 연결되었거나 이것이 autovacuum과 같은 내부 프로세스임을 나타냅니다.
client_hostname	text	client_addr의 역 DNS 조회에 의해 보고된 연결된 클라이언트의 호스트 이름. 이 필드는 IP 연결의 경우에만 null이 아니며 log_hostname이 활성화된 경우에만 됩니다.
client_port	integer	클라이언트가 이 백엔드와의 통신에 사용하는 TCP 포트 번호 또는 Unix 소켓이 사용되는 경우 -1입니다. 이 필드가 null이면 내부 서버 프로세스임을 나타냅니다.
backend_start	timestamp	이 프로세스가 시작된 시간입니다. 클라이언트 백엔드의 경우 클라이언트가 서버에 연결된 시간입니다.
xact_start	timestamp	이 프로세스의 현재 트랜잭션이 시작된 시간 또는 활성 트랜잭션이 없으면 null입니다. 현재 쿼리가 트랜잭션의 첫 번째인 경우 이 열은 query_start열과 같습니다.
query_start	timestamp	현재 활성 쿼리가 시작된 시간 또는 상태가 활성이 아닌 경우 마지막 쿼리가 시작된 시간
state_change	timestamp	상태가 마지막으로 변경된 시간
wait_event_type	text	백엔드가 대기 중인 이벤트 유형(있는 경우). 그렇지 않으면 NULL입니다
wait_event	text	백엔드가 현재 대기 중이면 대기 이벤트 이름, 그렇지 않으면 NULL입니다
state	text	이 백엔드의 현재 전체 상태입니다. 가능한 값은 다음과 같습니다. active: 백엔드가 쿼리를 실행 중입니다. idle: 백엔드가 새 클라이언트 명령을 기다리고 있습니다. idle in transaction: 백엔드가 트랜잭션에 있지만 현재 쿼리를 실행하고 있지 않습니다. idle in transaction (aborted): 이 상태는 트랜잭션의 명령문 중 하나가 오류를 일으킨 점을 제외하고는 idle in transaction과 유사합니다. fastpath function call: 백엔드가 빠른 경로 기능을 실행하고 있습니다. disabled: 이 백엔드에서 track_activities 가 비활성화 된 경우 이 상태가 보고됩니다.
backend_xid	xid	이 백엔드의 최상위 트랜잭션 식별자입니다(있는 경우).
backend_xmin	xid	현재 백엔드의 xmin 지평선입니다.
query	text	이 백엔드의 가장 최근 쿼리 텍스트입니다. 상태가 활성인 경우 이 필드는 현재 실행 중인 쿼리를 표시합니다. 다른 모든 상태에서는 마지막으로 실행된 쿼리가 표시됩니다. 기본적으로 쿼리 텍스트는 1024바이트에서 잘립니다. 이 값은 track_activity_query_size 매개변수를 통해 변경할 수 있습니다.

 

🙋🏻‍♂️ 후기

사내에서 트랜잭션 ID를 추출하여 작업을 해야하는 상황이 발생하여 구글링을 통해 알아본 결과 pg_stat_activity라는 테이블에서 제공해주는 것을 알 수 있었고, 적절히 파라메터 값만 설정해주면 제가 원하는 작업처리를 할 수 있었습니다. postgresql의 더 많은 라이브러리를 사용해보아야 겠습니다.

 

🔗  참고한 글

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

28.2. The Statistics Collector