标签

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)