diff options
Diffstat (limited to 'doc/man/large_objects.3')
-rw-r--r-- | doc/man/large_objects.3 | 499 |
1 files changed, 499 insertions, 0 deletions
diff --git a/doc/man/large_objects.3 b/doc/man/large_objects.3 new file mode 100644 index 00000000000..c253a90d093 --- /dev/null +++ b/doc/man/large_objects.3 @@ -0,0 +1,499 @@ +.\" This is -*-nroff-*- +.\" XXX standard disclaimer belongs here.... +.\" $Header: /usr/local/devel/postgres/src/ref/RCS/large_objects.3pqsrc,v 1.12 1 +993/08/23 09:03:16 aoki Exp $ +.TH "LARGE OBJECTS" INTRO 03/18/94 Postgres95 Postgres95 +.SH DESCRIPTION +.PP +In Postgres, data values are stored in tuples and individual tuples +cannot span data pages. Since the size of a data page is 8192 bytes, +the upper limit on the size of a data value is relatively low. To +support the storage of larger atomic values, Postgres provides a large +object interface. This interface provides file-oriented access to +user data that has been declared to be a large type. +.PP +This section describes the implementation and the +programmatic and query language interfaces to Postgres large object data. +.PP +.SH "Historical Note" +.SH "Historical Note" +.PP +Originally, postgres 4.2 supports three standard implementations of large +objects: as files external to Postgres, as Unix files managed by Postgres, and as +data stored within the Postgres database. It causes considerable confusion +among users. As a result, we only support large objects as data stored +within the Postgres database in Postgres. Even though is is slower to access, +it provides stricter data integrity and time travel. For historical reasons, +they are called Inversion large objects. (We will use Inversion and large +objects interchangeably to mean the same thing in this section.) +.SH "Inversion Large Objects" +.SH "Inversion Large Objects" +.PP +The Inversion large +object implementation breaks large objects up into \*(lqchunks\*(rq and +stores the chunks in tuples in the database. A B-tree index +guarantees fast searches for the correct chunk number when doing +random access reads and writes. +.SH "Large Object Interfaces" +.SH "Large Object Interfaces" +.PP +The facilities Postgres provides to access large objects, both in +the backend as part of user-defined functions or the front end +as part of an application using the \*(LQ interface, are described +below. (For users familiar with postgres 4.2, Postgres has a new set of +functions providing a more coherent interface. The interface is the same +for dynamically-loaded C functions as well as for \*(LQ. +.PP +The Postgres large object interface is modeled after the Unix file +system interface, with analogues of +.I open (2), +.I read (2), +.I write (2), +.I lseek (2), +etc. User functions call these routines to retrieve only the data of +interest from a large object. For example, if a large object type +called +.I mugshot +existed that stored photographs of faces, then a function called +.I beard +could be declared on +.I mugshot +data. +.I Beard +could look at the lower third of a photograph, and determine the color +of the beard that appeared there, if any. The entire large object +value need not be buffered, or even examined, by the +.I beard +function. +.\"As mentioned above, Postgres supports functional indices on +.\"large object data. In this example, the results of the +.\".I beard +.\"function could be stored in a B-tree index to provide fast searches +.\"for people with red beards. +.PP +Large objects may be accessed from dynamically-loaded C functions +or database client programs that link the Libpq library. +Postgres provides a set of routines that +support opening, reading, writing, closing, and seeking on large +objects. +.SH "Creating a Large Object" +.SH "Creating a Large Object" +.PP +The routine +.nf +Oid lo_creat(PGconn *conn, int mode) +.fi +creates a new large object. The +.I mode +is a bitmask describing several different attributes of the new +object. The symbolic constants listed here are defined in +.nf +/usr/local/postgres95/src/backend/libpq/libpq-fs.h +.fi +The access type (read, write, or both) is controlled by +.SM OR +ing together the bits +.SM INV_READ +and +.SM INV_WRITE . +If the large object should be archived \(em that is, if +historical versions of it should be moved periodically to a special +archive relation \(em then the +.SM INV_ARCHIVE +bit should be set. The low-order sixteen bits of +.I mask +are the storage manager number on which the large object should +reside. For sites other than Berkeley, these bits should always be +zero. +.\"At Berkeley, storage manager zero is magnetic disk, storage +.\"manager one is a Sony optical disk jukebox, and storage manager two is +.\"main memory. +.PP +The commands below create an (Inversion) large object: +.nf +inv_oid = lo_creat(INV_READ|INV_WRITE|INV_ARCHIVE); +.fi +.SH "Importing a Large Object" +.SH "Importing a Large Object" +To import a UNIX file as a large object, call +.nf +Oid +lo_import(PGconn *conn, text *filename) +.fi +The +.I filename +argument specifies the UNIX pathname of the file to be imported as +a large object. +.SH "Exporting a Large Object" +.SH "Exporting a Large Object" +To export a large object into UNIX file, call +.nf +int +lo_export(PGconn *conn, Oid lobjId, text *filename) +.fi +The +.I lobjId +argument specifies the Oid of the large object to export and +the +.I filename +argument specifies the UNIX pathname of the file. +.SH "Opening an Existing Large Object" +.SH "Opening an Existing Large Object" +.PP +To open an existing large object, call +.nf +int +lo_open(PGconn *conn, Oid lobjId, int mode, ...) +.fi +The +.I lobjId +argument specifies the Oid of the large object to open. +The mode bits control whether the object is opened for reading +.SM INV_READ ), ( +writing +.SM INV_WRITE ), ( +or both. +.PP +A large object cannot be opened before it is created. +.B lo_open +returns a large object descriptor for later use in +.B lo_read , +.B lo_write , +.B lo_lseek , +.B lo_tell , +and +.B lo_close . +.\"----------- +.SH "Writing Data to a Large Object" +.SH "Writing Data to a Large Object" +.PP +The routine +.nf +int +lo_write(PGconn *conn, int fd, char *buf, int len) +.fi +writes +.I len +bytes from +.I buf +to large object +.I fd . +The +.I fd +argument must have been returned by a previous +.I lo_open . +.PP +The number of bytes actually written is returned. +In the event of an error, +the return value is negative. +.SH "Seeking on a Large Object" +.SH "Seeking on a Large Object" +.PP +To change the current read or write location on a large object, +call +.nf +int +lo_lseek(PGconn *conn, int fd, int offset, int whence) +.fi +This routine moves the current location pointer for the large object +described by +.I fd +to the new location specified by +.I offset . +The valid values for .I whence are +.SM SEEK_SET +.SM SEEK_CUR +and +.SM SEEK_END. +.\"----------- +.SH "Closing a Large Object Descriptor" +.SH "Closing a Large Object Descriptor" +.PP +A large object may be closed by calling +.nf +int +lo_close(PGconn *conn, int fd) +.fi +where +.I fd +is a large object descriptor returned by +.I lo_open . +On success, +.I lo_close +returns zero. On error, the return value is negative. +.PP +.SH "Built in registered functions" +.SH "Built in registered functions" +.PP +There are two built-in registered functions, +.I lo_import +and +.I lo_export +which are convenient for use in SQL queries. +.PP +Here is an example of there use +.nf +CREATE TABLE image ( + name text, + raster oid +); + +INSERT INTO image (name, raster) + VALUES ('beautiful image', lo_import('/etc/motd')); + +SELECT lo_export(image.raster, "/tmp/motd") from image + WHERE name = 'beautiful image'; +.fi +.PP +.SH "Accessing Large Objects from LIBPQ" +.SH "Accessing Large Objects from LIBPQ" +Below is a sample program which shows how the large object interface in +\*(LP can be used. Parts of the program are commented out but are left +in the source for the readers benefit. This program can be found in +.nf +\&../src/test/examples +.fi +.PP +Frontend applications which use the large object interface in \*(LP +should include the header file +.B "libpq/libpq-fs.h" +and link with the +.B libpq +library. +.bp +.SH "Sample Program" +.SH "Sample Program" +.nf +/*------------------------------------------------------------------------- + * + * testlo.c-- + * test using large objects with libpq + * + * Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * $Header: /cvsroot/pgsql/doc/man/Attic/large_objects.3,v 1.1.1.1 1996/08/18 22:14:25 scrappy Exp $ + * + *------------------------------------------------------------------------- + */ +#include <stdio.h> +#include "libpq-fe.h" +#include "libpq/libpq-fs.h" + +#define BUFSIZE 1024 + +/* + * importFile - + * import file "in_filename" into database as large object "lobjOid" + * + */ +Oid importFile(PGconn *conn, char *filename) +{ + Oid lobjId; + int lobj_fd; + char buf[BUFSIZE]; + int nbytes, tmp; + int fd; + + /* + * open the file to be read in + */ + fd = open(filename, O_RDONLY, 0666); + if (fd < 0) { /* error */ + fprintf(stderr, "can't open unix file\"%s\"\n", filename); + } + + /* + * create the large object + */ + lobjId = lo_creat(conn, INV_READ|INV_WRITE); + if (lobjId == 0) { + fprintf(stderr, "can't create large object"); + } + + lobj_fd = lo_open(conn, lobjId, INV_WRITE); + /* + * read in from the Unix file and write to the inversion file + */ + while ((nbytes = read(fd, buf, BUFSIZE)) > 0) { + tmp = lo_write(conn, lobj_fd, buf, nbytes); + if (tmp < nbytes) { + fprintf(stderr, "error while reading \"%s\"", filename); + } + } + + (void) close(fd); + (void) lo_close(conn, lobj_fd); + + return lobjId; +} + +void pickout(PGconn *conn, Oid lobjId, int start, int len) +{ + int lobj_fd; + char* buf; + int nbytes; + int nread; + + lobj_fd = lo_open(conn, lobjId, INV_READ); + if (lobj_fd < 0) { + fprintf(stderr,"can't open large object %d", + lobjId); + } + + lo_lseek(conn, lobj_fd, start, SEEK_SET); + buf = malloc(len+1); + + nread = 0; + while (len - nread > 0) { + nbytes = lo_read(conn, lobj_fd, buf, len - nread); + buf[nbytes] = '\0'; + fprintf(stderr,">>> %s", buf); + nread += nbytes; + } + fprintf(stderr,"\n"); + lo_close(conn, lobj_fd); +} + +void overwrite(PGconn *conn, Oid lobjId, int start, int len) +{ + int lobj_fd; + char* buf; + int nbytes; + int nwritten; + int i; + + lobj_fd = lo_open(conn, lobjId, INV_READ); + if (lobj_fd < 0) { + fprintf(stderr,"can't open large object %d", + lobjId); + } + + lo_lseek(conn, lobj_fd, start, SEEK_SET); + buf = malloc(len+1); + + for (i=0;i<len;i++) + buf[i] = 'X'; + buf[i] = '\0'; + + nwritten = 0; + while (len - nwritten > 0) { + nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten); + nwritten += nbytes; + } + fprintf(stderr,"\n"); + lo_close(conn, lobj_fd); +} + + +/* + * exportFile - + * export large object "lobjOid" to file "out_filename" + * + */ +void exportFile(PGconn *conn, Oid lobjId, char *filename) +{ + int lobj_fd; + char buf[BUFSIZE]; + int nbytes, tmp; + int fd; + + /* + * create an inversion "object" + */ + lobj_fd = lo_open(conn, lobjId, INV_READ); + if (lobj_fd < 0) { + fprintf(stderr,"can't open large object %d", + lobjId); + } + + /* + * open the file to be written to + */ + fd = open(filename, O_CREAT|O_WRONLY, 0666); + if (fd < 0) { /* error */ + fprintf(stderr, "can't open unix file\"%s\"", + filename); + } + + /* + * read in from the Unix file and write to the inversion file + */ + while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0) { + tmp = write(fd, buf, nbytes); + if (tmp < nbytes) { + fprintf(stderr,"error while writing \"%s\"", + filename); + } + } + + (void) lo_close(conn, lobj_fd); + (void) close(fd); + + return; +} + +void +exit_nicely(PGconn* conn) +{ + PQfinish(conn); + exit(1); +} + +int +main(int argc, char **argv) +{ + char *in_filename, *out_filename; + char *database; + Oid lobjOid; + PGconn *conn; + PGresult *res; + + if (argc != 4) { + fprintf(stderr, "Usage: %s database_name in_filename out_filename\n", + argv[0]); + exit(1); + } + + database = argv[1]; + in_filename = argv[2]; + out_filename = argv[3]; + + /* + * set up the connection + */ + conn = PQsetdb(NULL, NULL, NULL, NULL, database); + + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) { + fprintf(stderr,"Connection to database '%s' failed.\n", database); + fprintf(stderr,"%s",PQerrorMessage(conn)); + exit_nicely(conn); + } + + res = PQexec(conn, "begin"); + PQclear(res); + printf("importing file \"%s\" ...\n", in_filename); +/* lobjOid = importFile(conn, in_filename); */ + lobjOid = lo_import(conn, in_filename); +/* + printf("\tas large object %d.\n", lobjOid); + + printf("picking out bytes 1000-2000 of the large object\n"); + pickout(conn, lobjOid, 1000, 1000); + + printf("overwriting bytes 1000-2000 of the large object with X's\n"); + overwrite(conn, lobjOid, 1000, 1000); +*/ + + printf("exporting large object to file \"%s\" ...\n", out_filename); +/* exportFile(conn, lobjOid, out_filename); */ + lo_export(conn, lobjOid,out_filename); + + res = PQexec(conn, "end"); + PQclear(res); + PQfinish(conn); + exit(0); +} +.fi |