2 $Header: /cvsroot/pgsql/doc/src/sgml/lobj.sgml,v 1.31 2003/11/01 01:56:29 petere Exp $
5 <chapter id="largeObjects">
6 <title id="largeObjects-title">Large Objects</title>
8 <indexterm zone="largeobjects"><primary>large object</></>
9 <indexterm><primary>BLOB</><see>large object</></>
12 In <productname>PostgreSQL</productname> releases prior to 7.1,
13 the size of any row in the database could not exceed the size of a
14 data page. Since the size of a data page is 8192 bytes (the
15 default, which can be raised up to 32768), the upper limit on the
16 size of a data value was relatively low. To support the storage of
17 larger atomic values, <productname>PostgreSQL</productname>
18 provided and continues to provide a large object interface. This
19 interface provides file-oriented access to user data that is stored in
20 a special large-object structure.
24 This chapter describes the implementation and the programming and
25 query language interfaces to <productname>PostgreSQL</productname>
26 large object data. We use the <application>libpq</application> C
27 library for the examples in this chapter, but most programming
28 interfaces native to <productname>PostgreSQL</productname> support
29 equivalent functionality. Other interfaces may use the large
30 object interface internally to provide generic support for large
31 values. This is not described here.
34 <sect1 id="lo-history">
35 <title>History</title>
38 <productname>POSTGRES 4.2</productname>, the indirect predecessor
39 of <productname>PostgreSQL</productname>, supported three standard
40 implementations of large objects: as files external to the
41 <productname>POSTGRES</productname> server, as external files
42 managed by the <productname>POSTGRES</productname> server, and as
43 data stored within the <productname>POSTGRES</productname>
44 database. This caused considerable confusion among users. As a
45 result, only support for large objects as data stored within the
46 database is retained in <productname>PostgreSQL</productname>.
47 Even though this is slower to access, it provides stricter data
48 integrity. For historical reasons, this storage scheme is
49 referred to as <firstterm>Inversion large
50 objects</firstterm>. (You will see the term Inversion used
51 occasionally to mean the same thing as large object.) Since
52 <productname>PostgreSQL 7.1</productname>, all large objects are
53 placed in one system table called
54 <classname>pg_largeobject</classname>.
58 <indexterm><primary>TOAST</></>
59 <indexterm><primary>sliced bread</><see>TOAST</></indexterm>
60 <productname>PostgreSQL 7.1</productname> introduced a mechanism
61 (nicknamed <quote><acronym>TOAST</acronym></quote>) that allows
62 data rows to be much larger than individual data pages. This
63 makes the large object interface partially obsolete. One
64 remaining advantage of the large object interface is that it allows values up
65 to 2 GB in size, whereas <acronym>TOAST</acronym> can only handle 1 GB.
70 <sect1 id="lo-implementation">
71 <title>Implementation Features</title>
74 The large object implementation breaks large
75 objects up into <quote>chunks</quote> and stores the chunks in
76 rows in the database. A B-tree index guarantees fast
77 searches for the correct chunk number when doing random
78 access reads and writes.
82 <sect1 id="lo-interfaces">
83 <title>Client Interfaces</title>
86 This section describes the facilities that
87 <productname>PostgreSQL</productname> client interface libraries
88 provide for accessing large objects. All large object
89 manipulation using these functions <emphasis>must</emphasis> take
90 place within an SQL transaction block. (This requirement is
91 strictly enforced as of <productname>PostgreSQL 6.5</>, though it
92 has been an implicit requirement in previous versions, resulting
93 in misbehavior if ignored.)
94 The <productname>PostgreSQL</productname> large object interface is modeled after
95 the <acronym>Unix</acronym> file-system interface, with analogues of
96 <function>open</function>, <function>read</function>,
97 <function>write</function>,
98 <function>lseek</function>, etc.
102 Client applications which use the large object interface in
103 <application>libpq</application> should include the header file
104 <filename>libpq/libpq-fs.h</filename> and link with the
105 <application>libpq</application> library.
109 <title>Creating a Large Object</title>
114 Oid lo_creat(PGconn *conn, int mode);
116 <indexterm><primary>lo_creat</></>
117 creates a new large object.
118 <replaceable class="parameter">mode</replaceable> is a bit mask
119 describing several different attributes of the new
120 object. The symbolic constants listed here are defined
121 in the header file <filename>libpq/libpq-fs.h</filename>.
122 The access type (read, write, or both) is controlled by
123 or'ing together the bits <symbol>INV_READ</symbol> and
124 <symbol>INV_WRITE</symbol>. The low-order sixteen bits of the mask have
125 historically been used at Berkeley to designate the storage manager number on which the large object
127 bits should always be zero now.
128 The return value is the OID that was assigned to the new large object.
134 inv_oid = lo_creat(INV_READ|INV_WRITE);
140 <title>Importing a Large Object</title>
143 To import an operating system file as a large object, call
145 Oid lo_import(PGconn *conn, const char *filename);
147 <indexterm><primary>lo_import</></>
148 <replaceable class="parameter">filename</replaceable>
149 specifies the operating system name of
150 the file to be imported as a large object.
151 The return value is the OID that was assigned to the new large object.
156 <title>Exporting a Large Object</title>
159 To export a large object
160 into an operating system file, call
162 int lo_export(PGconn *conn, Oid lobjId, const char *filename);
164 <indexterm><primary>lo_export</></>
165 The <parameter>lobjId</parameter> argument specifies the OID of the large
166 object to export and the <parameter>filename</parameter> argument specifies
167 the operating system name name of the file.
172 <title>Opening an Existing Large Object</title>
175 To open an existing large object, call
177 int lo_open(PGconn *conn, Oid lobjId, int mode);
179 <indexterm><primary>lo_open</></>
180 The <parameter>lobjId</parameter> argument specifies the OID of the large
181 object to open. The <parameter>mode</parameter> bits control whether the
182 object is opened for reading (<symbol>INV_READ</>), writing (<symbol>INV_WRITE</symbol>), or
184 A large object cannot be opened before it is created.
185 <function>lo_open</function> returns a large object descriptor
186 for later use in <function>lo_read</function>, <function>lo_write</function>,
187 <function>lo_lseek</function>, <function>lo_tell</function>, and
188 <function>lo_close</function>. The descriptor is only valid for
189 the duration of the current transaction.
194 <title>Writing Data to a Large Object</title>
199 int lo_write(PGconn *conn, int fd, const char *buf, size_t len);
201 <indexterm><primary>lo_write</></> writes
202 <parameter>len</parameter> bytes from <parameter>buf</parameter>
203 to large object <parameter>fd</>. The <parameter>fd</parameter>
204 argument must have been returned by a previous
205 <function>lo_open</function>. The number of bytes actually
206 written is returned. In the event of an error, the return value
212 <title>Reading Data from a Large Object</title>
217 int lo_read(PGconn *conn, int fd, char *buf, size_t len);
219 <indexterm><primary>lo_read</></> reads
220 <parameter>len</parameter> bytes from large object
221 <parameter>fd</parameter> into <parameter>buf</parameter>. The
222 <parameter>fd</parameter> argument must have been returned by a
223 previous <function>lo_open</function>. The number of bytes
224 actually read is returned. In the event of an error, the return
230 <title>Seeking on a Large Object</title>
233 To change the current read or write location on a large
236 int lo_lseek(PGconn *conn, int fd, int offset, int whence);
238 <indexterm><primary>lo_lseek</></> This function moves the
239 current location pointer for the large object described by
240 <parameter>fd</> to the new location specified by
241 <parameter>offset</>. The valid values for <parameter>whence</>
242 are <symbol>SEEK_SET</> (seek from object start),
243 <symbol>SEEK_CUR</> (seek from current position), and
244 <symbol>SEEK_END</> (seek from object end). The return value is
245 the new location pointer.
250 <title>Obtaining the Seek Position of a Large Object</title>
253 To obtain the current read or write location of a large object,
256 int lo_tell(PGconn *conn, int fd);
258 <indexterm><primary>lo_tell</></> If there is an error, the
259 return value is negative.
264 <title>Closing a Large Object Descriptor</title>
267 A large object may be closed by calling
269 int lo_close(PGconn *conn, int fd);
271 <indexterm><primary>lo_close</></> where <parameter>fd</> is a
272 large object descriptor returned by <function>lo_open</function>.
273 On success, <function>lo_close</function> returns zero. On
274 error, the return value is negative.
278 Any large object descriptors that remain open at the end of a
279 transaction will be closed automatically.
284 <title>Removing a Large Object</title>
287 To remove a large object from the database, call
289 int lo_unlink(PGconn *conn, Oid lobjId);
291 <indexterm><primary>lo_unlink</></> The
292 <parameter>lobjId</parameter> argument specifies the OID of the
293 large object to remove. In the event of an error, the return
301 <sect1 id="lo-funcs">
302 <title>Server-side Functions</title>
305 There are two built-in server-side functions,
306 <function>lo_import</function><indexterm><primary>lo_import</></>
308 <function>lo_export</function>,<indexterm><primary>lo_export</></>
309 for large object access, which are available for use in
310 <acronym>SQL</acronym> commands. Here is an example of their
318 INSERT INTO image (name, raster)
319 VALUES ('beautiful image', lo_import('/etc/motd'));
321 SELECT lo_export(image.raster, '/tmp/motd') FROM image
322 WHERE name = 'beautiful image';
327 These functions read and write files in the server's file system, using the
328 permissions of the database's owning user. Therefore, their use is restricted
329 to superusers. (In contrast, the client-side import and export functions
330 read and write files in the client's file system, using the permissions of
331 the client program. Their use is not restricted.)
335 <sect1 id="lo-examplesect">
336 <title>Example Program</title>
339 <xref linkend="lo-example"> is a sample program which shows how the large object
341 in <application>libpq</> can be used. Parts of the program are
342 commented out but are left in the source for the reader's
343 benefit. This program can also be found in
344 <filename>src/test/examples/testlo.c</filename> in the source distribution.
347 <example id="lo-example">
348 <title>Large Objects with <application>libpq</application> Example Program</title>
350 /*--------------------------------------------------------------
353 * test using large objects with libpq
355 * Copyright (c) 1994, Regents of the University of California
357 *--------------------------------------------------------------
359 #include <stdio.h>
360 #include "libpq-fe.h"
361 #include "libpq/libpq-fs.h"
367 * import file "in_filename" into database as large object "lobjOid"
371 importFile(PGconn *conn, char *filename)
381 * open the file to be read in
383 fd = open(filename, O_RDONLY, 0666);
386 fprintf(stderr, "can't open unix file %s\n", filename);
390 * create the large object
392 lobjId = lo_creat(conn, INV_READ | INV_WRITE);
394 fprintf(stderr, "can't create large object\n");
396 lobj_fd = lo_open(conn, lobjId, INV_WRITE);
399 * read in from the Unix file and write to the inversion file
401 while ((nbytes = read(fd, buf, BUFSIZE)) > 0)
403 tmp = lo_write(conn, lobj_fd, buf, nbytes);
405 fprintf(stderr, "error while reading large object\n");
409 (void) lo_close(conn, lobj_fd);
415 pickout(PGconn *conn, Oid lobjId, int start, int len)
422 lobj_fd = lo_open(conn, lobjId, INV_READ);
425 fprintf(stderr, "can't open large object %d\n",
429 lo_lseek(conn, lobj_fd, start, SEEK_SET);
430 buf = malloc(len + 1);
433 while (len - nread > 0)
435 nbytes = lo_read(conn, lobj_fd, buf, len - nread);
437 fprintf(stderr, ">>> %s", buf);
441 fprintf(stderr, "\n");
442 lo_close(conn, lobj_fd);
446 overwrite(PGconn *conn, Oid lobjId, int start, int len)
454 lobj_fd = lo_open(conn, lobjId, INV_READ);
457 fprintf(stderr, "can't open large object %d\n",
461 lo_lseek(conn, lobj_fd, start, SEEK_SET);
462 buf = malloc(len + 1);
464 for (i = 0; i < len; i++)
469 while (len - nwritten > 0)
471 nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten);
475 fprintf(stderr, "\n");
476 lo_close(conn, lobj_fd);
480 * exportFile * export large object "lobjOid" to file "out_filename"
484 exportFile(PGconn *conn, Oid lobjId, char *filename)
493 * create an inversion "object"
495 lobj_fd = lo_open(conn, lobjId, INV_READ);
498 fprintf(stderr, "can't open large object %d\n",
503 * open the file to be written to
505 fd = open(filename, O_CREAT | O_WRONLY, 0666);
508 fprintf(stderr, "can't open unix file %s\n",
513 * read in from the Unix file and write to the inversion file
515 while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0)
517 tmp = write(fd, buf, nbytes);
520 fprintf(stderr, "error while writing %s\n",
525 (void) lo_close(conn, lobj_fd);
532 exit_nicely(PGconn *conn)
539 main(int argc, char **argv)
550 fprintf(stderr, "Usage: %s database_name in_filename out_filename\n",
556 in_filename = argv[2];
557 out_filename = argv[3];
560 * set up the connection
562 conn = PQsetdb(NULL, NULL, NULL, NULL, database);
564 /* check to see that the backend connection was successfully made */
565 if (PQstatus(conn) == CONNECTION_BAD)
567 fprintf(stderr, "Connection to database '%s' failed.\n", database);
568 fprintf(stderr, "%s", PQerrorMessage(conn));
572 res = PQexec(conn, "begin");
575 printf("importing file %s\n", in_filename);
576 /* lobjOid = importFile(conn, in_filename); */
577 lobjOid = lo_import(conn, in_filename);
579 printf("as large object %d.\n", lobjOid);
581 printf("picking out bytes 1000-2000 of the large object\n");
582 pickout(conn, lobjOid, 1000, 1000);
584 printf("overwriting bytes 1000-2000 of the large object with X's\n");
585 overwrite(conn, lobjOid, 1000, 1000);
588 printf("exporting large object to file %s\n", out_filename);
589 /* exportFile(conn, lobjOid, out_filename); */
590 lo_export(conn, lobjOid, out_filename);
592 res = PQexec(conn, "end");
603 <!-- Keep this comment at the end of the file
608 sgml-minimize-attributes:nil
609 sgml-always-quote-attributes:t
612 sgml-parent-document:nil
613 sgml-default-dtd-file:"./reference.ced"
614 sgml-exposed-tags:nil
615 sgml-local-catalogs:("/usr/lib/sgml/catalog")
616 sgml-local-ecat-files:nil