DEV Community: tongxi

openGauss dolphin-bit-string-types

tongxi — Sun, 23 Apr 2023 02:38:36 +0000

Compared with the original openGauss, Dolphin modifies the bit string types as follows:

The data of the bit type is of variable length up to the maximum length n. Longer strings will be rejected. The data of the bit varying type is of variable length up to the maximum length n. Longer strings will be rejected.
If one explicitly casts a bit-string value to bit(n), it will be truncated or zero-padded on the left to be exactly n bits, without raising an error.
""
--Create a table.
openGauss=# CREATE TABLE bit_type_t1
(
BT_COL1 INTEGER,
BT_COL2 BIT(3),
BT_COL3 BIT VARYING(5)
) ;

--Data is converted if it exceeds the length of this data type.
openGauss=# INSERT INTO bit_type_t1 VALUES(2, B'1000'::bit(3), B'101');

--View data.
openGauss=# SELECT * FROM bit_type_t1;
bt_col1 | bt_col2 | bt_col3
---------+---------+---------
2 | 100 | 101
(2 rows)

--If the length of a character string is insufficient, the character string is converted to bit(n) and zeros are padded on the left.
openGauss=# SELECT B'10'::bit(4);

bit

000010
(1 row)

--Delete a table.
openGauss=# DROP TABLE bit_type_t1;

openGauss M*-Compatible Time Types

tongxi — Sun, 23 Apr 2023 02:37:51 +0000

Compared with the original openGauss, Dolphin modifies the date/time types as follows:

The performance of the date, time, datetime, and timestamp types are modified.
The year data type is added.
Note: Due to the inherent features of the openGauss, the openGauss cannot be fully compatible with all features of the M* time data type. Therefore, you need to use the features according to the requirements in this document. Do not use the features that are not described in this document. In addition, the compatible features cover the requirements in most scenarios.

The following table lists the basic attributes after the time data type is compatible with the M* database.

Type Description Storage Space Value Range (for Users) Precision Range Remarks
date Indicates a date. 4 bytes 4713 BC ~ 5874897 AD - (1) The input must be a valid date. The month or number of days cannot be 0. (2) If the year is greater than or equal to 10000, the value must be in the YYYY-MM-DD format. (3) If BC or AD is not specified for the input data, AD is used by default.
time(p) Indicates the time or a period of time (hours, minutes, and seconds) of a day. p indicates the precision. 8 bytes -838:59:59[.frac] ~ 838:59:59[.frac] p indicates the precision after the decimal point. The value ranges from 0 to 6. If this parameter is not specified, the default value 0 is used. -
datetime(p) Indicates date and time without time zone information. p indicates the precision. 8 bytes 0 AD ~ 294276 AD p indicates the precision after the decimal point. The value ranges from 0 to 6. If this parameter is not specified, the default value 0 is used. (1) The input must be a valid date. The month or number of days cannot be 0. (2) If the entered year is greater than or equal to 10000, the year must be in the YYYY-MM-DD format.
timestamp(p) Date and time with time zone information. p indicates the precision. 8 bytes 0 AD ~ 294276 AD p indicates the precision after the decimal point. The value ranges from 0 to 6. If this parameter is not specified, the default value 0 is used. (1) The input must be a valid date. The month or number of days cannot be 0. (2) Note that the timestamp type in the original openGauss database indicates a timestamp without a time zone. After compatibility, the timestamp type is close to the M* database, indicating a timestamp with a time zone. Therefore, compatibility issues exist. (3) If the entered year is greater than or equal to 10000, the year must be in the YYYY-MM-DD format.
year(w) Indicates a year. w indicates the display width. The value of year(4) or year is in the YYYY format, and the value of year(2) is in the YY format. 2 bytes 1901 ~ 2155 - -
Remarks

Note that, for M*, in the CREATE TABLE or ALTER TABLE statement, if the precision is not specified when the time type (such as timestamp, datetime, and time) column attribute is defined, the default value 0 is used. When the cast(expr as typename) syntax is used for type conversion, if no precision is specified for the target type, the default precision is 0. Therefore, if you want to retain the input precision of the data, you need to explicitly use typmod.
In addition, :: is used to convert the compatible time type. If no precision is specified for the target type, the default precision is 0.
Date Type Inputs
The following formats are supported:

Format Description
'YYYY-MM-DD' , 'YY-MM-DD' Year, month, and date
'YYYYMMDD', 'YYMMDD' Year, month, and date
YYYYMMDD, YYMMDD Year, month, and date
Remarks:

