2 $Header: /cvsroot/pgsql/doc/src/sgml/lobj.sgml,v 1.27 2002/04/18 14:28:14 momjian 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 <title>Introduction</title>
15 In <productname>PostgreSQL</productname> releases prior to 7.1,
16 the size of any row in the database could not exceed the size of a
17 data page. Since the size of a data page is 8192 bytes (the
18 default, which can be raised up to 32768), the upper limit on the
19 size of a data value was relatively low. To support the storage of
20 larger atomic values, <productname>PostgreSQL</productname>
21 provided and continues to provide a large object interface. This
22 interface provides file-oriented access to user data that has been
23 declared to be a large object.
27 <productname>POSTGRES 4.2</productname>, the indirect predecessor
28 of <productname>PostgreSQL</productname>, supported three standard
29 implementations of large objects: as files external to the
30 <productname>POSTGRES</productname> server, as external files
31 managed by the <productname>POSTGRES</productname> server, and as
32 data stored within the <productname>POSTGRES</productname>
33 database. This caused considerable confusion among users. As a
34 result, only support for large objects as data stored within the
35 database is retained in <productname>PostgreSQL</productname>.
36 Even though this is slower to access, it provides stricter data
37 integrity. For historical reasons, this storage scheme is
38 referred to as <firstterm>Inversion large
39 objects</firstterm>. (You will see the term Inversion used
40 occasionally to mean the same thing as large object.) Since
41 <productname>PostgreSQL 7.1</productname>, all large objects are
42 placed in one system table called
43 <classname>pg_largeobject</classname>.
47 <indexterm><primary>TOAST</></>
48 <indexterm><primary>sliced bread</><see>TOAST</></indexterm>
49 <productname>PostgreSQL 7.1</productname> introduced a mechanism
50 (nicknamed <quote><acronym>TOAST</acronym></quote>) that allows
51 data rows to be much larger than individual data pages. This
52 makes the large object interface partially obsolete. One
53 remaining advantage of the large object interface is that it
54 allows random access to the data, i.e., the ability to read or
55 write small chunks of a large value. It is planned to equip
56 <acronym>TOAST</acronym> with such functionality in the future.
60 This section describes the implementation and the programming and
61 query language interfaces to <productname>PostgreSQL</productname>
62 large object data. We use the <application>libpq</application> C
63 library for the examples in this section, but most programming
64 interfaces native to <productname>PostgreSQL</productname> support
65 equivalent functionality. Other interfaces may use the large
66 object interface internally to provide generic support for large
67 values. This is not described here.
72 <sect1 id="lo-implementation">
73 <title>Implementation Features</title>
76 The large object implementation breaks large
77 objects up into <quote>chunks</quote> and stores the chunks in
78 tuples in the database. A B-tree index guarantees fast
79 searches for the correct chunk number when doing random
80 access reads and writes.
84 <sect1 id="lo-interfaces">
85 <title>Interfaces</title>
88 The facilities <productname>PostgreSQL</productname> provides to
89 access large objects, both in the backend as part of user-defined
90 functions or the front end as part of an application
91 using the interface, are described below. For users
92 familiar with <productname>POSTGRES 4.2</productname>,
93 <productname>PostgreSQL</productname> has a new set of
94 functions providing a more coherent interface.
98 All large object manipulation <emphasis>must</emphasis> take
99 place within an SQL transaction. This requirement is strictly
100 enforced as of <productname>PostgreSQL 6.5</>, though it has been an
101 implicit requirement in previous versions, resulting in
102 misbehavior if ignored.
108 The <productname>PostgreSQL</productname> large object interface is modeled after
109 the <acronym>Unix</acronym> file-system interface, with analogues of
110 <function>open(2)</function>, <function>read(2)</function>,
111 <function>write(2)</function>,
112 <function>lseek(2)</function>, etc. User
113 functions call these routines to retrieve only the data of
114 interest from a large object. For example, if a large
115 object type called <type>mugshot</type> existed that stored
116 photographs of faces, then a function called <function>beard</function> could
117 be declared on <type>mugshot</type> data. <function>beard</> could look at the
118 lower third of a photograph, and determine the color of
119 the beard that appeared there, if any. The entire
120 large-object value need not be buffered, or even
121 examined, by the <function>beard</function> function.
122 Large objects may be accessed from dynamically-loaded <acronym>C</acronym>
123 functions or database client programs that link the
124 library. <productname>PostgreSQL</productname> provides a set of routines that
125 support opening, reading, writing, closing, and seeking on
130 <title>Creating a Large Object</title>
135 Oid lo_creat(PGconn *<replaceable class="parameter">conn</replaceable>, int <replaceable class="parameter">mode</replaceable>)
137 creates a new large object.
138 <replaceable class="parameter">mode</replaceable> is a bit mask
139 describing several different attributes of the new
140 object. The symbolic constants listed here are defined
141 in the header file <filename>libpq/libpq-fs.h</filename>.
142 The access type (read, write, or both) is controlled by
143 or'ing together the bits <symbol>INV_READ</symbol> and
144 <symbol>INV_WRITE</symbol>. The low-order sixteen bits of the mask have
145 historically been used at Berkeley to designate the storage manager number on which the large object
147 bits should always be zero now.
148 The commands below create a large object:
150 inv_oid = lo_creat(INV_READ|INV_WRITE);
156 <title>Importing a Large Object</title>
159 To import an operating system file as a large object, call
161 Oid lo_import(PGconn *<replaceable class="parameter">conn</replaceable>, const char *<replaceable class="parameter">filename</replaceable>)
163 <replaceable class="parameter">filename</replaceable>
164 specifies the operating system name of
165 the file to be imported as a large object.
170 <title>Exporting a Large Object</title>
173 To export a large object
174 into an operating system file, call
176 int lo_export(PGconn *<replaceable class="parameter">conn</replaceable>, Oid <replaceable class="parameter">lobjId</replaceable>, const char *<replaceable class="parameter">filename</replaceable>)
178 The <parameter>lobjId</parameter> argument specifies the OID of the large
179 object to export and the <parameter>filename</parameter> argument specifies
180 the operating system name name of the file.
185 <title>Opening an Existing Large Object</title>
188 To open an existing large object, call
190 int lo_open(PGconn *conn, Oid lobjId, int mode)
192 The <parameter>lobjId</parameter> argument specifies the OID of the large
193 object to open. The <parameter>mode</parameter> bits control whether the
194 object is opened for reading (<symbol>INV_READ</>), writing (<symbol>INV_WRITE</symbol>), or
196 A large object cannot be opened before it is created.
197 <function>lo_open</function> returns a large object descriptor
198 for later use in <function>lo_read</function>, <function>lo_write</function>,
199 <function>lo_lseek</function>, <function>lo_tell</function>, and
200 <function>lo_close</function>.
205 <title>Writing Data to a Large Object</title>
210 int lo_write(PGconn *conn, int fd, const char *buf, size_t len)
212 writes <parameter>len</parameter> bytes from <parameter>buf</parameter> to large object <parameter>fd</>. The <parameter>fd</parameter>
213 argument must have been returned by a previous <function>lo_open</function>.
214 The number of bytes actually written is returned. In
215 the event of an error, the return value is negative.
220 <title>Reading Data from a Large Object</title>
225 int lo_read(PGconn *conn, int fd, char *buf, size_t len)
227 reads <parameter>len</parameter> bytes from large object <parameter>fd</parameter> into <parameter>buf</parameter>. The <parameter>fd</parameter>
228 argument must have been returned by a previous <function>lo_open</function>.
229 The number of bytes actually read is returned. In
230 the event of an error, the return value is negative.
235 <title>Seeking on a Large Object</title>
238 To change the current read or write location on a large
241 int lo_lseek(PGconn *conn, int fd, int offset, int whence)
243 This routine moves the current location pointer for the
244 large object described by <parameter>fd</> to the new location specified
245 by <parameter>offset</>. The valid values for <parameter>whence</> are
246 <symbol>SEEK_SET</>, <symbol>SEEK_CUR</>, and <symbol>SEEK_END</>.
251 <title>Closing a Large Object Descriptor</title>
254 A large object may be closed by calling
256 int lo_close(PGconn *conn, int fd)
258 where <parameter>fd</> is a large object descriptor returned by
259 <function>lo_open</function>. On success, <function>lo_close</function>
260 returns zero. On error, the return value is negative.
265 <title>Removing a Large Object</title>
268 To remove a large object from the database, call
270 int lo_unlink(PGconn *<replaceable class="parameter">conn</replaceable>, Oid lobjId)
272 The <parameter>lobjId</parameter> argument specifies the OID of the large
273 object to remove. In the event of an error, the return value is negative.
280 <sect1 id="lo-funcs">
281 <title>Server-side Built-in Functions</title>
284 There are two built-in registered functions, <function>lo_import</function>
285 and <function>lo_export</function> which are convenient for use
286 in <acronym>SQL</acronym>
288 Here is an example of their use
295 INSERT INTO image (name, raster)
296 VALUES ('beautiful image', lo_import('/etc/motd'));
298 SELECT lo_export(image.raster, '/tmp/motd') FROM image
299 WHERE name = 'beautiful image';
304 <sect1 id="lo-libpq">
305 <title>Accessing Large Objects from <application>Libpq</application></title>
308 <xref linkend="lo-example"> is a sample program which shows how the large object
310 in <application>libpq</> can be used. Parts of the program are
311 commented out but are left in the source for the reader's
312 benefit. This program can be found in
313 <filename>src/test/examples/testlo.c</filename> in the source distribution.
314 Frontend applications which use the large object interface
315 in <application>libpq</application> should include the header file
316 <filename>libpq/libpq-fs.h</filename> and link with the <application>libpq</application> library.
319 <example id="lo-example">
320 <title>Large Objects with <application>Libpq</application> Example Program</title>
322 /*--------------------------------------------------------------
325 * test using large objects with libpq
327 * Copyright (c) 1994, Regents of the University of California
329 *--------------------------------------------------------------
331 #include <stdio.h>
332 #include "libpq-fe.h"
333 #include "libpq/libpq-fs.h"
339 * import file "in_filename" into database as large object "lobjOid"
343 importFile(PGconn *conn, char *filename)
353 * open the file to be read in
355 fd = open(filename, O_RDONLY, 0666);
358 fprintf(stderr, "can't open unix file %s\n", filename);
362 * create the large object
364 lobjId = lo_creat(conn, INV_READ | INV_WRITE);
366 fprintf(stderr, "can't create large object\n");
368 lobj_fd = lo_open(conn, lobjId, INV_WRITE);
371 * read in from the Unix file and write to the inversion file
373 while ((nbytes = read(fd, buf, BUFSIZE)) > 0)
375 tmp = lo_write(conn, lobj_fd, buf, nbytes);
377 fprintf(stderr, "error while reading large object\n");
381 (void) lo_close(conn, lobj_fd);
387 pickout(PGconn *conn, Oid lobjId, int start, int len)
394 lobj_fd = lo_open(conn, lobjId, INV_READ);
397 fprintf(stderr, "can't open large object %d\n",
401 lo_lseek(conn, lobj_fd, start, SEEK_SET);
402 buf = malloc(len + 1);
405 while (len - nread > 0)
407 nbytes = lo_read(conn, lobj_fd, buf, len - nread);
409 fprintf(stderr, ">>> %s", buf);
413 fprintf(stderr, "\n");
414 lo_close(conn, lobj_fd);
418 overwrite(PGconn *conn, Oid lobjId, int start, int len)
426 lobj_fd = lo_open(conn, lobjId, INV_READ);
429 fprintf(stderr, "can't open large object %d\n",
433 lo_lseek(conn, lobj_fd, start, SEEK_SET);
434 buf = malloc(len + 1);
436 for (i = 0; i < len; i++)
441 while (len - nwritten > 0)
443 nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten);
447 fprintf(stderr, "\n");
448 lo_close(conn, lobj_fd);
452 * exportFile * export large object "lobjOid" to file "out_filename"
456 exportFile(PGconn *conn, Oid lobjId, char *filename)
465 * create an inversion "object"
467 lobj_fd = lo_open(conn, lobjId, INV_READ);
470 fprintf(stderr, "can't open large object %d\n",
475 * open the file to be written to
477 fd = open(filename, O_CREAT | O_WRONLY, 0666);
480 fprintf(stderr, "can't open unix file %s\n",
485 * read in from the Unix file and write to the inversion file
487 while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0)
489 tmp = write(fd, buf, nbytes);
492 fprintf(stderr, "error while writing %s\n",
497 (void) lo_close(conn, lobj_fd);
504 exit_nicely(PGconn *conn)
511 main(int argc, char **argv)
522 fprintf(stderr, "Usage: %s database_name in_filename out_filename\n",
528 in_filename = argv[2];
529 out_filename = argv[3];
532 * set up the connection
534 conn = PQsetdb(NULL, NULL, NULL, NULL, database);
536 /* check to see that the backend connection was successfully made */
537 if (PQstatus(conn) == CONNECTION_BAD)
539 fprintf(stderr, "Connection to database '%s' failed.\n", database);
540 fprintf(stderr, "%s", PQerrorMessage(conn));
544 res = PQexec(conn, "begin");
547 printf("importing file %s\n", in_filename);
548 /* lobjOid = importFile(conn, in_filename); */
549 lobjOid = lo_import(conn, in_filename);
551 printf("as large object %d.\n", lobjOid);
553 printf("picking out bytes 1000-2000 of the large object\n");
554 pickout(conn, lobjOid, 1000, 1000);
556 printf("overwriting bytes 1000-2000 of the large object with X's\n");
557 overwrite(conn, lobjOid, 1000, 1000);
560 printf("exporting large object to file %s\n", out_filename);
561 /* exportFile(conn, lobjOid, out_filename); */
562 lo_export(conn, lobjOid, out_filename);
564 res = PQexec(conn, "end");
575 <!-- Keep this comment at the end of the file
580 sgml-minimize-attributes:nil
581 sgml-always-quote-attributes:t
584 sgml-parent-document:nil
585 sgml-default-dtd-file:"./reference.ced"
586 sgml-exposed-tags:nil
587 sgml-local-catalogs:("/usr/lib/sgml/catalog")
588 sgml-local-ecat-files:nil