lo_create / lo_import / lo_export 与大对象操作

上一节讲清了大对象的存储结构与权限模型，这一节进入动手操作层面：怎么创建、怎么读写、怎么导入导出、怎么在客户端语言里调用。

大对象的操作接口分布在三个层次上，理解这个分层是掌握本节的钥匙：

层次	运行位置	调用方式	典型场景
服务端函数（SQL 层）	数据库进程内	直接在 SQL 里调用	psql 里调试、触发器、服务端逻辑
客户端接口（libpq / JDBC / PHP）	应用进程内	语言绑定调用	应用读写大文件
psql 元命令	psql 客户端	\lo_* 反斜杠命令	手工运维、快速导入导出

三个层次的函数名高度相似，却各有各的语义细节。本节会逐层展开，重点讲清"服务端函数名不带下划线、客户端函数名带下划线"这条最容易出错的规则，以及 lo_import / lo_export 之所以默认只有超级用户可用的根本原因。

前置知识

建议先阅读：大对象（Large Objects）概述

基础概念

服务端函数 vs 客户端函数

这是操作大对象时最容易踩的一个坑：同一件事，两个层次有不同的函数名。

服务端（SQL）              客户端（libpq C API）
─────────────────────────────────────────────
lo_open(loid, mode)   ←→  lo_open(conn, loid, mode)
lo_close(fd)          ←→  lo_close(conn, fd)
loread(fd, len)       ←→  lo_read(conn, fd, buf, len)
lowrite(fd, data)     ←→  lo_write(conn, fd, buf, len)
lo_lseek(fd, ...)     ←→  lo_lseek(conn, fd, ...)
lo_lseek64(...)       ←→  lo_lseek64(...)
lo_tell(fd)           ←→  lo_tell(conn, fd)
lo_tell64(...)        ←→  lo_tell64(...)
lo_truncate(fd, len)  ←→  lo_truncate(conn, fd, len)
lo_truncate64(...)    ←→  lo_truncate64(...)
lo_create(loid)       ←→  lo_create(conn, loid)
lo_unlink(loid)       ←→  lo_unlink(conn, loid)

规律很清楚：读写的动词，服务端不带下划线（loread / lowrite），客户端带下划线（lo_read / lo_write）；其余函数名两侧完全相同，但客户端版本多一个 conn 参数。

看到一段大对象代码时，先确认它在哪一层，能省掉大量困惑。

描述符：大对象的"文件句柄"

大对象的读写不是"直接给 OID 就能读"，而是要先打开，拿到一个整数描述符（descriptor / file descriptor，fd），再对这个描述符读写。

这个设计是刻意的——它把大对象 API 设计得像操作系统的文件 API：

大对象操作	对应的文件操作
lo_open	open()
lo_close	close()
loread	read()
lowrite	write()
lo_lseek	lseek()
lo_tell	tell()
lo_truncate	ftruncate()

初学者常问"OID 和描述符有什么区别"，答案就在这张表里：OID 是大对象的身份证（持久），描述符是本次打开的临时句柄（只在本事务内有效）。

BEGIN;
-- OID 是 16385，返回的描述符（fd）是 0
SELECT lo_open(16385, 262144);   -- INV_READ

返回的 0 就是描述符；同时打开多个大对象会依次得到 0、1、2……描述符不是 OID，二者不可互换。

服务端函数总览

服务端（SQL 层）可用的大对象函数按用途分组如下：

