主持人 > 博客 > 管理 > 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 个参数

使用 占位符 参数不错，可以让你用 准备好的报表，减少了业务逻辑（查询字符串只形成并传输一次）和数据库服务器（不需要为每个请求实例重新解析和规划）的负载。

可变数量的参数

当我们想提前传递未知数量的参数时，问题就在等着我们：

... 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 为参数保留额外的内存，并且集合中的记录数量仅受业务逻辑应用程序 Wishlist 的限制。 在特别的临床病例中，有必要看到 大于 $9000 的“编号​​”参数 - 不要这样做。

让我们重写查询，已经应用 “两级”连载:

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 系统表中而在 pg_type, pg_depend, pg_attribute, pg_attrdef, ... ——什么也没有。

因此，在每个都有大量短连接的web系统中，这样的表每次都会产生新的系统记录，当与数据库的连接关闭时，这些记录将被删除。 最终， 不受控制地使用 TEMP TABLE 导致 pg_catalog 中的表“膨胀” 并减慢许多使用它们的操作。
当然，这可以与 定期通过 VACUUM FULL 根据系统目录表。

会话变量

假设前一个案例中的数据处理对于单个 SQL 查询来说相当复杂，但您希望经常这样做。 也就是说，我们要在 做块, 但通过临时表使用数据传输将过于昂贵。

我们也不能使用 $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

添加评论