| 1 |
<!-- doc/src/sgml/lo.sgml --> |
| 2 |
|
| 3 |
<sect1 id="lo" xreflabel="lo"> |
| 4 |
<title>lo — manage large objects</title> |
| 5 |
|
| 6 |
<indexterm zone="lo"> |
| 7 |
<primary>lo</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<para> |
| 11 |
The <filename>lo</filename> module provides support for managing Large Objects |
| 12 |
(also called LOs or BLOBs). This includes a data type <type>lo</type> |
| 13 |
and a trigger <function>lo_manage</function>. |
| 14 |
</para> |
| 15 |
|
| 16 |
<para> |
| 17 |
This module is considered <quote>trusted</quote>, that is, it can be |
| 18 |
installed by non-superusers who have <literal>CREATE</literal> privilege |
| 19 |
on the current database. |
| 20 |
</para> |
| 21 |
|
| 22 |
<sect2 id="lo-rationale"> |
| 23 |
<title>Rationale</title> |
| 24 |
|
| 25 |
<para> |
| 26 |
One of the problems with the JDBC driver (and this affects the ODBC driver |
| 27 |
also), is that the specification assumes that references to BLOBs (Binary |
| 28 |
Large OBjects) are stored within a table, and if that entry is changed, the |
| 29 |
associated BLOB is deleted from the database. |
| 30 |
</para> |
| 31 |
|
| 32 |
<para> |
| 33 |
As <productname>PostgreSQL</productname> stands, this doesn't occur. Large objects |
| 34 |
are treated as objects in their own right; a table entry can reference a |
| 35 |
large object by OID, but there can be multiple table entries referencing |
| 36 |
the same large object OID, so the system doesn't delete the large object |
| 37 |
just because you change or remove one such entry. |
| 38 |
</para> |
| 39 |
|
| 40 |
<para> |
| 41 |
Now this is fine for <productname>PostgreSQL</productname>-specific applications, but |
| 42 |
standard code using JDBC or ODBC won't delete the objects, resulting in |
| 43 |
orphan objects — objects that are not referenced by anything, and |
| 44 |
simply occupy disk space. |
| 45 |
</para> |
| 46 |
|
| 47 |
<para> |
| 48 |
The <filename>lo</filename> module allows fixing this by attaching a trigger |
| 49 |
to tables that contain LO reference columns. The trigger essentially just |
| 50 |
does a <function>lo_unlink</function> whenever you delete or modify a value |
| 51 |
referencing a large object. When you use this trigger, you are assuming |
| 52 |
that there is only one database reference to any large object that is |
| 53 |
referenced in a trigger-controlled column! |
| 54 |
</para> |
| 55 |
|
| 56 |
<para> |
| 57 |
The module also provides a data type <type>lo</type>, which is really just |
| 58 |
a <glossterm linkend="glossary-domain">domain</glossterm> over |
| 59 |
the <type>oid</type> type. This is useful for differentiating |
| 60 |
database columns that hold large object references from those that are |
| 61 |
OIDs of other things. You don't have to use the <type>lo</type> type to |
| 62 |
use the trigger, but it may be convenient to use it to keep track of which |
| 63 |
columns in your database represent large objects that you are managing with |
| 64 |
the trigger. It is also rumored that the ODBC driver gets confused if you |
| 65 |
don't use <type>lo</type> for BLOB columns. |
| 66 |
</para> |
| 67 |
</sect2> |
| 68 |
|
| 69 |
<sect2 id="lo-how-to-use"> |
| 70 |
<title>How to Use It</title> |
| 71 |
|
| 72 |
<para> |
| 73 |
Here's a simple example of usage: |
| 74 |
</para> |
| 75 |
|
| 76 |
<programlisting> |
| 77 |
CREATE TABLE image (title text, raster lo); |
| 78 |
|
| 79 |
CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image |
| 80 |
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster); |
| 81 |
</programlisting> |
| 82 |
|
| 83 |
<para> |
| 84 |
For each column that will contain unique references to large objects, |
| 85 |
create a <literal>BEFORE UPDATE OR DELETE</literal> trigger, and give the column |
| 86 |
name as the sole trigger argument. You can also restrict the trigger |
| 87 |
to only execute on updates to the column by using <literal>BEFORE UPDATE |
| 88 |
OF</literal> <replaceable class="parameter">column_name</replaceable>. |
| 89 |
If you need multiple <type>lo</type> |
| 90 |
columns in the same table, create a separate trigger for each one, |
| 91 |
remembering to give a different name to each trigger on the same table. |
| 92 |
</para> |
| 93 |
</sect2> |
| 94 |
|
| 95 |
<sect2 id="lo-limitations"> |
| 96 |
<title>Limitations</title> |
| 97 |
|
| 98 |
<itemizedlist> |
| 99 |
<listitem> |
| 100 |
<para> |
| 101 |
Dropping a table will still orphan any objects it contains, as the trigger |
| 102 |
is not executed. You can avoid this by preceding the <command>DROP |
| 103 |
TABLE</command> with <command>DELETE FROM <replaceable>table</replaceable></command>. |
| 104 |
</para> |
| 105 |
|
| 106 |
<para> |
| 107 |
<command>TRUNCATE</command> has the same hazard. |
| 108 |
</para> |
| 109 |
|
| 110 |
<para> |
| 111 |
If you already have, or suspect you have, orphaned large objects, see the |
| 112 |
<xref linkend="vacuumlo"/> module to help |
| 113 |
you clean them up. It's a good idea to run <application>vacuumlo</application> |
| 114 |
occasionally as a back-stop to the <function>lo_manage</function> trigger. |
| 115 |
</para> |
| 116 |
</listitem> |
| 117 |
|
| 118 |
<listitem> |
| 119 |
<para> |
| 120 |
Some frontends may create their own tables, and will not create the |
| 121 |
associated trigger(s). Also, users may not remember (or know) to create |
| 122 |
the triggers. |
| 123 |
</para> |
| 124 |
</listitem> |
| 125 |
</itemizedlist> |
| 126 |
</sect2> |
| 127 |
|
| 128 |
<sect2 id="lo-author"> |
| 129 |
<title>Author</title> |
| 130 |
|
| 131 |
<para> |
| 132 |
Peter Mount <email>[email protected]</email> |
| 133 |
</para> |
| 134 |
</sect2> |
| 135 |
|
| 136 |
</sect1> |