aboutsummaryrefslogtreecommitdiff
path: root/doc/man/large_objects.3
diff options
context:
space:
mode:
Diffstat (limited to 'doc/man/large_objects.3')
-rw-r--r--doc/man/large_objects.3499
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