Postgresql 中文操作指南

F.22. lo — manage large objects #

lo 模块提供用于管理大对象（也称为 LO 或 BLOB）的支持。其中包括数据类型 lo 和触发器 lo_manage。

The lo module provides support for managing Large Objects (also called LOs or BLOBs). This includes a data type lo and a trigger lo_manage.

此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上具有 CREATE 权限的非超级用户安装。

This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.

F.22.1. Rationale #

JDBC 驱动程序的一个问题（它也影响 ODBC 驱动程序），是其规范假设对 BLOB（二进制大对象）的引用存储在表中，如果更改该条目，则会从数据库中删除关联的 BLOB。

One of the problems with the JDBC driver (and this affects the ODBC driver also), is that the specification assumes that references to BLOBs (Binary Large OBjects) are stored within a table, and if that entry is changed, the associated BLOB is deleted from the database.

由于 PostgreSQL 的特性，此问题不会发生。大对象被视为其本身的对象；表条目可以通过 OID 引用大对象，但是有多个表条目可以引用相同的大对象 OID，因此系统不会仅仅因为你更改或删除某个此类条目就删除大对象。

As PostgreSQL stands, this doesn’t occur. Large objects are treated as objects in their own right; a table entry can reference a large object by OID, but there can be multiple table entries referencing the same large object OID, so the system doesn’t delete the large object just because you change or remove one such entry.

对于 PostgreSQL 特定的应用程序来说这很好，但是使用 JDBC 或 ODBC 的标准代码不会删除对象，从而会导致孤儿对象 - 这些对象不被任何事物引用，而且只占用磁盘空间。

Now this is fine for PostgreSQL-specific applications, but standard code using JDBC or ODBC won’t delete the objects, resulting in orphan objects — objects that are not referenced by anything, and simply occupy disk space.

lo 模块允许通过附加触发器到包含 LO 引用列的表来修复它。触发器实际上只在你删除或修改引用大对象的值时执行 lo_unlink。当你使用这个触发器时，你是在假设在触发器控制的列里只存在对任何大对象的 1 个数据库引用！

The lo module allows fixing this by attaching a trigger to tables that contain LO reference columns. The trigger essentially just does a lo_unlink whenever you delete or modify a value referencing a large object. When you use this trigger, you are assuming that there is only one database reference to any large object that is referenced in a trigger-controlled column!

该模块还提供了一个数据类型 lo ，它实际上只是一个 [role="bare"]glossary.html#GLOSSARY-DOMAIN domain 类型上的 oid 类型。这对于区分存储大对象引用的数据库列与其他对象的 OID 十分有用。你无需使用 lo 类型来使用触发器，但可以使用它来跟踪数据库中的哪些列表示你正使用触发器管理的大对象，这可能会很方便。据传，如果你不使用 lo 来处理 BLOB 列，ODBC 驱动程序会感到困惑。

The module also provides a data type lo, which is really just a [role="bare"]glossary.html#GLOSSARY-DOMAINdomain over the oid type. This is useful for differentiating database columns that hold large object references from those that are OIDs of other things. You don’t have to use the lo type to use the trigger, but it may be convenient to use it to keep track of which columns in your database represent large objects that you are managing with the trigger. It is also rumored that the ODBC driver gets confused if you don’t use lo for BLOB columns.

F.22.2. How to Use It #

下面是一个简单的使用示例：

Here’s a simple example of usage:

CREATE TABLE image (title text, raster lo);

CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
    FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

对于将包含对大对象的唯一引用的每个列，创建一个 BEFORE UPDATE OR DELETE 触发器，并把列名设为唯一的触发器参数。你还可以使用 BEFORE UPDATE OF column_name 限制触发器仅对列更新执行。如果你需要同一表中的多个 lo 列，为每个列创建一个单独的触发器，记住给同一表上的每个触发器指定不同的名称。

For each column that will contain unique references to large objects, create a BEFORE UPDATE OR DELETE trigger, and give the column name as the sole trigger argument. You can also restrict the trigger to only execute on updates to the column by using BEFORE UPDATE OF column_name. If you need multiple lo columns in the same table, create a separate trigger for each one, remembering to give a different name to each trigger on the same table.

F.22.3. Limitations #

F.22.4. Author #

Peter Mount < link:mailto:peter@retep.org.uk[peter@retep.org.uk]>

Peter Mount <link:mailto:peter@retep.org.uk[peter@retep.org.uk]>