# 大对象API

## 1. SQL接口
– lo_create：创建大对象
– lo_import：从文件导入大对象
– lo_export：导出大对象到文件
– lo_unlink：删除大对象
– lo_open：打开大对象
– lo_close：关闭大对象
– lo_read：读取大对象
– lo_write：写入大对象
– lo_lseek：定位大对象

## 2. 客户端库接口
– libpq：C语言接口
– psycopg2：Python接口
– JDBC：Java接口
– pq：Go接口
– Npgsql：.NET接口

## 3. 事务支持
– 大对象操作支持事务
– 可以在事务中回滚大对象操作
– 支持并发访问控制

## 4. 错误处理
– 大对象操作可能的错误：
– 权限错误
– 空间不足错误
– 锁定错误
– I/O错误

# 大对象实现

## 1. 数据库表结构
– pg_largeobject：存储大对象数据
– pg_largeobject_metadata：存储大对象元数据

## 2. 存储机制
– 大对象数据被分割成多个块存储
– 每个块默认大小为2KB
– 通过OID唯一标识大对象

## 3. 访问机制
– 大对象通过OID访问
– 支持流式读写
– 支持随机访问

## 4. 权限管理
– 大对象权限基于数据库权限
– 可以通过GRANT/REVOKE管理权限
– 支持行级安全策略

## 5. 垃圾回收
– 未引用的大对象会被VACUUM清理
– 可以手动运行VACUUM FULL清理

# 应用集成

## 1. 集成策略
– 直接使用大对象API
– 通过中间层封装大对象操作
– 使用ORM框架的大对象支持

## 2. 元数据管理
– 创建元数据表存储大对象元数据
– 包含文件名、类型、大小等信息
– 建立索引提高查询性能

## 3. 上传下载流程
– 上传：将文件内容写入大对象，保存OID和元数据
– 下载：根据OID读取大对象内容，返回给客户端

## 4. 缓存策略
– 对于频繁访问的大对象使用缓存
– 缓存大小根据内存情况调整
– 实现缓存失效机制

## 5. 错误处理
– 处理大对象操作错误
– 实现重试机制
– 记录错误日志

#!/usr/bin/env python3
# large_objects_python.py
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

import psycopg2
import os

# 数据库连接参数
conn_params = {
“fgedu.net.cn”: “localfgedu.net.cn”,
“port”: 5432,
“fgedudb”: “fgedudb”,
“fgedu”: “fgedu”,
“password”: “fgedu_password”
}

# 创建大对象表
def create_lob_table():
conn = psycopg2.connect(**conn_params)
cur = conn.cursor()

