IvorySQL v3.0 新特性浅测

原创严少安 2023-10-31

1161

IvorySQL 3.0 编译安装

之前介绍过 IvorySQL 2 的编译安装，但 3.0 是大版本变更，安装过程与之前还是有些许区别，需要注意。

操作系统

操作系统信息：

[shawnyan@rocky9 ~]$ cat /etc/redhat-release
Rocky Linux release 9.2 (Blue Onyx)
[shawnyan@rocky9 ~]$ uname -a
Linux rocky9.shawnyan.cn 5.14.0-284.30.1.el9_2.x86_64 #1 SMP PREEMPT_DYNAMIC Sat Sep 16 09:55:41 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux
[shawnyan@rocky9 ~]$ date
Tue Oct 31 10:12:29 PM CST 2023

本文使用的编译环境为 Rocky Linux 9.2 操作系统，关于如何安装该系统，可参考：

Step by Step 安装 Rocky Linux 9.2 系统

克隆源码

这里提示两种下载源码方式，一是直接用 git 进行克隆，可以获取到最新版的代码，及提交记录，二是直接下载打包好的源码。

git clone https://github.com/IvorySQL/IvorySQL.git -b IVORY_REL_3_STABLE --depth=1

查看源码版本

[root@rocky9 IvorySQL]# git lg
* a14ca56 - (grafted, HEAD -> IVORY_REL_3_STABLE, tag: Ivorysql_3.0_Beta, origin/IVORY_REL_3_STABLE) IvorySQL:Modify version information. (12 days ago) <wangjie>

或者直接下载源码包

wget https://github.com/IvorySQL/IvorySQL/archive/refs/tags/Ivorysql_3.0_Beta.zip

编译 IvorySQL 3

进入代码目录，进行编译配置，这里需要注意的是，如果本地环境没有安装 ICU 类库，则会报错，所以可以在编译选项中排除编译 ICU 。

编译配置项

./configure --prefix=/opt/ivorysql --with-extra-version="-ShawnYan" \
--without-icu

输出日志：

...
configure: using compiler=gcc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-4)
configure: using CFLAGS=-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2
configure: using CPPFLAGS= -D_GNU_SOURCE
configure: using LDFLAGS=  -Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port
[root@rocky9 IvorySQL-Ivorysql_3.0_Beta]#

ICU 报错

checking for icu-uc icu-i18n... no
configure: error: ICU library not found
If you have ICU already installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-icu to disable ICU support.

笔者案：这里是个优化点。

make 编译

虽然 Ivory 3 增加了新的编译方式，可以用 meson 进行编译，但本文使用的方式依旧是传统的 make。

但是，在编译过程中遇到如下报错：

Can't locate FindBin.pm in @INC (you may need to install the FindBin module) (@INC contains: /usr/local/lib64/perl5/5.32 /usr/local/share/perl5/5.32 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5) at genbki.pl line 20.
BEGIN failed--compilation aborted at genbki.pl line 20.
make[2]: *** [Makefile:109: bki-stamp] Error 2
make[2]: Leaving directory '/data/IvorySQL-Ivorysql_3.0_Beta/src/backend/catalog'
make[1]: *** [Makefile:138: submake-catalog-headers] Error 2
make[1]: Leaving directory '/data/IvorySQL-Ivorysql_3.0_Beta/src/backend'
make: *** [src/Makefile.global:393: submake-generated-headers] Error 2

提示缺少依赖 perl-FindBin，安装后再次编译，通过。

笔者案：这里是个优化点。

...
make[1]: Leaving directory '/data/IvorySQL-Ivorysql_3.0_Beta/contrib/ora_btree_gist'
make -C contrib/ora_btree_gin all
make[1]: Entering directory '/data/IvorySQL-Ivorysql_3.0_Beta/contrib/ora_btree_gin'
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o ora_btree_gin.o ora_btree_gin.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o ora_btree_gin.so  ora_btree_gin.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/opt/ivorysql/lib',--enable-new-dtags -fvisibility=hidden
make[1]: Leaving directory '/data/IvorySQL-Ivorysql_3.0_Beta/contrib/ora_btree_gin'
[root@rocky9 IvorySQL-Ivorysql_3.0_Beta]#

