Postgresql中的large object

標籤：注入   分析   nsa   gre   標識   src   引用   hit   方式   

1、初識postgresql large object

一位同事在對使用pg_dump備份出來的檔案（使用plain格式）進行恢複時，覺得速度非常慢，讓我分析一下是什麼原因。

我拿到他的.bak檔案，檔案有1個多G。為了方便分析，我用split工具把檔案給拆成了多個小檔案。在.bak檔案中，我發現有大量這樣的SQL：

通過查詢文檔，我知道lo表示large object。還別說，這是我第一次接觸到postgresql 中的large object 。因為受oracle中LOB概念的影響，我想當然地以為large object 也是postgresql中的一種資料類型，就像ORACLE中的BLOB或CLOB。可是翻看了參考書籍與官方文檔後，發現竟然沒有對應的欄位類型。網上有人說，postgresql中的large object，其對應的欄位類型為oid。這樣的說法讓我真的好費解，只到最後，待我搞明白large object是怎麼回事後，我意識到這樣說的人大概知道一點，但應該沒有理解透。

在查詢官方文檔的過程中，接觸到兩張與large object相關的系統資料表：

簡單點說，pg_largeobject_metadata表是large object的中繼資料表，記錄每個large object的OID（物件識別碼）、屬主、存取權限。Pg_largeobject表是具體儲存large object的表，其儲存方式為：將large ojbect以page（2K）為單位分成多個單元，第個單元在Pg_largeobject中表示為1條記錄。

可是，我沒有發現有任何說明，可以解釋如何以large object作為欄位值。

 

2、如何理解postgresql large object

在oracle中，假設我們定義了表的一個欄位類型為BLOB，例如

Create table t (desc blob)

然後我們可以向該欄位中插入記錄: insert into t values (‘0Xadfe4358942c‘)

最後，在我們查詢時，直接select desc from t 就會返回：

0Xadfe4358942c

 

我錯就錯在預設了postgresql也是這麼玩兒的。然而呢？

實際上，在postgresql中（至少截止到9.6版本）沒有large object欄位。Large object在postgresql中是作為一個個的對象存在的，可以使用特定方法以oid來引用它們。我們來舉個現實中的例子來說明一下：假如把資料庫表的每條記錄比喻成1個人，LOB比喻成1隻狗，那麼oracle的處理方式為1個人有了1隻狗，資料庫中才有了這隻狗。而關於這隻狗怎麼來的，是憑空變出來的、基因複製的、還是自己用泥巴做的，oracle不管它。postgresql large object的管理方式則不同，資料庫中首先得有1隻狗或多隻狗，然後這個人就可以辦理領養手續，但領養手續只完成領養登記，表示這隻狗屬於你了，但狗你不能真正帶回家。因為狗並沒有真正帶回來，所以多個人可以登記領養同一隻狗，只需要將領養編號賦予同一隻狗的ID即可。

 

3、Postgresql large object 的使用

上面只是一個比喻，那postgresql large object如何使用的呢？使用過程中的每一步操作又對應上文的什麼內容呢？

我們就以.bak中的過程來做樣本。

1. 建立large object （資料庫中有了一隻狗，但現在這隻狗還只是個概念，還不是實體）

   執行該過程後，會在pg_largeobject_metadata中產生1條記錄，oid欄位值為1000001。而pg_largeobject表不會產生相應記錄，因為現在的large object還是空的。

2. 開啟large object （找到這隻狗，準備為它賦予生命）--執行這一步前需要開啟事務: begin;

3. 為large object 注入實際資料 （給狗賦予生命，讓它變成一個實體）--執行這一步後需要提交事務：commit;

   執行該過程後，會在pg_largeobject產生記錄，記錄的條數與large object的大小有關（每2K大小一條記錄）

4. 至此，狗已經有了，但它只是孤零零地在那時。現在需要有人對它辦理領養登記

   我們建立一張表：

   然後往該表中插入一條記錄，lo欄位的值為1000001

   好了，我們現在領養手續辦理完了。可能有人要說，不對呀，這就完了？是的，這就完了。從這裡可以看出，人知道它領養了哪只狗，但狗不知道誰領養了它。在電腦術語來說，就是引用者知道被引用者，但被引用者不知道引用者。

   現在領也領養了，但有什麼意義呢？就這麼標識一下引用，是不是就可以通過test表的lo欄位來查看large object的內容呢？我們來看一下：

   很遺憾，看到的只是引用值，看不到large object的具體的值。實際上，在postgresql中，對large object 的使用需要使用專門的方法，不能直接使用引用欄位的方式來查看、使用。

5. 辦理領養登記之後的使用

   辦理領養登記之後，可以的方法包括：

   1. lo_export(oid loid, text filename) --將大對象loid的資料匯出到一個伺服器檔案filename中，返回匯出長度（整型）。
   2. lo_unlink(oid loid)      -- 刪除一個地址為loid的大對象，返回整型 1-成功  -1 -失敗。
   3. lo_open(oid loid, integer open_mode)  -- 開啟一個地址為loid的大對象，為讀寫做準備，open_mode為開啟類型: inv_write(寫，值為131072）、inv_read（讀，值為262144）或者inv_write|inv_read (讀寫，值為393216）。返迴文件控制代碼fd（整型），若fd為負數，失敗。
   4. loread(integer fd, integer len)  -- 讀控制代碼fd當前位置開始的len大小的資料，返回資料內容（bytea類型）。
   5. lowrite(integer fd, bytea buf)   -- 在控制代碼fd當前位置開始將位元據buf寫入大對象中，返回所寫的長度（整型）。
   6. lo_lseek(integer fd, integer offset, integer whence) -- 改變控制代碼fd當前的讀寫位置。whence是定址方式，seek_set（值為0）從對象頭開始，seek_cur（值為1）從當前讀寫位置開始，seek_end（值為2）從對象尾開始，offset是位移尺寸。返回新的讀寫位置（整型），-1表示錯誤。

      註：loread、lowrite會自動改變當前讀寫位置，所以若順序讀寫，lo_lseek這個命令就沒什麼用。

   7. lo_tell(integer fd)      -- 返回控制代碼fd的當前位置（整型）
   8. lo_truncate(integer fd, integer len) -- 截取控制代碼fd所開啟的大對象長度為len大小。若len大於原來大對象的長度，會在大對象尾碼一個‘\0‘字元。成功返回0，失敗為負數。
   9. lo_close(integer fd)    -- 關閉控制代碼fd， 成功返回0，失敗為負數。

      以上函數涉及到df控制代碼的，必須在一個transaction內完成，也就是說控制代碼fd只在一個事務內有效，事務結束它自動關閉。

6. 使用案例

例如，我們可以lo_export方法將test表lo欄位所對應的large object匯出到本地。

查看test.f檔案

 

通過查詢1f 8b 08檔案頭所對應的檔案格式，可知test.f檔案為gz壓縮檔。通過gunzip解壓test.f檔案

 

好，現在我們看清它的明文是什麼了。這實際是一種向量瓦片的明碼格式。

Postgresql中的large object