安装环境
OS: Ubuntu 20.04.3
PostgreSQL 服务安装
# 防止版本问题先清理所有历史版本
$ sudo apt purge postgresql*
# 安装
$ sudo apt-get upgrade
$ sudo apt install postgresql postgresql-contrib postgresql-client postgresql-plpython3
# 配置 postgresql.conf 远程访问
$ diff /etc/postgresql/12/main/postgresql.conf
- # listen_addresses = 'localhost'
+ listen_addresses = '*'
# 配置 postgresql.conf 远程访问
$ diff /etc/postgresql/12/main/pg_hba.conf
- host all all 127.0.0.1/32 md5
+ host all all 0.0.0.0/0 md5
# 查看服务状态
$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql
$ sudo systemctl status postgresql
# 安装 plpython 插件
$ sudo -u postgres psql
# 创建插件 plpython3u
postgres=# CREATE EXTENSION plpython3u;
# 查询支持的插件列表
postgres=# select * from pg_language;
oid | lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
-------+------------+----------+---------+--------------+---------------+-----------+--------------+--------
12 | internal | 10 | f | f | 0 | 0 | 2246 |
13 | c | 10 | f | f | 0 | 0 | 2247 |
14 | sql | 10 | f | t | 0 | 0 | 2248 |
13455 | plpgsql | 10 | t | t | 13452 | 13453 | 13454 |
16388 | plpython3u | 10 | t | f | 16385 | 16386 | 16387 |
(5 rows)
# 测试 python 插件
postgres=# create function pymax (a integer, b integer)
returns integer
as $$
if a > b:
return a
return b
$$ language plpython3u;
# 执行查询操作
postgres=# select pymax(1,2);
# 测试依赖库
postgres=# create or replace function to_tsv(i_text text) returns tsvector as $$
import jieba
seg_list = jieba.cut(i_text, cut_all=True);
return("".join(seg_list))
$$ language plpython3u;
# 执行查询操作
postgres=# select to_tsv('我爱北京天安门');
创建用户
# 创建用户 dev
$ sudo -u postgres createuser --superuser dev
# 配置 dev 用户密码
# 进入
$ sudo -u postgres psql
\password dev
\q
# 创建 ctest 数据库
$ sudo -u postgres createdb ctest --owner=dev
# 登录数据库
$ psql -U dev -d ctest -h 127.0.0.1 -p 5432
Password for user dev:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1), server 10.14 (Ubuntu 10.14-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
ctest=#
pljava 安装
- 同步
pljava代码并编译
# 同步 pljava 代码
git clone https://github.com/tada/pljava
# 切换到 V1_5_0(当前 java 版本为 1.8), 1.6.x 版本都是 10 以上的 jdk 版本
git checkout V1_5_0
# 编译执行
mvn clean install
# 查看 pg 配置目录
pg_config
PKGLIBDIR = /usr/lib/postgresql/12/lib
SHAREDIR = /usr/share/postgresql/12
# 安装 pljava.so 库到 PKGLIBDIR 目录下
sudo cp ./pljava-so/target/nar/pljava-so-1.5.0-SNAPSHOT-i386-Linux-gpp-shared/lib/i386-Linux-gpp/shared/libpljava-so-1.5.0-SNAPSHOT.so \
/usr/lib/postgresql/12/lib/pljava.so
# 安装 pljava.jar 库到 SHAREDIR/extension 目录下
sudo cp ./pljava/target/pljava-1.5.0-SNAPSHOT.jar /usr/share/postgresql/12/extension/pljava--1.5.0.jar
sudo cp ./src/sql/install.sql /usr/share/postgresql/12/extension/pljava--1.5.0.sql
# 配置 java 环境, 在 pljava--1.5.0.sql 中添加如下两行
SET PLJAVA.CLASSPATH='/usr/share/postgresql/9.4/extension/pljava--1.4.4.jar';
SET PLJAVA.VMOPTIONS='-Xms64M -Xmx128M';
# 添加 `pljava.control` 文件
$ cat /usr/share/postgresql/12/extension/pljava.control
# pljava extension
comment = 'PL/Java bundled as an extension'
default_version = '1.5.0'
relocatable = false
- 配置
pljava插件
# 创建插件
CREATE EXTENSION pljava;
# 查看支持的 java 语言内容
SELECT * FROM pg_language WHERE lanname LIKE 'java%';
# 删除插件
DROP EXTENSION pljava;
pg_jieba 安装
- 编译
pg_jieba代码
# 安装 pg dev 包
sudo apt install postgresql-server-dev-12 libpq-dev -y
# 同步 pg_jieba 代码
git clone git clone https://github.com/jaiminpan/pg_jieba
git clone https://github.com/jaiminpan/pg_jieba
cd pg_jieba/
git submodule update --init --recursive
mkdir build
cd build
cmake -DPostgreSQL_TYPE_INCLUDE_DIR=/usr/include/postgresql/12/server/ ..
make
sudo make install
- 配置
pg_jieba插件
CREATE EXTENSION pg_jieba;
- 测试
pg_jieba
# 以 to_tsquery 的方式进行查询
select * from to_tsquery('jiebacfg', '是拖拉机学院手扶拖拉机专业的。不用多久，我就会升职加薪，当上CEO，走上人生巅峰。');
# 以 ts_debug 的方式进行查询
select * from ts_debug('jiebacfg', '是拖拉机学院手扶拖拉机专业的。不用多久，我就会升职加薪，当上CEO，走上人生巅峰。');
安装 smlar 插件
作为文本相似的查询，常见插件有 smlar 和 pg_similarity, 这里以 smlar 为例进行测试
- 编译安装插件
# 同步代码
git clone git://sigaev.ru/smlar.git
# 编译执行
cd smlar
# 使用 pg_config 进行配置
export USE_PGXS=1
# 编译执行
make
# 安装
sudo make install
注 安装时会出现异常，注释掉 Makefile 中如下即可
#ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
#else
#subdir = contrib/smlar
#top_builddir = ../..
#include $(top_builddir)/src/Makefile.global
#include $(top_srcdir)/contrib/contrib-global.mk
#endif
- 构建词频表
# 添加文本相似度插件 smlar, 含索引方式实现
create extension smlar;
# 建表
create table documents_body_stats (
value text unique,
ndoc int not null
);
# 导入词频数据(这里可以创建物化视图，每日刷新即可)
insert into documents_body_stats
select word, ndoc
from ts_stat( 'select to_tsvector(''simple'', body) from documents' );
# 插入文档数量
insert into documents_body_stats values (null, (select count(*) from documents) );
- 配置
smlar.conf
- 在配置文件中进行配置
在 postgres.conf 文件中，我们看到 include_dir = 'conf.d' 表示将所有 conf.d 目录下的 .conf 后缀的文件，添加到配置
$ cat /etc/postgresql/12/main/conf.d/smlar.conf
smlar.persistent_cache true
smlar.stattable 'documents_body_stats'
smlar.type 'tfidf'
smlar.idf_plus_one true
smlar.tf_method 'const'
smlar.threshold 0.5
- 在使用过程中同样能修改
SET smlar.stattable TO 'documents_body_stats'
注 配置文件的方式进行配置需要进行重启服务sudo systemctl restart postgresql
- 插件测试
select smlar(tsvector2textarray(to_tsvector('jiebacfg','黑猩猩每年都在增长')), tsvector2textarray(to_tsvector('jiebacfg','黑猩猩和人类很接近 readface')))
# 查询 `电力发展快速` 相似度高的文章
select
*,
smlar( tsvector2textarray(tcsv), tsvector2textarray(to_tsvector('jiebacfg','电力发展快速')) )
from
tbl_news
where
smlar( tsvector2textarray(tcsv), tsvector2textarray(to_tsvector('jiebacfg','电力发展快速')) ) > 0.1
order by
smlar( tsvector2textarray(tcsv), tsvector2textarray(to_tsvector('jiebacfg','电力发展快速')) ) desc
limit 10;
- 海明距离计算优化
# 构建海明测试表
create table hm4 (id int, hmval bit(64), hmarr text[]);
# 构建 gin 索引，gin 索引具有块级收敛，二重过滤
create index idx_hm1 on hm1 using gin(hmarr _text_sml_ops );
# 配置 smlar 类型为 overlap
set smlar.type = overlap;
# 构建自动拆分函数
create or replace function sp(val bit(64)) returns text[] as $$
select regexp_split_to_array('1_'||substring(val::text,1,10)||',2_'||substring(val::text,11,10)||',3_'||substring(val::text,21,10)||',4_'||substring(val::text,31,10)||',5_'||substring(val::text,41,10)||',6_'||substring(val::text,51,14), ',') ;
$$ language sql strict;
# 插入数据
insert into hm4 (id, hmval) values (1, 123::bit(64));
# 插入 bit(64) 数据，使用 cast 语句进行转换，可用于java插入语句
insert into hm4 (id, hmval) values(1, cast(1233 as bit(64)))
# 构建触发器函数
create or replace function tg() returns trigger as $$
declare
begin
NEW.hmarr := sp(NEW.hmval);
return NEW;
end;
$$ language plpgsql strict;
# 添加触发器
create trigger tg before insert or update on hm4 for each row execute procedure tg();
# 查询优化
select
*,
smlar( hmarr, sp(123::bit(64))) -- 查询与123::bit(64)海明距离小于2的记录
from
hm4
where
hmarr % sp(123::bit(64)) -- 查询与123::bit(64)海明距离小于2的记录
and length(replace(bitxor(123::bit(64), hmval)::text,'0','')) < 2 -- 查询与123::bit(64)海明距离小于2的记录
limit 100;
