作者
digoal
日期
2018-06-24
标签
PostgreSQL , Greenplum , 元数据 , allow_system_table_mods
背景
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)
```
参考
https://www.postgresql.org/docs/10/static/runtime-config-developer.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
