概述

有時候，你可能需要在 PostgreSQL 中管理大對象，例如 CLOB、BLOB 和 BFILE。PostgreSQL 中有兩種處理大對象的方法：一種是使用現有的數據類型，例如用于二進制大對象的 bytea 和用于基于字符的大對象的 text；另一種是使用 pg_largeobject。本文將解釋如何使用 pg_largeobject。

pg_largeobject

pg_largeobject 是 PostgreSQL 提供的大對象解決方案之一，它允許以流式方式訪問存儲在特殊大對象結構中的用戶數據。當處理的數據值過大而無法作為一個整體方便地操作時，流式訪問非常有用。

pg_largeobject 是一個用于存儲實際大對象的系統表。每個大對象在系統表 pg_largeobject_metadata 中都有一個條目。使用 pg_largeobject 管理的大對象可以通過讀/寫 API 進行創建、修改和刪除。pg_largeobject 允許存儲高達 4TB 的大對象。

另一種大對象解決方案是使用現有的數據類型 bytea 和 text，它們基于 TOAST 表構建，限制了大對象的大小為 1GB。

以下是為 pg_largeobject 設計的兩個系統表的架構。

postgres=# \d+ pg_largeobject;Table "pg_catalog.pg_largeobject"Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------loid   | oid     |           | not null |         | plain    |             |              |pageno | integer |           | not null |         | plain    |             |              |data   | bytea   |           | not null |         | extended |             |              |
Indexes:"pg_largeobject_loid_pn_index" PRIMARY KEY, btree (loid, pageno)
Access method: heappostgres=# \d+ pg_largeobject_metadata;Table "pg_catalog.pg_largeobject_metadata"Column  |   Type    | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
----------+-----------+-----------+----------+---------+----------+-------------+--------------+-------------oid      | oid       |           | not null |         | plain    |             |              |lomowner | oid       |           | not null |         | plain    |             |              |lomacl   | aclitem[] |           |          |         | extended |             |              |
Indexes:"pg_largeobject_metadata_oid_index" PRIMARY KEY, btree (oid)
Access method: heap

pg_largeobject 接口

PostgreSQL 通過 libpq 庫提供客戶端接口來訪問大對象。該接口類似于 Unix 文件系統接口，例如 create、open、read、write、lseek 等。

注意，使用該接口操作大對象時，必須在 SQL 事務塊內進行，因為大對象文件描述符僅在特定事務中有效。以下是與 pg_largeobject 相關的所有接口。

postgres=# \dfS lo*List of functionsSchema   |     Name      | Result data type |    Argument data types    | Type
------------+---------------+------------------+---------------------------+------pg_catalog | lo_close      | integer          | integer                   | funcpg_catalog | lo_creat      | oid              | integer                   | funcpg_catalog | lo_create     | oid              | oid                       | funcpg_catalog | lo_export     | integer          | oid, text                 | funcpg_catalog | lo_from_bytea | oid              | oid, bytea                | funcpg_catalog | lo_get        | bytea            | oid                       | funcpg_catalog | lo_get        | bytea            | oid, bigint, integer      | funcpg_catalog | lo_import     | oid              | text                      | funcpg_catalog | lo_import     | oid              | text, oid                 | funcpg_catalog | lo_lseek      | integer          | integer, integer, integer | funcpg_catalog | lo_lseek64    | bigint           | integer, bigint, integer  | funcpg_catalog | lo_open       | integer          | oid, integer              | funcpg_catalog | lo_put        | void             | oid, bigint, bytea        | funcpg_catalog | lo_tell       | integer          | integer                   | funcpg_catalog | lo_tell64     | bigint           | integer                   | funcpg_catalog | lo_truncate   | integer          | integer, integer          | funcpg_catalog | lo_truncate64 | integer          | integer, bigint           | funcpg_catalog | lo_unlink     | integer          | oid                       | funcpg_catalog | log           | double precision | double precision          | funcpg_catalog | log           | numeric          | numeric                   | funcpg_catalog | log           | numeric          | numeric, numeric          | funcpg_catalog | log10         | double precision | double precision          | funcpg_catalog | log10         | numeric          | numeric                   | funcpg_catalog | loread        | bytea            | integer, integer          | funcpg_catalog | lower         | anyelement       | anymultirange             | funcpg_catalog | lower         | anyelement       | anyrange                  | funcpg_catalog | lower         | text             | text                      | funcpg_catalog | lower_inc     | boolean          | anymultirange             | funcpg_catalog | lower_inc     | boolean          | anyrange                  | funcpg_catalog | lower_inf     | boolean          | anymultirange             | funcpg_catalog | lower_inf     | boolean          | anyrange                  | funcpg_catalog | lowrite       | integer          | integer, bytea            | func

