PostgreSQL数据库大小

触发器函数，用于处理包含大对象引用的表列的删除和更新。——如果行被删除或更新，旧的引用将从pg_largeobject表中删除。创建或替换函数rmserver_largeobject_cleanup_trgfunc()返回触发器作为$func$ begin——更新触发器有NEW和OLD，如果TG_OP = ' Update '那么——根据表，如果引用改变，取消链接(删除)旧的大对象。如果TG_TABLE_NAME = 'ra_ent_processexecparam'，则如果新建。Processcontext <> old。然后执行lo_unlink(old.processcontext);如果;elseif TG_TABLE_NAME = 'ra_ent_bytebuffer' then if new。Buffer <> old。然后执行lo_unlink(old.buffer);如果;elseif TG_TABLE_NAME = 'ra_ent_jobsubmissionfailure' then if new。进程<> old。process then perform lo_unlink(old.process::bigint); end if; elseif TG_TABLE_NAME = 'ra_ent_jobsubmissionfailure' then if new.message <> old.message then perform lo_unlink(old.message::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job_error' then if new.message <> old.message then perform lo_unlink(old.message::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job_error' then if new.trace <> old.trace then perform lo_unlink(old.trace::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job_error' then if new.explanation <> old.explanation then perform lo_unlink(old.explanation::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job_log' then if coalesce(new.log, '') <> old.log then perform lo_unlink(old.log::bigint); end if; elseif TG_TABLE_NAME = 'jobservice_job' then if new.process <> old.process then perform lo_unlink(old.process::bigint); end if; end if; return NEW; elseif TG_OP = 'DELETE' then -- Depending on the table, unlink (delete) the referenced large object. if TG_TABLE_NAME = 'ra_ent_processexecparam' then perform lo_unlink(old.processcontext); elseif TG_TABLE_NAME = 'ra_ent_bytebuffer' then perform lo_unlink(old.buffer); elseif TG_TABLE_NAME = 'ra_ent_jobsubmissionfailure' then perform lo_unlink(old.process::bigint); elseif TG_TABLE_NAME = 'ra_ent_jobsubmissionfailure' then perform lo_unlink(old.message::bigint); elseif TG_TABLE_NAME = 'jobservice_job_error' then perform lo_unlink(old.message::bigint); elseif TG_TABLE_NAME = 'jobservice_job_error' then perform lo_unlink(old.trace::bigint); elseif TG_TABLE_NAME = 'jobservice_job_error' then perform lo_unlink(old.explanation::bigint); elseif TG_TABLE_NAME = 'jobservice_job_log' then perform lo_unlink(old.log::bigint); elseif TG_TABLE_NAME = 'jobservice_job' then perform lo_unlink(old.process::bigint); end if; return OLD; end if; end; $func$ language plpgsql volatile; drop trigger if exists ra_ent_processexecparam_lob_trg on ra_ent_processexecparam; create trigger ra_ent_processexecparam_lob_trg after update or delete on ra_ent_processexecparam for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists ra_ent_bytebuffer_lob_trg on ra_ent_bytebuffer; create trigger ra_ent_bytebuffer_lob_trg after update or delete on ra_ent_bytebuffer for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists ra_ent_jobsubmissionfailure_lob_trg on ra_ent_jobsubmissionfailure; create trigger ra_ent_jobsubmissionfailure_lob_trg after update or delete on ra_ent_jobsubmissionfailure for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists jobservice_job_error_lob_trg on jobservice_job_error; create trigger jobservice_job_error_lob_trg after update or delete on jobservice_job_error for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists jobservice_job_log_lob_trg on jobservice_job_log; create trigger jobservice_job_log_lob_trg after update or delete on jobservice_job_log for each row execute procedure rmserver_largeobject_cleanup_trgfunc(); drop trigger if exists jobservice_job_lob_trg on jobservice_job; create trigger jobservice_job_lob_trg after update or delete on jobservice_job for each row execute procedure rmserver_largeobject_cleanup_trgfunc();

其中oid为(select processcontext as loboid from ra_ent_processexecparam union from ra_ent_bytebuffer)， text为(select process as loboidt from ra_ent_jobsubmissionfailure union from ra_ent_jobsubmissionfailure union from jobservice_job_error union from jobservice_job_error union from trace from jobservice_job_log union from jobservice_job_error union from log union from jobservice_job_job)，Existing_oids as (select loboid from oid where loboid not null)， nonexisting_oids as (select loboidt from text where loboidt not null)。Oid作为nexoid从pg_largeobject_metadata lom左外连接existing_oids eo on eo。Loboid = lom。我在哪里?loboid是null)， stats as (select 'Existing large objects'::text作为category, count(loboid)作为objectcount, pg_size_pretty(sum(length(lo_get(lom.oid)))))作为size from existing_oids eo内部连接pg_largeobject_metadata lom on lom)。Oid = eo。Loboid union select 'pg_largeobject表'作为category, count(oid)， pg_size_pretty(pg_relation_size('pg_largeobject')) from pg_largeobject_metadata lom) select * from stats;

其中oid为(select processcontext as loboid from ra_ent_processexecparam union from ra_ent_bytebuffer)， text为(select process as loboidt from ra_ent_jobsubmissionfailure union from ra_ent_jobsubmissionfailure union from jobservice_job_error union from jobservice_job_error union from trace from jobservice_job_log union from jobservice_job_error union from log union from jobservice_job_job)，Existing_oids as (select loboid from oid where loboid not null)， nonexisting_oids as (select loboidt from text where loboidt not null)。Oid作为nexoid从pg_largeobject_metadata lom左外连接existing_oids eo on eo。Loboid = lom。我在哪里?从nonexisting_oid中选择nexoid为invalid_oid, lo_unlink(nexoid为null);

VACUUM FULL pg_largeobject;

如果ra_ent_processexecparam上存在ra_ent_processexecparam_lob_trg，则删除触发器;如果ra_ent_bytebuffer上存在ra_ent_bytebuffer_lob_trg，则删除触发器;如果ra_ent_jobsubmissionfailure上存在ra_ent_jobsubmissionfailure_lob_trg，删除触发器;如果jobservice_job_error上存在jobservice_job_error_lob_trg，删除触发器;如果jobservice_job_log_lob_trg在jobservice_job_log上存在，删除触发器;如果jobservice_job上存在jobservice_job_lob_trg，删除触发器;删除rmserver_largeobject_cleanup_trgfunc();