Postgresql 中文操作指南

35.3. Client Interfaces #

本节介绍 PostgreSQL 的 libpq 客户端接口库用于访问大型对象所提供的功能。PostgreSQL 大型对象接口是仿照 Unix 文件系统接口建模的，具有 open、read、write、lseek 等类似内容。

This section describes the facilities that PostgreSQL’s libpq client interface library provides for accessing large objects. The PostgreSQL large object interface is modeled after the Unix file-system interface, with analogues of open, read, write, lseek, etc.

使用这些 must 函数的所有大型对象操作都在 SQL 事务块中进行，因为大型对象文件描述符仅在事务持续期间有效。不允许在只读事务中进行写操作，包括带 INV_WRITE 模式的 lo_open。

All large object manipulation using these functions must take place within an SQL transaction block, since large object file descriptors are only valid for the duration of a transaction. Write operations, including lo_open with the INV_WRITE mode, are not allowed in a read-only transaction.

如果在执行以下任意一个函数时发生错误，该函数将返回一个不可能的值，通常为 0 或 -1。描述错误的消息存储在连接对象中，并可以用 PQerrorMessage 检索。

If an error occurs while executing any one of these functions, the function will return an otherwise-impossible value, typically 0 or -1. A message describing the error is stored in the connection object and can be retrieved with PQerrorMessage.

使用这些函数的客户端应用程序应该包括头文件 libpq/libpq-fs.h，并与 libpq 库链接。

Client applications that use these functions should include the header file libpq/libpq-fs.h and link with the libpq library.

客户端应用程序在 libpq 连接处于管道模式时不能使用这些函数。

Client applications cannot use these functions while a libpq connection is in pipeline mode.

35.3.1. Creating a Large Object #

函数

The function

Oid lo_create(PGconn *conn, Oid lobjId);

创建一个新的大型对象。可以由 lobjId 指定要分配的 OID；如果指定，则当该 OID 已用于某个大型对象时，会发生故障。如果 lobjId 为 InvalidOid（零），则 lo_create 会分配一个未使用的 OID。返回值是分配给新大型对象，或故障的 InvalidOid（零）的 OID。

creates a new large object. The OID to be assigned can be specified by lobjId; if so, failure occurs if that OID is already in use for some large object. If lobjId is InvalidOid (zero) then lo_create assigns an unused OID. The return value is the OID that was assigned to the new large object, or InvalidOid (zero) on failure.

示例：

An example:

inv_oid = lo_create(conn, desired_oid);

旧函数

The older function

Oid lo_creat(PGconn *conn, int mode);

也创建一个新的大型对象，始终分配未使用的 OID。返回值是分配给新大型对象，或故障的 InvalidOid（零）的 OID。

also creates a new large object, always assigning an unused OID. The return value is the OID that was assigned to the new large object, or InvalidOid (zero) on failure.

在 PostgreSQL 8.1 及更高版本中，忽略 mode，所以 lo_creat 完全等于带有零秒参数的 lo_create。但是，除非需要处理早于 8.1 的服务器，否则几乎没有理由使用 lo_creat。要处理这样一个旧服务器，您必须使用 lo_creat，而不是 lo_create，并且必须将 mode 设置为 INV_READ、INV_WRITE 或 INV_READ | INV_WRITE 中的一个。（这些符号常量在头文件 libpq/libpq-fs.h 中定义。）

In PostgreSQL releases 8.1 and later, the mode is ignored, so that lo_creat is exactly equivalent to lo_create with a zero second argument. However, there is little reason to use lo_creat unless you need to work with servers older than 8.1. To work with such an old server, you must use lo_creat not lo_create, and you must set mode to one of INV_READ, INV_WRITE, or INV_READ | INV_WRITE. (These symbolic constants are defined in the header file libpq/libpq-fs.h.)

示例：

An example:

inv_oid = lo_creat(conn, INV_READ|INV_WRITE);

35.3.2. Importing a Large Object #

要将操作系统文件作为大对象导入，请调用

To import an operating system file as a large object, call

Oid lo_import(PGconn *conn, const char *filename);

filename 指定要作为大型对象导入的文件的操作系统名称。返回值是分配给新大型对象，或故障的 InvalidOid（零）的 OID。请注意，该文件由客户端接口库读取，而不是由服务器读取；因此，该文件必须存在于客户端文件系统中，并且客户端应用程序必须能够读取该文件。