编译完成后，进行安装。

[shawnyan@rocky9 IvorySQL-Ivorysql_3.0_Beta]$ sudo make install
make -C ./src/backend generated-headers
make[1]: Entering directory '/data/IvorySQL-Ivorysql_3.0_Beta/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/data/IvorySQL-Ivorysql_3.0_Beta/src/backend/catalog'
...
make[1]: Entering directory '/data/IvorySQL-Ivorysql_3.0_Beta/contrib/ora_btree_gin'
/bin/mkdir -p '/opt/ivorysql/lib/postgresql'
/bin/mkdir -p '/opt/ivorysql/share/postgresql/extension'
/bin/mkdir -p '/opt/ivorysql/share/postgresql/extension'
/bin/install -c -m 755  ora_btree_gin.so '/opt/ivorysql/lib/postgresql/ora_btree_gin.so'
/bin/install -c -m 644 ./ora_btree_gin.control '/opt/ivorysql/share/postgresql/extension/'
/bin/install -c -m 644 ./ora_btree_gin--1.0.sql  '/opt/ivorysql/share/postgresql/extension/'
make[1]: Leaving directory '/data/IvorySQL-Ivorysql_3.0_Beta/contrib/ora_btree_gin'
[shawnyan@rocky9 IvorySQL-Ivorysql_3.0_Beta]$

接下来，初始化数据目录后，便可启动 IvorySQL 3。

初始化 PG 模式 `initdb -m pg`

从 IvorySQL 3 开始，IvorySQL 兼容 Oracle 的方式不再通过插件形式兼容，而是直接固化到源码中。
在 IvorySQL 初始化时，可以指定是 pg 模式，还是 oracle 模式，默认为 oracle 模式。

只是，在初始化的时候找了好久不知道可以传什么参数，试了 postgres/postgresql 都不行，原来只需要简单的 pg 二字。

[ivorysql@rocky9 ~]$ initdb -? | grep "dbmode"
  -m, --dbmode=MODE 	    set database mode, default is oracle

笔者案：这里是个优化点。

这里先指定 pg 模式，来看看熟悉的一面。

[ivorysql@rocky9 ~]$ initdb -m pg
The files belonging to this database system will be owned by user "ivorysql".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/ivorysql/3/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/ivorysql/3/data -l logfile start

[ivorysql@rocky9 ~]$ pg_ctl -D /var/lib/ivorysql/3/data -l logfile start
waiting for server to start.... done
server started

启动服务后，连接数据库，发现找不到 ivorysql 这个数据库，经确认，该库在 ivory 3 中已被移除。

为了便于测试，这里先手动创建一个名为 ivorysql 的数据库，然后再次连接。

查看版本号

图片.png

可以看到 IvorySQL 3.0 BETA 版，基于 PostgreSQL 16.0，版本号中加了我的签名。

查看 ivorysql 相关设定

ivorysql=# select name,setting,short_desc from pg_settings where name like '%ivory%';
                name                 |   setting   |                              short_desc                              
-------------------------------------+-------------+----------------------------------------------------------------------
 ivorysql.compatible_mode            | pg          | Set default sql parser compatibility mode
 ivorysql.database_mode              | pg          | Set database mode
 ivorysql.datetime_ignore_nls_mask   | 0           | Sets the datetime type input is not controlled by the NLS parameter.
 ivorysql.enable_emptystring_to_NULL | off         | whether convert empty string to NULL.
 ivorysql.identifier_case_switch     | interchange | Set character case conversion mode.
 ivorysql.listen_addresses           | localhost   | Sets oracle host name or IP address(es) to listen to.
 ivorysql.port                       | 1521        | Sets the Oracle TCP port the server listens on.
(7 rows)

图片.png

初始化 Oracle 模式 `initdb -m oracle`

