PostgreSQL：只读表

问题描述

我的问题与此有关：

我找到了简单的解决方案，但是我不确定这样做是否安全。

所以：

  update pg_class set relkind ='m'其中relname ='< table_name>';; 
  

（当然，考虑到表模式应该更复杂）

但是在我的简单测试中却成功了：

 创建表t（i int） ;插入t值（1）; 
更新pg_class set relkind ='m'，其中relname ='t'; 
插入t值（1）; 
-错误：无法更改实例化视图 t 
 select * from t; 
-i 
--
-1 
-（1行）
  

所以，我的问题（目前仅是理论上的）是：此解决方案可能会出问题吗？

解决方案

是的，可能会出错。

Postgres永远不会通过DDL命令允许这种状态，现在它的行为基本上是不确定的。 / p>

一方面，每个物化视图都应具有关联的定义，因此 pg_dump 现在崩溃，抱怨是视图 t的定义似乎为空（长度为零）。

您的物化视图可能还具有列默认值，约束，触发器和许多其他内容，这些内容永远都不会通过DDL允许，这可能会导致一系列问题。

只读的表，设置适当的，o r拒绝中的任何更改。

My question is related to this one: how to lock a table for writing

I found the simple solution, but I am not sure is it safe for side-effects.

So:

update pg_class set relkind = 'm' where relname = '<table_name>';

(surely it should be more complex to take in account the table schema)

However in my simple tests it makes the trick:

create table t(i int); insert into t values(1);
update pg_class set relkind = 'm' where relname = 't';
insert into t values(1);
-- ERROR:  cannot change materialized view "t"
select * from t;
-- i 
-- ---
-- 1
-- (1 row)

So, my question (totally theoretical for now) is: Does something could to going wrong with this solution?

解决方案

Yes, things can go wrong.

Postgres would never allow this state through DDL commands, and its behaviour is now basically undefined.

For one thing, every materialised view is expected to have an associated definition, and so pg_dump now crashes, complaining that the definition of view "t" appears to be empty (length zero).

Your "materialised view" may also have column defaults, constraints, triggers, and many other things which would never be permitted via DDL, which might cause their own set of problems.

If you want to make a table read-only, set the appropriate permissions, or reject any changes in a trigger.

这篇关于PostgreSQL：只读表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！