The input must be a valid date. The month or date cannot be 0.
The original value range of M* is within 10000. Therefore, if you want to enter a date greater than or equal to 10000, use the YYYY-MM-DD format, for example, '10100-12-12'.
Year 0000 is allowed. In addition, in openGauss, year 0000 is considered a leap year. You can enter 0000-2-29 (M* not allowed).
Examples (Note that openGauss is compatible with B database.)

""
--Create a table.
openGauss=# CREATE TABLE test_date(
openGauss(# dt date);
CREATE TABLE

--Insert data.
openGauss=# INSERT INTO test_date VALUES ('2020-12-21');
INSERT 0 1
openGauss=# INSERT INTO test_date VALUES ('141221');
INSERT 0 1
openGauss=# INSERT INTO test_date VALUES (20151022);
INSERT 0 1

--View data.
openGauss=# SELECT * FROM test_date;

dt

2020-12-21
2014-12-21
2015-10-22
(3 rows)
Time Type Inputs
The following formats are supported:

Format Description
'[-][D] hh:mm:ss[.frac]' Indicates the hour, minute, and second. The value can be a negative number. D indicates the number of days. The value ranges from 0 to 34.
'[-]hhmmss[.frac]' Hour, minute, and second
[-]hhmmss[.frac] Hour, minute, and second
Remarks:

For the format 'hh:mm:ss', the loose input formats 'hh:mm' and 'ss' are also supported.
When the integer 0 is entered, the value is 00:00:00, which is also a zero value of the time type.
After the time type is compatible, the value range can be greater than 24 hours. Do not convert the time type to the timetz type.
Examples (Note that openGauss is compatible with B database.)

""
--Create a table.
openGauss=# CREATE TABLE test_time(
openGauss(# ti time(2));
CREATE TABLE

--Insert data.
openGauss=# INSERT INTO test_time VALUES ('2 9:12:24.1234');
INSERT 0 1
openGauss=# INSERT INTO test_time VALUES ('-34:56:59.1234');
INSERT 0 1
openGauss=# INSERT INTO test_time VALUES (561234);
INSERT 0 1

--View data.
openGauss=# SELECT * FROM test_time;

ti

57:12:24.12
-34:56:59.12
56:12:34
(3 rows)
Datetime Type Inputs
The following formats are supported:

Format Description
'YYYY-MM-DD hh:mm:ss[.frac]', 'YY-MM-DD hh:mm:ss[.frac]' Timestamp
'YYYYMMDDhhmmss', 'YYMMDDhhmmss' Timestamp
YYYYMMDDhhmmss, YYMMDDhhmmss Timestamp
Remarks:

The input must be a valid date. The month or date cannot be 0.
For the YYYYMMDDhhmmss and YYMMDDhhmmss formats, the first four letters of the character string are identified as the year only when the length of the character string is 8 or 14. In other cases, only the first two letters are identified as the year.
If the input format is YYYYMMDDhhmmss or YYMMDDhhmmss, the length of the input integer must be 6, 8, 12, or 14. If the length does not meet this requirement, zeros are added before the integer, if the length is 6, 8, 12, or 14, the value is in the YYMMDD format. If the length is 8, the value is in the YYYYMMDD format. If the length is 12, the value is in the YYMMDDhhmmss format. If the length is 14, the value is in the YYYYMMDDhhmmss format.
If you want to enter a timestamp whose year is greater than or equal to 10000, use the 'YYYY-MM-DD hh:mm:ss[.frac]' format.
Examples (Note that openGauss is compatible with B database.)

""
--Create a table.
openGauss=# CREATE TABLE test_datetime(
openGauss(# dt datetime(2));
CREATE TABLE

--Insert data.
openGauss=# INSERT INTO test_datetime VALUES ('2020-11-08 02:31:25.961');
INSERT 0 1
openGauss=# INSERT INTO test_datetime VALUES (201112234512);
INSERT 0 1

--View data.
openGauss=# SELECT * FROM test_datetime;

dt

2020-11-08 02:31:25.96
2020-11-12 23:45:12
(3 rows)
Timestamp Type Inputs
The following formats are supported:

Format Description
'YYYY-MM-DD hh:mm:ss[.frac][+/-hh:mm:ss]', 'YY-MM-DD hh:mm:ss[.frac][+/-hh:mm:ss]' Timestamp with time zone
'YYYYMMDDhhmmss[.frac]', 'YYMMDDhhmmss[.frac]' Timestamp with time zone
YYYYMMDDhhmmss[.frac], YYMMDDhhmmss[.frac] Timestamp with time zone
Remarks:

The input must be a valid date. The month or date cannot be 0.
The compatible timestamp type allows the time zone information [+/-hh:mm:ss] to be added after the format 'YYYY-MM-DD hh:mm:ss[.frac]'.
If you want to enter a timestamp whose year is greater than or equal to 10000, use the 'YYYY-MM-DD hh:mm:ss[.frac]' format.
Note that the timestamp type in the M* database is a timestamp without the time zone, and that in openGauss is a timestamp with the time zone. After compatibility, the timestamp type is stored in the timestamptz type internally. Note that the timestamp type is stored in the timestamptz type. Pay attention to the difference before using the timestamp type. If you want to use a timestamp without a time zone, use the datetime type.
Note: The M* does not have the timestamp with[out] time zone syntax, but we still retain this syntax in openGauss. The timestamp with time zone is equivalent to the original openGauss timestamptz type, and timestamp without time zone is equivalent to the timestamp type in the original openGauss (not the compatible timestamp type, that is, the original timestamp without time zone).
Examples (Note that openGauss is compatible with B database.)

""
--Create a table.
openGauss=# CREATE TABLE test_timestamp(
openGauss(# ts timestamp(2));
CREATE TABLE

--Insert data.
openGauss=# INSERT INTO test_timestamp VALUES ('2012-10-21 23:55:23-12:12');
INSERT 0 1
openGauss=# INSERT INTO test_timestamp VALUES (201112234512);
INSERT 0 1

--View data.
openGauss=# SELECT * FROM test_timestamp;

ts

2012-10-22 20:07:23
2020-11-12 23:45:12
(3 rows)

--Change the time zone.
openGauss=# SET TIME ZONE UTC;
SET
--View data.
openGauss=# SELECT * FROM test_timestamp;

ts

2012-10-22 12:07:23
2020-11-12 15:45:12
(3 rows)
Input of the year/year(4) or year(2) type
The following formats are supported:

Format Description
'YYYY', 'YY' Indicates a year. When you enter two digits, if the value is less than 70, add 2000 to the value. For example, 69 indicates 2069. If the value is greater than or equal to 70, add 1900 to the value. For example, 70 indicates 1970.
YYYY, YY Year
Remarks

The three types accept the same input format and range. The only difference is that the output format of the year(2) type is only two digits.
If '0' is entered, openGauss will parse the value to the year 2000. However, if an integer 0 is entered, openGauss will parse the value to 0, indicating the value 0 of the year type.
Examples (Note that openGauss is compatible with B database.)

""
--Create a table.
openGauss=# CREATE TABLE test_year(
openGauss(# y year,
openGauss(# y2 year(2));
CREATE TABLE

--Insert data.
openGauss=# INSERT INTO test_year VALUES ('70', '70');
INSERT 0 1
openGauss=# INSERT INTO test_year VALUES ('69', '69');
INSERT 0 1
openGauss=# INSERT INTO test_year VALUES ('2069', '2069');
INSERT 0 1
openGauss=# INSERT INTO test_year VALUES ('1970', '1970');
INSERT 0 1

--View data.
openGauss=# SELECT * FROM test_year;
y | y2
------+----
1970 | 70
2069 | 69
2069 | 69
1970 | 70
(4 rows)

openGauss dolphin-character-types

tongxi — Sun, 23 Apr 2023 02:36:51 +0000

Compared with the original openGauss, Dolphin modifies the character types as follows:

The meaning of n of the CHARACTER/NCHAR type is modified. n indicates the character length instead of the byte length.
During comparison of all character data types, spaces at the end are ignored, for example, in the WHERE and JOIN scenarios. For example, 'a'::text = 'a'::text is true. For the VARCHAR, VARCHAR2, NVARCHAR2, NVARCHAR, TEXT, and CLOB types, HASH JOIN and HASH AGG ignore spaces at the end only when string_hash_compatible is set to on.
The optional modifier (n) is added for TEXT. That is, the usage of TEXT(n) is supported. n is meaningless and does not affect any performance.
The TINYTEXT(n)/MEDIUMTEXT(n)/LONGTEXT(n) data type is added, which is the alias of TEXT. n is meaningless and does not affect any performance.
Table 1 Character types

Name

Description

Storage Space

CHAR(n)

CHARACTER(n)

NCHAR(n)

Character string with fixed length. Empty characters are filled in with blank spaces. n indicates the string length. If it is not specified, the default precision 1 is used.

The maximum size is 10 MB.

TEXT(n),

TINYTEXT(n),

MEDIUMTEXT(n),

LONGTEXT(n)

Character string with variable length. n has no actual meaning and does not affect any performance.

The maximum size is 1 GB - 1 byte. However, the size of the column description header and the size of the tuple (less than 1 GB - 1 byte) where the column is located must also be considered. Therefore, the maximum size of the TEXT type may be less than 1 GB - 1 byte.

Example:

""
--Create a table.
openGauss=# CREATE TABLE char_type_t1
(
CT_COL1 CHARACTER(4),
CT_COL2 TEXT(10),
CT_COL3 TINYTEXT(11),
CT_COL4 MEDIUMTEXT(12),
CT_COL5 LONGTEXT(13)
);

--Insert data.
openGauss=# INSERT INTO char_type_t1 VALUES ('Four characters');
openGauss=# INSERT INTO char_type_t1 VALUES('e ');

--View data.
openGauss=# SELECT CT_COL1,length(CT_COL1) FROM char_type_t1;
ct_col1 | length
----------+--------
Four characters | 4
e | 1
(2 rows)

--Filter data.
openGauss=# SELECT CT_COL1 FROM char_type_t1 WHERE CT_COL1 = 'e';

ct_col1

e
(1 row)

openGauss=# SELECT CT_COL1 FROM char_type_t1 WHERE CT_COL1 = 'e ';

ct_col1

e
(1 row)

--Delete the table.
openGauss=# DROP TABLE char_type_t1;

openGauss dolphin-numeric-types

tongxi — Sun, 23 Apr 2023 02:36:02 +0000

For details about arithmetic operators and related built-in functions, see Arithmetic Functions and Operators.

Compared with the original openGauss, Dolphin modifies the arithmetic types as follows:

The INT, TINYINT, SMALLINT, and BIGINT support the optional modifier (n), that is, the usage of TINYINT(n), SMALLINT(n), and BIGINT(n) is supported. n is meaningless and does not affect any performance.
The MEDIUMINT(n) data type is added, which is the alias of INT4. n is meaningless and does not affect any performance. The storage space is 4 bytes, and the data ranges from -2,147,483,648 to +2,147,483,647.
The FIXED[(p[,s])] data type is added, which is the alias of the NUMERIC type. The precision is specified by users. Two bytes are occupied for every four decimals of precision. An extra eight-byte overhead is added for numbers of this type. Up to 131,072 digits before the decimal point and up to 16,383 digits after the decimal point when no precision is specified
The float4(p[,s]) mode is added, which is equivalent to dec(p[,s]).
The double data type is added, which is the alias of float8.
The new float4 and float support the modifier (n). That is, float4(n) and float(n) are supported. When the value range of n is [1,24], float4(n) and float(n) indicate a single-precision floating point number. If the value range of n is [25,53], float4(n) and float(n) indicate a double-precision floating point number.
For the decimal, dec, fixed, and numeric data types, if the precision is not specified, the default precision is (10,0). That is, the total number of digits is 10 and the number of decimal places is 0.
The UNSIGNED INT, TINYINT, SMALLINT, and BIGINT types are added. Compared with a common integer, the most significant bit of the UNSIGNED INT, TINYINT, SMALLINT, BIGINT type is a digit bit instead of a sign bit.
The zerofill attribute is added, which is supported only in syntax and does not have the effect of filling zeros. It is equivalent to UNSIGNED.
The cast function parameters SIGNED and UNSIGNED are added. CAST AS UNSIGNED converts the type to uint8, and CAST AS SIGNED converts the type to int8.
Table 1 Integer types

Alias of theAlias of the
Name

Description

Storage Space

Value Range

TINYINT(n)

Tiny integer, also called INT1. n has no actual meaning and does not affect any performance.

1 byte

-128 to +127

SMALLINT(n)

Small integer, also called INT2. n has no actual meaning and does not affect any performance.

2 bytes

-32,768 ~ +32,767

INTEGER(n)

Typical choice for integers, also called INT4. n has no actual meaning and does not affect any performance.

4 bytes

-2,147,483,648 ~ +2,147,483,647

MEDIUMINT(n)

Alias of INT4. n is meaningless and does not affect any performance.

4 bytes

-2,147,483,648 ~ +2,147,483,647

BIGINT(n)

Big integer, also called INT8. n has no actual meaning and does not affect any performance.

8 bytes

-9,223,372,036,854,775,808 ~ +9,223,372,036,854,775,807

TINYINT(n) UNSIGNED

Tiny integer, also called INT1. n has no actual meaning and does not affect any performance.

1 byte

0 ~ 255

SMALLINT(n) UNSIGNED

Unsigned small integer, also called UINT2. n has no actual meaning and does not affect any performance.

2 bytes

0 ~ +65,535

INTEGER(n) UNSIGNED

Unsigned integer, also called UINT4. n has no actual meaning and does not affect any performance.

4 bytes

0 ~ +4,294,967,295

MEDIUMINT(n) UNSIGNED

Alias of UINT4. n is meaningless and does not affect any performance.

4 bytes

0 ~ +4,294,967,295

BIGINT(n) UNSIGNED

Unsigned large integer, also called UINT8. n has no actual meaning and does not affect any performance.

8 bytes

0 ~ +18,446,744,073,709,551,615

Example:

""
--Create a table that contains data of the TINYINT(n), SMALLINT(n), MEDIUMINT(n), and BIGINT(n) types.
openGauss=# CREATE TABLE int_type_t1
(
IT_COL1 TINYINT(10),
IT_COL2 SMALLINT(20),
IT_COL3 MEDIUMINT(30),
IT_COL4 BIGINT(40),
IT_COL5 INTEGER(50)
);

--Create a table with the zerofill attribute column.
openGauss=# CREATE TABLE int_type_t2
(
IT_COL1 TINYINT(10) zerofill,
IT_COL2 SMALLINT(20) unsigned zerofill,
IT_COL3 MEDIUMINT(30) unsigned,
IT_COL4 BIGINT(40) zerofill,
IT_COL5 INTEGER(50) zerofill
);

--Delete a table.
openGauss=# DROP TABLE int_type_t1, int_type_t2;

--Use CAST UNSIGNED to convert an expression to the uint8 type.
openGauss=# select cast(1 - 2 as unsigned);

uint8

18446744073709551615
(1 row)

--Use CAST SIGNED to convert an expression to the int8 type.
openGauss=# select cast(1 - 2 as signed);

int8

-1
(1 row)
Table 2 Arbitrary precision types

Name

Description

Storage Space

Value Range

NUMERIC[(p[,s])],

DECIMAL[(p[,s])]

FIXED[(p[,s])]

The value range of p (precision) is [1,1000], and the value range of s (scale) is [0,p].

Note:
p indicates the total digits, and s indicates the decimal digits.

The precision is specified by users. Two bytes are occupied for every four decimals of precision. An extra eight-byte overhead is added for numbers of this type.

If the precision is not specified, the value is equivalent to (10,0), that is, a maximum of 10 digits before the decimal point and 0 digits after the decimal point.

NUMBER[(p[,s])]

Alias of the NUMERIC class.

The precision is specified by users. Two bytes are occupied for every four decimals of precision. An extra eight-byte overhead is added for numbers of this type.

Up to 131,072 digits before the decimal point and up to 16,383 digits after the decimal point when no precision is specified.

Example:

""
--Create a table with FIXED(p,s), FIXED, decimal, and number data.
openGauss=# CREATE TABLE dec_type_t1
(
DEC_COL1 FIXED,
DEC_COL2 FIXED(20,5),
DEC_COL3 DECIMAL,
DEC_COL4 NUMBER
);

--Delete a table.
openGauss=# DROP TABLE dec_type_t1;
Table 3 Floating-point types

Name

Description

Storage Space

Value Range

FLOAT[(p)],

FLOAT4[(p)]

Floating point, which is not very precise. The value range of p (precision) is [1,53].

4 bytes or 8 bytes

When the precision p is between [1,24], the option REAL is used as the internal identifier. When the precision p is between [25,53], the option DOUBLE PRECISION is used as the internal identifier. If no precision is specified, REAL is used as the internal identifier.

DOUBLE PRECISION,

FLOAT8,

DOUBLE

Double-precision floating point, which is not very precise.

8 bytes

–1.79E+308 to 1.79E+308, 15-bit decimal digits.

FLOAT4(p,s)

The value range of p (precision) is [1,1000], and the value range of s (scale) is [0,p].

Note:
p indicates the total digits, and s indicates the decimal digits. It is equivalent to dec(p,s).

The precision is specified by users. Two bytes are occupied for every four decimals of precision. An extra eight-byte overhead is added for numbers of this type.

Example:

""
--Create a table that contains data of the float4(p,s), double, float4(n), and float(n) types.
openGauss=# CREATE TABLE float_type_t1
(
F_COL1 FLOAT4(10,4),
F_COL2 DOUBLE,
F_COL3 float4(10),
F_COL4 float4(30),
F_COL5 float(10),
F_COL6 float(30)
);

--Delete a table.
openGauss=# DROP TABLE float_type_t1;

openGauss dolphin-keywords.

tongxi — Sun, 23 Apr 2023 02:35:11 +0000

The SQL contains reserved and non-reserved words. Standards require that reserved keywords not be used as other identifiers. Non-reserved keywords have special meanings only in a specific environment and can be used as identifiers in other environments.

The naming rules for identifiers are as follows:

An identifier name can only contain letters, underscores, digits (0-9), and dollar signs ($).

An identifier name must start with a letter (a to z) or an underscore (_).

NOTE:

The naming rules are recommended but not mandatory.

In special cases, double quotation marks (") can be used to avoid special character errors.

Compared with the original openGauss, Dolphin modifies keywords as follows:

MEDIUMINT is added as a non-reserved keyword.
The keyword DATE can be used as a function.
LAST_DAY is added as a reserved keyword to distinguish the original LAST_DAY function of openGauss from the LAST_DAY function of Dolphin at the syntax level.
GET_FORMAT is added as a non-reserved keyword to identify the GET_FORMAT function in syntax.
DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, and SECOND_MICROSECOND are added as non-reserved keywords for the EXTRACT function to identify the corresponding unit in syntax.
Table 1 SQL keywords

Keyword

openGauss

SQL:1999

SQL-92

FORMAT

Non-reserved (excluding functions and types)

KEYS

Non-reserved

MEDIUMINT

Non-reserved (excluding functions and types)

SIGNED

Non-reserved (excluding functions and types)

UNSIGNED

Non-reserved (excluding functions and types)

ZEROFILL

Non-reserved

DATE Non-reserved (excluding functions and types) - -
LAST_DAY Reserved - -
GET_FORMAT Non-reserved (excluding functions and types) - -
DAY_HOUR Non-reserved - -
DAY_MINUTE Non-reserved - -
DAY_SECOND Non-reserved - -
DAY_MICROSECOND Non-reserved - -
HOUR_MINUTE Non-reserved - -
HOUR_SECOND Non-reserved - -
HOUR_MICROSECOND Non-reserved - -
MINUTE_SECOND Non-reserved - -
MINUTE_MICROSECOND Non-reserved - -
SECOND_MICROSECOND Non-reserved - -

openGauss Dolphin Restrictions

tongxi — Sun, 23 Apr 2023 02:32:53 +0000

The lite edition is not supported.
The Dolphin plug-in cannot be deleted.
The Dolphin plug-in can be created only in the B-compatible database.
The Dolphin plug-in needs to create data types and functions in schemas such as pg_catalog. Therefore, loading the Dolphin plug-in requires initial user permissions. openGauss automatically loads the Dolphin plug-in when the initial user or a user with the initial user permissions connects to B-compatible database for the first time. If a B-compatible database has never been connected by an initial user or a user with initial user permissions, it will not load the Dolphin plug-in.
All added or modified syntaxes in Dolphin cannot be viewed by running \h on the gsql client, and cannot be automatically supplemented on the gsql client.
The creation of the Dolphin plug-in deletes the functions and types with the same name required by the plug-in that exists in the database and the objects that previously depend on the plug-in.

openGauss Dolphin Installation

tongxi — Sun, 23 Apr 2023 02:31:14 +0000

The plug-in is automatically installed and loaded. You do not need to manually install and load the plug-in. To manually compile and load the plug-in, perform the following steps:

Compilation and Installation
Compile and install openGauss.

Copy the Dolphin source code to the contrib directory of the openGauss source code.

Go to the Dolphin directory and run make install.

Create a B-compatible database and connect to B-compatible database as the initial user.

OM Installation
Install openGauss on the OM.

Copy the files required by the plug-in.
Path of dolphin.so: app/lib/postgresql/
Path of dolphin.control and dolphin–1.0.sql: app/share/postgresql/extension

Create a B-compatible database and connect to B-compatible database as the initial user.

openGauss Dolphin Overview

tongxi — Sun, 23 Apr 2023 02:30:30 +0000

openGauss provides Dolphin extensions (version: Dolphin-1.0.0). Dolphin extensions of openGauss are compatible with MySQL databases (dbcompatibility='B') in terms of keywords, data types, constants and macros, functions and operators, expressions, type conversion, DDL/DML/DCL syntax, stored procedures/user-defined functions, and system views.

openGauss Configuring Client Access Authentication

tongxi — Wed, 19 Apr 2023 02:35:20 +0000

Background
If a host needs to connect to a database remotely, you need to add information about the host in configuration file of the database system and perform client access authentication. The configuration file (pg_hba.conf by default) is stored in the data directory of the database. HBA is short for host-based authentication.

The system supports the following three authentication methods, which all require the pg_hba.conf file.

Host-based authentication: A server checks the configuration file based on the IP address, username, and target database of the client to determine whether the user can be authenticated.
Password authentication: A password can be an encrypted password for remote connection or a non-encrypted password for local connection.
SSL encryption: The OpenSSL is used to provide a secure connection between the server and the client.
In the pg_hba.conf file, each record occupies one row and specifies an authentication rule. An empty row or a row started with a number sign (#) is neglected.

Each authentication rule consists of multiple columns separated by spaces and forward slashes (/), or spaces and tab characters. If a field is enclosed with quotation marks ("), it can contain spaces. One record cannot span different rows.

Procedure
Log in as the OS user omm to the primary node of the database.

Configure the client authentication mode and enable the client to connect to the host as user jack. User omm cannot be used for remote connection.

Assume you are to allow the client whose IP address is 10.10.0.30 to access the current host.

""
gs_guc set -N all -I all -h "host all jack 10.10.0.30/32 sha256"
NOTE:

Before using user jack, connect to the database locally and run the following command in the database to create user jack:
""
CREATE USER jack PASSWORD 'Test@123';

-N all indicates all hosts in openGauss.
-I all indicates all instances on the host.
-h specifies statements that need to be added in the pg_hba.conf file.
all indicates that a client can connect to any database.
jack indicates the user that accesses the database.
10.10.0.30/32 indicates that only the client whose IP address is 10.10.0.30 can connect to the host. The specified IP address must be different from those used in openGauss. 32 indicates that there are 32 bits whose value is 1 in the subnet mask. That is, the subnet mask is 255.255.255.255.
sha256 indicates that the password of user jack is encrypted using the SHA-256 algorithm.
This command adds a rule to the pg_hba.conf file corresponds to the primary node of the database. The rule is used to authenticate clients that access primary node.

Each record in the pg_hba.conf file can be in one of the following four formats. For parameter description of the four formats, see Configuration File Reference.

""
local DATABASE USER METHOD [OPTIONS]
host DATABASE USER ADDRESS METHOD [OPTIONS]
hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
During authentication, the system checks records in the pg_hba.conf file in sequence for connection requests, so the record sequence is vital.

NOTE:
Configure records in the pg_hba.conf file from top to bottom based on communication and format requirements in the descending order of priorities. The IP addresses of the openGauss cluster and added hosts are of the highest priority and should be configured prior to those manually configured by users. If the IP addresses manually configured by users and those of added hosts are in the same network segment, delete the manually configured IP addresses before the scale-out and configure them after the scale-out.

The suggestions on configuring authentication rules are as follows:

Records placed at the front have strict connection parameters but weak authentication methods.
Records placed at the end have weak connection parameters but strict authentication methods.
NOTE:

If a user wants to connect to a specified database, the user must be authenticated by the rules in the pg_hba.conf file and have the CONNECT permission for the database. If you want to restrict a user from connecting to certain databases, you can grant or revoke the user's CONNECT permission, which is easier than setting rules in the pg_hba.conf file.
The trust authentication mode is insecure for a connection between the openGauss and a client outside the cluster. In this case, set the authentication mode to sha256.

openGauss Comparison – Disk vs. MOT

tongxi — Wed, 19 Apr 2023 02:31:01 +0000

The following table briefly compares the various features of the openGauss disk-based storage engine and the MOT storage engine.

Table 1 Comparison – Disk-based vs. MOT

Feature

openGauss

Disk Store

openGauss

MOT Engine

Intel x86 + Kunpeng ARM

Yes

SQL and Feature-set Coverage

100%

98%

Scale-up (Many-cores, NUMA)

Low Efficiency

High Efficiency

Throughput

High

Extremely High

Latency

Low

Extremely Low

Distributed (Cluster Mode)

Yes

Isolation Levels

READ COMMITTED
REPEATABLE READ
READ COMMITTED
REPEATABLE READ
Concurrency Control

Pessimistic + MVCC

Optimistic + MVCC

Data Capacity (Data + Index)

Unlimited

Limited to DRAM

Native Compilation

Yes

Query (by PREPARE command)
Stored Procedures (by PREPARE command)
Replication, Recovery

Yes

Replication Options

2 (sync, async)

3 (sync, async, group-commit)

openGauss MOT JIT Diagnostics

tongxi — Wed, 19 Apr 2023 02:27:10 +0000

mot_jit_detail
This built-in function is used to query the details about JIT compilation (code generation).

Usage Examples
""
select * from mot_jit_detail();

select proc_oid, substr(query, 0, 50), namespace, jittable_status, valid_status, last_updated, plan_type, codegen_time from mot_jit_detail();
Output Description
Field

Description

proc_oid

Procedure OID (Real Object ID of the procedure in the database). 0 for queries.

query

Query string or stored procedure name.

namespace

Namespace to which the query or procedure belongs to. For procedures and top level queries, the value will be GLOBAL. For all the invoke queries, sub-queries, this field will show the parent information.

jittable_status

Whether the query or procedure is jittable:
jittable – Query or procedure is jittable
unjittable - Query or procedure is not jittable
invalid - Invalid state (temporary state after invalidation due to DDL or when JIT compilation is in progress)
valid_status

Whether the query or procedure is valid or not:

valid – Query or procedure is valid
unavailable – JIT compilation is in progress
error – Error state
dropped – Procedure is dropped
replaced – Procedure is replaced
last_updated

Timestamp when the status was updated last time.

plan_type

Whether this is a stored procedure (SP) or query type.

codegen_time

Total time taken for code generation (JIT compilation), in micro seconds.

verify_time

LLVM Verification time (internal), in micro seconds.

finalize_time

LLVM Finalize time (internal), in micro seconds.

compile_time

LLVM Compile time (internal), in micro seconds.

mot_jit_profile
This built-in function is used to query the profiling data (performance data) of the query or stored procedure execution.

Usage Examples
""
select * from mot_jit_profile();

select proc_oid, id, parent_id, substr(query, 0, 50), namespace, weight, total, self, child_gross, child_net from mot_jit_profile();
Output Description
Field

Description

proc_oid

Procedure OID (Real Object ID of the procedure in the database). 0 for queries.

Internal ID to manipulate the output.

parent_id

Parent ID (Internal ID of the parent). Applicable only for sub-queries and sub-procedures. -1 for top-level queries and procedures.

query

Query string or stored procedure name.

namespace

weight

The average number of times the sub-query or sub-procedure was executed (per one parent SP execution), in micro seconds.

total

Total time taken to execute the query or procedure, in micro seconds.

self

Time taken by the query or procedure excluding the time taken by the sub-queries & sub-procedures, in micro seconds.

child_gross

Total time spent in execution of all the sub-queries & sub-procedures (child_net + time spent to prepare for execution of all the sub-queries & sub-procedures), in micro seconds.

child_net

Total time taken by all the sub-queries & sub-procedures i.e., ∑ (total of child * weight), in micro seconds.

def_vars

Time taken to define variables (internal), in micro seconds.

init_vars

Time taken to initialize variables (internal), in micro seconds.

Miscellaneous
Another useful system table to get information about stored procedures and functions is pg_proc.

For example, body of a stored procedure can be queried using the following query:

""
select proname,prosrc from pg_proc where proname='sp_call_filter_rules_100_1';

openGauss JIT for Stored procedures

tongxi — Wed, 19 Apr 2023 02:26:15 +0000

JIT for Stored Procedures (JIT SP) is supported by the openGauss MOT engine (starting from 5.0 version), and its goal is deliver even higher performance and lower latency.

JIT SP refers to code generation, compiling and execution of stored procedures (SP) by LLVM runtime code generation and execution library. JIT SP is available to SPs accessing MOT tables (only) and is completely transparent to users. SPs with Cross-Tx usage will be executed by standard PLSQL. Acceleration level depends on the SP logic complexity. For example, a real customer application achieved acceleration of 20%, 44%, 300% and 500% for different SPs, shaving microseconds to tens of milliseconds of the SP latency.

During the PREPARE phase of a query invoking an SP, or the first SP execution, the JIT module performs an attempt to translate the SP SQL into a C-based function and compile it in runtime (using LLVM). If successful, the consecutive SP invocations the MOT will execute a compiled function, leading to performance gains. In case of failure to produce a compiled function, the SP will be executed by standard PLSQL. Both scenarios are fully transparent to users.

You may refer to JIT Diagnostics for useful diagnostics information.