-<!--
-$Header: /cvsroot/pgsql/doc/src/sgml/lobj.sgml,v 1.27 2002/04/18 14:28:14 momjian Exp $
--->
+<!-- doc/src/sgml/lobj.sgml -->
<chapter id="largeObjects">
- <title id="largeObjects-title">Large Objects</title>
+ <title>Large Objects</title>
<indexterm zone="largeobjects"><primary>large object</></>
<indexterm><primary>BLOB</><see>large object</></>
- <sect1 id="lo-intro">
- <title>Introduction</title>
-
<para>
- In <productname>PostgreSQL</productname> releases prior to 7.1,
- the size of any row in the database could not exceed the size of a
- data page. Since the size of a data page is 8192 bytes (the
- default, which can be raised up to 32768), the upper limit on the
- size of a data value was relatively low. To support the storage of
- larger atomic values, <productname>PostgreSQL</productname>
- provided and continues to provide a large object interface. This
- interface provides file-oriented access to user data that has been
- declared to be a large object.
+ <productname>PostgreSQL</productname> has a <firstterm>large object</>
+ facility, which provides stream-style access to user data that is stored
+ in a special large-object structure. Streaming access is useful
+ when working with data values that are too large to manipulate
+ conveniently as a whole.
</para>
<para>
- <productname>POSTGRES 4.2</productname>, the indirect predecessor
- of <productname>PostgreSQL</productname>, supported three standard
- implementations of large objects: as files external to the
- <productname>POSTGRES</productname> server, as external files
- managed by the <productname>POSTGRES</productname> server, and as
- data stored within the <productname>POSTGRES</productname>
- database. This caused considerable confusion among users. As a
- result, only support for large objects as data stored within the
- database is retained in <productname>PostgreSQL</productname>.
- Even though this is slower to access, it provides stricter data
- integrity. For historical reasons, this storage scheme is
- referred to as <firstterm>Inversion large
- objects</firstterm>. (You will see the term Inversion used
- occasionally to mean the same thing as large object.) Since
- <productname>PostgreSQL 7.1</productname>, all large objects are
- placed in one system table called
- <classname>pg_largeobject</classname>.
+ This chapter describes the implementation and the programming and
+ query language interfaces to <productname>PostgreSQL</productname>
+ large object data. We use the <application>libpq</application> C
+ library for the examples in this chapter, but most programming
+ interfaces native to <productname>PostgreSQL</productname> support
+ equivalent functionality. Other interfaces might use the large
+ object interface internally to provide generic support for large
+ values. This is not described here.
</para>
+ <sect1 id="lo-intro">
+ <title>Introduction</title>
+
+ <indexterm>
+ <primary>TOAST</primary>
+ <secondary>versus large objects</secondary>
+ </indexterm>
+
<para>
- <indexterm><primary>TOAST</></>
- <indexterm><primary>sliced bread</><see>TOAST</></indexterm>
- <productname>PostgreSQL 7.1</productname> introduced a mechanism
- (nicknamed <quote><acronym>TOAST</acronym></quote>) that allows
- data rows to be much larger than individual data pages. This
- makes the large object interface partially obsolete. One
- remaining advantage of the large object interface is that it
- allows random access to the data, i.e., the ability to read or
- write small chunks of a large value. It is planned to equip
- <acronym>TOAST</acronym> with such functionality in the future.
+ All large objects are stored in a single system table named <link
+ linkend="catalog-pg-largeobject"><structname>pg_largeobject</structname></link>.
+ Each large object also has an entry in the system table <link
+ linkend="catalog-pg-largeobject-metadata"><structname>pg_largeobject_metadata</structname></link>.
+ Large objects can be created, modified, and deleted using a read/write API
+ that is similar to standard operations on files.
</para>
<para>
- This section describes the implementation and the programming and
- query language interfaces to <productname>PostgreSQL</productname>
- large object data. We use the <application>libpq</application> C
- library for the examples in this section, but most programming
- interfaces native to <productname>PostgreSQL</productname> support
- equivalent functionality. Other interfaces may use the large
- object interface internally to provide generic support for large
- values. This is not described here.
+ <productname>PostgreSQL</productname> also supports a storage system called
+ <link
+ linkend="storage-toast"><quote><acronym>TOAST</acronym></quote></link>,
+ which automatically stores values
+ larger than a single database page into a secondary storage area per table.
+ This makes the large object facility partially obsolete. One
+ remaining advantage of the large object facility is that it allows values
+ up to 4 TB in size, whereas <acronym>TOAST</acronym>ed fields can be at
+ most 1 GB. Also, reading and updating portions of a large object can be
+ done efficiently, while most operations on a <acronym>TOAST</acronym>ed
+ field will read or write the whole value as a unit.
</para>
</sect1>
<title>Implementation Features</title>
<para>
- The large object implementation breaks large
- objects up into <quote>chunks</quote> and stores the chunks in
- tuples in the database. A B-tree index guarantees fast
+ The large object implementation breaks large
+ objects up into <quote>chunks</quote> and stores the chunks in
+ rows in the database. A B-tree index guarantees fast
searches for the correct chunk number when doing random
access reads and writes.
</para>
+
+ <para>
+ The chunks stored for a large object do not have to be contiguous.
+ For example, if an application opens a new large object, seeks to offset
+ 1000000, and writes a few bytes there, this does not result in allocation
+ of 1000000 bytes worth of storage; only of chunks covering the range of
+ data bytes actually written. A read operation will, however, read out
+ zeroes for any unallocated locations preceding the last existing chunk.
+ This corresponds to the common behavior of <quote>sparsely allocated</>
+ files in <acronym>Unix</acronym> file systems.
+ </para>
+
+ <para>
+ As of <productname>PostgreSQL</> 9.0, large objects have an owner
+ and a set of access permissions, which can be managed using
+ <xref linkend="sql-grant"> and
+ <xref linkend="sql-revoke">.
+ <literal>SELECT</literal> privileges are required to read a large
+ object, and
+ <literal>UPDATE</literal> privileges are required to write or
+ truncate it.
+ Only the large object's owner (or a database superuser) can delete,
+ comment on, or change the owner of a large object.
+ To adjust this behavior for compatibility with prior releases, see the
+ <xref linkend="guc-lo-compat-privileges"> run-time parameter.
+ </para>
</sect1>
<sect1 id="lo-interfaces">
- <title>Interfaces</title>
+ <title>Client Interfaces</title>
+
+ <para>
+ This section describes the facilities that
+ <productname>PostgreSQL</productname>'s <application>libpq</>
+ client interface library provides for accessing large objects.
+ The <productname>PostgreSQL</productname> large object interface is
+ modeled after the <acronym>Unix</acronym> file-system interface, with
+ analogues of <function>open</function>, <function>read</function>,
+ <function>write</function>,
+ <function>lseek</function>, etc.
+ </para>
<para>
- The facilities <productname>PostgreSQL</productname> provides to
- access large objects, both in the backend as part of user-defined
- functions or the front end as part of an application
- using the interface, are described below. For users
- familiar with <productname>POSTGRES 4.2</productname>,
- <productname>PostgreSQL</productname> has a new set of
- functions providing a more coherent interface.
-
- <note>
- <para>
- All large object manipulation <emphasis>must</emphasis> take
- place within an SQL transaction. This requirement is strictly
- enforced as of <productname>PostgreSQL 6.5</>, though it has been an
- implicit requirement in previous versions, resulting in
- misbehavior if ignored.
- </para>
- </note>
+ All large object manipulation using these functions
+ <emphasis>must</emphasis> take place within an SQL transaction block,
+ since large object file descriptors are only valid for the duration of
+ a transaction.
</para>
<para>
- The <productname>PostgreSQL</productname> large object interface is modeled after
- the <acronym>Unix</acronym> file-system interface, with analogues of
- <function>open(2)</function>, <function>read(2)</function>,
- <function>write(2)</function>,
- <function>lseek(2)</function>, etc. User
- functions call these routines to retrieve only the data of
- interest from a large object. For example, if a large
- object type called <type>mugshot</type> existed that stored
- photographs of faces, then a function called <function>beard</function> could
- be declared on <type>mugshot</type> data. <function>beard</> could look at the
- lower third of a photograph, and determine the color of
- the beard that appeared there, if any. The entire
- large-object value need not be buffered, or even
- examined, by the <function>beard</function> function.
- Large objects may be accessed from dynamically-loaded <acronym>C</acronym>
- functions or database client programs that link the
- library. <productname>PostgreSQL</productname> provides a set of routines that
- support opening, reading, writing, closing, and seeking on
- large objects.
+ If an error occurs while executing any one of these functions, the
+ function will return an otherwise-impossible value, typically 0 or -1.
+ A message describing the error is stored in the connection object and
+ can be retrieved with <function>PQerrorMessage</>.
</para>
- <sect2>
+ <para>
+ Client applications that use these functions should include the header file
+ <filename>libpq/libpq-fs.h</filename> and link with the
+ <application>libpq</application> library.
+ </para>
+
+ <sect2 id="lo-create">
<title>Creating a Large Object</title>
<para>
- The routine
+ <indexterm><primary>lo_creat</></>
+ The function
<synopsis>
-Oid lo_creat(PGconn *<replaceable class="parameter">conn</replaceable>, int <replaceable class="parameter">mode</replaceable>)
+Oid lo_creat(PGconn *conn, int mode);
</synopsis>
- creates a new large object.
- <replaceable class="parameter">mode</replaceable> is a bit mask
- describing several different attributes of the new
- object. The symbolic constants listed here are defined
- in the header file <filename>libpq/libpq-fs.h</filename>.
- The access type (read, write, or both) is controlled by
- or'ing together the bits <symbol>INV_READ</symbol> and
- <symbol>INV_WRITE</symbol>. The low-order sixteen bits of the mask have
- historically been used at Berkeley to designate the storage manager number on which the large object
- should reside. These
- bits should always be zero now.
- The commands below create a large object:
+ creates a new large object.
+ The return value is the OID that was assigned to the new large object,
+ or <symbol>InvalidOid</symbol> (zero) on failure.
+
+ <replaceable class="parameter">mode</replaceable> is unused and
+ ignored as of <productname>PostgreSQL</productname> 8.1; however, for
+ backward compatibility with earlier releases it is best to
+ set it to <symbol>INV_READ</symbol>, <symbol>INV_WRITE</symbol>,
+ or <symbol>INV_READ</symbol> <literal>|</> <symbol>INV_WRITE</symbol>.
+ (These symbolic constants are defined
+ in the header file <filename>libpq/libpq-fs.h</filename>.)
+ </para>
+
+ <para>
+ An example:
+<programlisting>
+inv_oid = lo_creat(conn, INV_READ|INV_WRITE);
+</programlisting>
+ </para>
+
+ <para>
+ <indexterm><primary>lo_create</></>
+ The function
+<synopsis>
+Oid lo_create(PGconn *conn, Oid lobjId);
+</synopsis>
+ also creates a new large object. The OID to be assigned can be
+ specified by <replaceable class="parameter">lobjId</replaceable>;
+ if so, failure occurs if that OID is already in use for some large
+ object. If <replaceable class="parameter">lobjId</replaceable>
+ is <symbol>InvalidOid</symbol> (zero) then <function>lo_create</> assigns an unused
+ OID (this is the same behavior as <function>lo_creat</>).
+ The return value is the OID that was assigned to the new large object,
+ or <symbol>InvalidOid</symbol> (zero) on failure.
+ </para>
+
+ <para>
+ <function>lo_create</> is new as of <productname>PostgreSQL</productname>
+ 8.1; if this function is run against an older server version, it will
+ fail and return <symbol>InvalidOid</symbol>.
+ </para>
+
+ <para>
+ An example:
<programlisting>
-inv_oid = lo_creat(INV_READ|INV_WRITE);
+inv_oid = lo_create(conn, desired_oid);
</programlisting>
</para>
</sect2>
- <sect2>
+ <sect2 id="lo-import">
<title>Importing a Large Object</title>
<para>
+ <indexterm><primary>lo_import</></>
To import an operating system file as a large object, call
<synopsis>
-Oid lo_import(PGconn *<replaceable class="parameter">conn</replaceable>, const char *<replaceable class="parameter">filename</replaceable>)
+Oid lo_import(PGconn *conn, const char *filename);
</synopsis>
- <replaceable class="parameter">filename</replaceable>
+ <replaceable class="parameter">filename</replaceable>
specifies the operating system name of
the file to be imported as a large object.
+ The return value is the OID that was assigned to the new large object,
+ or <symbol>InvalidOid</symbol> (zero) on failure.
+ Note that the file is read by the client interface library, not by
+ the server; so it must exist in the client file system and be readable
+ by the client application.
+ </para>
+
+ <para>
+ <indexterm><primary>lo_import_with_oid</></>
+ The function
+<synopsis>
+Oid lo_import_with_oid(PGconn *conn, const char *filename, Oid lobjId);
+</synopsis>
+ also imports a new large object. The OID to be assigned can be
+ specified by <replaceable class="parameter">lobjId</replaceable>;
+ if so, failure occurs if that OID is already in use for some large
+ object. If <replaceable class="parameter">lobjId</replaceable>
+ is <symbol>InvalidOid</symbol> (zero) then <function>lo_import_with_oid</> assigns an unused
+ OID (this is the same behavior as <function>lo_import</>).
+ The return value is the OID that was assigned to the new large object,
+ or <symbol>InvalidOid</symbol> (zero) on failure.
+ </para>
+
+ <para>
+ <function>lo_import_with_oid</> is new as of <productname>PostgreSQL</productname>
+ 8.4 and uses <function>lo_create</function> internally which is new in 8.1; if this function is run against 8.0 or before, it will
+ fail and return <symbol>InvalidOid</symbol>.
</para>
</sect2>
- <sect2>
+ <sect2 id="lo-export">
<title>Exporting a Large Object</title>
<para>
+ <indexterm><primary>lo_export</></>
To export a large object
into an operating system file, call
<synopsis>
-int lo_export(PGconn *<replaceable class="parameter">conn</replaceable>, Oid <replaceable class="parameter">lobjId</replaceable>, const char *<replaceable class="parameter">filename</replaceable>)
+int lo_export(PGconn *conn, Oid lobjId, const char *filename);
</synopsis>
- The <parameter>lobjId</parameter> argument specifies the OID of the large
- object to export and the <parameter>filename</parameter> argument specifies
- the operating system name name of the file.
+ The <parameter>lobjId</parameter> argument specifies the OID of the large
+ object to export and the <parameter>filename</parameter> argument
+ specifies the operating system name of the file. Note that the file is
+ written by the client interface library, not by the server. Returns 1
+ on success, -1 on failure.
</para>
</sect2>
- <sect2>
+ <sect2 id="lo-open">
<title>Opening an Existing Large Object</title>
<para>
- To open an existing large object, call
+ <indexterm><primary>lo_open</></>
+ To open an existing large object for reading or writing, call
<synopsis>
-int lo_open(PGconn *conn, Oid lobjId, int mode)
+int lo_open(PGconn *conn, Oid lobjId, int mode);
</synopsis>
- The <parameter>lobjId</parameter> argument specifies the OID of the large
- object to open. The <parameter>mode</parameter> bits control whether the
- object is opened for reading (<symbol>INV_READ</>), writing (<symbol>INV_WRITE</symbol>), or
- both.
- A large object cannot be opened before it is created.
- <function>lo_open</function> returns a large object descriptor
- for later use in <function>lo_read</function>, <function>lo_write</function>,
- <function>lo_lseek</function>, <function>lo_tell</function>, and
- <function>lo_close</function>.
-</para>
+ The <parameter>lobjId</parameter> argument specifies the OID of the large
+ object to open. The <parameter>mode</parameter> bits control whether the
+ object is opened for reading (<symbol>INV_READ</>), writing
+ (<symbol>INV_WRITE</symbol>), or both.
+ (These symbolic constants are defined
+ in the header file <filename>libpq/libpq-fs.h</filename>.)
+ <function>lo_open</function> returns a (non-negative) large object
+ descriptor for later use in <function>lo_read</function>,
+ <function>lo_write</function>, <function>lo_lseek</function>,
+ <function>lo_lseek64</function>, <function>lo_tell</function>,
+ <function>lo_tell64</function>, <function>lo_truncate</function>,
+ <function>lo_truncate64</function>, and <function>lo_close</function>.
+ The descriptor is only valid for
+ the duration of the current transaction.
+ On failure, -1 is returned.
+ </para>
+
+ <para>
+ The server currently does not distinguish between modes
+ <symbol>INV_WRITE</symbol> and <symbol>INV_READ</> <literal>|</>
+ <symbol>INV_WRITE</symbol>: you are allowed to read from the descriptor
+ in either case. However there is a significant difference between
+ these modes and <symbol>INV_READ</> alone: with <symbol>INV_READ</>
+ you cannot write on the descriptor, and the data read from it will
+ reflect the contents of the large object at the time of the transaction
+ snapshot that was active when <function>lo_open</> was executed,
+ regardless of later writes by this or other transactions. Reading
+ from a descriptor opened with <symbol>INV_WRITE</symbol> returns
+ data that reflects all writes of other committed transactions as well
+ as writes of the current transaction. This is similar to the behavior
+ of <literal>REPEATABLE READ</> versus <literal>READ COMMITTED</> transaction
+ modes for ordinary SQL <command>SELECT</> commands.
+ </para>
+
+ <para>
+ An example:
+<programlisting>
+inv_fd = lo_open(conn, inv_oid, INV_READ|INV_WRITE);
+</programlisting>
+ </para>
</sect2>
-<sect2>
+<sect2 id="lo-write">
<title>Writing Data to a Large Object</title>
<para>
- The routine
-<programlisting>
-int lo_write(PGconn *conn, int fd, const char *buf, size_t len)
-</programlisting>
- writes <parameter>len</parameter> bytes from <parameter>buf</parameter> to large object <parameter>fd</>. The <parameter>fd</parameter>
- argument must have been returned by a previous <function>lo_open</function>.
- The number of bytes actually written is returned. In
- the event of an error, the return value is negative.
+ <indexterm><primary>lo_write</></>
+ The function
+<synopsis>
+int lo_write(PGconn *conn, int fd, const char *buf, size_t len);
+</synopsis>
+ writes <parameter>len</parameter> bytes from <parameter>buf</parameter>
+ (which must be of size <parameter>len</parameter>) to large object
+ descriptor <parameter>fd</>. The <parameter>fd</parameter> argument must
+ have been returned by a previous <function>lo_open</function>. The
+ number of bytes actually written is returned (in the current
+ implementation, this will always equal <parameter>len</parameter> unless
+ there is an error). In the event of an error, the return value is -1.
+</para>
+
+<para>
+ Although the <parameter>len</parameter> parameter is declared as
+ <type>size_t</>, this function will reject length values larger than
+ <literal>INT_MAX</>. In practice, it's best to transfer data in chunks
+ of at most a few megabytes anyway.
</para>
</sect2>
-<sect2>
+<sect2 id="lo-read">
<title>Reading Data from a Large Object</title>
<para>
- The routine
-<programlisting>
-int lo_read(PGconn *conn, int fd, char *buf, size_t len)
-</programlisting>
- reads <parameter>len</parameter> bytes from large object <parameter>fd</parameter> into <parameter>buf</parameter>. The <parameter>fd</parameter>
- argument must have been returned by a previous <function>lo_open</function>.
- The number of bytes actually read is returned. In
- the event of an error, the return value is negative.
+ <indexterm><primary>lo_read</></>
+ The function
+<synopsis>
+int lo_read(PGconn *conn, int fd, char *buf, size_t len);
+</synopsis>
+ reads up to <parameter>len</parameter> bytes from large object descriptor
+ <parameter>fd</parameter> into <parameter>buf</parameter> (which must be
+ of size <parameter>len</parameter>). The <parameter>fd</parameter>
+ argument must have been returned by a previous
+ <function>lo_open</function>. The number of bytes actually read is
+ returned; this will be less than <parameter>len</parameter> if the end of
+ the large object is reached first. In the event of an error, the return
+ value is -1.
+</para>
+
+<para>
+ Although the <parameter>len</parameter> parameter is declared as
+ <type>size_t</>, this function will reject length values larger than
+ <literal>INT_MAX</>. In practice, it's best to transfer data in chunks
+ of at most a few megabytes anyway.
</para>
</sect2>
-<sect2>
-<title>Seeking on a Large Object</title>
+<sect2 id="lo-seek">
+<title>Seeking in a Large Object</title>
<para>
- To change the current read or write location on a large
- object, call
-<programlisting>
-int lo_lseek(PGconn *conn, int fd, int offset, int whence)
-</programlisting>
- This routine moves the current location pointer for the
- large object described by <parameter>fd</> to the new location specified
- by <parameter>offset</>. The valid values for <parameter>whence</> are
- <symbol>SEEK_SET</>, <symbol>SEEK_CUR</>, and <symbol>SEEK_END</>.
+ <indexterm><primary>lo_lseek</></>
+ To change the current read or write location associated with a
+ large object descriptor, call
+<synopsis>
+int lo_lseek(PGconn *conn, int fd, int offset, int whence);
+</synopsis>
+ This function moves the
+ current location pointer for the large object descriptor identified by
+ <parameter>fd</> to the new location specified by
+ <parameter>offset</>. The valid values for <parameter>whence</>
+ are <symbol>SEEK_SET</> (seek from object start),
+ <symbol>SEEK_CUR</> (seek from current position), and
+ <symbol>SEEK_END</> (seek from object end). The return value is
+ the new location pointer, or -1 on error.
+</para>
+
+<para>
+ <indexterm><primary>lo_lseek64</></>
+ When dealing with large objects that might exceed 2GB in size,
+ instead use
+<synopsis>
+pg_int64 lo_lseek64(PGconn *conn, int fd, pg_int64 offset, int whence);
+</synopsis>
+ This function has the same behavior
+ as <function>lo_lseek</function>, but it can accept an
+ <parameter>offset</> larger than 2GB and/or deliver a result larger
+ than 2GB.
+ Note that <function>lo_lseek</function> will fail if the new location
+ pointer would be greater than 2GB.
+</para>
+
+<para>
+ <function>lo_lseek64</> is new as of <productname>PostgreSQL</productname>
+ 9.3. If this function is run against an older server version, it will
+ fail and return -1.
+</para>
+
+</sect2>
+
+<sect2 id="lo-tell">
+<title>Obtaining the Seek Position of a Large Object</title>
+
+<para>
+ <indexterm><primary>lo_tell</></>
+ To obtain the current read or write location of a large object descriptor,
+ call
+<synopsis>
+int lo_tell(PGconn *conn, int fd);
+</synopsis>
+ If there is an error, the return value is -1.
+</para>
+
+<para>
+ <indexterm><primary>lo_tell64</></>
+ When dealing with large objects that might exceed 2GB in size,
+ instead use
+<synopsis>
+pg_int64 lo_tell64(PGconn *conn, int fd);
+</synopsis>
+ This function has the same behavior
+ as <function>lo_tell</function>, but it can deliver a result larger
+ than 2GB.
+ Note that <function>lo_tell</function> will fail if the current
+ read/write location is greater than 2GB.
+</para>
+
+<para>
+ <function>lo_tell64</> is new as of <productname>PostgreSQL</productname>
+ 9.3. If this function is run against an older server version, it will
+ fail and return -1.
+</para>
+</sect2>
+
+<sect2 id="lo-truncate">
+<title>Truncating a Large Object</title>
+
+<para>
+ <indexterm><primary>lo_truncate</></>
+ To truncate a large object to a given length, call
+<synopsis>
+int lo_truncate(PGcon *conn, int fd, size_t len);
+</synopsis>
+ This function truncates the large object
+ descriptor <parameter>fd</> to length <parameter>len</>. The
+ <parameter>fd</parameter> argument must have been returned by a
+ previous <function>lo_open</function>. If <parameter>len</> is
+ greater than the large object's current length, the large object
+ is extended to the specified length with null bytes ('\0').
+ On success, <function>lo_truncate</function> returns
+ zero. On error, the return value is -1.
+</para>
+
+<para>
+ The read/write location associated with the descriptor
+ <parameter>fd</parameter> is not changed.
+</para>
+
+<para>
+ Although the <parameter>len</parameter> parameter is declared as
+ <type>size_t</>, <function>lo_truncate</function> will reject length
+ values larger than <literal>INT_MAX</>.
+</para>
+
+<para>
+ <indexterm><primary>lo_truncate64</></>
+ When dealing with large objects that might exceed 2GB in size,
+ instead use
+<synopsis>
+int lo_truncate64(PGcon *conn, int fd, pg_int64 len);
+</synopsis>
+ This function has the same
+ behavior as <function>lo_truncate</function>, but it can accept a
+ <parameter>len</> value exceeding 2GB.
+</para>
+
+<para>
+ <function>lo_truncate</> is new as of <productname>PostgreSQL</productname>
+ 8.3; if this function is run against an older server version, it will
+ fail and return -1.
+</para>
+
+<para>
+ <function>lo_truncate64</> is new as of <productname>PostgreSQL</productname>
+ 9.3; if this function is run against an older server version, it will
+ fail and return -1.
</para>
</sect2>
-<sect2>
+<sect2 id="lo-close">
<title>Closing a Large Object Descriptor</title>
<para>
- A large object may be closed by calling
-<programlisting>
-int lo_close(PGconn *conn, int fd)
-</programlisting>
- where <parameter>fd</> is a large object descriptor returned by
- <function>lo_open</function>. On success, <function>lo_close</function>
- returns zero. On error, the return value is negative.
+ <indexterm><primary>lo_close</></>
+ A large object descriptor can be closed by calling
+<synopsis>
+int lo_close(PGconn *conn, int fd);
+</synopsis>
+ where <parameter>fd</> is a
+ large object descriptor returned by <function>lo_open</function>.
+ On success, <function>lo_close</function> returns zero. On
+ error, the return value is -1.
+</para>
+
+<para>
+ Any large object descriptors that remain open at the end of a
+ transaction will be closed automatically.
</para>
</sect2>
- <sect2>
+ <sect2 id="lo-unlink">
<title>Removing a Large Object</title>
<para>
+ <indexterm><primary>lo_unlink</></>
To remove a large object from the database, call
<synopsis>
-int lo_unlink(PGconn *<replaceable class="parameter">conn</replaceable>, Oid lobjId)
+int lo_unlink(PGconn *conn, Oid lobjId);
</synopsis>
- The <parameter>lobjId</parameter> argument specifies the OID of the large
- object to remove. In the event of an error, the return value is negative.
+ The <parameter>lobjId</parameter> argument specifies the OID of the
+ large object to remove. Returns 1 if successful, -1 on failure.
</para>
</sect2>
-
</sect1>
<sect1 id="lo-funcs">
-<title>Server-side Built-in Functions</title>
+<title>Server-side Functions</title>
+
+ <para>
+ Server-side functions tailored for manipulating large objects from SQL are
+ listed in <xref linkend="lo-funcs-table">.
+ </para>
+
+ <table id="lo-funcs-table">
+ <title>SQL-oriented Large Object Functions</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_from_bytea</primary>
+ </indexterm>
+ <literal><function>lo_from_bytea(<parameter>loid</parameter> <type>oid</type>, <parameter>string</parameter> <type>bytea</type>)</function></literal>
+ </entry>
+ <entry><type>oid</type></entry>
+ <entry>
+ Create a large object and store data there, returning its OID.
+ Pass <literal>0</> to have the system choose an OID.
+ </entry>
+ <entry><literal>lo_from_bytea(0, E'\\xffffff00')</literal></entry>
+ <entry><literal>24528</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_put</primary>
+ </indexterm>
+ <literal><function>lo_put(<parameter>loid</parameter> <type>oid</type>, <parameter>offset</parameter> <type>bigint</type>, <parameter>str</parameter> <type>bytea</type>)</function></literal>
+ </entry>
+ <entry><type>void</type></entry>
+ <entry>
+ Write data at the given offset.
+ </entry>
+ <entry><literal>lo_put(24528, 1, E'\\xaa')</literal></entry>
+ <entry></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_get</primary>
+ </indexterm>
+ <literal><function>lo_get(<parameter>loid</parameter> <type>oid</type> <optional>, <parameter>from</parameter> <type>bigint</type>, <parameter>for</parameter> <type>int</type></optional>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Extract contents or a substring thereof.
+ </entry>
+ <entry><literal>lo_get(24528, 0, 3)</literal></entry>
+ <entry><literal>\xffaaff</literal></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ There are additional server-side functions corresponding to each of the
+ client-side functions described earlier; indeed, for the most part the
+ client-side functions are simply interfaces to the equivalent server-side
+ functions. The ones just as convenient to call via SQL commands are
+ <function>lo_creat</function><indexterm><primary>lo_creat</></>,
+ <function>lo_create</function>,
+ <function>lo_unlink</function><indexterm><primary>lo_unlink</></>,
+ <function>lo_import</function><indexterm><primary>lo_import</></>, and
+ <function>lo_export</function><indexterm><primary>lo_export</></>.
+ Here are examples of their use:
-<para>
- There are two built-in registered functions, <function>lo_import</function>
- and <function>lo_export</function> which are convenient for use
- in <acronym>SQL</acronym>
- queries.
- Here is an example of their use
<programlisting>
CREATE TABLE image (
name text,
raster oid
);
+SELECT lo_creat(-1); -- returns OID of new, empty large object
+
+SELECT lo_create(43213); -- attempts to create large object with OID 43213
+
+SELECT lo_unlink(173454); -- deletes large object with OID 173454
+
INSERT INTO image (name, raster)
VALUES ('beautiful image', lo_import('/etc/motd'));
+INSERT INTO image (name, raster) -- same as above, but specify OID to use
+ VALUES ('beautiful image', lo_import('/etc/motd', 68583));
+
SELECT lo_export(image.raster, '/tmp/motd') FROM image
WHERE name = 'beautiful image';
</programlisting>
-</para>
+ </para>
+
+ <para>
+ The server-side <function>lo_import</function> and
+ <function>lo_export</function> functions behave considerably differently
+ from their client-side analogs. These two functions read and write files
+ in the server's file system, using the permissions of the database's
+ owning user. Therefore, their use is restricted to superusers. In
+ contrast, the client-side import and export functions read and write files
+ in the client's file system, using the permissions of the client program.
+ The client-side functions do not require superuser privilege.
+ </para>
+
+ <para>
+ The functionality of <function>lo_read</function> and
+ <function>lo_write</function> is also available via server-side calls,
+ but the names of the server-side functions differ from the client side
+ interfaces in that they do not contain underscores. You must call
+ these functions as <function>loread</> and <function>lowrite</>.
+ </para>
+
</sect1>
-<sect1 id="lo-libpq">
-<title>Accessing Large Objects from <application>Libpq</application></title>
+<sect1 id="lo-examplesect">
+<title>Example Program</title>
<para>
- <xref linkend="lo-example"> is a sample program which shows how the large object
+ <xref linkend="lo-example"> is a sample program which shows how the large object
interface
- in <application>libpq</> can be used. Parts of the program are
+ in <application>libpq</> can be used. Parts of the program are
commented out but are left in the source for the reader's
- benefit. This program can be found in
+ benefit. This program can also be found in
<filename>src/test/examples/testlo.c</filename> in the source distribution.
- Frontend applications which use the large object interface
- in <application>libpq</application> should include the header file
- <filename>libpq/libpq-fs.h</filename> and link with the <application>libpq</application> library.
</para>
<example id="lo-example">
- <title>Large Objects with <application>Libpq</application> Example Program</title>
-<programlisting>
-/*--------------------------------------------------------------
+ <title>Large Objects with <application>libpq</application> Example Program</title>
+<programlisting><![CDATA[
+/*-------------------------------------------------------------------------
*
- * testlo.c--
+ * testlo.c
* test using large objects with libpq
*
- * Copyright (c) 1994, Regents of the University of California
+ * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/test/examples/testlo.c
*
- *--------------------------------------------------------------
+ *-------------------------------------------------------------------------
*/
-#include <stdio.h>
-#include "libpq-fe.h"
-#include "libpq/libpq-fs.h"
+#include <stdio.h>
+#include <stdlib.h>
-#define BUFSIZE 1024
+#include <sys/types.h>
+#include <sys/stat.h>
+#include <fcntl.h>
+#include <unistd.h>
+
+#include "libpq-fe.h"
+#include "libpq/libpq-fs.h"
+
+#define BUFSIZE 1024
/*
- * importFile
- * import file "in_filename" into database as large object "lobjOid"
+ * importFile -
+ * import file "in_filename" into database as large object "lobjOid"
*
*/
-Oid
+static Oid
importFile(PGconn *conn, char *filename)
{
Oid lobjId;
* open the file to be read in
*/
fd = open(filename, O_RDONLY, 0666);
- if (fd < 0)
+ if (fd < 0)
{ /* error */
- fprintf(stderr, "can't open unix file %s\n", filename);
+ fprintf(stderr, "cannot open unix file\"%s\"\n", filename);
}
/*
*/
lobjId = lo_creat(conn, INV_READ | INV_WRITE);
if (lobjId == 0)
- fprintf(stderr, "can't create large object\n");
+ fprintf(stderr, "cannot create large object");
lobj_fd = lo_open(conn, lobjId, INV_WRITE);
/*
* read in from the Unix file and write to the inversion file
*/
- while ((nbytes = read(fd, buf, BUFSIZE)) > 0)
+ while ((nbytes = read(fd, buf, BUFSIZE)) > 0)
{
tmp = lo_write(conn, lobj_fd, buf, nbytes);
- if (tmp < nbytes)
- fprintf(stderr, "error while reading large object\n");
+ if (tmp < nbytes)
+ fprintf(stderr, "error while reading \"%s\"", filename);
}
- (void) close(fd);
- (void) lo_close(conn, lobj_fd);
+ close(fd);
+ lo_close(conn, lobj_fd);
return lobjId;
}
-void
+static void
pickout(PGconn *conn, Oid lobjId, int start, int len)
{
int lobj_fd;
int nread;
lobj_fd = lo_open(conn, lobjId, INV_READ);
- if (lobj_fd < 0)
- {
- fprintf(stderr, "can't open large object %d\n",
- lobjId);
- }
+ if (lobj_fd < 0)
+ fprintf(stderr, "cannot open large object %u", lobjId);
lo_lseek(conn, lobj_fd, start, SEEK_SET);
buf = malloc(len + 1);
nread = 0;
- while (len - nread > 0)
+ while (len - nread > 0)
{
nbytes = lo_read(conn, lobj_fd, buf, len - nread);
- buf[nbytes] = ' ';
- fprintf(stderr, ">>> %s", buf);
+ buf[nbytes] = '\0';
+ fprintf(stderr, ">>> %s", buf);
nread += nbytes;
+ if (nbytes <= 0)
+ break; /* no more data? */
}
free(buf);
- fprintf(stderr, "\n");
+ fprintf(stderr, "\n");
lo_close(conn, lobj_fd);
}
-void
+static void
overwrite(PGconn *conn, Oid lobjId, int start, int len)
{
int lobj_fd;
int nwritten;
int i;
- lobj_fd = lo_open(conn, lobjId, INV_READ);
- if (lobj_fd < 0)
- {
- fprintf(stderr, "can't open large object %d\n",
- lobjId);
- }
+ lobj_fd = lo_open(conn, lobjId, INV_WRITE);
+ if (lobj_fd < 0)
+ fprintf(stderr, "cannot open large object %u", lobjId);
lo_lseek(conn, lobj_fd, start, SEEK_SET);
buf = malloc(len + 1);
- for (i = 0; i < len; i++)
+ for (i = 0; i < len; i++)
buf[i] = 'X';
- buf[i] = ' ';
+ buf[i] = '\0';
nwritten = 0;
- while (len - nwritten > 0)
+ while (len - nwritten > 0)
{
nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten);
nwritten += nbytes;
+ if (nbytes <= 0)
+ {
+ fprintf(stderr, "\nWRITE FAILED!\n");
+ break;
+ }
}
free(buf);
- fprintf(stderr, "\n");
+ fprintf(stderr, "\n");
lo_close(conn, lobj_fd);
}
+
/*
- * exportFile * export large object "lobjOid" to file "out_filename"
+ * exportFile -
+ * export large object "lobjOid" to file "out_filename"
*
*/
-void
+static void
exportFile(PGconn *conn, Oid lobjId, char *filename)
{
int lobj_fd;
int fd;
/*
- * create an inversion "object"
+ * open the large object
*/
lobj_fd = lo_open(conn, lobjId, INV_READ);
- if (lobj_fd < 0)
- {
- fprintf(stderr, "can't open large object %d\n",
- lobjId);
- }
+ if (lobj_fd < 0)
+ fprintf(stderr, "cannot open large object %u", lobjId);
/*
* open the file to be written to
*/
- fd = open(filename, O_CREAT | O_WRONLY, 0666);
- if (fd < 0)
+ fd = open(filename, O_CREAT | O_WRONLY | O_TRUNC, 0666);
+ if (fd < 0)
{ /* error */
- fprintf(stderr, "can't open unix file %s\n",
+ fprintf(stderr, "cannot open unix file\"%s\"",
filename);
}
/*
- * read in from the Unix file and write to the inversion file
+ * read in from the inversion file and write to the Unix file
*/
- while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0)
+ while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0)
{
tmp = write(fd, buf, nbytes);
- if (tmp < nbytes)
+ if (tmp < nbytes)
{
- fprintf(stderr, "error while writing %s\n",
+ fprintf(stderr, "error while writing \"%s\"",
filename);
}
}
- (void) lo_close(conn, lobj_fd);
- (void) close(fd);
+ lo_close(conn, lobj_fd);
+ close(fd);
return;
}
-void
+static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
if (argc != 4)
{
- fprintf(stderr, "Usage: %s database_name in_filename out_filename\n",
+ fprintf(stderr, "Usage: %s database_name in_filename out_filename\n",
argv[0]);
exit(1);
}
conn = PQsetdb(NULL, NULL, NULL, NULL, database);
/* check to see that the backend connection was successfully made */
- if (PQstatus(conn) == CONNECTION_BAD)
+ if (PQstatus(conn) != CONNECTION_OK)
{
- fprintf(stderr, "Connection to database '%s' failed.\n", database);
- fprintf(stderr, "%s", PQerrorMessage(conn));
+ fprintf(stderr, "Connection to database failed: %s",
+ PQerrorMessage(conn));
exit_nicely(conn);
}
- res = PQexec(conn, "begin");
+ res = PQexec(conn, "begin");
PQclear(res);
-
- printf("importing file %s\n", in_filename);
+ printf("importing file \"%s\" ...\n", in_filename);
/* lobjOid = importFile(conn, in_filename); */
lobjOid = lo_import(conn, in_filename);
-/*
- printf("as large object %d.\n", lobjOid);
+ if (lobjOid == 0)
+ fprintf(stderr, "%s\n", PQerrorMessage(conn));
+ else
+ {
+ printf("\tas large object %u.\n", lobjOid);
- printf("picking out bytes 1000-2000 of the large object\n");
- pickout(conn, lobjOid, 1000, 1000);
+ printf("picking out bytes 1000-2000 of the large object\n");
+ pickout(conn, lobjOid, 1000, 1000);
- printf("overwriting bytes 1000-2000 of the large object with X's\n");
- overwrite(conn, lobjOid, 1000, 1000);
-*/
+ printf("overwriting bytes 1000-2000 of the large object with X's\n");
+ overwrite(conn, lobjOid, 1000, 1000);
- printf("exporting large object to file %s\n", out_filename);
-/* exportFile(conn, lobjOid, out_filename); */
- lo_export(conn, lobjOid, out_filename);
+ printf("exporting large object to file \"%s\" ...\n", out_filename);
+/* exportFile(conn, lobjOid, out_filename); */
+ if (lo_export(conn, lobjOid, out_filename) < 0)
+ fprintf(stderr, "%s\n", PQerrorMessage(conn));
+ }
- res = PQexec(conn, "end");
+ res = PQexec(conn, "end");
PQclear(res);
PQfinish(conn);
- exit(0);
+ return 0;
}
+]]>
</programlisting>
</example>
</sect1>
</chapter>
-
-<!-- Keep this comment at the end of the file
-Local variables:
-mode:sgml
-sgml-omittag:nil
-sgml-shorttag:t
-sgml-minimize-attributes:nil
-sgml-always-quote-attributes:t
-sgml-indent-step:1
-sgml-indent-data:t
-sgml-parent-document:nil
-sgml-default-dtd-file:"./reference.ced"
-sgml-exposed-tags:nil
-sgml-local-catalogs:("/usr/lib/sgml/catalog")
-sgml-local-ecat-files:nil
-End:
--->
projects / postgresql / blobdiff