分类	函数	签名	说明
创建/删除	lo_create	lo_create(loid oid) → oid	创建；传 0 由系统分配 OID
创建/删除	lo_creat	lo_creat([mode integer]) → oid	旧别名，mode 自 8.1 起被忽略
创建/删除	lo_unlink	lo_unlink(loid oid) → integer	删除大对象
打开/关闭	lo_open	lo_open(loid oid, mode integer) → integer	打开，返回描述符
打开/关闭	lo_close	lo_close(fd integer) → integer	关闭描述符
读写	loread	loread(fd integer, length integer) → bytea	从当前位置读若干字节
读写	lowrite	lowrite(fd integer, data bytea) → integer	从当前位置写，返回写入字节数
定位	lo_lseek	lo_lseek(fd integer, offset integer, whence integer) → integer	32 位定位
定位	lo_lseek64	lo_lseek64(fd integer, offset bigint, whence integer) → bigint	64 位定位
定位	lo_tell / lo_tell64	lo_tell(fd integer) → integer	32 位当前位置
定位	lo_tell64	lo_tell64(fd integer) → bigint	64 位当前位置
截断	lo_truncate	lo_truncate(fd integer, len integer) → integer	32 位截断
截断	lo_truncate64	lo_truncate64(fd integer, len bigint) → integer	64 位截断
整值	lo_from_bytea	lo_from_bytea(loid oid, data bytea) → oid	用一段字节创建/覆盖大对象
整值	lo_put	lo_put(loid oid, offset bigint, data bytea) → void	在指定偏移写入（不需打开）
整值	lo_get	lo_get(loid oid [, offset bigint, length integer]) → bytea	读取整值或片段
文件	lo_import	lo_import(filename text [, loid oid]) → oid	从服务器文件导入
文件	lo_export	lo_export(loid oid, filename text) → integer	导出到服务器文件

何时用哪一组

函数分组的背后是使用场景的差异：

• lo_create + lo_open + lowrite：流式写入，适合数据来自网络流或需要分块生成。
• lo_from_bytea + lo_put：一次性把一段已知的 bytea 落成大对象，或做局部修补，不需要描述符，最简洁。
• lo_get：小对象读取、读文件头、做校验。
• lo_import / lo_export：文件已经在服务器主机上，两者之间搬运。

-- 路径 1：lo_from_bytea（最简洁）
SELECT lo_from_bytea(0, '\x00010203');

-- 路径 2：lo_create + lowrite（流式，需在事务内）
BEGIN;
SELECT lo_create(0);
SELECT lo_open(17000, 393216);       -- INV_READ | INV_WRITE
SELECT lowrite(0, '\x00010203');
SELECT lo_close(0);
COMMIT;

-- 路径 3：从服务器文件导入
SELECT lo_import('/tmp/data.bin');

语法与用法

创建：lo_create 与 lo_creat

-- 系统自动分配 OID，返回新 OID（如 16389）
SELECT lo_create(0);

-- 指定 OID（不常见，可能导致冲突）
SELECT lo_create(20000);

旧式别名：

-- 等价于 lo_create，mode 参数被忽略（返回如 16390）
SELECT lo_creat(-1);

务必注意：lo_creat 是"少一个 e"的旧别名，很多人以为 lo_creat(-1) 里的 -1 有什么特殊含义。事实是：mode 参数自 PostgreSQL 8.1 起就被完全忽略，写 -1、0、INV_READ 结果都一样。新代码一律用 lo_create。

删除：lo_unlink

-- 返回 1 表示删除成功，0 表示该 OID 不存在
SELECT lo_unlink(16389);

删除需要属主或超级用户身份，仅有 UPDATE 权限是不够的：

-- 非属主删除会失败：ERROR:  must be owner of large object 16391
SELECT lo_unlink(16391);

打开、关闭与读写：lo_open / loread / lowrite

BEGIN;

-- 只读打开，返回描述符（此处为 0）
SELECT lo_open(16385, 262144);   -- INV_READ

-- 读写打开
SELECT lo_open(16385, 393216);   -- INV_READ | INV_WRITE

-- 从当前位置写入，返回写入字节数
SELECT lowrite(0, '\x48656c6c6f2c20506f73746772657321');   -- "Hello, postgres!"

-- 回到开头
SELECT lo_lseek(0, 0, 0);

-- 读 7 个字节
SELECT loread(0, 7);

-- 关闭，返回 0 表示成功
SELECT lo_close(0);

COMMIT;

                loread
--------------------------------------
 \x48656c6c6f2c20
(1 row)

