GUC Framework

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.enable_case_switch

The value is (on/off) and the default value is on. It enables the identifier case conversion feature in Oracle compatible mode. See section 7.3 for details

ivorysql.listen_addresses

Indicates the address(es) for compatibility mode listening. The default value is 'localhost'; a comma-separated list of addresses is accepted. 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_semantics

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. Developer options

The parameters below also belong to the ivorysql.* family, but they are developer/diagnostic switches: they are hidden from pg_settings (registered with GUC_NO_SHOW_ALL) and are not written to ivorysql.conf.sample, so they normally do not need to be changed. Their current value can be viewed with the show command and changed within a session with set.

Variable

Default

Description

ivorysql.allow_out_parameter_const

false

The value is (on/off). When on, an OUT parameter of a PL/iSQL function can be a constant value

ivorysql.default_with_rowids

false

The value is (on/off). When on, a rowid column is added automatically when a new table is created

ivorysql.enable_internal_warning

false

The value is (on/off). When on, internal warning information is printed

ivorysql.enable_seq_scale_fixed

false

The value is (on/off). Switch for the sequence scale value fix

ivorysql.identifier_case_from_pg_dump

false

Shows whether the identifier with quotes is from pg_dump. Used by pg_dump; it is flagged GUC_DISALLOW_IN_FILE, so it can only be set inside a session

ivorysql.out_parameter_column_position

false

The value is (on/off). When on, a PL/iSQL function constructs the OUT parameter columns by their position

ivorysql.rowid_seq_cache

20

Number of cached sequence values for rowid generation, range 1-60000

1.4. Example

1.4.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 | 24-04-05
 2 | 24-04-15
(2 rows)