# 1. 数据库设计
CREATE TABLE fgedu_documents (
id SERIAL PRIMARY KEY,
document_name VARCHAR(255) NOT NULL,
document_type VARCHAR(100) NOT NULL,
document_size BIGINT NOT NULL,
lo_oid OID NOT NULL,
author VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 2. 上传文档函数
CREATE OR REPLACE FUNCTION upload_document(
p_name VARCHAR(255),
p_type VARCHAR(100),
p_size BIGINT,
p_data BYTEA,
p_author VARCHAR(100)
) RETURNS INTEGER AS $$
DECLARE
v_oid OID;
v_id INTEGER;
BEGIN
— 创建大对象
v_oid := lo_from_bytea(0, p_data);

— 插入文档记录
INSERT INTO fgedu_documents (
document_name, document_type, document_size, lo_oid, author
) VALUES (
p_name, p_type, p_size, v_oid, p_author
) RETURNING id INTO v_id;

RETURN v_id;
END;
$$ LANGUAGE plpgsql;

# 3. 下载文档函数
CREATE OR REPLACE FUNCTION download_document(p_id INTEGER) RETURNS BYTEA AS $$
DECLARE
v_oid OID;
v_data BYTEA;
BEGIN
— 获取大对象OID
SELECT lo_oid INTO v_oid FROM fgedu_documents WHERE id = p_id;

— 读取大对象数据
v_data := lo_get(v_oid);

RETURN v_data;
END;
$$ LANGUAGE plpgsql;

# 4. 删除文档函数
CREATE OR REPLACE FUNCTION delete_document(p_id INTEGER) RETURNS BOOLEAN AS $$
DECLARE
v_oid OID;
BEGIN
— 获取大对象OID
SELECT lo_oid INTO v_oid FROM fgedu_documents WHERE id = p_id;

— 删除文档记录
DELETE FROM fgedu_documents WHERE id = p_id;

— 删除大对象
PERFORM lo_unlink(v_oid);

RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

# 5. 使用示例
— 上传文档
SELECT upload_document(‘Annual Report.pdf’, ‘fgapplication/pdf’, 2097152, pg_read_binary_file(‘/path/to/report.pdf’), ‘admin’);

— 下载文档
SELECT download_document(1);

— 删除文档
SELECT delete_document(1);

# 1. 数据库设计
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fgedu_product_images (
id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES fgedu_products(id) ON DELETE CASCADE,
image_name VARCHAR(255) NOT NULL,
image_type VARCHAR(50) NOT NULL,
image_size BIGINT NOT NULL,
lo_oid OID NOT NULL,
is_primary BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 2. 插入产品图片
— 插入产品
INSERT INTO fgedu_products (product_name, description, price)
VALUES (‘Smartphone’, ‘Latest model’, 999.99);

— 插入产品图片
INSERT INTO fgedu_product_images (product_id, image_name, image_type, image_size, lo_oid, is_primary)
VALUES (1, ‘front.jpg’, ‘image/jpeg’, 1048576, lo_import(‘/path/to/front.jpg’), TRUE);

INSERT INTO fgedu_product_images (product_id, image_name, image_type, image_size, lo_oid)
VALUES (1, ‘back.jpg’, ‘image/jpeg’, 1048576, lo_import(‘/path/to/back.jpg’));

# 3. 获取产品图片
— 获取产品的主图片
SELECT lo_get(lo_oid) AS image_data
FROM fgedu_product_images
WHERE product_id = 1 AND is_primary = TRUE;

— 获取产品的所有图片
SELECT image_name, image_type, lo_get(lo_oid) AS image_data
FROM fgedu_product_images
WHERE product_id = 1;

# 4. 清理产品图片
— 删除产品（级联删除相关图片）
DELETE FROM fgedu_products WHERE id = 1;

— 注意：需要手动删除大对象
CREATE OR REPLACE FUNCTION cleanup_product_images()
RETURNS TRIGGER AS $$
BEGIN
— 删除关联的大对象
FOR i IN SELECT lo_oid FROM fgedu_product_images WHERE product_id = OLD.id LOOP
PERFORM lo_unlink(i.lo_oid);
END LOOP;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_delete_product
BEFORE DELETE ON fgedu_products
FOR EACH ROW
EXECUTE FUNCTION cleanup_product_images();

# 1. 使用专用表空间
— 创建大对象专用表空间
CREATE TABLESPACE lo_ts LOCATION ‘/postgresql/large_objects’;

— 将大对象表移动到专用表空间
ALTER TABLE pg_largeobject SET TABLESPACE lo_ts;
ALTER TABLE pg_largeobject_metadata SET TABLESPACE lo_ts;

# 2. 调整块大小
— 查看当前块大小
SHOW block_size;

— 注意：块大小在初始化数据库时设置，无法在运行时修改
— 建议在初始化数据库时根据大对象大小设置合适的块大小

# 3. 批量操作
— 批量导入大对象
CREATE OR REPLACE FUNCTION batch_import_files(file_paths TEXT[])
RETURNS TABLE(oid OID, path TEXT) AS $$
DECLARE
file_path TEXT;
BEGIN
FOREACH file_path IN ARRAY file_paths LOOP
RETURN QUERY SELECT lo_import(file_path), file_path;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 使用示例
SELECT * FROM batch_import_files(ARRAY[‘/path/to/file1.jpg’, ‘/path/to/file2.jpg’]);

# 4. 并行处理
— 使用并行查询处理大对象
ALTER TABLE fgedu_files ADD COLUMN processed BOOLEAN DEFAULT FALSE;

— 并行更新处理状态
UPDATE fgedu_files SET processed = TRUE WHERE id IN (
SELECT id FROM fgedu_files WHERE processed = FALSE ORDER BY id LIMIT 100
);

# 1. 使用连接池
— 配置pgBouncer或其他连接池
— 减少连接建立和关闭的开销

# 2. 缓存策略
— 实现应用级缓存
— 缓存频繁访问的大对象

# 3. 异步操作
— 使用异步I/O
— 避免阻塞主线程

# 4. 分段读取
— 对于大型大对象，使用分段读取
— 减少内存使用

# 5. 索引优化
— 为关联表创建合适的索引
CREATE INDEX idx_fgedu_files_created_at ON fgedu_files(created_at);
CREATE INDEX idx_fgedu_files_lo_oid ON fgedu_files(lo_oid);

# 6. 预读取
— 对于顺序访问的场景，实现预读取
— 提高读取性能

# 2. 大对象不存在
# 错误信息：ERROR: large object 123456 does not exist
# 解决方法：
– 检查大对象OID是否正确
– 确保大对象未被删除
– 验证引用完整性

# 3. 存储空间不足
# 错误信息：ERROR: could not extend file “base/16384/123456”: No space left on device
# 解决方法：
– 检查磁盘空间
– 清理未使用的大对象
– 扩展存储容量

# 4. 大对象操作超时
# 错误信息：ERROR: canceling statement due to statement timeout
# 解决方法：
– 增加statement_timeout参数
– 优化大对象操作
– 考虑使用分段操作

# 5. 大对象数据损坏
# 错误信息：ERROR: invalid large object descriptor
# 解决方法：
– 检查大对象的完整性
– 从备份恢复
– 实现数据验证机制

# 1. 监控大对象数量
SELECT count(*) FROM pg_largeobject_metadata;

# 2. 监控大对象大小
SELECT sum(lo_size(loid)) FROM pg_largeobject_metadata;

# 3. 监控大对象表空间使用
SELECT pg_size_pretty(pg_fgedutbs_size(‘lo_ts’));

# 4. 监控大对象操作
— 启用审计日志
— 监控lo_*函数的使用

# 5. 监控未引用的大对象
SELECT count(*) FROM pg_largeobject_metadata
WHERE loid NOT IN (
SELECT DISTINCT lo_oid FROM fgedu_files
);

# 6. 监控大对象访问性能
— 使用pg_stat_statements监控大对象相关SQL
SELECT query, calls, total_exec_time
FROM pg_stat_statements
WHERE query LIKE ‘%lo_%’
ORDER BY total_exec_time DESC;