改进基于输入阵列的UPSERT的功能

Question

我希望得到一些帮助，以改进作为数组传入的行的UPSERTing方法。我在Postgres 11.4上部署在RDS上。我有很多表格需要整理，但我从一个简单的表格开始进行实验：

BEGIN;
DROP TABLE IF EXISTS "data"."item" CASCADE;

CREATE TABLE IF NOT EXISTS "data"."item" (
    "id" uuid NOT NULL DEFAULT NULL,
    "marked_for_deletion" boolean NOT NULL DEFAULT false,
    "name_" citext NOT NULL DEFAULT NULL,

CONSTRAINT item_id_pkey
    PRIMARY KEY ("id")
);

CREATE INDEX item_marked_for_deletion_ix_bgin ON "data"."item" USING GIN("marked_for_deletion") WHERE marked_for_deletion = true;

ALTER TABLE "data"."item" OWNER TO "user_change_structure";
COMMIT;

到目前为止，该函数如下所示：

DROP FUNCTION IF EXISTS data.item_insert_array (item[]);

CREATE OR REPLACE FUNCTION data.item_insert_array (data_in item[]) 
  RETURNS int
AS $$
INSERT INTO item (
    id, 
    marked_for_deletion, 
    name_)

SELECT
    d.id, 
    d.marked_for_deletion,
    d.name_

FROM unnest(data_in) d

ON CONFLICT(id) DO UPDATE SET 
    marked_for_deletion = EXCLUDED.marked_for_deletion,
    name_ = EXCLUDED.name_;

SELECT cardinality(data_in); -- array_length() doesn't work. ¯\_(ツ)_/¯

$$ LANGUAGE sql;

ALTER FUNCTION data.item_insert_array(item[]) OWNER TO user_bender;

调用如下所示：

select * from item_insert_array(

    array[
        ('2f888809-2777-524b-abb7-13df413440f5',true,'Salad fork'),
        ('f2924dda-8e63-264b-be55-2f366d9c3caa',false,'Melon baller'),
        ('d9ecd18d-34fd-5548-90ea-0183a72de849',true,'Fondue fork')
        ]::item[]
    );

我正在尝试为UPSERT开发一个注入安全且性能良好的系统。我将替换一个更简单的多值插入，其中INSERT完全在客户端组成。这意味着，我不能确定我在连接文本时没有引入缺陷。（我在这里问过这个问题：Postgres批量插入/更新是注入安全的。也许是一个接受数组的函数？）

在各种优秀答案的帮助下，我已经走到了这一步：

https://dba.stackexchange.com/questions/224785/pass-array-of-mixed-type-into-stored-function

https://dba.stackexchange.com/questions/131505/use-array-of-composite-type-as-function-parameter-and-access-it

https://dba.stackexchange.com/questions/225176/how-to-pass-an-array-to-a-plpgsql-function-with-variadic-parameter/

我没有尝试所有这些的最复杂的版本，例如，我可以接受每个表有一个函数，每个数组元素有完全相同的格式。一旦我整理好基本模式，我将编写代码生成器来构建我需要的一切。所以，我不认为我需要可变的参数列表、多态元素或者一切打包成JSON的东西。(虽然我会时不时需要插入JSON，但那只是数据。)

对于一些问题，我仍然可以使用一些补救帮助：

>

上面的代码是注入安全的，还是我需要在PL/pgSQL中重写它，以便将FOREACH与EXECUTE… USING或FORMAT或quote_literal等一起使用？

我正在将输入数组设置为项[]。这很好，因为我正在传递这个小表的所有字段，但我并不总是想传递所有列。我想我可以使用anyarray作为函数中的类型，但我不知道在那种情况下如何传入数组。是否有一个素材类型的通用数组？我可以为每个函数创建自定义类型，但我宁愿不创建。主要是因为我只会在这种情况下使用类型。

似乎将其实现为过程而不是函数是有意义的，以便我可以在函数中处理事务。我是否偏离了基础？

关于要返回的内容是否有任何风格（或其他）？我现在返回一个计数，这至少有点有用。

我在这里有点过了，所以任何一般性评论都将不胜感激。为了清楚起见，我想要的是一个安全插入多行并具有良好性能的模式，理想情况下，不涉及每个函数或COPY的自定义类型。

谢谢！

Answer 1

我认为你的代码很好。回答你的问题:

> < li>

因为您没有使用动态SQL，所以SQL注入会自动保护您的安全。

使用anyarray很棘手，通常只适用于C函数。您可能能够让它工作，但会很复杂（例如，涉及元数据查询）。

您提供不完整行的想法需要解决一些困难：

• 如果跳过某些列，您将如何判断哪个数组元素属于哪个列？

如果您希望在冲突情况下只< code >更新某些列，您可以提供一个附加的< code>upd_cols varbit参数来说明应该更新哪些列。

第四列的更新如下所示：

SET col = CASE WHEN get_bit(upd_cols, 3) = 1
               THEN EXCLUDED.col
               ELSE item.col
          END

我认为在代码中使用事务控制没有任何好处。这将降低<罢工>作用过程，因为调用方可能希望调用您的函数，然后由于某些问题回滚操作。

我会RETURNvoid（或者使用一个过程），因为调用者已经知道数组有多大了。这就是普通DML语句所做的。

Answer 2

我们有很多不同的服务器在Postgres中推到中央表，这增加了另一个问题。如果我在我的表中添加一列呢？

ALTER TABLE item ADD COLUMN category citext;

现在表格有四列，而不是三列。

我所有现有的推送都会立即中断，因为现在输入中缺少一列。我们可以同时更新所有服务器的可能性为0%，因此这不是一个选择。

一种解决方案是为表的每个版本创建一个自定义类型：

CREATE TYPE item_v1 AS (
    id uuid,
    marked_for_deletion boolean,
    name_ citext);

CREATE TYPE item_v2 AS (
    id uuid,
    marked_for_deletion boolean,
    name_ citext,
    category citext);

然后是每种类型的函数:

CREATE OR REPLACE FUNCTION data.item_insert_array (data_in item_v1[]) 
etc.

CREATE OR REPLACE FUNCTION data.item_insert_array (data_in item_v2[]) 
etc.

我想你可以有一个巨大的方法，它接受一个数组并使用一个CASE来选择运行什么代码。有几个原因我不会那样做，但我想你可以。(我已经在不止一种语言中看到这种方法很快变得令人不快。)

所有这些似乎都是一项相当艰巨的工作。有没有我缺少的更简单的技巧？我设想您可以提交结构化文本/XML/JSON，将其解压缩并从中工作。但我不会把它归入“simpler”

显然，我仍然在这里进行设计。我已经编写了足够的代码来测试我所展示的内容，但是在返回并在数十个表上实现它之前，我想整理一下细节。

谢谢你的任何帮助。