Bug 1381725

Description of problem:
During the process of uploading a new deployment (content backed), the content gets duplicated in PostgreSQL's pg_largeobject table.

This results in unbound file system growth for the PostgreSQL database. There is no method provided via JBoss ON to clean this up or prevent it. The process of simply uploading a file in preparation for a deployment will continue to leak large amount of disk space.


How reproducible:
Always

Steps to Reproduce:
1.  Install, configure, and start JBoss ON 3.3.7 system.
2.  Install, configure, and start EAP 6.4 standalone server.
3.  Import newly discovered EAP server into inventory.
4.  Configure newly imported EAP server's connection settings.
5.  Create new _Deployment_ child resource for EAP server using jboss-helloworld.war.

Actual results:
The following queries against the PostgreSQL database reveals a single entry that corresponds to the deployment of the jboss-helloworld.war.

.SQL: `SELECT * FROM rhq_package_version;`
----
id	display_name	short_description	long_description	version	display_version	file_name	file_size	file_md5	file_sha256	file_creation_time	license_name	license_version	metadata	package_id	architecture_id	config_id	package_bits_id
10011	jboss-as-helloworld.war	<null>	<null>	0	<null>	jboss-as-helloworld.war	7606	<null>	d02323a97678e377e4fdd7c4d9b6d5891f1a29d3ed0df0bc8c5a548f1fdfded3	1473374418000	<null>	<null>	<null>	10001	1	<null>	10011
----


.SQL: `SELECT * FROM rhq_package_bits;`
----
id	bits
10011	19054
----

However, the pg_largeobject table includes two entries with only one being referenced:

.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  ...
----

The content binary data is identical.

Expected results:
The results from rhq_package_version and rhq_package_bits are correct but the results from pg_largeobject should have been:

.SQL: `SELECT * FROM pg_largeobject;`
----
loid	pageno	data
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  ...
----

Additional info:
This issue is specific to how PostgreSQL stores blob data. Instead of storing the binary data in the rhq_package_bits table, Postgres writes the data to the pg_largeobject table and stores the data reference in the rhq_package_bits table. 

In the case of uploading a new deployment, the initial rhq_package_version and rhq_package_bit entries are created. However, once we can properly analyze the uploaded content and complete the create child wizard, the rhq_package_version table is updated. This triggers an update to rhq_package_bits and results in the pg_largeobject orphan. 

The PostgreSQL JDBC driver is not able to determine if the only reference to this data is held by the rhq_package_bits table after the UPDATE is performed.

I believe the fix here is that when the UPDATE query is executed against the rhq_package_version table, an unlink query should also be executed.