EDB PPAS(Oracle 相容版) Oracle與PostgreSQL 相容模式的引數配置切換

weixin_34205076發表於2018-05-06

標籤

PostgreSQL , EDB , PPAS , 引數 , Oracle模式 , PostgreSQL模式


背景

EDB PPAS是EDB推出的一款同時相容Oracle和PostgreSQL協議的資料庫,在去O的場景中,使用非常廣泛,價格便宜,同時效能和Oracle差不多,並且SQL語法,儲存過程等相容性都特別好。

除了Oracle相容,EDB PPAS實際上底層是PostgreSQL,如果你想把PPAS跑在相容PG的模式下,需要調整一些引數。(因為Oracle和PG在某些功能點上的取向不太一致,下面舉例)

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  

逐條講解

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模式,後面有解釋。

Oracle 相容模式:

postgres=# set edb_redwood_greatest_least =on;  
SET  
  
postgres=# select greatest(1,null,2);  
 greatest   
----------  
           
(1 row)  

PostgreSQL 相容模式:

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

STRICT

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.

Oracle 相容模式:

postgres=# set edb_redwood_strings=on;  
SET  
  
postgres=# select 'a'||null||'b';  
 ?column?   
----------  
 ab  
(1 row)  

PostgreSQL 相容模式:

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

Oracle 相容模式:

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)  

PostgreSQL 相容模式:

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)   

PostgreSQL 相容模式:

\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    

Oracle 相容模式:

set db_dialect = 'redwood';  

PostgreSQL 相容模式:

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

postgres=# show cursor_tuple_fraction  ;  
 cursor_tuple_fraction   
-----------------------  
 0.1  
(1 row)  

Oracle 相容模式:

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.

PostgreSQL 相容模式:

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時釋放。

Oracle 相容模式:

set edb_early_lock_release=on;  

PostgreSQL 相容模式:

set edb_early_lock_release=off;  

9、datestyle

預設日期格式

Oracle 相容模式:

postgres=# set datestyle = 'redwood,show_time';  
SET  
  
postgres=# select now();  
               now                  
----------------------------------  
 11-APR-18 23:42:53.840558 +08:00  
(1 row)  

PostgreSQL 相容模式:

#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相容模式:

#oracle_home =''                        # path to the Oracle home directory;  
                                        # only used by OCI Dblink; defaults  
                                        # to ORACLE_HOME environment variable.  

小結,相容模式引數設定

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

PostgreSQL 相容模式:

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/orafce

其他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

相關文章