filename specifies the operating system name of the file to be imported as a large object. The return value is the OID that was assigned to the new large object, or InvalidOid (zero) on failure. Note that the file is read by the client interface library, not by the server; so it must exist in the client file system and be readable by the client application.

函数

The function

Oid lo_import_with_oid(PGconn *conn, const char *filename, Oid lobjId);

也导入一个新的大型对象。可以由 lobjId 指定要分配的 OID；如果指定，则当该 OID 已用于某个大型对象时，会发生故障。如果 lobjId 为 InvalidOid（零），则 lo_import_with_oid 会分配一个未使用的 OID（这与 lo_import 的行为相同）。返回值是分配给新大型对象，或故障的 InvalidOid（零）的 OID。

also imports a new large object. The OID to be assigned can be specified by lobjId; if so, failure occurs if that OID is already in use for some large object. If lobjId is InvalidOid (zero) then lo_import_with_oid assigns an unused OID (this is the same behavior as lo_import). The return value is the OID that was assigned to the new large object, or InvalidOid (zero) on failure.

lo_import_with_oid 自 PostgreSQL 8.4 起是新的，并在内部使用 lo_create，后者在 8.1 中是新的；如果对 8.0 或更早的版本运行此函数，则会失败并返回 InvalidOid。

lo_import_with_oid is new as of PostgreSQL 8.4 and uses lo_create internally which is new in 8.1; if this function is run against 8.0 or before, it will fail and return InvalidOid.

35.3.3. Exporting a Large Object #

若要将大型对象导出为操作系统文件，调用

To export a large object into an operating system file, call

int lo_export(PGconn *conn, Oid lobjId, const char *filename);

lobjId 参数指定要导出的大型对象的 OID，filename 参数指定该文件的操作系统名称。请注意，该文件由客户端接口库写入，而不是由服务器写入。成功时返回 1，失败时返回 -1。

The lobjId argument specifies the OID of the large object to export and the filename argument specifies the operating system name of the file. Note that the file is written by the client interface library, not by the server. Returns 1 on success, -1 on failure.

35.3.4. Opening an Existing Large Object #

若要打开现有大型对象以便读取或写入，调用

To open an existing large object for reading or writing, call

int lo_open(PGconn *conn, Oid lobjId, int mode);

lobjId 参数指定要打开的大型对象的 OID。mode 位控制对象是打开以进行读取 (INV_READ)、写入 (INV_WRITE)，还是两者 (libpq/libpq-fs.h)。（这些符号常量在头文件 libpq/libpq-fs.h 中定义。）lo_open 为以后在 lo_read、lo_write、lo_lseek、lo_lseek64、lo_tell、lo_tell64、lo_truncate、lo_truncate64 和 lo_close 中使用返回一个（非负）大型对象描述符。该描述符仅在当前事务的持续期间有效。在失败时，返回 -1。

The lobjId argument specifies the OID of the large object to open. The mode bits control whether the object is opened for reading (INV_READ), writing (INV_WRITE), or both. (These symbolic constants are defined in the header file libpq/libpq-fs.h.) lo_open returns a (non-negative) large object descriptor for later use in lo_read, lo_write, lo_lseek, lo_lseek64, lo_tell, lo_tell64, lo_truncate, lo_truncate64, and lo_close. The descriptor is only valid for the duration of the current transaction. On failure, -1 is returned.

服务器当前不会区分模式 INV_WRITE 与 INV_READ | INV_WRITE：无论在何种情况下，您都可以从描述符中读取。但是，这些模式与单独的 INV_READ 之间存在显着差异：使用 INV_READ，您无法写入描述符，并且从该描述符读取的数据将反映在执行 lo_open 时激活的事务快照时间的大型对象内容，而不管此后此事务或其他事务的写入。使用 INV_WRITE 打开的描述符读取的内容反映了其他已提交事务的所有写入以及当前事务的写入。这与普通 SQL SELECT 命令的 REPEATABLE READ 与 READ COMMITTED 事务模式的行为类似。

