标簽
PostgreSQL , EDB , PPAS , 參數 , Oracle模式 , PostgreSQL模式
https://github.com/digoal/blog/blob/master/201804/20180411_02.md#%E8%83%8C%E6%99%AF 背景
EDB PPAS是EDB推出的一款同時相容Oracle和PostgreSQL協定的資料庫，在去O的場景中，使用非常廣泛，價格便宜，同時性能和Oracle差不多，并且SQL文法，存儲過程等相容性都特别好。
除了Oracle相容，EDB PPAS實際上底層是PostgreSQL，如果你想把PPAS跑在相容PG的模式下，需要調整一些參數。（因為Oracle和PG在某些功能點上的取向不太一緻，下面舉例）
https://github.com/digoal/blog/blob/master/201804/20180411_02.md#oracle-%E6%A8%A1%E5%BC%8F%E5%8F%82%E6%95%B0 Oracle 模式參數
# - Oracle compatibility -
edb_redwood_date = on # translate DATE to TIMESTAMP(0)
edb_redwood_greatest_least = on # GREATEST/LEAST are strict
edb_redwood_strings = on # treat NULL as an empty string in
# string concatenation
#edb_redwood_raw_names = off # don't uppercase/quote names in sys views
#edb_stmt_level_tx = off # allow continuing on errors instead
# rolling back
db_dialect = 'redwood' # Sets the precedence of built-in
# namespaces.
# 'redwood' means sys, dbo, pg_catalog
# 'postgres' means pg_catalog, sys, dbo
#optimizer_mode = choose # Oracle-style optimizer hints.
# choose, all_rows, first_rows,
# first_rows_10, first_rows_100,
# first_rows_1000
#edb_early_lock_release = off # release locks for prepared statements
# when the portal is closed
#oracle_home ='' # path to the Oracle home directory;
# only used by OCI Dblink; defaults
# to ORACLE_HOME environment variable.
#datestyle = 'iso, ymd' # PostgreSQL default for your locale
datestyle = 'redwood,show_time'
#default_with_oids = off
#default_with_rowids = off
https://github.com/digoal/blog/blob/master/201804/20180411_02.md#%E9%80%90%E6%9D%A1%E8%AE%B2%E8%A7%A3 逐條講解
1、edb_redwood_date
Oracle date類型包含了日期和時間。而PG的date類型隻有日期，timestamp才是日期和時間，time是隻有時間沒有日期。
Oracle 相容模式：
postgres=# set edb_redwood_date=on;
SET
postgres=# select (now())::date;
now
---------------------------
11-APR-18 22:36:43.192825
(1 row)
PostgreSQL 相容模式：
postgres=# set edb_redwood_date=off;
SET
postgres=# select (now())::date;
now
-----------
11-APR-18
(1 row)
2、edb_redwood_greatest_least
當參數中有一個NULL時，Oracle會就傳回NULL。而PostgreSQL會忽略NULL，除非所有參數都是NULL才傳回NULL。
這裡Oracle為strict模式，而PG為非strict模式，後面有解釋。
postgres=# set edb_redwood_greatest_least =on;
SET
postgres=# select greatest(1,null,2);
greatest
----------
(1 row)
postgres=# set edb_redwood_greatest_least =off;
SET
postgres=# select greatest(1,null,2);
greatest
----------
2
(1 row)
3、edb_redwood_strings
連接配接字元串時，如果有NULL的字元串，Oracle當成empty字元處理。而PG會傳回NULL(即strict模式, 連接配接符對應的函數為strict模式，那麼隻要有任一參數為NULL，直接傳回NULL)。
https://www.postgresql.org/docs/devel/static/sql-createfunction.htmlSTRICT
CALLED ON NULL INPUT (the default) indicates that the function will be called normally when some of its arguments are null.
It is then the function author's responsibility to check for null values if necessary and respond appropriately.
RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null.
If this parameter is specified, the function is not executed when there are null arguments;
instead a null result is assumed automatically.
postgres=# set edb_redwood_strings=on;
SET
postgres=# select 'a'||null||'b';
?column?
----------
ab
(1 row)
postgres=# set edb_redwood_strings=off;
SET
postgres=# select 'a'||null||'b';
?column?
----------
(1 row)
4、edb_redwood_raw_names
Oracle，預設對象名使用大寫存儲。PostgreSQL預設使用小寫存儲。同時如果使用了非預設行為的對象名（例如Oracle中混入小寫，PG對象名中混入大寫），那麼在Oracle中顯示是會使用雙引号對這些對象名。PG不會使用雙引号顯示這些對象名。
CREATE USER reduser IDENTIFIED BY password;
edb=# \c - reduser
Password for user reduser:
You are now connected to database "edb" as user "reduser".
CREATE TABLE all_lower (col INTEGER);
CREATE TABLE ALL_UPPER (COL INTEGER);
CREATE TABLE "Mixed_Case" ("Col" INTEGER);
postgres=# set edb_redwood_raw_names=false;
SET
edb=> SELECT * FROM USER_TABLES;
schema_name | table_name | tablespace_name | status | temporary
-------------+--------------+-----------------+--------+-----------
REDUSER | ALL_LOWER | | VALID | N
REDUSER | ALL_UPPER | | VALID | N
REDUSER | "Mixed_Case" | | VALID | N
(3 rows)
postgres=# set edb_redwood_raw_names=true;
SET
edb=> SELECT * FROM USER_TABLES;
schema_name | table_name | tablespace_name | status | temporary
-------------+------------+-----------------+--------+-----------
reduser | all_lower | | VALID | N
reduser | all_upper | | VALID | N
reduser | Mixed_Case | | VALID | N
(3 rows)
edb=> SELECT schemaname, tablename, tableowner FROM pg_tables WHERE tableowner = 'reduser';
schemaname | tablename | tableowner
------------+------------+------------
reduser | all_lower | reduser
reduser | all_upper | reduser
reduser | Mixed_Case | reduser
(3 rows)
5、edb_stmt_level_tx
Oracle中，預設情況下事務中有異常的SQL時，異常SQL前執行的正常SQL對資料庫産生的應用會繼續保留，并且可以繼續執行SQL。送出時正常執行SQL産生的影響都會被送出。
PostgreSQL中，預設情況下，事務中隻有有任意SQL異常，都會導緻整個事務復原，預設為事務原子性。(但是PG可以通過開啟SAVEPOINT實作ORACLE一樣的效果，PPAS則通過這個參數控制。)
Oracle 相容模式(開啟它有性能影響，注意)：
\set AUTOCOMMIT off
SET edb_stmt_level_tx TO on;
INSERT INTO emp (empno,ename,deptno) VALUES (9001, 'JONES', 40);
INSERT INTO emp (empno,ename,deptno) VALUES (9002, 'JONES', 00);
ERROR: insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk"
DETAIL: Key (deptno)=(0) is not present in table "dept"
COMMIT;
SELECT empno, ename, deptno FROM emp WHERE empno > 9000;
empno | ename | deptno
-------+-------+--------
9001 | JONES | 40
(1 row)
\set AUTOCOMMIT off
SET edb_stmt_level_tx TO off;
INSERT INTO emp (empno,ename,deptno) VALUES (9001, 'JONES', 40);
INSERT INTO emp (empno,ename,deptno) VALUES (9002, 'JONES', 00);
ERROR: insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk"
DETAIL: Key (deptno)=(0) is not present in table "dept".
COMMIT;
SELECT empno, ename, deptno FROM emp WHERE empno > 9000;
empno | ename | deptno
-------+-------+--------
(0 rows)
6、db_dialect
改變資料庫搜尋對象的優先級，起到作用和PostgreSQL search_path類似。
隻不過他影響的是中繼資料表的搜尋優先級。
Oracle相容模式，搜尋順序為sys, dbo, pg_catalog. 分别表示Oracle系統表，SQL Server系統表，PG系統表的schema。
PostgreSQL相容模式，搜尋順序為pg_catalog, sys, dbo.
sys.all_all_tables sys."aq$_agent" sys.dba_tab_columns sys."edb$statio_idx_pk" sys.user_constraints
sys.all_cons_columns sys."aq$_callback_queue_table_i" sys.dba_tables sys."edb$statio_tab_pk" sys.user_db_links
sys.all_constraints sys."aq$_descriptor" sys.dba_tab_partitions sys."edb$stat_tab_pk" sys.user_ind_columns
sys.all_db_links sys."aq$_queued_callback" sys.dba_tab_subpartitions sys."edb$system_waits" sys.user_indexes
sys.all_directories sys."aq$_reg_info" sys.dba_triggers sys.lineno_text sys.user_objects
sys.all_ind_columns sys.callback_queue_table sys.dba_types sys.msg_prop_t sys.user_part_key_columns
sys.all_indexes sys.dba_all_tables sys.dba_users sys.plsql_profiler_data sys.user_part_tables
sys.all_objects sys.dba_cons_columns sys.dba_view_columns sys.plsql_profiler_rawdata sys.user_policies
sys.all_part_key_columns sys.dba_constraints sys.dba_views sys.plsql_profiler_runid sys.user_queues
sys.all_part_tables sys.dba_db_links sys.dbms_aq_stat_databases sys.plsql_profiler_runs sys.user_queue_tables
sys.all_policies sys.dba_directories sys.dbms_aq_stat_messages sys.plsql_profiler_runs_pkey sys.user_role_privs
sys.all_queues sys.dba_ind_columns sys.dbms_aq_stat_queues sys.plsql_profiler_units sys.user_sequences
sys.all_queue_tables sys.dba_indexes sys.dbms_aq_stat_waiters sys.product_component_version sys.user_source
sys.all_sequences sys.dba_objects sys.dual sys.scheduler_0100_component_name_type sys.user_subpart_key_columns
sys.all_source sys.dba_part_key_columns sys.edb_qt_1220_msgid sys.scheduler_0200_program_type sys.user_synonyms
sys.all_subpart_key_columns sys.dba_part_tables sys.edb_qt_1220_next_event_time sys.scheduler_0250_program_argument_type sys.user_tab_columns
sys.all_synonyms sys.dba_policies sys."edb$session_wait_history" sys.scheduler_0300_schedule_type sys.user_tables
sys.all_tab_columns sys.dba_profiles sys."edb$session_waits" sys.scheduler_0400_job_type sys.user_tab_partitions
sys.all_tables sys.dba_queues sys."edb$snap" sys.scheduler_0450_job_argument_type sys.user_tab_subpartitions
sys.all_tab_partitions sys.dba_queue_tables sys."edb$stat_all_indexes" sys.session_waits_hist_pk sys.user_triggers
sys.all_tab_subpartitions sys.dba_role_privs sys."edb$stat_all_tables" sys.session_waits_pk sys.user_types
sys.all_triggers sys.dba_roles sys."edb$stat_database" sys.snap_pk sys.user_users
sys.all_types sys.dba_sequences sys."edb$stat_db_pk" sys.snapshot_num_seq sys.user_view_columns
sys.all_users sys.dba_source sys."edb$stat_idx_pk" sys.system_waits_pk sys.user_views
sys.all_view_columns sys.dba_subpart_key_columns sys."edb$statio_all_indexes" sys.user_all_tables sys._utl_file_dir
sys.all_views sys.dba_synonyms sys."edb$statio_all_tables" sys.user_cons_columns sys."v$version"
dbo.sysindexes dbo.sysobjects dbo.systables dbo.systypes dbo.sysusers
pg_catalog.accesshistoryrow pg_catalog.pg_description_o_c_o_index pg_catalog.pg_shdepend_reference_index
pg_catalog.breakpoint pg_catalog.pg_enum pg_catalog.pg_shdescription
pg_catalog.cpu_stats pg_catalog.pg_enum_oid_index pg_catalog.pg_shdescription_o_c_index
pg_catalog.edb_all_resource_groups pg_catalog.pg_enum_typid_label_index pg_catalog.pg_shseclabel
pg_catalog.edb_dblink pg_catalog.pg_enum_typid_sortorder_index pg_catalog.pg_shseclabel_object_index
pg_catalog.edb_dir pg_catalog.pg_event_trigger pg_catalog.pg_stat_activity
pg_catalog.edb_dir_name_index pg_catalog.pg_event_trigger_evtname_index pg_catalog.pg_stat_all_indexes
pg_catalog.edb_dir_oid_index pg_catalog.pg_event_trigger_oid_index pg_catalog.pg_stat_all_tables
pg_catalog.edb_icache_server_list pg_catalog.pg_extension pg_catalog.pg_stat_archiver
pg_catalog.edb_package pg_catalog.pg_extension_name_index pg_catalog.pg_stat_bgwriter
pg_catalog.edb_password_history pg_catalog.pg_extension_oid_index pg_catalog.pg_stat_database
pg_catalog.edb_password_history_role_password_index pg_catalog.pg_file_settings pg_catalog.pg_stat_database_conflicts
pg_catalog.edb_password_history_role_passwordsetat_index pg_catalog.pg_foreign_data_wrapper pg_catalog.pg_statio_all_indexes
pg_catalog.edb_pkgelements pg_catalog.pg_foreign_data_wrapper_name_index pg_catalog.pg_statio_all_sequences
pg_catalog.edb_policy pg_catalog.pg_foreign_data_wrapper_oid_index pg_catalog.pg_statio_all_tables
pg_catalog.edb_policy_object_name_index pg_catalog.pg_foreign_server pg_catalog.pg_statio_sys_indexes
pg_catalog.edb_policy_oid_index pg_catalog.pg_foreign_server_name_index pg_catalog.pg_statio_sys_sequences
pg_catalog.edb_profile pg_catalog.pg_foreign_server_oid_index pg_catalog.pg_statio_sys_tables
pg_catalog.edb_profile_name_index pg_catalog.pg_foreign_table pg_catalog.pg_statio_user_indexes
pg_catalog.edb_profile_oid_index pg_catalog.pg_foreign_table_relid_index pg_catalog.pg_statio_user_sequences
pg_catalog.edb_profile_password_verify_function_index pg_catalog.pg_function pg_catalog.pg_statio_user_tables
pg_catalog.edb_queue pg_catalog.pg_group pg_catalog.pg_statistic
pg_catalog.edb_queue_callback pg_catalog.pg_hba_file_rules pg_catalog.pg_statistic_ext
pg_catalog.edb_queue_callback_oid_index pg_catalog.pg_icu_collate_names pg_catalog.pg_statistic_ext_name_index
pg_catalog.edb_queue_callback_qid_owner_callbackaction_index pg_catalog.pg_index pg_catalog.pg_statistic_ext_oid_index
pg_catalog.edb_queue_name_nsp_index pg_catalog.pg_indexes pg_catalog.pg_statistic_ext_relid_index
pg_catalog.edb_queue_oid_index pg_catalog.pg_index_indexrelid_index pg_catalog.pg_statistic_relid_att_inh_index
pg_catalog.edb_queue_relid_index pg_catalog.pg_index_indrelid_index pg_catalog.pg_stat_progress_vacuum
pg_catalog.edb_queue_table pg_catalog.pg_inherits pg_catalog.pg_stat_replication
pg_catalog.edb_queue_table_name_nsp_index pg_catalog.pg_inherits_parent_index pg_catalog.pg_stats
pg_catalog.edb_queue_table_oid_index pg_catalog.pg_inherits_relid_seqno_index pg_catalog.pg_stat_ssl
pg_catalog.edb_queue_table_relid_index pg_catalog.pg_init_privs pg_catalog.pg_stat_subscription
pg_catalog.edb_resource_group pg_catalog.pg_init_privs_o_c_o_index pg_catalog.pg_stat_sys_indexes
pg_catalog.edb_resource_group_name_index pg_catalog.pg_language pg_catalog.pg_stat_sys_tables
pg_catalog.edb_resource_group_oid_index pg_catalog.pg_language_name_index pg_catalog.pg_stat_user_functions
pg_catalog.edb_variable pg_catalog.pg_language_oid_index pg_catalog.pg_stat_user_indexes
pg_catalog.frame pg_catalog.pg_largeobject pg_catalog.pg_stat_user_tables
pg_catalog.pg_aggregate pg_catalog.pg_largeobject_loid_pn_index pg_catalog.pg_stat_wal_receiver
pg_catalog.pg_aggregate_fnoid_index pg_catalog.pg_largeobject_metadata pg_catalog.pg_stat_xact_all_tables
pg_catalog.pg_am pg_catalog.pg_largeobject_metadata_oid_index pg_catalog.pg_stat_xact_sys_tables
pg_catalog.pg_am_name_index pg_catalog.pg_locks pg_catalog.pg_stat_xact_user_functions
pg_catalog.pg_am_oid_index pg_catalog.pg_matviews pg_catalog.pg_stat_xact_user_tables
pg_catalog.pg_amop pg_catalog.pg_namespace pg_catalog.pg_subscription
pg_catalog.pg_amop_fam_strat_index pg_catalog.pg_namespace_nspname_index pg_catalog.pg_subscription_oid_index
pg_catalog.pg_amop_oid_index pg_catalog.pg_namespace_oid_index pg_catalog.pg_subscription_rel
pg_catalog.pg_amop_opr_fam_index pg_catalog.pg_opclass pg_catalog.pg_subscription_rel_srrelid_srsubid_index
pg_catalog.pg_amproc pg_catalog.pg_opclass_am_name_nsp_index pg_catalog.pg_subscription_subname_index
pg_catalog.pg_amproc_fam_proc_index pg_catalog.pg_opclass_oid_index pg_catalog.pg_synonym
pg_catalog.pg_amproc_oid_index pg_catalog.pg_operator pg_catalog.pg_synonym_oid_index
pg_catalog.pg_attrdef pg_catalog.pg_operator_oid_index pg_catalog.pg_synonym_synname_nspoid_index
pg_catalog.pg_attrdef_adrelid_adnum_index pg_catalog.pg_operator_oprname_l_r_n_index pg_catalog.pg_tables
pg_catalog.pg_attrdef_oid_index pg_catalog.pg_opfamily pg_catalog.pg_tablespace
pg_catalog.pg_attribute pg_catalog.pg_opfamily_am_name_nsp_index pg_catalog.pg_tablespace_oid_index
pg_catalog.pg_attribute_relid_attnam_index pg_catalog.pg_opfamily_oid_index pg_catalog.pg_tablespace_spcname_index
pg_catalog.pg_attribute_relid_attnum_index pg_catalog.pg_partitioned_table pg_catalog.pg_timezone_abbrevs
pg_catalog.pg_authid pg_catalog.pg_partitioned_table_partrelid_index pg_catalog.pg_timezone_names
pg_catalog.pg_authid_oid_index pg_catalog.pg_pltemplate pg_catalog.pg_transform
pg_catalog.pg_authid_rolname_index pg_catalog.pg_pltemplate_name_index pg_catalog.pg_transform_oid_index
pg_catalog.pg_authid_rolprofile_index pg_catalog.pg_policies pg_catalog.pg_transform_type_lang_index
pg_catalog.pg_auth_members pg_catalog.pg_policy pg_catalog.pg_trigger
pg_catalog.pg_auth_members_member_role_index pg_catalog.pg_policy_oid_index pg_catalog.pg_trigger_oid_index
pg_catalog.pg_auth_members_role_member_index pg_catalog.pg_policy_polrelid_polname_index pg_catalog.pg_trigger_tgconstraint_index
pg_catalog.pg_available_extensions pg_catalog.pg_prepared_statements pg_catalog.pg_trigger_tgrelid_tgname_index
pg_catalog.pg_available_extension_versions pg_catalog.pg_prepared_xacts pg_catalog.pg_ts_config
pg_catalog.pg_cast pg_catalog.pg_proc pg_catalog.pg_ts_config_cfgname_index
pg_catalog.pg_cast_oid_index pg_catalog.pg_procedure pg_catalog.pg_ts_config_map
pg_catalog.pg_cast_source_target_index pg_catalog.pg_proc_oid_index pg_catalog.pg_ts_config_map_index
pg_catalog.pg_class pg_catalog.pg_proc_proname_args_nsp_index pg_catalog.pg_ts_config_oid_index
pg_catalog.pg_class_oid_index pg_catalog.pg_publication pg_catalog.pg_ts_dict
pg_catalog.pg_class_relname_nsp_index pg_catalog.pg_publication_oid_index pg_catalog.pg_ts_dict_dictname_index
pg_catalog.pg_class_tblspc_relfilenode_index pg_catalog.pg_publication_pubname_index pg_catalog.pg_ts_dict_oid_index
pg_catalog.pg_collation pg_catalog.pg_publication_rel pg_catalog.pg_ts_parser
pg_catalog.pg_collation_name_enc_nsp_index pg_catalog.pg_publication_rel_oid_index pg_catalog.pg_ts_parser_oid_index
pg_catalog.pg_collation_oid_index pg_catalog.pg_publication_rel_prrelid_prpubid_index pg_catalog.pg_ts_parser_prsname_index
pg_catalog.pg_config pg_catalog.pg_publication_tables pg_catalog.pg_ts_template
pg_catalog.pg_constraint pg_catalog.pg_range pg_catalog.pg_ts_template_oid_index
pg_catalog.pg_constraint_conname_nsp_index pg_catalog.pg_range_rngtypid_index pg_catalog.pg_ts_template_tmplname_index
pg_catalog.pg_constraint_conrelid_index pg_catalog.pg_replication_origin pg_catalog.pg_type
pg_catalog.pg_constraint_contypid_index pg_catalog.pg_replication_origin_roiident_index pg_catalog.pg_type_oid_index
pg_catalog.pg_constraint_oid_index pg_catalog.pg_replication_origin_roname_index pg_catalog.pg_type_typname_nsp_index
pg_catalog.pg_conversion pg_catalog.pg_replication_origin_status pg_catalog.pg_user
pg_catalog.pg_conversion_default_index pg_catalog.pg_replication_slots pg_catalog.pg_user_mapping
pg_catalog.pg_conversion_name_nsp_index pg_catalog.pg_rewrite pg_catalog.pg_user_mapping_oid_index
pg_catalog.pg_conversion_oid_index pg_catalog.pg_rewrite_oid_index pg_catalog.pg_user_mappings
pg_catalog.pg_cursors pg_catalog.pg_rewrite_rel_rulename_index pg_catalog.pg_user_mapping_user_server_index
pg_catalog.pg_database pg_catalog.pg_roles pg_catalog.pg_variable_oid_index
pg_catalog.pg_database_datname_index pg_catalog.pg_rules pg_catalog.pg_variable_varname_pkg_index
pg_catalog.pg_database_oid_index pg_catalog.pg_seclabel pg_catalog.pg_views
pg_catalog.pg_db_role_setting pg_catalog.pg_seclabel_object_index pg_catalog.process_info
pg_catalog.pg_db_role_setting_databaseid_rol_index pg_catalog.pg_seclabels pg_catalog.proxyinfo
pg_catalog.pg_default_acl pg_catalog.pg_sequence pg_catalog.session_wait_history
pg_catalog.pg_default_acl_oid_index pg_catalog.pg_sequences pg_catalog.session_waits
pg_catalog.pg_default_acl_role_nsp_obj_index pg_catalog.pg_sequence_seqrelid_index pg_catalog.stats_record
pg_catalog.pg_depend pg_catalog.pg_settings pg_catalog.system_waits
pg_catalog.pg_depend_depender_index pg_catalog.pg_shadow pg_catalog.targetinfo
pg_catalog.pg_depend_reference_index pg_catalog.pg_shdepend pg_catalog.var
pg_catalog.pg_description pg_catalog.pg_shdepend_depender_index
set db_dialect = 'redwood';
set db_dialect = 'postgres';
7、optimizer_mode
Oracle 優化器，根據用戶端的想法來對SQL進行優化。比如用戶端是否想先接收10條記錄，還是想接受所有記錄等。
PostgreSQL 優化器，則通過cursor_tuple_fraction參數來告訴優化器同樣的事情，不過它更加彈性，因為是一個0-1的比例值。
src/backend/optimizer/plan/planner.c
https://www.postgresql.org/docs/9.0/static/runtime-config-query.htmlpostgres=# show cursor_tuple_fraction ;
cursor_tuple_fraction
-----------------------
0.1
(1 row)
set optimizer_mode=choose;
#optimizer_mode = choose # Oracle-style optimizer hints.
# choose, all_rows, first_rows,
# first_rows_10, first_rows_100,
# first_rows_1000
Table 2-2 - Optimizer Modes
| Hint | Description |
|---|---|
| ALL_ROWS | Optimizes for retrieval of all rows of the result set. |
| CHOOSE | Does no default optimization based on assumed number of rows to be retrieved from the result set. This is the default. |
| FIRST_ROWS | Optimizes for retrieval of only the first row of the result set. |
| FIRST_ROWS_10 | Optimizes for retrieval of the first 10 rows of the results set. |
| FIRST_ROWS_100 | Optimizes for retrieval of the first 100 rows of the result set. |
| FIRST_ROWS_1000 | Optimizes for retrieval of the first 1000 rows of the result set. |
set optimizer_mode=choose;
8、edb_early_lock_release
#edb_early_lock_release = off # release locks for prepared statements
# when the portal is closed
在使用prepared statement時，當portal關閉，是否釋放鎖。
設定為ON時，與Oracle相容，portal關閉即釋放。
設定為OFF時，與目前的PG版本相容，等COMMIT時釋放。
set edb_early_lock_release=on;
set edb_early_lock_release=off;
9、datestyle
預設日期格式
postgres=# set datestyle = 'redwood,show_time';
SET
postgres=# select now();
now
----------------------------------
11-APR-18 23:42:53.840558 +08:00
(1 row)
#datestyle = 'iso, ymd' # PostgreSQL default for your locale
postgres=# set datestyle = 'iso, ymd';
SET
postgres=# select now();
now
-------------------------------
2018-04-11 23:43:05.554233+08
(1 row)
10、行号
Oracle相容模式：
default_with_oids = on
default_with_rowids = on
開啟rowids後，會新增OID列，同時設定為UNIQUE，建立唯一索引。
沒必要的話就不要用了。OID是UNIT32，也就也為這這個表最多隻能存放40億條記錄。同時增加了存儲空間，索引開銷。
postgres=# set default_with_rowids =on;
SET
postgres=# create table b(id int);
CREATE TABLE
postgres=# \d+ b
Table "public.b"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
Indexes:
"pg_oid_32035_index" UNIQUE, btree (oid)
Has OIDs: yes
postgres=# insert into b select generate_series(1,10);
INSERT 0 10
postgres=# select oid,rownum,* from b;
oid | rownum | id
-------+--------+----
32039 | 1 | 1
32040 | 2 | 2
32041 | 3 | 3
32042 | 4 | 4
32043 | 5 | 5
32044 | 6 | 6
32045 | 7 | 7
32046 | 8 | 8
32047 | 9 | 9
32048 | 10 | 10
(10 rows)
postgres=# update b set id=2 where id=1;
UPDATE 1
ROWNUM與資料庫的AM掃描方法相關，與掃描順序一緻。如果是SEQSCAN則與CTID順序一緻，如果是INDEX SCAN則與INDEX傳回順序一緻。
postgres=# create index idx_b on b(id);
CREATE INDEX
postgres=# explain select oid,rownum,*,ctid from b order by id;
QUERY PLAN
---------------------------------------------------------
Sort (cost=1.27..1.29 rows=10 width=22)
Sort Key: id
-> Seq Scan on b (cost=0.00..1.10 rows=10 width=22)
(3 rows)
由于使用了SEQSCAN，SEQ按BLOCK, ITEM OFFSET順序掃描，是以ROWNUM與CTID(blockid, itemoffset)的順序一緻
postgres=# select oid,rownum,*,ctid from b order by id;
oid | rownum | id | ctid
-------+--------+----+--------
32040 | 1 | 2 | (0,2)
32039 | 10 | 2 | (0,11)
32041 | 2 | 3 | (0,3)
32042 | 3 | 4 | (0,4)
32043 | 4 | 5 | (0,5)
32044 | 5 | 6 | (0,6)
32045 | 6 | 7 | (0,7)
32046 | 7 | 8 | (0,8)
32047 | 8 | 9 | (0,9)
32048 | 9 | 10 | (0,10)
(10 rows)
postgres=# set enable_seqscan=off;
SET
postgres=# explain select oid,rownum,*,ctid from b order by id;
QUERY PLAN
-----------------------------------------------------------------
Index Scan using idx_b on b (cost=0.14..3.58 rows=10 width=22)
(1 row)
此時ROWNUM與索引掃描的順序一緻
postgres=# select oid,rownum,*,ctid from b order by id;
oid | rownum | id | ctid
-------+--------+----+--------
32039 | 1 | 2 | (0,11)
32040 | 2 | 2 | (0,2)
32041 | 3 | 3 | (0,3)
32042 | 4 | 4 | (0,4)
32043 | 5 | 5 | (0,5)
32044 | 6 | 6 | (0,6)
32045 | 7 | 7 | (0,7)
32046 | 8 | 8 | (0,8)
32047 | 9 | 9 | (0,9)
32048 | 10 | 10 | (0,10)
(10 rows)
PostgreSQL相容模式：
default_with_oids = off
default_with_rowids = off
11、oracle_home
指定Oracle的OCI目錄，用到OCI，相容Oracle DBLINK。
#oracle_home ='' # path to the Oracle home directory;
# only used by OCI Dblink; defaults
# to ORACLE_HOME environment variable.
https://github.com/digoal/blog/blob/master/201804/20180411_02.md#%E5%B0%8F%E7%BB%93%E5%85%BC%E5%AE%B9%E6%A8%A1%E5%BC%8F%E5%8F%82%E6%95%B0%E8%AE%BE%E7%BD%AE 小結，相容模式參數設定
# - Oracle compatibility -
edb_redwood_date = on # translate DATE to TIMESTAMP(0)
edb_redwood_greatest_least = on # GREATEST/LEAST are strict
edb_redwood_strings = on # treat NULL as an empty string in
# string concatenation
edb_redwood_raw_names = on # don't uppercase/quote names in sys views
# edb_stmt_level_tx = off # 即使是ORACLE也建議關閉
edb_stmt_level_tx = on # allow continuing on errors instead
# rolling back
db_dialect = 'redwood' # Sets the precedence of built-in
# namespaces.
# 'redwood' means sys, dbo, pg_catalog
# 'postgres' means pg_catalog, sys, dbo
optimizer_mode = choose # Oracle-style optimizer hints.
# choose, all_rows, first_rows,
# first_rows_10, first_rows_100,
# first_rows_1000
edb_early_lock_release = on # release locks for prepared statements
# when the portal is closed
#oracle_home ='' # path to the Oracle home directory;
# only used by OCI Dblink; defaults
# to ORACLE_HOME environment variable.
datestyle = 'redwood,show_time'
default_with_oids = on
default_with_rowids = on
edb_redwood_date = off # translate DATE to TIMESTAMP(0)
edb_redwood_greatest_least = off # GREATEST/LEAST are strict
edb_redwood_strings = off # treat NULL as an empty string in
# string concatenation
edb_redwood_raw_names = off # don't uppercase/quote names in sys views
edb_stmt_level_tx = off
db_dialect = 'postgres' # Sets the precedence of built-in
# namespaces.
# 'redwood' means sys, dbo, pg_catalog
# 'postgres' means pg_catalog, sys, dbo
optimizer_mode = choose # Oracle-style optimizer hints.
# choose, all_rows, first_rows,
# first_rows_10, first_rows_100,
# first_rows_1000
edb_early_lock_release = off # release locks for prepared statements
# when the portal is closed
datestyle = 'iso, ymd' # PostgreSQL default for your locale
default_with_oids = off
default_with_rowids = off
除了PPAS是一個高度相容Oracle的版本，如果使用者想在社群版本PG上相容Oracle，需要注意什麼，可參考：
https://github.com/digoal/blog/blob/master/class/21.md社群版本PG也有一個很好的相容包： orafce
https://github.com/orafce/orafcehttps://github.com/digoal/blog/blob/master/201804/20180411_02.md#%E5%85%B6%E4%BB%96edb-ppas%E7%8B%AC%E6%9C%89%E5%8F%82%E6%95%B0 其他EDB PPAS獨有參數
1、外部memcache緩存
# - InfiniteCache
#edb_enable_icache = off
#edb_icache_servers = '' #'host1:port1,host2,ip3:port3,ip4'
#edb_icache_compression_level = 6
2、資源組管理
《PostgreSQL 商用版本EPAS(阿裡雲ppas(Oracle 相容版)) HTAP功能之資源隔離管理 - CPU與刷髒資源組管理》# - EDB Resource Manager -
edb_max_resource_groups = 16 # 0-65536 (change requires restart)
#edb_resource_group = ''
#edb_enable_pruning = on # fast pruning for EDB-partitioned tables
#edb_custom_plan_tries = 5 # 0 disable custom plan evaluation
3、審計日志，PG的用法類似，隻是EDB PPAS把參數名改了
#---------------------------------------------------------------------------
# EDB AUDIT
#---------------------------------------------------------------------------
#edb_audit = 'none' # none, csv or xml
# These are only used if edb_audit is not none:
#edb_audit_directory = 'edb_audit' # Directory where log files are written
# Can be absolute or relative to PGDATA
#edb_audit_filename = 'audit-%Y-%m-%d_%H%M%S' # Audit file name pattern.
# Can include strftime() escapes
#edb_audit_rotation_day = 'every' # Automatic rotation of logfiles based
# on day of week. none, every, sun,
# mon, tue, wed, thu, fri, sat
#edb_audit_rotation_size = 0 # Automatic rotation of logfiles will
# happen after this many megabytes (MB)
# of log output. 0 to disable.
#edb_audit_rotation_seconds = 0 # Automatic log file rotation will
# happen after this many seconds.
#edb_audit_connect = 'failed' # none, failed, all
#edb_audit_disconnect = 'none' # none, all
#edb_audit_statement = 'ddl, error' # Statement type to be audited:
# none, dml, insert, update, delete, truncate,
# select, error, rollback, ddl, create, drop,
# alter, grant, revoke, all
#edb_audit_tag = '' # Audit log session tracking tag.
#edb_log_every_bulk_value = off # Writes every set of bulk operation
# parameter values during logging.
# This GUC applies to both EDB AUDIT and PG LOGGING.
#edb_audit_destination = 'file' # file or syslog
4、自動優化。
postgresql.conf中的設定優先級更高，如果設定了postgresql.conf的參數(比如shared buffer)，将覆寫自動優化産生的參數指。
#---------------------------------------------------------------------------
# DYNA-TUNE
#---------------------------------------------------------------------------
edb_dynatune = 100 # percentage of server resources
# dedicated to database server,
# defaults to 66
edb_dynatune_profile = mixed # workload profile for tuning.
# 'oltp', 'reporting' or 'mixed',
5、initdb初始化時的選擇，是否需要安裝redwood相關的視圖、轉換
initdb --help|less
--no-redwood-compat do not install Redwood-compatibility casts and views
--redwood-like use Redwood-compatible LIKE behavior
Oracle相容, --redwood-like
PostgreSQL相容, --no-redwood-compat
![阿裡雲天池龍珠計劃SQL訓練營day1[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)