1
<!-- doc/src/sgml/lo.sgml -->
11
The <filename>lo</> module provides support for managing Large Objects
12
(also called LOs or BLOBs). This includes a data type <type>lo</>
13
and a trigger <function>lo_manage</>.
17
<title>Rationale</title>
20
One of the problems with the JDBC driver (and this affects the ODBC driver
21
also), is that the specification assumes that references to BLOBs (Binary
22
Large OBjects) are stored within a table, and if that entry is changed, the
23
associated BLOB is deleted from the database.
27
As <productname>PostgreSQL</> stands, this doesn't occur. Large objects
28
are treated as objects in their own right; a table entry can reference a
29
large object by OID, but there can be multiple table entries referencing
30
the same large object OID, so the system doesn't delete the large object
31
just because you change or remove one such entry.
35
Now this is fine for <productname>PostgreSQL</>-specific applications, but
36
standard code using JDBC or ODBC won't delete the objects, resulting in
37
orphan objects — objects that are not referenced by anything, and
38
simply occupy disk space.
42
The <filename>lo</> module allows fixing this by attaching a trigger
43
to tables that contain LO reference columns. The trigger essentially just
44
does a <function>lo_unlink</> whenever you delete or modify a value
45
referencing a large object. When you use this trigger, you are assuming
46
that there is only one database reference to any large object that is
47
referenced in a trigger-controlled column!
51
The module also provides a data type <type>lo</>, which is really just
52
a domain of the <type>oid</> type. This is useful for differentiating
53
database columns that hold large object references from those that are
54
OIDs of other things. You don't have to use the <type>lo</> type to
55
use the trigger, but it may be convenient to use it to keep track of which
56
columns in your database represent large objects that you are managing with
57
the trigger. It is also rumored that the ODBC driver gets confused if you
58
don't use <type>lo</> for BLOB columns.
63
<title>How to Use It</title>
66
Here's a simple example of usage:
70
CREATE TABLE image (title TEXT, raster lo);
72
CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
73
FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);
77
For each column that will contain unique references to large objects,
78
create a <literal>BEFORE UPDATE OR DELETE</> trigger, and give the column
79
name as the sole trigger argument. If you need multiple <type>lo</>
80
columns in the same table, create a separate trigger for each one,
81
remembering to give a different name to each trigger on the same table.
86
<title>Limitations</title>
91
Dropping a table will still orphan any objects it contains, as the trigger
92
is not executed. You can avoid this by preceding the <command>DROP
93
TABLE</> with <command>DELETE FROM <replaceable>table</></command>.
97
<command>TRUNCATE</> has the same hazard.
101
If you already have, or suspect you have, orphaned large objects, see the
102
<xref linkend="vacuumlo"> module to help
103
you clean them up. It's a good idea to run <application>vacuumlo</>
104
occasionally as a back-stop to the <function>lo_manage</> trigger.
110
Some frontends may create their own tables, and will not create the
111
associated trigger(s). Also, users may not remember (or know) to create
119
<title>Author</title>
122
Peter Mount <email>peter@retep.org.uk</email>