diff options
| author | Marc G. Fournier <scrappy@hub.org> | 1997-01-15 15:16:25 +0000 |
|---|---|---|
| committer | Marc G. Fournier <scrappy@hub.org> | 1997-01-15 15:16:25 +0000 |
| commit | 59bb41a235761a605708e7d6387518ea178a72d5 (patch) | |
| tree | 03e1d79e2e428c9ac68bf0004dd92870c06bc3f5 /doc/manual | |
| parent | f02bd9335010684a64fcd9bc0f86615839d14fc4 (diff) | |
| download | postgresql-59bb41a235761a605708e7d6387518ea178a72d5.tar.gz postgresql-59bb41a235761a605708e7d6387518ea178a72d5.zip | |
Import of PostgreSQL User Manual
Diffstat (limited to 'doc/manual')
| -rw-r--r-- | doc/manual/admin.html | 539 | ||||
| -rw-r--r-- | doc/manual/advanced.html | 237 | ||||
| -rw-r--r-- | doc/manual/appenda.html | 200 | ||||
| -rw-r--r-- | doc/manual/architec.html | 76 | ||||
| -rw-r--r-- | doc/manual/copy.html | 32 | ||||
| -rw-r--r-- | doc/manual/extend.html | 199 | ||||
| -rw-r--r-- | doc/manual/figure01.gif | bin | 0 -> 10020 bytes | |||
| -rw-r--r-- | doc/manual/figure02.gif | bin | 0 -> 6306 bytes | |||
| -rw-r--r-- | doc/manual/figure03.gif | bin | 0 -> 26163 bytes | |||
| -rw-r--r-- | doc/manual/intro.html | 201 | ||||
| -rw-r--r-- | doc/manual/libpq.html | 815 | ||||
| -rw-r--r-- | doc/manual/lobj.html | 429 | ||||
| -rw-r--r-- | doc/manual/pg95user.html | 154 | ||||
| -rw-r--r-- | doc/manual/query.html | 259 | ||||
| -rw-r--r-- | doc/manual/refs.html | 55 | ||||
| -rw-r--r-- | doc/manual/rules.html | 43 | ||||
| -rw-r--r-- | doc/manual/start.html | 231 | ||||
| -rw-r--r-- | doc/manual/xaggr.html | 109 | ||||
| -rw-r--r-- | doc/manual/xfunc.html | 474 | ||||
| -rw-r--r-- | doc/manual/xindex.html | 430 | ||||
| -rw-r--r-- | doc/manual/xoper.html | 70 | ||||
| -rw-r--r-- | doc/manual/xtypes.html | 148 |
22 files changed, 4701 insertions, 0 deletions
diff --git a/doc/manual/admin.html b/doc/manual/admin.html new file mode 100644 index 00000000000..be24aca1e60 --- /dev/null +++ b/doc/manual/admin.html @@ -0,0 +1,539 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - ADMINISTERING POSTGRES</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="rules.html">[ Previous ]</A> +<A HREF="refs.html">[ Next ]</A> +</font> +<HR> +<H1>15. ADMINISTERING POSTGRES</H1> +<HR> + In this section, we will discuss aspects of POSTGRES + that are of interest to those who make extensive use of + POSTGRES, or who are the site administrator for a group + of POSTGRES users. + +<H2>15.1. Frequent Tasks</H2> + Here we will briefly discuss some procedures that you + should be familiar with in managing any POSTGRES + installation. + +<H3>15.1.1. Starting the Postmaster</H3> + If you did not install POSTGRES exactly as described in + the installation instructions, you may have to perform + some additional steps before starting the postmaster + process. + <UL> + <LI>Even if you were not the person who installed POSTGRES, + you should understand the installation + instructions. The installation instructions explain + some important issues with respect to where POSTGRES + places some important files, proper settings for + environment variables, etc. that may vary from one + version of POSTGRES to another.<p> + <LI>You must start the postmaster process with the userid + that owns the installed database files. In most + cases, if you have followed the installation + instructions, this will be the user "<B>postgres</B>". If + you do not start the postmaster with the right userid, + the backend servers that are started by the + postmaster will not be able to read the data.<p> + <LI>Make sure that <CODE>/usr/local/postgres95/bin</CODE> is in your + shell command path, because the postmaster will use + your <B>PATH</B> to locate POSTGRES commands.<p> + <LI>Remember to set the environment variable <B>PGDATA</B> to + the directory where the POSTGRES databases are + installed. (This variable is more fully explained + in the POSTGRES installation instructions.)<p> + <LI>If you do start the postmaster using non-standard + options, such as a different TCP port number, remember + to tell all users so that they can set their + <B>PGPORT</B> environment variable correctly.<p> + </UL> + +<H3>15.1.2. Shutting Down the Postmaster</H3> + If you need to halt the postmaster process, you can use + the <B>UNIX</B> <B>kill(1)</B> command. Some people habitually use + the <B>-9</B> or <B>-KILL</B> option; this should never be necessary + and we do not recommend that you do this, as the postmaster + will be unable to free its various shared + resources, its child processes will be unable to exit + gracefully, etc. + +<H3>15.1.3. Adding and Removing Users</H3> + The createuser and destroyuser commands enable and disable + access to POSTGRES by specific users on the host + system. + +<H3>15.1.4. Periodic Upkeep</H3> + The vacuum command should be run on each database periodically. + This command processes deleted instances<A HREF="#9"><font size=-1>[9]</font></A> + and, more importantly, updates the system statistics + concerning the size of each class. If these statistics + are permitted to become out-of-date and inaccurate, the + POSTGRES query optimizer may make extremely poor decisions + with respect to query evaluation strategies. + Therefore, we recommend running vacuum every night or + so (perhaps in a script that is executed by the <B>UNIX</B> + <B>cron(1)</B> or <B>at(1)</B> commands). + Do frequent backups. That is, you should either back + up your database directories using the POSTGRES copy + command and/or the <B>UNIX</B> <B>dump(1)</B> or <B>tar(1)</B> commands. + You may think, "Why am I backing up my database? What + about crash recovery?" One side effect of the POSTGRES + "no overwrite" storage manager is that it is also a "no + log" storage manager. That is, the database log stores + only abort/commit data, and this is not enough information + to recover the database if the storage medium + (disk) or the database files are corrupted! In other + words, if a disk block goes bad or POSTGRES happens to + corrupt a database file, you cannot recover that file. + This can be disastrous if the file is one of the shared + catalogs, such as pg_database. + +<H3>15.1.5. Tuning</H3> + Once your users start to load a significant amount of + data, you will typically run into performance problems. + POSTGRES is not the fastest DBMS in the world, but many + of the worst problems encountered by users are due to + their lack of experience with any DBMS. Some general + tips include: + <OL> + <LI> Define indices over attributes that are commonly + used for qualifications. For example, if you + often execute queries of the form + +<pre> SELECT * from EMP where salary < 5000 +</pre> + then a B-tree index on the salary attribute will + probably be useful. If scans involving equality + are more common, as in + +<pre> SELECT * from EMP where salary = 5000 +</pre> + then you should consider defining a hash index + on salary. You can define both, though it will + use more disk space and may slow down updates a + bit. Scans using indices are much faster than + sequential scans of the entire class.<p> + <LI> Run the vacuum command a lot. This command + updates the statistics that the query optimizer + uses to make intelligent decisions; if the + statistics are inaccurate, the system will make + inordinately stupid decisions with respect to + the way it joins and scans classes.<p> + <LI> When specifying query qualfications (i.e., the + where part of the query), try to ensure that a + clause involving a constant can be turned into + one of the form range_variable operator constant, e.g., + +<pre> EMP.salary = 5000 +</pre> + The POSTGRES query optimizer will only use an + index with a constant qualification of this + form. It doesn't hurt to write the clause as + +<pre> 5000 = EMP.salary +</pre> + if the operator (in this case, =) has a commutator + operator defined so that POSTGRES can + rewrite the query into the desired form. However, + if such an operator does not exist, POSTGRES + will never consider the use of an index.<p> + <LI> When joining several classes together in one + query, try to write the join clauses in a + "chained" form, e.g., + +<pre> where A.a = B.b and B.b = C.c and ... +</pre> + Notice that relatively few clauses refer to a + given class and attribute; the clauses form a + linear sequence connecting the attributes, like + links in a chain. This is preferable to a query + written in a "star" form, such as + +<pre> where A.a = B.b and A.a = C.c and ... +</pre> + Here, many clauses refer to the same class and + attribute (in this case, A.a). When presented + with a query of this form, the POSTGRES query + optimizer will tend to consider far more choices + than it should and may run out of memory.<p> + <LI> If you are really desperate to see what query + plans look like, you can run the postmaster with + the -d option and then run monitor with the -t + option. The format in which query plans will be + printed is hard to read but you should be able + to tell whether any index scans are being performed.<br> +</OL> + +<H2>15.2. Infrequent Tasks</H2> + + At some time or another, every POSTGRES site + administrator has to perform all of the following actions. + +15.2.1. Cleaning Up After Crashes + The <B>postgres</B> server and the <B>postmaster</B> run as two + different processes. They may crash separately or + together. The housekeeping procedures required to fix + one kind of crash are different from those required to + fix the other. + The message you will usually see when the backend + server crashes is: + +<pre> FATAL: no response from backend: detected in ... +</pre> + This generally means one of two things: there is a bug + in the POSTGRES server, or there is a bug in some user + code that has been dynamically loaded into POSTGRES. + You should be able to restart your application and + resume processing, but there are some considerations: + <OL> + <LI> POSTGRES usually dumps a core file (a snapshot + of process memory used for debugging) in the + database directory +<pre> /usr/local/postgres95/data/base/<database>/core +</pre> + on the server machine. If you don't want to try + to debug the problem or produce a stack trace to + report the bug to someone else, you can delete + this file (which is probably around 10MB).<p> + <LI> When one backend crashes in an uncontrolled way + (i.e., without calling its built-in cleanup + routines), the postmaster will detect this situation, + kill all running servers and reinitialize + the state shared among all backends (e.g., the + shared buffer pool and locks). If your server + crashed, you will get the "no response" message + shown above. If your server was killed because + someone else's server crashed, you will see the + following message: + +<pre> I have been signalled by the postmaster. + Some backend process has died unexpectedly and possibly + corrupted shared memory. The current transaction was + aborted, and I am going to exit. Please resend the + last query. -- The postgres backend +</pre><br> + <LI> Sometimes shared state is not completely cleaned + up. Frontend applications may see errors of the + form: + +<pre> WARN: cannot write block 34 of myclass [mydb] blind +</pre> + In this case, you should kill the postmaster and + restart it.<p> + <LI> When the system crashes while updating the system + catalogs (e.g., when you are creating a + class, defining an index, retrieving into a + class, etc.) the B-tree indices defined on the + catalogs are sometimes corrupted. The general + (and non-unique) symptom is that all queries + stop working. If you have tried all of the + above steps and nothing else seems to work, try + using the reindexdb command. If reindexdb succeeds + but things still don't work, you have + another problem; if it fails, the system catalogs + themselves were almost certainly corrupted + and you will have to go back to your backups.<p> + </OL> + The postmaster does not usually crash (it doesn't do + very much except start servers) but it does happen on + occasion. In addition, there are a few cases where it + encounters problems during the reinitialization of + shared resources. Specifically, there are race conditions + where the operating system lets the postmaster + free shared resources but then will not permit it to + reallocate the same amount of shared resources (even + when there is no contention). + You will typically have to run the ipcclean command if + system errors cause the postmaster to crash. If this + happens, you may find (using the UNIX ipcs(1) command) + that the "<B>postgres</B>" user has shared memory and/or + semaphores allocated even though no postmaster process + is running. In this case, you should run ipcclean as + the "<B>postgres</B>" user in order to deallocate these + resources. Be warned that all such resources owned by + the "<B>postgres</B>" user will be deallocated. If you have + multiple postmaster processes running on the same + machine, you should kill all of them before running + ipcclean (otherwise, they will crash on their own when + their shared resources are suddenly deallocated). + +<H3>15.2.2. Moving Database Directories</H3> + By default, all POSTGRES databases are stored in + separate subdirectories under + <CODE>/usr/local/postgres95/data/base</CODE>.<A HREF="#10"><font size=-1>[10]</font></A> At some point, you + may find that you wish to move one or more databases to + another location (e.g., to a filesystem with more free + space). + If you wish to move all of your databases to the new + location, you can simply: + <UL> + <LI>Kill the postmaster.<p> + <LI>Copy the entire data directory to the new location + (making sure that the new files are owned by user + "<B>postgres</B>"). + +<pre> % cp -rp /usr/local/postgres95/data /new/place/data +</pre><p> + <LI>Reset your PGDATA environment variable (as described + earlier in this manual and in the installation + instructions). + +<pre> # using csh or tcsh... + % setenv PGDATA /new/place/data + + # using sh, ksh or bash... + % PGDATA=/new/place/data; export PGDATA + +</pre><p> + <LI>Restart the postmaster. + +<pre> % postmaster & +</pre><p> + <LI>After you run some queries and are sure that the + newly-moved database works, you can remove the old + data directory. +<pre> % rm -rf /usr/local/postgres95/data +</pre><p> +</UL> + To install a single database in an alternate directory + while leaving all other databases in place, do the following: +<UL> + <LI>Create the database (if it doesn't already exist) + using the createdb command. In the following steps + we will assume the database is named foo.<p> + <LI>Kill the postmaster.<p> + <LI>Copy the directory + <CODE>/usr/local/postgres95/data/base/foo</CODE> and its contents + to its ultimate destination. It should still be + owned by the "<B>postgres</B>" user. + +<pre> % cp -rp /usr/local/postgres95/data/base/foo /new/place/foo +</pre> + <LI>Remove the directory + <CODE>/usr/local/postgres95/data/base/foo</CODE>: + +<pre> % rm -rf /usr/local/postgres95/data/base/foo +</pre> + <LI>Make a symbolic link from + <CODE>/usr/local/postgres95/data/base</CODE> to the new directory: + +<pre> % ln -s /new/place/foo /usr/local/postgres95/data/base/foo +</pre> + <LI>Restart the postmaster. +</UL> +<p> +<H3>15.2.3. Updating Databases</H3> + POSTGRES is a research system. In general, POSTGRES + may not retain the same binary format for the storage + of databases from release to release. Therefore, when + you update your POSTGRES software, you will probably + have to modify your databases as well. This is a common + occurrence with commercial database systems as + well; unfortunately, unlike commercial systems, POSTGRES + does not come with user-friendly utilities to make + your life easier when these updates occur. + In general, you must do the following to update your + databases to a new software release: + <UL> + <LI>Extensions (such as user-defined types, functions, + aggregates, etc.) must be reloaded by re-executing + the <B>SQL CREATE</B> commands. See Appendix A for more + details. + <LI>Data must be dumped from the old classes into ASCII + files (using the <B>COPY</B> command), the new classes created + in the new database (using the <B>CREATE TABLE</B> + command), and the data reloaded from the ASCII files. + <LI>Rules and views must also be reloaded by + reexecuting the various CREATE commands. + </UL> + You should give any new release a "trial period"; in + particular, do not delete the old database until you + are satisfied that there are no compatibility problems + with the new software. For example, you do not want to + discover that a bug in a type's "input" (conversion + from ASCII) and "output" (conversion to ASCII) routines + prevents you from reloading your data after you have + destroyed your old databases! (This should be standard + procedure when updating any software package, but some + people try to economize on disk space without applying + enough foresight.) + +<H2>15.3. Database Security</H2> + + Most sites that use POSTGRES are educational or + research institutions and do not pay much attention to + security in their POSTGRES installations. If desired, + one can install POSTGRES with additional security + features. Naturally, such features come with additional + administrative overhead that must be dealt with. + +<H3>15.3.1. Kerberos</H3> + POSTGRES can be configured to use the <B>MIT</B> <B>Kerberos</B> network + authentication system. This prevents outside + users from connecting to your databases over the network + without the correct authentication information. +<p> +<H2>15.4. Querying the System Catalogs</H2> + As an administrator (or sometimes as a plain user), you + want to find out what extensions have been added to a + given database. The queries listed below are "canned" + queries that you can run on any database to get simple + answers. Before executing any of the queries below, be + sure to execute the POSTGRES <B>vacuum</B> command. (The + queries will run much more quickly that way.) Also, + note that these queries are also listed in +<pre> /usr/local/postgres95/tutorial/syscat.sql +</pre> + so use cut-and-paste (or the <B>\i</B> command) instead of + doing a lot of typing. + This query prints the names of all database adminstrators + and the name of their database(s). +<pre> SELECT usename, datname + FROM pg_user, pg_database + WHERE usesysid = int2in(int4out(datdba)) + ORDER BY usename, datname; +</pre> + This query lists all user-defined classes in the + database. +<pre> SELECT relname + FROM pg_class + WHERE relkind = 'r' -- not indices + and relname !~ '^pg_' -- not catalogs + and relname !~ '^Inv' -- not large objects + ORDER BY relname; +</pre> + This query lists all simple indices (i.e., those that + are not defined over a function of several attributes). +<pre> SELECT bc.relname AS class_name, + ic.relname AS index_name, + a.attname + FROM pg_class bc, -- base class + pg_class ic, -- index class + pg_index i, + pg_attribute a -- att in base + WHERE i.indrelid = bc.oid + and i.indexrelid = ic.oid + and i.indkey[0] = a.attnum + and a.attrelid = bc.oid + and i.indproc = '0'::oid -- no functional indices + ORDER BY class_name, index_name, attname; +</pre> + This query prints a report of the user-defined + attributes and their types for all user-defined classes + in the database. +<pre> SELECT c.relname, a.attname, t.typname + FROM pg_class c, pg_attribute a, pg_type t + WHERE c.relkind = 'r' -- no indices + and c.relname !~ '^pg_' -- no catalogs + and c.relname !~ '^Inv' -- no large objects + and a.attnum > 0 -- no system att's + and a.attrelid = c.oid + and a.atttypid = t.oid + ORDER BY relname, attname; +</pre> + This query lists all user-defined base types (not + including array types). +<pre> SELECT u.usename, t.typname + FROM pg_type t, pg_user u + WHERE u.usesysid = int2in(int4out(t.typowner)) + and t.typrelid = '0'::oid -- no complex types + and t.typelem = '0'::oid -- no arrays + and u.usename <> 'postgres' + ORDER BY usename, typname; +</pre> + This query lists all left-unary (post-fix) operators. +<pre> SELECT o.oprname AS left_unary, + right.typname AS operand, + result.typname AS return_type + FROM pg_operator o, pg_type right, pg_type result + WHERE o.oprkind = 'l' -- left unary + and o.oprright = right.oid + and o.oprresult = result.oid + ORDER BY operand; +</pre> + This query lists all right-unary (pre-fix) operators. +<pre> SELECT o.oprname AS right_unary, + left.typname AS operand, + result.typname AS return_type + FROM pg_operator o, pg_type left, pg_type result + WHERE o.oprkind = 'r' -- right unary + and o.oprleft = left.oid + and o.oprresult = result.oid + ORDER BY operand; +</pre> + This query lists all binary operators. +<pre> SELECT o.oprname AS binary_op, + left.typname AS left_opr, + right.typname AS right_opr, + result.typname AS return_type + FROM pg_operator o, pg_type left, pg_type right, pg_type result + WHERE o.oprkind = 'b' -- binary + and o.oprleft = left.oid + and o.oprright = right.oid + and o.oprresult = result.oid + ORDER BY left_opr, right_opr; +</pre> + This query returns the name, number of arguments + (parameters) and return type of all user-defined C + functions. The same query can be used to find all + built-in C functions if you change the "<B>C</B>" to "<B>internal</B>", + or all <B>SQL</B> functions if you change the "<B>C</B>" to + "<B>sql</B>". +<pre> SELECT p.proname, p.pronargs, t.typname + FROM pg_proc p, pg_language l, pg_type t + WHERE p.prolang = l.oid + and p.prorettype = t.oid + and l.lanname = 'c' + ORDER BY proname; +</pre> + This query lists all of the aggregate functions that + have been installed and the types to which they can be + applied. count is not included because it can take any + type as its argument. +<pre> SELECT a.aggname, t.typname + FROM pg_aggregate a, pg_type t + WHERE a.aggbasetype = t.oid + ORDER BY aggname, typname; +</pre> + This query lists all of the operator classes that can + be used with each access method as well as the operators + that can be used with the respective operator + classes. +<pre> SELECT am.amname, opc.opcname, opr.oprname + FROM pg_am am, pg_amop amop, pg_opclass opc, pg_operator opr + WHERE amop.amopid = am.oid + and amop.amopclaid = opc.oid + and amop.amopopr = opr.oid + ORDER BY amname, opcname, oprname; +</pre> +<p> + +<HR> +<A NAME="9"><B>9.</B></A> +This may mean different things depending on the archive +mode with which each class has been created. However, the +current implementation of the vacuum command does not perform any compaction or clustering of data. Therefore, the +UNIX files which store each POSTGRES class never shrink and +the space "reclaimed" by vacuum is never actually reused. + +<HR width=50 align=left> +<A NAME="10"><B>10.</B></A> +Data for certain classes may stored elsewhere if a +non-standard storage manager was specified when they were +created. Use of non-standard storage managers is an experimental feature that is not supported outside of Berkeley. +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="rules.html">[ Previous ]</A> +<A HREF="refs.html">[ Next ]</A> +</font> + + diff --git a/doc/manual/advanced.html b/doc/manual/advanced.html new file mode 100644 index 00000000000..35ae6744bb7 --- /dev/null +++ b/doc/manual/advanced.html @@ -0,0 +1,237 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - ADVANCED POSTGRES SQL FEATURES</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="query.html">[ Previous ]</A> +<A HREF="extend.html">[ Next ]</A> +</font> +<HR> +<H1>5. ADVANCED POSTGRES <B>SQL</B> FEATURES</H1> +<HR> + Having covered the basics of using POSTGRES <B>SQL</B> to + access your data, we will now discuss those features of + POSTGRES that distinguish it from conventional data + managers. These features include inheritance, time + travel and non-atomic data values (array- and + set-valued attributes). + Examples in this section can also be found in + <CODE>advance.sql</CODE> in the tutorial directory. (Refer to the + introduction of the <A HREF="query.html">previous chapter</A> for how to use + it.) + +<H2><A NAME="inheritance">5.1. Inheritance</A></H2> + Let's create two classes. The capitals class contains + state capitals which are also cities. Naturally, the + capitals class should inherit from cities. + +<pre> CREATE TABLE cities ( + name text, + population float, + altitude int -- (in ft) + ); + + CREATE TABLE capitals ( + state char2 + ) INHERITS (cities); +</pre> + In this case, an instance of capitals <B>inherits</B> all + attributes (name, population, and altitude) from its + parent, cities. The type of the attribute name is + <B>text</B>, a built-in POSTGRES type for variable length + ASCII strings. The type of the attribute population is + <B>float4</B>, a built-in POSTGRES type for double precision + floating point numbres. State capitals have an extra + attribute, state, that shows their state. In POSTGRES, + a class can inherit from zero or more other classes,<A HREF="#4"><font size=-1>[4]</font></A> + and a query can reference either all instances of a + class or all instances of a class plus all of its + descendants. For example, the following query finds + all the cities that are situated at an attitude of 500 + 'ft or higher: + +<pre> SELECT name, altitude + FROM cities + WHERE altitude > 500; + + + +----------+----------+ + |name | altitude | + +----------+----------+ + |Las Vegas | 2174 | + +----------+----------+ + |Mariposa | 1953 | + +----------+----------+ +</pre> + On the other hand, to find the names of all cities, + including state capitals, that are located at an altitude + over 500 'ft, the query is: + +<pre> SELECT c.name, c.altitude + FROM cities* c + WHERE c.altitude > 500; +</pre> + which returns: + +<pre> +----------+----------+ + |name | altitude | + +----------+----------+ + |Las Vegas | 2174 | + +----------+----------+ + |Mariposa | 1953 | + +----------+----------+ + |Madison | 845 | + +----------+----------+ +</pre> + Here the * after cities indicates that the query should + be run over cities and all classes below cities in the + inheritance hierarchy. Many of the commands that we + have already discussed -- select, update and delete -- + support this * notation, as do others, like alter command. + +<H2><A NAME="time-travel">5.2. Time Travel</A></H2> + POSTGRES supports the notion of time travel. This feature + allows a user to run historical queries. For + example, to find the current population of Mariposa + city, one would query: + +<pre> SELECT * FROM cities WHERE name = 'Mariposa'; + + +---------+------------+----------+ + |name | population | altitude | + +---------+------------+----------+ + |Mariposa | 1320 | 1953 | + +---------+------------+----------+ +</pre> + POSTGRES will automatically find the version of Mariposa's + record valid at the current time. + One can also give a time range. For example to see the + past and present populations of Mariposa, one would + query: + +<pre> SELECT name, population + FROM cities['epoch', 'now'] + WHERE name = 'Mariposa'; +</pre> + where "epoch" indicates the beginning of the system + clock.<A HREF="#5"><font size=-1>[5]</font></A> If you have executed all of the examples so + far, then the above query returns: + +<pre> +---------+------------+ + |name | population | + +---------+------------+ + |Mariposa | 1200 | + +---------+------------+ + |Mariposa | 1320 | + +---------+------------+ +</pre> + The default beginning of a time range is the earliest + time representable by the system and the default end is + the current time; thus, the above time range can be + abbreviated as ``[,].'' + +<H2><A NAME="non-atomic-values">5.3. Non-Atomic Values</A></H2> + One of the tenets of the relational model is that the + attributes of a relation are atomic. POSTGRES does not + have this restriction; attributes can themselves contain + sub-values that can be accessed from the query + language. For example, you can create attributes that + are arrays of base types. + +<H3><A NAME="arrays">5.3.1. Arrays</A></H3> + POSTGRES allows attributes of an instance to be defined + as fixed-length or variable-length multi-dimensional + arrays. Arrays of any base type or user-defined type + can be created. To illustrate their use, we first create a + class with arrays of base types. + +<pre> * CREATE TABLE SAL_EMP ( + name text, + pay_by_quarter int4[], + schedule char16[][] + ); +</pre> + The above query will create a class named SAL_EMP with + a <B>text</B> string (name), a one-dimensional array of <B>int4</B> + (pay_by_quarter), which represents the employee's + salary by quarter and a two-dimensional array of <B>char16</B> + (schedule), which represents the employee's weekly + schedule. Now we do some <B>INSERTS</B>s; note that when + appending to an array, we enclose the values within + braces and separate them by commas. If you know <B>C</B>, + this is not unlike the syntax for initializing structures. + +<pre> INSERT INTO SAL_EMP + VALUES ('Bill', + '{10000, 10000, 10000, 10000}', + '{{"meeting", "lunch"}, {}}'); + + INSERT INTO SAL_EMP + VALUES ('Carol', + '{20000, 25000, 25000, 25000}', + '{{"talk", "consult"}, {"meeting"}}'); +</pre> + By default, POSTGRES uses the "one-based" numbering + convention for arrays -- that is, an array of n elements starts with array[1] and ends with array[n]. + Now, we can run some queries on SAL_EMP. First, we + show how to access a single element of an array at a + time. This query retrieves the names of the employees + whose pay changed in the second quarter: + +<pre> * SELECT name + FROM SAL_EMP + WHERE SAL_EMP.pay_by_quarter[1] <> + SAL_EMP.pay_by_quarter[2]; + + +------+ + |name | + +------+ + |Carol | + +------+ +</pre> + This query retrieves the third quarter pay of all + employees: + +<pre> * SELECT SAL_EMP.pay_by_quarter[3] FROM SAL_EMP; + + + +---------------+ + |pay_by_quarter | + +---------------+ + |10000 | + +---------------+ + |25000 | + +---------------+ +</pre> + We can also access arbitrary slices of an array, or + subarrays. This query retrieves the first item on + Bill's schedule for the first two days of the week. + +<pre> * SELECT SAL_EMP.schedule[1:2][1:1] + FROM SAL_EMP + WHERE SAL_EMP.name = 'Bill'; + + +-------------------+ + |schedule | + +-------------------+ + |{{"meeting"},{""}} | + +-------------------+ + +</pre> +<p> +<HR> +<A NAME="4"><B>4.</B></A> i.e., the inheritance hierarchy is a directed acyclic +graph.<br> +<A NAME="5"><B>5.</B></A> On UNIX systems, this is always midnight, January 1, +1970 GMT.<br> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="query.html">[ Previous ]</A> +<A HREF="extend.html">[ Next ]</A> +</font> + diff --git a/doc/manual/appenda.html b/doc/manual/appenda.html new file mode 100644 index 00000000000..6049e85f712 --- /dev/null +++ b/doc/manual/appenda.html @@ -0,0 +1,200 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - Appendix A:</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="refs.html">[ Previous ]</A> +[ Next ] +</font> +<HR> +<H1>Appendix A: Linking Dynamically-Loaded Functions</H1> +<HR> + After you have created and registered a user-defined + function, your work is essentially done. POSTGRES, + however, must load the object code (e.g., a .o file, or + a shared library) that implements your function. As + previously mentioned, POSTGRES loads your code at + runtime, as required. In order to allow your code to be + dynamically loaded, you may have to compile and + linkedit it in a special way. This section briefly + describes how to perform the compilation and + linkediting required before you can load your user-defined + functions into a running POSTGRES server. Note that + this process has changed as of Version 4.2.<A HREF="#11">11</A> You + should expect to read (and reread, and re-reread) the + manual pages for the C compiler, cc(1), and the link + editor, ld(1), if you have specific questions. In + addition, the regression test suites in the directory + /usr/local/postgres95/src/regress contain several + working examples of this process. If you copy what these + tests do, you should not have any problems. + The following terminology will be used below: + <DL> + <DT>Dynamic loading + <DD>is what POSTGRES does to an object file. The + object file is copied into the running POSTGRES + server and the functions and variables within the + file are made available to the functions within + the POSTGRES process. POSTGRES does this using + the dynamic loading mechanism provided by the + operating system. + + <DT>Loading and link editing + <DD>is what you do to an object file in order to produce + another kind of object file (e.g., an executable + program or a shared library). You perform + this using the link editing program, ld(1). + </DL> +<p> + The following general restrictions and notes also apply + to the discussion below. + <UL> + <LI>Paths given to the create function command must be + absolute paths (i.e., start with "/") that refer to + directories visible on the machine on which the + POSTGRES server is running.<A HREF="#12">12</A> + <LI>The POSTGRES user must be able to traverse the path + given to the create function command and be able to + read the object file. This is because the POSTGRES + server runs as the POSTGRES user, not as the user + who starts up the frontend process. (Making the + file or a higher-level directory unreadable and/or + unexecutable by the "postgres" user is an extremely + common mistake.) + <LI>Symbol names defined within object files must not + conflict with each other or with symbols defined in + POSTGRES. + <LI>The GNU C compiler usually does not provide the special + options that are required to use the operating + system's dynamic loader interface. In such cases, + the C compiler that comes with the operating system + must be used. + </UL> +<p> +<B>ULTRIX</B><br> + It is very easy to build dynamically-loaded object + files under ULTRIX. ULTRIX does not have any sharedlibrary + mechanism and hence does not place any restrictions on + the dynamic loader interface. On the other + hand, we had to (re)write a non-portable dynamic loader + ourselves and could not use true shared libraries. + Under ULTRIX, the only restriction is that you must + produce each object file with the option -G 0. (Notice + that that's the numeral ``0'' and not the letter + ``O''). For example, + +<pre> # simple ULTRIX example + % cc -G 0 -c foo.c +</pre> + produces an object file called foo.o that can then be + dynamically loaded into POSTGRES. No additional loading or link-editing must be performed. +<p> +<B>DEC OSF/1</B><br> + Under DEC OSF/1, you can take any simple object file + and produce a shared object file by running the ld command over it with the correct options. The commands to + do this look like: + +<pre> # simple DEC OSF/1 example + % cc -c foo.c + % ld -shared -expect_unresolved '*' -o foo.so foo.o +</pre> + The resulting shared object file can then be loaded + into POSTGRES. When specifying the object file name to + the create function command, one must give it the name + of the shared object file (ending in .so) rather than + the simple object file.<A HREF="#13">13</A> If the file you specify is + not a shared object, the backend will hang! +<p> +<B>SunOS 4.x, Solaris 2.x and HP-UX</B><br> + Under both SunOS 4.x, Solaris 2.x and HP-UX, the simple + object file must be created by compiling the source + file with special compiler flags and a shared library + must be produced. + The necessary steps with HP-UX are as follows. The +z + flag to the HP-UX C compiler produces so-called + "Position Independent Code" (PIC) and the +u flag + removes + some alignment restrictions that the PA-RISC architecture + normally enforces. The object file must be turned + into a shared library using the HP-UX link editor with + the -b option. This sounds complicated but is actually + very simple, since the commands to do it are just: +<pre> # simple HP-UX example + % cc +z +u -c foo.c + % ld -b -o foo.sl foo.o +</pre> + + As with the .so files mentioned in the last subsection, + the create function command must be told which file is + the correct file to load (i.e., you must give it the + location of the shared library, or .sl file). + Under SunOS 4.x, the commands look like: + +<pre> # simple SunOS 4.x example + % cc -PIC -c foo.c + % ld -dc -dp -Bdynamic -o foo.so foo.o +</pre> + and the equivalent lines under Solaris 2.x are: +<pre> # simple Solaris 2.x example + % cc -K PIC -c foo.c + or + % gcc -fPIC -c foo.c + % ld -G -Bdynamic -o foo.so foo.o +</pre> + When linking shared libraries, you may have to specify + some additional shared libraries (typically system + libraries, such as the C and math libraries) on your ld + command line. +<HR> +<A NAME="11"><B>11.</B></A> The old POSTGRES dynamic +loading mechanism required +in-depth knowledge in terms of executable format, placement +and alignment of executable instructions within memory, etc. +on the part of the person writing the dynamic loader. Such +loaders tended to be slow and buggy. As of Version 4.2, the +POSTGRES dynamic loading mechanism has been rewritten to use +the dynamic loading mechanism provided by the operating +system. This approach is generally faster, more reliable and +more portable than our previous dynamic loading mechanism. +The reason for this is that nearly all modern versions of +UNIX use a dynamic loading mechanism to implement shared +libraries and must therefore provide a fast and reliable +mechanism. On the other hand, the object file must be +postprocessed a bit before it can be loaded into POSTGRES. We +hope that the large increase in speed and reliability will +make up for the slight decrease in convenience. +<hr width=50 align=left> +<A NAME="12"><B>12.</B></A> Relative paths do in fact work, +but are relative to +the directory where the database resides (which is generally +invisible to the frontend application). Obviously, it makes +no sense to make the path relative to the directory in which +the user started the frontend application, since the server +could be running on a completely different machine!<br> +<hr width=50 align=left> +<A NAME="13"><B>13.</B></A> Actually, POSTGRES does not care +what you name the +file as long as it is a shared object file. If you prefer +to name your shared object files with the extension .o, this +is fine with POSTGRES so long as you make sure that the correct +file name is given to the create function command. In +other words, you must simply be consistent. However, from a +pragmatic point of view, we discourage this practice because +you will undoubtedly confuse yourself with regards to which +files have been made into shared object files and which have +not. For example, it's very hard to write Makefiles to do +the link-editing automatically if both the object file and +the shared object file end in .o!<br> + +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="refs.html">[ Previous ]</A> +[ Next ] +</font> +</BODY> +</HTML> diff --git a/doc/manual/architec.html b/doc/manual/architec.html new file mode 100644 index 00000000000..65c6a3e2b47 --- /dev/null +++ b/doc/manual/architec.html @@ -0,0 +1,76 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - ARCHITECTURE</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="intro.html">[ Previous ]</A> +<A HREF="start.html">[ Next ]</A> +</font> +<HR> +<H1>2. POSTGRES ARCHITECTURE CONCEPTS</H1> +<HR> + Before we continue, you should understand the basic + POSTGRES system architecture. Understanding how the + parts of POSTGRES interact will make the next chapter + somewhat clearer. + In database jargon, POSTGRES uses a simple "process + per-user" client/server model. A POSTGRES session + consists of the following cooperating UNIX processes (programs): +<UL> + <LI>A supervisory daemon process (the <B>postmaster</B>), + <LI>the user's frontend application (e.g., the <B>psql</B> program), and + <LI>the one or more backend database servers (the <B>postgres</B> process itself). +</UL> + A single <B>postmaster</B> manages a given collection of + databases on a single host. Such a collection of + databases is called an installation or site. Frontend + applications that wish to access a given database + within an installation make calls to the library. + The library sends user requests over the network to the + <B>postmaster</B> (Figure 1(a)), which in turn starts a new + backend server process (Figure 1(b)) + + <IMG SRC="figure01.gif" ALIGN=right ALT="Figure 1- How a connection is established"><br> + + and connects the + frontend process to the new server (Figure 1(c)). From + that point on, the frontend process and the backend + server communicate without intervention by the + <B>postmaster</B>. Hence, the <B>postmaster</B> is always running, waiting + for requests, whereas frontend and backend processes + come and go. The <B>LIBPQ</B> library allows a single + frontend to make multiple connections to backend processes. + However, the frontend application is still a + single-threaded process. Multithreaded frontend/backend + connections are not currently supported in <B>LIBPQ</B>. + One implication of this architecture is that the + <B>postmaster</B> and the backend always run on the same + machine (the database server), while the frontend + application may run anywhere. You should keep this + in mind, + because the files that can be accessed on a client + machine may not be accessible (or may only be accessed + using a different filename) on the database server + machine. + You should also be aware that the <B>postmaster</B> and + postgres servers run with the user-id of the POSTGRES + "superuser." Note that the POSTGRES superuser does not + have to be a special user (e.g., a user named + "postgres"). Furthermore, the POSTGRES superuser + should + definitely not be the UNIX superuser, "root"! In any + case, all files relating to a database should belong to + this POSTGRES superuser. + +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="intro.html">[ Previous ]</A> +<A HREF="start.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/copy.html b/doc/manual/copy.html new file mode 100644 index 00000000000..387334414b6 --- /dev/null +++ b/doc/manual/copy.html @@ -0,0 +1,32 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 - Copyright</TITLE> +</HEAD> + +<BODY> +<H1 align=center>The <B>POSTGRES95</B> - Copyright</H1> +<HR> + <B>POSTGRES95</B> is copyright (C) 1994-5 by the Regents of the +University of California. Permission to use, copy, modify, +and distribute this software and its documentation for any +purpose, without fee, and without a written agreement is +hereby granted, provided that the above copyright notice and +this paragraph and the following two paragraphs appear in +all copies.<p> +<b> IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE + LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, + INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST + PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND + ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA + HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + THE UNIVERSITY OF CALIFORNIA SPECIFICALLY + DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, + THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS + FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED + HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF + CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, + SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. +</b> + +</BODY> +</HTML> diff --git a/doc/manual/extend.html b/doc/manual/extend.html new file mode 100644 index 00000000000..a3cdfc06211 --- /dev/null +++ b/doc/manual/extend.html @@ -0,0 +1,199 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - EXTENDING SQL: AN OVERVIEW</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="advanced.html">[ Previous ]</A> +<A HREF="xfunc.html">[ Next ]</A> +</font> +<HR> + +<H1>6. EXTENDING SQL: AN OVERVIEW</H1> +<HR> + In the sections that follow, we will discuss how you + can extend the POSTGRES <B>SQL</B> query language by adding: + <UL> + <LI>functions + <LI>types + <LI>operators + <LI>aggregates + </UL> +<p> +<H2><A NAME="how-extensibility-works">6.1. How Extensibility Works</A></H2> + POSTGRES is extensible because its operation is + catalog-driven. If you are familiar with standard + relational systems, you know that they store information + about databases, tables, columns, etc., in what are + commonly known as system catalogs. (Some systems call + this the data dictionary). The catalogs appear to the + user as classes, like any other, but the DBMS stores + its internal bookkeeping in them. One key difference + between POSTGRES and standard relational systems is + that POSTGRES stores much more information in its + catalogs -- not only information about tables and columns, + but also information about its types, functions, access + methods, and so on. These classes can be modified by + the user, and since POSTGRES bases its internal operation + on these classes, this means that POSTGRES can be + extended by users. By comparison, conventional + database systems can only be extended by changing hardcoded + procedures within the DBMS or by loading modules + specially-written by the DBMS vendor. + POSTGRES is also unlike most other data managers in + that the server can incorporate user-written code into + itself through dynamic loading. That is, the user can + specify an object code file (e.g., a compiled .o file + or shared library) that implements a new type or function + and POSTGRES will load it as required. Code written + in <B>SQL</B> are even more trivial to add to the server. + This ability to modify its operation "on the fly" makes + POSTGRES uniquely suited for rapid prototyping of new + applications and storage structures. + +<H2><A NAME="the-postgres-type-system">6.2. The POSTGRES Type System</A></H2> + The POSTGRES type system can be broken down in several + ways. + Types are divided into base types and composite types. + Base types are those, like <CODE>int4</CODE>, that are implemented + in a language such as <B>C</B>. They generally correspond to + what are often known as "abstract data types"; POSTGRES + can only operate on such types through methods provided + by the user and only understands the behavior of such + types to the extent that the user describes them. + Composite types are created whenever the user creates a + class. EMP is an example of a composite type. + POSTGRES stores these types in only one way (within the + file that stores all instances of the class) but the + user can "look inside" at the attributes of these types + from the query language and optimize their retrieval by + (for example) defining indices on the attributes. + POSTGRES base types are further divided into built-in + types and user-defined types. Built-in types (like + <CODE>int4</CODE>) are those that are compiled into the system. + User-defined types are those created by the user in the + manner to be described below. + +<H2><A NAME="about-the-postgres-system-catalogs">6.3. About the POSTGRES System Catalogs</A></H2> + Having introduced the basic extensibility concepts, we + can now take a look at how the catalogs are actually + laid out. You can skip this section for now, but some + later sections will be incomprehensible without the + information given here, so mark this page for later + reference. + All system catalogs have names that begin with <CODE>pg_</CODE>. + The following classes contain information that may be + useful to the end user. (There are many other system + catalogs, but there should rarely be a reason to query + them directly.) + <p> +<center> +<table border=1> +<tr> + <th>catalog name</th><th> description </th> +</tr> +<tr> + <td><CODE>pg_database</CODE> </td><td> databases </td> +</tr> +<tr> + <td><CODE>pg_class</CODE> </td><td> classes </td> +</tr> +<tr> + <td><CODE>pg_attribute</CODE> </td><td> class attributes </td> + </tr> +<tr> + <td><CODE>pg_index</CODE> </td><td> secondary indices </td> +</tr> +<tr> +</tr> +<tr> + <td><CODE>pg_proc</CODE> </td><td> procedures (both C and SQL) </td> +</tr> +<tr> + <td><CODE>pg_type</CODE> </td><td> types (both base and complex) </td> +</tr> +<tr> + <td><CODE>pg_operator</CODE> </td><td> operators </td> +</tr> +<tr> + <td><CODE>pg_aggregate</CODE> </td><td> aggregates and aggregate functions </td> +</tr> +<tr> +</tr> +<tr> +</tr> +<tr> + <td><CODE>pg_am</CODE> </td><td> access methods </td> +</tr> +<tr> + <td><CODE>pg_amop</CODE> </td><td> access method operators </td> +</tr> +<tr> + <td><CODE>pg_amproc</CODE> </td><td> access method support functions </td> +</tr> +<tr> + <td><CODE>pg_opclass</CODE> </td><td> access method operator classes </td> +</tr> +</table> +</center> + +<p> + <IMG SRC="figure03.gif" + ALT="Figure 3. The major POSTGRES system catalogs"> + The Reference Manual gives a more detailed explanation + of these catalogs and their attributes. However, Figure 3 + shows the major entities and their relationships + in the system catalogs. (Attributes that do not refer + to other entities are not shown unless they are part of + a primary key.) + This diagram is more or less incomprehensible until you + actually start looking at the contents of the catalogs + and see how they relate to each other. For now, the + main things to take away from this diagram are as follows: + + <OL> + <LI> In several of the sections that follow, we will + present various join queries on the system + catalogs that display information we need to extend + the system. Looking at this diagram should make + some of these join queries (which are often + three- or four-way joins) more understandable, + because you will be able to see that the + attributes used in the queries form foreign keys + in other classes. + <LI> Many different features (classes, attributes, + functions, types, access methods, etc.) are + tightly integrated in this schema. A simple + create command may modify many of these catalogs. + <LI> Types and procedures <A HREF="#6"><font size=-1>[6]</font></A> + are central to the schema. + Nearly every catalog contains some reference to + instances in one or both of these classes. For + example, POSTGRES frequently uses type + signatures (e.g., of functions and operators) to + identify unique instances of other catalogs. + <LI> There are many attributes and relationships that + have obvious meanings, but there are many + (particularly those that have to do with access + methods) that do not. The relationships between + <CODE>pg_am, pg_amop, pg_amproc, pg_operator</CODE> and + <CODE>pg_opclass</CODE> are particularly hard to understand + and will be described in depth (in the section + on interfacing types and operators to indices) + after we have discussed basic extensions. +</OL> +<p> +<HR> +<A NAME="6"><B>6.</B></A> We use the words <I>procedure</I> and <I>function</I> more or less +interchangably. +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="advanced.html">[ Previous ]</A> +<A HREF="xfunc.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/figure01.gif b/doc/manual/figure01.gif Binary files differnew file mode 100644 index 00000000000..58337e2595f --- /dev/null +++ b/doc/manual/figure01.gif diff --git a/doc/manual/figure02.gif b/doc/manual/figure02.gif Binary files differnew file mode 100644 index 00000000000..bdf3925c9a7 --- /dev/null +++ b/doc/manual/figure02.gif diff --git a/doc/manual/figure03.gif b/doc/manual/figure03.gif Binary files differnew file mode 100644 index 00000000000..24e3187e6c6 --- /dev/null +++ b/doc/manual/figure03.gif diff --git a/doc/manual/intro.html b/doc/manual/intro.html new file mode 100644 index 00000000000..cf7b75af57b --- /dev/null +++ b/doc/manual/intro.html @@ -0,0 +1,201 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - Introduction</TITLE> +</HEAD> + +<BODY> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +[ Previous ] +<A HREF="architec.html">[ Next ]</A> +</font> +<HR> +<H1>1. INTRODUCTION</H1> +<HR> + This document is the user manual for the + <A HREF="http://s2k-ftp.cs.berkeley.edu:8000/postgres95/"><B>POSTGRES95</B></A> + database management system developed at the University + of California at Berkeley. <B>POSTGRES95</B> is based on + <A HREF="http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/postgres.html"> + <B>POSTGRES release 4.2</B></A>. The POSTGRES project, + led by Professor Michael Stonebraker, has been sponsored by the + Defense Advanced Research Projects Agency (DARPA), the + Army Research Office (ARO), the National Science + Foundation (NSF), and ESL, Inc. +<H2>1.1. What is POSTGRES?</H2> + Traditional relational database management systems + (DBMSs) support a data model consisting of a collection + of named relations, containing attributes of a specific + type. In current commercial systems, possible types + include floating point numbers, integers, character + strings, money, and dates. It is commonly recognized + that this model is inadequate for future data + processing applications. + The relational model successfully replaced previous + models in part because of its "Spartan simplicity". + However, as mentioned, this simplicity often makes the + implementation of certain applications very difficult + to implement. POSTGRES offers substantial additional + power by incorporating the following four additional + basic constructs in such a way that users can easily + extend the system: +<p> +<PRE> classes + inheritance + types + functions +</PRE><p> + In addition, POSTGRES supports a powerful production + rule system. + +<H2><A NAME="a-short-history-of-the-postgres-project">1.2. A Short History of the POSTGRES Project</A></H2> + Implementation of the POSTGRES DBMS began in 1986. The + initial concepts for the system were presented in + <A HREF="refs.html#STON86">[STON86]</A> and the definition of the initial data model + appeared in <A HREF="refs.html#ROW87">[ROWE87]</A>. The design of the rule system at + that time was described in <A HREF="refs.html#STON87a">[STON87a]</A>. The rationale + and architecture of the storage manager were detailed + in <A HREF="refs.html#STON87b">[STON87b]</A>. + POSTGRES has undergone several major releases since + then. The first "demoware" system became operational + in 1987 and was shown at the 1988 <B>ACM-SIGMOD</B> + Conference. We released Version 1, described in <A HREF="refs.html#STON90a">[STON90a]</A>, + to a few external users in June 1989. In response to a + critique of the first rule system <A HREF="refs.html#STON89">[STON89]</A>, the rule + system was redesigned <A HREF="refs.html#STON90">[STON90b]</A> and Version 2 was + released in June 1990 with the new rule system. + Version 3 appeared in 1991 and added support for multiple + storage managers, an improved query executor, and a + rewritten rewrite rule system. For the most part, + releases since then have focused on portability and + reliability. + POSTGRES has been used to implement many different + research and production applications. These include: a + financial data analysis system, a jet engine + performance monitoring package, an asteroid tracking + database, a medical information database, and several + geographic information systems. POSTGRES has also been + used as an educational tool at several universities. + Finally, <A HREF="http://www.illustra.com/">Illustra Information Technologies</A> picked up + the code and commercialized it. + POSTGRES became the primary data manager for the + <A HREF="http://www.sdsc.edu/0/Parts_Collabs/S2K/s2k_home.html">Sequoia 2000</A> scientific computing project in late 1992. + Furthermore, the size of the external user community + nearly doubled during 1993. It became increasingly + obvious that maintenance of the prototype code and + support was taking up large amounts of time that should + have been devoted to database research. In an effort + to reduce this support burden, the project officially + ended with <B>Version 4.2</B>. + +<H2><A NAME="what-is-postgres95">1.3. What is <B>POSTGRES95</B>?</A></H2> + <B>POSTGRES95</B> is a derivative of the last official release + of POSTGRES (version 4.2). The code is now completely + ANSI C and the code size has been trimmed by 25%. There + are a lot of internal changes that improve performance + and code maintainability. <B>POSTGRES95</B> runs about 30-50% + faster on the Wisconsin Benchmark compared to v4.2. + Apart from bug fixes, these are the major enhancements: +<UL> + <LI>The query language <B>POSTQUEL</B> has been replaced with + <B>SQL</B> (implemented in the server). We do not support + subqueries (which can be imitated with user defined + <B>SQL</B> functions) at the moment. Aggregates have been + re-implemented. We also added support for <B>GROUP BY</B>. + The <B>libpq</B> interface is still available for <B>C</B> + programs. + <LI>In addition to the monitor program, we provide a new + program (<B>psql</B>) which supports <B>GNU</B> <B>readline</B>. + <LI>We added a new front-end library, <B>libpgtcl</B>, that + supports <B>Tcl</B>-based clients. A sample shell, + pgtclsh, provides new Tcl commands to interface <B>tcl</B> + programs with the <B>POSTGRES95</B> backend. + <LI>The large object interface has been overhauled. We + kept Inversion large objects as the only mechanism + for storing large objects. (This is not to be + confused with the Inversion file system which has been + removed.) + <LI>The instance-level rule system has been removed. + <LI>Rules are still available as rewrite rules. + <LI>A short tutorial introducing regular <B>SQL</B> features as + well as those of ours is distributed with the source + code. + <LI><B>GNU</B> make (instead of <B>BSD</B> make) is used for the + build. Also, <B>POSTGRES95</B> can be compiled with an + unpatched <B>gcc</B> (data alignment of doubles has been + fixed). +</UL> +<p> +<H2><A NAME="about-this-release">1.4. About This Release</A></H2> + <B>POSTGRES95</B> is available free of charge. This manual + describes version 1.0 of <B>POSTGRES95</B>. The authors have + compiled and tested <B>POSTGRES95</B> on the following + platforms: +<p> +<center> +<table border=4> + <tr> + <th>Architecture</th> + <th>Processor</th> + <th>Operating System</th> + </tr> + <tr> + <td>DECstation 3000</td> + <td>Alpha AXP</td> + <td>OSF/1 2.1, 3.0, 3.2</td> + </tr> + <tr> + <td>DECstation 5000</td> + <td>MIPS</td> + <td>ULTRIX 4.4</td> + </tr> + <tr> + <td>Sun4</td> + <td>SPARC</td> + <td>SunOS 4.1.3, 4.1.3_U1; Solaris 2.4</td> + </tr> + <tr> + <td>H-P 9000/700 and 800</td> + <td>PA-RISC</td> + <td>HP-UX 9.00, 9.01, 9.03</td> + </tr> + <tr> + <td>Intel</td> + <td>X86</td> + <td>Linux 1.2.8, ELF</td> +</table> +</center> +<p> +<H2><A NAME="outline-of-this-manual">1.5. Outline of This Manual</A></H2> + From now on, We will use POSTGRES to mean <B>POSTGRES95</B>. + The first part of this manual goes over some basic sys- + tem concepts and procedures for starting the POSTGRES + system. We then turn to a tutorial overview of the + POSTGRES data model and SQL query language, introducing + a few of its advanced features. Next, we explain the + POSTGRES approach to extensibility and describe how + users can extend POSTGRES by adding user-defined types, + operators, aggregates, and both query language and pro- + gramming language functions. After an extremely brief + overview of the POSTGRES rule system, the manual + concludes with a detailed appendix that discusses some of + the more involved and operating system-specific + procedures involved in extending the system. +<HR> +<B>UNIX</B> is a trademark of X/Open, Ltd. Sun4, SPARC, SunOS +and Solaris are trademarks of Sun Microsystems, Inc. DEC, +DECstation, Alpha AXP and ULTRIX are trademarks of Digital +Equipment Corp. PA-RISC and HP-UX are trademarks of +Hewlett-Packard Co. OSF/1 is a trademark of the Open +Software Foundation.<p> + + We assume proficiency with UNIX and C programming. + +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +[ Previous ] +<A HREF="architec.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/libpq.html b/doc/manual/libpq.html new file mode 100644 index 00000000000..71f8e7a1502 --- /dev/null +++ b/doc/manual/libpq.html @@ -0,0 +1,815 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - LIBPQ</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xindex.html">[ Previous ]</A> +<A HREF="lobj.html">[ Next ]</A> +</font> +<HR> +<H1>12. <B>LIBPQ</B></H1> +<HR> + <B>LIBPQ</B> is the application programming interface to POSTGRES. + <B>LIBPQ</B> is a set of library routines which allows + client programs to pass queries to the POSTGRES backend + server and to receive the results of these queries. + This version of the documentation describes the <B>C</B> + interface library. Three short programs are included + at the end of this section to show how to write programs that use <B>LIBPQ</B>. + There are several examples of <B>LIBPQ</B> applications in the + following directories: + +<pre> ../src/test/regress + ../src/test/examples + ../src/bin/psql +</pre> + Frontend programs which use <B>LIBPQ</B> must include the + header file <CODE>libpq-fe.h</CODE> and must link with the <B>libpq</B> + library. + +<H2><A NAME="control-and-initialization">12.1. Control and Initialization</A></H2> + The following environment variables can be used to set + up default environment values to avoid hard-coding + database names into an application program: + +<UL> + <LI><B>PGHOST</B> sets the default server name. + <LI><B>PGOPTIONS</B> sets additional runtime options for the POSTGRES backend. + <LI><B>PGPORT</B> sets the default port for communicating with the POSTGRES backend. + <LI><B>PGTTY</B> sets the file or tty on which debugging messages from the backend server are displayed. + <LI><B>PGDATABASE</B> sets the default POSTGRES database name. + <LI><B>PGREALM</B> sets the Kerberos realm to use with POSTGRES, if it is different from the local realm. If + <LI><B>PGREALM</B> is set, POSTGRES applications will attempt + authentication with servers for this realm and use + separate ticket files to avoid conflicts with local + ticket files. This environment variable is only + used if Kerberos authentication is enabled. +</UL> + +<H2><A NAME="database-connection-functions">12.2. Database Connection Functions</A></H2> + The following routines deal with making a connection to + a backend from a <B>C</B> program. + + <DL> + <DT><B>PQsetdb</B> + <DD>Makes a new connection to a backend. +<pre> PGconn *PQsetdb(char *pghost, + char *pgport, + char *pgoptions, + char *pgtty, + char *dbName); +</pre> + <DD>If any argument is NULL, then the corresponding + environment variable is checked. If the environment variable is also not set, then hardwired + defaults are used. + <DD>PQsetdb always returns a valid PGconn pointer. + <DD>The PQstatus (see below) command should be called + to ensure that a connection was properly made + before queries are sent via the connection. <B>LIBPQ</B> + programmers should be careful to maintain the + <DD>PGconn abstraction. Use the accessor functions + below to get at the contents of PGconn. Avoid + directly referencing the fields of the PGconn + structure as they are subject to change in the + future.<br> + <DT><B>PQdb</B> + <DD>Returns the database name of the connection. +<pre> char *PQdb(PGconn *conn) +</pre><br> + <DT><B>PQhost</B> + <DD>Returns the host name of the connection. +<pre> char *PQhost(PGconn *conn) +</pre><br> + <DT><B>PQoptions</B> + <DD>Returns the pgoptions used in the connection. +<pre> char *PQoptions(PGconn *conn) +</pre><br> + <DT><B>PQport</B> + <DD>Returns the pgport of the connection. +<pre> char *PQport(PGconn *conn) +</pre><br> + <DT><B>PQtty</B> + <DD>Returns the pgtty of the connection. +<pre> char *PQtty(PGconn *conn) +</pre><br> + <DT><B>PQstatus</B> + <DD>Returns the status of the connection. + <DD>The status can be CONNECTION_OK or CONNECTION_BAD. +<pre> ConnStatusType *PQstatus(PGconn *conn) +</pre><br> + <DT><B>PQerrorMessage</B> + <DD>Returns the error message associated with the connection +<pre> char *PQerrorMessage(PGconn* conn); +</pre><br> + + <DT><B>PQfinish</B> + <DD>Close the connection to the backend. Also frees + memory used by the PGconn structure. The PGconn + pointer should not be used after PQfinish has been + called. +<pre> void PQfinish(PGconn *conn) +</pre><br> + <DT><B>PQreset</B> + <DD>Reset the communication port with the backend. + This function will close the IPC socket connection + to the backend and attempt to reestablish a new + connection to the same backend. +<pre> void PQreset(PGconn *conn) +</pre><br> + <DT><B>PQtrace</B> + <DD>Enables tracing of messages passed between the + frontend and the backend. The messages are echoed + to the debug_port file stream. +<pre> void PQtrace(PGconn *conn, + FILE* debug_port); +</pre><br> + <DT><B>PQuntrace</B> + <DD>Disables tracing of messages passed between the + frontend and the backend. +<pre> void PQuntrace(PGconn *conn); +</pre><br> +</DL> +<H2><A NAME="query-execution-functions">12.3. Query Execution Functions</A></H2> +<DL> + <DT><B>PQexec</B> + <DD>Submit a query to POSTGRES. Returns a PGresult + pointer if the query was successful or a NULL otherwise. If a NULL is returned, PQerrorMessage can + be used to get more information about the error. +<pre> PGresult *PQexec(PGconn *conn, + char *query); +</pre> + <DD>The <B>PGresult</B> structure encapsulates the query + result returned by the backend. <B>LIBPQ</B> programmers + should be careful to maintain the PGresult + abstraction. Use the accessor functions described + below to retrieve the results of the query. Avoid + directly referencing the fields of the PGresult + structure as they are subject to change in the + future.<br> + <DT><B>PQresultStatus</B> + <DD>Returns the result status of the query. PQresultStatus can return one of the following values: +<pre> PGRES_EMPTY_QUERY, + PGRES_COMMAND_OK, /* the query was a command */ + PGRES_TUPLES_OK, /* the query successfully returned tuples */ + PGRES_COPY_OUT, + PGRES_COPY_IN, + PGRES_BAD_RESPONSE, /* an unexpected response was received */ + PGRES_NONFATAL_ERROR, + PGRES_FATAL_ERROR +</pre> + <DD>If the result status is PGRES_TUPLES_OK, then the + following routines can be used to retrieve the + tuples returned by the query.<br> + <DT><B>PQntuples</B> returns the number of tuples (instances) + in the query result. + +<pre> int PQntuples(PGresult *res); +</pre><br> + <DT><B>PQnfields</B> + <DD>Returns the number of fields + (attributes) in the query result. + +<pre> int PQnfields(PGresult *res); +</pre><br> + <DT><B>PQfname</B> + <DD>Returns the field (attribute) name associated with the given field index. Field indices + start at 0. + +<pre> char *PQfname(PGresult *res, + int field_index); +</pre><br> + <DT><B>PQfnumber</B> + <DD>Returns the field (attribute) index + associated with the given field name. + +<pre> int PQfnumber(PGresult *res, + char* field_name); +</pre><br> + <DT><B>PQftype</B> + <DD>Returns the field type associated with the + given field index. The integer returned is an + internal coding of the type. Field indices start + at 0. + +<pre> Oid PQftype(PGresult *res, + int field_num); +</pre><br> + <DT><B>PQfsize</B> + <DD>Returns the size in bytes of the field + associated with the given field index. If the size + returned is -1, the field is a variable length + field. Field indices start at 0. + +<pre> int2 PQfsize(PGresult *res, + int field_index); +</pre><br> + <DT><B>PQgetvalue</B> + <DD>Returns the field (attribute) value. + For most queries, the value returned by PQgetvalue + is a null-terminated ASCII string representation + of the attribute value. If the query was a result + of a <B>BINARY</B> cursor, then the value returned by + PQgetvalue is the binary representation of the + type in the internal format of the backend server. + It is the programmer's responsibility to cast and + convert the data to the correct C type. The value + returned by PQgetvalue points to storage that is + part of the PGresult structure. One must explicitly + copy the value into other storage if it is to + be used past the lifetime of the PGresult structure itself. + +<pre> char* PQgetvalue(PGresult *res, + int tup_num, + int field_num); +</pre><br> + <DT><B>PQgetlength</B> + <DD>Returns the length of a field + (attribute) in bytes. If the field is a struct + varlena, the length returned here does not include + the size field of the varlena, i.e., it is 4 bytes + less. +<pre> int PQgetlength(PGresult *res, + int tup_num, + int field_num); +</pre><br> + <DT><B>PQcmdStatus</B> + Returns the command status associated with the + last query command. +<pre> + char *PQcmdStatus(PGresult *res); +</pre><br> + <DT><B>PQoidStatus</B> + Returns a string with the object id of the tuple + inserted if the last query is an INSERT command. + Otherwise, returns an empty string. +<pre> char* PQoidStatus(PGresult *res); +</pre><br> + <DT><B>PQprintTuples</B> + Prints out all the tuples and, optionally, the + attribute names to the specified output stream. + The programs psql and monitor both use PQprintTuples for output. + +<pre> void PQprintTuples( + PGresult* res, + FILE* fout, /* output stream */ + int printAttName,/* print attribute names or not*/ + int terseOutput, /* delimiter bars or not?*/ + int width /* width of column, variable width if 0*/ + ); +</pre><br> + + <DT><B>PQclear</B> + Frees the storage associated with the PGresult. + Every query result should be properly freed when + it is no longer used. Failure to do this will + result in memory leaks in the frontend application. +<pre> void PQclear(PQresult *res); +</pre><br> +</DL> +<H2><A NAME="fast-path">12.4. Fast Path</A></H2> + POSTGRES provides a fast path interface to send function calls to the backend. This is a trapdoor into + system internals and can be a potential security hole. + Most users will not need this feature. + +<pre> PGresult* PQfn(PGconn* conn, + int fnid, + int *result_buf, + int *result_len, + int result_is_int, + PQArgBlock *args, + int nargs); +</pre><br> + + The fnid argument is the object identifier of the function to be executed. result_buf is the buffer in which + to load the return value. The caller must have allocated sufficient space to store the return value. The + result length will be returned in the storage pointed + to by result_len. If the result is to be an integer + value, than result_is_int should be set to 1; otherwise + it should be set to 0. args and nargs specify the + arguments to the function. +<pre> typedef struct { + int len; + int isint; + union { + int *ptr; + int integer; + } u; + } PQArgBlock; +</pre> + PQfn always returns a valid PGresult*. The resultStatus should be checked before the result is used. The + caller is responsible for freeing the PGresult with + PQclear when it is not longer needed. +<H2><A NAME="asynchronous-notification">12.5. Asynchronous Notification</A></H2> + POSTGRES supports asynchronous notification via the + LISTEN and NOTIFY commands. A backend registers its + interest in a particular relation with the LISTEN command. All backends listening on a particular relation + will be notified asynchronously when a NOTIFY of that + relation name is executed by another backend. No + additional information is passed from the notifier to + the listener. Thus, typically, any actual data that + needs to be communicated is transferred through the + relation. + <B>LIBPQ</B> applications are notified whenever a connected + backend has received an asynchronous notification. + However, the communication from the backend to the + frontend is not asynchronous. Notification comes + piggy-backed on other query results. Thus, an application must submit queries, even empty ones, in order to + receive notice of backend notification. In effect, the + <B>LIBPQ</B> application must poll the backend to see if there + is any pending notification information. After the + execution of a query, a frontend may call PQNotifies to + see if any notification data is available from the + backend. +<DL> + <DT><B>PQNotifies</B> + <DD>returns the notification from a list of unhandled + notifications from the backend. Returns NULL if + there are no pending notifications from the backend. PQNotifies behaves like the popping of a + stack. Once a notification is returned from PQnotifies, it is considered handled and will be + removed from the list of notifications. +<pre> PGnotify* PQNotifies(PGconn *conn); +</pre><br> +</DL> + The second sample program gives an example of the use + of asynchronous notification. +<H2><A NAME="functions-associated-with-the-copy-command">12.6. Functions Associated with the COPY Command</A></H2> + The copy command in POSTGRES has options to read from + or write to the network connection used by <B>LIBPQ</B>. + Therefore, functions are necessary to access this network connection directly so applications may take full + advantage of this capability. +<DL> + <DT><B>PQgetline</B> + <DD>Reads a newline-terminated line of characters + (transmitted by the backend server) into a buffer + string of size length. Like fgets(3), this routine copies up to length-1 characters into string. + It is like gets(3), however, in that it converts + the terminating newline into a null character. + PQgetline returns EOF at EOF, 0 if the entire line + has been read, and 1 if the buffer is full but the + terminating newline has not yet been read. + Notice that the application must check to see if a + new line consists of the single character ".", + which indicates that the backend server has finished sending the results of the copy command. + Therefore, if the application ever expects to + receive lines that are more than length-1 characters long, the application must be sure to check + the return value of PQgetline very carefully. + The code in + +<pre> ../src/bin/psql/psql.c +</pre> + contains routines that correctly handle the copy + protocol. +<pre> int PQgetline(PGconn *conn, + char *string, + int length) +</pre><br> + <DT><B>PQputline</B> + <DD>Sends a null-terminated string to the backend + server. + The application must explicitly send the single + character "." to indicate to the backend that it + has finished sending its data. + +<pre> void PQputline(PGconn *conn, + char *string); +</pre><br> + <DT><B>PQendcopy</B> + <DD>Syncs with the backend. This function waits until + the backend has finished the copy. It should + either be issued when the last string has been + sent to the backend using PQputline or when the + last string has been received from the backend + using PGgetline. It must be issued or the backend + may get "out of sync" with the frontend. Upon + return from this function, the backend is ready to + receive the next query. + The return value is 0 on successful completion, + nonzero otherwise. +<pre> int PQendcopy(PGconn *conn); +</pre><br> + As an example: +<pre> PQexec(conn, "create table foo (a int4, b char16, d float8)"); + PQexec(conn, "copy foo from stdin"); + PQputline(conn, "3<TAB>hello world<TAB>4.5\n"); + PQputline(conn,"4<TAB>goodbye world<TAB>7.11\n"); + ... + PQputline(conn,".\n"); + PQendcopy(conn); +</pre><br> +</DL> +<H2><A NAME="tracing-functions">12.7. <B>LIBPQ</B> Tracing Functions</A></H2> +<DL> + <DT><B>PQtrace</B> + <DD>Enable tracing of the frontend/backend communication to a debugging file stream. +<pre> void PQtrace(PGconn *conn + FILE *debug_port) +</pre><br> + + <DT><B>PQuntrace</B> + <DD>Disable tracing started by PQtrace +<pre> void PQuntrace(PGconn *conn) +</pre><br> +</DL> +<H2><A NAME="authentication-functions">12.8. User Authentication Functions</A></H2> + If the user has generated the appropriate authentication credentials (e.g., obtaining <B>Kerberos</B> tickets), + the frontend/backend authentication process is handled + by <B>PQexec</B> without any further intervention. The following routines may be called by <B>LIBPQ</B> programs to tailor the behavior of the authentication process. +<DL> + <DT><B>fe_getauthname</B> + <DD>Returns a pointer to static space containing whatever name the user has authenticated. Use of this + routine in place of calls to getenv(3) or getpwuid(3) by applications is highly recommended, as + it is entirely possible that the authenticated + user name is not the same as value of the <B>USER</B> + environment variable or the user's entry in + <CODE>/etc/passwd</CODE>. + +<pre> char *fe_getauthname(char* errorMessage) +</pre><br> + <DT><B>fe_setauthsvc</B> + <DD>Specifies that <B>LIBPQ</B> should use authentication + service name rather than its compiled-in default. + <DD>This value is typically taken from a command-line + switch. +<pre> void fe_setauthsvc(char *name, + char* errorMessage) +</pre> + <DD>Any error messages from the authentication + attempts are returned in the errorMessage argument. +</DL> + +<H2><A NAME="bugs">12.9. BUGS</A></H2> + The query buffer is 8192 bytes long, and queries over + that length will be silently truncated. +<H2><A NAME="sample-programs">12.10. Sample Programs</H2> +<p> +<H3><A NAME="sample-program-1">12.10.1. Sample Program 1</A></H3> +<pre> + /* + * testlibpq.c + * Test the C version of LIBPQ, the POSTGRES frontend library. + * + * + */ + #include <stdio.h> + #include "libpq-fe.h" +<p> + void + exit_nicely(PGconn* conn) + { + PQfinish(conn); + exit(1); + } +<p> + main() + { + char *pghost, *pgport, *pgoptions, *pgtty; + char* dbName; + int nFields; + int i,j; +<p> + /* FILE *debug; */ +<p> + PGconn* conn; + PGresult* res; +<p> + /* begin, by setting the parameters for a backend connection + if the parameters are null, then the system will try to use + reasonable defaults by looking up environment variables + or, failing that, using hardwired constants */ + pghost = NULL; /* host name of the backend server */ + pgport = NULL; /* port of the backend server */ + pgoptions = NULL; /* special options to start up the backend server */ + pgtty = NULL; /* debugging tty for the backend server */ + dbName = "template1"; +<p> + /* make a connection to the database */ + conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); +<p> + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) { + fprintf(stderr,"Connection to database '%s' failed.0, dbName); + fprintf(stderr,"%s",PQerrorMessage(conn)); + exit_nicely(conn); + } +<p> + /* debug = fopen("/tmp/trace.out","w"); */ + /* PQtrace(conn, debug); */ +<p> + /* start a transaction block */ + + res = PQexec(conn,"BEGIN"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"BEGIN command failed0); + PQclear(res); + exit_nicely(conn); + } + /* should PQclear PGresult whenever it is no longer needed to avoid + memory leaks */ + PQclear(res); +<p> + /* fetch instances from the pg_database, the system catalog of databases*/ + res = PQexec(conn,"DECLARE myportal CURSOR FOR select * from pg_database"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"DECLARE CURSOR command failed0); + PQclear(res); + exit_nicely(conn); + } + PQclear(res); +<p> + res = PQexec(conn,"FETCH ALL in myportal"); + if (PQresultStatus(res) != PGRES_TUPLES_OK) { + fprintf(stderr,"FETCH ALL command didn't return tuples properly0); + PQclear(res); + exit_nicely(conn); + } +<p> + /* first, print out the attribute names */ + nFields = PQnfields(res); + for (i=0; i < nFields; i++) { + printf("%-15s",PQfname(res,i)); + } + printf("0); +<p> + /* next, print out the instances */ + for (i=0; i < PQntuples(res); i++) { + for (j=0 ; j < nFields; j++) { + printf("%-15s", PQgetvalue(res,i,j)); + } + printf("0); + } +<p> + PQclear(res); +<p> + /* close the portal */ + res = PQexec(conn, "CLOSE myportal"); + PQclear(res); +<p> + /* end the transaction */ + res = PQexec(conn, "END"); + PQclear(res); +<p> + /* close the connection to the database and cleanup */ + PQfinish(conn); + + /* fclose(debug); */ + } +</pre> +<p> +<H3><A NAME="sample-program-2">12.10.2. Sample Program 2</A></H3> +<pre> + /* + * testlibpq2.c + * Test of the asynchronous notification interface + * + populate a database with the following: +<p> + CREATE TABLE TBL1 (i int4); +<p> + CREATE TABLE TBL2 (i int4); +<p> + CREATE RULE r1 AS ON INSERT TO TBL1 DO [INSERT INTO TBL2 values (new.i); NOTIFY TBL2]; +<p> + * Then start up this program + * After the program has begun, do +<p> + INSERT INTO TBL1 values (10); +<p> + * + * + */ + #include <stdio.h> + #include "libpq-fe.h" +<p> + void exit_nicely(PGconn* conn) + { + PQfinish(conn); + exit(1); + } +<p> + main() + { + char *pghost, *pgport, *pgoptions, *pgtty; + char* dbName; + int nFields; + int i,j; +<p> + PGconn* conn; + PGresult* res; + PGnotify* notify; +<p> + /* begin, by setting the parameters for a backend connection + if the parameters are null, then the system will try to use + reasonable defaults by looking up environment variables + or, failing that, using hardwired constants */ + pghost = NULL; /* host name of the backend server */ + pgport = NULL; /* port of the backend server */ + pgoptions = NULL; /* special options to start up the backend server */ + pgtty = NULL; /* debugging tty for the backend server */ + dbName = getenv("USER"); /* change this to the name of your test database*/ +<p> + /* make a connection to the database */ + conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); + + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) { + fprintf(stderr,"Connection to database '%s' failed.0, dbName); + fprintf(stderr,"%s",PQerrorMessage(conn)); + exit_nicely(conn); + } +<p> + res = PQexec(conn, "LISTEN TBL2"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"LISTEN command failed0); + PQclear(res); + exit_nicely(conn); + } + /* should PQclear PGresult whenever it is no longer needed to avoid + memory leaks */ + PQclear(res); +<p> + while (1) { + /* async notification only come back as a result of a query*/ + /* we can send empty queries */ + res = PQexec(conn, " "); + /* printf("res->status = %s0, pgresStatus[PQresultStatus(res)]); */ + /* check for asynchronous returns */ + notify = PQnotifies(conn); + if (notify) { + fprintf(stderr, + "ASYNC NOTIFY of '%s' from backend pid '%d' received0, + notify->relname, notify->be_pid); + free(notify); + break; + } + PQclear(res); + } +<p> + /* close the connection to the database and cleanup */ + PQfinish(conn); +<p> + } +</pre> +<p> +<H3><A NAME="sample-program-3">12.10.3. Sample Program 3</A></H3> +<pre> + /* + * testlibpq3.c + * Test the C version of LIBPQ, the POSTGRES frontend library. + * tests the binary cursor interface + * + * + * + populate a database by doing the following: +<p> + CREATE TABLE test1 (i int4, d float4, p polygon); +<p> + INSERT INTO test1 values (1, 3.567, '(3.0, 4.0, 1.0, 2.0)'::polygon); +<p> + INSERT INTO test1 values (2, 89.05, '(4.0, 3.0, 2.0, 1.0)'::polygon); +<p> + the expected output is: +<p> + tuple 0: got + i = (4 bytes) 1, + d = (4 bytes) 3.567000, + p = (4 bytes) 2 points boundbox = (hi=3.000000/4.000000, lo = 1.000000,2.000000) + tuple 1: got + i = (4 bytes) 2, + d = (4 bytes) 89.050003, + p = (4 bytes) 2 points boundbox = (hi=4.000000/3.000000, lo = 2.000000,1.000000) +<p> + * + */ + #include <stdio.h> + #include "libpq-fe.h" + #include "utils/geo-decls.h" /* for the POLYGON type */ +<p> + void exit_nicely(PGconn* conn) + { + PQfinish(conn); + exit(1); + } +<p> + main() + { + char *pghost, *pgport, *pgoptions, *pgtty; + char* dbName; + int nFields; + int i,j; + int i_fnum, d_fnum, p_fnum; +<p> + PGconn* conn; + PGresult* res; +<p> + /* begin, by setting the parameters for a backend connection + if the parameters are null, then the system will try to use + reasonable defaults by looking up environment variables + or, failing that, using hardwired constants */ + pghost = NULL; /* host name of the backend server */ + pgport = NULL; /* port of the backend server */ + pgoptions = NULL; /* special options to start up the backend server */ + pgtty = NULL; /* debugging tty for the backend server */ +<p> + dbName = getenv("USER"); /* change this to the name of your test database*/ +<p> + /* make a connection to the database */ + conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); +<p> + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) { + fprintf(stderr,"Connection to database '%s' failed.0, dbName); + fprintf(stderr,"%s",PQerrorMessage(conn)); + exit_nicely(conn); + } +<p> + /* start a transaction block */ + res = PQexec(conn,"BEGIN"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"BEGIN command failed0); + PQclear(res); + exit_nicely(conn); + } + /* should PQclear PGresult whenever it is no longer needed to avoid + memory leaks */ + PQclear(res); +<p> + /* fetch instances from the pg_database, the system catalog of databases*/ + res = PQexec(conn,"DECLARE mycursor BINARY CURSOR FOR select * from test1"); + if (PQresultStatus(res) != PGRES_COMMAND_OK) { + fprintf(stderr,"DECLARE CURSOR command failed0); + PQclear(res); + exit_nicely(conn); + } + PQclear(res); +<p> + res = PQexec(conn,"FETCH ALL in mycursor"); + if (PQresultStatus(res) != PGRES_TUPLES_OK) { + fprintf(stderr,"FETCH ALL command didn't return tuples properly0); + PQclear(res); + exit_nicely(conn); + } +<p> + i_fnum = PQfnumber(res,"i"); + d_fnum = PQfnumber(res,"d"); + p_fnum = PQfnumber(res,"p"); +<p> + for (i=0;i<3;i++) { + printf("type[%d] = %d, size[%d] = %d0, + i, PQftype(res,i), + i, PQfsize(res,i)); + } + for (i=0; i < PQntuples(res); i++) { + int *ival; + float *dval; + int plen; + POLYGON* pval; + /* we hard-wire this to the 3 fields we know about */ + ival = (int*)PQgetvalue(res,i,i_fnum); + dval = (float*)PQgetvalue(res,i,d_fnum); + plen = PQgetlength(res,i,p_fnum); +<p> + /* plen doesn't include the length field so need to increment by VARHDSZ*/ + pval = (POLYGON*) malloc(plen + VARHDRSZ); + pval->size = plen; + memmove((char*)&pval->npts, PQgetvalue(res,i,p_fnum), plen); + printf("tuple %d: got0, i); + printf(" i = (%d bytes) %d,0, + PQgetlength(res,i,i_fnum), *ival); + printf(" d = (%d bytes) %f,0, + PQgetlength(res,i,d_fnum), *dval); + printf(" p = (%d bytes) %d points boundbox = (hi=%f/%f, lo = %f,%f)0, + PQgetlength(res,i,d_fnum), + pval->npts, + pval->boundbox.xh, + pval->boundbox.yh, + pval->boundbox.xl, + pval->boundbox.yl); + } +<p> + PQclear(res); +<p> + /* close the portal */ + res = PQexec(conn, "CLOSE mycursor"); + PQclear(res); +<p> + /* end the transaction */ + res = PQexec(conn, "END"); + PQclear(res); +<p> + /* close the connection to the database and cleanup */ + PQfinish(conn); +<p> + } +</pre> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xindex.html">[ Previous ]</A> +<A HREF="lobj.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/lobj.html b/doc/manual/lobj.html new file mode 100644 index 00000000000..c8d0e518e89 --- /dev/null +++ b/doc/manual/lobj.html @@ -0,0 +1,429 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - LARGE OBJECTS</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="libpq.html">[ Previous ]</A> +<A HREF="rules.html">[ Next ]</A> +</font> +<HR> +<H1>13. LARGE OBJECTS</H1> +<HR> + 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. + This section describes the implementation and the + programmatic and query language interfaces to POSTGRES + large object data. + +<H2><A NAME="historical-note">13.1. Historical Note</A></H2> + Originally, <B>POSTGRES 4.2</B> supports three standard + implementations of large objects: as files external + to POSTGRES, as <B>UNIX</B> 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 <B>POSTGRES95</B>. 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.) + +<H2><A NAME="inversion-large-objects">13.2. Inversion Large Objects</A></H2> + The Inversion large object implementation breaks large + objects up into "chunks" 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. + +<H2><A NAME="large-object-interfaces">13.3. Large Object Interfaces</A></H2> + 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 interface, are described below. (For users + familiar with <B>POSTGRES 4.2</B>, <B>POSTGRES95</B> 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 . + The POSTGRES large object interface is modeled after + the <B>UNIX</B> file system interface, with analogues of + <B>open(2), read(2), write(2), lseek(2)</B>, 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 mugshot existed that stored + photographs of faces, then a function called beard could + be declared on mugshot data. 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 beard function. + Large objects may be accessed from dynamically-loaded <B>C</B> + functions or database client programs that link the + library. POSTGRES provides a set of routines that + support opening, reading, writing, closing, and seeking on + large objects. +<p> +<H3><A NAME="creating-large-objects">13.3.1. Creating a Large Object</A></H3> + The routine +<pre> Oid lo_creat(PGconn *conn, int mode) +</pre> + creates a new large object. The mode is a bitmask + describing several different attributes of the new + object. The symbolic constants listed here are defined + in +<pre> /usr/local/postgres95/src/backend/libpq/libpq-fs.h +</pre> + The access type (read, write, or both) is controlled by + OR ing together the bits <B>INV_READ</B> and <B>INV_WRITE</B>. If + the large object should be archived -- that is, if + historical versions of it should be moved periodically to + a special archive relation -- then the <B>INV_ARCHIVE</B> bit + should be set. The low-order sixteen bits of mask are + the storage manager number on which the large object + should reside. For sites other than Berkeley, these + bits should always be zero. + The commands below create an (Inversion) large object: +<pre> inv_oid = lo_creat(INV_READ|INV_WRITE|INV_ARCHIVE); +</pre> + +<H3><A NAME="importing-a-large-object">13.3.2. Importing a Large Object</A></H3> +To import a <B>UNIX</B> file as + a large object, call +<pre> Oid + lo_import(PGconn *conn, text *filename) +</pre> + The filename argument specifies the <B>UNIX</B> pathname of + the file to be imported as a large object. +<p> +<H3><A NAME="exporting-a-large-object">13.3.3. Exporting a Large Object</A></H3> +To export a large object + into <B>UNIX</B> file, call +<pre> int + lo_export(PGconn *conn, Oid lobjId, text *filename) +</pre> + The lobjId argument specifies the Oid of the large + object to export and the filename argument specifies + the <B>UNIX</B> pathname of the file. +<p> +<H3><A NAME="opening-an-existing-large-object">13.3.4. Opening an Existing Large Object</A></H3> + To open an existing large object, call +<pre> int + lo_open(PGconn *conn, Oid lobjId, int mode, ...) +</pre> + The lobjId argument specifies the Oid of the large + object to open. The mode bits control whether the + object is opened for reading INV_READ), writing or + both. + A large object cannot be opened before it is created. + lo_open returns a large object descriptor for later use + in lo_read, lo_write, lo_lseek, lo_tell, and lo_close. +<p> +<H3><A NAME="writing-data-to-a-large-object">13.3.5. Writing Data to a Large Object</A></H3> + The routine +<pre> int + lo_write(PGconn *conn, int fd, char *buf, int len) +</pre> + writes len bytes from buf to large object fd. The fd + argument must have been returned by a previous lo_open. + The number of bytes actually written is returned. In + the event of an error, the return value is negative. +<p> +<H3><A NAME="seeking-on-a-large-object">13.3.6. Seeking on a Large Object</A></H3> + To change the current read or write location on a large + object, call +<pre> int + lo_lseek(PGconn *conn, int fd, int offset, int whence) +</pre> + This routine moves the current location pointer for the + large object described by fd to the new location specified + by offset. The valid values for .i whence are + SEEK_SET SEEK_CUR and SEEK_END. +<p> +<H3><A NAME="closing-a-large-object-descriptor">13.3.7. Closing a Large Object Descriptor</A></H3> + A large object may be closed by calling +<pre> int + lo_close(PGconn *conn, int fd) +</pre> + where fd is a large object descriptor returned by + lo_open. On success, <B>lo_close</B> returns zero. On error, + the return value is negative. + +<H2><A NAME="built-in-registered-functions">13.4. Built in registered functions</A></H2> + There are two built-in registered functions, <B>lo_import</B> + and <B>lo_export</B> which are convenient for use in <B>SQL</B> + queries. + Here is an example of there use +<pre> 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'; +</pre> +<H2><A NAME="accessing-large-objects-from-libpq">13.5. Accessing Large Objects from LIBPQ</A></H2> + Below is a sample program which shows how the large object + interface + in LIBPQ 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 +<pre> ../src/test/examples +</pre> + Frontend applications which use the large object interface + in LIBPQ should include the header file + libpq/libpq-fs.h and link with the libpq library. + +<H2><A NAME="sample-program">13.6. Sample Program</A></H2> +<pre> /*-------------------------------------------------------------- + * + * testlo.c-- + * test using large objects with libpq + * + * Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * /usr/local/devel/pglite/cvs/src/doc/manual.me,v 1.16 1995/09/01 23:55:00 jolly Exp + * + *-------------------------------------------------------------- + */ + #include <stdio.h> + #include "libpq-fe.h" + #include "libpq/libpq-fs.h" +<p> + #define BUFSIZE 1024 +<p> + /* + * 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; +<p> + /* + * open the file to be read in + */ + fd = open(filename, O_RDONLY, 0666); + if (fd < 0) { /* error */ + fprintf(stderr, "can't open unix file + } +<p> + /* + * create the large object + */ + lobjId = lo_creat(conn, INV_READ|INV_WRITE); + if (lobjId == 0) { + fprintf(stderr, "can't create large object"); + } +<p> + 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 + } + } +<p> + (void) close(fd); + (void) lo_close(conn, lobj_fd); +<p> + return lobjId; + } +<p> + void pickout(PGconn *conn, Oid lobjId, int start, int len) + { + int lobj_fd; + char* buf; + int nbytes; + int nread; +<p> + lobj_fd = lo_open(conn, lobjId, INV_READ); + if (lobj_fd < 0) { + fprintf(stderr,"can't open large object %d", + lobjId); + } +<p> + lo_lseek(conn, lobj_fd, start, SEEK_SET); + buf = malloc(len+1); +<p> + nread = 0; + while (len - nread > 0) { + nbytes = lo_read(conn, lobj_fd, buf, len - nread); + buf[nbytes] = ' '; + fprintf(stderr,">>> %s", buf); + nread += nbytes; + } + fprintf(stderr,"0); + lo_close(conn, lobj_fd); + } +<p> + void overwrite(PGconn *conn, Oid lobjId, int start, int len) + { + int lobj_fd; + char* buf; + int nbytes; + int nwritten; + int i; +<p> + lobj_fd = lo_open(conn, lobjId, INV_READ); + if (lobj_fd < 0) { + fprintf(stderr,"can't open large object %d", + lobjId); + } +<p> + lo_lseek(conn, lobj_fd, start, SEEK_SET); + buf = malloc(len+1); +<p> + for (i=0;i<len;i++) + buf[i] = 'X'; + buf[i] = ' '; +<p> + nwritten = 0; + while (len - nwritten > 0) { + nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten); + nwritten += nbytes; + } + fprintf(stderr,"0); + lo_close(conn, lobj_fd); + } +<p> + + /* + * 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; +<p> + /* + * 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); + } +<p> + /* + * 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 + filename); + } +<p> + /* + * 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 + filename); + } + } +<p> + (void) lo_close(conn, lobj_fd); + (void) close(fd); +<p> + return; + } +<p> + void + exit_nicely(PGconn* conn) + { + PQfinish(conn); + exit(1); + } +<p> + int + main(int argc, char **argv) + { + char *in_filename, *out_filename; + char *database; + Oid lobjOid; + PGconn *conn; + PGresult *res; +<p> + if (argc != 4) { + fprintf(stderr, "Usage: %s database_name in_filename out_filename0, + argv[0]); + exit(1); + } +<p> + database = argv[1]; + in_filename = argv[2]; + out_filename = argv[3]; +<p> + /* + * set up the connection + */ + conn = PQsetdb(NULL, NULL, NULL, NULL, database); +<p> + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) { + fprintf(stderr,"Connection to database '%s' failed.0, database); + fprintf(stderr,"%s",PQerrorMessage(conn)); + exit_nicely(conn); + } +<p> + res = PQexec(conn, "begin"); + PQclear(res); + + printf("importing file + /* lobjOid = importFile(conn, in_filename); */ + lobjOid = lo_import(conn, in_filename); + /* + printf("as large object %d.0, lobjOid); +<p> + printf("picking out bytes 1000-2000 of the large object0); + pickout(conn, lobjOid, 1000, 1000); +<p> + printf("overwriting bytes 1000-2000 of the large object with X's0); + overwrite(conn, lobjOid, 1000, 1000); + */ +<p> + printf("exporting large object to file + /* exportFile(conn, lobjOid, out_filename); */ + lo_export(conn, lobjOid,out_filename); +<p> + res = PQexec(conn, "end"); + PQclear(res); + PQfinish(conn); + exit(0); + } +</pre> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="libpq.html">[ Previous ]</A> +<A HREF="rules.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/pg95user.html b/doc/manual/pg95user.html new file mode 100644 index 00000000000..7ce98828b6d --- /dev/null +++ b/doc/manual/pg95user.html @@ -0,0 +1,154 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual</TITLE> +</HEAD> + +<BODY> +<H1 align=center> +The <B> +<A HREF="http://s2k-ftp.cs.berkeley.edu:8000/postgres95/"> +POSTGRES95</A></B> User Manual</H1> + +<p align=CENTER> + + Version 1.0 (September 5, 1995)<br><br> + + <A HREF="http://s2k-ftp.cs.berkeley.edu:8000/personal/andrew">Andrew Yu</A> + and + <A HREF="http://http.cs.berkeley.edu/~jolly/">Jolly Chen</A><br> + (with the POSTGRES Group)<br> + Computer Science Div., Dept. of EECS<br> + University of California at Berkeley<br> +</p> +<!--#exec cgi="/cgi-bin/wais-pg95.pl"--> +<H1 align=center>Table of Contents</H1> +<DL> + <DT><A HREF="intro.html">1. INTRODUCTION</A> + <DL> + <DT><A HREF="intro.html#a-short-history-of-the-postgres-project">1.1. A Short History of POSTGRES</A> + <DT><A HREF="intro.html#what-is-postgres95">1.2. What is POSTGRES95?</A> + <DT><A HREF="intro.html#about-this-release">1.4. About This Release</A> + <DT><A HREF="intro.html#outline-of-this-manual">1.5. Outline of This Manual</A> + </DL> + <DT><A HREF="architec.html">2. ARCHITECTURE CONCEPTS</A> + <DT><A HREF="start.html">3. GETTING STARTED</A> + <DL> + <DT><A HREF="start.html#setting-up-your-environment">3.1. Setting Up Your Enviroment</A> + <DT><A HREF="start.html#starting-the-postmaster">3.2. Starting The Postmaster</A> + <DT><A HREF="start.html#adding-and-deleting-users">3.3. Adding And Deleting Users</A> + <DT><A HREF="start.html#starting-applications">3.4. Starting Applications</A> + <DT><A HREF="start.html#managing-a-database">3.5. Managing A Database</A> + <DL> + <DT><A HREF="start.html#creating-a-database">3.5.1. Creating A Database</A> + <DT><A HREF="start.html#accesssing-a-database">3.5.2. Accessing A Database</A> + <DT><A HREF="start.html#destroying-a-database">3.5.3. Destroying A Database</A> + </DL> + </DL> + <DT><A HREF="query.html">4. QUERY LANGUAGE</A> + <DL> + <DT><A HREF="query.html#concepts">4.1. Concepts</A> + <DT><A HREF="query.html#creating-a-new-class">4.2. Creating a New Class</A> + <DT><A HREF="query.html#populating-a-class-with-instances">4.3. Populating a Class with Instances</A> + <DT><A HREF="query.html#querying-a-class">4.4. Querying a Class</A> + <DT><A HREF="query.html#redirecting-select-queries">4.5. Redirecting SELECT Queries</A> + </DL> + <DT><A HREF="advanced.html">5. ADVANCED POSTGRES SQL FEATURES</A> + <DL> + <DT><A HREF="advanced.html#inheritance">5.1. Inheritance</A> + <DT><A HREF="advanced.html#time-travel">5.2. Time Travel</A> + <DT><A HREF="advanced.html#non-atomic-values">5.3. Non-Atomic Values</A> + <DL> + <DT><A HREF="advanced.html#arrays">5.3.1. Arrays</A> + </DL> + </DL> + <DT><A HREF="extend.html">6. EXTENDING SQL: AN OVERVIEW</A> + <DL> + <DT><A HREF="extend.html#how-extensibility-works">6.1. How Extensibility Works</A> + <DT><A HREF="extend.html#the-postgres-type-system">6.2. The POSTGRES Type System</A> + <DT><A HREF="extend.html#about-the-postgres-system-catalogs">6.3. About the POSTGRES System Catalogs</A> + </DL> + <DT><A HREF="xfunc.html">7. EXTENDING SQL: FUNCTIONS</A> + <DL> + <DT><A HREF="xfunc.html#query-language-sql-functions">7.1. Query Language (SQL) Functions</A> + <DL> + <DT><A HREF="xfunc.html#sql-functions-on-base-types">7.1.1. SQL Functions on Base Types</A> + </DL> + <DT><A HREF="xfunc.html#programming-language-functions">7.2. Programming Language Functions</A> + <DL> + <DT><A HREF="xfunc.html#programming-language-functions-on-base-types">7.2.1. Programming Language Functions on Base Types</A> + <DT><A HREF="xfunc.html#programming-language-functions-on-composite-types">7.2.2. Programming Language Functions on Composite Types</A> + <DT><A HREF="xfunc.html#caveats">7.2.3. Caveats</A> + </DL> + </DL> + <DT><A HREF="xtypes.html">8. EXTENDING SQL: TYPES</A> + <DL> + <DT><A HREF="xtypes.html#user-defined-types">8.1. User-Defined Types</A> + <DL> + <DT><A HREF="xtypes.html#functions-needed-for-a-user-defined-type">8.1.1. Functions Needed for a User-Defined Type</A> + <DT><A HREF="xtypes.html#large-objects">8.1.2. Large Objects</A> + </DL> + </DL> + <DT><A HREF="xoper.html">9. EXTENDING SQL: OPERATORS</A> + <DL> + </DL> + <DT><A HREF="xaggr.html">10. EXTENDING SQL: AGGREGATES</A> + <DL> + </DL> + <DT><A HREF="xindex.html">11. INTERFACING EXTENSIONS TO INDICES</A> + <DL> + </DL> + <DT><A HREF="libpq.html">12. LIBPQ</A> + <DL> + <DT><A HREF="libpq.html#control-and-initialization">12.1. Control and Initialization</A> + <DT><A HREF="libpq.html#database-connection-functions">12.2. Database Connection Functions</A> + <DT><A HREF="libpq.html#query-execution-functions">12.3. Query Execution Functions</A> + <DT><A HREF="libpq.html#fast-path">12.4. Fast Path</A> + <DT><A HREF="libpq.html#asynchronous-notification">12.5. Asynchronous Notification</A> + <DT><A HREF="libpq.html#functions-associated-with-the-copy-command">12.6. Functions Associated with the COPY Command</A> + <DT><A HREF="libpq.html#tracing-functions">12.7. LIBPQ Tracing Functions</A></A> + <DT><A HREF="libpq.html#authentication-functions">12.8. User Authentication Functions</A> + <DT><A HREF="libpq.html#bugs">12.9. BUGS</A> + <DT><A HREF="libpq.html#sample-programs">12.10. Sample Programs</A> + <DL> + <DT><A HREF="libpq.html#sample-program-1">12.10.1. Sample Program 1</A> + <DT><A HREF="libpq.html#sample-program-2">12.10.2. Sample Program 2</A> + <DT><A HREF="libpq.html#sample-program-3">12.10.3. Sample Program 3</A> + </DL> + </DL> + <DT><A HREF="lobj.html">13. LARGE OBJECTS</A> + <DL> + <DT><A HREF="lobj.html#historical-note">13.1. Historical Note</A> + <DT><A HREF="lobj.html#inversion-large-objects">13.2. Inversion Large Objects</A> + <DT><A HREF="lobj.html#large-object-interfaces">13.3. Large Object Interfaces</A> + <DL> + <DT><A HREF="lobj.html#creating-large-objects">13.3.1. Creating a Large Object</A> + <DT><A HREF="lobj.html#importing-a-large-object">13.3.2. Importing a Large Object</A> + <DT><A HREF="lobj.html#exporting-a-large-object">13.3.3. Exporting a Large Object</A> + <DT><A HREF="lobj.html#opening-an-existing-large-object">13.3.4. Opening an Existing Large Object</A> + <DT><A HREF="lobj.html#writing-data-to-a-large-object">13.3.5. Writing Data to a Large Object</A> + <DT><A HREF="lobj.html#seeking-on-a-large-object">13.3.6. Seeking on a Large Object</A> + <DT><A HREF="lobj.html#closing-a-large-object-descriptor">13.3.7. Closing a Large Object Descriptor</A> + </DL> + <DT><A HREF="lobj.html#built-in-registered-functions">13.4. Built in registered functions</A> + <DT><A HREF="lobj.html#accessing-large-objects-from-libpq">13.5. Accessing Large Objects from LIBPQ</A> + <DT><A HREF="lobj.html#sample-program">13.6. Sample Program</A> + </DL> + <DT><A HREF="rules.html">14. THE POSTGRES RULE SYSTEM</A> + <DT><A HREF="admin.html">15. ADMINISTERING POSTGRES</A> + <DT><A HREF="refs.html">16. REFERENCES</A> + <p> + <DT><A HREF="appenda.html">Appendix A: Linking Dynamically-Loaded Functions</A> + +</DL> +<HR> +<A HREF="http://eol.ists.ca/cgi-bin/HyperNews/get/dunlop/pg95-user.html">HyperNews Forum</A> - About this Manual. +<HR> +<p align=center><B>POSTGRES95</B> is <A HREF="copy.html">copyright</A> © 1994-5 by the Regents of the +University of California.<br><br> +Converted to HTML by <a href="http://www.eol.ists.ca/~dunlop/dunlop.html">J. Douglas Dunlop</a> +<a href="mailto:dunlop@eol.ists.ca"><dunlop@eol.ists.ca></a><br> +The file +<A HREF="http://www.eol.ists.ca/~dunlop/postgres95-manual/pg95user.tgz"> +pg95user.tgz</a> contains the complete manual for download.</p> +</BODY> +</HTML> diff --git a/doc/manual/query.html b/doc/manual/query.html new file mode 100644 index 00000000000..a8f8db8147a --- /dev/null +++ b/doc/manual/query.html @@ -0,0 +1,259 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - THE QUERY LANGUAGE</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="start.html">[ Previous ]</A> +<A HREF="advanced.html">[ Next ]</A> +</font> +<HR> +<H1>4. THE QUERY LANGUAGE</H1> +<HR> + The POSTGRES query language is a variant of <B>SQL-3</B>. It + has many extensions such as an extensible type system, + inheritance, functions and production rules. Those are + features carried over from the original POSTGRES query + language, POSTQUEL. This section provides an overview + of how to use POSTGRES <B>SQL</B> to perform simple operations. + This manual is only intended to give you an idea of our + flavor of <B>SQL</B> and is in no way a complete tutorial on + <B>SQL</B>. Numerous books have been written on <B>SQL</B>. For + instance, consult <A HREF="refs.html#MELT93">[MELT93]</A> or + <A HREF="refs.html#DATE93">[DATE93]</A>. You should also + be aware that some features are not part of the <B>ANSI</B> + standard. + In the examples that follow, we assume that you have + created the mydb database as described in the previous + subsection and have started <B>psql</B>. + Examples in this manual can also be found in + <CODE>/usr/local/postgres95/src/tutorial</CODE>. Refer to the + <CODE>README</CODE> file in that directory for how to use them. To + start the tutorial, do the following: +<pre> % cd /usr/local/postgres95/src/tutorial + % psql -s mydb + Welcome to the POSTGRES95 interactive sql monitor: + + type \? for help on slash commands + type \q to quit + type \g or terminate with semicolon to execute query + You are currently connected to the database: jolly + + + mydb=> \i basics.sql +</pre> + The <B>\i</B> command read in queries from the specified + files. The <B>-s</B> option puts you in single step mode which + pauses before sending a query to the backend. Queries + in this section are in the file <CODE>basics.sql</CODE>. + +<H2><A NAME="concepts">4.1. Concepts</A></H2> + The fundamental notion in POSTGRES is that of a class, + which is a named collection of object instances. Each + instance has the same collection of named attributes, + and each attribute is of a specific type. Furthermore, + each instance has a permanent <B>object identifier (OID)</B> + that is unique throughout the installation. Because + <B>SQL</B> syntax refers to tables, we will <B>use the terms + table< and class interchangeably</B>. Likewise, a <B>row is an + instance</B> and <B>columns are attributes</B>. + As previously discussed, classes are grouped into + databases, and a collection of databases managed by a + single <B>postmaster</B> process constitutes an installation + or site. + +<H2><A NAME="creating-a-new-class">4.2. Creating a New Class</A></H2> + You can create a new class by specifying the class + name, along with all attribute names and their types: +<pre> CREATE TABLE weather ( + city varchar(80), + temp_lo int, -- low temperature + temp_hi int, -- high temperature + prcp real, -- precipitation + date date + ); +</pre> + Note that keywords are case-insensitive but identifiers + are case-sensitive. POSTGRES <B>SQL</B> supports the usual + <B>SQL</B> types <B>int, float, real, smallint, char(N), + varchar(N), date,</B> and <B>time</B>. As we will + see later, POSTGRES can be customized with an + arbitrary number of + user-defined data types. Consequently, type names are + not keywords. + So far, the POSTGRES create command looks exactly like + the command used to create a table in a traditional + relational system. However, we will presently see that + classes have properties that are extensions of the + relational model. + +<H2><A NAME="populating-a-class-with-instances">4.3. Populating a Class with Instances</A></H2> + The <B>insert</B> statement is used to populate a class with + instances: +<pre> INSERT INTO weather + VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994') +</pre> + You can also use the <B>copy</B> command to perform load large + amounts of data from flat (<B>ASCII</B>) files. + +<H2><A NAME="querying-a-class">4.4. Querying a Class</A></H2> + The weather class can be queried with normal relational + selection and projection queries. A <B>SQL</B> <B>select</B> + statement is used to do this. The statement is divided into + a target list (the part that lists the attributes to be + returned) and a qualification (the part that specifies + any restrictions). For example, to retrieve all the + rows of weather, type: +<pre> SELECT * FROM WEATHER; +</pre> + + and the output should be: +<pre> + +--------------+---------+---------+------+------------+ + |city | temp_lo | temp_hi | prcp | date | + +--------------+---------+---------+------+------------+ + |San Francisco | 46 | 50 | 0.25 | 11-27-1994 | + +--------------+---------+---------+------+------------+ + |San Francisco | 43 | 57 | 0 | 11-29-1994 | + +--------------+---------+---------+------+------------+ + |Hayward | 37 | 54 | | 11-29-1994 | + +--------------+---------+---------+------+------------+ +</pre> + You may specify any aribitrary expressions in the target list. For example, you can do: +<pre> * SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather; +</pre> + Arbitrary Boolean operators ( <B>and</B>, or and <B>not</B>) are + allowed in the qualification of any query. For example, +<pre> SELECT * + FROM weather + WHERE city = 'San Francisco' + and prcp > 0.0; + + +--------------+---------+---------+------+------------+ + |city | temp_lo | temp_hi | prcp | date | + +--------------+---------+---------+------+------------+ + |San Francisco | 46 | 50 | 0.25 | 11-27-1994 | + +--------------+---------+---------+------+------------+ +</pre> + + As a final note, you can specify that the results of a + select can be returned in a <B>sorted order</B> or with <B>duplicate instances removed</B>. +<pre> SELECT DISTINCT city + FROM weather + ORDER BY city; +</pre> + +<H2><A NAME="redirecting-select-queries">4.5. Redirecting SELECT Queries</A></H2> + Any select query can be redirected to a new class +<pre> SELECT * INTO temp from weather; +</pre> + This creates an implicit create command, creating a new + class temp with the attribute names and types specified + in the target list of the <B>SELECT INTO</B> command. We can + then, of course, perform any operations on the resulting + class that we can perform on other classes. + +<H2><A NAME="joins-between-classes">4.6. Joins Between Classes</A></H2> + Thus far, our queries have only accessed one class at a + time. Queries can access multiple classes at once, or + access the same class in such a way that multiple + instances of the class are being processed at the same + time. A query that accesses multiple instances of the + same or different classes at one time is called a join + query. + As an example, say we wish to find all the records that + are in the temperature range of other records. In + effect, we need to compare the temp_lo and temp_hi + attributes of each EMP instance to the temp_lo and + temp_hi attributes of all other EMP instances.<A HREF="#2">2</A> We can + do this with the following query: +<pre> SELECT W1.city, W1.temp_lo, W1.temp_hi, + W2.city, W2.temp_lo, W2.temp_hi + FROM weather W1, weather W2 + WHERE W1.temp_lo < W2.temp_lo + and W1.temp_hi > W2.temp_hi; + + +--------------+---------+---------+---------------+---------+---------+ + |city | temp_lo | temp_hi | city | temp_lo | temp_hi | + +--------------+---------+---------+---------------+---------+---------+ + |San Francisco | 43 | 57 | San Francisco | 46 | 50 | + +--------------+---------+---------+---------------+---------+---------+ + |San Francisco | 37 | 54 | San Francisco | 46 | 50 | + +--------------+---------+---------+---------------+---------+---------+ +</pre> + In this case, both W1 and W2 are surrogates for an + instance of the class weather, and both range over all + instances of the class. (In the terminology of most + database systems, W1 and W2 are known as "range variables.") + A query can contain an arbitrary number of + class names and surrogates.<A HREF="#3">3</A> + +<H2><A NAME="updates">4.7. Updates</A></H2> + You can update existing instances using the update command. + Suppose you discover the temperature readings are + all off by 2 degrees as of Nov 28, you may update the + data as follow: +<pre> * UPDATE weather + SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 + WHERE date > '11/28/1994; +</pre> + +<H2><A NAME="deletions">4.8. Deletions</A></H2> + Deletions are performed using the <B>delete</B> command: +<pre> * DELETE FROM weather WHERE city = 'Hayward'; +</pre> + All weather recording belongs to Hayward is removed. + One should be wary of queries of the form +<pre> DELETE FROM classname; +</pre> + Without a qualification, the delete command will simply + delete all instances of the given class, leaving it + empty. The system will not request confirmation before + doing this. + +<H2><A NAME="using-aggregate-functions">4.9. Using Aggregate Functions</A></H2> + Like most other query languages, POSTGRES supports + aggregate functions. However, the current + implementation of POSTGRES aggregate functions is very limited. + Specifically, while there are aggregates to compute + such functions as the <B>count, sum, average, maximum</B> and + <B>minimum</B> over a set of instances, aggregates can only + appear in the target list of a query and not in the + qualification ( where clause) As an example, +<pre> SELECT max(temp_lo) + FROM weather; +</pre> + Aggregates may also have <B>GROUP BY</B> clauses: +<pre> + SELECT city, max(temp_lo) + FROM weather + GROUP BY city; +</pre> +<HR> + <A NAME="2"><B>2.</B></A> This is only a conceptual model. The actual join may + be performed in a more efficient manner, but this is invisible to the user.<br> + + <A NAME="3"><B>3.</B></A> The semantics of such a join are + that the qualification + is a truth expression defined for the Cartesian product of + the classes indicated in the query. For those instances in + the Cartesian product for which the qualification is true, + POSTGRES computes and returns the values specified in the + target list. POSTGRES <B>SQL</B> does not assign any meaning to + duplicate values in such expressions. This means that POSTGRES + sometimes recomputes the same target list several times + this frequently happens when Boolean expressions are connected + with an or. To remove such duplicates, you must use + the select distinct statement. + +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="start.html">[ Previous ]</A> +<A HREF="advanced.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/refs.html b/doc/manual/refs.html new file mode 100644 index 00000000000..064e9918aa7 --- /dev/null +++ b/doc/manual/refs.html @@ -0,0 +1,55 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - REFERENCES</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="rules.html">[ Previous ]</A> +<A HREF="appenda.html">[ Next ]</A> +</font> +<HR> +<H1>16. REFERENCES</H1> +<HR> +<DL COMPACT> + <DT><A NAME="DATE93"><B>[DATE93]</B></A><DD>Date, C. J. and Darwen, Hugh, A Guide to The + SQL Standard, 3rd Edition, Reading, MA, June + 1993. + <DT><A NAME="MELT93"><B>[MELT93]</B></A><DD>Melton, J. Understanding the New SQL, 1994. + <DT><A NAME="ONG90"><B>[ONG90]</B></A><DD>Ong, L. and Goh, J., ``A Unified Framework + for Version Modeling Using Production Rules + in a Database System," Electronics Research + Laboratory, University of California, ERL + Technical Memorandum M90/33, Berkeley, CA, + April 1990. + <DT><A NAME="ROWE87"><B>[ROWE87]</B></A><DD>Rowe, L. and Stonebraker, M., ``The POSTGRES + Data Model,'' Proc. 1987 VLDB Conference, + Brighton, England, Sept. 1987. + <DT><A NAME="STON86"><B>[STON86]</B></A><DD>Stonebraker, M. and Rowe, L., ``The Design + of POSTGRES,'' Proc. 1986 ACM-SIGMOD Conference on Management of Data, Washington, DC, + May 1986. + <DT><A NAME="STON87a"><B>[STON87a]</B></A><DD>Stonebraker, M., Hanson, E. and Hong, C.-H., + ``The Design of the POSTGRES Rules System,'' + Proc. 1987 IEEE Conference on Data Engineering, Los Angeles, CA, Feb. 1987. + <DT><A NAME="STON87b"><B>[STON87b]</B></A><DD>Stonebraker, M., ``The POSTGRES Storage System,'' Proc. 1987 VLDB Conference, Brighton, + England, Sept. 1987. + <DT><A NAME="STON89"><B>[STON89]</B></A><DD>Stonebraker, M., Hearst, M., and Potamianos, + S., ``A Commentary on the POSTGRES Rules + System,'' SIGMOD Record 18(3), Sept. 1989. + <DT><A NAME="STON90a"><B>[STON90a]</B></A><DD>Stonebraker, M., Rowe, L. A., and Hirohama, + M., ``The Implementation of POSTGRES,'' IEEE + Transactions on Knowledge and Data Engineering 2(1), March 1990. + <DT><A NAME="STON90b"><B>[STON90b]</B></A><DD>Stonebraker, M. et al., ``On Rules, Procedures, Caching and Views in Database Systems,'' Proc. 1990 ACM-SIGMOD Conference on + Management of Data, Atlantic City, N.J., + June 1990. +</DL> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="rules.html">[ Previous ]</A> +<A HREF="appenda.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/rules.html b/doc/manual/rules.html new file mode 100644 index 00000000000..927cfd6f1c6 --- /dev/null +++ b/doc/manual/rules.html @@ -0,0 +1,43 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - THE POSTGRES RULE SYSTEM</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="lobj.html">[ Previous ]</A> +<A HREF="admin.html">[ Next ]</A> +</font> +<HR> +<H1>14. THE POSTGRES RULE SYSTEM +</H1><HR> + Production rule systems are conceptually simple, but + there are many subtle points involved in actually using + them. Consequently, we will not attempt to explain the + actual syntax and operation of the POSTGRES rule system + here. Instead, you should read <A HREF="refs.html#STON90b">[STON90b]</A> to understand + some of these points and the theoretical foundations of + the POSTGRES rule system before trying to use rules. + The discussion in this section is intended to provide + an overview of the POSTGRES rule system and point the + user at helpful references and examples. + The "query rewrite" rule system modifies queries to + take rules into consideration, and then passes the modified + query to the query optimizer for execution. It + is very powerful, and can be used for many things such + as query language procedures, views, and versions. The + power of this rule system is discussed in + <A HREF="refs.html#ONG90">[ONG90]</A> as + well as <A HREF="refs.html#STON90b">[STON90b]</A>. +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="lobj.html">[ Previous ]</A> +<A HREF="admin.html">[ Next ]</A> +</font> +</BODY> +</HTML> + + diff --git a/doc/manual/start.html b/doc/manual/start.html new file mode 100644 index 00000000000..db9960661b6 --- /dev/null +++ b/doc/manual/start.html @@ -0,0 +1,231 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - GETTING STARTED</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="architec.html">[ Previous ]</A> +<A HREF="query.html">[ Next ]</A> +</font> +<HR> +<H1>3. GETTING STARTED WITH POSTGRES</H1> +<HR> + This section discusses how to start POSTGRES and set up + your own environment so that you can use frontend + applications. We assume POSTGRES has already been + successfully installed. (Refer to the installation notes + for how to install POSTGRES.) +<p> + Some of the steps listed in this section will apply to + all POSTGRES users, and some will apply primarily to + the site database administrator. This site administrator + is the person who installed the software, created + the database directories and started the <B>postmaster</B> + process. This person does not have to be the UNIX + superuser, "root," or the computer system administrator. + In this section, items for end users are labelled + "User" and items intended for the site administrator + are labelled "Admin." + Throughout this manual, any examples that begin with + the character ``%'' are commands that should be typed + at the UNIX shell prompt. Examples that begin with the + character ``*'' are commands in the POSTGRES query + language, POSTGRES <B>SQL</B>. + +<H2><A NAME="setting-up-your-environment">3.1. Admin/User: Setting Up Your Environment</A></H2> + <IMG SRC="figure02.gif" ALT="Figure 2. POSTGRES file layout."> + Figure 2. shows how the POSTGRES distribution is laid + out when installed in the default way. For simplicity, + we will assume that POSTGRES has been installed in the + directory /usr/local/postgres95. Therefore, wherever + you see the directory /usr/local/postgres95 you should + substitute the name of the directory where POSTGRES is + actually installed. + All POSTGRES commands are installed in the directory + /usr/local/postgres95/bin. Therefore, you should add + this directory to your shell command path. If you use + a variant of the Berkeley C shell, such as csh or tcsh, + you would add +<pre> % set path = ( /usr/local/postgres95/bin $path ) +</pre> + in the .login file in your home directory. If you use + a variant of the Bourne shell, such as sh, ksh, or + bash, then you would add +<pre> + % PATH=/usr/local/postgres95/bin:$PATH + % export PATH +</pre> + to the .profile file in your home directory. + From now on, we will assume that you have added the + POSTGRES bin directory to your path. In addition, we + will make frequent reference to "setting a shell + variable" or "setting an environment variable" throughout + this document. If you did not fully understand the + last paragraph on modifying your search path, you + should consult the UNIX manual pages that describe your + shell before going any further. + +<H2><A NAME="starting-the-postmaster">3.2. Admin: Starting the <B>Postmaster</A></B></H2> + It should be clear from the preceding discussion that + nothing can happen to a database unless the <B>postmaster</B> + process is running. As the site administrator, there + are a number of things you should remember before + starting the <B>postmaster</B>. These are discussed in the + section of this manual titled, "Administering POSTGRES." + However, if POSTGRES has been installed by following + the installation instructions exactly as written, the + following simple command is all you should + need to start the <B>postmaster</B>: +<pre> % postmaster & +</pre> + The <B>postmaster</B> occasionally prints out messages which + are often helpful during troubleshooting. If you wish + to view debugging messages from the <B>postmaster</B>, you can + start it with the -d option and redirect the output to + the log file: +<pre> % postmaster -d >& pm.log & +</pre> + If you do not wish to see these messages, you can type +<pre> % postmaster -S +</pre> + and the <B>postmaster</B> will be "S"ilent. Notice that there + is no ampersand ("&") at the end of the last example. + +<H2><A NAME="adding-and-deleting-users">3.3. Admin: Adding and Deleting Users</A></H2> + The createuser command enables specific users to access + POSTGRES. The destroyuser command removes users and + prevents them from accessing POSTGRES. Note that these + commands only affect users with respect to POSTGRES; + they have no effect administration of users that the + operating system manages. + +<H2><A NAME="starting-applications">3.4. User: Starting Applications</A></H2> + Assuming that your site administrator has properly + started the <B>postmaster</B> process and authorized you to + use the database, you (as a user) may begin to start up + applications. As previously mentioned, you should add + /usr/local/postgres95/bin to your shell search path. + In most cases, this is all you should have to do in + terms of preparation.<A HREF="#1">1</A> + If you get the following error message from a POSTGRES + command (such as <B>psql</B> or createdb): +<pre> connectDB() failed: Is the postmaster running at 'localhost' on port '4322'? +</pre> + it is usually because (1) the <B>postmaster</B> is not running, or (2) you are attempting to connect to the wrong + server host. + If you get the following error message: +<pre> FATAL 1:Feb 17 23:19:55:process userid (2360) != + database owner (268) +</pre> + it means that the site administrator started the <B>postmaster</B> as the wrong user. Tell him to restart it as + the POSTGRES superuser. + +<H2><A NAME="managing-a-database">3.5. User: Managing a Database</A></H2> + Now that POSTGRES is up and running we can create some + databases to experiment with. Here, we describe the + basic commands for managing a database. + +<H3><A NAME="creating-a-database">3.5.1. Creating a Database</A></H3> + Let's say you want to create a database named mydb. + You can do this with the following command: +<pre> % createdb mydb +</pre> + + POSTGRES allows you to create any number of databases + at a given site and you automatically become the + database administrator of the database you just created. Database names must have an alphabetic first + character and are limited to 16 characters in length. + Not every user has authorization to become a database + administrator. If POSTGRES refuses to create databases + for you, then the site administrator needs to grant you + permission to create databases. Consult your site + administrator if this occurs. + +<H3><A NAME="accessing-a-database">3.5.2. Accessing a Database</A></H3> + Once you have constructed a database, you can access it + by: + <UL> + <LI>running the POSTGRES terminal monitor programs ( + monitor or <B>psql</B>) which allows you to interactively + enter, edit, and execute <B>SQL</B> commands. + <LI>writing a C program using the LIBPQ subroutine + library. This allows you to submit <B>SQL</B> commands + from C and get answers and status messages back to + your program. This interface is discussed further + in section ??. + </UL> + You might want to start up <B>psql</B>, to try out the examples in this manual. It can be activated for the mydb + database by typing the command: +<pre> % psql mydb +</pre> + You will be greeted with the following message: +<pre> Welcome to the POSTGRES95 interactive sql monitor: + + type \? for help on slash commands + type \q to quit + type \g or terminate with semicolon to execute query + You are currently connected to the database: mydb + + mydb=> +</pre> This prompt indicates that the terminal monitor is listening to you and that you can type <B>SQL</B> queries into a + workspace maintained by the terminal monitor. + The <B>psql</B> program responds to escape codes that begin + with the backslash character, "\". For example, you + can get help on the syntax of various POSTGRES <B>SQL</B> commands by typing: +<pre> mydb=> \h +</pre> + Once you have finished entering your queries into the + workspace, you can pass the contents of the workspace + to the POSTGRES server by typing: +<pre> mydb=> \g +</pre> + This tells the server to process the query. If you + terminate your query with a semicolon, the \g is not + necessary. <B>psql</B> will automatically process semicolon terminated queries. + To read queries from a file, say myFile, instead of + entering them interactively, type: +<pre> mydb=> \i fileName +</pre> + To get out of <B>psql</B> and return to UNIX, type +<pre> mydb=> \q +</pre> + and <B>psql</B> will quit and return you to your command + shell. (For more escape codes, type \h at the monitor + prompt.) + White space (i.e., spaces, tabs and newlines) may be + used freely in <B>SQL</B> queries. Comments are denoted by + <b>--</b>. Everything after the dashes up to the end of the + line is ignored. + +<H3><A NAME="detroying-a-database">3.5.3. Destroying a Database</A></H3> + If you are the database administrator for the database + mydb, you can destroy it using the following UNIX command: +<pre> % destroydb mydb +</pre> + This action physically removes all of the UNIX files + associated with the database and cannot be undone, so + this should only be done with a great deal of fore-thought. + +<p> +<HR> + +<A NAME="1"><B>1.</B></A> If your site administrator has not set things up in the +default way, you may have some more work to do. For example, if the database server machine is a remote machine, you +will need to set the <B>PGHOST</B> environment variable to the name +of the database server machine. The environment variable +<B>PGPORT</B> may also have to be set. The bottom line is this: if +you try to start an application program and it complains +that it cannot connect to the <B>postmaster</B>, you should immediately consult your site administrator to make sure that your +environment is properly set up. + +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="architec.html">[ Previous ]</A> +<A HREF="query.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/xaggr.html b/doc/manual/xaggr.html new file mode 100644 index 00000000000..28707221648 --- /dev/null +++ b/doc/manual/xaggr.html @@ -0,0 +1,109 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - EXTENDING SQL: AGGREGATES</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xoper.html">[ Previous ]</A> +<A HREF="xindex.html">[ Next ]</A> +</font> +<HR> +<H1>10. EXTENDING SQL: AGGREGATES</H1> +<HR> + Aggregates in POSTGRES are expressed in terms of state + transition functions. That is, an aggregate can be + defined in terms of state that is modified whenever an + instance is processed. Some state functions look at a + particular value in the instance when computing the new + state (<B>sfunc1</B> in the create aggregate syntax) while + others only keep track of their own internal state + (<B>sfunc2</B>). + If we define an aggregate that uses only <B>sfunc1</B>, we + define an aggregate that computes a running function of + the attribute values from each instance. "Sum" is an + example of this kind of aggregate. "Sum" starts at + zero and always adds the current instance's value to + its running total. We will use the <B>int4pl</B> that is + built into POSTGRES to perform this addition. + +<pre> CREATE AGGREGATE complex_sum ( + sfunc1 = complex_add, + basetype = complex, + stype1 = complex, + initcond1 = '(0,0)' + ); + + + SELECT complex_sum(a) FROM test_complex; + + + +------------+ + |complex_sum | + +------------+ + |(34,53.9) | + +------------+ +</pre> + + If we define only <B>sfunc2</B>, we are specifying an aggregate + that computes a running function that is independent of + the attribute values from each instance. + "Count" is the most common example of this kind of + aggregate. "Count" starts at zero and adds one to its + running total for each instance, ignoring the instance + value. Here, we use the built-in <B>int4inc</B> routine to do + the work for us. This routine increments (adds one to) + its argument. + +<pre> CREATE AGGREGATE my_count (sfunc2 = int4inc, -- add one + basetype = int4, stype2 = int4, + initcond2 = '0') + + SELECT my_count(*) as emp_count from EMP; + + + +----------+ + |emp_count | + +----------+ + |5 | + +----------+ +</pre> + + "Average" is an example of an aggregate that requires + both a function to compute the running sum and a function + to compute the running count. When all of the + instances have been processed, the final answer for the + aggregate is the running sum divided by the running + count. We use the <B>int4pl</B> and <B>int4inc</B> routines we used + before as well as the POSTGRES integer division + routine, <B>int4div</B>, to compute the division of the sum by + the count. + +<pre> CREATE AGGREGATE my_average (sfunc1 = int4pl, -- sum + basetype = int4, + stype1 = int4, + sfunc2 = int4inc, -- count + stype2 = int4, + finalfunc = int4div, -- division + initcond1 = '0', + initcond2 = '0') + + SELECT my_average(salary) as emp_average FROM EMP; + + + +------------+ + |emp_average | + +------------+ + |1640 | + +------------+ +</pre> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xoper.html">[ Previous ]</A> +<A HREF="xindex.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/xfunc.html b/doc/manual/xfunc.html new file mode 100644 index 00000000000..557e9ec0bf1 --- /dev/null +++ b/doc/manual/xfunc.html @@ -0,0 +1,474 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - EXTENDING SQL: FUNCTIONS</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="extend.html">[ Previous ]</A> +<A HREF="xtypes.html">[ Next ]</A> +</font> +<HR> +<H1>7. EXTENDING <B>SQL</B>: FUNCTIONS</H1> +<HR> + As it turns out, part of defining a new type is the + definition of functions that describe its behavior. + Consequently, while it is possible to define a new + function without defining a new type, the reverse is + not true. We therefore describe how to add new functions + to POSTGRES before describing how to add new + types. + POSTGRES <B>SQL</B> provides two types of functions: query + language functions (functions written in <B>SQL</B> and + programming language functions (functions written in a + compiled programming language such as <B>C</B>.) Either kind + of function can take a base type, a composite type or + some combination as arguments (parameters). In addition, + both kinds of functions can return a base type or + a composite type. It's easier to define <B>SQL</B> functions, + so we'll start with those. + Examples in this section can also be found in <CODE>funcs.sql</CODE> + and <CODE>C-code/funcs.c</CODE>. +<p> +<H2><A NAME="query-language-sql-functions">7.1. Query Language (<B>SQL</B>) Functions</A></H2> + +<H3><A NAME="sql-functions-on-base-types">7.1.1. <B>SQL</B> Functions on Base Types</A></H3> + The simplest possible <B>SQL</B> function has no arguments and + simply returns a base type, such as <B>int4</B>: + +<pre> CREATE FUNCTION one() RETURNS int4 + AS 'SELECT 1 as RESULT' LANGUAGE 'sql'; + + + SELECT one() AS answer; + + +-------+ + |answer | + +-------+ + |1 | + +-------+ +</pre> + Notice that we defined a target list for the function + (with the name RESULT), but the target list of the + query that invoked the function overrode the function's + target list. Hence, the result is labelled answer + instead of one. +<p> + It's almost as easy to define <B>SQL</B> functions that take + base types as arguments. In the example below, notice + how we refer to the arguments within the function as $1 + and $2. + +<pre> CREATE FUNCTION add_em(int4, int4) RETURNS int4 + AS 'SELECT $1 + $2;' LANGUAGE 'sql'; + + + SELECT add_em(1, 2) AS answer; + + + +-------+ + |answer | + +-------+ + |3 | + +-------+ +</pre> + +<H3>7.1.2. <B>SQL</B> Functions on Composite Types</H3> + When specifying functions with arguments of composite + types (such as EMP), we must not only specify which + argument we want (as we did above with $1 and $2) but + also the attributes of that argument. For example, + take the function double_salary that computes what your + salary would be if it were doubled. + +<pre> CREATE FUNCTION double_salary(EMP) RETURNS int4 + AS 'SELECT $1.salary * 2 AS salary;' LANGUAGE 'sql'; + + SELECT name, double_salary(EMP) AS dream + FROM EMP + WHERE EMP.dept = 'toy'; + + + +-----+-------+ + |name | dream | + +-----+-------+ + |Sam | 2400 | + +-----+-------+ +</pre> + Notice the use of the syntax $1.salary. + Before launching into the subject of functions that + return composite types, we must first introduce the + function notation for projecting attributes. The simple way + to explain this is that we can usually use the + notation attribute(class) and class.attribute interchangably. + +<pre> -- + -- this is the same as: + -- SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30 + -- + SELECT name(EMP) AS youngster + FROM EMP + WHERE age(EMP) < 30; + + + +----------+ + |youngster | + +----------+ + |Sam | + +----------+ +</pre> + As we shall see, however, this is not always the case. + This function notation is important when we want to use + a function that returns a single instance. We do this + by assembling the entire instance within the function, + attribute by attribute. This is an example of a function + that returns a single EMP instance: + +<pre> CREATE FUNCTION new_emp() RETURNS EMP + AS 'SELECT \'None\'::text AS name, + 1000 AS salary, + 25 AS age, + \'none\'::char16 AS dept;' + LANGUAGE 'sql'; +</pre> + + In this case we have specified each of the attributes + with a constant value, but any computation or expression + could have been substituted for these constants. + Defining a function like this can be tricky. Some of + the more important caveats are as follows: + + + <UL> + <LI>The target list order must be exactly the same as + that in which the attributes appear in the <B>CREATE + TABLE</B> statement (or when you execute a .* query). + <LI>You must be careful to typecast the expressions + (using ::) very carefully or you will see the following error: + +<pre> WARN::function declared to return type EMP does not retrieve (EMP.*) +</pre> + <LI>When calling a function that returns an instance, we + cannot retrieve the entire instance. We must either + project an attribute out of the instance or pass the + entire instance into another function. +<pre> SELECT name(new_emp()) AS nobody; + + + +-------+ + |nobody | + +-------+ + |None | + +-------+ +</pre> + <LI>The reason why, in general, we must use the function + syntax for projecting attributes of function return + values is that the parser just doesn't understand + the other (dot) syntax for projection when combined + with function calls. + +<pre> SELECT new_emp().name AS nobody; + WARN:parser: syntax error at or near "." +</pre> + </UL> + + Any collection of commands in the <B>SQL</B> query language + can be packaged together and defined as a function. + The commands can include updates (i.e., <B>insert</B>, <B>update</B> + and <B>delete</B>) as well as <B>select</B> queries. However, the + final command must be a <B>select</B> that returns whatever is + specified as the function's returntype. + +<pre> + CREATE FUNCTION clean_EMP () RETURNS int4 + AS 'DELETE FROM EMP WHERE EMP.salary <= 0; + SELECT 1 AS ignore_this' + LANGUAGE 'sql'; + + SELECT clean_EMP(); + + + +--+ + |x | + +--+ + |1 | + +--+ +</pre> +<p> + +<H2><A NAME="programming-language-functions">7.2. Programming Language Functions</A></H2> +<H3><A NAME="programming-language-functions-on-base-types">7.2.1. Programming Language Functions on Base Types</A></H3> + Internally, POSTGRES regards a base type as a "blob of + memory." The user-defined functions that you define + over a type in turn define the way that POSTGRES can + operate on it. That is, POSTGRES will only store and + retrieve the data from disk and use your user-defined + functions to input, process, and output the data. + Base types can have one of three internal formats: + <UL> + <LI>pass by value, fixed-length + <LI>pass by reference, fixed-length + <LI>pass by reference, variable-length + </UL> + By-value types can only be 1, 2 or 4 bytes in length + (even if your computer supports by-value types of other + sizes). POSTGRES itself only passes integer types by + value. You should be careful to define your types such + that they will be the same size (in bytes) on all + architectures. For example, the <B>long</B> type is dangerous + because it is 4 bytes on some machines and 8 bytes on + others, whereas <B>int</B> type is 4 bytes on most <B>UNIX</B> + machines (though not on most personal computers). A + reasonable implementation of the <B>int4</B> type on <B>UNIX</B> + machines might be: + +<pre> /* 4-byte integer, passed by value */ + typedef int int4; +</pre> + + On the other hand, fixed-length types of any size may + be passed by-reference. For example, here is a sample + implementation of the POSTGRES char16 type: + +<pre> /* 16-byte structure, passed by reference */ + typedef struct { + char data[16]; + } char16; +</pre> + + Only pointers to such types can be used when passing + them in and out of POSTGRES functions. + Finally, all variable-length types must also be passed + by reference. All variable-length types must begin + with a length field of exactly 4 bytes, and all data to + be stored within that type must be located in the memory + immediately following that length field. The + length field is the total length of the structure + (i.e., it includes the size of the length field + itself). We can define the text type as follows: + +<pre> typedef struct { + int4 length; + char data[1]; + } text; +</pre> + + Obviously, the data field is not long enough to hold + all possible strings -- it's impossible to declare such + a structure in <B>C</B>. When manipulating variable-length + types, we must be careful to allocate the correct + amount of memory and initialize the length field. For + example, if we wanted to store 40 bytes in a text + structure, we might use a code fragment like this: + +<pre> #include "postgres.h" + #include "utils/palloc.h" + + ... + + char buffer[40]; /* our source data */ + + ... + + text *destination = (text *) palloc(VARHDRSZ + 40); + destination->length = VARHDRSZ + 40; + memmove(destination->data, buffer, 40); + + ... + +</pre> + Now that we've gone over all of the possible structures + for base types, we can show some examples of real functions. + Suppose <CODE>funcs.c</CODE> look like: + +<pre> #include <string.h> + #include "postgres.h" /* for char16, etc. */ + #include "utils/palloc.h" /* for palloc */ + + int + add_one(int arg) + { + return(arg + 1); + } + + char16 * + concat16(char16 *arg1, char16 *arg2) + { + char16 *new_c16 = (char16 *) palloc(sizeof(char16)); + + memset((void *) new_c16, 0, sizeof(char16)); + (void) strncpy(new_c16, arg1, 16); + return (char16 *)(strncat(new_c16, arg2, 16)); + } +<p> + text * + copytext(text *t) + { + /* + * VARSIZE is the total size of the struct in bytes. + */ + text *new_t = (text *) palloc(VARSIZE(t)); +<p> + memset(new_t, 0, VARSIZE(t)); +<p> + VARSIZE(new_t) = VARSIZE(t); + /* + * VARDATA is a pointer to the data region of the struct. + */ + memcpy((void *) VARDATA(new_t), /* destination */ + (void *) VARDATA(t), /* source */ + VARSIZE(t)-VARHDRSZ); /* how many bytes */ +<p> + return(new_t); + } +</pre> + On <B>OSF/1</B> we would type: + +<pre> CREATE FUNCTION add_one(int4) RETURNS int4 + AS '/usr/local/postgres95/tutorial/obj/funcs.so' LANGUAGE 'c'; + + CREATE FUNCTION concat16(char16, char16) RETURNS char16 + AS '/usr/local/postgres95/tutorial/obj/funcs.so' LANGUAGE 'c'; + + CREATE FUNCTION copytext(text) RETURNS text + AS '/usr/local/postgres95/tutorial/obj/funcs.so' LANGUAGE 'c'; +</pre> + + On other systems, we might have to make the filename + end in .sl (to indicate that it's a shared library). +<p> +<H3><A NAME="programming-language-functions-on-composite-types">7.2.2. Programming Language Functions on Composite Types</A></H3> + Composite types do not have a fixed layout like C + structures. Instances of a composite type may contain + null fields. In addition, composite types that are + part of an inheritance hierarchy may have different + fields than other members of the same inheritance hierarchy. + Therefore, POSTGRES provides a procedural + interface for accessing fields of composite types from + C. + As POSTGRES processes a set of instances, each instance + will be passed into your function as an opaque structure of type <B>TUPLE</B>. + Suppose we want to write a function to answer the query + +<pre> * SELECT name, c_overpaid(EMP, 1500) AS overpaid + FROM EMP + WHERE name = 'Bill' or name = 'Sam'; +</pre> + In the query above, we can define c_overpaid as: + +<pre> #include "postgres.h" /* for char16, etc. */ + #include "libpq-fe.h" /* for TUPLE */ +<p> + bool + c_overpaid(TUPLE t,/* the current instance of EMP */ + int4 limit) + { + bool isnull = false; + int4 salary; +<p> + salary = (int4) GetAttributeByName(t, "salary", &isnull); +<p> + if (isnull) + return (false); + return(salary > limit); + } +</pre> + + <B>GetAttributeByName</B> is the POSTGRES system function that + returns attributes out of the current instance. It has + three arguments: the argument of type TUPLE passed into + the function, the name of the desired attribute, and a + return parameter that describes whether the attribute + is null. <B>GetAttributeByName</B> will align data properly + so you can cast its return value to the desired type. + For example, if you have an attribute name which is of + the type char16, the <B>GetAttributeByName</B> call would look + like: + +<pre> char *str; + ... + str = (char *) GetAttributeByName(t, "name", &isnull) +</pre> + + The following query lets POSTGRES know about the + c_overpaid function: + +<pre> * CREATE FUNCTION c_overpaid(EMP, int4) RETURNS bool + AS '/usr/local/postgres95/tutorial/obj/funcs.so' LANGUAGE 'c'; +</pre> + While there are ways to construct new instances or modify + existing instances from within a C function, these + are far too complex to discuss in this manual. +<p> +<H3><A NAME="caveats">7.2.3. Caveats</A></H3> + We now turn to the more difficult task of writing + programming language functions. Be warned: this section + of the manual will not make you a programmer. You must + have a good understanding of <B>C</B> (including the use of + pointers and the malloc memory manager) before trying + to write <B>C</B> functions for use with POSTGRES. + While it may be possible to load functions written in + languages other than <B>C</B> into POSTGRES, this is often + difficult (when it is possible at all) because other + languages, such as <B>FORTRAN</B> and <B>Pascal</B> often do not follow + the same "calling convention" as <B>C</B>. That is, other + languages do not pass argument and return values + between functions in the same way. For this reason, we + will assume that your programming language functions + are written in <B>C</B>. + The basic rules for building <B>C</B> functions are as follows: + <OL> + <LI> Most of the header (include) files for POSTGRES + should already be installed in + /usr/local/postgres95/include (see Figure 2). + You should always include + +<pre> -I/usr/local/postgres95/include +</pre> + on your cc command lines. Sometimes, you may + find that you require header files that are in + the server source itself (i.e., you need a file + we neglected to install in include). In those + cases you may need to add one or more of +<pre> + -I/usr/local/postgres95/src/backend + -I/usr/local/postgres95/src/backend/include + -I/usr/local/postgres95/src/backend/port/<PORTNAME> + -I/usr/local/postgres95/src/backend/obj +</pre> + + (where <PORTNAME> is the name of the port, e.g., + alpha or sparc). + <LI> When allocating memory, use the POSTGRES + routines palloc and pfree instead of the + corresponding <B>C</B> library routines malloc and free. + The memory allocated by palloc will be freed + automatically at the end of each transaction, + preventing memory leaks. + <LI> Always zero the bytes of your structures using + memset or bzero. Several routines (such as the + hash access method, hash join and the sort algorithm) + compute functions of the raw bits contained in + your structure. Even if you initialize all fields + of your structure, there may be + several bytes of alignment padding (holes in the + structure) that may contain garbage values. + <LI> Most of the internal POSTGRES types are declared + in postgres.h, so it's usually a good idea to + include that file as well. + <LI> Compiling and loading your object code so that + it can be dynamically loaded into POSTGRES + always requires special flags. See Appendix A + for a detailed explanation of how to do it for + your particular operating system. + </OL> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="extend.html">[ Previous ]</A> +<A HREF="xtypes.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/xindex.html b/doc/manual/xindex.html new file mode 100644 index 00000000000..aca6b3712eb --- /dev/null +++ b/doc/manual/xindex.html @@ -0,0 +1,430 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - THE QUERY LANGUAGE</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xaggr.html">[ Previous ]</A> +<A HREF="libpq.html">[ Next ]</A> +</font> +<HR> +<H1>11. INTERFACING EXTENSIONS TO INDICES</H1> +<HR> + The procedures described thus far let you define a new + type, new functions and new operators. However, we + cannot yet define a secondary index (such as a <B>B-tree</B>, + <B>R-tree</B> or hash access method) over a new type or its + operators.<p> + + <A HREF="extend.html#about-the-postgres-system-catalogs">Look back at Figure 3</A>. + The right half shows the catalogs + that we must modify in order to tell POSTGRES how + to use a user-defined type and/or user-defined operators + with an index (i.e., <CODE>pg_am, pg_amop, pg_amproc</CODE> and + <CODE>pg_opclass</CODE>). Unfortunately, there is no simple command + to do this. We will demonstrate how to modify these + catalogs through a running example: a new operator + class for the <B>B-tree</B> access method that sorts integers + in ascending absolute value order.<p> + + The <CODE>pg_am</CODE> class contains one instance for every user + defined access method. Support for the heap access + method is built into POSTGRES, but every other access + method is described here. The schema is +<p> +<center> +<table border=1> +<tr> + <td>amname </td><td> name of the access method </td> +</tr> +<td>amowner </td><td> object id of the owner's instance in pg_user </td> +</tr> +<tr> +<td>amkind </td><td> not used at present, but set to 'o' as a place holder </td> +</tr> +<tr> +<td>amstrategies </td><td> number of strategies for this access method (see below) </td> +</tr> +<tr> +<td>amsupport </td><td> number of support routines for this access method (see below) </td> +</tr> +<tr> +<td>amgettuple<br> + aminsert<br> + ...</td> +<td>procedure identifiers for interface routines to the access + method. For example, regproc ids for opening, closing, and + getting instances from the access method appear here. </td> +</tr> +</table> +</center> + +<p> + + The <B>object ID</B> of the instance in <CODE>pg_am</CODE> is used as a + foreign key in lots of other classes. You don't need + to add a new instance to this class; all you're interested in + is the <B>object ID</B> of the access method instance + you want to extend: + +<pre> SELECT oid FROM pg_am WHERE amname = 'btree' + + +----+ + |oid | + +----+ + |403 | + +----+ +</pre> + + The <CODE>amstrategies</CODE> attribute exists to standardize + comparisons across data types. For example, <B>B-tree</B>s + impose a strict ordering on keys, lesser to greater. + Since POSTGRES allows the user to define operators, + POSTGRES cannot look at the name of an operator (eg, > + or <) and tell what kind of comparison it is. In fact, + some access methods don't impose any ordering at all. + For example, <B>R-tree</B>s express a rectangle-containment + relationship, whereas a hashed data structure expresses + only bitwise similarity based on the value of a hash + function. POSTGRES needs some consistent way of taking + a qualification in your query, looking at the operator + and then deciding if a usable index exists. This + implies that POSTGRES needs to know, for example, that + the <= and > operators partition a <B>B-tree</B>. POSTGRES + uses strategies to express these relationships between + operators and the way they can be used to scan indices.<p> + + Defining a new set of strategies is beyond the scope of + this discussion, but we'll explain how <B>B-tree</B> strategies + work because you'll need to know that to add a new + operator class. In the <CODE>pg_am</CODE> class, the amstrategies + attribute is the number of strategies defined for this + access method. For <B>B-tree</B>s, this number is 5. These + strategies correspond to +<p> + +<center> +<table border=1> +<tr> + <td>less than </td><td> 1 </td> +</tr> +<tr> + <td>less than or equal </td><td> 2 </td> +</tr> +<tr> + <td>equal </td><td> 3 </td> +</tr> +<tr> + <td>greater than or equal </td><td> 4 </td> +</tr> +<tr> + <td>greater than </td><td> 5 </td> +</tr> +</table> +</center> +<p> + + The idea is that you'll need to add procedures corresponding + to the comparisons above to the <CODE>pg_amop</CODE> relation + (see below). The access method code can use these + strategy numbers, regardless of data type, to figure + out how to partition the <B>B-tree</B>, compute selectivity, + and so on. Don't worry about the details of adding + procedures yet; just understand that there must be a + set of these procedures for <CODE>int2, int4, oid,</CODE> and every + other data type on which a <B>B-tree</B> can operate. +<p> + Sometimes, strategies aren't enough information for the + system to figure out how to use an index. Some access + methods require other support routines in order to + work. For example, the <B>B-tree</B> access method must be + able to compare two keys and determine whether one is + greater than, equal to, or less than the other. + Similarly, the <B>R-tree</B> access method must be able to compute + intersections, unions, and sizes of rectangles. These + operations do not correspond to user qualifications in + SQL queries; they are administrative routines used by + the access methods, internally.<p> + + In order to manage diverse support routines + consistently across all POSTGRES access methods, <CODE>pg_am</CODE> + includes an attribute called <CODE>amsupport</CODE>. This attribute + records the number of support routines used by an + access method. For <B>B-tree</B>s, this number is one -- the + routine to take two keys and return -1, 0, or +1, + depending on whether the first key is less than, equal + to, or greater than the second.<A HREF="#8"><font size=-1>[8]</font></A><p> + + The <CODE>amstrategies</CODE> entry in pg_am is just the number of + strategies defined for the access method in question. + The procedures for less than, less equal, and so on + don't appear in <CODE>pg_am</CODE>. Similarly, <CODE>amsupport</CODE> is just + the number of support routines required by the access + method. The actual routines are listed elsewhere.<p> + + The next class of interest is pg_opclass. This class + exists only to associate a name with an oid. In + pg_amop, every <B>B-tree</B> operator class has a set of + procedures, one through five, above. Some existing + opclasses are <CODE>int2_ops, int4_ops, and oid_ops</CODE>. You + need to add an instance with your opclass name (for + example, <CODE>complex_abs_ops</CODE>) to <CODE>pg_opclass</CODE>. The <CODE>oid</CODE> of + this instance is a foreign key in other classes. + +<pre> INSERT INTO pg_opclass (opcname) VALUES ('complex_abs_ops'); + + SELECT oid, opcname + FROM pg_opclass + WHERE opcname = 'complex_abs_ops'; + + +------+--------------+ + |oid | opcname | + +------+--------------+ + |17314 | int4_abs_ops | + +------+--------------+ +</pre> + + Note that the oid for your <CODE>pg_opclass</CODE> instance will be + different! You should substitute your value for 17314 + wherever it appears in this discussion.<p> + + So now we have an access method and an operator class. + We still need a set of operators; the procedure for + defining operators was discussed earlier in this manual. + For the complex_abs_ops operator class on Btrees, + the operators we require are: + +<pre> absolute value less-than + absolute value less-than-or-equal + absolute value equal + absolute value greater-than-or-equal + absolute value greater-than +</pre> + + Suppose the code that implements the functions defined + is stored in the file + +<pre> + /usr/local/postgres95/src/tutorial/complex.c +</pre> + + Part of the code look like this: (note that we will + only show the equality operator for the rest of the + examples. The other four operators are very similar. + Refer to <CODE>complex.c</CODE> or <CODE>complex.sql</CODE> for the details.) + +<pre> #define Mag(c) ((c)->x*(c)->x + (c)->y*(c)->y) + + bool + complex_abs_eq(Complex *a, Complex *b) + { + double amag = Mag(a), bmag = Mag(b); + return (amag==bmag); + } +</pre> + + There are a couple of important things that are happening below.<p> + + First, note that operators for less-than, less-than-or + equal, equal, greater-than-or-equal, and greater-than + for <CODE>int4</CODE> are being defined. All of these operators are + already defined for <CODE>int4</CODE> under the names <, <=, =, >=, + and >. The new operators behave differently, of + course. In order to guarantee that POSTGRES uses these + new operators rather than the old ones, they need to be + named differently from the old ones. This is a key + point: you can overload operators in POSTGRES, but only + if the operator isn't already defined for the argument + types. That is, if you have < defined for (int4, + int4), you can't define it again. POSTGRES does not + check this when you define your operator, so be careful. + To avoid this problem, odd names will be used for + the operators. If you get this wrong, the access methods + are likely to crash when you try to do scans.<p> + + The other important point is that all the operator + functions return Boolean values. The access methods + rely on this fact. (On the other hand, the support + function returns whatever the particular access method + expects -- in this case, a signed integer.) + The final routine in the file is the "support routine" + mentioned when we discussed the amsupport attribute of + the <CODE>pg_am</CODE> class. We will use this later on. For now, + ignore it. + +<pre> CREATE FUNCTION complex_abs_eq(complex, complex) + RETURNS bool + AS '/usr/local/postgres95/tutorial/obj/complex.so' + LANGUAGE 'c'; +</pre> + + Now define the operators that use them. As noted, the + operator names must be unique among all operators that + take two <CODE>int4</CODE> operands. In order to see if the + operator names listed below are taken, we can do a query on + <CODE>pg_operator</CODE>: + +<pre> /* + * this query uses the regular expression operator (~) + * to find three-character operator names that end in + * the character & + */ + SELECT * + FROM pg_operator + WHERE oprname ~ '^..&$'::text; +</pre> + + to see if your name is taken for the types you want. + The important things here are the procedure (which are + the <B>C</B> functions defined above) and the restriction and + join selectivity functions. You should just use the + ones used below--note that there are different such + functions for the less-than, equal, and greater-than + cases. These must be supplied, or the access method + will crash when it tries to use the operator. You + should copy the names for restrict and join, but use + the procedure names you defined in the last step. + +<pre> CREATE OPERATOR = ( + leftarg = complex, rightarg = complex, procedure = complex_abs_eq, + restrict = eqsel, join = eqjoinsel + ) +</pre> + + Notice that five operators corresponding to less, less + equal, equal, greater, and greater equal are defined.<p> + + We're just about finished. the last thing we need to do + is to update the <CODE>pg_amop</CODE> relation. To do this, we need + the following attributes: + <p> + +<center> +<table border=1> + <td>amopid </td><td> the <CODE>oid</CODE> of the <CODE>pg_am</CODE> instance for B-tree + (== 403, see above) </td> +<tr> +</tr> + <td>amopclaid </td><td> the <CODE>oid</CODE> of the + <CODE>pg_opclass</CODE> instance for <CODE>int4_abs_ops</CODE> (== + whatever you got instead of <CODE>17314</CODE>, see above)</td> +<tr> +</tr> + <td>amopopr </td><td> the <CODE>oid</CODE>s of the operators for the opclass (which we'll + get in just a minute) </td> +<tr> +</tr> + <td>amopselect, amopnpages </td><td> cost functions.</td> +</tr> +</table> +</center> +<p> + The cost functions are used by the query optimizer to + decide whether or not to use a given index in a scan. + Fortunately, these already exist. The two functions + we'll use are <CODE>btreesel</CODE>, which estimates the selectivity + of the <B>B-tree</B>, and <CODE>btreenpage</CODE>, which estimates the + number of pages a search will touch in the tree.<p> + + So we need the <CODE>oid</CODE>s of the operators we just defined. + We'll look up the names of all the operators that take + two <CODE>int4</CODE>s, and pick ours out: + +<pre> SELECT o.oid AS opoid, o.oprname + INTO TABLE complex_ops_tmp + FROM pg_operator o, pg_type t + WHERE o.oprleft = t.oid and o.oprright = t.oid + and t.typname = 'complex'; + + which returns: + + +------+---------+ + |oid | oprname | + +------+---------+ + |17321 | < | + +------+---------+ + |17322 | <= | + +------+---------+ + |17323 | = | + +------+---------+ + |17324 | >= | + +------+---------+ + |17325 | > | + +------+---------+ +</pre> + + (Again, some of your <CODE>oid</CODE> numbers will almost certainly + be different.) The operators we are interested in are + those with <CODE>oid</CODE>s 17321 through 17325. The values you + get will probably be different, and you should + substitute them for the values below. We can look at the + operator names and pick out the ones we just added.<p> + + Now we're ready to update <CODE>pg_amop</CODE> with our new operator + class. The most important thing in this entire + discussion is that the operators are ordered, from less equal + through greater equal, in <CODE>pg_amop</CODE>. We add the + instances we need: + +<pre> INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy, + amopselect, amopnpages) + SELECT am.oid, opcl.oid, c.opoid, 3, + 'btreesel'::regproc, 'btreenpage'::regproc + FROM pg_am am, pg_opclass opcl, complex_ops_tmp c + WHERE amname = 'btree' and opcname = 'complex_abs_ops' + and c.oprname = '='; +</pre> + + Note the order: "less than" is 1, "less than or equal" + is 2, "equal" is 3, "greater than or equal" is 4, and + "greater than" is 5.<p> + + The last step (finally!) is registration of the + "support routine" previously described in our discussion of + <CODE>pg_am</CODE>. The <CODE>oid</CODE> of this support routine is stored in + the <CODE>pg_amproc</CODE> class, keyed by the access method <CODE>oid</CODE> and + the operator class <CODE>oid</CODE>. First, we need to register the + function in POSTGRES (recall that we put the <B>C</B> code + that implements this routine in the bottom of the file + in which we implemented the operator routines): + +<pre> CREATE FUNCTION int4_abs_cmp(int4, int4) + RETURNS int4 + AS '/usr/local/postgres95/tutorial/obj/complex.so' + LANGUAGE 'c'; + + SELECT oid, proname FROM pg_proc WHERE prname = 'int4_abs_cmp'; + + +------+--------------+ + |oid | proname | + +------+--------------+ + |17328 | int4_abs_cmp | + +------+--------------+ +</pre> + (Again, your <CODE>oid</CODE> number will probably be different and + you should substitute the value you see for the value + below.) Recalling that the <B>B-tree</B> instance's oid is + 403 and that of <CODE>int4_abs_ops</CODE> is 17314, we can add the + new instance as follows: + +<pre> INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) + VALUES ('403'::oid, -- btree oid + '17314'::oid, -- pg_opclass tuple + '17328'::oid, -- new pg_proc oid + '1'::int2); +</pre> +<p> +<HR> +<A NAME="8"><B>[8]</B></A> Strictly speaking, this routine can return a negative +number (< 0), 0, or a non-zero positive number (> 0). +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xaggr.html">[ Previous ]</A> +<A HREF="libpq.html">[ Next ]</A> +</font> +</BODY> +</HTML> diff --git a/doc/manual/xoper.html b/doc/manual/xoper.html new file mode 100644 index 00000000000..10e5e203c1b --- /dev/null +++ b/doc/manual/xoper.html @@ -0,0 +1,70 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - THE QUERY LANGUAGE</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xtypes.html">[ Previous ]</A> +<A HREF="xaggr.html">[ Next ]</A> +</font> +<HR> +<H1>9. EXTENDING SQL: OPERATORS</H1> +<HR> + POSTGRES supports left unary, right unary and binary + operators. Operators can be overloaded, or re-used + with different numbers and types of arguments. If + there is an ambiguous situation and the system cannot + determine the correct operator to use, it will return + an error and you may have to typecast the left and/or + right operands to help it understand which operator you + meant to use. + To create an operator for adding two complex numbers + can be done as follows. First we need to create a + function to add the new types. Then, we can create the + operator with the function. + +<pre> + CREATE FUNCTION complex_add(complex, complex) + RETURNS complex + AS '$PWD/obj/complex.so' + LANGUAGE 'c'; + + + CREATE OPERATOR + ( + leftarg = complex, + rightarg = complex, + procedure = complex_add, + commutator = + + ); +</pre> + + We've shown how to create a binary operator here. To + create unary operators, just omit one of leftarg (for + left unary) or rightarg (for right unary). + If we give the system enough type information, it can + automatically figure out which operators to use. + +<pre> + SELECT (a + b) AS c FROM test_complex; + + + +----------------+ + |c | + +----------------+ + |(5.2,6.05) | + +----------------+ + |(133.42,144.95) | + +----------------+ +</pre> +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xtypes.html">[ Previous ]</A> +<A HREF="xaggr.html">[ Next ]</A> +</font> +</BODY> +</HTML> + diff --git a/doc/manual/xtypes.html b/doc/manual/xtypes.html new file mode 100644 index 00000000000..55e45698424 --- /dev/null +++ b/doc/manual/xtypes.html @@ -0,0 +1,148 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - EXTENDING SQL: TYPES</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xfunc.html">[ Previous ]</A> +<A HREF="xoper.html">[ Next ]</A> +</font> +<HR> +<H1>8. EXTENDING SQL: TYPES</H1> +<HR> + As previously mentioned, there are two kinds of types + in POSTGRES: base types (defined in a programming language) + and composite types (instances). + Examples in this section up to interfacing indices can + be found in <CODE>complex.sql</CODE> and <CODE>complex.c</CODE>. Composite examples + are in <CODE>funcs.sql</CODE>. +<p> +<H2><A NAME="user-defined-types">8.1. User-Defined Types</A></H2> +<p> +<H3><A NAME="functions-needed-for-a-user-defined-type">8.1.1. Functions Needed for a User-Defined Type</A></H3> + A user-defined type must always have input and output + functions. These functions determine how the type + appears in strings (for input by the user and output to + the user) and how the type is organized in memory. The + input function takes a null-delimited character string + as its input and returns the internal (in memory) + representation of the type. The output function takes the + internal representation of the type and returns a null + delimited character string. + Suppose we want to define a complex type which represents + complex numbers. Naturally, we choose to represent a + complex in memory as the following <B>C</B> structure: + +<pre> typedef struct Complex { + double x; + double y; + } Complex; +</pre> + and a string of the form (x,y) as the external string + representation. + These functions are usually not hard to write, especially + the output function. However, there are a number of points + to remember. + + <OL> + <LI> When defining your external (string) representation, + remember that you must eventually write a + complete and robust parser for that representation + as your input function! + +<pre> Complex * + complex_in(char *str) + { + double x, y; + Complex *result; + + if (sscanf(str, " ( %lf , %lf )", &x, &y) != 2) { + elog(WARN, "complex_in: error in parsing + return NULL; + } + result = (Complex *)palloc(sizeof(Complex)); + result->x = x; + result->y = y; + return (result); + } +</pre> + + The output function can simply be: + +<pre> char * + complex_out(Complex *complex) + { + char *result; +<p> + if (complex == NULL) + return(NULL); +<p> + result = (char *) palloc(60); + sprintf(result, "(%g,%g)", complex->x, complex->y); + return(result); + } +</pre> + <LI> You should try to make the input and output + functions inverses of each other. If you do + not, you will have severe problems when you need + to dump your data into a file and then read it + back in (say, into someone else's database on + another computer). This is a particularly common + problem when floating-point numbers are + involved. + </OL> + To define the <B>complex</B> type, we need to create the two + user-defined functions complex_in and complex_out + before creating the type: + +<pre> CREATE FUNCTION complex_in(opaque) + RETURNS complex + AS '/usr/local/postgres95/tutorial/obj/complex.so' + LANGUAGE 'c'; + + CREATE FUNCTION complex_out(opaque) + RETURNS opaque + AS '/usr/local/postgres95/tutorial/obj/complex.so' + LANGUAGE 'c'; + + CREATE TYPE complex ( + internallength = 16, + input = complex_in, + output = complex_out + ); +</pre> + + As discussed earlier, POSTGRES fully supports arrays of + base types. Additionally, POSTGRES supports arrays of + user-defined types as well. When you define a type, + POSTGRES automatically provides support for arrays of + that type. For historical reasons, the array type has + the same name as the user-defined type with the + underscore character _ prepended. + Composite types do not need any function defined on + them, since the system already understands what they + look like inside. +<p> +<H3><A NAME="large-objects">8.1.2. Large Objects</A></H3> + The types discussed to this point are all "small" + objects -- that is, they are smaller than 8KB<A HREF="#7"><font size=-1>[7]</font></A> in size. + If you require a larger type for something like a document + retrieval system or for storing bitmaps, you will + need to use the POSTGRES large object interface. +<p> +<HR> +<A NAME="8"><B>[7]</B></A> 8 * 1024 == 8192 bytes. In fact, the type must be considerably smaller than 8192 bytes, since the POSTGRES tuple +and page overhead must also fit into this 8KB limitation. +The actual value that fits depends on the machine architecture. +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="xfunc.html">[ Previous ]</A> +<A HREF="xoper.html">[ Next ]</A> +</font> +</BODY> +</HTML> + |
