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 |
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)