プロホスト > ブログ > 行政 > PostgreSQL のアンチパターン: SQL にセットと選択を渡す

PostgreSQL のアンチパターン: SQL にセットと選択を渡す

開発者は時々必要とする パラメータのセット、または選択範囲全体をリクエストに渡します 「入り口で」。 場合によっては、この問題に対する非常に奇妙な解決策が存在することがあります。
PostgreSQL のアンチパターン: SQL にセットと選択を渡す
「逆から」見て、それをしない方法、その理由、そしてより良く行う方法を見てみましょう。

リクエストボディへの値の直接「挿入」

通常は次のようになります。

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

...または次のようにします。

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

この方法については、こう言われ、書かれ、 描かれても 十分：

PostgreSQL のアンチパターン: SQL にセットと選択を渡す

ほとんどいつもそうです SQLインジェクションへのダイレクトパス ビジネス ロジックに余分な負荷がかかり、クエリ文字列を「接着」する必要があります。

このアプローチは、必要な場合にのみ部分的に正当化できます。 パーティショニングを使用する PostgreSQL バージョン 10 以下では、より効率的な計画が可能です。 これらのバージョンでは、スキャンされたセクションのリストは、送信されたパラメーターを考慮せずに、リクエスト本文にのみ基づいて決定されます。

$n 個の引数

使用 プレースホルダー パラメータは適切なので、使用できます 準備されたステートメントこれにより、ビジネス ロジック (クエリ文字列が形成され送信されるのは XNUMX 回だけ) とデータベース サーバー (リクエストのインスタンスごとに再解析と計画が必要ない) の両方の負荷が軽減されます。

引数の数は可変

未知の数の引数を事前に渡したい場合には、問題が待っています。

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

リクエストをこのフォームのままにしておくと、インジェクションの可能性は避けられますが、リクエストを接着/解析する必要が生じます。 引数の数からオプションごとに。 毎回行うよりはすでに良いですが、しなくても大丈夫です。

次のパラメータを XNUMX つだけ渡すだけで十分です。 配列のシリアル化表現:

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

唯一の違いは、引数を目的の配列型に明示的に変換する必要があることです。 しかし、どこに対処するのかが事前にわかっているため、これは問題を引き起こしません。

サンプル転送（マトリックス）

通常、これらは「XNUMX 回のリクエストで」データベースに挿入するデータ セットを転送するためのあらゆる種類のオプションです。

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

リクエストの「再接着」に関する上記の問題に加えて、これにより次のような問題が発生する可能性もあります。 メモリ不足 そしてサーバークラッシュ。 理由は簡単です。PG は引数用に追加のメモリを予約しており、セット内のレコード数はビジネス ロジック アプリケーションの Wishlist によってのみ制限されます。 特に臨床例では、次のことを確認する必要がありました。 $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

一時テーブル

しかし、送信されるサンプルのデータ量が非常に大きい場合、それを XNUMX つのシリアル化パラメータに投入することは困難であり、場合によっては不可能です。 大量のメモリ割り当て。 たとえば、長期間にわたって外部システムから大規模なバッチのイベント データを収集する必要があり、それをデータベース側で XNUMX 回だけ処理したいとします。

この場合、最善の解決策は次のように使用することです。 一時テーブル:

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

方法は良いです まれに大容量の送信を行う場合 データ。
データの構造を記述するという観点から見ると、一時テーブルと「通常の」テーブルの違いは XNUMX つだけです。 pg_class システムテーブル内とで pg_type、pg_depend、pg_attribute、pg_attrdef、... — そして何もありません。

したがって、それぞれの存続期間の短い接続が多数ある Web システムでは、このようなテーブルは毎回新しいシステム レコードを生成し、データベースへの接続が閉じられると削除されます。 最終的に、 TEMP TABLE を制御せずに使用すると、pg_catalog 内のテーブルの「膨張」が発生します そして、それらを使用する多くの操作が遅くなります。
もちろん、これに対抗することもできます 定期券 VACUUM FULL システムカタログ表によると。

セッション変数

前のケースのデータの処理は XNUMX つの SQL クエリでは非常に複雑ですが、それを頻繁に実行したいとします。 つまり、手続き型処理を使用したいのです。 DOブロックただし、一時テーブルを介したデータ転送を使用すると、コストがかかりすぎます。

また、$n-parameters を使用して匿名ブロックに渡すこともできません。 セッション変数と関数は、この状況から抜け出すのに役立ちます。 現在の設定.

バージョン 9.2 より前は、事前に設定する必要がありました 特別な名前空間 カスタム変数クラス 「彼らの」セッション変数の場合。 現在のバージョンでは、次のように記述できます。

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

コメントを追加します