标签
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) 
