Greenplum 与 PostgreSQL 修改元数据(catalog)的方法 allow_system_table

本文详细介绍了在PostgreSQL和Greenplum数据库中修改元数据的方法。在PostgreSQL中，需要通过设置allow_system_table_mods参数并重启服务器来开启元数据修改权限；而在Greenplum中，可以通过直接设置allow_system_table_mods参数来实现。文章还提供了具体的命令示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

背景

PostgreSQL大量的信息保存在元数据中，所有的元数据都是内部维护的，例如建表、建索引、删表等操作，自动维护元数据。

在某些迫不得已的情况下才可能需要直接对元数据进行修改。

默认情况下，用户是允许修改元数据的。

postgres=# \set VERBOSITY verbose  
  
postgres=# delete from pg_class where relname='test';  
ERROR:  42809: permission denied: "pg_class" is a system catalog  
LOCATION:  setTargetTable, parse_clause.c:802

Greenplum 打开修改元数据开关的方法

postgres=# set allow_system_table_mods='DML';  
SET  
postgres=# begin;  
BEGIN  
postgres=# delete from pg_class where relname='test';  
DELETE 1  
postgres=# rollback;  
ROLLBACK

PostgreSQL 打开修改元数据开关的方法

1、与Greenplum不同

postgres=# set allow_system_table_mods='DML';  
ERROR:  parameter "allow_system_table_mods" cannot be changed without restarting the server

2、需要重启生效，配置为on, off。

postgres=# alter system set allow_system_table_mods=on;  
ALTER SYSTEM  
  
postgres=# \q  
digoal@  -> pg_ctl stop -m fast  
waiting for server to shut down....................... done  
server stopped  
  
digoal@  -> pg_ctl start  
waiting for server to start....2018-06-24 10:27:04.987 CST [4239] LOG:  00000: listening on IPv4 address "0.0.0.0", port 4000  
2018-06-24 10:27:04.987 CST [4239] LOCATION:  StreamServerPort, pqcomm.c:593  
2018-06-24 10:27:04.990 CST [4239] LOG:  00000: listening on Unix socket "/tmp/.s.PGSQL.4000"  
2018-06-24 10:27:04.990 CST [4239] LOCATION:  StreamServerPort, pqcomm.c:587  
2018-06-24 10:27:04.991 CST [4239] LOG:  00000: listening on Unix socket "./.s.PGSQL.4000"  
2018-06-24 10:27:04.991 CST [4239] LOCATION:  StreamServerPort, pqcomm.c:587  
  
..2018-06-24 10:27:06.980 CST [4239] LOG:  00000: redirecting log output to logging collector process  
2018-06-24 10:27:06.980 CST [4239] HINT:  Future log output will appear in directory "log".  
2018-06-24 10:27:06.980 CST [4239] LOCATION:  SysLogger_Start, syslogger.c:635  
 done  
server started  
  
  
digoal@  -> psql  
psql (11beta1)  
Type "help" for help.  
  
postgres=# show allow_system_table_mods;  
 allow_system_table_mods   
-------------------------  
 on  
(1 row)  
  
postgres=# begin;  
BEGIN  
postgres=# delete from pg_class where relname='test';  
DELETE 1  
postgres=# rollback;  
ROLLBACK  
postgres=# \d test  
                             Table "public.test"  
 Column |  Type  | Collation | Nullable |               Default                 
--------+--------+-----------+----------+-------------------------------------  
 id     | bigint |           |          |   
 c1     | text   |           |          | md5((random())::text)  
 c2     | text   |           |          | md5((random())::text)  
 c3     | text   |           |          | md5((random())::text)  
 c4     | text   |           |          | md5((random())::text)  
 c5     | text   |           |          | md5((random())::text)