PostgreSQL – ON CONFLICT UPDATE with view with columns子集

目前正在运行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);

使用基于当前用户名的视图实现行级安全性.为视图授予权限,为普通用户删除基础表.在v 9.5之后添加了security_barrier.请注意,用户看不到所有者名称.

CREATE VIEW foo_user WITH (security_barrier = True) AS
    SELECT id,num,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,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,data) VALUES (2,'frog');
ERROR:  duplicate key value violates unique constraint "foo_num_word_owner_idx"
DETAIL:  Key (num,ownername)=(2,qwer,admin) already exists.

完全期待.没有错.

在冲突中,第一次尝试

现在我们尝试让客户体验更顺畅：

INSERT INTO foo_user (num,'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,'frog')
    ON CONFLICT (num,ownername) DO UPDATE 
    SET data = 'frog'
    WHERE num = 2 AND word = 'qwer';

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

真正.视图中不存在所有者名称.我们不能从唯一索引中删除所有者名称,因为我们完全希望不同的所有者具有相同的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,'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破坏者,但仍然).

任何见解都非常感谢.

CREATE OR REPLACE FUNCTION trf_set_num_word() RETURNS trigger AS $$
BEGIN
    -- Check if (num,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();