• Greenplum 与 PostgreSQL 修改元数据(catalog)的方法 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

     

    转载自:https://yq.aliyun.com/articles/647438

  • 相关阅读:
    git使用(1) --git本地基础操作
    openCV在ubuntu上的使用(0)
    x86汇编指令整理
    读书笔记--鸟哥的linux_2
    读书笔记--鸟哥的linux_1
    #转 c语言中.h文件的作用
    读书笔记--《java语言程序设计--基础篇》
    matlab plot
    关于天文中的坐标系的介绍
    FIR的学习1
  • 原文地址:https://www.cnblogs.com/xibuhaohao/p/11943035.html
Copyright © 2020-2023  润新知