定制插件

oraudb

本插件是基于orafce 的UDB定制版本，UDB在orafce的基础上对部分功能进行了增强。该插件的启用方法如下：

[root@unvdb orafce-VERSION_3_18_1]# ud_sql -c "CREATE EXTENSION oraudb;"

鉴于oraudb的一些对象建立在oracle schema下，可以根据需要在会话级、database级或实例级，设置search_path 包含oracle schema。例如：

ALTER ROLE role_name SET search_path to "$user",public,oracle ;

或者

ALTER DATABASE database_name  SET search_path = "$user",public,oracle ;

这样就配置完成了，但是需要注意时区问题，orafce 默认使用的是UDB-TX 数据库系统的时区，由单独的参数 orafce.timezone 控制。

[root@unvdb ~]# vi unvdbsvr.conf
log_timezone = 'PRC'
timezone = 'PRC'
orafce.timezone = 'PRC'
[root@unvdb ~]# 
[root@unvdb ~]# ud_sql
unvdbsvr=# show orafce.timezone;
 orafce.timezone
-----------------
 PRC
(1 row)
unvdbsvr=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
       sysdate       |              now              |       current_timestamp       |        clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
 2022-04-01 10:32:42 | 2022-04-01 10:32:41.543877+08 | 2022-04-01 10:32:41.543877+08 | 2022-04-01 10:32:41.543979+08
(1 row)

与 Oracle 数据库兼容的特性

数据类型

数据类型	说明
VARCHAR2	可变长度字符数据类型
NVARCHAR2	可变长度国家字符数据类型
DATE	存储日期和时间的数据类型

