Postgresql Trigger -- (II) 待完

做個Trigger的範例。歷程功能。

相信大家都曾經碰過這樣的狀況,  有一些非常非常重要的資料表，像是金錢往來的會計資料，記錄進出貨的庫存資料，我們希望對它做的所有操作，都能留下記錄，以便萬一出事的時候，可以查到倒底是誰動過資料。

要達到這樣的效果，有兩種做法，一種是在應用程式裡面做，一開始的時候，就開兩張資料表，一張主資料表，一張歷程資料表，主資料表中記下誰，在什麼時候動過資料，歷程資料表中則多一個欄位，記錄是做了什麼動作（新增、修改、刪除）

可是世界往往不像我們想像的那麼美好，有誰會知道，有一天某張資料表會變得那麼重要，重要到要幫它做歷程呢？

因此，大部份狀況，我們都是臨時決定，或被通知，要幫某資料表做歷程。在這種狀況下，往往系統都已經上線使用，要請廠商修改，都要花一筆錢。這種狀況下，PostgreSQL的Trigger就幫上忙了。

第二種作法就是，你可以臨時加開一張歷程表，利用Trigger，設定主資料表有任何動作時（新增、修改、刪除），都要到歷程表中寫入歷程資料。但是，要特別提出來的，主資料表中，一定要記下誰，在什麼時候動過資料，這種做法才能夠適用，因此，記得，在採購任何系統時，一定要記得提出，每張資料表中，必需有operateBy (最後資料操作者)，這個欄位，以免碰上要加歷程功能時，廠商還要修改加錢。

如果你忘記了，那Trigger只能幫我們留下，主資料表在什麼時候 operateAt，被做了什麼操作operate。至於是誰，你可能要到系統的登入記錄去找找，那個時間，有那一個有權限的人登入了系統。至於是不是他做的，那就還有得爭了。

-- Drop Table exgRateLog;
-- Drop Table exgRate;


Create Table exgRate (
   exgRateId serial primary key, -- 主鍵
   factTime timestamp,    　       -- 匯入時間
   currency char(3),          　　   -- 貨幣別
   purpose varchar(2),   　        -- 匯率種類
   currType varchar(10),     　    -- 貨幣種類
   rate numeric(18,6),         　   -- 匯率
   operateAt timestamp,
   operateBy varchar(15)
);

Create Table exgRateLog (
   exgRateId serial primary key, -- 主鍵
   factTime timestamp,    　       -- 匯入時間
   currency char(3),          　　   -- 貨幣別
   purpose varchar(2),   　        -- 匯率種類
   currType varchar(10),     　    -- 貨幣種類
   rate numeric(18,6),         　   -- 匯率

   operateAt timestamp,

   operateBy varchar(15),

   operate char(1)
);

Postgresql Trigger -- (I)

Trigger 中文名稱叫觸發器。

為什麼要叫觸發器? 因為我們希望，我們可以在某個資料表上，設定在某個動作（新增、修改、刪除）之前或之後，PostgreSQL能夠被觸發，自動執行一段我們設計好的程式碼。

所以，觸發器的使用設計步驟可分為兩個動作

1. 設計要自動執行的程式碼，建立一個函數Function

2. 對要監控的資料表，設定觸發器，以呼叫已建立的函數。

3. 測試

Partition Table -- (III)

因為partition table 談到rule 和 trigger，　所以打算介紹一下。

前面在談到
－－－
開發rule 或 trigger, 對操作主資料表的行為（新增、修改、刪除），重導到子資料表去。
－－－
這個東西，對一般的開發者來說，恐怕是十分陌生。簡單講，當你不想改，或無法改客戶端程式的時候，你就需要rule或trigger。

想像一下下面的場景，公司或你，從某廠商那裡採購了一套系統，剛開始大家用得非常高興，經過一年兩年，你有新的需求，跟廠商談了以後，他給了一個天價，要不然就不做，或是很不幸地，這家廠商倒了，你也找不到人來幫你改。

這時候，這套系統對公司來說已經是不可或缺；你又沒錢，或是有錢但禁不起轉換系統的風險，而你需要的，只是小小的一個功能修改。你該怎麼做？

如果你使用PostgreSQL做為後端的資料庫，PostgreSQL支援trigger和rule來幫你，讓你不需要修改廠商的程式，也可以達到你要的效果。

