1. New GUC variables

In order to be compatible with Oracle, it is necessary to add some variables for controlling the database execution results on the basis of the original GUC variables, so as to achieve the same behavior as Oracle.

In order to better add compatible GUC parameters and to minimize changes to the PG kernel source code, we need to design a framework to add GUC to a unified location.

1.1. Achieve

When adding guc parameters, we need to add them uniformly in the ivy_guc.c. Where Ivy_ConfigureNamesBool, Ivy_ConfigureNamesInt, Ivy_ConfigureNamesString, Ivy_ConfigureNamesReal and Ivy_ConfigureNamesEnum represent 5 different types of guc parameters. When adding guc parameters, simply add the value of guc to the corresponding array.

1.2. New variables(currently)

Variable

Description

ivorysql.compatible_mode

Indicates which database (pg/oracle) is currently compatible with, which can be viewed through the show command. The set command changes this variable, and the reset command resets it to the database mode at the time of connection. Resetting all will affect this variable

ivorysql.database_mode

Indicates the current database schema (pg/oracle), which can be viewed through the show command. The set/reset/reset all command does not affect this variable

ivorysql.datetime_ignore_nls_mask

Indicates whether the date format will be affected by the NLS parameter. The default value is 0, which can be set using the set command. The reset command resets the date format, and the reset all command resets the variable

ivorysql.enable_emptystring_to_NULL

The value is (on/off), and when this variable is on, it will convert the inserted empty string into a NULL value for storage

ivorysql.identifier_case_switch

Set character case conversion mode

ivorysql.listen_address

Indicates the address for compatibility mode listening. When initializing the database, read the configuration from the ivorysql.conf file, modify the value in the configuration file, and restart the database to take effect. This can be viewed through the show command

ivorysql.port

Indicates the port number for connecting in compatibility mode. When initializing the database, read the configuration from the ivorysql.conf file and modify the value in the configuration file. To take effect, restart the database and view it through the show command

nls_date_format

Represents the default date format, which can be viewed through the show command and defaults to 'YYYY-MM-DD'. It can be set through the set command and reset back to the default value through the reset command. The reset all command will reset this variable

nls_length_semantic

Compatible with oracle parameters of the same name, controlling the size of memory occupied by a character

nls_timestamp_format

Compatible with oracle parameters of the same name, controlling date format with time

nls_timestamp_tz_format

Compatible with oracle parameters of the same name, controlling the date format with time zone

shared_preload_libraries

When initializing the database, read from the ivorysql.conf file and view it through the show command. Modify the value in the configuration file and restart the database to take effect.

1.3. Example

1.3.1. ivorysql.datetime_ignore_nls_mask

The optional values ​​of this GUC variable are from 0 to 15.

optional values

Types not formatted through NLS

0

Does not block any types, all time formats are formatted by NLS.

1

date

2

timestamp

3

date、timestamp

4

timestamptz

5

date、timestamptz

6

timestamp、timestamptz

7

date、timestamp、timestamptz

8

timestampltz

9

date、timestampltz

10

timestamp、timestampltz

11

date、timestamp、timestampltz

12

timestamptz、timestampltz

13

date、timestamptz、timestampltz

14

timestamp、timestamptz、timestampltz

15

date、timestamp、timestamptz、timestampltz

  • Usage Example(date)

check value of nls_date_format and datetime_ignore_nls_mask

ivorysql=# set ivorysql.compatible_mode to oracle;
SET
ivorysql=# show nls_date_format;
 nls_date_format
-----------------
 YYYY-MM-DD
(1 row)
ivorysql=# show ivorysql.datetime_ignore_nls_mask;
 ivorysql.datetime_ignore_nls_mask
-----------------------------------
 0
(1 row)

create a table for testing

ivorysql=# create table test_nls_date(a int, created_at date);
CREATE TABLE

insert data

ivorysql=# insert into test_nls_date values(1, '2024/04/05');
INSERT 0 1
ivorysql=# select * from test_nls_date;
 a | created_at
---+------------
 1 | 2024-04-05
(1 row)

modify nls_date_format

ivorysql=# set nls_date_format to 'yy-mm-dd';
SET

Insert NLS formatted data and view, insert successfully.

ivorysql=# insert into test_nls_date values(2, '24/04/15');
INSERT 0 1
ivorysql=# select * from test_nls_date;
 a | created_at
---+------------
 1 | 24-04-05
 2 | 24-04-15
(2 rows)

Changing the date type to not undergo NLS processing, and inserting the same data, such as changing it to 1 (3, 5, 7, etc.), will result in an error upon data insertion. NLS formatting will not affect the query results for dates.

ivorysql=# set ivorysql.datetime_ignore_nls_mask to 1;
SET
ivorysql=# insert into test_nls_date values(3, '24/05/15');
ERROR:  date/time field value out of range: "24/05/15"
LINE 1: insert into test_nls_date values(3, '24/05/15');
                                            ^
HINT:  Perhaps you need a different "datestyle" setting.
ivorysql=#  select * from test_nls_date;
 a | created_at
---+------------
 1 | 2024-04-05
 2 | 2024-04-15
(2 rows)