Skip to main content

Large Objects

A large object is a stream of bytes of up to 4 TiB, kept apart from any table and named by an oid. ScramDB serves PostgreSQL's large-object interface: the SQL functions, the descriptor functions inside a transaction, the two catalogs, the per-object privileges, and the function call message that libpq's lo_* calls send. An object is stored in 2048-byte pages through the same storage, transactions and cluster placement as a table, so a rolled-back write leaves nothing, a snapshot reads the object as it was when the statement began, and a cluster holds it with no extra setup.

Whole objects​

lo_from_bytea, lo_get and lo_put need no descriptor. An object id of 0 lets the server choose one; any other id names the object.

sql
SELECT lo_create(16001);
SELECT lo_put(16001, 0, 'hello large object'::bytea);
SELECT lo_get(16001);
SELECT lo_get(16001, 6, 5);
SELECT lo_put(16001, 6, 'LARGE'::bytea);
SELECT length(lo_get(16001));
SELECT lo_from_bytea(16002, 'abc'::bytea);
SELECT oid FROM pg_largeobject_metadata WHERE oid IN (16001, 16002) ORDER BY oid;
SELECT loid, pageno, length(data) FROM pg_largeobject WHERE loid = 16001 ORDER BY pageno;
SELECT lo_unlink(16001);
SELECT lo_unlink(16002);

Descriptors​

A descriptor reads and writes an object from an offset, as a file handle does. It belongs to its transaction: it is numbered from 0, the lowest free number first, and it is closed when the transaction ends. A statement outside a transaction block is its own transaction, so a descriptor it opens is gone when it returns; open and use descriptors inside BEGIN ... COMMIT.

sql
BEGIN;
SELECT lo_create(16003);
SELECT lo_open(16003, 393216);
SELECT lowrite(0, 'hello world'::bytea);
SELECT lo_lseek(0, 0, 0);
SELECT loread(0, 5);
SELECT lo_tell(0);
SELECT lo_truncate(0, 5);
SELECT lo_close(0);
COMMIT;
SELECT lo_get(16003);
SELECT lo_unlink(16003);

The mode of lo_open is INV_READ (262144), INV_WRITE (131072) or both (393216); a descriptor opened for writing also reads. A descriptor opened for reading only sees the object as it was when lo_open ran, with that transaction's own earlier writes: what another session commits or unlinks afterwards, and what the transaction writes afterwards, is not read through it, and the size SEEK_END answers is the one at the open. A descriptor opened for writing reads the newest rows. lo_lseek takes SEEK_SET (0), SEEK_CUR (1) or SEEK_END (2).

Two sessions writing one object conflict only on a page both write: the second waits for the first and, once it commits, fails with XX000 tuple concurrently updated (deleted when the first unlinked the object); two sessions that both make the same new page, or the same object, fail with 23505 on pg_largeobject_loid_pn_index or pg_largeobject_metadata_oid_index; sessions that write different pages never wait. A write call reads the newest committed rows at every isolation level, so a REPEATABLE READ or SERIALIZABLE transaction does not fail a write over a commit that came after its snapshot, and finds an object unlinked since as missing (42704). The size of an object is the length of its last page.

FunctionAnswer
lo_create(oid), lo_creat(mode)The object's id. lo_create(0) and lo_creat choose it.
lo_open(oid, mode)The descriptor.
loread(fd, length), lowrite(fd, bytea)The bytes read (fewer at the end of the object), the number of bytes written.
lo_lseek(fd, offset, whence), lo_tell(fd)The new position, the position. lo_lseek64 and lo_tell64 answer 64-bit positions.
lo_truncate(fd, length), lo_truncate640. Growing an object fills the gap with zero bytes.
lo_close(fd)0.
lo_unlink(oid)1.
lo_get(oid [, offset, length]), lo_put(oid, offset, bytea), lo_from_bytea(oid, bytea)The bytes, nothing, the object's id.
lo_import(path [, oid]), lo_export(oid, path)The new object's id, 1.

A read, a write or a seek on a descriptor that is not open answers 42704, a write on a descriptor opened for reading 55000, and a read-only transaction refuses every function that changes an object.

Server files​

lo_import and lo_export read and write files on the server, a relative path being relative to the data directory. Only a superuser may call them unless EXECUTE was granted; no other privilege is checked, as in PostgreSQL. A file is read and written a bounded piece at a time, however large it is.