Partition Table -- (II)

根據PostgreSQL網站的資料
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
PostgreSQL支援 1. Range Partiontion    2. List Partition   兩種分割方式。實際上, 這兩種分割方式只是應用的不同, 它底層的機制, 是相同的。

我們最常使用到的分割，大概就是Range Partition。我們常會碰到隨著日期增加，資料量也不斷增加的資料，像是銀行的匯率資料，計程車隊的GPS定位資料等。這些資料，有些是不能丟，必需保存做為統計資料使用，這種資料，就必需使用partition的技術，進行保存，以方便後續的查詢使用。

但是，以系統設計的觀點來看，當你的資料會隨時間無限制增大的時候，整個系統的效能，勢必會被拖垮，容我提醒一聲，Patition並不是萬靈丹，它只是讓你操控大容量資料的一個手段而已，以之前提到的案例，銀行的匯率資料，過了一年（一個會計經營年度），恐怕就沒有什麼使用的價值，GPS定位資料，過了一個月（一個拆帳結算周期），恐怕也就沒什麼再使用的必要，在系統設計時，必需設計一個定期清理的機制，每年每月，將資料搬到備份表或歷程表中。只留下有使用價值的資料區間。

系統要使用的，必需是一個穩定的資料量或資料區間，否則，你就無法保證系統的穩定性。在這個設計前提下，我們才可以使用Partition的技術，技術是為系統服務的，絕對不要陷入為技術而技術的境地，這會傷害到你個人的價值。

簡單地講，PostgreSQL Partition Table的使用步驟如下：

1. 建立一個主資料表 Master table，主資料表必需有一個關鍵欄位，做為分隔界限的依據。
2. 繼承主資料表，建立子資料表 child table
3. 在子資料表建立限制Constraint, 限定關鍵欄位的區間間隔。
4. 在子資料表針對可能的查詢欄位，建立索引index
5. 開發rule 或 trigger, 對操作主資料表的行為（新增、修改、刪除），重導到子資料表去。
6. 把postgresql.conf 設定檔中的constraint exclusion參數打開，這樣針對主資料表的查詢，會針對子資料表進行最佳化。

完成上述的步驟後，我們對主資料表的新增、修改、刪除，PostgreSQL會自動地，對相應的子資料表做動作，當我們對資料表做出大資料量的查詢時，PostgreSQL會將查詢改寫，針對子資料表查詢，我們不會感覺到子資料表partition table 的存在。

Partition Table -- (I)

打算深入地談談partition table。
如果曾經開發過一些真實運行的系統，那就一定會碰上一個問題，資料量過大。

舉個例子來說，像是計程車業用的GPS衛星定位系統，每台車，每隔三分鐘就傳回最新的位置，一天就會累積 (60 min / 3 duration) *24 = 480 筆資料，一年就是 175,200筆資料，以200台加盟計程車來算，一年會累積的資料大概就會有3504萬筆資料。

三千五百多萬筆資料，可以想見的，如果要你查查某台車，在某個時間所經過的位置，那對資料庫會是多大的負擔。所以當我們碰上巨量的資料要儲存、查詢時，就需要資料庫支援一種特殊的機制 Patitioning，將巨量的資料分散成為小的資料區塊。

這也就是要談的 PostgreSQL partition table.

PostgreSQL Crosstab -- (VII)

之前資料格式有點雜亂, 利用 group by 整理一下
交叉表三 Version 2
------------------------------------
select currency, purpose, sum(即期)as 即期,sum(現金) as 現金
From
(
SELECT *
FROM crosstab(
  'select currency, purpose, currType, rate From exgRate',
  'Select distinct currType from exgRate' )
  AS t(
       currency char(3),
       purpose varchar(10),
       "即期" numeric(18,6),
       "現金" numeric(18,6)
       ) order by purpose, currency
) dt group by currency, purpose

PostgreSQL Crosstab -- (VI)

交叉表三
－－－－－－－－－－－－－－－－－－－
SELECT *
FROM crosstab(
  'select currency, purpose, currType, rate From exgRate',
  'Select distinct currType from exgRate' )
  AS t(
       currency char(3),
       purpose varchar(10),
       "即期" numeric(18,6),
       "現金" numeric(18,6)
       ) order by purpose, currency;