PostgreSQL issue when running the VACUUMLO command
An issue has recently been identified on the Jahia/Digital Factory/Jahia line of products when using *PostgreSQL *database. The issue comes up when using the maintenance and cleanup command "VACUUMLO" on the database side. This action is supposed to free some space inside the database by removing unreferenced objects inside large objects fields table "pg_largeobject".
Because of the way the Jahia/DF/DX database creation schema is designed, it may - in some cases - incorrectly identify some large objects inside the table "
pg_largeobject" as unreferenced, whereas these objects are actually in use. By running the "
VACUUMLO" command, PostgreSQL may delete from the database these large objects if they are identified as not referenced anymore from any of the other tables, even though they actually are in use. This can have an unexpected effect on the internal functioning of Jahia/DF/DX.
In order to run VACUUMLO safely against Jahia/DF schema, an additional step has to be executed before.
Before running the VACUUMLO, you should create a new DB materialized view using the following SQL script:
create materialized view jahia_oid as select externalid::oid as id from jahia_external_mapping union select expression::oid as id from jbpm_boolean_expression union select body::oid as id from jbpm_email_header union select "text"::oid as id from jbpm_i18ntext union select "text"::oid as id from jbpm_task_comment union select entry_path::oid as id from jahia_contenthistory union select cndfile::oid as id from jahia_nodetypes_provider;
This will collect the "OID" values from Jahia tables into a dedicated view.
After that you can run the
VACUUMLO command which will clean up the orphaned large objects.
Also each time before the
VACUUMLO command is executed you should refresh the view:
refresh materialized view jahia_oid