概述
有时候，你可能需要在 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: heap postgres=# \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 functions Schema | Name | Result data type | Argument data types | Type ------------+---------------+------------------+---------------------------+------ pg_catalog | lo_close | integer | integer | func pg_catalog | lo_creat | oid | integer | func pg_catalog | lo_create | oid | oid | func pg_catalog | lo_export | integer | oid, text | func pg_catalog | lo_from_bytea | oid | oid, bytea | func pg_catalog | lo_get | bytea | oid | func pg_catalog | lo_get | bytea | oid, bigint, integer | func pg_catalog | lo_import | oid | text | func pg_catalog | lo_import | oid | text, oid | func pg_catalog | lo_lseek | integer | integer, integer, integer | func pg_catalog | lo_lseek64 | bigint | integer, bigint, integer | func pg_catalog | lo_open | integer | oid, integer | func pg_catalog | lo_put | void | oid, bigint, bytea | func pg_catalog | lo_tell | integer | integer | func pg_catalog | lo_tell64 | bigint | integer | func pg_catalog | lo_truncate | integer | integer, integer | func pg_catalog | lo_truncate64 | integer | integer, bigint | func pg_catalog | lo_unlink | integer | oid | func pg_catalog | log | double precision | double precision | func pg_catalog | log | numeric | numeric | func pg_catalog | log | numeric | numeric, numeric | func pg_catalog | log10 | double precision | double precision | func pg_catalog | log10 | numeric | numeric | func pg_catalog | loread | bytea | integer, integer | func pg_catalog | lower | anyelement | anymultirange | func pg_catalog | lower | anyelement | anyrange | func pg_catalog | lower | text | text | func pg_catalog | lower_inc | boolean | anymultirange | func pg_catalog | lower_inc | boolean | anyrange | func pg_catalog | lower_inf | boolean | anymultirange | func pg_catalog | lower_inf | boolean | anyrange | func pg_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 $$ DECLARE fd integer; bytes integer; BEGIN fd := 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'; SET postgres=# 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 中的大对象，并提供了一些非常简单的示例。希望这能有所帮助。
