Go로 PostgreSQL 프록시 만들기 (9) - Transaction Pooling

Session-Level (기존):
Client A ─── Conn 1 ─── (연결 끊길 때까지 점유)
Client B ─── Conn 2
Client C ─── Conn 3

Transaction-Level (목표):
Client A ─┐
           ├── Conn 1 ─── (쿼리/트랜잭션 끝나면 반환)
Client B ─┤
           ├── Conn 2
Client C ─┘

1. 쿼리 도착 → Pool.Acquire()
2. 백엔드에 쿼리 전송 + 응답 릴레이
3. DISCARD ALL 전송 (상태 초기화)
4. Pool.Release()

func (s *Server) acquireWriterConn(ctx context.Context, bound *pool.Conn) (*pool.Conn, bool, error) {
    if bound != nil {
        return bound, false, nil  // 트랜잭션 중이면 기존 커넥션 재사용
    }
    conn, err := s.writerPool.Acquire(ctx)
    if err != nil {
        return nil, false, fmt.Errorf("acquire writer: %w", err)
    }
    return conn, true, nil
}

func (s *Server) resetAndReleaseWriter(conn *pool.Conn) {
    if err := s.resetConn(conn); err != nil {
        slog.Warn("reset writer conn failed, discarding", "error", err)
        s.writerPool.Discard(conn)
        return
    }
    s.writerPool.Release(conn)
}

DISCARD ALL;
-- 효과: SET 변수 초기화, TEMP 테이블 삭제, PREPARED STATEMENT 해제, LISTEN 해제 등

// Transaction lifecycle management
switch {
case !wasInTx && nowInTx:
    // BEGIN — bind writer for transaction duration
    boundWriter = wConn
case wasInTx && !nowInTx:
    // COMMIT/ROLLBACK — unbind and release
    boundWriter = nil
    s.resetAndReleaseWriter(wConn)
case acquired:
    // Single statement outside transaction — release immediately
    s.resetAndReleaseWriter(wConn)
}

case protocol.MsgParse:
    stmtName, query := protocol.ParseParseMessage(msg.Payload)
    route := session.RegisterStatement(stmtName, query)

    upper := strings.ToUpper(strings.TrimSpace(query))
    if strings.HasPrefix(upper, "BEGIN") || strings.HasPrefix(upper, "START TRANSACTION") {
        extTxStart = true
    }
    if strings.HasPrefix(upper, "COMMIT") || strings.HasPrefix(upper, "ROLLBACK") {
        extTxEnd = true
    }

case protocol.MsgSync:
    // ... 배치 실행 후 ...
    switch {
    case extTxStart && !extTxEnd:
        boundWriter = wConn    // BEGIN — bind
    case extTxEnd:
        boundWriter = nil      // COMMIT — unbind
        s.resetAndReleaseWriter(wConn)
    case acquired:
        s.resetAndReleaseWriter(wConn)  // 단일 배치 — release
    }