PostgreSQL - 在具有子列视图的情况下使用ON CONFLICT UPDATE

Question

PostgreSQL - 在具有子列视图的情况下使用ON CONFLICT UPDATE

postgresql

7

当前运行版本为9.5.3。当然会进行更新。

我有一个PostgreSQL数据库，其模式早于表行级安全性（即CREATE POLICY ...）。使用视图实现了行级安全性。在视图中通过选择仅具有所有者名称与CURRENT_USER匹配的行来完成安全性。

我正在尝试使用这样的视图构建upsert查询。问题出现在我尝试命名conflict_target时。

使用ON CONFLICT UPDATE ...的问题在于命名违反的约束条件。

玩具示例

CREATE TABLE foo (id serial, num int, word text, data text, ownername varchar(64));

对于每个用户，word 和 num 的组合必须是唯一的。

CREATE UNIQUE INDEX foo_num_word_owner_idx ON foo (num, word, ownername);

行级安全性是通过基于当前用户名的视图实现的。对于普通用户，对视图授予权限并从底层表中删除权限。 security_barrier 在 v9.5 之后添加。请注意，用户看不到 ownername。

CREATE VIEW foo_user WITH (security_barrier = True) AS
    SELECT id, num, word, data FROM foo 
    WHERE foo.ownername = CURRENT_USER;

现在自动设置所有者名称：

CREATE OR REPLACE FUNCTION trf_set_owner() RETURNS trigger AS
$$
BEGIN
    IF (TG_OP = 'INSERT') THEN
    NEW.ownername = CURRENT_USER::varchar(64);   
    END IF;
    IF (TG_OP = 'UPDATE') THEN
        NEW.ownername = CURRENT_USER::varchar(64);
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER foo_row_owner
    BEFORE INSERT OR UPDATE ON foo FOR EACH ROW
     EXECUTE PROCEDURE trf_set_owner();

请注意，视图中不会显示“ownername”列；行安全对用户而言是不可见的。

现在添加一些数据：

INSERT INTO foo_user (num, word, data) VALUES (1, 'asdf', 'cat'), (2, 'qwer', 'dog');


SELECT * FROM foo;
-- normally, this would give an error related to privileges,
-- because we don't allow users to query the underlying table.
-- bypassed here for demo purposes.

 id | num | word | data | ownername
----+-----+------+------+-----------
  1 |   1 | asdf | cat  | admin
  2 |   2 | qwer | dog  | admin
(2 rows)


SELECT * FROM foo_user;

 id | num | word | data
----+-----+------+------
  1 |   1 | asdf | cat
  2 |   2 | qwer | dog
(2 rows)

至此一切都很好。

我尝试过的方法

如上所述，对于每个用户，num和word必须是唯一的。不同所有者拥有相同的num和word并没有问题（实际上，我们期望它）。

我正在尝试利用INSERT中的ON CONFLICT子句来创建一些后端的UPSERT-ish功能。结果出现了问题。

错误的简单示例

首先，一个简单的插入失败：

INSERT INTO foo_user (num, word, data) VALUES (2, 'qwer', 'frog');
ERROR:  duplicate key value violates unique constraint "foo_num_word_owner_idx"
DETAIL:  Key (num, word, ownername)=(2, qwer, admin) already exists.

完全符合预期。这没有任何问题。

关于冲突，第一次尝试

现在我们尝试让客户端的体验更加流畅:

INSERT INTO foo_user (num, word, data) VALUES (2, 'qwer', 'frog')
    ON CONFLICT DO UPDATE 
    SET data = 'frog'
    WHERE num = 2 AND word = 'qwer';

ERROR:  ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 2:     ON CONFLICT DO UPDATE
            ^
HINT:  For example, ON CONFLICT (column_name).

就像文档所说的那样。它需要知道哪个规则被违反了。没问题：

在冲突时，第二次尝试

INSERT INTO foo_user (num, word, data) VALUES (2, 'qwer', 'frog')
    ON CONFLICT (num, word, ownername) DO UPDATE 
    SET data = 'frog'
    WHERE num = 2 AND word = 'qwer';

ERROR:  column "ownername" does not exist
LINE 2:     ON CONFLICT (num, word, ownername) DO UPDATE

是的。Ownername在视图中不存在。我们不能从唯一索引中删除ownername，因为我们完全希望不同的所有者具有相同的num和word值。

ON CONFLICT，第三次尝试

因此，我尝试将索引转换为约束，并命名该约束：

ALTER TABLE foo 
    ADD CONSTRAINT foo_num_word_owner_crt UNIQUE 
    USING INDEX foo_num_word_owner_idx;

NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index 
"foo_num_word_owner_idx" to "foo_num_word_owner_crt"

好的，现在来测试一下：

INSERT INTO foo_user (num, word, data) VALUES (2, 'qwer', 'frog')
    ON CONFLICT ON CONSTRAINT foo_num_word_owner_crt DO UPDATE 
    SET data = 'frog'
    WHERE num = 2 AND word = 'qwer';

ERROR:  constraint "foo_num_word_owner_crt" for table "foo_user" does not exist

很有道理：我们在查询视图时指定了表约束。

结论

现在我没有更多的想法了。我们如何让 ON CONFLICT 与这样的视图友好地协作？或者这不可能吗？

我距离建议我们从视图转移到具有行级安全性的表（握起大拇指和食指）只有一步之遥，但那是相当大量的工作（不一定是API断路器，但仍然很多）。

非常感谢任何见解。

- Jason

1

此时，你的方法永远不会奏效。使用 security_barrier，视图被视为目录表（就像 CHECK OPTION 一样），而 ON CONFLICT 在这些关系上永远不起作用（至少目前是这样）。如果你移除 security_barrier，你将遇到问题，约束既不在视图上，也无法推断出来 -- 这也是你的结论。 - pozs

1

但是，也许还有一种解决方案：PostgreSQL 中的视图使用规则系统。从技术上讲，可以重写对视图的普通 INSERT 查询，以在表上执行 INSERT ... ON CONFLICT ... 查询。或者，您也可以编写 INSTEAD OF 触发器。我必须承认，它们提供了一种更具体的解决方案：您只能编写一次 conflict_action。 - pozs

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Patrick · Answer 1

您可以通过删除ON CONFLICT子句并使用一个INSTEAD OF触发器来规避此问题，该触发器手动测试任何索引冲突：

CREATE OR REPLACE FUNCTION trf_set_num_word() RETURNS trigger AS $$
BEGIN
    -- Check if (num, word, ownername) exists by trying an UPDATE
    UPDATE foo SET data = 'frog'
    WHERE num = NEW.num AND word = NEW.word AND ownername = CURRENT_USER::varchar(64);   
    IF FOUND THEN
        RETURN NULL; -- If so, don't INSERT/UPDATE
    END IF;
    RETURN NEW; -- If not, do the INSERT
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER foo_user_num_word
    INSTEAD OF INSERT OR UPDATE ON foo_user FOR EACH ROW
    EXECUTE PROCEDURE trf_set_num_word();