\x48656c6c6f2c20 解码成 ASCII 是 Hello, 。注意 loread 会推进内部指针，连续调用会依次读到后续内容，不会重复读同一段；lo_close 返回 0 表示成功。事务结束时所有未关闭的描述符会被自动关闭，但显式关闭是好习惯——特别是在长事务里，未关闭的描述符会一直占用资源。

定位：lo_lseek 与 whence

lo_lseek 的第三个参数 whence 与 C 标准库 lseek 一致：

whence	常量	含义
0	SEEK_SET	从对象开头算起（绝对偏移）
1	SEEK_CUR	从当前位置算起
2	SEEK_END	从对象末尾算起（通常配合负偏移）

BEGIN;
SELECT lo_open(16385, 262144);

SELECT lo_lseek(0, 100, 0);    -- 绝对定位到 100
SELECT lo_lseek(0, 10, 1);     -- 相对当前位置前进 10（即 110）
SELECT lo_lseek(0, -5, 2);     -- 相对末尾回退 5

SELECT lo_tell(0);             -- 查看当前位置
SELECT lo_close(0);
COMMIT;

lo_tell 返回当前位置的偏移量（上例为 123）。lo_lseek 返回新的偏移量。当大对象超过 2 GB 时，32 位的 lo_lseek / lo_tell 会溢出，此时必须使用 lo_lseek64 / lo_tell64（PostgreSQL 9.3 引入）。

截断：lo_truncate

BEGIN;
SELECT lo_open(16385, 393216);   -- INV_READ | INV_WRITE
SELECT lo_truncate(0, 10);       -- 截断到 10 字节，成功返回 0
SELECT lo_close(0);
COMMIT;

截断超过 2 GB 的对象需要 lo_truncate64。截断需要 UPDATE 权限。注意 lo_truncate 只能把对象变短（len 必须小于当前长度），不能用来扩展——这与某些文件系统的 ftruncate 行为不同。

整值写入：lo_put

lo_put 不需要打开描述符，直接按 OID 和偏移写入：

SELECT lo_create(0) AS loid \gset         -- 创建空对象
SELECT lo_put(:loid, 0, '\x41424344');    -- 在偏移 0 处写入
SELECT lo_get(:loid);                     -- 验证，返回 \x41424344

有意思的是，lo_put 可以直接在稀疏位置写入，中间空洞读出为零：

SELECT lo_put(:loid, 1048576, '\x01020304');       -- 在 1 MB 偏移处写入
SELECT octet_length(lo_get(:loid)) AS logical_len; -- 返回 1048580，中间是零字节

逻辑长度达到了 1 MB 多，但实际物理占用只有 2 页。这是大对象稀疏存储的直接体现。

整值读写与 bytea 互转：lo_get / lo_from_bytea

-- 读取整个对象
SELECT lo_get(16389);

-- 读取偏移 0 开始 4 个字节、偏移 1024 开始 16 个字节
SELECT lo_get(16389, 0, 4);
SELECT lo_get(16389, 1024, 16);

lo_get 的三个参数都可选，offset 省略时从 0 开始，length 省略时读到末尾。当对象很大时慎用不带 length 的 lo_get——它会把整个对象读进内存。

lo_from_bytea 是反方向：

SELECT lo_from_bytea(0, '\xdeadbeef') AS loid;   -- 从 bytea 创建，返回新 OID
SELECT lo_from_bytea(16389, '\xcafebabe');       -- 指定 OID 覆盖内容

这是一个整值操作：它会把大对象的内容整体替换为传入的 bytea，长度也随之改变。传入 loid 为 0 时新建对象。

文件导入导出：lo_import / lo_export

SELECT lo_import('/etc/motd');            -- 导入：读服务器文件，返回 OID
SELECT lo_import('/etc/motd', 68583);     -- 导入并指定 OID
SELECT lo_export(68583, '/tmp/motd');     -- 导出：写到服务器文件

lo_import 返回新大对象的 OID；lo_export 返回写入的字节数。这两个函数操作的是数据库服务器主机的文件系统，不是客户端主机的。默认只有超级用户能调用。

