LightDB 13.8-22.3 is based on PostgreSQL 13.8 and contains a variety of fixes and new feature from LightDB 13.3-22.2.
Release date: 2022-09-30
Support q escape. for example:
select q'!name LIKE '%DBMS_%%'!';
?column?
----------------------
name LIKE '%DBMS_%%'
(1 row)
Rownum performance optimization. Like oracle, support count stop key to improve rownum performance.
lightdb@lt_test=# explain select * from public.test where rownum < 3;
QUERY PLAN
----------------------------------------------------------------
Count StopKey (cost=0.00..0.03 rows=2 width=4)
-> Seq Scan on test (cost=0.00..159.75 rows=11475 width=4)
(2 rows)
libpq supports getting LightDB version number.
listagg supports for analytic functions.
There is a description of listagg in the orafce chapter.
NVL function parameters support more parameter combinations such as strings and numbers.
Modify the default database to lt_test,
Replace the original default database postgres.
lt_upgrade supports upgrading LightDB from version 22.2 to version 22.3
(lt_upgrade will check the consistency of lightdb_syntax_compatible_type
while upgrading, notice that if the value are both 'oracle', lt_upgrade is not recommended).
see lt_upgrade.
Add new GUC parameters nls_date_format
and nls_timestamp_format for Oracle time function compatibility,
see nls_date_format and
nls_timestamp_format for more details.
search_path contains lt_catalog by default.
see Search Path.
schema oracle and mysql are processed specially,
can only be set when they are consistent with the lightdb_syntax_compatible_type.
Support for non-reserved keywords as column aliases
(When there is no keyword as and no quotation marks).
see Key Words.
select 3 type;
type
------
3
(1 row)
when lightdb_enable_default_tablespace is enabled, add the default tablespace lt_users.
The lt_users is the default tablespace of
other databases created by ourselves.
(unless overridden by a TABLESPACE clause in CREATE DATABASE).
Its storage path is in the same level directory of the instance path.
This can ensure that the amount of data under the instance path is not too large.
see Tablespaces.
lightdb_service.py supports any single node start stop.
Fixed lt_probackup recovery failure for the amount of data exceeds 2G.
Support for modifying column by modify column clause like oracle.
see ALTER TABLE for details.
Support the drop priamry key clause when alter table. see ALTER TABLE for details.
Add function and table for modifying tables that have view dependencies. see orafce for details.
Remove read-only table related features.
lt_dump enhancements, and many of new parameters shown below have been added.
see lt_dump.
Add parameter --lt-exclude-lightdb-objects and
parameter --lt-dump-lightdb-tables,
To solve the problem of database object conflicts created by
the LightDB built-in plug-in during export and import and
to solve the problem of overwriting the table created by
the LightDB built-in plug-in during import.
Add parameter --lt-disable-auto-analyze,
To solve the problem of lt_restore multithreaded import deadlock.
PL/oraSQL enhancements, and many of new features shown below have been added.
Package supports backup using lt_dump.
see lt_dump.
Package supports associative arrays.
see Section 42.8.
Support table function.
see Table function.
Support nested table.
see Nested Tables.
Support error handling.
see Error Handling.
Support stored procedure(including stored procedure in package) and anonymous block
after opening the cursor. in the transaction block,
you can continue the fetch operation after
commit or rollback.
see cursor-with-transcations.
Fix open cursor parameter is inconsistent with the defined cursor parameter. see cursor-default-values.
orafce Enhancements:
Add new oracle's Built-in package:
dbms_metadata, dbms_job, dbms_lock,
dbms_obfuscation_toolkit, utl_url, utl_encode,
utl_raw and dbms_snapshot.
Improve the original built-in package:
dbms_application_info, dbms_utility and dbms_lob.
Add new oracle's system view like [DBA/ALL/USER]_TABLES,
[DBA/ALL/USER]_TAB_COLUMNS and [DBA/ALL/USER]_INDEXES.
Add new oracle's function: BIN_TO_NUM,
BITOR/BITXOR, REMAINDER,
ROUND_TIES_TO_EVEN, INSTRB,
REGEXP_INSTR/REGEXP_REPLACE, REPLACE,
SOUNDEX, MONTHS_BETWEEN,
TZ_OFFSET, CONVERT, NUMTOYMINTERVAL,
TO_BLOB, NLS_CHARSET_ID /NLS_CHARSET_NAME,
SYS_CONTEXT/USERENV, EMPTY_CLOB,
ORA_HASH and VSIZE.
Add new oracle's agg function: ANY_VALUE, BIT_AND_AGG/BIT_OR_AGG/BIT_XOR_AGG, KURTOSIS_POP/KURTOSIS_SAMP
and SKEWNESS_POP/SKEWNESS_SAMP.
see orafce for details.
Oracle compatibility enhancements, and many of new features shown below have been added.
Support Oracle partition syntax. see CREATE TABLE and ALTER TABLE.
Support statement rollback. see lt_statement_rollback.
Support raise_application_error() function.
see Section 42.8.
listagg supports analysis functions.
the orafce chapter.
Enhance compatibility between empty string and NULL. see Section 17.11.
MySQL compatibility enhancements, and many of new features shown below have been added.
Support year() function.
Support day() function.
Support date_format() function.
Support str_to_date() function.
Support from_unixtime() function.
Support unix_timestamp() function.
Support ifnull() function.
Add interval constant compatibility.
Support date_add() function.
Support date_sub() function.
Support dayofweek() function.
Support weekofyear() function.
Add cast type conversion.
Support compress() function.
Support uncompress() function.
Support timestamp and string comparison.
Fix smallint error when using 'is true' or '= true'.
Fix select and group by field inconsistent report error.
Fix distinct and order by field inconsistent report error.
see the myfce chapter for for details.
JDBC driver library, We fixed some of the issues and enhanced data type compatibility:
Support numeric data type convert to Java Integer.
Add connection param extendSchema, The default value is 'lt_catalog,oracle' when currentSchema is set, extendSchema will automatic append to currentSchema's value.
Remove expiration time limit.
Support MySQL SQL_CALC_FOUND_ROWS and
SELECT FOUND_ROWS() feature，only when they are executed sequentially.