我们在PostgresQL中与REREFH COMPLETE ON COMMIT最接近的是什么？ [英] What's the closest we can get to `REFRESH COMPLETE ON COMMIT` in PostgresQL?

问题描述

因此，我们正在寻找一种方法来实施跨越多个表的约束。

我们遇到了此旧博客帖子，其中建议：

• 创建一个物化视图以选择违反所需约束的数据。必须使用提交时完成刷新定义MV，以便在事务结束之前对其进行更新。

  
  



• 在物化视图上创建检查约束，该约束始终评估为FALSE –例如检查（1 = 0）

  
  

就是这样。每当更新基础表时，都会刷新实例化视图。如果更新违反了规则，那么将在实例化视图中插入一行；但是对MV的检查约束不允许对其进行任何插入，因此事务失败。

尽管存在一些性能问号，但这个想法听起来很合理

但是，据我们所知，postgresql不支持提交时刷新之类的东西。

我们可以做的当然是在形成视图的表上安装触发器，从而触发对更新/删除/插入的刷新。

但不仅可能意味着必须为每个涉及的表执行刷新，那么在执行刷新时，我们可能也早就结束了交易。

也许我们可以做一些锁定的事情，但这又成为了足够快地锁定正确的事物的问题，这听起来像一个可怕的主意。

那么，我们可以做些什么吗？还是我们最好忘掉这个？ / p>

与提交前刷新最接近的是什么;行为？

解决方案

不要创建实例化视图，请手动滚动它。

作为一个示例，我们有两个表 a 和 b ，我们要确保这两个表中的行总和小于1000：

  BEGIN； 
 
 CREATE TABLE计数器（
 bigint NOT NULL，
 CHECK（总计<1000）
）; 
 
创建函数count_trig（）返回触发器
语言plpgsql AS 
 $$ BEGIN 
情况下
 TG_OP ='INSERT'然后
更新计数器SET总数=总数+ 1; 
返回新； 
当TG_OP ='DELETE'然后
更新计数器SET total = total-1; 
返还旧金； 
结束案例； 
 END; $$; 
 
在每个行执行过程中插入或删除
之后创建触发器count_trig 
 
在每个行执行过程中插入或删除b 
之后创建触发器count_trig 
 
插入计数器（总计）
值（（选择a）中的SELECT count（*）+ b中的select count（*） 
 
 COMMIT； 
  

So we're looking for a way to enforce constraints that span multiple tables.

We've come across this old blog post, which suggests:

• Create a materialized view to select data that violates the desired constraint. The MV must be defined with REFRESH COMPLETE ON COMMIT so that it is updated before the end of the transaction.

• Create a check constraint on the materialized view that always evaluates to FALSE – e.g. CHECK (1=0)

That’s it. Whenever the underlying tables are updated, the materialized view is refreshed. If the update violates the rule, then a row will be inserted into the materialized view; but the check constraint on the MV disallows any inserts into it, and so the transaction fails.

And although there are some performance questionmarks, the idea sounds reasonable enough.

However, postgresql does not -- to our knowledge -- support something like REFRESH ON COMMIT.

What we can do, of course, is install triggers on the tables that form the view that will trigger a refresh on update/delete/insert.

But not only would that potentially mean having to execute a refresh for each of the tables involved, we may also have long ended the transaction by the time the refresh is executed.

Maybe we could do something with locking, but then it becomes an issue of locking the right thing quick enough, which sounds like a terrible idea.

So is there something we can do or are we better off forgetting about this?

What's the closest we can get to "refresh before commit" behaviour?

解决方案

Don't create a materialized view, roll it by hand.

As an example, we have two tables a and b, and we want to make sure that the sum of the rows in these two tables is less than 1000:

BEGIN;

CREATE TABLE counter (
   total bigint NOT NULL,
   CHECK (total < 1000)
);

CREATE FUNCTION count_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   CASE
      WHEN TG_OP = 'INSERT' THEN
         UPDATE counter SET total = total + 1;
         RETURN NEW;
      WHEN TG_OP = 'DELETE' THEN
         UPDATE counter SET total = total - 1;
         RETURN OLD;
   END CASE;
END;$$;

CREATE TRIGGER count_trig AFTER INSERT OR DELETE ON a
   FOR EACH ROW EXECUTE PROCEDURE count_trig();

CREATE TRIGGER count_trig AFTER INSERT OR DELETE ON b
   FOR EACH ROW EXECUTE PROCEDURE count_trig();

INSERT INTO counter (total)
   VALUES ((SELECT count(*) FROM a) + (SELECT count(*) FROM b));

COMMIT;

这篇关于我们在PostgresQL中与REREFH COMPLETE ON COMMIT最接近的是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！