详细说明

权限检查在 lo_open 时发生

PostgreSQL 11 有一个行为变更：大对象的权限检查从"第一次读写调用"提前到了"打开时"。这意味着即使你只是打开、什么都不读，权限不足也会立刻失败。

-- 无 SELECT 权限的用户
SELECT lo_open(16391, 262144);   -- INV_READ

ERROR:  permission denied for large object 16391

这个变更对应用的错误处理有影响：以前可以把权限错误捕获在读写处，现在必须在 lo_open 处就捕获。

INV_READ 与 INV_WRITE 的数值

服务端 lo_open 的 mode 参数是一个位掩码：

常量	值（十进制）	值（十六进制）
INV_WRITE	131072	0x00020000
INV_READ	262144	0x00040000

组合方式：

需求	表达式	十进制
只读	INV_READ	262144
只写	INV_WRITE	131072
读写	INV_READ | INV_WRITE	393216

这两个常量定义在客户端头文件 libpq/libpq-fs.h 中。在纯 SQL 里没有符号名可用（除非自己 CREATE DOMAIN 或写注释），所以实践中常见到直接写数字：

-- 可读性差，建议加注释
SELECT lo_open(16385, 393216);   -- INV_READ | INV_WRITE

注意 INV_WRITE 的数值比 INV_READ 小，不要想当然地把"写"当成更大的那一档。另外，以 INV_WRITE 单独打开（不带 INV_READ）时，读取的结果是未定义的，实践中不要这么做。

lo_import / lo_export 的安全含义

这两个函数为什么默认只给超级用户？因为它们直接读写服务器文件系统：

-- 这行命令能读到数据库服务器的 /etc/shadow（如果权限允许）
SELECT lo_import('/etc/shadow');

如果允许普通用户调用 lo_import，等于让该用户通过数据库间接获得服务器文件的读取能力；lo_export 更危险，可以写文件到任意可写目录。

预定义角色正是为此设计的：

角色	授予后的能力
pg_read_server_files	读服务器文件（lo_import、COPY FROM file）
pg_write_server_files	写服务器文件（lo_export、COPY TO file）
pg_execute_server_program	执行程序（COPY ... PROGRAM）

GRANT pg_read_server_files TO app_importer;

授予前务必确认该角色确实可信。这些角色绕过操作系统层的文件权限，是数据库内的"文件系统后门"。

更安全的替代方案是让客户端接口来搬运文件：客户端读本地文件，通过 lo_write（libpq）送到服务器。这样服务器不需要开放文件系统访问，只是网络上多传一份数据。绝大多数应用应该走这条路。

psql 元命令

psql 提供了几个 \lo_* 元命令，适合手工运维：

元命令	用途
\lo_import FILE [COMMENT]	从客户端文件导入并创建大对象
\lo_export LOBOID FILE	把大对象导出到客户端文件
\lo_list / \lo_list+	列出大对象及其注释（+ 显示更多信息）
\lo_unlink LOBOID	删除指定大对象

关键区别：\lo_import / \lo_export 读写的是 psql 所在机器（客户端） 的文件，而 SQL 的 lo_import() / lo_export() 读写的是服务器的文件。这是两者最容易混淆的地方。

# 在 psql 里导入客户端本地文件
\lo_import /home/alice/photo.png
# → lo_import 16394

# 导出到客户端文件
\lo_export 16394 /home/alice/copy.png
# → lo_export

# 列出所有大对象（+ 显示更多信息）
\lo_list
# → 16394 | alice | 

# 删除
\lo_unlink 16394
# → lo_unlink 16394

大对象描述符与事务

描述符的生存期严格绑定在事务上。事务一旦结束，所有描述符立即失效：

BEGIN;
SELECT lo_open(16385, 262144);
COMMIT;

-- 事务已结束，描述符 0 不再有效
SELECT loread(0, 10);

ERROR:  invalid large-object descriptor: 0

