postgresql - 保证 upsert 为 `RETURNING`，同时限制存储的数据

Question

我有下表:

CREATE TABLE scoped_data(
  owner_id text,
  scope text
  key text,
  data json,
  PRIMARY KEY (owner_id, scope, key)
);

作为每个事务的一部分，我们将可能为多个范围插入数据。鉴于此表有可能快速增长，我不想存储 data如果是 NULL或一个空的 JSON 对象。

upsert 感觉像是对此的惯用方法。以下是在 PL/pgSQL 函数的上下文中:

WITH upserts AS (
  INSERT INTO scoped_data (owner_id, scope, key, data)
    VALUES
      (p_owner_id, 'broad', p_broad_key, p_broad_data),
      (p_owner_id, 'narrow', p_narrow_key, p_narrow_data),
      -- etc.
    ON CONFLICT (owner_id, scope, key)
      DO UPDATE SET data = scoped_data.data || COALESCE(EXCLUDED.data, '{}')
    RETURNING scope, data
)
SELECT json_object_agg(u.scope, u.data)
  FROM upserts u
  INTO v_all_scoped_data;

我包括 RETURNING因为我希望每个范围的数据的最新版本包含在一个变量中以供后续使用，因此我需要 RETURNING即使逻辑上没有更新数据，也能返回一些东西。

例如(全部为 key = 1 and scope = 'narrow' ):

1. data = '{}' => v_scoped_data = {} , key = 1 没有数据在scoped_data .
2. data = '{"some":"data"}' => v_scoped_data = { "narrow": { "some": "data" } } , 数据存在于 scoped_data .
3. data = '{}' => v_scoped_data = { "narrow": { "some": "data" } ，来自 2. 的数据不受影响。
4. data = '{"more":"stuff"}' => v_scoped_data = { "narrow": { "some": "data", "more": "stuff" } .更新表中存储的数据。

我最初添加了一个触发器 BEFORE INSERT ON scoped_data它做了以下事情:

IF NULLIF(NEW.data, '{}') IS NULL THEN
  RETURN NULL;
END IF;

RETURN NEW;

这可以很好地防止插入新 记录，但问题是此触发器还阻止了对现有 行的后续插入，因此没有 INSERT发生了所以没有ON CONFLICT因此 RETURNING 中没有返回任何内容.

我考虑过的几种方法，都感觉不雅或者觉得它们应该是不必要的:

• 添加 CHECK约束到 scoped_data.data : CHECK(NULLIF(data, '{}') IS NOT NULL) , 允许插入并捕获 PL/pgSQL 代码中的异常。
• DELETE在AFTER INSERT如果 data 触发字段是 NULL或为空。

我的处理方式是否正确？当有更好的方法时，我是否试图将这种逻辑强制转换为更新插入？可能显式 INSERT s 和 UPDATE这更合乎逻辑吗？

我正在使用 Postgres 9.6。

Answer 1

我会使用 BEFORE 触发器 ON INSERT 来防止不必要的插入和更新。

要在未执行操作的情况下也返回值，您可以UNION ALL 查询scoped_data 返回原始行，ORDER 结果，以便首先对任何新行进行排序(为两个查询引入一个人工列)并使用 LIMIT 1 获得正确的结果。