LightDB-A 23.2 release note

功能特性：

oralce语法兼容增强

Oracle兼容模式支持，LightDB-A支持Oracle兼容模式，默认是启用状态。

-- 创建oracle兼容模式的库(默认)
create database db1;

-- 创建oracle兼容模式的库
create database db2 with lightdb_syntax_compatible_type 'oracle';

-- 创建非oracle兼容模式的库
create database db3 with lightdb_syntax_compatible_type 'off';

MERGE INTO语法支持


CREATE TABLE target (tid integer, balance integer);
CREATE TABLE source (sid integer, delta integer);

MERGE INTO target t
USING source AS s
ON t.tid = s.sid
WHEN NOT MATCHED THEN
    INSERT VALUES (4, 4)
WHEN MATCHED THEN
    UPDATE SET balance = 0

DBLINK语法支持, 支持使用@语法访问外部表，其中外部表使用fdw创建，并且表名前缀需要server名称。

create server db100_fdw foreign data wrapper postgres_fdw 
options(host '192.168.237.145',port '59000',dbname 'db100');

create user mapping for postgres 
server db100_fdw 
options(user 'gpadmin', password 'gp123456');

--  创建外部表
create foreign table db100_fdw_t100(a int, b text) 
server db100_fdw 
options(schema_name 'public',table_name 't100');

-- 使用@语法访问外部表
select * from t100@db100_fdw;

oracle(+)关联语法支持，功能与left join或right join等价。

create table t1(key1 int, key2 int);
create table t2(key1 int, key2 int);

-- +关联语法
select * from t1 a, t2 b  where a.key1=b.key1(+);

Q转义语法支持

test_createdbabc=# select q'[this isn't a good news  $$$$]' ;
        ?column?           
------------------------------
this isn't a good news  $$$$
(1 row)

支持pivot,unpivot行列转换函数

create table t1(name varchar(40),chinese int,math int);
insert into t1 values('zhangsan',90,100);
insert into t1 values('lisi',88,99);

select * from t1 unpivot (score for course in(chinese,math));

存储过程支持goto语句

CREATE or REPLACE FUNCTION foreach_test(anyarray)
RETURNS void as $$
DECLARE x int;
BEGIN
foreach x in array $1
loop
    raise notice '%', x;
    goto abc;
end loop;
<<abc>>
raise notice 'end all';
END;
$$ language plpgsql;

select foreach_test(ARRAY[1,2,3,4]);

INSERT ALL语法支持

create table t2(a int, b int);
create table t3(a int, b int);
insert into t2 values (1,1), (2,1), (3,1), (4,1);

-- INSERT ALL语句
insert all into t3(a,b) select a,b from t2;

listagg 支持within group, over

create table t4 (a int, b varchar(10));
-- listagg
SELECT LISTAGG(t.b, ',') FROM t4 t GROUP BY t.a;

sysdate支持语句级别时间

testdb=# begin;
BEGIN
testdb=# select sysdate from dual;
       sysdate       
---------------------
 2023-06-07 09:55:44
(1 row)

testdb=# select sysdate from dual;
       sysdate       
---------------------
 2023-06-07 09:55:47
(1 row)

支持modify column和drop primary key语法

create table t5(id int primary key,name text);
alter table t5 drop primary key;

子查询可以支持不指定别名

兼容Oracle分区表语法

CREATE TABLE t6 
(
    a int,
    b float,
    c date,
    d timestamp,
    e varchar(20)
) PARTITION BY LIST(e)
(
    PARTITION p1 VALUES ('0001', '0002', '0003', '0004', '0005'),
    PARTITION p2 VALUES ('0006', '0007', '0008', '0009'),
    PARTITION p3 VALUES ('0010', '0011')
);

兼容oracle operator || 字符串拼接语法

select 'a' || 1;

oracle is null兼容

create table test_tb (name varchar(40) not null);

-- 违反not null约束
insert into test_tb values ('');

rowid支持

每张表有默认的rowid的隐藏字段，需要显式指定才可以查询，并且支持tid扫描。

explain (costs false)  select rowid,id,name 
from t1 where rowid='2,(0,1)';
                        QUERY PLAN                         
-----------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Tid Scan on t1
         TID Cond: (ctid = '(0,1)'::tid)
         Filter: ((gp_segment_id)::numeric = '2'::numeric)
 Optimizer: Postgres query optimizer
(5 rows)

支持常用优化器提示

内置lt_hint_plan 插件，支持如use_hash, user_merge, leading等，更多细节参考 lt_hint_plan

EXPLAIN (COSTS false) 
SELECT/*+leading(t2 t3 t1) */ * FROM t1, t2, t3 
WHERE t1.id = t2.id and t2.id=t3.id;

                        QUERY PLAN                        
----------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Nested Loop
         ->  Merge Join
               Merge Cond: (t2.id = t3.id)
               ->  Index Scan using t2_pkey on t2 @"lt#0"
               ->  Sort
                     Sort Key: t3.id
                     ->  Seq Scan on t3 @"lt#0"
         ->  Index Scan using t1_pkey on t1 @"lt#0"
               Index Cond: (id = t2.id)
 Optimizer: Postgres query optimizer
(11 rows)

内部默认集成如下插件，可点击对应链接查看插件的详细介绍。

优化器提示插件 lt_hint_plan
oracle兼容插件 orafce
SQL审计插件 pgaudit
存储过程插件 plpgsql
LightDB兼容特性插件 ltfce

内核增强

字符串数值类型兼容，在函数和操作符使用中，对字符串和数值类型进行合适转换尽力匹配原则，避免找不到函数或函数不唯一的情况。

-- 使用字符串类型调用数值运算函数
select |/'20'::text;
-- 使用使用数值类型调用字符串运算函数
select '9001.2'::numeric like '9001%';