unvdbsvr=# set search_path="$user", public, oracle;
SET
unvdbsvr=# CREATE TABLE tt (
unvdbsvr(#  name VARCHAR2(64) NOT NULL,
unvdbsvr(#  status NVARCHAR2(2000),
unvdbsvr(#  update_time DATE default (SYSDATE())
unvdbsvr(# );
CREATE TABLE
unvdbsvr=# \d tt
                        Table "public.tt"
   Column    |      Type       | Collation | Nullable |  Default
-------------+-----------------+-----------+----------+-----------
 name        | varchar2(64)    |           | not null |
 status      | nvarchar2(2000) |           |          |
 update_time | date            |           |          | sysdate()
# 测试在 date 数据类型的字段上创建分区表
create table test_range(id serial, create_time date) partition by range(create_time);
create table test_range_20220301 PARTITION of test_range FOR VALUES FROM ('2022-03-01 00:00:00') TO ('2022-03-02 00:00:00');
create table test_range_20220302 PARTITION of test_range FOR VALUES FROM ('2022-03-02 00:00:00') TO ('2022-03-03 00:00:00');
create table test_range_20220303 PARTITION of test_range FOR VALUES FROM ('2022-03-03 00:00:00') TO ('2022-03-04 00:00:00');
create table test_range_20220304 PARTITION of test_range FOR VALUES FROM ('2022-03-04 00:00:00') TO ('2022-03-05 00:00:00');
create table test_range_20220305 PARTITION of test_range FOR VALUES FROM ('2022-03-05 00:00:00') TO ('2022-03-06 00:00:00');
create table test_range_default partition of test_range default;
unvdbsvr=# \d+ test_range
                                            Partitioned table "public.test_range"
   Column    |  Type   | Collation | Nullable |                Default                 | Storage | Stats target | Description
-------------+---------+-----------+----------+----------------------------------------+---------+--------------+-------------
 id          | integer |           | not null | nextval('test_range_id_seq'::regclass) | plain   |              |
 create_time | date    |           |          |                                        | plain   |              |
Partition key: RANGE (create_time)
Partitions: test_range_20220301 FOR VALUES FROM ('2022-03-01') TO ('2022-03-02'),
            test_range_20220302 FOR VALUES FROM ('2022-03-02') TO ('2022-03-03'),
            test_range_20220303 FOR VALUES FROM ('2022-03-03') TO ('2022-03-04'),
            test_range_20220304 FOR VALUES FROM ('2022-03-04') TO ('2022-03-05'),
            test_range_20220305 FOR VALUES FROM ('2022-03-05') TO ('2022-03-06'),
            test_range_default DEFAULT
# 向分区表中插入数据
unvdbsvr=# insert into test_range (create_time) values (sysdate());
INSERT 0 1
# 查询分区表里的数据
unvdbsvr=# select * from test_range;
 id | create_time
----+-------------
  2 | 2022-07-31
(1 row)
unvdbsvr=# select to_char(create_time,'YYYY-MM-DD HH24:MI:SS') from test_range;
       to_char
---------------------
 2022-07-31 00:00:00
(1 row)

为什么这里的 date 数据类型似乎只存储 ‘年月日’，而 Oracle 的 date 数据类型会存储 ‘年月日时分秒’。这里就要说一下使用 orafce 的注意事项，详见后面章节<使用 orafce 的注意事项>。

支持 DUAL 表

unvdbsvr=# \d+ dual
                                  View "public.dual"
 Column |       Type        | Collation | Nullable | Default | Storage  | Description
--------+-------------------+-----------+----------+---------+----------+-------------
 dummy  | character varying |           |          |         | extended |
View definition:
 SELECT 'X'::character varying AS dummy;
unvdbsvr=# \dv public.*
               List of relations
 Schema |        Name        | Type |  Owner
--------+--------------------+------+----------
 public | dual               | view | unvdbsvr
 public | pg_stat_statements | view | unvdbsvr
(2 rows)
unvdbsvr=# select 1 from dual;
 ?column?
----------
        1
(1 row)
unvdbsvrs=# select * from dual;
 dummy
-------
 X
(1 row)
unvdbsvr=# SELECT CURRENT_DATE "date" FROM DUAL;
    date
------------
 2022-04-01

SQL 函数

数学函数

函数名称	说明	对比无插件UDB-TX22.4
BITAND	Performs a bitwise AND operation	增强
COSH	Calculates the hyperbolic cosine of a number	自带
SINH	Calculates the hyperbolic sine of a number	自带
TANH	Calculates the hyperbolic tangent of a number	自带

字符串函数

函数名称	说明	对比无插件UDB-TX22.4
INSTR	Returns the position of a substring in a string	新增
LENGTH	Returns the length of a string in number of characters	增强
LENGTHB	Returns the length of a string in number of bytes	新增
LPAD	Left-pads a string to a specified length with a sequence of characters	增强
LTRIM	Removes the specified characters from the beginning of a string	增强
NLSSORT	Returns a byte string used to sort strings in linguistic sort sequence based on locale	新增
REGEXP_COUNT	searches a string for a regular expression, and returns a count of the matches	新增
REGEXP_INSTR	returns the beginning or ending position within the string where the match for a pattern was located	新增
REGEXP_LIKE	condition in the WHERE clause of a query, causing the query to return rows that match the given pattern	新增
REGEXP_SUBSTR	returns the string that matches the pattern specified in the call to the function	新增
REGEXP_REPLACE	replace substring(s) matching a POSIX regular expression	增强
RPAD	Right-pads a string to a specified length with a sequence of characters	增强
RTRIM	Removes the specified characters from the end of a string	增强
SUBSTR	Extracts part of a string using characters to specify position and length	增强
SUBSTRB	Extracts part of a string using bytes to specify position and length	新增

Date/time 函数

函数名称	说明	对比无插件UDB-TX22.4
ADD_MONTHS	Adds months to a date	新增
DBTIMEZONE	Returns the value of the database time zone	新增
LAST_DAY	Returns the last day of the month in which the specified date falls	新增
MONTHS_BETWEEN	Returns the number of months between two dates	新增
NEXT_DAY	Returns the date of the first instance of a particular day of the week that follows the specified date	新增
ROUND	Rounds a date	增强
SESSIONTIMEZONE	Returns the time zone of the session	新增
SYSDATE	Returns the system date	新增
TRUNC	Truncates a date	增强

数据类型格式化函数

函数名称	说明	对比无插件UDB-TX22.4
TO_CHAR	Converts a value to a string	增强
TO_DATE	Converts a string to a date in accordance with the specified format	增强
TO_MULTI_BYTE	Converts a single-byte string to a multibyte string	新增
TO_NUMBER	Converts a value to a number in accordance with the specified format	增强
TO_SINGLE_BYTE	Converts a multibyte string to a single-byte string	新增

条件表达式函数

函数名称	说明	对比无插件UDB-TX22.4
DECODE	Compares values, and if they match, returns a corresponding value	增强
GREATEST	Returns the greatest of the list of one or more expressions	增强
LEAST	Returns the least of the list of one or more expressions	增强
LNNVL	Evaluates if a value is false or unknown	新增
NANVL	Returns a substitute value when a value is not a number (NaN)	新增
NVL	Returns a substitute value when a value is NULL	新增
NVL2	Returns a substitute value based on whether a value is NULL or not NULL	新增

聚合函数

函数名称	说明	对比无插件UDB-TX22.4
LISTAGG	Returns a concatenated, delimited list of string values	新增
MEDIAN	Calculates the median of a set of values	新增

返回内部信息的函数

函数名称	说明	对比无插件UDB-TX22.4
DUMP	Returns internal information of a value	新增

SQL 运算符

名称	说明	对比无插件UDB-TX22.4
Datetime operator	Datetime operator for the DATE type	-

Packages 包

名称	说明	对比无插件UDB-TX22.4
DBMS_ALERT	Sends alerts to multiple sessions	-
DBMS_ASSERT	Validates the properties of an input value	-
DBMS_OUTPUT	Sends messages to clients	-
DBMS_PIPE	Creates a pipe for inter-session communication	-
DBMS_RANDOM	Generates random numbers	-
DBMS_UTILITY	Provides various utilities	-
UTL_FILE	Enables text file operations	-

unvdbGIS

unvdbgis 是一组基于PostGIS定制的适用于UDB-TX 数据库的GIS扩展。每个扩展提供的技术细节，可联机查询。类似命令如下：

unvdb=# \dx+ udbgis

unvdbgis

unvdbgis的基本核心功能，仅支持地理图形（矢量要素），在其他Extension前启用。这个是基础插件，必须要安装的。

unvdbgis安装

联系技术支持获取插件包,
复制到数据库安装目录,
修改插件文件权限.
运行以下sql语句启用插件.

ud_sql
\c template1; --在template1模板上启用插件,后续创建的数据库默认启用插件.也可以进入目标数据库按需启用插件.
create extension fuzzystrmatch; 
create extension udbgis; 
create extension udbgis_tiger_geocoder; 
create extension udbgis_raster; 
create extension udbgis_topology; 
create extension udbgis_sfcgal; 
create extension address_standardizer; 
create extension address_standardizer_data_us; 
create extension pgrouting CASCADE; 
create extension pointcloud; 
create extension pointcloud_udbgis; 
create extension ogr_fdw; 
create extension udb_trajectory;

unvdbgis_raster

对栅格数据的支持。安装过unvdbgis后可直接启用该扩展，默认不启用。

unvdbgis_topology

拓扑功能的支持。安装过unvdbgis后可直接启用该扩展，默认不启用。

unvdbgis_tiger_geocoder

TIGER指的是（拓扑集成地理编码和参考），这个是美国人口普查局的GIS数据，提供了美国全国的行政区划、交通道路、水系等空间数据。这个Extension提供了TIGER数据的地理编码支持，需要注意的是这个Extension启用前，需要先启用fuzzystrmatch（字符串模糊查询）Extension（至少需要启用这个），以及可选的address_standardizer（TIGER数据地址规则化）、address_standardizer_data_us（地址规则化示例数据集）Extension。

（先安装了fuzzystrmatch后，才可以启用unvdbgis_tiger_geocoder）

unvdbgis_sfcgal

这个Extension主要是集成了CGAL（Computational Geometry Algorithms Library，计算几何算法库），来进行三维空间数据的空间运算，例如ST_3DDifference、ST_3DUnion 等，可见是通常空间运算在三维空间上的拓展。可产生各种3d模型，进而利用产生的数据进行3d渲染。

ud_trashcan-闪回回收站

简介

ud_trashcan是闪回回收站功能。在使用udbtx数据库时，可能会意外删除/truncate一些数据或数据库对象，可能是由于误操作、意外的数据丢失、或者其他不可预知的情况。为了应对这类情况，udbtx引入了闪回回收站，闪回回收站用于将删除/truncate的表临时转移到回收站，闪回回收站功能为用户提供一种误删表/truncate表后还原表的一种手段。

前提要求

闪回回收站是默认关闭的，需要手动开启，参数名称：udb_flashback.db_recyclebin，参数为bool类型。表回收站功能开关。取值范围如下： on：打开表回收站功能开关。 off（默认值）：关闭表回收站功能开关全局设置：在unvdbsvr.conf中设置udb_flashback.db_recyclebin = ‘on’;此种情况需要重启数据库使其生效。会话设置：在会话中设置，set udb_flashback.db_recyclebin to on;

注意事项

当Trash schema中存在同名的表时, 也会报错，所以尽量不要使用与回收站同名的表。
drop table的用户需要创建schema的权限, 以及写Trash schema的权限，如果没有创建schema的权限会报错。

安装

udb-tx-v24.2.8以后的版本，已启用 ud_trashcan, 直接使用即可。 udb-tx-v24.2.8及以前的版本，按以下步骤安装启用。

【附件】ud_trashcan.zip 如下载失败请联系技术支持获取。

启用

ud_trashcan.so 放到安装目录的lib/ 下
chown -R udb.udb 安装目录/lib/ud_trashcan.so

配置

配置文件unvdbsvr.conf里面设置参数shared_preload_libraries = ‘ud_trashcan.so’
重新启动数据库服务。

验证安装

unvdb=#  show udb_flashback.db_recyclebin;
udb_flashback.db_recyclebin 
-----------------------------
 off
(1 row)

使用示例

闪回回收站开关功能

-打开回收站开关。

unvdb=# show udb_flashback.db_recyclebin; 
 udb_flashback.db_recyclebin 
-----------------------------
 off
(1 row)

unvdb=# set udb_flashback.db_recyclebin to on;
SET

unvdb=# show udb_flashback.db_recyclebin;     
 udb_flashback.db_recyclebin 
-----------------------------
 on
(1 row)

闪回drop表数据

-创建表test_drop_t，并将该表删除，该表就会被放置到回收站当中，查询的时候发现test_drop_t已经被删除，但是我们可以从回收站中查询到删除之前test_drop_t的数据。

unvdb=# create table test_drop_t(no int,name text);
CREATE TABLE

unvdb=# insert into test_drop_t values (100,'test100'),(200,'test200'),(300,'test300');
INSERT 0 3

unvdb=# select * from test_drop_t;
 no  |  name   
-----+---------
 100 | test100
 200 | test200
 300 | test300
(3 rows)

unvdb=# drop table test_drop_t;
DROP TABLE

unvdb=# select * from "Trash".test_drop_t;
 no  |  name   
-----+---------
 100 | test100
 200 | test200
 300 | test300
(3 rows)

闪回truncate表数据

-创建表test_truncate_t，并将该表truncate，该表就会被放置到回收站当中，查询的时候发现test_truncate_t数据为空，但是我们可以从回收站中查询到truncate之前test_truncate_t的数据。

unvdb=# create table test_truncate_t(no int,name text);
CREATE TABLE

unvdb=# insert into test_truncate_t values (100,'test100'),(200,'test200'),(300,'test300');
INSERT 0 3

unvdb=# select * from test_truncate_t;
 no  |  name   
-----+---------
 100 | test100
 200 | test200
 300 | test300
(3 rows)

unvdb=# truncate table test_truncate_t;
TRUNCATE TABLE

unvdb=# select * from test_truncate_t;
 no  |  name   
----
(0 rows)

unvdb=# select * from "Trash".test_truncate_t;
 no  |  name   
-----+---------
 100 | test100
 200 | test200
 300 | test300
(3 rows)

清空回收站

-回收站需用户定期维护，避免回收站膨胀，用户可以通过drop…CASCADE操作对回收站进行维护，分为删除回收站中一个指定表和清空回收站。

-清空回收站，回收站中的表都将被清除。

unvdb=# SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'Trash'; 
    tablename    
-----------------
 test_truncate_t
 test_drop_t
(2 rows)

unvdb=# drop schema "Trash" CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table "Trash".test_truncate_t
drop cascades to table "Trash".test_drop_t
DROP SCHEMA

unvdb=# SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'Trash'; 
 tablename 
-----------
(0 rows)

-清空回收站中的表test_drop_t。

unvdb=# SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'Trash'; 
  tablename  
-------------
 test_drop_t
(1 row)

unvdb=# drop table "Trash".test_drop_t;
DROP TABLE

unvdb=# SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'Trash'; 
 tablename 
-----------
(0 rows)

关闭回收站

-关掉闪回回收站之后，删除/truncate表时，表将不会进入回收站。

unvdb=# show udb_flashback.db_recyclebin;
 udb_flashback.db_recyclebin 
-----------------------------
 on
(1 row)

unvdb=# set udb_flashback.db_recyclebin to off;
SET

unvdb=# show udb_flashback.db_recyclebin;                                      
 udb_flashback.db_recyclebin 
-----------------------------
 off
(1 row)

unvdb=# create table test_drop_t(no int,name text);
CREATE TABLE

unvdb=# insert into test_drop_t values (100,'test100'),(200,'test200'),(300,'test300');
INSERT 0 3

unvdb=# select * from test_drop_t;
 no  |  name   
-----+---------
 100 | test100
 200 | test200
 300 | test300
(3 rows)

unvdb=# drop table test_drop_t;
DROP TABLE

unvdb=# SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'Trash'; 
 tablename 
-----------
(0 rows)

ud_dirtyread-查看脏数据

简介

ud_dirtyread 是脏读插件，用于读取那些已经删除但未被清理的数据。当误操作delete数据时，可以使用它从脏数据中恢复出来。不支持 DDL 操作和 truncate 操作的恢复。

前提要求

未手动或自动执行 vacuum 清理操作

查询上一次 vacuum 执行时间，如已经执行了清理，则数据无法恢复。

select relname,last_vacuum, last_autovacuum, last_analyze, vacuum_count, autovacuum_count,  last_autoanalyze from pg_stat_user_tables;

注意事项

不建议在生产环境直接执行本节的所有操作。
建议将当前所有文件复制到一个独立的机器进行恢复操作，避免对当前环境造成更大的数据损坏。
在将结果恢复到生产环境时，建议先备份生产环境当前的所有数据。

安装

udb-tx-v22.4.22及以后的版本,已启用 ud_dirtyread, 直接使用即可。 udb-tx-v22.4.22及以前的版本,按以下步骤安装启用。

【附件】ud_dirtyread.zip 如下载失败请联系技术支持获取。

启用

ud_dirtyread.so 放到安装目录的lib/ 下
ud_dirtyread.control 和 ud_dirtyread–*.sql 放到安装目录的share/extension/ 下
chown -R udb.udb 安装目录/lib/ud_dirtyread.so
chown -R udb.udb 安装目录/share/extension/ud_dirtyread*

验证安装

waltest=#  CREATE EXTENSION ud_dirtyread;
CREATE EXTENSION

使用示例

创建测试数据

waltest=# select * from tb1;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)
waltest=# \dt tb1;
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | tb1  | table | unvdb
(1 row)
waltest=# \d tb1;
                        Table "public.tb1"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | integer                |           | not null | 
 name   | character varying(255) |           |          | 
Indexes:
    "tb1_pkey" PRIMARY KEY, btree (id)

查看当前数据元组信息

waltest=# select xmin,xmax,ctid,* from tb1;     
 xmin | xmax | ctid  | id | name 
------+------+-------+----+------
 1650 |    0 | (0,1) |  1 | a
 1651 |    0 | (0,2) |  2 | b
(2 rows)

查看是否为脏数据，dead列为f,表示非死数据

waltest=# select * from ud_dirtyread('tb1') as foo(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int,name varchar(255)); 
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name 
----------+-------+------+------+------+------+------+----+------
    66027 | (0,1) | 1650 |    0 |    0 |    0 | f    |  1 | a
    66027 | (0,2) | 1651 |    0 |    0 |    0 | f    |  2 | b
(2 rows)

删除数据

waltest=# delete from tb1 where id=1;
DELETE 1
waltest=# select * from tb1;
 id | name 
----+------
  2 | b
(1 row)

修改数据

waltest=# update tb1 set name='bb' where id=2;
UPDATE 1

查看已删除未清理的脏数据

waltest=# select * from ud_dirtyread('tb1') as foo(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int,name varchar(255));
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name 
----------+-------+------+------+------+------+------+----+------
    66027 | (0,1) | 1650 | 1652 |    0 |    0 | t    |  1 | a
    66027 | (0,2) | 1651 | 1653 |    0 |    0 | t    |  2 | b
    66027 | (0,3) | 1653 |    0 |    0 |    0 | f    |  2 | bb
(3 rows)

会发现id=1的那条数据状态 dead=t 更新的id=2那条数据的事务已经由1651变为1653，原来的状态变为了删除，值已经变更为bb

删除列

waltest=# alter table tb1 drop column name;
ALTER TABLE
waltest=# select * from tb1;
 id 
----
  2
(1 row)

查看已删除列的脏数据，此时要用 dropped_n 来代替

waltest=# select * from ud_dirtyread('tb1') as foo(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int,dropped_2 varchar(255)); 
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | dropped_2 
----------+-------+------+------+------+------+------+----+-----------
    66027 | (0,1) | 1650 | 1652 |    0 |    0 | t    |  1 | a
    66027 | (0,2) | 1651 | 1653 |    0 |    0 | t    |  2 | b
    66027 | (0,3) | 1653 |    0 |    0 |    0 | f    |  2 | bb
(3 rows)

ud_walminer-从wal日志中提取sql实现数据修复

简介

ud_walminer 可以解析 wal 日志，尽可能多的获取有用信息,进一步生成undo语句，实现恢复删除或丢失的数据。功能如下：

从wal日志中提取DML和少部分DDL，并生成对应的回滚语句；
磁盘部分损坏或执行了TRUNCATE等清除操作时，可以使用ud_walminer尝试从wal中提取数据，尽最大可能找回数据。不保证一定能恢复成功。

前提要求

表结构需要尽可能完整，例如字段名称，字段类型等
wal日志文件越多越好

注意事项

不建议在生产环境直接执行本节的所有操作。
建议将当前所有文件复制到一个独立的机器进行恢复操作，避免对当前环境造成更大的数据损坏。
在将结果恢复到生产环境时，建议先备份生产环境当前的所有数据。
在一次解析开始时会首先创建或truncate walminer_contents表，当得到想要的数据时建议先导出备份它们再执行第二次解析。

安装/启用

udb-tx-v22.4.22及以后的版本,已启用 ud_walminer, 直接使用即可。 udb-tx-v22.4.22及以前的版本,按以下步骤安装启用。

安装

【附件】ud_walminer.zip 如下载失败请联系技术支持获取。

启用

walminer.so 放到安装目录的lib/ 下
walminer.control和walminer–3.0.sql 放到安装目录的share/extension/ 下
chown -R udb.udb 安装目录/lib/walminer.so
chown -R udb.udb 安装目录/share/extension/walminer.*

验证安装

[udb@0a2593d70d5b udb]$ ud_sql 
ud_sql (22.4)
Type "help" for help.
unvdb=# create extension walminer;
-- 创建扩展插件
CREATE EXTENSION
unvdb=# 

使用说明

基础操作

create extension walminer; -- 创建扩展
select walminer_wal_add('/data/udb/ud_wal'); --添加要解析的wal日志目录或单个文件名
select walminer_wal_remove('/opt/test/wal'); --移除wal日志目录或单个文件名
select walminer_wal_list(); --列出wal文件

解析操作

#普通解析
select walminer_all(); --解析已添加的全部wal日志
select walminer_by_time(starttime, endtime); --在add的wal日志中查找对应时间范围的wal记录
select walminer_by_lsn(startlsn, endlsn); --在add的wal日志中查找对应lsn范围的wal记录
select walminer_by_xid(xid); --在add的wal日志中查找对应xid的wal记录
#精确解析
select walminer_by_time(starttime, endtime,'true');  --精确查找对应时间范围的wal记录
select walminer_by_lsn(startlsn, endlsn,'true');  --精确查找对应lsn范围的wal记录
select walminer_by_xid(xid,'true'); --精确查找对应xid的wal记录
#普通解析会严格匹配给定的范围，但可能没找到之前的checkpoint点，所以会出现记录不全的情况；
#精确解析会自动查找给定范围之前的checkpoint点，更完美的解析出结果。当普通解析结果不全时可以尝试精确解析。
#当本机没有数据结构时，可以加载其它机器生成的的数据结构
select walminer_build_dictionary('/tmp/store_dictionary'); --根据当前数据库结构生成数据字典
select walminer_load_dictionary('/tmp/store_dictionary'); --加载数据字典
#reloid 参数可以查找指定表，要用表的oid
select walminer_by_time(starttime, endtime,'false',reloid); --非精确模式查找对应时间范围的reloid表oid的wal记录
select walminer_by_lsn(startlsn, endlsn,'true',reloid); --在add的wal日志中查找对应lsn范围的wal记录
select walminer_by_xid(xid,'true',reloid);--在add的wal日志中查找对应xid的wal记录
#当表结构不完整时创建临时表并使用临时表映射解析。
create table tmp_avatar(i int); --创建临时表
select walminer_table_avatar(tmp_avatar, missed_relfilenode); --映射到临时表。此时的解析结果将按临时表格式输出。
select wal2sql(); --执行解析，wal文件越多，需要的时间也越多。
select * from walminer_contents; --查看解析结果
-- 表walminer_contents 
(
 sqlno int,         --本条sql在其事务内的序号
 xid bigint,        --事务ID
 topxid bigint,        --如果为子事务，这是是其父事务；否则为0
 sqlkind int,        --sql类型1->insert;2->update;3->delete(待优化项目)
 minerd bool,        --解析结果是否完整(缺失checkpoint情况下可能无法解析出正确结果)
 timestamp timestampTz, --这个SQL所在事务提交的时间
 op_text text,        --sql
 undo_text text,    --undo sql
 complete bool,        --如果为false，说明有可能这个sql所在的事务是不完整解析的
 schema text,        --目标表所在的模式
 relation text,        --目标表表名
 start_lsn pg_lsn,    --这个记录的开始LSN
 commit_lsn pg_lsn    --这个事务的提交LSN
)
select walminer_stop(); --结束分析操作，并释放内存。

实践演示

delete 操作恢复

模拟删除操作

waltest=# select * from tb1;         
 id | name 
----+------
  1 | a
  2 | b
(2 rows)
waltest=# delete from tb1;
DELETE 2
waltest=# select * from tb1;
 id | name 
----+------
(0 rows)

配置ud_walminer

waltest=# create extension walminer;--创建扩展
CREATE EXTENSION
waltest=# select walminer_wal_add('/data/udb/ud_wal');--添加wal目录
  walminer_wal_add   
---------------------
 24 file add success
(1 row)
waltest=# select walminer_wal_list();--列出wal文件
              walminer_wal_list              
---------------------------------------------
 (/data/udb/ud_wal/00000001000000090000001C)
 (/data/udb/ud_wal/00000001000000090000001D)
 (/data/udb/ud_wal/00000001000000090000001E)
 (/data/udb/ud_wal/00000001000000090000001F)
 (/data/udb/ud_wal/000000010000000900000020)
 (/data/udb/ud_wal/000000010000000900000021)
 (/data/udb/ud_wal/000000010000000900000022)
 (/data/udb/ud_wal/000000010000000900000023)
 (/data/udb/ud_wal/000000010000000900000024)
 (/data/udb/ud_wal/000000010000000900000025)
 (/data/udb/ud_wal/000000010000000900000026)
 (/data/udb/ud_wal/000000010000000900000027)
 (/data/udb/ud_wal/000000010000000900000028)
 (/data/udb/ud_wal/000000010000000900000029)
 (/data/udb/ud_wal/00000001000000090000002A)
 (/data/udb/ud_wal/00000001000000090000002B)
 (/data/udb/ud_wal/00000001000000090000002C)
 (/data/udb/ud_wal/00000001000000090000002D)
 (/data/udb/ud_wal/00000001000000090000002E)
 (/data/udb/ud_wal/00000001000000090000002F)
 (/data/udb/ud_wal/000000010000000900000030)
 (/data/udb/ud_wal/000000010000000900000031)
 (/data/udb/ud_wal/000000010000000900000032)
 (/data/udb/ud_wal/000000010000000900000033)
(24 rows)
waltest=# select walminer_all(); --提取wal日志
NOTICE:  Switch wal to 00000001000000090000001C on time 2023-03-27 13:58:30.446893+08
NOTICE:  Switch wal to 00000001000000090000001D on time 2023-03-27 13:58:30.447915+08
NOTICE:  Switch wal to 00000001000000090000001E on time 2023-03-27 13:58:30.44841+08
    walminer_all     
---------------------
 ud_minerwal success
(1 row)
waltest=# select * from walminer_contents; --查看提取结果
 sqlno | xid  | topxid | sqlkind | minerd |           timestamp           |              op_text              |                    undo_text                    | complete | schema | relat
ion | start_lsn  | commit_lsn 
-------+------+--------+---------+--------+-------------------------------+-----------------------------------+-------------------------------------------------+----------+--------+------
----+------------+------------
     1 | 1528 |      0 |       3 | t      | 2023-03-27 13:56:45.925757+08 | DELETE FROM public.tb1 WHERE id=1 | INSERT INTO public.tb1(id ,name) VALUES(1 ,'a') | t        | public | tb1  
    | 9/1C0058F8 | 9/1C005A10
     2 | 1528 |      0 |       3 | t      | 2023-03-27 13:56:45.925757+08 | DELETE FROM public.tb1 WHERE id=2 | INSERT INTO public.tb1(id ,name) VALUES(2 ,'b') | t        | public | tb1  
    | 9/1C0059A0 | 9/1C005A10
(2 rows)
--执行undo_text修复数据
waltest=# INSERT INTO public.tb1(id ,name) VALUES(1 ,'a');
INSERT 0 1
waltest=# INSERT INTO public.tb1(id ,name) VALUES(2 ,'b');
INSERT 0 1
waltest=# select * from tb1;--验证结果                                                                                                    
 id | name 
----+------
  1 | a
  2 | b
(2 rows)
select walminer_stop();--结束分析并释放内存。

DDL操作恢复

注意：ddl删除的列，只能恢复列信息，不能恢复列数据。

模拟删除列操作

unvdb=# \c waltest;
You are now connected to database "waltest" as user "unvdb".
waltest=# select * from tb1;
 id | name | age 
----+------+-----
  1 | a    |   1
  2 | b    |   2
(2 rows)
waltest=# ALTER TABLE "public"."tb1" DROP COLUMN "age";
ALTER TABLE
waltest=# select * from tb1;                           
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

恢复配置

waltest=# CREATE EXTENSION  walminer;
CREATE EXTENSION
waltest=# select walminer_wal_add('/data/udb/ud_wal');
  walminer_wal_add   
---------------------
 11 file add success
(1 row)
waltest=# select walminer_wal_list();
              walminer_wal_list              
---------------------------------------------
 (/data/udb/ud_wal/000000010000000900000029)
 (/data/udb/ud_wal/00000001000000090000002A)
 (/data/udb/ud_wal/00000001000000090000002B)
 (/data/udb/ud_wal/00000001000000090000002C)
 (/data/udb/ud_wal/00000001000000090000002D)
 (/data/udb/ud_wal/00000001000000090000002E)
 (/data/udb/ud_wal/00000001000000090000002F)
 (/data/udb/ud_wal/000000010000000900000030)
 (/data/udb/ud_wal/000000010000000900000031)
 (/data/udb/ud_wal/000000010000000900000032)
 (/data/udb/ud_wal/000000010000000900000033)
(11 rows)
waltest=# select wal2sql_with_ddl(); --开启ddl解析
 wal2sql_with_ddl 
------------------
 t
(1 row)
waltest=# select walminer_by_time('2023-03-27 15:00', '2023-03-27 15:15','true'); --必须使用精确解析
NOTICE:  Switch wal to 000000010000000900000029 on time 2023-03-27 15:12:59.812091+08
NOTICE:  Con not find relfilenode 65692 in dictionary, ignored related records
NOTICE:  Con not find relfilenode 65693 in dictionary, ignored related records
  walminer_by_time   
---------------------
 ud_minerwal success
(1 row)
waltest=# select * from walminer_contents;--检查解析结果
 sqlno | xid  | topxid | sqlkind | minerd |           timestamp           |                     op_text                     |             undo_text             | complete |   schema   |  
 relation   | start_lsn  | commit_lsn 
-------+------+--------+---------+--------+-------------------------------+-------------------------------------------------+-----------------------------------+----------+------------+--
------------+------------+------------
     1 | 1578 |      0 |       4 | t      | 2023-03-27 15:01:03.11249+08  | TRUNCATE TABLE public.tb1                       |                                   | t        | pg_catalog | p
g_class     | 9/291A7EB8 | 9/291AA7E0
     1 | 1583 |      0 |       1 | t      | 2023-03-27 15:09:24.706606+08 | INSERT INTO public.tb1(id ,name) VALUES(1 ,'a') | DELETE FROM public.tb1 WHERE id=1 | t        | public     | t
b1          | 9/291DA7F8 | 9/291DA910
     1 | 1584 |      0 |       1 | t      | 2023-03-27 15:09:27.201411+08 | INSERT INTO public.tb1(id ,name) VALUES(2 ,'b') | DELETE FROM public.tb1 WHERE id=2 | t        | public     | t
b1          | 9/291DA948 | 9/291DA9F8
     1 | 1585 |      0 |       2 | t      | 2023-03-27 15:09:31.79967+08  | UPDATE public.tb1 SET  WHERE id=2               | UPDATE public.tb1 SET  WHERE id=2 | t        | public     | t
b1          | 9/291DA9F8 | 9/291DAA78
     1 | 1586 |      0 |       4 | t      | 2023-03-27 15:10:15.078618+08 | ALTER TABLE public.tb1 DROP COLUMN age          |                                   | t        | pg_catalog | p
g_attribute | 9/291DCB98 | 9/291E0BF0
(5 rows)

修复数据

op_text列显示执行过 ALTER TABLE public.tb1 DROP COLUMN age，使用alter add 把它添加进来即可，但此列的数据无法恢复。

异常处理

无

cstore-列式存储（22.X版本）

简介

cstore插件是列式存储扩展，列式存储仅从磁盘读取相关的数据，可以更快速的分析统计数据。数据大小比普通表减少4-10倍的磁盘空间，能够更快速的将数据加载到内存处理。

优点：

压缩：磁盘占用空间减少4-10倍
列投影：只读取和查询相关的数据，提高IO性能
跳过索引：存储行组的最小最大统计信息，并使用它们跳过不相关的行

使用限制：

不支持 DELETE 删除数据
不支持 UPDATE 更新表
不支持 INSERT INTO 单行数据，需多行批量插入。因为插入操作时会flush，如果插入一行也会占用一个条带的磁盘空间，会导致性能下降的严重的磁盘浪费。

使用

CREATE EXTENSION cstore_fdw; -- 创建扩展
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw; -- 创建server指定外部数据
CREATE FOREIGN TABLE t1 -- 创建 FOREIGN 表
(
    customer_id TEXT,
    review_date DATE,
    review_rating INTEGER,
    review_votes INTEGER,
    review_helpful_votes INTEGER,
    product_id CHAR(10),
    product_title TEXT,
    product_sales_rank BIGINT,
    product_group TEXT,
    product_category TEXT,
    product_subcategory TEXT,
    similar_product_ids CHAR(10)[]
) SERVER cstore_server -- 指定server
OPTIONS(filename '/data/udb/cstore/cstb1.cstore',compression 'pglz'); -- 其它选项
\COPY t1 FROM '/data/udb/file/data.csv' WITH CSV; --导入数据

OPTIONS 参数

filename（可选）：表数据存储位置目录。默认值为 $UDBDATA/cstore_fdw
compression压缩（可选）：用于压缩价值流的压缩。默认值为 none，不压缩；可选值 pglz
stripe_row_count（可选）：每个条带的行数。默认值为 150000。减少它会减少用于加载数据和查询的内存量，但也会降低性能。
block_row_count（可选）：每个列块的行数。默认值为 10000。cstore 以块粒度压缩、创建，跳过索引并从磁盘读取。增加此值有助于压缩并减少从磁盘读取的结果。但是，较高的值也会降低跳过不相关行块的可能性。

空间对比

相同数据量下，cstore 列式存储占用磁盘空间比普通表减少4倍左右。

普通表大小

du -sh  /data/udb/udb22.4.21-1/data/udb/base/66354/
521M    /data/udb/udb22.4.21-1/data/udb/base/66354/
test=# SELECT pg_size_pretty(pg_table_size('t1'));
 pg_size_pretty 
----------------
 412 MB
(1 row)

列式存储表大小

du -sh  /data/udb/udb22.4.21-1/data/udb/cstore
100M    /data/udb/udb22.4.21-1/data/udb/cstore
cstore=# SELECT pg_size_pretty(cstore_table_size('t1'));
 pg_size_pretty 
----------------
 100 MB
(1 row)

查询对比

相同数据量下，cstore 列式存储查询比普通表性能提升 15倍左右。

test=# select count(*) from t1 where review_date ='2023-04-06';
 count 
-------
  4024
(1 row)
Time: 59.165 ms
test=# \c cstore
You are now connected to database "cstore" as user "unvdb".
cstore=# select count(*) from t1 where review_date ='2023-04-06';
 count 
-------
  4024
(1 row)
Time: 4.758 ms

columnar-列式存储（24.x）

使用

创建扩展以及示例表

CREATE EXTENSION "columnar";

CREATE TABLE columnar_table (
    id BIGINT,
    name TEXT,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) USING columnar;

可以在数据库的命令行界面使用命令查看当前表是否为列存表

\d+ columnar_table 
                                                       Table "public.columnar_table"
   Column   |            Type             | Collation | Nullable |      Default      | Storage  | Compression | Stats target | Description 
------------+-----------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
 id         | bigint                      |           |          |                   | plain    |             |              | 
 name       | text                        |           |          |                   | extended |             |              | 
 age        | integer                     |           |          |                   | plain    |             |              | 
 created_at | timestamp without time zone |           |          | CURRENT_TIMESTAMP | plain    |             |              | 
Access method: columnar

使用数据库软件将列存表迁移到其他库中（因其他库没有添加列存扩展所以自动转换为行存），然后查询。可以看到同样的结构同样的数据，其中占用的空间是不同的。

radius=# SELECT 
    (SELECT COUNT(*) FROM columnar_table) AS row_count,
    pg_size_pretty(pg_total_relation_size('columnar_table')) AS total_size;
 row_count | total_size 
-----------+------------
     10000 | 1984 kB
(1 row)

unvdb=# SELECT 
    (SELECT COUNT(*) FROM columnar_table) AS row_count,
    pg_size_pretty(pg_total_relation_size('columnar_table')) AS total_size;
 row_count | total_size 
-----------+------------
     10000 | 29 MB
(1 row)

限制

• 不支持逻辑复制 • 不支持ON CONFLICT子句 • 在某些情况下，索引可能会导致查询变慢，大部分情况下索引不是必须的 • 不支持BRIN索引

ud_similarity

介绍

ud_similarity是用于支持unvdb上的相似性查询的扩展。从某种意义上说，该实现紧密集成在 RDBMS 中，因为它定义了运算符<>，因此您可以使用 ~~~ 和！（这些运算符中的任何一个都表示相似性函数）。

ud_similarity有三个主要组成部分：

函数：实现文献中可用的相似性算法的一组函数。这些函数可以用作 UDF，并且将成为实现相似性运算符的基础;
运算符：在相似性函数顶部定义的一组运算符。他们使用相似性函数来获取相似性阈值，并将其值与用户定义的阈值进行比较，以确定它是否匹配;
会话变量：存储相似性函数参数的一组变量。这些变量可以在运行时定义。

安装和使用

在正式发布的unvdb中已包含ud_similarity，可直接使用：

unvdb=# CREATE EXTENSION ud_similarity;
CREATE EXTENSION
unvdb=#

如果不能创建扩展，可联系技术支持获取ud_similarity.zip：

以下为安装步骤：

$ unzip unvdb-tx-22.4.48-linux-x86_64-patch-ud_similarity.zip
$ cp unvdb-tx-22.4.48-linux-x86_64-patch-ud_similarity/lib/ud_similarity.so unvdb-tx-22.4.40-linux-x86_64/lib/
$ cp unvdb-tx-22.4.48-linux-x86_64-patch-ud_similarity/share/extension/* unvdb-tx-22.4.40-linux-x86_64/share/extension/    
$ cp unvdb-tx-22.4.48-linux-x86_64-patch-ud_similarity/share/extension/ud_similarity.conf unvdb-data/

# 修改unvdb-data/unvdbsvr.conf文件，添加如下内容：

include 'ud_similarity.conf'

# 重启unvdb服务：
$ unvdb-data/restart.sh

# 创建扩展：    
unvdb=# CREATE EXTENSION ud_similarity;
CREATE EXTENSION
unvdb=#

函数和运算符

此扩展支持一组相似性算法，每种算法都适用于特定域。

提供的算法如下：

L1距离（也被称为街区距离或曼哈顿距离）
余弦距离（Cosine Distance）
Dice系数（Dice Coefficient）
欧几里得距离（Euclidean Distance）
汉明距离（Hamming Distance）
Jaccard系数（Jaccard Coefficient）
Jaro距离（Jaro Distance）
Jaro-Winkler距离（Jaro-Winkler Distance）
Levenshtein距离（Levenshtein Distance），也被称为编辑距离（Edit Distance）
匹配系数（Matching Coefficient）
Monge-Elkan系数（Monge-Elkan Coefficient）
Needleman-Wunsch系数（Needleman-Wunsch Coefficient）
重叠系数（Overlap Coefficient）
Q-gram距离（Q-Gram Distance）
Smith-Waterman系数（Smith-Waterman Coefficient）
Smith-Waterman-Gotoh系数（Smith-Waterman-Gotoh Coefficient）
Soundex距离（Soundex Distance）

算法	函数	操作符	使用索引？	参数
L1距离	block(text, text) returns float8	~++	yes	ud_similarity.block_tokenizer (enum) ud_similarity.block_threshold (float8) ud_similarity.block_is_normalized (bool)
余弦距离	cosine(text, text) returns float8	~##	yes	ud_similarity.cosine_tokenizer (enum) ud_similarity.cosine_threshold (float8) ud_similarity.cosine_is_normalized (bool)
Dice系数	dice(text, text) returns float8	\~-\~	yes	ud_similarity.dice_tokenizer (enum) ud_similarity.dice_threshold (float8) ud_similarity.dice_is_normalized (bool)
欧几里得距离	euclidean(text, text) returns float8	~!!	yes	ud_similarity.euclidean_tokenizer (enum) ud_similarity.euclidean_threshold (float8) ud_similarity.euclidean_is_normalized (bool)
汉明距离	hamming(bit varying, bit varying) returns float8 hamming_text(text, text) returns float8	\~@\~	no	ud_similarity.hamming_threshold (float8) ud_similarity.hamming_is_normalized (bool)
Jaccard系数	jaccard(text, text) returns float8	~??	yes	ud_similarity.jaccard_tokenizer (enum) ud_similarity.jaccard_threshold (float8) ud_similarity.jaccard_is_normalized (bool)
Jaro距离	jaro(text, text) returns float8	~%%	no	ud_similarity.jaro_threshold (float8) ud_similarity.jaro_is_normalized (bool)
Jaro-Winkler距离	jarowinkler(text, text) returns float8	~@@	no	ud_similarity.jarowinkler_threshold (float8) ud_similarity.jarowinkler_is_normalized (bool)
Levenshtein距离	lev(text, text) returns float8	~==	no	ud_similarity.levenshtein_threshold (float8) ud_similarity.levenshtein_is_normalized (bool)
匹配系数	matchingcoefficient(text, text) returns float8	~^^	yes	ud_similarity.matching_tokenizer (enum) ud_similarity.matching_threshold (float8) ud_similarity.matching_is_normalized (bool)
Monge-Elkan系数	mongeelkan(text, text) returns float8	~\|\|	no	ud_similarity.mongeelkan_tokenizer (enum) ud_similarity.mongeelkan_threshold (float8) ud_similarity.mongeelkan_is_normalized (bool)
Needleman-Wunsch系数	needlemanwunsch(text, text) returns float8	\~#\~	no	ud_similarity.nw_threshold (float8) ud_similarity.nw_is_normalized (bool)
重叠系数	overlapcoefficient(text, text) returns float8	~**	yes	ud_similarity.overlap_tokenizer (enum) ud_similarity.overlap_threshold (float8) ud_similarity.overlap_is_normalized (bool)
Q-gram距离	qgram(text, text) returns float8	~~~	yes	ud_similarity.qgram_threshold (float8) ud_similarity.qgram_is_normalized (bool)
Smith-Waterman系数	smithwaterman(text, text) returns float8	\~=\~	no	ud_similarity.sw_threshold (float8) ud_similarity.sw_is_normalized (bool)
Smith-Waterman-Gotoh系数	smithwatermangotoh(text, text) returns float8	\~!\~	no	ud_similarity.swg_threshold (float8) ud_similarity.swg_is_normalized (bool)
Soundex距离	soundex(text, text) returns float8	\~*\~	no

这几个参数控制ud_similarity函数和运算符的行为。我没有详细解释每个参数，因为它们可以分为三类：tokenizer、 threshold 和normalized。

Tokenizer：控制字符串的标记化方式。有效值为 alnum、gram、word 和 camelcase。所有标记都是小写的（此选项可以在编译时设置;）。默认值为 alnum;
- alnum：分隔符是任何非字母数字字符。这意味着标记中只接受标准 C 语言环境中的字母字符和数字（0-9）。例如，字符串“Euler_Taveira_de_Oliveira 22/02/2011”被标记为“Euler”、“Taveira”、“de”、“Oliveira”、“22”、“02”、“2011”;
- gram：n-gram 是长度为 n 的子序列。从字符串中提取 n 个 gram 可以通过使用 sliding-by-one 技术来完成，即将长度为 n 的窗口从字符串中滑动一个字符。例如，字符串“euler taveira”（使用 n = 3）被标记为 “eul”、“ule”、“ler”、“er ”、“r t”、“ta”、“tav”、“ave”、“vei”、“eir” 和 “ira”。有一些作者认为 n-gram 将 “ e”、“eu”、“ra ” 和 “a” 添加到标记集，称为完整的 n-gram（此选项可以在编译时设置）;
- word：分隔符是空格字符（空格、换向表单、换行符、回车符、水平制表符和垂直制表符）。例如，字符串“Euler Taveira de Oliveira 22/02/2011”被标记为“Euler”、“Taveira”、“de”、“Oliveira”和“22/02/2011”;
- camelcase：分隔符是大写字符，但它们也作为第一个标记字符包含在内。例如，字符串“EulerTaveira de Oliveira”被标记为“Euler”、“Taveira de ”和“Oliveira”。
threshold：控制结果集的灵活性。运算符使用这些值来匹配字符串。对于每对字符串，如果计算值（使用相应的相似度函数）大于或等于阈值，则存在匹配项。取值范围为 0.0 到 1.0。默认值为 0.7;
和normalized：控制是否归一化相似系数/距离（介于 0.0 和 1.0 之间）。运算符会自动使用规范化值来匹配字符串，也就是说，此参数仅在使用相似性函数时才有意义。默认值为 true。

示例

在运行时设置参数：

unvdb=# show ud_similarity.block_threshold;
ud_similarity.block_threshold
-------------------------------
0.7
(1 row)

unvdb=# set ud_similarity.block_threshold to 0.5;
SET
unvdb=# show ud_similarity.block_threshold;
ud_similarity.block_threshold
-------------------------------
0.5
(1 row)

unvdb=# set ud_similarity.block_tokenizer to camelcase;
SET
unvdb=# set ud_similarity.block_is_normalized to false;
SET

以简单表格为例：

unvdb=# create table foo (a text);
CREATE TABLE
unvdb=# insert into foo values('Euler'),('Oiler'),('Euler Taveira de Oliveira'),('Maria Taveira dos Santos'),('Carlos Santos Silva');
INSERT 0 5
unvdb=#
unvdb=# create table bar (b text);
CREATE TABLE
unvdb=# insert into bar values('Euler T. de Oliveira'),('Euller'),('Oliveira, Euler Taveira'),('Sr. Oliveira');
INSERT 0 4

示例1：使用相似函数cosine、jaro 和 euclidean。

unvdb=# select a, b, cosine(a,b), jaro(a, b), euclidean(a, b) from foo, bar;
            a             |            b            |       cosine        |        jaro        |      euclidean
--------------------------+-------------------------+---------------------+--------------------+---------------------
Euler                     | Euler T. de Oliveira    |                 0.5 |               0.75 |  0.5799159747915971
Euler                     | Euller                  |                   0 | 0.9444444444444444 |                   0
Euler                     | Oliveira, Euler Taveira |  0.5773502691896258 | 0.6057971014492753 |   0.552786404500042
Euler                     | Sr. Oliveira            |                   0 | 0.5055555555555555 |  0.2254033307585167
Oiler                     | Euler T. de Oliveira    |                   0 | 0.4722222222222222 | 0.45767385545335953
Oiler                     | Euller                  |                   0 |                0.7 |                   0
Oiler                     | Oliveira, Euler Taveira |                   0 |  0.672463768115942 |  0.3675444679663242
Oiler                     | Sr. Oliveira            |                   0 | 0.6722222222222223 |  0.2254033307585167
Euler Taveira de Oliveira | Euler T. de Oliveira    |                0.75 | 0.7980701754385964 |                0.75
Euler Taveira de Oliveira | Euller                  |                   0 | 0.6777777777777777 | 0.45767385545335953
Euler Taveira de Oliveira | Oliveira, Euler Taveira |  0.8660254037844387 | 0.7731884057971014 |                 0.8
Euler Taveira de Oliveira | Sr. Oliveira            | 0.35355339059327373 | 0.5922222222222222 |  0.5527864045000421
Maria Taveira dos Santos  | Euler T. de Oliveira    |                   0 | 0.6023504273504273 |                 0.5
Maria Taveira dos Santos  | Euller                  |                   0 | 0.3055555555555556 | 0.45767385545335953
Maria Taveira dos Santos  | Oliveira, Euler Taveira |  0.2886751345948129 | 0.5350241545893719 |   0.552786404500042
Maria Taveira dos Santos  | Sr. Oliveira            |                   0 | 0.6342592592592593 |   0.452277442494834
Carlos Santos Silva       | Euler T. de Oliveira    |                   0 | 0.5421052631578946 | 0.47084973778708183
Carlos Santos Silva       | Euller                  |                   0 | 0.3128654970760234 |  0.3675444679663242
Carlos Santos Silva       | Oliveira, Euler Taveira |                   0 | 0.6066615814899567 |  0.4226497308103742
Carlos Santos Silva       | Sr. Oliveira            |                   0 | 0.5077276524644945 | 0.37982632705395764
(20 rows)

示例 2：使用运算符 levenshtein （~==）并在运行时更改其阈值。

unvdb=# show ud_similarity.levenshtein_threshold;
ud_similarity.levenshtein_threshold
-------------------------------------
0.7
(1 row)

unvdb=# select a, b, lev(a,b) from foo, bar where a ~== b;
            a             |          b           |        lev
---------------------------+----------------------+--------------------
Euler                     | Euller               | 0.8333333333333334
Euler Taveira de Oliveira | Euler T. de Oliveira |               0.76
(2 rows)

unvdb=# set ud_similarity.levenshtein_threshold to 0.5;
SET
unvdb=# select a, b, lev(a,b) from foo, bar where a ~== b;
            a             |          b           |        lev
---------------------------+----------------------+--------------------
Euler                     | Euller               | 0.8333333333333334
Oiler                     | Euller               |                0.5
Euler Taveira de Oliveira | Euler T. de Oliveira |               0.76
(3 rows)

示例 3：使用运算符 qgram （~~~）并在运行时更改其阈值。

unvdb=# show ud_similarity.qgram_threshold;
ud_similarity.qgram_threshold
-------------------------------
0.7
(1 row)

unvdb=# select a, b,qgram(a, b) from foo, bar where a ~~~ b;
            a             |            b            |       qgram
--------------------------+-------------------------+--------------------
Euler                     | Euller                  |                0.8
Euler Taveira de Oliveira | Euler T. de Oliveira    | 0.7755102040816326
Euler Taveira de Oliveira | Oliveira, Euler Taveira | 0.8076923076923077
(3 rows)

unvdb=# set ud_similarity.qgram_threshold to 0.35;
SET
unvdb=# select a, b,qgram(a, b) from foo, bar where a ~~~ b;
            a             |            b            |        qgram
--------------------------+-------------------------+---------------------
Euler                     | Euler T. de Oliveira    | 0.41379310344827586
Euler                     | Euller                  |                 0.8
Oiler                     | Euller                  |                 0.4
Euler Taveira de Oliveira | Euler T. de Oliveira    |  0.7755102040816326
Euler Taveira de Oliveira | Oliveira, Euler Taveira |  0.8076923076923077
Euler Taveira de Oliveira | Sr. Oliveira            | 0.43902439024390244
(6 rows)

示例 4：使用一组使用相同阈值（0.7）的运算符来说明某些相似性函数已适用于某些数据域。

unvdb=# select * from bar where b ~@@ 'euler'; -- jaro-winkler operator
          b
----------------------
Euler T. de Oliveira
Euller
(2 rows)

unvdb=# select * from bar where b ~~~ 'euler'; -- qgram operator
b
---
(0 rows)

unvdb=# select * from bar where b ~== 'euler'; -- levenshtein operator
b
--------
Euller
(1 row)

unvdb=# select * from bar where b ~## 'euler'; -- cosine operator
b
---
(0 rows)

odbc_fdw

简介

此 unvdb 扩展为使用 Open Database Connectivity(ODBC)的远程数据库。

Linux平台安装

安装unixODBC

sudo yum install unixODBC-devel

配置unixODBC

查看unixODBC配置

[test@localhost ~]$ odbcinst -j 
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/test/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[test@localhost ~]$

配置 UNVDB ODBC 驱动管理器

sudo vi /etc/odbcinst.ini 添加如下内容

Driver 路径修改为安装包实际路径

[UNVDB]
Description     = ODBC for UNDB-TX
Driver          = /home/test/unvdb-tx-24.2.11-linux-aarch64/Interface/ODBC/unvdbodbcw.so  
Setup           = /usr/lib64/libodbcpsqlS.so
Driver64        = /home/test/unvdb-tx-24.2.11-linux-aarch64/Interface/ODBC/unvdbodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1

配置数据源

vi ~/.odbc.ini 添加如下内容

[REMOTE_UNVDB]
Driver = UNVDB
Description = Test on UNVDB
#以下信息根据实际修改
Database = unvdb
Servername = 127.0.0.1
UserName = unvdb
Port = 5678

测试示例

unvdb=# CREATE EXTENSION odbc_fdw;
CREATE EXTENSION
unvdb=# CREATE SERVER loopback FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'REMOTE_UNVDB');
CREATE SERVER
#修改为实际用户名和密码
unvdb=# CREATE USER MAPPING FOR CURRENT_USER SERVER loopback OPTIONS (odbc_UID 'unvdb', odbc_PWD 'XXX');
CREATE USER MAPPING
unvdb=# CREATE TABLE test (c1 int NOT NULL, c2 text);
CREATE TABLE
unvdb=# CREATE FOREIGN TABLE remote_test (
 c1 int NOT NULL, c2 text
) SERVER loopback OPTIONS (schema 'public', table 'test');
CREATE FOREIGN TABLE
unvdb=# insert into test values(1,'test');
INSERT 0 1
unvdb=# select * from remote_test;
 c1 |  c2  
----+------
  1 | test
(1 row)


#清理
DROP FOREIGN TABLE remote_test;
drop table test;
DROP USER MAPPING FOR CURRENT_USER SERVER loopback;
DROP SERVER loopback;
DROP EXTENSION odbc_fdw;

定制插件

oraudb

unvdbGIS

unvdbgis

unvdbgis安装

unvdbgis_raster

unvdbgis_topology

unvdbgis_tiger_geocoder

unvdbgis_sfcgal

ud_trashcan-闪回回收站

简介

前提要求

注意事项

安装

启用

配置

验证安装

使用示例

闪回回收站开关功能

闪回drop表数据

闪回truncate表数据

清空回收站

关闭回收站

ud_dirtyread-查看脏数据

简介

前提要求

注意事项

安装

启用

验证安装

使用示例

创建测试数据

查看当前数据元组信息

查看是否为脏数据，dead列为f,表示非死数据

删除数据

修改数据

查看已删除未清理的脏数据

删除列

查看已删除列的脏数据，此时要用 dropped_n 来代替

ud_walminer-从wal日志中提取sql实现数据修复

简介

前提要求

注意事项

安装/启用

安装

启用

验证安装

使用说明

基础操作

解析操作

实践演示

delete 操作恢复

模拟删除操作

配置ud_walminer

DDL操作恢复

模拟删除列操作

恢复配置

修复数据

异常处理

cstore-列式存储（22.X版本）

简介

使用

空间对比

普通表大小

列式存储表大小

查询对比

columnar-列式存储 （24.x）

使用

限制

ud_similarity

介绍

安装和使用

函数和运算符

示例

odbc_fdw

简介

Linux平台安装

安装unixODBC

配置unixODBC

查看unixODBC配置

columnar-列式存储（24.x）