MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

由於考慮採用 EnterpriseDB 或是直接用 PostgreSQL 的人，通常需要一些入手的資料。這邊紀錄便提供相關快速上手的簡單筆記 ～ 這篇筆記以 資料庫安裝完畢後的快速使用 為目標，基本紀錄登入使用的範例：

2018年4月2日 星期一

EDB Postgres 10 企業版裡面的 Partitioned Table 系統表

在 PostgreSQL 10 引進了原生的 Partitioned Table 語法，EDB 的企業版在很久之前就有 Tartitioned Table。各自也都有相關的系統表。那麼，這些系統表有相互含括嗎？
這裡紀錄一下這個問題，然後順便偷懶，當作新的 Partitioned Table 語法功能筆記～

以下參考 Postgres 10 Partitioned Table 原生語法範例與 EDB 10 企業版範例，分別建立 Partitioned Table。然後查詢 Postgres 10 新增的原生系統表與企業版的 Oracle 相容 Data Dictionary。

首先是利用 EDB 10 企業版語法建立 Partitioned Table，然後分別查找原生 Partitioned Table 系統表 pg_partitioned_table 與企業版的 ALL_PART_TABLES 
edb=# -- 驗明正身～
edb=# select version();
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 EnterpriseDB 10.1.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)


edb=# 
edb=# CREATE TABLE sales
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
)
PARTITION BY LIST(country)
(
PARTITION europe VALUES('FRANCE', 'ITALY'),
PARTITION asia VALUES('INDIA', 'PAKISTAN'),
PARTITION americas VALUES('US', 'CANADA')
);
CREATE TABLE
edb=#
edb=# \x
Expanded display is on.
edb=# select * from pg_partitioned_table;
-[ RECORD 1 ]-+------
partrelid     | 16384
partstrat     | l
partnatts     | 1
partattrs     | 3
partclass     | 3126
partcollation | 100
partexprs     | 
partnullorder | 0

edb=# select * from all_part_tables ;
-[ RECORD 1 ]-------------+-------------
owner                     | ENTERPRISEDB
schema_name               | PUBLIC
table_name                | SALES
partitioning_type         | LIST
subpartitioning_type      | NONE
partition_count           | 3
def_subpartition_count    | 0
partitioning_key_count    | 1
subpartitioning_key_count | 
status                    | VALID
def_tablespace_name       | 
def_pct_free              | 
def_pct_used              | 
def_ini_trans             | 
def_max_trans             | 
def_initial_extent        | 
def_next_extent           | 
def_min_extents           | 
def_max_extents           | 
def_pct_increase          | 
def_freelists             | 
def_freelist_groups       | 
def_logging               | YES
def_compression           | NONE
def_buffer_pool           | DEFAULT
ref_ptn_constraint_name   | 
interval                  |

edb=#
edb=# \d+ sales
                                              Table "public.sales"
 Column  |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
---------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 dept_no | numeric                     |           |          |         | main     |              |
 part_no | character varying           |           |          |         | extended |              |
 country | character varying(20)       |           |          |         | extended |              |
 date    | timestamp without time zone |           |          |         | plain    |              |
 amount  | numeric                     |           |          |         | main     |              |
Partition key: LIST (country)
Partitions: sales_americas FOR VALUES IN ('US', 'CANADA'),
            sales_asia FOR VALUES IN ('INDIA', 'PAKISTAN'),
            sales_europe FOR VALUES IN ('FRANCE', 'ITALY')

edb=#

上面可見，透過 Oracle 相容語法建立的 Partitioned Table 也會在 pg_partitioned_table 裡面註冊相關資訊。

下面切換到 postgres database 裡面，用原生語法建立 Partitioned Table。一樣用兩種 
edb=# \c postgres
You are now connected to database "postgres" as user "enterprisedb".
postgres=#
postgres=# --PGSQL10 原生語法
postgres=# 
postgres=# CREATE TABLE measurement_year_month (
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));

CREATE TABLE measurement_ym_older
    PARTITION OF measurement_year_month
    FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);

CREATE TABLE measurement_ym_y2016m11
    PARTITION OF measurement_year_month
    FOR VALUES FROM (2016, 11) TO (2016, 12);

CREATE TABLE measurement_ym_y2016m12
    PARTITION OF measurement_year_month
    FOR VALUES FROM (2016, 12) TO (2017, 01);

CREATE TABLE measurement_ym_y2017m01
    PARTITION OF measurement_year_month
    FOR VALUES FROM (2017, 01) TO (2017, 02);
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
postgres=#
postgres=# select * from pg_partitioned_table;
-[ RECORD 1 ]-+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
partrelid     | 16408
partstrat     | r
partnatts     | 2
partattrs     | 0 0
partclass     | 3123 3123
partcollation | 0 0
partexprs     | ({FUNCEXPR :funcid 2021 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 100 :args ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 152 :constvalue 8 [ 32 0 0 0 121 101 97 114 ]} {VAR :varno 1 :varattno 1 :vartype 1114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 162}) :location 144} {FUNCEXPR :funcid 2021 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 100 :args ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 180 :constvalue 9 [ 36 0 0 0 109 111 110 116 104 ]} {VAR :varno 1 :varattno 1 :vartype 1114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 191}) :location 172})
partnullorder | 0

