Release date: 2022-04-06
This release contains a variety of changes from LightDB 13.3-21.3.
Oracle compatibility enhancements, and many of new features shown below have been added.
If only one subquery is used in the SELECT FROM clause, you do not need to use the as clause to explicitly specify an alias for the subquery. See SELECT for more details.
You can use ROWNUM as the filter condition in the WHERE clause of UPDATE and DELETE statements to specify the updated or deleted rows, for example:
UPDATE mytable SET column = value WHERE ROWNUM = rownum;
DELETE FROM mytable WHERE ROWNUM = rownum;
You can also use ROWNUM to assign unique values to each row of a table, as in this example:
UPDATE mytable SET column = ROWNUM;
To use this feature, you need to set lightdb_syntax_compatible_type to oracle, for example:
SET lightdb_syntax_compatible_type = 'oracle';
See Section 7.9 for more details.
Support MERGE syntax, MERGE performs actions
that modify rows in the target_table_name,
using the data_source.
MERGE provides a single SQL statement that can
conditionally INSERT or UPDATE rows, a task
that would otherwise require multiple procedural language statements.
See MERGE for more details.
support SYSTIMESTAMP function, SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE. For example:
SELECT SYSTIMESTAMP FROM DUAL;
SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') FROM DUAL;
See Section 9.9.4 for more details.
If you have set lightdb_syntax_compatible_type to oracle, then the result of expression "value || NULL" will be the value, otherwise the result will be NULL.
If you have set lightdb_syntax_compatible_type to oracle, then the result of expression "''::text is NULL" will be true, otherwise the result will be false.
The LISTAGG function in orafce extension now support DISTINCT and WITHIN GROUP syntax, you can use it as in this example:
SELECT LISTAGG(DISTINCT column, ',') WITHIN GROUP (ORDER BY column) FROM mytable;
See orafce for more details.
Add new keyword MINUS for SELECT statements and as the alias of EXCEPT, they are completely equivalent. See SELECT for more details.
GUC parameters lightdb_enable_rowid and lightdb_enable_rownum are removed, and set lightdb_syntax_compatible_type to oracle to instead.
pg_hint_plan enhancements，and several new hints such as USE_HASH_AGGREGATION, Semijoin, Antijoin and Swap_join_inputs have been added. Besides, it also realizes the support of canopy for distributed database. See pghint_plan for more details.
Support Transparent Data Encryption(TDE). TDE can (and can only) be enabled by specifying the -K option when generating a data directory using initdb or lt_initdb, and this option passes in a 32-bytes hexadecimal key string, which is used for encryption and decryption. See encryption key command for more details.
Adding new extension lt_sm to support sm2, sm3 and sm4 encryption. lt_sm is a extension for LightDB that provides a series of functions for sm2, sm3 and sm4 encryption algorithms. Data can be encrypted and decrypted using the above functions. See lt_sm for more details.
Adding New GUC parameters lightdb_tsearch_non_stopwords and lightdb_tsearch_word_superpose for full text search.
LightDB full text search has many built-in stop words. Words connected by stop words will be split into multiple independent words by to_tsvector function, for example, "2022-04-10" will be split into "2022", "04" and "10".
lightdb_tsearch_non_stopwords is used to customize non-stop words, which is just the opposite of stop words, that is, non-stop words are considered to be an integral part of a word and thus will not be split into multiple independent words by to_tsvector function any more. Still the above example, if you set '-' as non-stop words, you will only get a result of "2022-04-10".
lightdb_tsearch_word_superpose is used to overlay the effect of using stop words and non-stop words. For example, if you set lightdb_tsearch_word_superpose to on and set lightdb_tsearch_non_stopwords to '-', you will get the results including "2022", "04", "10" and "2022-04-10" at the same time when using to_tsvector("2022-04-10").
See Section 12.1.3 for more details.
Adding new GUC parameters lt_ring_buffers to set the maximum amount of memory used for ring buffers within each database session. When running a sequential scan query that needs to access a large number of pages(more than a quarter of the total pages of shared_buffers) just once, a special access strategy called bulk-read is used. A page that has been touched only by such a scan is unlikely to be needed again soon, so instead of blowing out the entire buffer cache, a small ring of buffers that specified by lt_ring_buffers is allocated and those buffers are reused for the whole scan. This also implies that much of the write traffic caused by such a statement will be done by the backend itself and not pushed off onto other processes.
pg_profile enhancements, adding new wait_event_type named DBCpu in wait event statistics, which indicate how much cpu time the user activity spends on database. See pgprofile for more details.
pg_cron enhancements, support three task scheduling modes: asap, next interval and fixed interval.
asap: Each task creates only one session and corresponds to a task queue, and multiple tasks can be stored in the queue. If the subsequent scheduling cycle comes during the execution of the current task, they will be put into the queue. At the same time, the current execution will complete all tasks in the queue one by one.
next interval: Each task creates only one session and corresponds to a task queue, and only one task is stored in the queue. If the subsequent scheduling cycle comes during the execution of the current task, they will not be put into the queue, but the next scheduling cycle after current task execution.
fixed interval: This mode is similar to next interval, but the difference is that if the subsequent scheduling cycle comes during the execution of the current task, a separate session will be created for the subsequent scheduling to execute, that is, multiple scheduling tasks will be executed in parallel, but the maximum number of parallel executions is cron.max_connections_per_task.
In addition, the task timeout function is also added. If the task execution time exceeds the specified maximum time, the task will be forcibly interrupted and cron.job_run_details will record relevant error messages. The default timeout is 15s, which is determined by cron.task_running_timeout. This parameter can only take effect when the database is started.
See lt_cron for more details.
Adding new GUC parameters pg_prewarm.lt_autoprewarm_dbs,
pg_prewarm.lt_autoprewarm_tables and
pg_prewarm.lt_autoprewarm_indexes for pg_prewarm extension,
which are used to selectively prewarm the specified database, table and index
automatically when LightDB is started. See lt_prewarm for more details.
Canopy extension is enabled by default now.
New extension pg_show_plans is integrated and disabled by default. See lt_show_plans for more details.
Automatically ANALYZE when execute CREATE INDEX to collect statistics.
UPDATE and DELETE statement allow use LIMIT clause to limit the rows will be updated and deleted, for example:
UPDATE mytable SET column = value LIMIT n;
DELETE FROM mytable WHERE column > value LIMIT m, n;
If you use function(include bulit-in function and user defined function) to specify the default value of a column in CREATE TABLE statement, the function parameters can be other column names, variable names, and expressions containing other column names and variable names, for example:
CREATE FUNCTION myfunc(v integer)
RETURNS integer AS $$
BEGIN
RETURN v + 10;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE mytable (
id int default 10,
key int default myfunc(id),
v1 int default key,
v2 int default key + myfunc(key)
);
See CREATE TABLE for more details.
When using "CREATE TABLE AS SELECT colname AS alias" to create a table, ltapk is not allowed as the alias. See CREATE TABLE for more details.
lt_standby_forward (EXPERIMENTAL) is a module which forwards SQL statements
from a hot standby to primary, providing a facility to reduce loading of primary
and allow clients query from standby. For example, client can send INSERT,
CREATE to a hot standby. See lt_standby_forward for more details.
The sqlprompt format of ltsql is adjusted to username@database.