4. 示例

為了更好地理解如何使用 pg_largeobject，以下是一些示例。

創建一個大對象

postgres=# select lo_create(0);lo_create
-----------16384
(1 row)postgres=# select * from pg_largeobject_metadata;oid  | lomowner | lomacl
-------+----------+--------16384 |       10 |
(1 row)postgres=# select * from pg_largeobject;loid | pageno | data
------+--------+------
(0 rows)

導入一個大對象

創建一個簡單的文本文件。

$ echo "this is a test on pg_largeobject." > /tmp/lo_test.txt

將文本文件導入 pg_largeobject。

postgres=*# select lo_import('/tmp/lo_test.txt');lo_import
-----------16385
(1 row)postgres=# select * from pg_largeobject_metadata;oid  | lomowner | lomacl
-------+----------+--------16384 |       10 |16385 |       10 |
(2 rows)

顯示導入 pg_largeobject 后的內容。

postgres=# set bytea_output = 'escape';
SET
postgres=# select * from pg_largeobject;loid  | pageno |                 data
-------+--------+---------------------------------------16385 |      0 | this is a test on pg_largeobject.\012
(1 row)

操作一個大對象

使用 pg_largeobject 的一大優勢是它允許我們修改大對象。以下是一個向另一個大對象追加信息的示例。

begin;
select lo_open(16385, x'60000'::int);
select lo_lseek(0, 32, 0);
select lowrite(0, ', + large object.\012');
commit;postgres=# select * from pg_largeobject;loid  | pageno |                         data
-------+--------+-------------------------------------------------------16385 |      0 | this is a test on pg_largeobject, + large object.\012
(1 row)

這里，x’60000’用于將大對象訪問模式設置為INV_WRITE|INV_READ。這些訪問模式在頭文件 libpq/libpq-fs.h 中定義如下。

#define INV_WRITE               0x00020000
#define INV_READ                0x00040000

將大對象導出到外部文件

除了在 PostgreSQL 內部訪問大對象外，你還可以將大對象導出到外部文件。

postgres=# select lo_export(16385, '/tmp/lo_test_new.txt');

然后，你可以像普通文件一樣檢查內容。

$ cat /tmp/lo_test_new.txt
this is a test on pg_largeobject, + large object.

將操作封裝到函數中

你可以使用 libpq 庫構建函數來自定義大對象的訪問。以下是一個使用 PL/SQL 的簡單示例。

DROP FUNCTION IF EXISTS my_lo_append;
CREATE OR REPLACE FUNCTION my_lo_append(oid, bytea)
RETURNS oid AS $$
DECLAREfd integer;bytes integer;
BEGINfd := lo_open($1, x'60000'::int);bytes := lo_lseek(0, 0, 2);bytes := lowrite(fd, $2);PERFORM lo_close(fd);RETURN $1;
END;
$$ LANGUAGE plpgsql STRICT;

然后調用自定義函數來追加一些信息。

postgres=# select my_lo_append(16385::oid, 'abc123'::bytea);my_lo_append
--------------16385
(1 row)postgres=# set bytea_output = 'escape';
SETpostgres=# select * from pg_largeobject;loid  | pageno |                    data
-------+--------+---------------------------------------------16385 |      0 | this is a test on pg_largeobject.\012abc123
(1 row)postgres=# select lo_export(16385, '/tmp/lo_test_new.txt');lo_export
-----------1
(1 row)

你還可以檢查導出后作為文件操作的大對象。

$ cat /tmp/lo_test_new.txt
this is a test on pg_largeobject.
abc123

總結

在本文中，我解釋了如何使用 pg_largeobject 來處理 PostgreSQL 中的大對象，并提供了一些非常簡單的示例。希望這能有所幫助。