postgres=#
postgres=# select * from all_part_tables ;
-[ RECORD 1 ]-------------+-----------------------
owner                     | ENTERPRISEDB
schema_name               | PUBLIC
table_name                | MEASUREMENT_YEAR_MONTH
partitioning_type         | RANGE
subpartitioning_type      | NONE
partition_count           | 4
def_subpartition_count    | 0
partitioning_key_count    | 2
subpartitioning_key_count | 
status                    | VALID
def_tablespace_name       | 
def_pct_free              | 
def_pct_used              | 
def_ini_trans             | 
def_max_trans             | 
def_initial_extent        | 
def_next_extent           | 
def_min_extents           | 
def_max_extents           | 
def_pct_increase          | 
def_freelists             | 
def_freelist_groups       | 
def_logging               | YES
def_compression           | NONE
def_buffer_pool           | DEFAULT
ref_ptn_constraint_name   | 
interval                  | 

postgres=#
postgres=# \d+ measurement_year_month
                                      Table "public.measurement_year_month"
  Column   |            Type             | Collation | Nullable | Default | Storage | Stats target | Description
-----------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
 logdate   | timestamp without time zone |           | not null |         | plain   |              |
 peaktemp  | integer                     |           |          |         | plain   |              |
 unitsales | integer                     |           |          |         | plain   |              |
Partition key: RANGE (date_part('year'::text, logdate), date_part('month'::text, logdate))
Partitions: measurement_ym_older FOR VALUES FROM (MINVALUE, MINVALUE) TO ('2016', '11'),
            measurement_ym_y2016m11 FOR VALUES FROM ('2016', '11') TO ('2016', '12'),
            measurement_ym_y2016m12 FOR VALUES FROM ('2016', '12') TO ('2017', '1'),
            measurement_ym_y2017m01 FOR VALUES FROM ('2017', '1') TO ('2017', '2')

postgres=#

上面可以看到，原生語法建立的 Partitioned Table 也可以在企業版的 data dictionary 查看得到。這讓 Partitioned Table 狀況查看在企業版更為容易～
此外，兩個 Partitioned Table 語法的內部架構上應該還是有一點點差異，從 pg_partitioned_table 可以略窺一二。

既然這篇要順道紀錄 Partitioned Table 功能，就再紀錄一下以下幾點：
  • Oracle 語法利用 Partitioned Name 管理，不會直接看 Table Partitions 名稱；建立 PGSQL 10 partitioned table 則會自動指定 Partitioned Name

  • 在 EDB10 裡面，企業版語法提供 List/Range/Hash 三種，而原生的支援 List/Range 兩種（PGSQL 11 將引入 Hash Partitioned Table 支援） 

  • PGSQL 10 的 Partitioned Table Index 建立目前還是需要逐個對個別 Table Partitions 建立；此外，目前還沒支援類似 Oracle 的 Global Index 這種跨 Partition 的 Unique Constraint 物件。

  • 目前的 Partitioned Table 還不支援 Upsert 功能（INSERT ... ON CONFLICT）；也還沒支援更新 Partitioned Key 欄位的資料（涉及資料的跨表格搬遷）

  • 查詢上，還是需要調整 constraint_exclusion 為 partition 才能讓查詢把 Partitioned Key 納入執行計畫考量。不過這已經預設好了，其實不用管它～

  • 舊方法的 Table Partitioning 還是能用的！先前的幾個 Partitioned Table 管理套件都還是能在 PGSQL 10 使用（例如，pg_partman 或是 pgslice 等擴充套件）

  • 在 pgAdmin4 v2.1 裡面，正式支援 Partitioned Table 的資訊。呈現上，只會呈現母表，點開之後，才會看到列舉 Partitions，點右鍵就可以建立 Index。


最後，根據 PostgresWeekly  2018 年一月號電子報發出的訊息，PGSQL 11 的 Partitioned Table 將會改善建立 Index 的功能，可以對母表下達 Index 建立指令，就自動幫所有 Table Partitions 建立完畢；以及更新 Partitioned Key 欄位資料（跨 Table Partition 資料異動）。目前 Partitoined Table 還正在優化中，後續會不斷有更多強化功能（例如，跟 Foreign data Wrapper 整合～），請各位拭目以待。


最後，看來 EDB 企業版與 PGSQL 原生功能的整合性還不錯，功能都不會延遲跟上～


參考資料
舊方法的 Partitioned Table：還是能用的
EDB 10 企業版手冊
PostgreSQL 10 手冊
於
標籤： ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)