— 1. 创建角色
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = ‘fgedu_lo_admin’) THEN
CREATE ROLE fgedu_lo_admin;
END IF;

IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = ‘fgedu_lo_reader’) THEN
CREATE ROLE fgedu_lo_reader;
END IF;

IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = ‘fgedu_lo_writer’) THEN
CREATE ROLE fgedu_lo_writer;
END IF;
END $$;

— 2. 配置权限
GRANT SELECT, UPDATE ON pg_largeobject TO fgedu_lo_admin;
GRANT SELECT, UPDATE ON pg_largeobject_metadata TO fgedu_lo_admin;

GRANT SELECT ON pg_largeobject TO fgedu_lo_reader;
GRANT SELECT ON pg_largeobject_metadata TO fgedu_lo_reader;

GRANT SELECT, UPDATE ON pg_largeobject TO fgedu_lo_writer;
GRANT SELECT, UPDATE ON pg_largeobject_metadata TO fgedu_lo_writer;

— 3. 限制公共访问
REVOKE ALL ON pg_largeobject FROM PUBLIC;
REVOKE ALL ON pg_largeobject_metadata FROM PUBLIC;

— 4. 创建权限管理函数
CREATE OR REPLACE FUNCTION grant_lo_permission(
p_loid OID,
p_grantee TEXT,
p_privilege TEXT
) RETURNS BOOLEAN AS $$
BEGIN
— 检查当前用户是否有权限授予
IF NOT (
SELECT rolsuper FROM pg_roles WHERE rolname = current_fgedu
) AND NOT EXISTS (
SELECT 1 FROM pg_largeobject_metadata
WHERE oid = p_loid AND lomowner = (SELECT oid FROM pg_roles WHERE rolname = current_fgedu)
) THEN
RAISE EXCEPTION ‘没有权限授予此大对象的权限’;
END IF;

— 记录权限授予
INSERT INTO fgedu_lo_permissions (loid, grantee, privilege_type, granted_by)
VALUES (p_loid, p_grantee, p_privilege, current_fgedu);

RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

— 5. 创建权限撤销函数
CREATE OR REPLACE FUNCTION revoke_lo_permission(
p_loid OID,
p_grantee TEXT,
p_privilege TEXT
) RETURNS BOOLEAN AS $$
BEGIN
DELETE FROM fgedu_lo_permissions
WHERE loid = p_loid
AND grantee = p_grantee
AND privilege_type = p_privilege;

RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

— 1. 清理孤立大对象
CREATE OR REPLACE FUNCTION cleanup_orphaned_lobs()
RETURNS TABLE (deleted_count INTEGER, freed_space BIGINT) AS $$
DECLARE
v_count INTEGER := 0;
v_space BIGINT := 0;
rec RECORD;
BEGIN
FOR rec IN
SELECT lom.oid, lom.len
FROM pg_largeobject_metadata lom
LEFT JOIN fgedu_documents d ON lom.oid = d.file_data
WHERE d.id IS NULL
LOOP
PERFORM lo_unlink(rec.oid);
v_count := v_count + 1;
v_space := v_space + rec.len;
END LOOP;

RETURN QUERY SELECT v_count, v_space;
END;
$$ LANGUAGE plpgsql;

— 2. 压缩大对象（示例：使用pgcrypto加密压缩）
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION compress_large_object(
p_loid OID
) RETURNS OID AS $$
DECLARE
v_new_oid OID;
v_data BYTEA;
BEGIN
— 读取大对象数据
SELECT lo_get(p_loid) INTO v_data;

— 压缩数据（这里使用简单的示例，实际可以使用更高效的压缩算法）
— v_data := compress(v_data);

— 创建新的大对象
v_new_oid := lo_create(0);
PERFORM lo_put(v_new_oid, 0, v_data);

RETURN v_new_oid;
END;
$$ LANGUAGE plpgsql;

— 3. 大对象统计信息收集
CREATE OR REPLACE FUNCTION analyze_lobs()
RETURNS TABLE (
total_count BIGINT,
total_size BIGINT,
avg_size NUMERIC,
max_size BIGINT,
orphaned_count BIGINT,
orphaned_size BIGINT
) AS $$
BEGIN
RETURN QUERY
SELECT
(SELECT COUNT(*) FROM pg_largeobject_metadata),
(SELECT COALESCE(SUM(len), 0) FROM pg_largeobject_metadata),
(SELECT COALESCE(AVG(len), 0) FROM pg_largeobject_metadata),
(SELECT COALESCE(MAX(len), 0) FROM pg_largeobject_metadata),
(SELECT COUNT(*) FROM pg_largeobject_metadata lom
LEFT JOIN fgedu_documents d ON lom.oid = d.file_data
WHERE d.id IS NULL),
(SELECT COALESCE(SUM(lom.len), 0) FROM pg_largeobject_metadata lom
LEFT JOIN fgedu_documents d ON lom.oid = d.file_data
WHERE d.id IS NULL);
END;
$$ LANGUAGE plpgsql;

— 1. 创建监控表
CREATE TABLE fgedu_IF NOT EXISTS fgedu_lo_monitoring (
id SERIAL PRIMARY KEY,
check_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_count BIGINT,
total_size BIGINT,
orphaned_count BIGINT,
orphaned_size BIGINT,
avg_size NUMERIC,
max_size BIGINT
);

— 2. 创建监控函数
CREATE OR REPLACE FUNCTION monitor_lobs()
RETURNS VOID AS $$
BEGIN
INSERT INTO fgedu_lo_monitoring (
total_count, total_size, orphaned_count, orphaned_size, avg_size, max_size
)
SELECT
(SELECT COUNT(*) FROM pg_largeobject_metadata),
(SELECT COALESCE(SUM(len), 0) FROM pg_largeobject_metadata),
(SELECT COUNT(*) FROM pg_largeobject_metadata lom
LEFT JOIN fgedu_documents d ON lom.oid = d.file_data
WHERE d.id IS NULL),
(SELECT COALESCE(SUM(lom.len), 0) FROM pg_largeobject_metadata lom
LEFT JOIN fgedu_documents d ON lom.oid = d.file_data
WHERE d.id IS NULL),
(SELECT COALESCE(AVG(len), 0) FROM pg_largeobject_metadata),
(SELECT COALESCE(MAX(len), 0) FROM pg_largeobject_metadata);
END;
$$ LANGUAGE plpgsql;

— 3. 创建监控视图
CREATE OR REPLACE VIEW fgedu_lo_monitoring_view AS
SELECT
check_time,
total_count,
pg_size_pretty(total_size) as total_size,
orphaned_count,
pg_size_pretty(orphaned_size) as orphaned_size,
pg_size_pretty(avg_size::BIGINT) as avg_size,
pg_size_pretty(max_size) as max_size,
CASE
WHEN total_size > 0 THEN ROUND(orphaned_size::NUMERIC / total_size * 100, 2)
ELSE 0
END as orphaned_percentage
FROM fgedu_lo_monitoring
ORDER BY check_time DESC;

— 4. 查询监控数据
SELECT * FROM fgedu_lo_monitoring_view LIMIT 10;