PostgreSQL 开启with-llvm(JIT)后，新增插件异常(clang: Command not found)处理 ...

淡淡的烟草味﹌ 2022-02-14 13:41 282阅读 0赞

### 标签 ###

PostgreSQL , llvm , clang , jit

--------------------

## 背景 ##

PostgreSQL 11版本开始引入了对JIT的支持，在OLAP类型的SQL有比较大的性能提升。

如果你使用的是YUM安装的PG，clang可能没有加入，在后期编译其他插件时可能遇到类似的报错：

比如pg\_hint\_plan插件

git clone https://github.com/ossc-db/pg_hint_plan
    cd pg_hint_plan
    
    pg_config在path中
    export PATH=$PGHOME/bin:$PATH
    
    USE_PGXS=1 make
    USE_PGXS=1 make install

报错如下

USE_PGXS=1 make  
    
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pg_hint_plan.o pg_hint_plan.c  
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -L/usr/pgsql-11/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags -Wl,--build-id  -shared -o pg_hint_plan.so pg_hint_plan.o  
    /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o pg_hint_plan.bc pg_hint_plan.c  
    make: /opt/rh/llvm-toolset-7/root/usr/bin/clang: Command not found  
    make: *** [pg_hint_plan.bc] Error 127

最简单的方法，可以关闭新增插件的bc编译项来解决

vi /usr/pgsql-11/lib/pgxs/src/makefiles/pgxs.mk  
      
    注释所有llvm相关即可  
    
    #ifeq ($(with_llvm), yes)
    #all: $(addsuffix .bc, $(MODULES)) $(patsubst %.o,%.bc, $(OBJS))
    #endif
    
    #ifeq ($(with_llvm), yes)
    #       $(foreach mod, $(MODULES), $(call install_llvm_module,$(mod),$(mod).bc))
    #endif # with_llvm
    
    #ifeq ($(with_llvm), yes)
    #       $(call install_llvm_module,$(MODULE_big),$(OBJS))
    #endif # with_llvm
    
    #ifeq ($(with_llvm), yes)
    #       $(foreach mod, $(MODULES), $(call uninstall_llvm_module,$(mod)))
    #endif # with_llvm
    
    #ifeq ($(with_llvm), yes)
    #       $(call uninstall_llvm_module,$(MODULE_big))
    #endif # with_llvm

恢复正常

确保pg_config在path中
    
    [root@pg11-test pgsql]# cat ../.bash_profile 
    [ -f /etc/profile ] && source /etc/profile
    PGDATA=/var/lib/pgsql/11/data
    export PGDATA
    # If you want to customize your settings,
    # Use the file below. This is not overridden
    # by the RPMS.
    [ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
    export PS1="$USER@`/bin/hostname -s`-> "    
    export PGPORT=1921    
    export PGDATA=/data01/pg11/pg_root$PGPORT    
    export LANG=en_US.utf8    
    export PGHOME=/usr/pgsql-11  
    export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH    
    export DATE=`date +"%Y%m%d%H%M"`  
    export PATH=$PGHOME/bin:$PATH:.    
    export MANPATH=$PGHOME/share/man:$MANPATH    
    export PGHOST=$PGDATA    
    export PGUSER=postgres    
    export PGDATABASE=postgres    
    alias rm='rm -i'    
    alias ll='ls -lh'    
    unalias vi  
    
    
    [root@pg11-test pgsql]# cd pg_hint_plan
    [root@pg11-test pg_hint_plan]# . ../.bash_profile 
    -bash: unalias: vi: not found
    root@pg11-test-> USE_PGXS=1 make
    make: Nothing to be done for `all'.
    root@pg11-test-> USE_PGXS=1 make clean
    rm -f pg_hint_plan.so pg_hint_plan.o  \
        pg_hint_plan.bc
    rm -rf sql/ut-fdw.sql expected/ut-fdw.out RPMS
    rm -rf results/ regression.diffs regression.out tmp_check/ tmp_check_iso/ log/ output_iso/
    root@pg11-test-> USE_PGXS=1 make 
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O3 -fPIC -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE   -c -o pg_hint_plan.o pg_hint_plan.c
    gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O3 -fPIC -L/usr/pgsql-11/lib   -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags -Wl,--build-id  -shared -o pg_hint_plan.so pg_hint_plan.o
    root@pg11-test-> USE_PGXS=1 make install
    /usr/bin/mkdir -p '/usr/pgsql-11/share/extension'
    /usr/bin/mkdir -p '/usr/pgsql-11/share/extension'
    /usr/bin/mkdir -p '/usr/pgsql-11/lib'
    /usr/bin/install -c -m 644 .//pg_hint_plan.control '/usr/pgsql-11/share/extension/'
    /usr/bin/install -c -m 644 .//pg_hint_plan--1.3.0.sql  '/usr/pgsql-11/share/extension/'
    /usr/bin/install -c -m 755  pg_hint_plan.so '/usr/pgsql-11/lib/'

配置

vi postgresql.conf
    shared_preload_libraries = 'pg_hint_plan' 
    
    
    pg_ctl restart -m fast

psql
    postgres=# create extension pg_hint_plan ;  
    CREATE EXTENSION  
    set client_min_messages ='notice';  
    set client_min_messages ='log';  
    set pg_hint_plan.debug_print =on;  
    set pg_hint_plan.enable_hint=on;  
    set pg_hint_plan.message_level =log;  
    set pg_hint_plan.parse_messages =log;  
    set pg_hint_plan.enable_hint_table =on;  
      
    postgres=# create table test(id int primary key, info text);  
    CREATE TABLE  
    postgres=# insert into test select generate_series(1,1000000), 'test';  
    INSERT 0 1000000  
      
    postgres=# explain /*+ seqscan(test) */ select count(*) from test;  
    LOG:  pg_hint_plan:  
    used hint:  
    SeqScan(test)  
    not used hint:  
    duplication hint:  
    error hint:  
      
                                 QUERY PLAN                               
    --------------------------------------------------------------------  
     Aggregate  (cost=17906.00..17906.01 rows=1 width=8)  
       ->  Seq Scan on test  (cost=0.00..15406.00 rows=1000000 width=0)  
    (2 rows)