The server currently does not distinguish between modes INV_WRITE and INV_READ | INV_WRITE: you are allowed to read from the descriptor in either case. However there is a significant difference between these modes and INV_READ alone: with INV_READ you cannot write on the descriptor, and the data read from it will reflect the contents of the large object at the time of the transaction snapshot that was active when lo_open was executed, regardless of later writes by this or other transactions. Reading from a descriptor opened with INV_WRITE returns data that reflects all writes of other committed transactions as well as writes of the current transaction. This is similar to the behavior of REPEATABLE READ versus READ COMMITTED transaction modes for ordinary SQL SELECT commands.

如果对象没有 SELECT 权限，或指定了 INV_WRITE 且没有 UPDATE 权限，则 lo_open 将失败。（在 PostgreSQL 11 之前，这些权限检查是在使用描述符时在第一个实际读入或写入调用上执行的。）可使用 lo_compat_privileges 运行时参数禁用这些权限检查。

lo_open will fail if SELECT privilege is not available for the large object, or if INV_WRITE is specified and UPDATE privilege is not available. (Prior to PostgreSQL 11, these privilege checks were instead performed at the first actual read or write call using the descriptor.) These privilege checks can be disabled with the lo_compat_privileges run-time parameter.

示例：

An example:

inv_fd = lo_open(conn, inv_oid, INV_READ|INV_WRITE);

35.3.5. Writing Data to a Large Object #

函数

The function

int lo_write(PGconn *conn, int fd, const char *buf, size_t len);

从 buf（其大小必须为 len）向大型对象描述符 fd 写入 len 字节。fd 参数必须由前一个 lo_open 返回。返回实际写入的字节数（在当前实现中，这总是等于 len，除非出现错误）。如果出现错误，返回值为 -1。

writes len bytes from buf (which must be of size len) to large object descriptor fd. The fd argument must have been returned by a previous lo_open. The number of bytes actually written is returned (in the current implementation, this will always equal len unless there is an error). In the event of an error, the return value is -1.

尽管 len 参数声明为 size_t，但此函数将拒绝大于 INT_MAX 的长度值。实际上，最好的做法是无论如何以几兆字节最多的大小传输数据。

Although the len parameter is declared as size_t, this function will reject length values larger than INT_MAX. In practice, it’s best to transfer data in chunks of at most a few megabytes anyway.

35.3.6. Reading Data from a Large Object #

函数

The function

int lo_read(PGconn *conn, int fd, char *buf, size_t len);

从大型对象描述符 fd 读入 buf（其大小必须为 len）最多 len 字节。fd 参数必须由前一个 lo_open 返回。返回实际读取的字节数；如果先到达大型对象的结尾，这将小于 len。如果出现错误，返回值为 -1。

reads up to len bytes from large object descriptor fd into buf (which must be of size len). The fd argument must have been returned by a previous lo_open. The number of bytes actually read is returned; this will be less than len if the end of the large object is reached first. In the event of an error, the return value is -1.

尽管 len 参数声明为 size_t，但此函数将拒绝大于 INT_MAX 的长度值。实际上，最好的做法是无论如何以几兆字节最多的大小传输数据。

Although the len parameter is declared as size_t, this function will reject length values larger than INT_MAX. In practice, it’s best to transfer data in chunks of at most a few megabytes anyway.

35.3.7. Seeking in a Large Object #

若要更改与大型对象描述符关联的当前读取或写入位置，调用

To change the current read or write location associated with a large object descriptor, call

int lo_lseek(PGconn *conn, int fd, int offset, int whence);

此函数将由 fd 标识的大型对象描述符的当前位置指针移动到由 offset 指定的新位置。whence 的有效值为 SEEK_SET（从对象开始处查找）、SEEK_CUR（从当前位置查找）和 SEEK_END（从对象末尾查找）。返回值为新位置指针，或在发生错误时返回 -1。

This function moves the current location pointer for the large object descriptor identified by fd to the new location specified by offset. The valid values for whence are SEEK_SET (seek from object start), SEEK_CUR (seek from current position), and SEEK_END (seek from object end). The return value is the new location pointer, or -1 on error.

在处理大小可能超过 2 GB 的大型对象时，改用

When dealing with large objects that might exceed 2GB in size, instead use

pg_int64 lo_lseek64(PGconn *conn, int fd, pg_int64 offset, int whence);

此函数的行为与 lo_lseek 相同，但它可以接受大于 2GB 的 offset 和/或提供大于 2GB 的结果。请注意，如果新位置指针大于 2GB，lo_lseek 将失败。

