PostgreSQL-与具有列子集的视图进行冲突更新

Question

目前正在运行9.5.3版本。当然，计划更新。

我有一个PostgreSQL数据库，其模式早于表行级安全性（即创建策略…）。行级安全性是使用视图实现的。在视图中，只选择所有者名称与当前用户匹配的行来实现安全性。

我正试图使用这样一个视图构建一个upsert查询。当我试图命名冲突\u目标时，问题就出现了。

使用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;    

现在自动设置ownername：

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值。

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

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

这是有道理的：我们正在查询视图，但指定了一个表约束。

现在我没有主意了。我们如何才能在冲突中获得，从而更好地处理这样的视图？还是不可能？

我很接近（举起大拇指和食指）建议我们从视图切换到具有行级安全性的表，但这是相当多的工作（不一定是API破坏者，但仍然如此）。

任何见解都非常感谢。




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();