PostgreSQL database back-end for JBoss ON accumulates orphan BLOB entries in its pg_largeobject table each time content is uploaded

Environment

• Red Hat JBoss Operations Network (ON) 3.3
• PostgreSQL 8.4, 9.0, 9.1, 9.2, 9.3, 9.4, 9.5
• Creation of new content backed child resource such as WAR, EAR, Deployment, DomainDeployment, etc.

Issue

• New deployment results in duplicate blobs being stored in PostgreSQL database
• Deployment or Domain Deployment content gets stored in pg_largeobject table twice at upload

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 uploaded, it is temporarily written to the JBoss ON back-end database. The next step of the upload process is for the user or system to fill in details about the uploaded content such as version information, run-time names, etc. Once the additional information is provided or discovered, the content entry in the database is updated. For efficiency, a new row is created in the database resulting in the binary content being stored by PostgreSQL being duplicated.

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

Diagnostic Steps

• The following illustrates this issue when deploying a WAR. The upload action during the deploy wizard results in the following data being added to the PostgreSQL database:

  • SQL: SELECT id,display_name,file_name,file_size,package_id,package_bits_id FROM rhq_package_version
    | id | display_name | file_name | file_size | package_id | package_bits_id |
    | ----- | ----------------------- | ----------------------- | --------- | ---------- | --------------- |
    | 10011 | jboss-as-helloworld.war | jboss-as-helloworld.war | 7606 | 10001 | 10011 |

  • SQL: SELECT * FROM rhq_package_bits
    | id | bits |
    | ----- | ----- |
    | 10011 | 19054 |

  • SQL: SELECT * FROM pg_largeobject
    | loid | pageno | data |
    | ----- | ------ | --------------------------------------- |
    | 19053 | 0 | 50 4b 03 04 0a 00 00 00 00 ... |
    | 19053 | 1 | 03 33 e4 1a 70 7a 00 7c 88 ... |
    | 19053 | 2 | 66 4d f5 a5 5a 05 b1 13 d0 ... |
    | 19053 | 3 | 41 2d 49 4e 46 2f 4d 41 4e ... |
    | 19054 | 0 | 50 4b 03 04 0a 00 00 00 00 ... |
    | 19054 | 1 | 03 33 e4 1a 70 7a 00 7c 88 ... |
    | 19054 | 2 | 66 4d f5 a5 5a 05 b1 13 d0 ... |
    | 19054 | 3 | 41 2d 49 4e 46 2f 4d 41 4e ... |

  Note that the loid of 19053 is not referenced in rhq_package_bits. Also notice that it is identical to loid 19054 which is referenced in rhq_package_bits.

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.