标签

PostgreSQL , llvm , clang , jit

背景

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

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

比如pg_hint_plan插件

git clone https://github.com/ossc-db/pg_hint_plancd pg_hint_planpg_config在path中export PATH=$PGHOME/bin:$PATHUSE_PGXS=1 makeUSE_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/profilePGDATA=/var/lib/pgsql/11/dataexport 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_profileexport 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 foundroot@pg11-test-> USE_PGXS=1 makemake: Nothing to be done for `all'.root@pg11-test-> USE_PGXS=1 make cleanrm -f pg_hint_plan.so pg_hint_plan.o  \    pg_hint_plan.bcrm -rf sql/ut-fdw.sql expected/ut-fdw.out RPMSrm -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.cgcc -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.oroot@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.confshared_preload_libraries = 'pg_hint_plan' pg_ctl restart -m fast

psqlpostgres=# 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)