postgresql - create triggers for cleaning large objects (CLOB) of TEXT type when update / remove

[Bang Pham Huu]

The issue comes from this document
​https://www.postgresql.org/docs/9.1/lo.html

And the fix in petascope to avoid leftover objects in database: ​https://projects.rasdaman.com/wiki/PetascopeDB#Hibernate

One of the problems with the JDBC driver
(and this affects the ODBC driver also),
is that the specification assumes that references to BLOBs
(Binary Large OBjects) are stored within a table,
and if that entry is changed, the associated BLOB is deleted from the
database.

As PostgreSQL stands, this doesn't occur.
Large objects are treated as objects in their own right;
a table entry can reference a large object by OID,
but there can be multiple table entries referencing
the same large object OID, so the system doesn't delete the large
object just because you change or remove one such entry.

Now this is fine for PostgreSQL-specific applications,
but standard code using JDBC or ODBC won't delete the objects,
resulting in orphan objects — objects that are not referenced by
anything, and simply occupy disk space.

It creates a big problem for back up / restore petascope as it takes very
long time for these tasks.

There is a solution for that, which needs to create triggers manually on
the tables which has CLOB fields. For example:

CREATE TABLE image (title TEXT, raster lo);

CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
    FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);