# 创建大对象元数据表
cur.execute(“””
CREATE TABLE fgedu_IF NOT EXISTS fgedu_files (
id SERIAL PRIMARY KEY,
file_name VARCHAR(255) NOT NULL,
file_type VARCHAR(100) NOT NULL,
file_size BIGINT NOT NULL,
lob_oid OID NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
“””)

conn.commit()
cur.close()
conn.close()
print(“大对象表创建成功”)

# 上传文件到PostgreSQL大对象
def upload_file(file_path):
conn = psycopg2.connect(**conn_params)
cur = conn.cursor()

# 获取文件名和类型
file_name = os.path.basename(file_path)
file_type = os.path.splitext(file_path)[1].lstrip(‘.’)
file_size = os.path.getsize(file_path)

try:
# 开始事务
conn.autocommit = False

# 导入文件到大对象
cur.execute(“SELECT lo_import(%s)”, (file_path,))
lob_oid = cur.fetchone()[0]

# 插入元数据
cur.execute(“””
INSERT INTO fgedu_files (file_name, file_type, file_size, lob_oid)
VALUES (%s, %s, %s, %s)
“””, (file_name, file_type, file_size, lob_oid))

# 提交事务
conn.commit()
print(f”文件上传成功，OID: {lob_oid}”)
return lob_oid
except Exception as e:
# 回滚事务
conn.rollback()
print(f”上传失败: {e}”)
return None
finally:
cur.close()
conn.close()

# 从PostgreSQL大对象下载文件
def download_file(lob_oid, output_path):
conn = psycopg2.connect(**conn_params)
cur = conn.cursor()

try:
# 导出大对象到文件
cur.execute(“SELECT lo_export(%s, %s)”, (lob_oid, output_path))
conn.commit()
print(f”文件下载成功: {output_path}”)
return True
except Exception as e:
print(f”下载失败: {e}”)
return False
finally:
cur.close()
conn.close()

# 列出所有大对象
def list_large_objects():
conn = psycopg2.connect(**conn_params)
cur = conn.cursor()

try:
cur.execute(“””
SELECT id, file_name, file_type, file_size, lob_oid, created_at
FROM fgedu_files
ORDER BY created_at DESC
“””)

rows = cur.fetchall()
print(“大对象列表:”)
for row in rows:
print(f”ID: {row[0]}, 文件名: {row[1]}, 类型: {row[2]}, 大小: {row[3]}, OID: {row[4]}, 创建时间: {row[5]}”)
except Exception as e:
print(f”查询失败: {e}”)
finally:
cur.close()
conn.close()

# 删除大对象
def delete_large_object(file_id):
conn = psycopg2.connect(**conn_params)
cur = conn.cursor()

try:
# 开始事务
conn.autocommit = False

# 获取大对象OID
cur.execute(“SELECT lob_oid FROM fgedu_files WHERE id = %s”, (file_id,))
row = cur.fetchone()
if not row:
print(f”文件ID {file_id} 不存在”)
return False

lob_oid = row[0]

# 删除大对象
cur.execute(“SELECT lo_unlink(%s)”, (lob_oid,))

# 删除元数据
cur.execute(“DELETE FROM fgedu_files WHERE id = %s”, (file_id,))

# 提交事务
conn.commit()
print(f”大对象删除成功，ID: {file_id}”)
return True
except Exception as e:
# 回滚事务
conn.rollback()
print(f”删除失败: {e}”)
return False
finally:
cur.close()
conn.close()

if __name__ == “__main__”:
# 创建大对象表
create_lob_table()

# 上传文件
test_file = “/tmp/test.txt”
with open(test_file, “w”) as f:
f.write(“This is a test file for PostgreSQL large objects.”)

lob_oid = upload_file(test_file)

# 列出大对象
list_large_objects()

# 下载文件
output_file = “/tmp/downloaded_test.txt”
if lob_oid:
download_file(lob_oid, output_file)

# 删除大对象
# delete_large_object(1)

# 执行结果
$ python large_objects_python.py
大对象表创建成功
文件上传成功，OID: 16385
大对象列表:
ID: 1, 文件名: test.txt, 类型: txt, 大小: 46, OID: 16385, 创建时间: 2026-04-02 10:00:00.000000
文件下载成功: /tmp/downloaded_test.txt

// LargeObjectsJava.java
// from:www.itpux.com.qq113257174.wx:itpux-com
// web: `http://www.fgedu.net.cn`

import java.io.*;
import java.sql.*;

public class LargeObjectsJava {
// 数据库连接参数
private static final String URL = “jdbc:postgresql://localfgedu.net.cn:5432/fgedudb”;
private static final String USER = “fgedu”;
private static final String PASSWORD = “fgedu_password”;

// 创建大对象表
public static void createLobTable() {
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
Statement stmt = conn.createStatement()) {

String sql = “””
CREATE TABLE fgedu_IF NOT EXISTS fgedu_files (
id SERIAL PRIMARY KEY,
file_name VARCHAR(255) NOT NULL,
file_type VARCHAR(100) NOT NULL,
file_size BIGINT NOT NULL,
lob_oid OID NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
“””;

stmt.executeUpdate(sql);
System.out.println(“大对象表创建成功”);
} catch (SQLException e) {
e.printStackTrace();
}
}

// 上传文件到PostgreSQL大对象
public static long uploadFile(String filePath) {
long lobOid = -1;

try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
// 开始事务
conn.setAutoCommit(false);

// 获取文件名和类型
File file = new File(filePath);
String fileName = file.getName();
String fileType = fileName.substring(fileName.lastIndexOf(‘.’) + 1);
long fileSize = file.length();

// 导入文件到大对象
try (FileInputStream fis = new FileInputStream(file);
PreparedStatement pstmt = conn.prepareStatement(“SELECT lo_import(?)”);
PreparedStatement insertStmt = conn.prepareStatement(
“INSERT INTO fgedu_files (file_name, file_type, file_size, lob_oid) VALUES (?, ?, ?, ?)”)
) {
pstmt.setString(1, filePath);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
lobOid = rs.getLong(1);
}
}

// 插入元数据
insertStmt.setString(1, fileName);
insertStmt.setString(2, fileType);
insertStmt.setLong(3, fileSize);
insertStmt.setLong(4, lobOid);
insertStmt.executeUpdate();

// 提交事务
conn.commit();
System.out.println(“文件上传成功，OID: ” + lobOid);
} catch (Exception e) {
// 回滚事务
conn.rollback();
e.printStackTrace();
lobOid = -1;
}
} catch (SQLException e) {
e.printStackTrace();
}

