PostgreSQL 안티패턴: 집합 및 선택을 SQL에 전달

때때로 개발자에게 필요한 매개변수 집합 또는 전체 선택을 요청에 전달 "입구에서". 때때로 이 문제에 대한 매우 이상한 해결책이 있습니다.

"반대에서" 가서 어떻게 하면 안되는지, 왜, 어떻게 하면 더 잘할 수 있는지 알아봅시다.

요청 본문에 값을 직접 "삽입"

일반적으로 다음과 같이 보입니다.

query = "SELECT * FROM tbl WHERE id = " + value

... 또는 이와 같이:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

이 방법에 대해 말하고, 쓰고, 심지어 그려 충분한:

거의 항상 그렇습니다 SQL 삽입에 대한 직접 경로 쿼리 문자열을 강제로 "접착"해야 하는 비즈니스 로직에 대한 추가 부하.

이 접근 방식은 필요한 경우에만 부분적으로 정당화될 수 있습니다. 파티셔닝 사용 보다 효율적인 계획을 위해 PostgreSQL 버전 10 이하에서. 이 버전에서 스캔 섹션 목록은 전송된 매개변수를 고려하지 않고 요청 본문을 기준으로만 결정됩니다.

$n 인수

사용 자리 표시자 매개 변수가 좋습니다. 사용할 수 있습니다. 준비된 진술, 비즈니스 로직(쿼리 문자열이 한 번만 구성되고 전송됨)과 데이터베이스 서버(요청의 각 인스턴스에 대해 재구문 분석 및 계획이 필요하지 않음) 모두에서 부하를 줄입니다.

인수의 가변 개수

알 수 없는 수의 인수를 미리 전달하려고 할 때 문제가 발생할 것입니다.

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

이 양식에 요청을 남기면 잠재적 인 주입으로부터 우리를 구할 수 있지만 여전히 요청을 붙이거나 파싱해야 할 필요가 있습니다. 인수 수에서 각 옵션에 대해. 매번하는 것보다 이미 낫지 만 그것 없이도 할 수 있습니다.

다음을 포함하는 하나의 매개변수만 전달하면 충분합니다. 배열의 직렬화된 표현:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

유일한 차이점은 인수를 원하는 배열 유형으로 명시적으로 변환해야 한다는 것입니다. 그러나 이것은 우리가 어디로 가고 있는지 미리 알고 있기 때문에 문제를 일으키지 않습니다.

샘플 전송(매트릭스)

일반적으로 이들은 "한 번의 요청으로" 데이터베이스에 삽입하기 위해 데이터 세트를 전송하기 위한 모든 종류의 옵션입니다.

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

위에서 설명한 요청의 "재접착" 문제 외에도 이로 인해 다음과 같은 문제가 발생할 수 있습니다. 메모리 부족 그리고 서버 충돌. 이유는 간단합니다. PG는 인수에 대한 추가 메모리를 예약하고 세트의 레코드 수는 비즈니스 로직 애플리케이션 위시리스트에 의해서만 제한됩니다. 특히 임상 사례에서 볼 필요가 있었습니다. $9000보다 큰 "번호가 매겨진" 인수 - 이렇게 하지 마세요.

이미 적용하여 쿼리를 다시 작성해 보겠습니다. "XNUMX단계" 직렬화:

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

예, 배열 내부의 "복잡한" 값의 경우 따옴표로 묶어야 합니다.
이러한 방식으로 임의의 수의 필드로 선택 항목을 "확장"할 수 있음이 분명합니다.

언네스트, 언네스트, …

때때로 내가 언급한 "배열의 배열" 대신 여러 "열의 배열"을 전달하는 옵션이 있습니다. 지난 기사에서:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

이 방법을 사용하면 서로 다른 열에 대한 값 목록을 생성할 때 실수를 하더라도 완전히 예상치 못한 결과, 또한 서버 버전에 따라 다릅니다.

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

버전 9.3부터 PostgreSQL에는 json 유형 작업을 위한 완전한 기능이 있습니다. 따라서 입력 매개변수가 브라우저에 정의되어 있으면 바로 거기에서 양식을 작성할 수 있습니다. SQL 쿼리용 json 객체:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

이전 버전의 경우에도 동일한 방법을 사용할 수 있습니다. 각각(hstore), 그러나 hstore에서 복잡한 객체를 이스케이프 처리하여 올바른 "접기"를 수행하면 문제가 발생할 수 있습니다.

json_populate_recordset

"입력" json 배열의 데이터가 일부 테이블을 채울 것이라는 것을 미리 알고 있는 경우 "역참조" 필드와 json_populate_recordset 함수를 사용하여 원하는 유형으로 캐스팅하는 데 많은 비용을 절약할 수 있습니다.

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

그리고 이 함수는 테이블 형식에 의존하지 않고 단순히 전달된 객체 배열을 선택 항목으로 "확장"합니다.

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

임시 테이블

그러나 전송된 샘플의 데이터 양이 매우 큰 경우 이를 하나의 직렬화된 매개변수에 던지는 것은 어렵고 때로는 불가능합니다. 대용량 메모리 할당. 예를 들어 외부 시스템에서 오랜 시간 동안 대규모 이벤트 데이터 배치를 수집한 다음 데이터베이스 측에서 일회성으로 처리하려고 합니다.

이 경우 가장 좋은 솔루션은 다음을 사용하는 것입니다. 임시 테이블:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

방법이 좋다 대용량의 간헐적인 전송을 위해 데이터.
데이터 구조를 설명하는 관점에서 볼 때 임시 테이블은 "일반" 테이블과 한 가지 기능만 다릅니다. pg_class 시스템 테이블에서, 및 c pg_type, pg_depend, pg_attribute, pg_attrdef, ... — 그리고 전혀.

따라서 각각에 대해 단기 연결이 많은 웹 시스템에서 이러한 테이블은 매번 새로운 시스템 레코드를 생성하며 데이터베이스 연결이 닫힐 때 삭제됩니다. 결국, TEMP TABLE을 통제하지 않고 사용하면 pg_catalog의 테이블이 "팽창"합니다. 이를 사용하는 많은 작업 속도가 느려집니다.
물론 이것은 다음과 같이 싸울 수 있습니다. 주기적 패스 VACUUM FULL 시스템 카탈로그 테이블에 따라.

세션 변수

이전 사례의 데이터 처리가 단일 SQL 쿼리에 대해 상당히 복잡하지만 자주 수행하려고 한다고 가정합니다. 즉, 절차적 처리를 사용하고 싶습니다. DO 블록, 하지만 임시 테이블을 통한 데이터 전송을 사용하면 비용이 너무 많이 듭니다.

또한 $n 매개변수를 사용하여 익명 블록으로 전달할 수 없습니다. 세션 변수와 함수는 우리가 상황에서 벗어나도록 도와줄 것입니다. 현재 설정.

버전 9.2 이전에는 미리 구성해야 했습니다. 특수 네임스페이스 custom_variable_classes "자신의" 세션 변수에 대해. 현재 버전에서는 다음과 같이 작성할 수 있습니다.

SET my.val = '{1,2,3}';
DO $$
DECLARE
  id integer;
BEGIN
  FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
    RAISE NOTICE 'id : %', id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

지원되는 다른 절차적 언어로 사용할 수 있는 다른 솔루션이 있습니다.

더 많은 방법을 알고 계십니까? 의견에 공유하십시오!

출처 : habr.com