这个特性在连接池环境下尤其需要注意。如果框架自动提交了事务，而应用代码以为还在同一个事务里继续用描述符，就会莫名其妙地失败。写应用代码时，务必把"打开—读写—关闭"整段包在同一个显式事务里。

游标与大对象

大对象本身提供的是"按偏移随机读取"，不需要 SQL 游标。但两者会一起出现的情况是：用游标遍历业务表，逐行读取关联的大对象。

BEGIN;

DECLARE cur CURSOR FOR
    SELECT id, name, content FROM documents ORDER BY id;

-- 逐行处理
FETCH 1 FROM cur;
-- 应用层拿到 content 这个 OID 后，再 lo_open / loread

CLOSE cur;
COMMIT;

这里有一个性能陷阱：一个大事务内打开大量描述符或读取大量大对象，会显著拉长事务、增加锁与内存压力。更好的做法是分批处理，或者在应用层用独立的短事务逐个读取。

另外，loread 单次读取的字节数建议控制在合理范围（例如 64 kB ~ 1 MB），不要试图一次读几百 MB——那会绕过大对象"分片读取"的初衷。

libpq 客户端接口

libpq 的接口在服务端函数基础上多了 PGconn *conn 参数：

Oid lo_create(PGconn *conn, Oid lobjId);
Oid lo_creat(PGconn *conn, int mode);            /* 旧接口，mode 被忽略 */
Oid lo_import(PGconn *conn, const char *filename);
Oid lo_import_with_oid(PGconn *conn, const char *filename, Oid lobjId);
int lo_export(PGconn *conn, Oid lobjId, const char *filename);

int lo_open(PGconn *conn, Oid lobjId, int mode);
int lo_close(PGconn *conn, int fd);
int lo_read(PGconn *conn, int fd, char *buf, size_t len);
int lo_write(PGconn *conn, int fd, const char *buf, size_t len);
int lo_lseek(PGconn *conn, int fd, int offset, int whence);
pg_int64 lo_lseek64(PGconn *conn, int fd, pg_int64 offset, int whence);
int lo_tell(PGconn *conn, int fd);
pg_int64 lo_tell64(PGconn *conn, int fd);
int lo_truncate(PGconn *conn, int fd, size_t len);
int lo_truncate64(PGconn *conn, int fd, pg_int64 len);
int lo_unlink(PGconn *conn, Oid lobjId);

各函数的引入版本：lo_truncate 为 8.3，lo_import_with_oid 为 8.4，lo_lseek64 / lo_tell64 / lo_truncate64 为 9.3。

一个完整的 libpq 写入流程：

PGconn *conn = PQconnectdb("dbname=mydb");
PGresult *res;

res = PQexec(conn, "BEGIN");      /* 大对象操作必须在事务内 */
PQclear(res);

Oid loid = lo_create(conn, 0);    /* 创建 */
int fd = lo_open(conn, loid, INV_READ | INV_WRITE);   /* 打开，取描述符 */
lo_write(conn, fd, buf, buflen);  /* 写入 */
lo_close(conn, fd);               /* 关闭 */

res = PQexec(conn, "COMMIT");
PQclear(res);

注意 libpq 的大对象函数不能在 pipeline 模式下使用——这是官方明确的限制。使用 pipeline 时，大对象调用会失败。

JDBC 客户端接口

Java 侧通过 PostgreSQL JDBC 驱动的 LargeObjectManager 操作大对象：

import org.postgresql.PGConnection;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

// 必须关闭自动提交——大对象操作需要在事务内
conn.setAutoCommit(false);

PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lom = pgConn.getLargeObjectAPI();

// 创建
long oid = lom.createLO(LargeObjectManager.READ | LargeObjectManager.WRITE);

// 打开
LargeObject lo = lom.open(oid, LargeObjectManager.WRITE);

// 写入
byte[] buf = readFromSomewhere();
lo.write(buf);

// 关闭
lo.close();

conn.commit();

JDBC 里的两个模式常量与 libpq 的 INV_* 数值一致：