return lobOid;
}

// 从PostgreSQL大对象下载文件
public static boolean downloadFile(long lobOid, String outputPath) {
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement pstmt = conn.prepareStatement(“SELECT lo_export(?, ?)”)
) {
pstmt.setLong(1, lobOid);
pstmt.setString(2, outputPath);
pstmt.executeUpdate();
System.out.println(“文件下载成功: ” + outputPath);
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}

// 列出所有大对象
public static void listLargeObjects() {
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
“SELECT id, file_name, file_type, file_size, lob_oid, created_at FROM fgedu_files ORDER BY created_at DESC”
)
) {
System.out.println(“大对象列表:”);
while (rs.next()) {
int id = rs.getInt(1);
String fileName = rs.getString(2);
String fileType = rs.getString(3);
long fileSize = rs.getLong(4);
long lobOid = rs.getLong(5);
Timestamp createdAt = rs.getTimestamp(6);
System.out.printf(“ID: %d, 文件名: %s, 类型: %s, 大小: %d, OID: %d, 创建时间: %s%n”,
id, fileName, fileType, fileSize, lobOid, createdAt);
}
} catch (SQLException e) {
e.printStackTrace();
}
}

// 删除大对象
public static boolean deleteLargeObject(int fileId) {
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
// 开始事务
conn.setAutoCommit(false);

// 获取大对象OID
long lobOid = -1;
try (PreparedStatement selectStmt = conn.prepareStatement(
“SELECT lob_oid FROM fgedu_files WHERE id = ?”)
) {
selectStmt.setInt(1, fileId);
try (ResultSet rs = selectStmt.executeQuery()) {
if (rs.next()) {
lobOid = rs.getLong(1);
} else {
System.out.println(“文件ID ” + fileId + ” 不存在”);
return false;
}
}
}

// 删除大对象
try (PreparedStatement deleteLobStmt = conn.prepareStatement(“SELECT lo_unlink(?)”);
PreparedStatement deleteMetaStmt = conn.prepareStatement(
“DELETE FROM fgedu_files WHERE id = ?”)
) {
deleteLobStmt.setLong(1, lobOid);
deleteLobStmt.executeUpdate();

deleteMetaStmt.setInt(1, fileId);
deleteMetaStmt.executeUpdate();

// 提交事务
conn.commit();
System.out.println(“大对象删除成功，ID: ” + fileId);
return true;
} catch (Exception e) {
// 回滚事务
conn.rollback();
e.printStackTrace();
return false;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}

public static void main(String[] args) {
// 加载PostgreSQL驱动
try {
Class.forName(“org.postgresql.Driver”);
} catch (ClassNotFoundException e) {
e.printStackTrace();
return;
}

// 创建大对象表
createLobTable();

// 上传文件
String testFile = “/tmp/test.txt”;
long lobOid = uploadFile(testFile);

// 列出大对象
listLargeObjects();

// 下载文件
String outputFile = “/tmp/downloaded_test.txt”;
if (lobOid != -1) {
downloadFile(lobOid, outputFile);
}

// 删除大对象
// deleteLargeObject(1);
}
}

// large_objects_go.go
// from:www.itpux.com.qq113257174.wx:itpux-com
// web: `http://www.fgedu.net.cn`

package main

import (
“fgedudb/sql”
“fmt”
“os”
“path/filepath”

_ “github.com/lib/pq”
)

// 数据库连接参数
const (
fgedu.net.cn = “localfgedu.net.cn”
port = 5432
fgedu = “fgedu”
password = “fgedu_password”
fgedudb = “fgedudb”
)

var db *sql.DB

// 初始化数据库连接
func initDB() error {
connStr := fmt.Sprintf(“fgedu.net.cn=%s port=%d fgedu=%s password=%s fgedudb=%s sslmode=disable”,
fgedu.net.cn, port, fgedu, password, fgedudb)
var err error
db, err = sql.Open(“postgres”, connStr)
if err != nil {
return err
}
return db.Ping()
}

