doc/src/sgml/lo.sgml

   1 <!-- doc/src/sgml/lo.sgml -->
   2
   3 <sect1 id="lo" xreflabel="lo">
   4  <title>lo &mdash; 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 &mdash; 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>peter@retep.org.uk</email>
 133   </para>
 134  </sect2>
 135
 136 </sect1>