JDBC 常量	值	对应
LargeObjectManager.WRITE	0x00020000	INV_WRITE
LargeObjectManager.READ	0x00040000	INV_READ

LargeObject 还提供了 size()、seek()、truncate()、read()、getInputStream() / getOutputStream() 等方法，其中流式接口在传输大文件时比较实用。

PHP（pgsql 扩展）客户端接口

PHP 的 pgsql 扩展提供了完整的大对象函数集。它们在名字上与 libpq 保持一致但使用下划线，且所有操作必须在事务内：

PHP 函数	作用
pg_lo_create()	创建大对象，返回 OID
pg_lo_open()	打开，返回 PgSql\Lob 对象
pg_lo_write()	写入数据，返回写入字节数
pg_lo_read()	读取指定字节数
pg_lo_read_all()	读取全部剩余内容
pg_lo_seek()	定位
pg_lo_tell()	获取当前位置
pg_lo_truncate()	截断
pg_lo_close()	关闭
pg_lo_import()	从服务器文件导入
pg_lo_export()	导出到服务器文件
pg_lo_unlink()	删除

pg_lo_open() 的 mode 参数用字符串表示，而不是位掩码：

字符串	含义
"r"	只读
"w"	只写
"rw"	读写

完整的 PHP 写入示例（提交后再做业务表关联）：

<?php
$conn = pg_connect("host=localhost dbname=mydb user=app");

pg_query($conn, "BEGIN");              // 大对象操作在事务外会失败

$oid = pg_lo_create($conn);            // 创建
$lob = pg_lo_open($conn, $oid, "rw");  // 打开（读写）

// 分块写入，避免 PHP 进程内存峰值过高
$fh = fopen('/tmp/big.iso', 'rb');
while (!feof($fh)) {
    $chunk = fread($fh, 1024 * 1024);   // 每次 1 MB
    if ($chunk === false || $chunk === '') {
        break;
    }
    pg_lo_write($lob, $chunk);
}
fclose($fh);
pg_lo_close($lob);

pg_query($conn, "COMMIT");
pg_query_params($conn, "INSERT INTO photos (title, raster) VALUES ($1, $2)",
                ['big.iso', $oid]);
?>

读取同理，用 pg_lo_read() 分块循环写入本地文件即可。关键点：单次写入不宜过大，几十 MB 以上的文件务必分块。

PHP 官方文档明确建议：能用 bytea 就不要用大对象。 大对象 API 更复杂、需要事务、容易产生孤儿对象，只有在对象确实超过 1 GB 或需要随机访问时才值得使用。

清理与 vacuumlo

lo_unlink 只能删一个，手工维护容易遗漏。批量清理孤儿对象用 vacuumlo：

# 先 dry-run
vacuumlo -n -v "postgresql://app@localhost/mydb"

Connected to database "mydb"
Would remove large object 16385
Would remove large object 16388
...

# 确认后正式执行
vacuumlo -v "postgresql://app@localhost/mydb"

vacuumlo 的思路是"扫描所有类型为 oid / lo 的列，收集引用，未被引用的即视为孤儿"。因此两个前提必须成立：引用列类型必须是 oid 或 lo；且引用必须是"当前存在"的（例如引用了不存在的 OID 不会被保护）。用 domain 包装的列不会被识别。

实战示例

示例一：从服务器文件导入并挂到业务行

CREATE TABLE documents (
    id      serial PRIMARY KEY,
    name    text NOT NULL,
    content oid
);

-- 导入
SELECT lo_import('/tmp/quarterly.pdf') AS loid \gset

-- 挂载
INSERT INTO documents (name, content) VALUES ('quarterly.pdf', :loid);

-- 校验
SELECT d.id, d.name, octet_length(lo_get(d.content)) AS size
FROM documents d;

 id |     name      |  size
----+---------------+--------
  1 | quarterly.pdf | 245760
(1 row)

示例二：分块流式写入

假设要生成一个 10 MB 的测试大对象：

BEGIN;