sql
SELECT lo_import('<path on the server>');
SELECT lo_import('<path on the server>', 16010);
SELECT lo_export(16010, '<path on the server>');

Privileges​

An object has an owner, the role that made it, and an access list. SELECT lets a role read it (lo_get, a read descriptor, lo_export) and UPDATE write, truncate and open it for writing; the owner and a superuser hold both, and only the owner or a role that is a member of the owner's role may unlink, grant, comment or change the owner. Every check is the one the other objects use, so the answers are PostgreSQL's: 42501 permission denied for large object N for a missing privilege, 42704 for an object that is not there.

sql
CREATE ROLE lo_reader;
SELECT lo_create(16004);
GRANT SELECT ON LARGE OBJECT 16004 TO lo_reader;
GRANT UPDATE ON LARGE OBJECT 16004 TO lo_reader;
REVOKE UPDATE ON LARGE OBJECT 16004 FROM lo_reader;
COMMENT ON LARGE OBJECT 16004 IS 'scanned contract';
SELECT has_largeobject_privilege('lo_reader', 16004, 'SELECT');
SELECT has_largeobject_privilege('lo_reader', 16004, 'UPDATE');
SELECT has_largeobject_privilege(16004, 'SELECT, UPDATE');
SELECT oid, lomacl FROM pg_largeobject_metadata WHERE oid = 16004;
ALTER LARGE OBJECT 16004 OWNER TO lo_reader;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO lo_reader;
ALTER DEFAULT PRIVILEGES REVOKE SELECT ON LARGE OBJECTS FROM lo_reader;
SELECT lo_unlink(16004);
DROP OWNED BY lo_reader;
DROP ROLE lo_reader;
  • has_largeobject_privilege([role,] object, privileges) answers whether the role (the current one when left out; a name, where public is every role, or an oid) holds any privilege of the comma-separated list, SELECT or UPDATE, optionally followed by WITH GRANT OPTION. It answers NULL for an object that is not there, 42704 for a role that is not and 22023 for a privilege it does not know.
  • ALTER DEFAULT PRIVILEGES ... ON LARGE OBJECTS seeds the access list of every object its role creates afterwards.
  • The setting lo_compat_privileges (superuser, off by default) lifts every per-object check, as in versions of PostgreSQL before 9.0: reading, writing and COMMENT ON LARGE OBJECT. ALTER LARGE OBJECT ... OWNER TO still needs the object's owner.
  • A role that owns objects or holds privileges on them is dropped after DROP OWNED BY or REASSIGN OWNED BY, both of which cover large objects; DROP ROLE otherwise answers 2BP01 and names the objects.
  • EXECUTE on the functions is granted to everyone except lo_import and lo_export, and REVOKE EXECUTE removes it as for any function.

Catalogs​

pg_largeobject_metadata holds one row per object (oid, lomowner, lomacl) and is readable by every role. pg_largeobject holds the pages (loid, pageno, data) and is readable by a superuser, by the members of pg_read_all_data and by a role granted SELECT on it: any other role gets 42501 permission denied for table pg_largeobject. Both are read-only views; change objects through the functions. pg_description carries the comment of an object under the class pg_largeobject.

Function call message​

Drivers that call large-object functions through the protocol's function call message (F, for example libpq's lo_open, lo_read, lo_write and lo_lseek) are served as PostgreSQL serves them: the arguments and the result travel in text or binary format per field, a void function answers a zero-length value, a strict function with a NULL argument answers NULL, and a refused call is an error followed by ReadyForQuery with the session intact. The message runs as one statement, so the EXECUTE check, the transaction block and every error answer are those of the SQL call.

A call names its function by pg_proc oid. The large-object functions, the other system routines and user-defined scalar functions are served; a procedure or a set-returning function answers 0A000 cannot call function "name" via fastpath interface, and a PostgreSQL built-in with no routine of the same oid here answers 42883 function with OID N does not exist. A message larger than the connection's read buffer holds its bytes under the server's execution memory, and 53200 answers a call the server has no memory for.

Differences from PostgreSQL​

HerePostgreSQL
On a cluster, a REPEATABLE READ or SERIALIZABLE transaction that writes a page another node committed to after its snapshot may fail with 40001 when it commits.The write succeeds at every isolation level.

Metrics​

Large-object activity is counted on the metrics endpoint: see Observability. The function call message adds scramdb_connection_function_calls_total and scramdb_connection_function_call_failures_total.