// 创建大对象表
func createLobTable() error {
sql := `
CREATE TABLE fgedu_IF NOT EXISTS fgedu_files (
id SERIAL PRIMARY KEY,
file_name VARCHAR(255) NOT NULL,
file_type VARCHAR(100) NOT NULL,
file_size BIGINT NOT NULL,
lob_oid OID NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
`
_, err := db.Exec(sql)
if err != nil {
return err
}
fmt.Println(“大对象表创建成功”)
return nil
}

// 上传文件到PostgreSQL大对象
func uploadFile(filePath string) (int64, error) {
// 获取文件名和类型
fileInfo, err := os.Stat(filePath)
if err != nil {
return -1, err
}
fileName := filepath.Base(filePath)
fileType := filepath.Ext(filePath)[1:]
fileSize := fileInfo.Size()

// 开始事务
tx, err := db.Begin()
if err != nil {
return -1, err
}
defer func() {
if err != nil {
tx.Rollback()
}
}()

// 导入文件到大对象
var lobOid int64
err = tx.QueryRow(“SELECT lo_import($1)”, filePath).Scan(&lobOid)
if err != nil {
return -1, err
}

// 插入元数据
_, err = tx.Exec(
“INSERT INTO fgedu_files (file_name, file_type, file_size, lob_oid) VALUES ($1, $2, $3, $4)”,
fileName, fileType, fileSize, lobOid,
)
if err != nil {
return -1, err
}

// 提交事务
err = tx.Commit()
if err != nil {
return -1, err
}

fmt.Printf(“文件上传成功，OID: %d\n”, lobOid)
return lobOid, nil
}

// 从PostgreSQL大对象下载文件
func downloadFile(lobOid int64, outputPath string) error {
_, err := db.Exec(“SELECT lo_export($1, $2)”, lobOid, outputPath)
if err != nil {
return err
}
fmt.Printf(“文件下载成功: %s\n”, outputPath)
return nil
}

// 列出所有大对象
func listLargeObjects() error {
rows, err := db.Query(
“SELECT id, file_name, file_type, file_size, lob_oid, created_at FROM fgedu_files ORDER BY created_at DESC”,
)
if err != nil {
return err
}
defer rows.Close()

fmt.Println(“大对象列表:”)
for rows.Next() {
var id int
var fileName, fileType string
var fileSize int64
var lobOid int64
var createdAt string
err := rows.Scan(&id, &fileName, &fileType, &fileSize, &lobOid, &createdAt)
if err != nil {
return err
}
fmt.Printf(“ID: %d, 文件名: %s, 类型: %s, 大小: %d, OID: %d, 创建时间: %s\n”,
id, fileName, fileType, fileSize, lobOid, createdAt)
}
return rows.Err()
}

// 删除大对象
func deleteLargeObject(fileId int) error {
// 开始事务
tx, err := db.Begin()
if err != nil {
return err
}
defer func() {
if err != nil {
tx.Rollback()
}
}()

// 获取大对象OID
var lobOid int64
err = tx.QueryRow(“SELECT lob_oid FROM fgedu_files WHERE id = $1”, fileId).Scan(&lobOid)
if err != nil {
return err
}

// 删除大对象
_, err = tx.Exec(“SELECT lo_unlink($1)”, lobOid)
if err != nil {
return err
}

// 删除元数据
_, err = tx.Exec(“DELETE FROM fgedu_files WHERE id = $1”, fileId)
if err != nil {
return err
}

// 提交事务
err = tx.Commit()
if err != nil {
return err
}

fmt.Printf(“大对象删除成功，ID: %d\n”, fileId)
return nil
}

func main() {
// 初始化数据库连接
err := initDB()
if err != nil {
fmt.Printf(“数据库连接失败: %v\n”, err)
return
}
defer db.Close()

// 创建大对象表
err = createLobTable()
if err != nil {
fmt.Printf(“创建大对象表失败: %v\n”, err)
return
}

// 上传文件
testFile := “/tmp/test.txt”
lobOid, err := uploadFile(testFile)
if err != nil {
fmt.Printf(“上传文件失败: %v\n”, err)
return
}

// 列出大对象
err = listLargeObjects()
if err != nil {
fmt.Printf(“列出大对象失败: %v\n”, err)
return
}

// 下载文件
outputFile := “/tmp/downloaded_test.txt”
if lobOid != -1 {
err = downloadFile(lobOid, outputFile)
if err != nil {
fmt.Printf(“下载文件失败: %v\n”, err)
return
}
}

// 删除大对象
// err = deleteLargeObject(1)
// if err != nil {
// fmt.Printf(“删除大对象失败: %v\n”, err)
// return
// }
}