SELECT lo_create(0) AS loid \gset
SELECT lo_open(:loid, 393216) AS fd \gset

-- 用 generate_series 循环写入
SELECT lowrite(:fd, decode(repeat('41', 1024), 'hex'))
FROM generate_series(1, 10240);   -- 10240 × 1024 字节 = 10 MB

SELECT lo_close(:fd);

SELECT lo_get(:loid, 0, 8) AS head;

COMMIT;

    head
------------
 \x4141414141414141
(1 row)

41 是 ASCII 的 A，说明写入成功。

示例三：只读文件头做格式识别

不需要读整个文件，只取前几个字节即可判断类型：

-- 判断是否为 PDF（%PDF → 25504446）
SELECT CASE
           WHEN lo_get(content, 0, 4) = '\x25504446' THEN 'PDF'
           WHEN lo_get(content, 0, 3) = '\xffd8ff'   THEN 'JPEG'
           WHEN lo_get(content, 0, 8) = '\x89504e470d0a1a0a' THEN 'PNG'
           ELSE 'unknown'
       END AS file_type,
       name
FROM documents;

 file_type |     name
-----------+---------------
 PDF       | quarterly.pdf
(1 row)

这是大对象相对 bytea 的一个实打实的优势：不用把 200 MB 的 PDF 拉进内存就能判断类型。

示例四：局部修补

给一个大对象在指定偏移打补丁，不需要重写整个对象：

-- 在偏移 1024 处覆盖 4 个字节
SELECT lo_put(16391, 1024, '\xdeadbeef');

-- 验证
SELECT lo_get(16391, 1024, 4);

   lo_get
------------
 \xdeadbeef
(1 row)

如果换成 bytea 列，同样的操作需要"读出整值 → 在内存里改 → 写回整值"，代价完全不同。

示例五：查看大对象占用与引用情况

-- 逻辑大小与实际物理占用
SELECT lo.loid,
       (max(lo.pageno) + 1) * 2048          AS logical_bytes,
       sum(length(lo.data))                 AS physical_bytes,
       pg_size_pretty(sum(length(lo.data))) AS physical_size
FROM pg_largeobject lo
GROUP BY lo.loid
ORDER BY physical_bytes DESC;

  loid | logical_bytes | physical_bytes | physical_size
-------+---------------+----------------+---------------
 16391 |      10485760 |       10485760 | 10 MB
 16392 |       1048580 |           4096 | 4096 bytes
(2 rows)

第二行的大对象逻辑长度约 1 MB，物理占用只有 4 kB（2 页），这就是稀疏存储的效果。据此也能找出未被引用的对象：

SELECT m.oid AS orphan_loid
FROM pg_largeobject_metadata m
LEFT JOIN documents d ON d.content = m.oid
WHERE d.content IS NULL
LIMIT 20;

示例六：lo 扩展 + 触发器 + 手工清理的组合

CREATE EXTENSION IF NOT EXISTS lo;

CREATE TABLE attachments (
    id      serial PRIMARY KEY,
    name    text,
    payload lo
);

CREATE TRIGGER t_payload
    BEFORE UPDATE OR DELETE ON attachments
    FOR EACH ROW EXECUTE FUNCTION lo_manage(payload);

-- 删除行时触发器自动 lo_unlink
INSERT INTO attachments (name, payload)
VALUES ('a.bin', lo_import('/tmp/a.bin'));
DELETE FROM attachments WHERE name = 'a.bin';

-- 但 TRUNCATE 不会触发行级触发器，仍会产生孤儿对象
TRUNCATE attachments;

所以即使挂了触发器，仍需定期 vacuumlo -n -v "postgresql://app@localhost/mydb" 兜底巡检。

注意事项

服务端与客户端函数名差一个下划线

loread / lowrite 是 SQL 层函数，lo_read / lo_write 是 libpq 函数。写文档、查报错、搜索资料时先确认层次，否则会白费很多时间。

lo_creat 是旧别名，用 lo_create

