PostgreSQL issue when running the VACUUMLO command

March 6, 2023

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