diff options
Diffstat (limited to 'doc/src/sgml/lo.sgml')
| -rw-r--r-- | doc/src/sgml/lo.sgml | 136 |
1 files changed, 136 insertions, 0 deletions
diff --git a/doc/src/sgml/lo.sgml b/doc/src/sgml/lo.sgml new file mode 100644 index 0000000..f46cd39 --- /dev/null +++ b/doc/src/sgml/lo.sgml @@ -0,0 +1,136 @@ +<!-- doc/src/sgml/lo.sgml --> + +<sect1 id="lo" xreflabel="lo"> + <title>lo</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> + <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> + <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> + <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> + <title>Author</title> + + <para> + Peter Mount <email>peter@retep.org.uk</email> + </para> + </sect2> + +</sect1> |