arrays - 改进基于输入数组的 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 开发一个注入(inject)安全且性能良好的系统。我将替换一个更简单的多值插入，其中 INSERT 完全在客户端组成。意思是，我不能确定在连接文本时我没有引入缺陷。 (我在这里问过这个问题:Postgres bulk insert/update that's injection-safe. Perhaps a function that takes an array?)

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

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/

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

我仍然可以使用一些补救帮助来解决一些问题:

• 上面的代码是注入(inject)安全的吗，还是我需要在 PL/pgSQL 中重写它以使用 FOREACH 和 EXECUTE...USING 或 FORMAT 或 quote_literal 等？

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

• 将其实现为过程而不是函数似乎更有意义，这样我就可以在函数内处理事务。我是否基于此？

• 返回什么有任何风格(或其他)？我现在返回一个计数，这至少有点用。

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

谢谢!

Answer 1

我们有很多不同的服务器推送到 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，解压缩并从那里开始工作。但我不会在“更简单”下归档。

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

感谢您的帮助。