tessl/maven-org-postgresql--postgresql
PostgreSQL JDBC Driver allows Java programs to connect to a PostgreSQL database using standard, database independent Java code.
large-objects.mddocs/
Large Objects
This document covers PostgreSQL's Large Object (LOB/BLOB) API for storing and managing large binary data that exceeds the 1GB limit of the bytea type.
Capabilities
LargeObjectManager
Manager for creating, opening, and deleting large objects.
package org.postgresql.largeobject;
import java.sql.SQLException;
/**
* Manager for PostgreSQL Large Objects (BLOBs).
* Large objects are stored outside regular tables and can be up to 4TB in size.
* Access via PGConnection.getLargeObjectAPI().
*
* Note: All large object operations must be performed within a transaction.
*/
public class LargeObjectManager {
/**
* Mode constant: Open large object for reading.
*/
public static final int READ = 0x00040000;
/**
* Mode constant: Open large object for writing.
*/
public static final int WRITE = 0x00020000;
/**
* Mode constant: Open large object for reading and writing.
*/
public static final int READWRITE = READ | WRITE;
/**
* Creates a new large object with default READWRITE access.
* Returns a unique OID that identifies the large object.
*
* @return OID of the created large object
* @throws SQLException if creation fails
*/
public long createLO() throws SQLException;
/**
* Creates a new large object with specific mode.
*
* @param mode Access mode (READ, WRITE, or READWRITE)
* @return OID of the created large object
* @throws SQLException if creation fails
*/
public long createLO(int mode) throws SQLException;
/**
* Deprecated: Use createLO() instead. Returns int instead of long.
*
* @return OID of created large object as int
* @throws SQLException if creation fails
* @deprecated Use {@link #createLO()} instead
*/
@Deprecated
public int create() throws SQLException;
/**
* Deprecated: Use createLO(int) instead. Returns int instead of long.
*
* @param mode Access mode
* @return OID of created large object as int
* @throws SQLException if creation fails
* @deprecated Use {@link #createLO(int)} instead
*/
@Deprecated
public int create(int mode) throws SQLException;
/**
* Opens an existing large object with READWRITE mode.
*
* @param oid OID of the large object
* @return LargeObject handle for I/O operations
* @throws SQLException if large object doesn't exist or cannot be opened
*/
public LargeObject open(long oid) throws SQLException;
/**
* Opens an existing large object with READWRITE mode, optionally committing on close.
*
* @param oid OID of the large object
* @param commitOnClose If true, commits transaction when large object is closed
* @return LargeObject handle for I/O operations
* @throws SQLException if large object doesn't exist or cannot be opened
*/
public LargeObject open(long oid, boolean commitOnClose) throws SQLException;
/**
* Opens an existing large object with specified mode.
*
* @param oid OID of the large object
* @param mode Access mode (READ, WRITE, or READWRITE)
* @return LargeObject handle for I/O operations
* @throws SQLException if large object doesn't exist or cannot be opened
*/
public LargeObject open(long oid, int mode) throws SQLException;
/**
* Opens an existing large object with specified mode, optionally committing on close.
*
* @param oid OID of the large object
* @param mode Access mode (READ, WRITE, or READWRITE)
* @param commitOnClose If true, commits transaction when large object is closed
* @return LargeObject handle for I/O operations
* @throws SQLException if large object doesn't exist or cannot be opened
*/
public LargeObject open(long oid, int mode, boolean commitOnClose) throws SQLException;
/**
* Deprecated: Use open(long) instead. Takes int OID.
*
* @param oid OID of large object as int
* @return LargeObject handle
* @throws SQLException if open fails
* @deprecated Use {@link #open(long)} instead
*/
@Deprecated
public LargeObject open(int oid) throws SQLException;
/**
* Deprecated: Opens large object with int OID, optionally committing on close.
*
* @param oid OID of large object as int
* @param commitOnClose If true, commits transaction when large object is closed
* @return LargeObject handle
* @throws SQLException if open fails
*/
public LargeObject open(int oid, boolean commitOnClose) throws SQLException;
/**
* Deprecated: Use open(long, int) instead. Takes int OID.
*
* @param oid OID of large object as int
* @param mode Access mode
* @return LargeObject handle
* @throws SQLException if open fails
* @deprecated Use {@link #open(long, int)} instead
*/
@Deprecated
public LargeObject open(int oid, int mode) throws SQLException;
/**
* Deprecated: Opens large object with int OID and mode, optionally committing on close.
*
* @param oid OID of large object as int
* @param mode Access mode
* @param commitOnClose If true, commits transaction when large object is closed
* @return LargeObject handle
* @throws SQLException if open fails
*/
public LargeObject open(int oid, int mode, boolean commitOnClose) throws SQLException;
/**
* Deletes a large object.
* The large object must not be open.
*
* @param oid OID of the large object to delete
* @throws SQLException if deletion fails
*/
public void delete(long oid) throws SQLException;
/**
* Deprecated: Use delete(long) instead. Takes int OID.
*
* @param oid OID of large object to delete as int
* @throws SQLException if deletion fails
* @deprecated Use {@link #delete(long)} instead
*/
@Deprecated
public void delete(int oid) throws SQLException;
/**
* Deletes a large object (alias for delete).
* The large object must not be open.
*
* @param oid OID of the large object to delete
* @throws SQLException if deletion fails
*/
public void unlink(long oid) throws SQLException;
/**
* Deprecated: Use unlink(long) instead. Takes int OID.
*
* @param oid OID of large object to delete as int
* @throws SQLException if deletion fails
* @deprecated Use {@link #unlink(long)} instead
*/
@Deprecated
public void unlink(int oid) throws SQLException;
}Usage Examples:
import org.postgresql.PGConnection;
import org.postgresql.largeobject.LargeObjectManager;
import org.postgresql.largeobject.LargeObject;
import java.sql.Connection;
import java.sql.SQLException;
// Example 1: Create and write to large object
public class CreateLargeObjectExample {
public static long createAndWrite(Connection conn, byte[] data)
throws SQLException {
// Must be in transaction
conn.setAutoCommit(false);
try {
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
// Create new large object
long oid = lobj.createLO(LargeObjectManager.READWRITE);
// Open for writing
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
// Write data
obj.write(data);
// Close
obj.close();
// Commit transaction
conn.commit();
return oid;
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}
// Example 2: Read from large object
public class ReadLargeObjectExample {
public static byte[] readAll(Connection conn, long oid) throws SQLException {
conn.setAutoCommit(false);
try {
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
// Open for reading
LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
// Get size and read all data
long size = obj.size64();
byte[] data = new byte[(int) size];
obj.read(data, 0, data.length);
// Close
obj.close();
conn.commit();
return data;
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}
// Example 3: Delete large object
public class DeleteLargeObjectExample {
public static void deleteLargeObject(Connection conn, long oid)
throws SQLException {
conn.setAutoCommit(false);
try {
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
// Delete
lobj.delete(oid);
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}LargeObject
Handle for reading and writing large object data.
package org.postgresql.largeobject;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;
/**
* Represents an open large object for I/O operations.
* Must be closed when done.
*/
public class LargeObject {
/**
* Seek reference: Absolute position from start of large object.
*/
public static final int SEEK_SET = 0;
/**
* Seek reference: Relative to current position.
*/
public static final int SEEK_CUR = 1;
/**
* Seek reference: Relative to end of large object.
*/
public static final int SEEK_END = 2;
/**
* Reads data from the large object into a buffer.
*
* @param buf Buffer to receive data
* @param off Offset in buffer
* @param len Maximum number of bytes to read
* @return Number of bytes actually read, or -1 if at end
* @throws SQLException if read fails
*/
public int read(byte[] buf, int off, int len) throws SQLException;
/**
* Writes data to the large object.
*
* @param buf Buffer containing data to write
* @throws SQLException if write fails
*/
public void write(byte[] buf) throws SQLException;
/**
* Writes partial buffer to the large object.
*
* @param buf Buffer containing data
* @param off Offset in buffer
* @param len Number of bytes to write
* @throws SQLException if write fails
*/
public void write(byte[] buf, int off, int len) throws SQLException;
/**
* Deprecated: Use seek64(long, int) instead.
* Seeks to a position in the large object.
*
* @param pos Position to seek to
* @throws SQLException if seek fails
* @deprecated Limited to 2GB, use {@link #seek64(long, int)} instead
*/
@Deprecated
public void seek(int pos) throws SQLException;
/**
* Deprecated: Use seek64(long, int) instead.
* Seeks with reference point.
*
* @param pos Position relative to reference
* @param ref Reference point (SEEK_SET=0, SEEK_CUR=1, SEEK_END=2)
* @throws SQLException if seek fails
* @deprecated Limited to 2GB, use {@link #seek64(long, int)} instead
*/
@Deprecated
public void seek(int pos, int ref) throws SQLException;
/**
* Seeks to a 64-bit position in the large object.
* Supports large objects up to 4TB.
*
* @param pos Position to seek to (or offset if using reference)
* @param ref Reference point:
* 0 (SEEK_SET) = absolute position from start
* 1 (SEEK_CUR) = relative to current position
* 2 (SEEK_END) = relative to end
* @throws SQLException if seek fails
*/
public void seek64(long pos, int ref) throws SQLException;
/**
* Deprecated: Use tell64() instead.
* Returns the current position in the large object.
*
* @return Current position
* @throws SQLException if position cannot be determined
* @deprecated Limited to 2GB, use {@link #tell64()} instead
*/
@Deprecated
public int tell() throws SQLException;
/**
* Returns the current 64-bit position in the large object.
*
* @return Current position
* @throws SQLException if position cannot be determined
*/
public long tell64() throws SQLException;
/**
* Deprecated: Use size64() instead.
* Returns the size of the large object.
*
* @return Size in bytes
* @throws SQLException if size cannot be determined
* @deprecated Limited to 2GB, use {@link #size64()} instead
*/
@Deprecated
public int size() throws SQLException;
/**
* Returns the 64-bit size of the large object.
*
* @return Size in bytes
* @throws SQLException if size cannot be determined
*/
public long size64() throws SQLException;
/**
* Deprecated: Use truncate64(long) instead.
* Truncates the large object to specified length.
*
* @param len New length
* @throws SQLException if truncation fails
* @deprecated Limited to 2GB, use {@link #truncate64(long)} instead
*/
@Deprecated
public void truncate(int len) throws SQLException;
/**
* Truncates the large object to specified 64-bit length.
*
* @param len New length in bytes
* @throws SQLException if truncation fails
*/
public void truncate64(long len) throws SQLException;
/**
* Returns an InputStream for reading from the large object.
* Allows using standard Java I/O operations.
*
* @return InputStream for reading
* @throws SQLException if stream cannot be created
*/
public InputStream getInputStream() throws SQLException;
/**
* Returns an InputStream for reading from the large object with a limit.
* The stream will return EOF after the specified number of bytes.
*
* @param limit Maximum number of bytes to read from the stream
* @return InputStream for reading (limited to specified bytes)
* @throws SQLException if stream cannot be created
*/
public InputStream getInputStream(long limit) throws SQLException;
/**
* Returns an InputStream for reading from the large object with custom buffer size and limit.
* The stream will return EOF after the specified number of bytes.
*
* @param bufferSize Size of the internal buffer for the stream
* @param limit Maximum number of bytes to read from the stream
* @return InputStream for reading (limited to specified bytes)
* @throws SQLException if stream cannot be created
*/
public InputStream getInputStream(int bufferSize, long limit) throws SQLException;
/**
* Returns an OutputStream for writing to the large object.
* Allows using standard Java I/O operations.
*
* @return OutputStream for writing
* @throws SQLException if stream cannot be created
*/
public OutputStream getOutputStream() throws SQLException;
/**
* Closes the large object handle.
* Must be called when done with the large object.
*
* @throws SQLException if close fails
*/
public void close() throws SQLException;
/**
* Creates a copy of this large object handle.
* The copy shares the same file descriptor but has an independent position.
*
* @return A new LargeObject handle pointing to the same large object
* @throws SQLException if copy fails
*/
public LargeObject copy() throws SQLException;
/**
* Returns the OID of this large object.
*
* @return OID
*/
public long getLongOID();
/**
* Deprecated: Use getLongOID() instead.
* Returns the OID as int.
*
* @return OID
* @deprecated Use {@link #getLongOID()} instead
*/
@Deprecated
public int getOID();
}Usage Examples:
import org.postgresql.largeobject.LargeObject;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.io.IOException;
// Example 1: Streaming read with InputStream
public class StreamingReadExample {
public static void readToFile(Connection conn, long oid, String filename)
throws SQLException, IOException {
conn.setAutoCommit(false);
try {
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
// Use InputStream for convenient reading
try (InputStream in = obj.getInputStream();
FileOutputStream out = new FileOutputStream(filename)) {
byte[] buffer = new byte[8192];
int bytesRead;
while ((bytesRead = in.read(buffer)) != -1) {
out.write(buffer, 0, bytesRead);
}
}
obj.close();
conn.commit();
} catch (SQLException | IOException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}
// Example 2: Streaming write with OutputStream
public class StreamingWriteExample {
public static long writeFromFile(Connection conn, String filename)
throws SQLException, IOException {
conn.setAutoCommit(false);
try {
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
// Create large object
long oid = lobj.createLO(LargeObjectManager.READWRITE);
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
// Use OutputStream for convenient writing
try (OutputStream out = obj.getOutputStream();
FileInputStream in = new FileInputStream(filename)) {
byte[] buffer = new byte[8192];
int bytesRead;
while ((bytesRead = in.read(buffer)) != -1) {
out.write(buffer, 0, bytesRead);
}
}
obj.close();
conn.commit();
return oid;
} catch (SQLException | IOException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}
// Example 3: Random access operations
public class RandomAccessExample {
public static void modifyPartial(Connection conn, long oid,
long offset, byte[] data)
throws SQLException {
conn.setAutoCommit(false);
try {
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
LargeObject obj = lobj.open(oid, LargeObjectManager.READWRITE);
// Seek to offset
obj.seek64(offset, 0); // 0 = SEEK_SET (absolute position)
// Write data at that position
obj.write(data);
// Get final position and size
long position = obj.tell64();
long size = obj.size64();
System.out.println("Position: " + position + ", Size: " + size);
obj.close();
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
public static byte[] readPartial(Connection conn, long oid,
long offset, int length)
throws SQLException {
conn.setAutoCommit(false);
try {
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
// Seek to offset
obj.seek64(offset, 0);
// Read specific amount
byte[] data = new byte[length];
int bytesRead = obj.read(data, 0, length);
obj.close();
conn.commit();
// Return only bytes actually read
if (bytesRead < length) {
byte[] result = new byte[bytesRead];
System.arraycopy(data, 0, result, 0, bytesRead);
return result;
}
return data;
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}
// Example 4: Truncate large object
public class TruncateExample {
public static void truncate(Connection conn, long oid, long newSize)
throws SQLException {
conn.setAutoCommit(false);
try {
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
LargeObject obj = lobj.open(oid, LargeObjectManager.READWRITE);
// Truncate to new size
obj.truncate64(newSize);
obj.close();
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}Integration with Tables
Storing large object OIDs in tables.
Usage Examples:
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
// Example 1: Store OID in table
public class StoreLargeObjectExample {
public static void storeDocument(Connection conn, String title,
String filename) throws SQLException, IOException {
conn.setAutoCommit(false);
try {
// Create large object
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
long oid = lobj.createLO(LargeObjectManager.READWRITE);
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
// Write file to large object
try (OutputStream out = obj.getOutputStream();
FileInputStream in = new FileInputStream(filename)) {
byte[] buffer = new byte[8192];
int bytesRead;
while ((bytesRead = in.read(buffer)) != -1) {
out.write(buffer, 0, bytesRead);
}
}
obj.close();
// Store OID in table
try (PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO documents (title, content_oid) VALUES (?, ?)")) {
pstmt.setString(1, title);
pstmt.setLong(2, oid);
pstmt.executeUpdate();
}
conn.commit();
} catch (SQLException | IOException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}
// Example 2: Retrieve OID from table and read
public class RetrieveLargeObjectExample {
public static byte[] getDocument(Connection conn, int documentId)
throws SQLException {
conn.setAutoCommit(false);
try {
// Get OID from table
long oid;
try (PreparedStatement pstmt = conn.prepareStatement(
"SELECT content_oid FROM documents WHERE id = ?")) {
pstmt.setInt(1, documentId);
try (ResultSet rs = pstmt.executeQuery()) {
if (!rs.next()) {
throw new SQLException("Document not found");
}
oid = rs.getLong("content_oid");
}
}
// Read large object
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
long size = obj.size64();
byte[] data = new byte[(int) size];
obj.read(data, 0, data.length);
obj.close();
conn.commit();
return data;
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}
// Example 3: Delete document and large object
public class DeleteLargeObjectExample {
public static void deleteDocument(Connection conn, int documentId)
throws SQLException {
conn.setAutoCommit(false);
try {
// Get OID
long oid;
try (PreparedStatement pstmt = conn.prepareStatement(
"SELECT content_oid FROM documents WHERE id = ?")) {
pstmt.setInt(1, documentId);
try (ResultSet rs = pstmt.executeQuery()) {
if (!rs.next()) {
throw new SQLException("Document not found");
}
oid = rs.getLong("content_oid");
}
}
// Delete from table
try (PreparedStatement pstmt = conn.prepareStatement(
"DELETE FROM documents WHERE id = ?")) {
pstmt.setInt(1, documentId);
pstmt.executeUpdate();
}
// Delete large object
PGConnection pgConn = conn.unwrap(PGConnection.class);
LargeObjectManager lobj = pgConn.getLargeObjectAPI();
lobj.delete(oid);
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
}
}Large Objects vs. Bytea
Comparison:
| Feature | Large Objects | Bytea Column |
|---|---|---|
| Maximum size | 4 TB | 1 GB |
| Storage | Outside tables (pg_largeobject) | Inside tables |
| Random access | Yes (seek/tell) | No (must read entire value) |
| Streaming | Yes (InputStream/OutputStream) | No |
| Transaction safety | Must be in transaction | Normal ACID |
| Vacuum | Requires separate cleanup | Automatic |
| Performance | Better for very large data | Better for small/medium data |
Best Practices:
-
Use Large Objects for:
- Files larger than 1 MB
- Data requiring random access
- Streaming uploads/downloads
-
Use Bytea for:
- Small binary data (< 1 MB)
- Data that fits in memory
- Simpler transaction handling
-
Always use transactions
conn.setAutoCommit(false); // Large object operations conn.commit(); -
Clean up orphaned large objects
-- Find orphaned large objects SELECT oid FROM pg_largeobject_metadata WHERE oid NOT IN (SELECT content_oid FROM documents); -
Consider using triggers for cleanup
CREATE TRIGGER delete_large_object_trigger AFTER DELETE ON documents FOR EACH ROW EXECUTE FUNCTION lo_manage(content_oid);
Install with Tessl CLI
npx tessl i tessl/maven-org-postgresql--postgresql