This function has the same behavior as lo_lseek, but it can accept an offset larger than 2GB and/or deliver a result larger than 2GB. Note that lo_lseek will fail if the new location pointer would be greater than 2GB.

lo_lseek64 自 PostgreSQL 9.3 起新增。如果针对较旧的服务器版本运行此函数，它将失败并返回 -1。

lo_lseek64 is new as of PostgreSQL 9.3. If this function is run against an older server version, it will fail and return -1.

35.3.8. Obtaining the Seek Position of a Large Object #

若要获取大型对象描述符的当前读取或写入位置，调用

To obtain the current read or write location of a large object descriptor, call

int lo_tell(PGconn *conn, int fd);

如果出现错误，返回值为 -1。

If there is an error, the return value is -1.

在处理大小可能超过 2 GB 的大型对象时，改用

When dealing with large objects that might exceed 2GB in size, instead use

pg_int64 lo_tell64(PGconn *conn, int fd);

此函数的行为与 lo_tell 相同，但它可以提供大于 2GB 的结果。请注意，如果当前读/写位置大于 2GB，lo_tell 将失败。

This function has the same behavior as lo_tell, but it can deliver a result larger than 2GB. Note that lo_tell will fail if the current read/write location is greater than 2GB.

lo_tell64 自 PostgreSQL 9.3 起新增。如果针对较旧的服务器版本运行此函数，它将失败并返回 -1。

lo_tell64 is new as of PostgreSQL 9.3. If this function is run against an older server version, it will fail and return -1.

35.3.9. Truncating a Large Object #

若要将大型对象截断为给定长度，调用

To truncate a large object to a given length, call

int lo_truncate(PGconn *conn, int fd, size_t len);

此函数将大型对象描述符 fd 截断为长度 len。fd 参数必须由前一个 lo_open 返回。如果 len 大于大型对象的当前长度，则大型对象将扩展到指定长度并使用空字节 ('\0') 填充。成功时，lo_truncate 返回零。如果出现错误，返回值为 -1。

This function truncates the large object descriptor fd to length len. The fd argument must have been returned by a previous lo_open. If len is greater than the large object’s current length, the large object is extended to the specified length with null bytes ('\0'). On success, lo_truncate returns zero. On error, the return value is -1.

与描述符 fd 关联的读/写位置未更改。

The read/write location associated with the descriptor fd is not changed.

尽管 len 参数声明为 size_t，但 lo_truncate 将拒绝大于 INT_MAX 的长度值。

Although the len parameter is declared as size_t, lo_truncate will reject length values larger than INT_MAX.

在处理大小可能超过 2 GB 的大型对象时，改用

When dealing with large objects that might exceed 2GB in size, instead use

int lo_truncate64(PGconn *conn, int fd, pg_int64 len);

此函数的行为与 lo_truncate 相同，但它可以接受一个超过 2GB 的 len 值。

This function has the same behavior as lo_truncate, but it can accept a len value exceeding 2GB.

lo_truncate 自 PostgreSQL 8.3 起新增；如果针对较旧的服务器版本运行此函数，它将失败并返回 -1。

lo_truncate is new as of PostgreSQL 8.3; if this function is run against an older server version, it will fail and return -1.

lo_truncate64 自 PostgreSQL 9.3 起新增；如果针对较旧的服务器版本运行此函数，它将失败并返回 -1。

lo_truncate64 is new as of PostgreSQL 9.3; if this function is run against an older server version, it will fail and return -1.

35.3.10. Closing a Large Object Descriptor #

可以通过调用关闭大型对象描述符

A large object descriptor can be closed by calling

int lo_close(PGconn *conn, int fd);

其中 fd 是由 lo_open 返回的大型对象描述符。成功时，lo_close 返回零。如果出现错误，返回值为 -1。

where fd is a large object descriptor returned by lo_open. On success, lo_close returns zero. On error, the return value is -1.

在事务结束时仍保持打开状态的任何大型对象描述符将自动关闭。

Any large object descriptors that remain open at the end of a transaction will be closed automatically.

若要从数据库中移除大型对象，调用

To remove a large object from the database, call

int lo_unlink(PGconn *conn, Oid lobjId);

lobjId 参数指定要移除的大型对象的 OID。如果成功，返回 1，如果失败，返回 -1。

The lobjId argument specifies the OID of the large object to remove. Returns 1 if successful, -1 on failure.