summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/lo.sgml
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 13:44:03 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 13:44:03 +0000
commit293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch)
treefc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/lo.sgml
parentInitial commit. (diff)
downloadpostgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz
postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip
Adding upstream version 16.2.upstream/16.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/lo.sgml')
-rw-r--r--doc/src/sgml/lo.sgml136
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..6d9bceb
--- /dev/null
+++ b/doc/src/sgml/lo.sgml
@@ -0,0 +1,136 @@
+<!-- doc/src/sgml/lo.sgml -->
+
+<sect1 id="lo" xreflabel="lo">
+ <title>lo &mdash; 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 &mdash; 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>