Debian Codegraph @

doc/src/sgml/lobj.sgml

raw
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 int64_t lo_lseek64(PGconn *conn, int fd, int64_t 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 int64_t 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, int64_t 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>