PostgreSQL database back-end for JBoss ON includes orphan BLOB entries in its pg_largeobject table after content backed resources are removed from inventory

Solution Unverified - Updated

Environment

  • Red Hat JBoss Operations Network (ON) 3.3
  • PostgreSQL 8.4, 9.0, 9.1, 9.2, 9.3, 9.4, 9.5
  • Remove existing resource backed by content or its parent or grandparent resource using the Uninventory child inventory operation

Issue

  • Uninventory of resource leaves behind binary large object (BLOB) entries in PostgreSQL database
  • Number of rows in pg_largeobject table does not decrease when resource is removed from inventory using Uninventory operation

Resolution

Use the PostgreSQL vacuumlo utility to remove the orphaned large object (LOB) entries from the JBoss ON back-end database.

vacuumlo -U rhqadmin -W -v rhq

The above command assumes that you have installed the Content from www.postgresql.org is not included.vacuumlo PostgreSQL Additional Supplied Modules provided by the postgresql-contrib package. If the database is not on the local host, you will also need to provide the -h and -p arguments.

Root Cause

When content is deleted as part of the Uninventory request, its entry in the back-end JBoss ON database is removed using a SQL DELETE query. However, PostgreSQL stores large object (LOB) data in its system table pg_largeobject and only stores a reference to that data in the actual JBoss ON tables.

This issue has been captured in This content is not included.Red Hat Bugzilla 1381720 and will be considered for inclusion in a future release of JBoss ON.

Diagnostic Steps

  • The following SQL query can be used to determine whether there are orphaned pg_largeobject table entries:

      SELECT DISTINCT loid FROM pg_largeobject WHERE NOT EXISTS (SELECT bits FROM rhq_package_bits WHERE pg_largeobject.loid = rhq_package_bits.bits)

    If any rows are returned, this issue may apply.

SBR
Product(s)
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.