lo

The lo module provides support for managing large objects (also known as LOs or BLOBs). This includes a data type lo and a trigger lo_manage.

This module is considered "trusted", meaning it can be installed by non-superusers who have CREATE privilege on the current database.

1. Rationale

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

However, this does not happen with Halo. Large objects are treated as autonomous objects. A table row can reference a large object via its OID, and multiple table rows can reference the same large object OID, so the system will not delete the large object when you change or delete such a row.

Now this works fine for Halo-native applications, but standard code using JDBC or ODBC will not delete those objects, resulting in orphaned objects — objects that are not referenced by anything and that waste disk space.

lo allows you to fix this problem by attaching a trigger to the table containing the LO reference column. The trigger essentially just calls lo_unlink when you delete or modify a value that references a large object. When you use this trigger, you must assume that there is only one database reference to any given large object in a trigger-controlled column!

This module also provides a data type lo, which is actually just a domain over the oid type. This helps distinguish database columns that hold large object references from columns that hold OIDs of other things. You do not have to use the lo type to use the trigger, but it is very convenient for tracking which columns in the database represent large objects that you want managed by the trigger. It has also been reported that the ODBC driver gets confused if you do not use lo for BLOB columns.

2. How to Use It

Here is a simple usage example:

test=## CREATE TABLE image (title text, raster lo);

CREATE TABLE

test=## CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image

test-## FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);

CREATE TRIGGER

For each column that will contain unique references to large objects, create a BEFORE UPDATE OR DELETE trigger, and pass the column name as the sole trigger parameter. You can also use BEFORE UPDATE OF column_name to restrict the trigger to fire only on update events for that column. If you need multiple lo columns in the same table, create a separate trigger for each one, remembering to specify a different name for each trigger on the same table.

3. Limitations

• Dropping a table will still leave any objects it contains as orphans, because the trigger will not be executed in this case. You can avoid this problem by running DELETE FROM table before DROP TABLE.

TRUNCATE has the same hazard.

It is a good idea to occasionally run vacuumlo as a backup to the lo_manage trigger.

• Some frontends may create their own tables and will not create the associated triggers. Also, users may not remember (or know) to create the triggers.