[台灣應用實列]從 Oracle 遷移到 PostgreSQL
更新:2007-04-18
對映章節:
作者簡介: ingram, chen
Java狂，熱衷 server-side open source 技術。現於中研院某角落謀有一職，兼任 DinBenDon 站
長。(本文已連絡原著作者, 經同意後轉載.)
阿益:這是一篇很棒的遷移經驗談, 更具說服力的是作者來自台灣最高的學術研究單位:國立中央研究院, 從原本使用者 Oracle 到為何選擇 PostgreSQL 做了這篇文, 感謝作者同意轉載.
內容:
我們單位已經開始規畫將原本 oracle 的資料庫轉移到 PostgreSQL，原因無它，Oracle 對學術單位來說太 over kill，我們也鮮少使用 oracle 進階的功能。基於經費、維護上的考量，最後選擇改採 Open source 的 PostgreSQL。至於為啥不用 MySQL ? 表面的理由是 postgreSQL 和 oracle 在很多方面都很類似，轉移比較簡單，而 PostgreSQL 功能也比 MySQL 完整太多了。背後的理由嘛... 算是個人偏好與信仰吧？MySQL vs. PostgreSQL 對我來說就像是 VB vs. Java一樣。選擇 Java 的我自然也就選擇 PostgreSQL 囉。
Lob, Lob, Lob
轉移 db 最痛苦的欄位非 Blob, Clob 莫屬了，因為各家 db 對 lob 的做法都不一樣。PostgreSQL 本身並不支援Blob, Clob 欄位。你得使用 bytea, oid, text 等欄位來模擬。text 可以對應的 jdbc clob。而 bytea 與 oid 則對應到 Blob。bytea(byte array) 可直接在該欄位上儲存所有 binary 的資料，算是最簡單的做法。但缺點是 query 該 row 時也會一口氣把全部的資料都讀到記憶體裡... 除非是儲存一些 .gif, .jpg 之類的小檔，不然還是選擇採間接儲存的 oid 比較好。現行 hibernate 的工具會替 java Blob 產生 oid 的欄位型別。
oid 是 PostgreSQL 一個內藏指標，任何資料庫物件 (sequence/table/row... etc) 都有一個 oid。而 oid 欄位就是可儲存 oid 值的欄位。PosgreSQL 裡還有一個系統 table 叫 pg_largeobject，所有的 lob 物件都會存到這個公用的 table 上。我們來看看 blob 的資料會怎樣儲存：
table: MY_BLOBS
id(int8) filename(varchar) blob_data(oid)
==================================================
12 mydata.xls 20300
13 yourdata.png 20301
table: pg_largeobject
loid pageno data
==================================================
20300 0 134134134AED14134DD.....
20300 1 19AE3ADE91091999000.....
20301 0 1093101910519949592.....
MY_BLOBS.blob_data 裡記錄的 oid 直接對應到 pg_largeobject.loid 欄位。而 pg_largeobject.data 裡才是真正的 binary 資料。在預設的情況下，一筆資料最大是 8 kB，所以如果太長的資料會分段儲存，例如上面的 20300 就被分成兩頁存了 (pageno, 0 和 1)。ok, 這就是 blob 在 postgreSQL 間接儲存的做法。
blob 採用 oid 的缺點
- 無法控管權限，任何可以存取該 database 的 user 都可以存取 pg_largeobject 的內容
- 讀取 oid 的欄位要在 transaction 下操作。沒有 transaction 會丟 exception。
- 修改/刪除 blob 會留下舊的資料，系統不會自動清除。(pg_largeobject 會留下一堆用不著的 binary data)
第一個問題對我們來說還好，只要管理得宜就不是問題。第三個就很麻煩了，隨著時間一長，垃圾資料會越來越多 (這種垃圾叫 orphan large object，請用 google 查一查....)。所幸已經有人寫工具專門來清理 -- 安裝 postgres-contrib 套件後，裡面有個 vaccumlo 的程式，只要用 cron 排程定期清理即可。至於讀取 oid 要在 transaction 下... 這個最慘了！我們過去的程式讀取 blob 都沒有在 transaction 內，而且有些部份是仰賴 OpenSessionInView 這個 pattern 來減輕 DAO 的實做 (note 1)，因為這些在 oracle 上都沒有問題。現在 port 到 postgreSQL，程式得被迫重新再檢驗一次，甚至改寫.... orz。再次證明即使使用了 hibernate，migrate db 還是相當頭痛的一件事。
note 1: Spring 的 OpenSessionInViewFilter 在 page render 這個階段並沒有包 transaction，所以遇到 oid 也會出 exception。
奇特的 hibernate blob truncate bug
除了上面三個 oid 的缺點外，搭配 hibernate 使用時還有個 bug (?)。看看下面這段程式：
- //Read and update in transaction
- Transaction transaction = session.beginTransaction();
- MyEntity myEntity = (MyEntity) session.get(MyEntity.class, new Long(12));
- byte[] readBytes = IOUtils.toByteArray(myEntity.getData().getBinaryStream());
- assertEquals(1000, readBytes.length); // data is 1000 bytes.
- myEntity.setFilename("makeSomeChange.txt");
-
- // fileName got updated but blob is truncated to 0 byte at database!
- session.saveOrUpdate(myEntity);
- transaction.commit();
- session.close();
MyEntity 有兩個 property，一是 fileName(varchar)，二是 data(blob by oid)。上面的程式很簡單，(1) 開始 transaction ，(2) 讀出一筆 myEntity，(3) 裡面的 blob 讀出來變成 byte array (4) 修改檔名 (5) update myEntity 回資料庫 (6) transaction commit。可以預期的是，這一筆 myEntity 應該只會修改 fileName 欄位，其他欄位都不變... 呃，回頭查一下 db，發現 pg_largeobject 裡的資料長度竟然變成 0，資料無故被砍了！究其原因，應該是 hibernate 將 "讀到空的 binary inputStream" 也寫回 db 裡去了！真是個大 bug 地雷。貼到 hibernate 論譠但是沒人理... orz。所幸還是有暫時的解決方案，昨個參加 Javaworld TWJUG，Cyberjos兄建議替 mapping 加上 dynamic-update="true"
- <class name="MyEntity" table="my_entity"
- dynamic-update="true">
我試了結果果然可行。hibernate 產生的 update sql 就不再包含 oid 那個欄位了，因此也不會有讀過的資料被砍的問題。dynamic-update="true" 的功能是 update 時，只替 dirty 的欄位產生 set foo = ? ，所以能夠避免不必要的 update。
結語
我們的程式不採用 file system，而使用 lob 來儲存檔案有幾個原因：
- Lob 支援 transaction，file based 的沒有。
- 資料都統一放在 db 裡，簡化備份。
- 檔案都不是很大
但是 jdbc/hibernate/blob/clob 卻讓我們開發過程中吃足了苦頭：oracle9i 時期的 jdbc driver 處理 lob 時會有 leak、而目前最新的 oracle jdbc driver 處理 clob 時，遇到長度為 1000~2000 byte 的檔案會出錯 ( <1000> 2000 時就沒問題.... 別問我為什麼)... blah blah。現在我們打算擺脫 oracle 了，想說 postgreSQL 應該就沒這些怪問題了，結果還是有一堆東西要調整... lob 真是讓人又愛又恨啊。
延伸閱讀(Link):
PostgreSQL 大型物件 BYTEA vs OID
發表文章
沒有留言:
張貼留言