由于 Ivory 3 中，初始化默认选择 oracle 模式，所以 -m oracle 和不加 -m 参数是等价的。

[ivorysql@rocky9 3]$ initdb -m oracle
The files belonging to this database system will be owned by user "ivorysql".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/ivorysql/3/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/ivorysql/3/data -l logfile start

[ivorysql@rocky9 3]$

启动 Oracle 模式下的 IvorySQL 3

启动后，再次查询设定，可以看到，数据库模式变成了 oracle，但当前兼容模式是 pg。

ivorysql=# select name,setting,short_desc from pg_settings where name like '%ivory%';
                name                 |   setting   |                              short_desc                              
-------------------------------------+-------------+----------------------------------------------------------------------
 ivorysql.compatible_mode            | pg          | Set default sql parser compatibility mode
 ivorysql.database_mode              | oracle      | Set database mode
 ivorysql.datetime_ignore_nls_mask   | 0           | Sets the datetime type input is not controlled by the NLS parameter.
 ivorysql.enable_emptystring_to_NULL | on          | whether convert empty string to NULL.
 ivorysql.identifier_case_switch     | interchange | Set character case conversion mode.
 ivorysql.listen_addresses           | localhost   | Sets oracle host name or IP address(es) to listen to.
 ivorysql.port                       | 1521        | Sets the Oracle TCP port the server listens on.
(7 rows)

图片.png

此时，创建 varchar2 字段类型的表会报错。

odb=# create table t(id int, name varchar2(10));
2023-10-31 22:23:41.196 CST [84392] ERROR:  type "varchar2" does not exist at character 29
2023-10-31 22:23:41.196 CST [84392] STATEMENT:  create table t(id int, name varchar2(10));
ERROR:  type "varchar2" does not exist
LINE 1: create table t(id int, name varchar2(10));
                                    ^

这是因为 psql 默认从端口 5432 进入，如果想使用 IvorySQL 的 Oracle 模式，需要从端口 1521 进入。

[ivorysql@rocky9 3]$ psql -p1521
psql (16.0-ShawnYan)
Type "help" for help.

ivorysql=# select name,setting,short_desc from pg_settings where name like '%ivory%';
                name                 |   setting   |                              short_desc                              
-------------------------------------+-------------+----------------------------------------------------------------------
 ivorysql.compatible_mode            | oracle      | Set default sql parser compatibility mode
 ivorysql.database_mode              | oracle      | Set database mode
 ivorysql.datetime_ignore_nls_mask   | 0           | Sets the datetime type input is not controlled by the NLS parameter.
 ivorysql.enable_emptystring_to_NULL | on          | whether convert empty string to NULL.
 ivorysql.identifier_case_switch     | interchange | Set character case conversion mode.
 ivorysql.listen_addresses           | localhost   | Sets oracle host name or IP address(es) to listen to.
 ivorysql.port                       | 1521        | Sets the Oracle TCP port the server listens on.
(7 rows)

再次创建测试表，成功。

odb=# create table t(id int, name varchar2(10));
CREATE TABLE
odb=# \d t
                     Table "public.t"
 Column |      Type       | Collation | Nullable | Default 
--------+-----------------+-----------+----------+---------
 id     | pg_catalog.int4 |           |          | 
 name   | varchar2(10)    |           |          | 

odb=#

IvorySQL 3 支持双端口号

上文已经描述了 pg/oracle 两种模式，而且端口号不同，再次从 OS 层面进行验证。

[ivorysql@rocky9 ~]$ ss -antpl | grep postgres
LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=84211,fd=7))
LISTEN 0      200        127.0.0.1:1521      0.0.0.0:*    users:(("postgres",pid=84211,fd=9))
LISTEN 0      200            [::1]:5432         [::]:*    users:(("postgres",pid=84211,fd=6))
LISTEN 0      200            [::1]:1521         [::]:*    users:(("postgres",pid=84211,fd=8))
[ivorysql@rocky9 ~]$

可以更加完整的看到，服务监听了 5432 和 1521 两个端口。