137 lines
4.7 KiB
Plaintext
137 lines
4.7 KiB
Plaintext
<!-- doc/src/sgml/lo.sgml -->
|
|
|
|
<sect1 id="lo" xreflabel="lo">
|
|
<title>lo — manage large objects</title>
|
|
|
|
<indexterm zone="lo">
|
|
<primary>lo</primary>
|
|
</indexterm>
|
|
|
|
<para>
|
|
The <filename>lo</filename> module provides support for managing Large Objects
|
|
(also called LOs or BLOBs). This includes a data type <type>lo</type>
|
|
and a trigger <function>lo_manage</function>.
|
|
</para>
|
|
|
|
<para>
|
|
This module is considered <quote>trusted</quote>, that is, it can be
|
|
installed by non-superusers who have <literal>CREATE</literal> privilege
|
|
on the current database.
|
|
</para>
|
|
|
|
<sect2 id="lo-rationale">
|
|
<title>Rationale</title>
|
|
|
|
<para>
|
|
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.
|
|
</para>
|
|
|
|
<para>
|
|
As <productname>PostgreSQL</productname> 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.
|
|
</para>
|
|
|
|
<para>
|
|
Now this is fine for <productname>PostgreSQL</productname>-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.
|
|
</para>
|
|
|
|
<para>
|
|
The <filename>lo</filename> module allows fixing this by attaching a trigger
|
|
to tables that contain LO reference columns. The trigger essentially just
|
|
does a <function>lo_unlink</function> whenever you delete or modify a value
|
|
referencing a large object. When you use this trigger, you are assuming
|
|
that there is only one database reference to any large object that is
|
|
referenced in a trigger-controlled column!
|
|
</para>
|
|
|
|
<para>
|
|
The module also provides a data type <type>lo</type>, which is really just
|
|
a <glossterm linkend="glossary-domain">domain</glossterm> over
|
|
the <type>oid</type> type. This is useful for differentiating
|
|
database columns that hold large object references from those that are
|
|
OIDs of other things. You don't have to use the <type>lo</type> type to
|
|
use the trigger, but it may be convenient to use it to keep track of which
|
|
columns in your database represent large objects that you are managing with
|
|
the trigger. It is also rumored that the ODBC driver gets confused if you
|
|
don't use <type>lo</type> for BLOB columns.
|
|
</para>
|
|
</sect2>
|
|
|
|
<sect2 id="lo-how-to-use">
|
|
<title>How to Use It</title>
|
|
|
|
<para>
|
|
Here's a simple example of usage:
|
|
</para>
|
|
|
|
<programlisting>
|
|
CREATE TABLE image (title text, raster lo);
|
|
|
|
CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
|
|
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
|
|
</programlisting>
|
|
|
|
<para>
|
|
For each column that will contain unique references to large objects,
|
|
create a <literal>BEFORE UPDATE OR DELETE</literal> trigger, and give the column
|
|
name as the sole trigger argument. You can also restrict the trigger
|
|
to only execute on updates to the column by using <literal>BEFORE UPDATE
|
|
OF</literal> <replaceable class="parameter">column_name</replaceable>.
|
|
If you need multiple <type>lo</type>
|
|
columns in the same table, create a separate trigger for each one,
|
|
remembering to give a different name to each trigger on the same table.
|
|
</para>
|
|
</sect2>
|
|
|
|
<sect2 id="lo-limitations">
|
|
<title>Limitations</title>
|
|
|
|
<itemizedlist>
|
|
<listitem>
|
|
<para>
|
|
Dropping a table will still orphan any objects it contains, as the trigger
|
|
is not executed. You can avoid this by preceding the <command>DROP
|
|
TABLE</command> with <command>DELETE FROM <replaceable>table</replaceable></command>.
|
|
</para>
|
|
|
|
<para>
|
|
<command>TRUNCATE</command> has the same hazard.
|
|
</para>
|
|
|
|
<para>
|
|
If you already have, or suspect you have, orphaned large objects, see the
|
|
<xref linkend="vacuumlo"/> module to help
|
|
you clean them up. It's a good idea to run <application>vacuumlo</application>
|
|
occasionally as a back-stop to the <function>lo_manage</function> trigger.
|
|
</para>
|
|
</listitem>
|
|
|
|
<listitem>
|
|
<para>
|
|
Some frontends may create their own tables, and will not create the
|
|
associated trigger(s). Also, users may not remember (or know) to create
|
|
the triggers.
|
|
</para>
|
|
</listitem>
|
|
</itemizedlist>
|
|
</sect2>
|
|
|
|
<sect2 id="lo-author">
|
|
<title>Author</title>
|
|
|
|
<para>
|
|
Peter Mount <email>peter@retep.org.uk</email>
|
|
</para>
|
|
</sect2>
|
|
|
|
</sect1>
|