lo_creat 的 mode 参数自 8.1 起被忽略。不要因为它带 mode 就以为能控制访问模式。

大对象操作必须在事务内

事务外调用会得到 invalid large-object descriptor。连接池自动提交的场景下尤其要小心。

描述符不跨事务

事务一提交，描述符立即失效。不要把描述符存到变量里跨事务复用。

lo_import / lo_export 操作服务器文件系统

它们不是客户端文件。要给客户端应用程序员读本地文件，用 pg_lo_write（PHP）/ lo_write（libpq）/ JDBC 的 OutputStream，或者用 psql 的 \lo_import。

INV_WRITE 的数值小于 INV_READ

INV_WRITE = 131072，INV_READ = 262144。写死数字时务必核对。

lo_get 不带 length 会读整个对象

对超过几百 MB 的对象，必须指定 length 分块读，否则容易触发内存问题。

lo_truncate 只能缩短

它不能用来扩展对象。要扩展，直接用 lo_put / lowrite 写入更远的位置。

pipeline 模式下不能用大对象函数

libpq 的 pipeline 模式与大对象 API 不兼容，这是官方限制。

vacuumlo 只认 oid / lo 类型的列

把 OID 存在 text、bigint 或 domain 列里，vacuumlo 会误删。必须用 -n 先 dry-run。

不要轻易授予 pg_read_server_files

这个角色能让持有者读取服务器上任意可读文件，攻击面很大。

孤儿对象不会自己消失

删除业务行、DROP TABLE、TRUNCATE 都不会自动清理大对象（lo_manage 触发器也覆盖不到后两者）。定期 vacuumlo 是必须的。

最佳实践

1. 应用层读写大文件时，优先走客户端接口（libpq / JDBC / PHP pgsql），把文件内容通过协议送到服务器，而不是把文件先放到服务器磁盘再用 lo_import。

2. 统一使用 lo_create，不要用 lo_creat，避免后来者误以为 mode 参数有效。

3. 把"打开—读/写—关闭"整段包在显式事务里，并确保事务边界与应用逻辑一致：

   BEGIN;
   -- lo_open / loread / lowrite / lo_close
   COMMIT;

4. 读写都分块，单次 64 kB ~ 1 MB，避免把整个对象读入内存：

   SELECT loread(:fd, 65536);   -- 每次 64 kB

5. 超过 2 GB 的对象一律用 64 位函数（lo_lseek64 / lo_tell64 / lo_truncate64），不要等溢出后才改。

6. 权限按最小化原则授予，读给 SELECT、写给 UPDATE，删除类操作留给属主。

7. 严格控制 pg_read_server_files / pg_write_server_files 的授予范围，并把授权记录纳入审计。

8. 在 lo_open 处就处理权限错误，而不是等第一次读写，因为 PostgreSQL 11 之后检查时机提前了。

9. 引用列固定用 oid 类型（或 lo 类型），并建索引，既方便查询也让 vacuumlo 能识别。

10. 把 vacuumlo -n 加入例行巡检，确认无异常后再执行正式清理；频率与常规 VACUUM 巡检保持一致。

11. 避免在单个长事务内大批量打开描述符，改成短事务分批处理，减少锁与内存压力。

12. 用 pg_largeobject 的页统计做容量监控，及时发现异常膨胀：

    SELECT pg_size_pretty(sum(length(data))) AS lo_payload
    FROM pg_largeobject;

下一节

继续学习：BYTEA vs 大对象

参考链接

• PostgreSQL 官方文档：Server-Side Functions for Large Objects
• PostgreSQL 官方文档：Client Interfaces for Large Objects
• PostgreSQL 官方文档：Large Objects
• PostgreSQL 官方文档：Example Program for Large Objects
• PostgreSQL 官方文档：vacuumlo
• PostgreSQL 官方文档：psql
• PostgreSQL 官方文档：Predefined Roles
• PostgreSQL 官方文档：lo module
• PostgreSQL 官方文档：GRANT
• PHP 手册：PostgreSQL 大对象函数