doc/src/sgml/lobj.sgml

   1 <!-- doc/src/sgml/lobj.sgml -->
   2
   3  <chapter id="largeobjects">
   4   <title>Large Objects</title>
   5
   6   <indexterm zone="largeobjects"><primary>large object</primary></indexterm>
   7   <indexterm><primary>BLOB</primary><see>large object</see></indexterm>
   8
   9    <para>
  10     <productname>PostgreSQL</productname> has a <firstterm>large object</firstterm>
  11     facility, which provides stream-style access to user data that is stored
  12     in a special large-object structure.  Streaming access is useful
  13     when working with data values that are too large to manipulate
  14     conveniently as a whole.
  15    </para>
  16
  17    <para>
  18     This chapter describes the implementation and the programming and
  19     query language interfaces to <productname>PostgreSQL</productname>
  20     large object data.  We use the <application>libpq</application> C
  21     library for the examples in this chapter, but most programming
  22     interfaces native to <productname>PostgreSQL</productname> support
  23     equivalent functionality.  Other interfaces might use the large
  24     object interface internally to provide generic support for large
  25     values.  This is not described here.
  26    </para>
  27
  28   <sect1 id="lo-intro">
  29    <title>Introduction</title>
  30
  31    <indexterm>
  32     <primary>TOAST</primary>
  33     <secondary>versus large objects</secondary>
  34    </indexterm>
  35
  36    <para>
  37     All large objects are stored in a single system table named <link
  38     linkend="catalog-pg-largeobject"><structname>pg_largeobject</structname></link>.
  39     Each large object also has an entry in the system table <link
  40     linkend="catalog-pg-largeobject-metadata"><structname>pg_largeobject_metadata</structname></link>.
  41     Large objects can be created, modified, and deleted using a read/write API
  42     that is similar to standard operations on files.
  43    </para>
  44
  45    <para>
  46     <productname>PostgreSQL</productname> also supports a storage system called
  47     <link
  48     linkend="storage-toast"><quote><acronym>TOAST</acronym></quote></link>,
  49     which automatically stores values
  50     larger than a single database page into a secondary storage area per table.
  51     This makes the large object facility partially obsolete.  One
  52     remaining advantage of the large object facility is that it allows values
  53     up to 4 TB in size, whereas <acronym>TOAST</acronym>ed fields can be at
  54     most 1 GB.  Also, reading and updating portions of a large object can be
  55     done efficiently, while most operations on a <acronym>TOAST</acronym>ed
  56     field will read or write the whole value as a unit.
  57    </para>
  58
  59   </sect1>
  60
  61   <sect1 id="lo-implementation">
  62    <title>Implementation Features</title>
  63
  64    <para>
  65     The large object implementation breaks large
  66     objects up into <quote>chunks</quote> and stores the chunks in
  67     rows in the database.  A B-tree index guarantees fast
  68     searches for the correct chunk number when doing random
  69     access reads and writes.
  70    </para>
  71
  72    <para>
  73     The chunks stored for a large object do not have to be contiguous.
  74     For example, if an application opens a new large object, seeks to offset
  75     1000000, and writes a few bytes there, this does not result in allocation
  76     of 1000000 bytes worth of storage; only of chunks covering the range of
  77     data bytes actually written.  A read operation will, however, read out
  78     zeroes for any unallocated locations preceding the last existing chunk.
  79     This corresponds to the common behavior of <quote>sparsely allocated</quote>
  80     files in <acronym>Unix</acronym> file systems.
  81    </para>
  82
  83    <para>
  84     As of <productname>PostgreSQL</productname> 9.0, large objects have an owner
  85     and a set of access permissions, which can be managed using
  86     <xref linkend="sql-grant"/> and
  87     <xref linkend="sql-revoke"/>.
  88     <literal>SELECT</literal> privileges are required to read a large
  89     object, and
  90     <literal>UPDATE</literal> privileges are required to write or
  91     truncate it.
  92     Only the large object's owner (or a database superuser) can delete,
  93     comment on, or change the owner of a large object.
  94     To adjust this behavior for compatibility with prior releases, see the
  95     <xref linkend="guc-lo-compat-privileges"/> run-time parameter.
  96    </para>
  97   </sect1>
  98
  99   <sect1 id="lo-interfaces">
 100    <title>Client Interfaces</title>
 101
 102    <para>
 103     This section describes the facilities that
 104     <productname>PostgreSQL</productname>'s <application>libpq</application>
 105     client interface library provides for accessing large objects.
 106     The <productname>PostgreSQL</productname> large object interface is
 107     modeled after the <acronym>Unix</acronym> file-system interface, with
 108     analogues of <function>open</function>,  <function>read</function>,
 109     <function>write</function>,
 110     <function>lseek</function>, etc.
 111    </para>
 112
 113    <para>
 114     All large object manipulation using these functions
 115     <emphasis>must</emphasis> take place within an SQL transaction block,
 116     since large object file descriptors are only valid for the duration of
 117     a transaction. Write operations, including <function>lo_open</function>
 118     with the <symbol>INV_WRITE</symbol> mode, are not allowed in a read-only
 119     transaction.
 120    </para>
 121
 122    <para>
 123     If an error occurs while executing any one of these functions, the
 124     function will return an otherwise-impossible value, typically 0 or -1.
 125     A message describing the error is stored in the connection object and
 126     can be retrieved with <xref linkend="libpq-PQerrorMessage"/>.
 127    </para>
 128
 129    <para>
 130     Client applications that use these functions should include the header file
 131     <filename>libpq/libpq-fs.h</filename> and link with the
 132     <application>libpq</application> library.
 133    </para>
 134
 135    <para>
 136     Client applications cannot use these functions while a libpq connection is in pipeline mode.
 137    </para>
 138
 139    <sect2 id="lo-create">
 140     <title>Creating a Large Object</title>
 141
 142     <para>
 143      <indexterm><primary>lo_create</primary></indexterm>
 144      The function
 145 <synopsis>
 146 Oid lo_create(PGconn *conn, Oid lobjId);
 147 </synopsis>
 148      creates a new large object.  The OID to be assigned can be
 149      specified by <replaceable class="parameter">lobjId</replaceable>;
 150      if so, failure occurs if that OID is already in use for some large
 151      object.  If <replaceable class="parameter">lobjId</replaceable>
 152      is <symbol>InvalidOid</symbol> (zero) then <function>lo_create</function>
 153      assigns an unused OID.
 154      The return value is the OID that was assigned to the new large object,
 155      or <symbol>InvalidOid</symbol> (zero) on failure.
 156     </para>
 157
 158     <para>
 159      An example:
 160 <programlisting>
 161 inv_oid = lo_create(conn, desired_oid);
 162 </programlisting>
 163     </para>
 164
 165     <para>
 166      <indexterm><primary>lo_creat</primary></indexterm>
 167      The older function
 168 <synopsis>
 169 Oid lo_creat(PGconn *conn, int mode);
 170 </synopsis>
 171      also creates a new large object, always assigning an unused OID.
 172      The return value is the OID that was assigned to the new large object,
 173      or <symbol>InvalidOid</symbol> (zero) on failure.
 174     </para>
 175
 176     <para>
 177      In <productname>PostgreSQL</productname> releases 8.1 and later,
 178      the <replaceable class="parameter">mode</replaceable> is ignored,
 179      so that <function>lo_creat</function> is exactly equivalent to
 180      <function>lo_create</function> with a zero second argument.
 181      However, there is little reason to use <function>lo_creat</function>
 182      unless you need to work with servers older than 8.1.
 183      To work with such an old server, you must
 184      use <function>lo_creat</function> not <function>lo_create</function>,
 185      and you must set <replaceable class="parameter">mode</replaceable> to
 186      one of <symbol>INV_READ</symbol>, <symbol>INV_WRITE</symbol>,
 187      or <symbol>INV_READ</symbol> <literal>|</literal> <symbol>INV_WRITE</symbol>.
 188      (These symbolic constants are defined
 189      in the header file <filename>libpq/libpq-fs.h</filename>.)
 190     </para>
 191
 192     <para>
 193      An example:
 194 <programlisting>
 195 inv_oid = lo_creat(conn, INV_READ|INV_WRITE);
 196 </programlisting>
 197     </para>
 198    </sect2>
 199
 200    <sect2 id="lo-import">
 201     <title>Importing a Large Object</title>
 202
 203     <para>
 204      <indexterm><primary>lo_import</primary></indexterm>
 205      To import an operating system file as a large object, call
 206 <synopsis>
 207 Oid lo_import(PGconn *conn, const char *filename);
 208 </synopsis>
 209      <replaceable class="parameter">filename</replaceable>
 210      specifies the operating system name of
 211      the file to be imported as a large object.
 212      The return value is the OID that was assigned to the new large object,
 213      or <symbol>InvalidOid</symbol> (zero) on failure.
 214      Note that the file is read by the client interface library, not by
 215      the server; so it must exist in the client file system and be readable
 216      by the client application.
 217     </para>
 218
 219     <para>
 220      <indexterm><primary>lo_import_with_oid</primary></indexterm>
 221      The function
 222 <synopsis>
 223 Oid lo_import_with_oid(PGconn *conn, const char *filename, Oid lobjId);
 224 </synopsis>
 225      also imports a new large object.  The OID to be assigned can be
 226      specified by <replaceable class="parameter">lobjId</replaceable>;
 227      if so, failure occurs if that OID is already in use for some large
 228      object.  If <replaceable class="parameter">lobjId</replaceable>
 229      is <symbol>InvalidOid</symbol> (zero) then <function>lo_import_with_oid</function> assigns an unused
 230      OID (this is the same behavior as <function>lo_import</function>).
 231      The return value is the OID that was assigned to the new large object,
 232      or <symbol>InvalidOid</symbol> (zero) on failure.
 233     </para>
 234
 235     <para>
 236      <function>lo_import_with_oid</function> is new as of <productname>PostgreSQL</productname>
 237      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
 238      fail and return <symbol>InvalidOid</symbol>.
 239     </para>
 240    </sect2>
 241
 242    <sect2 id="lo-export">
 243     <title>Exporting a Large Object</title>
 244
 245     <para>
 246      <indexterm><primary>lo_export</primary></indexterm>
 247      To export a large object
 248      into an operating system file, call
 249 <synopsis>
 250 int lo_export(PGconn *conn, Oid lobjId, const char *filename);
 251 </synopsis>
 252      The <parameter>lobjId</parameter> argument specifies the OID of the large
 253      object to export and the <parameter>filename</parameter> argument
 254      specifies the operating system name of the file.  Note that the file is
 255      written by the client interface library, not by the server.  Returns 1
 256      on success, -1 on failure.
 257     </para>
 258    </sect2>
 259
 260    <sect2 id="lo-open">
 261     <title>Opening an Existing Large Object</title>
 262
 263     <para>
 264      <indexterm><primary>lo_open</primary></indexterm>
 265      To open an existing large object for reading or writing, call
 266 <synopsis>
 267 int lo_open(PGconn *conn, Oid lobjId, int mode);
 268 </synopsis>
 269      The <parameter>lobjId</parameter> argument specifies the OID of the large
 270      object to open.   The <parameter>mode</parameter> bits control whether the
 271      object is opened for reading (<symbol>INV_READ</symbol>), writing
 272      (<symbol>INV_WRITE</symbol>), or both.
 273      (These symbolic constants are defined
 274      in the header file <filename>libpq/libpq-fs.h</filename>.)
 275      <function>lo_open</function> returns a (non-negative) large object
 276      descriptor for later use in <function>lo_read</function>,
 277      <function>lo_write</function>, <function>lo_lseek</function>,
 278      <function>lo_lseek64</function>, <function>lo_tell</function>,
 279      <function>lo_tell64</function>, <function>lo_truncate</function>,
 280      <function>lo_truncate64</function>, and <function>lo_close</function>.
 281      The descriptor is only valid for
 282      the duration of the current transaction.
 283      On failure, -1 is returned.
 284     </para>
 285
 286     <para>
 287      The server currently does not distinguish between modes
 288      <symbol>INV_WRITE</symbol> and <symbol>INV_READ</symbol> <literal>|</literal>
 289      <symbol>INV_WRITE</symbol>: you are allowed to read from the descriptor
 290      in either case.  However there is a significant difference between
 291      these modes and <symbol>INV_READ</symbol> alone: with <symbol>INV_READ</symbol>
 292      you cannot write on the descriptor, and the data read from it will
 293      reflect the contents of the large object at the time of the transaction
 294      snapshot that was active when <function>lo_open</function> was executed,
 295      regardless of later writes by this or other transactions.  Reading
 296      from a descriptor opened with <symbol>INV_WRITE</symbol> returns
 297      data that reflects all writes of other committed transactions as well
 298      as writes of the current transaction.  This is similar to the behavior
 299      of <literal>REPEATABLE READ</literal> versus <literal>READ COMMITTED</literal> transaction
 300      modes for ordinary SQL <command>SELECT</command> commands.
 301     </para>
 302
 303     <para>
 304      <function>lo_open</function> will fail if <literal>SELECT</literal>
 305      privilege is not available for the large object, or
 306      if <symbol>INV_WRITE</symbol> is specified and <literal>UPDATE</literal>
 307      privilege is not available.
 308      (Prior to <productname>PostgreSQL</productname> 11, these privilege
 309      checks were instead performed at the first actual read or write call
 310      using the descriptor.)
 311      These privilege checks can be disabled with the
 312      <xref linkend="guc-lo-compat-privileges"/> run-time parameter.
 313     </para>
 314
 315     <para>
 316      An example:
 317 <programlisting>
 318 inv_fd = lo_open(conn, inv_oid, INV_READ|INV_WRITE);
 319 </programlisting>
 320     </para>
 321 </sect2>
 322
 323 <sect2 id="lo-write">
 324 <title>Writing Data to a Large Object</title>
 325
 326 <para>
 327      <indexterm><primary>lo_write</primary></indexterm>
 328      The function
 329 <synopsis>
 330 int lo_write(PGconn *conn, int fd, const char *buf, size_t len);
 331 </synopsis>
 332      writes <parameter>len</parameter> bytes from <parameter>buf</parameter>
 333      (which must be of size <parameter>len</parameter>) to large object
 334      descriptor <parameter>fd</parameter>.  The <parameter>fd</parameter> argument must
 335      have been returned by a previous <function>lo_open</function>.  The
 336      number of bytes actually written is returned (in the current
 337      implementation, this will always equal <parameter>len</parameter> unless
 338      there is an error).  In the event of an error, the return value is -1.
 339 </para>
 340
 341 <para>
 342      Although the <parameter>len</parameter> parameter is declared as
 343      <type>size_t</type>, this function will reject length values larger than
 344      <literal>INT_MAX</literal>.  In practice, it's best to transfer data in chunks
 345      of at most a few megabytes anyway.
 346 </para>
 347 </sect2>
 348
 349 <sect2 id="lo-read">
 350 <title>Reading Data from a Large Object</title>
 351
 352 <para>
 353      <indexterm><primary>lo_read</primary></indexterm>
 354      The function
 355 <synopsis>
 356 int lo_read(PGconn *conn, int fd, char *buf, size_t len);
 357 </synopsis>
 358      reads up to <parameter>len</parameter> bytes from large object descriptor
 359      <parameter>fd</parameter> into <parameter>buf</parameter> (which must be
 360      of size <parameter>len</parameter>).  The <parameter>fd</parameter>
 361      argument must have been returned by a previous
 362      <function>lo_open</function>.  The number of bytes actually read is
 363      returned; this will be less than <parameter>len</parameter> if the end of
 364      the large object is reached first.  In the event of an error, the return
 365      value is -1.
 366 </para>
 367
 368 <para>
 369      Although the <parameter>len</parameter> parameter is declared as
 370      <type>size_t</type>, this function will reject length values larger than
 371      <literal>INT_MAX</literal>.  In practice, it's best to transfer data in chunks
 372      of at most a few megabytes anyway.
 373 </para>
 374 </sect2>
 375
 376 <sect2 id="lo-seek">
 377 <title>Seeking in a Large Object</title>
 378
 379 <para>
 380      <indexterm><primary>lo_lseek</primary></indexterm>
 381      To change the current read or write location associated with a
 382      large object descriptor, call
 383 <synopsis>
 384 int lo_lseek(PGconn *conn, int fd, int offset, int whence);
 385 </synopsis>
 386      This function moves the
 387      current location pointer for the large object descriptor identified by
 388      <parameter>fd</parameter> to the new location specified by
 389      <parameter>offset</parameter>.  The valid values for <parameter>whence</parameter>
 390      are <symbol>SEEK_SET</symbol> (seek from object start),
 391      <symbol>SEEK_CUR</symbol> (seek from current position), and
 392      <symbol>SEEK_END</symbol> (seek from object end).  The return value is
 393      the new location pointer, or -1 on error.
 394 </para>
 395
 396 <para>
 397      <indexterm><primary>lo_lseek64</primary></indexterm>
 398      When dealing with large objects that might exceed 2GB in size,
 399      instead use
 400 <synopsis>
 401 pg_int64 lo_lseek64(PGconn *conn, int fd, pg_int64 offset, int whence);
 402 </synopsis>
 403      This function has the same behavior
 404      as <function>lo_lseek</function>, but it can accept an
 405      <parameter>offset</parameter> larger than 2GB and/or deliver a result larger
 406      than 2GB.
 407      Note that <function>lo_lseek</function> will fail if the new location
 408      pointer would be greater than 2GB.
 409 </para>
 410
 411 <para>
 412      <function>lo_lseek64</function> is new as of <productname>PostgreSQL</productname>
 413      9.3.  If this function is run against an older server version, it will
 414      fail and return -1.
 415 </para>
 416
 417 </sect2>
 418
 419 <sect2 id="lo-tell">
 420 <title>Obtaining the Seek Position of a Large Object</title>
 421
 422 <para>
 423      <indexterm><primary>lo_tell</primary></indexterm>
 424      To obtain the current read or write location of a large object descriptor,
 425      call
 426 <synopsis>
 427 int lo_tell(PGconn *conn, int fd);
 428 </synopsis>
 429      If there is an error, the return value is -1.
 430 </para>
 431
 432 <para>
 433      <indexterm><primary>lo_tell64</primary></indexterm>
 434      When dealing with large objects that might exceed 2GB in size,
 435      instead use
 436 <synopsis>
 437 pg_int64 lo_tell64(PGconn *conn, int fd);
 438 </synopsis>
 439      This function has the same behavior
 440      as <function>lo_tell</function>, but it can deliver a result larger
 441      than 2GB.
 442      Note that <function>lo_tell</function> will fail if the current
 443      read/write location is greater than 2GB.
 444 </para>
 445
 446 <para>
 447      <function>lo_tell64</function> is new as of <productname>PostgreSQL</productname>
 448      9.3.  If this function is run against an older server version, it will
 449      fail and return -1.
 450 </para>
 451 </sect2>
 452
 453 <sect2 id="lo-truncate">
 454 <title>Truncating a Large Object</title>
 455
 456 <para>
 457      <indexterm><primary>lo_truncate</primary></indexterm>
 458      To truncate a large object to a given length, call
 459 <synopsis>
 460 int lo_truncate(PGconn *conn, int fd, size_t len);
 461 </synopsis>
 462      This function truncates the large object
 463      descriptor <parameter>fd</parameter> to length <parameter>len</parameter>.  The
 464      <parameter>fd</parameter> argument must have been returned by a
 465      previous <function>lo_open</function>.  If <parameter>len</parameter> is
 466      greater than the large object's current length, the large object
 467      is extended to the specified length with null bytes ('\0').
 468      On success, <function>lo_truncate</function> returns
 469      zero.  On error, the return value is -1.
 470 </para>
 471
 472 <para>
 473      The read/write location associated with the descriptor
 474      <parameter>fd</parameter> is not changed.
 475 </para>
 476
 477 <para>
 478      Although the <parameter>len</parameter> parameter is declared as
 479      <type>size_t</type>, <function>lo_truncate</function> will reject length
 480      values larger than <literal>INT_MAX</literal>.
 481 </para>
 482
 483 <para>
 484      <indexterm><primary>lo_truncate64</primary></indexterm>
 485      When dealing with large objects that might exceed 2GB in size,
 486      instead use
 487 <synopsis>
 488 int lo_truncate64(PGconn *conn, int fd, pg_int64 len);
 489 </synopsis>
 490      This function has the same
 491      behavior as <function>lo_truncate</function>, but it can accept a
 492      <parameter>len</parameter> value exceeding 2GB.
 493 </para>
 494
 495 <para>
 496      <function>lo_truncate</function> is new as of <productname>PostgreSQL</productname>
 497      8.3; if this function is run against an older server version, it will
 498      fail and return -1.
 499 </para>
 500
 501 <para>
 502      <function>lo_truncate64</function> is new as of <productname>PostgreSQL</productname>
 503      9.3; if this function is run against an older server version, it will
 504      fail and return -1.
 505 </para>
 506 </sect2>
 507
 508 <sect2 id="lo-close">
 509 <title>Closing a Large Object Descriptor</title>
 510
 511 <para>
 512      <indexterm><primary>lo_close</primary></indexterm>
 513      A large object descriptor can be closed by calling
 514 <synopsis>
 515 int lo_close(PGconn *conn, int fd);
 516 </synopsis>
 517      where <parameter>fd</parameter> is a
 518      large object descriptor returned by <function>lo_open</function>.
 519      On success, <function>lo_close</function> returns zero.  On
 520      error, the return value is -1.
 521 </para>
 522
 523 <para>
 524      Any large  object  descriptors that remain open at the end of a
 525      transaction will be closed automatically.
 526 </para>
 527 </sect2>
 528
 529    <sect2 id="lo-unlink">
 530     <title>Removing a Large Object</title>
 531
 532     <para>
 533      <indexterm><primary>lo_unlink</primary></indexterm>
 534      To remove a large object from the database, call
 535 <synopsis>
 536 int lo_unlink(PGconn *conn, Oid lobjId);
 537 </synopsis>
 538      The <parameter>lobjId</parameter> argument specifies the OID of the
 539      large object to remove.  Returns 1 if successful, -1 on failure.
 540     </para>
 541    </sect2>
 542
 543 </sect1>
 544
 545 <sect1 id="lo-funcs">
 546 <title>Server-Side Functions</title>
 547
 548   <para>
 549    Server-side functions tailored for manipulating large objects from SQL are
 550    listed in <xref linkend="lo-funcs-table"/>.
 551   </para>
 552
 553    <table id="lo-funcs-table">
 554     <title>SQL-Oriented Large Object Functions</title>
 555     <tgroup cols="1">
 556      <thead>
 557       <row>
 558        <entry role="func_table_entry"><para role="func_signature">
 559         Function
 560        </para>
 561        <para>
 562         Description
 563        </para>
 564        <para>
 565         Example(s)
 566        </para></entry>
 567       </row>
 568      </thead>
 569
 570      <tbody>
 571       <row>
 572        <entry role="func_table_entry"><para role="func_signature">
 573         <indexterm>
 574          <primary>lo_from_bytea</primary>
 575         </indexterm>
 576         <function>lo_from_bytea</function> ( <parameter>loid</parameter> <type>oid</type>, <parameter>data</parameter> <type>bytea</type> )
 577         <returnvalue>oid</returnvalue>
 578        </para>
 579        <para>
 580         Creates a large object and stores <parameter>data</parameter> in it.
 581         If <parameter>loid</parameter> is zero then the system will choose a
 582         free OID, otherwise that OID is used (with an error if some large
 583         object already has that OID).  On success, the large object's OID is
 584         returned.
 585        </para>
 586        <para>
 587         <literal>lo_from_bytea(0, '\xffffff00')</literal>
 588         <returnvalue>24528</returnvalue>
 589        </para></entry>
 590       </row>
 591
 592       <row>
 593        <entry role="func_table_entry"><para role="func_signature">
 594         <indexterm>
 595          <primary>lo_put</primary>
 596         </indexterm>
 597         <function>lo_put</function> ( <parameter>loid</parameter> <type>oid</type>, <parameter>offset</parameter> <type>bigint</type>, <parameter>data</parameter> <type>bytea</type> )
 598         <returnvalue>void</returnvalue>
 599        </para>
 600        <para>
 601         Writes <parameter>data</parameter> starting at the given offset within
 602         the large object; the large object is enlarged if necessary.
 603        </para>
 604        <para>
 605         <literal>lo_put(24528, 1, '\xaa')</literal>
 606         <returnvalue></returnvalue>
 607        </para></entry>
 608       </row>
 609
 610       <row>
 611        <entry role="func_table_entry"><para role="func_signature">
 612         <indexterm>
 613          <primary>lo_get</primary>
 614         </indexterm>
 615         <function>lo_get</function> ( <parameter>loid</parameter> <type>oid</type> <optional>, <parameter>offset</parameter> <type>bigint</type>, <parameter>length</parameter> <type>integer</type> </optional> )
 616         <returnvalue>bytea</returnvalue>
 617        </para>
 618        <para>
 619         Extracts the large object's contents, or a substring thereof.
 620        </para>
 621        <para>
 622         <literal>lo_get(24528, 0, 3)</literal>
 623         <returnvalue>\xffaaff</returnvalue>
 624        </para></entry>
 625       </row>
 626      </tbody>
 627     </tgroup>
 628    </table>
 629
 630   <para>
 631    There are additional server-side functions corresponding to each of the
 632    client-side functions described earlier; indeed, for the most part the
 633    client-side functions are simply interfaces to the equivalent server-side
 634    functions.  The ones just as convenient to call via SQL commands are
 635    <function>lo_creat</function><indexterm><primary>lo_creat</primary></indexterm>,
 636    <function>lo_create</function>,
 637    <function>lo_unlink</function><indexterm><primary>lo_unlink</primary></indexterm>,
 638    <function>lo_import</function><indexterm><primary>lo_import</primary></indexterm>, and
 639    <function>lo_export</function><indexterm><primary>lo_export</primary></indexterm>.
 640    Here are examples of their use:
 641
 642 <programlisting>
 643 CREATE TABLE image (
 644     name            text,
 645     raster          oid
 646 );
 647
 648 SELECT lo_creat(-1);       -- returns OID of new, empty large object
 649
 650 SELECT lo_create(43213);   -- attempts to create large object with OID 43213
 651
 652 SELECT lo_unlink(173454);  -- deletes large object with OID 173454
 653
 654 INSERT INTO image (name, raster)
 655     VALUES ('beautiful image', lo_import('/etc/motd'));
 656
 657 INSERT INTO image (name, raster)  -- same as above, but specify OID to use
 658     VALUES ('beautiful image', lo_import('/etc/motd', 68583));
 659
 660 SELECT lo_export(image.raster, '/tmp/motd') FROM image
 661     WHERE name = 'beautiful image';
 662 </programlisting>
 663   </para>
 664
 665   <para>
 666     The server-side <function>lo_import</function> and
 667     <function>lo_export</function> functions behave considerably differently
 668     from their client-side analogs.  These two functions read and write files
 669     in the server's file system, using the permissions of the database's
 670     owning user.  Therefore, by default their use is restricted to superusers.
 671     In contrast, the client-side import and export functions read and write
 672     files in the client's file system, using the permissions of the client
 673     program.  The client-side functions do not require any database
 674     privileges, except the privilege to read or write the large object in
 675     question.
 676   </para>
 677
 678   <caution>
 679    <para>
 680     It is possible to <xref linkend="sql-grant"/> use of the
 681     server-side <function>lo_import</function>
 682     and <function>lo_export</function> functions to non-superusers, but
 683     careful consideration of the security implications is required.  A
 684     malicious user of such privileges could easily parlay them into becoming
 685     superuser (for example by rewriting server configuration files), or could
 686     attack the rest of the server's file system without bothering to obtain
 687     database superuser privileges as such.  <emphasis>Access to roles having
 688     such privilege must therefore be guarded just as carefully as access to
 689     superuser roles.</emphasis>  Nonetheless, if use of
 690     server-side <function>lo_import</function>
 691     or <function>lo_export</function> is needed for some routine task, it's
 692     safer to use a role with such privileges than one with full superuser
 693     privileges, as that helps to reduce the risk of damage from accidental
 694     errors.
 695    </para>
 696   </caution>
 697
 698   <para>
 699     The functionality of <function>lo_read</function> and
 700     <function>lo_write</function> is also available via server-side calls,
 701     but the names of the server-side functions differ from the client side
 702     interfaces in that they do not contain underscores.  You must call
 703     these functions as <function>loread</function> and <function>lowrite</function>.
 704   </para>
 705
 706 </sect1>
 707
 708 <sect1 id="lo-examplesect">
 709 <title>Example Program</title>
 710
 711 <para>
 712      <xref linkend="lo-example"/> is a sample program which shows how the large object
 713      interface
 714      in <application>libpq</application> can be used.  Parts of the program are
 715      commented out but are left in the source for  the  reader's
 716      benefit.  This program can also be found in
 717      <filename>src/test/examples/testlo.c</filename> in the source distribution.
 718 </para>
 719
 720   <example id="lo-example">
 721    <title>Large Objects with <application>libpq</application> Example Program</title>
 722 <programlisting><![CDATA[
 723 /*-----------------------------------------------------------------
 724  *
 725  * testlo.c
 726  *    test using large objects with libpq
 727  *
 728  * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
 729  * Portions Copyright (c) 1994, Regents of the University of California
 730  *
 731  *
 732  * IDENTIFICATION
 733  *    src/test/examples/testlo.c
 734  *
 735  *-----------------------------------------------------------------
 736  */
 737 #include <stdio.h>
 738 #include <stdlib.h>
 739
 740 #include <sys/types.h>
 741 #include <sys/stat.h>
 742 #include <fcntl.h>
 743 #include <unistd.h>
 744
 745 #include "libpq-fe.h"
 746 #include "libpq/libpq-fs.h"
 747
 748 #define BUFSIZE         1024
 749
 750 /*
 751  * importFile -
 752  *    import file "in_filename" into database as large object "lobjOid"
 753  *
 754  */
 755 static Oid
 756 importFile(PGconn *conn, char *filename)
 757 {
 758     Oid         lobjId;
 759     int         lobj_fd;
 760     char        buf[BUFSIZE];
 761     int         nbytes,
 762                 tmp;
 763     int         fd;
 764
 765     /*
 766      * open the file to be read in
 767      */
 768     fd = open(filename, O_RDONLY, 0666);
 769     if (fd < 0)
 770     {                           /* error */
 771         fprintf(stderr, "cannot open unix file\"%s\"\n", filename);
 772     }
 773
 774     /*
 775      * create the large object
 776      */
 777     lobjId = lo_creat(conn, INV_READ | INV_WRITE);
 778     if (lobjId == 0)
 779         fprintf(stderr, "cannot create large object");
 780
 781     lobj_fd = lo_open(conn, lobjId, INV_WRITE);
 782
 783     /*
 784      * read in from the Unix file and write to the inversion file
 785      */
 786     while ((nbytes = read(fd, buf, BUFSIZE)) > 0)
 787     {
 788         tmp = lo_write(conn, lobj_fd, buf, nbytes);
 789         if (tmp < nbytes)
 790             fprintf(stderr, "error while reading \"%s\"", filename);
 791     }
 792
 793     close(fd);
 794     lo_close(conn, lobj_fd);
 795
 796     return lobjId;
 797 }
 798
 799 static void
 800 pickout(PGconn *conn, Oid lobjId, int start, int len)
 801 {
 802     int         lobj_fd;
 803     char       *buf;
 804     int         nbytes;
 805     int         nread;
 806
 807     lobj_fd = lo_open(conn, lobjId, INV_READ);
 808     if (lobj_fd < 0)
 809         fprintf(stderr, "cannot open large object %u", lobjId);
 810
 811     lo_lseek(conn, lobj_fd, start, SEEK_SET);
 812     buf = malloc(len + 1);
 813
 814     nread = 0;
 815     while (len - nread > 0)
 816     {
 817         nbytes = lo_read(conn, lobj_fd, buf, len - nread);
 818         buf[nbytes] = '\0';
 819         fprintf(stderr, ">>> %s", buf);
 820         nread += nbytes;
 821         if (nbytes <= 0)
 822             break;              /* no more data? */
 823     }
 824     free(buf);
 825     fprintf(stderr, "\n");
 826     lo_close(conn, lobj_fd);
 827 }
 828
 829 static void
 830 overwrite(PGconn *conn, Oid lobjId, int start, int len)
 831 {
 832     int         lobj_fd;
 833     char       *buf;
 834     int         nbytes;
 835     int         nwritten;
 836     int         i;
 837
 838     lobj_fd = lo_open(conn, lobjId, INV_WRITE);
 839     if (lobj_fd < 0)
 840         fprintf(stderr, "cannot open large object %u", lobjId);
 841
 842     lo_lseek(conn, lobj_fd, start, SEEK_SET);
 843     buf = malloc(len + 1);
 844
 845     for (i = 0; i < len; i++)
 846         buf[i] = 'X';
 847     buf[i] = '\0';
 848
 849     nwritten = 0;
 850     while (len - nwritten > 0)
 851     {
 852         nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten);
 853         nwritten += nbytes;
 854         if (nbytes <= 0)
 855         {
 856             fprintf(stderr, "\nWRITE FAILED!\n");
 857             break;
 858         }
 859     }
 860     free(buf);
 861     fprintf(stderr, "\n");
 862     lo_close(conn, lobj_fd);
 863 }
 864
 865
 866 /*
 867  * exportFile -
 868  *    export large object "lobjOid" to file "out_filename"
 869  *
 870  */
 871 static void
 872 exportFile(PGconn *conn, Oid lobjId, char *filename)
 873 {
 874     int         lobj_fd;
 875     char        buf[BUFSIZE];
 876     int         nbytes,
 877                 tmp;
 878     int         fd;
 879
 880     /*
 881      * open the large object
 882      */
 883     lobj_fd = lo_open(conn, lobjId, INV_READ);
 884     if (lobj_fd < 0)
 885         fprintf(stderr, "cannot open large object %u", lobjId);
 886
 887     /*
 888      * open the file to be written to
 889      */
 890     fd = open(filename, O_CREAT | O_WRONLY | O_TRUNC, 0666);
 891     if (fd < 0)
 892     {                           /* error */
 893         fprintf(stderr, "cannot open unix file\"%s\"",
 894                 filename);
 895     }
 896
 897     /*
 898      * read in from the inversion file and write to the Unix file
 899      */
 900     while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0)
 901     {
 902         tmp = write(fd, buf, nbytes);
 903         if (tmp < nbytes)
 904         {
 905             fprintf(stderr, "error while writing \"%s\"",
 906                     filename);
 907         }
 908     }
 909
 910     lo_close(conn, lobj_fd);
 911     close(fd);
 912 }
 913
 914 static void
 915 exit_nicely(PGconn *conn)
 916 {
 917     PQfinish(conn);
 918     exit(1);
 919 }
 920
 921 int
 922 main(int argc, char **argv)
 923 {
 924     char       *in_filename,
 925                *out_filename;
 926     char       *database;
 927     Oid         lobjOid;
 928     PGconn     *conn;
 929     PGresult   *res;
 930
 931     if (argc != 4)
 932     {
 933         fprintf(stderr, "Usage: %s database_name in_filename out_filename\n",
 934                 argv[0]);
 935         exit(1);
 936     }
 937
 938     database = argv[1];
 939     in_filename = argv[2];
 940     out_filename = argv[3];
 941
 942     /*
 943      * set up the connection
 944      */
 945     conn = PQsetdb(NULL, NULL, NULL, NULL, database);
 946
 947     /* check to see that the backend connection was successfully made */
 948     if (PQstatus(conn) != CONNECTION_OK)
 949     {
 950         fprintf(stderr, "%s", PQerrorMessage(conn));
 951         exit_nicely(conn);
 952     }
 953
 954     /* Set always-secure search path, so malicious users can't take control. */
 955     res = PQexec(conn,
 956                  "SELECT pg_catalog.set_config('search_path', '', false)");
 957     if (PQresultStatus(res) != PGRES_TUPLES_OK)
 958     {
 959         fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
 960         PQclear(res);
 961         exit_nicely(conn);
 962     }
 963     PQclear(res);
 964
 965     res = PQexec(conn, "begin");
 966     PQclear(res);
 967     printf("importing file \"%s\" ...\n", in_filename);
 968 /*  lobjOid = importFile(conn, in_filename); */
 969     lobjOid = lo_import(conn, in_filename);
 970     if (lobjOid == 0)
 971         fprintf(stderr, "%s\n", PQerrorMessage(conn));
 972     else
 973     {
 974         printf("\tas large object %u.\n", lobjOid);
 975
 976         printf("picking out bytes 1000-2000 of the large object\n");
 977         pickout(conn, lobjOid, 1000, 1000);
 978
 979         printf("overwriting bytes 1000-2000 of the large object with X's\n");
 980         overwrite(conn, lobjOid, 1000, 1000);
 981
 982         printf("exporting large object to file \"%s\" ...\n", out_filename);
 983 /*      exportFile(conn, lobjOid, out_filename); */
 984         if (lo_export(conn, lobjOid, out_filename) < 0)
 985             fprintf(stderr, "%s\n", PQerrorMessage(conn));
 986     }
 987
 988     res = PQexec(conn, "end");
 989     PQclear(res);
 990     PQfinish(conn);
 991     return 0;
 992 }
 993 ]]>
 994 </programlisting>
 995 </example>
 996
 997 </sect1>
 998 </chapter>