使 Postgres 支持多版本客户端代码的策略

我即将着手对我们的代码进行全面检查以推送到 Postgres，并希望在继续之前征求一些反馈。

这里的设置是我们已经部署了很多与非 Postgres 数据库一起部署的软件，我们定期将行推送到中央 Postgres 数据库中。(RDS 11.4) 我们同时更新所有部署的代码副本的可能性为零。因此，我们总是会在该领域拥有多个活动版本……有时会有很多不同的版本。INSERT这本身没问题，但它确实使将天真的语句烘焙到客户端代码中变得难以管理。我已经做到了。它只是轻微地咬了我们一下，但最终可能会变得非常痛苦。我们中央 Postgres 上的以下任何和所有 DDL 更改都可能会中断来自旧版本已部署软件的推送：

• 删除字段
• 重命名字段
• 重新输入字段
• 添加NOT NULL DEFAULT NULL字段

显而易见的是，我必须将硬编码/固定引用从客户端代码中移出并移到其他代码中。大多数人可能在他们的收集器/现场应用程序和 Postgres 之间有某种带有 ORM 等的堆栈。我们不。因此，在 SO 上的人们的大力帮助下，我使用“hsys”作为示例表构建了以下解决方案。

1. 为每个表版本创建一个自定义类型，如 hsys_v1。

   CREATE TYPE api.hsys_v1 AS (
       id uuid,
       name_ citext,
       marked_for_deletion boolean);
   

2. 对于每个表和版本，编写一个接受自定义类型数组的 INSERT 处理函数。

   CREATE OR REPLACE FUNCTION ascendco.insert_hsys_v1 (data_in api.hsys_v1[])
     RETURNS int
   AS $BODY$
   
   -- The CTE below is a roundabout way of returning an insertion count from a pure SQL function in Postgres.
   with inserted_rows as (
           INSERT INTO hsys (
               id,
               name_,
               marked_for_deletion)
   
           SELECT
               rows_in.id,
               rows_in.name_,
               rows_in.marked_for_deletion
   
           FROM unnest(data_in) as rows_in
   
           ON CONFLICT(id) DO UPDATE SET
               name_ = EXCLUDED.name_,
               marked_for_deletion = EXCLUDED.marked_for_deletion
   
           returning 1 as row_counter)
   
       select sum(row_counter)::integer from inserted_rows;
   
   $BODY$
   LANGUAGE sql;
   

   这里的想法是，当我更改表时，我将能够创建一个hsys_v2类型和一个insert_hsys_v2 (hsys_v2[])函数来匹配。然后老客户可以继续以旧格式推送，只要我重写insert_hsys_v1以将内容映射/转换/强制转换为新表格格式。

几周前我写了一个 GUI 来抓取我的表定义并在上面放了一个代码生成器。然后我停了下来。我意识到我想知道我是否遗漏了一些我应该考虑的东西，并且希望有人指出这个策略中的一个漏洞。我并没有懒于联系当地的程序员……没有。（我在澳大利亚农村。）

如果这个策略没有问题，我会进行大修。作为奖励，到目前为止的工作使得为自定义转换和视图添加代码构建器变得容易。不确定它们是否有用，但我同时生成它们。