01/23/93 INTRODUCTION(POSTGRES) NAME SECTION 1 - INTRODUCTION OVERVIEW This document is the reference manual for the POSTGRES database manage- ment system under development at the University of California at Berke- ley. The POSTGRES project, led by Professor Michael Stonebraker, is sponsored by the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. POSTGRES is distributed in source code format and is the property of the Regents of the University of California. However, the University will grant unlimited commercialization rights for any derived work on the condition that it obtain an educational license to the derived work. For further information, consult the Berkeley Campus Software Office, 295 Evans Hall, University of California, Berkeley, CA 94720. Note that there is no organization who can help you with any bugs you may encounter or with any other problems. In other words, this is unsup- ported software. POSTGRES DISTRIBUTION This reference describes Version 4.1 of POSTGRES. The POSTGRES software is about 200,000 lines of C code. Information on obtaining the source code is available from: Claire Mosher Computer Science Division 521 Evans Hall University of California Berkeley, CA 94720 (510) 642-4662 Version 4.1 has been tuned modestly. Hence, on the Wisconsin benchmark, one should expect performance about twice that of the public domain, University of California version of INGRES, a relational prototype from the late 1970s. As distributed, POSTGRES runs on Digital Equipment Corporation computers based on MIPS R2000 and R3000 processors (under Ultrix 4.2 and 4.3) and Sun Microsystems computers based on SPARC processors (under SunOS 4.1). Work is underway to support Digital Equipment Corporation computers based on Alpha AXP (DECchip 21064) processors (under OSF/1) and Hewlett-Packard computers based on PA-RISC processors (under HP-UX). POSTGRES DOCUMENTATION This reference manual describes the functionality of Version 4.1 and contains notations where appropriate to indicate which features are not implemented in Version 4.1. Application developers should note that this reference contains only the specification for the low-level call- oriented application program interface, LIBPQ. 1 INTRODUCTION(POSTGRES) 01/23/93 The remainder of this reference manual is structured as follows. In Section 2 (UNIX), we discuss the POSTGRES capabilities that are avail- able directly from the operating system. Section 3 (BUILT-INS) describes POSTGRES internal data types, functions, and operators. Sec- tion 4 (COMMANDS) then describes POSTQUEL, the language by which a user interacts with a POSTGRES database. Then, Section 5 (LIBPQ) describes a library of low level routines through which a user can formulate POST- QUEL queries from a C program and get appropriate return information back to his program. Next, Section 6 (FAST PATH) continues with a description of a method by which applications may execute functions in POSTGRES with very high performance. Section 7 (LARGE OBJECTS) describes the internal POSTGRES interface for accessing large objects. The reference concludes with Section 8 (FILES), a collection of file format descriptions for files used by POSTGRES. ACKNOWLEDGEMENTS POSTGRES has been constructed by a team of undergraduate, graduate, and staff programmers. The contributors (in alphabetical order) consisted of: Jeff Anton, Paul Aoki, James Bell, Jennifer Caetta, Philip Chang, Jolly Chen, Ron Choi, Bob Devine, Matt Dillon, Zelaine Fong, Adam Glass, Jeffrey Goh, Steven Grady, Serge Granik, Marti Hearst, Joey Hellerstein, Michael Hirohama, Chin-heng Hong, Wei Hong, Anant Jhingran, Greg Kem- nitz, Case Larsen, Jeff Meredith, Michael Olson, Nels Olson, Lay-Peng Ong, Carol Paxson, Avi Pfeffer, Spyros Potamianos, Sunita Sarawagi, David Muir Sharnoff, Cimarron Taylor, Yongdong Wang, Kristin Wright and Andrew Yu. Marc Teitelbaum served as chief programmer for Version 4.1 and was responsible for overall coordination of the project. This reference was collectively written by the above implementation team, assisted by Michael Stonebraker, Chandra Ghosh and Claire Mosher. FOOTNOTES UNIX is a trademark of AT&T . 2 01/23/93 INTRODUCTION(UNIX) NAME SECTION 2 - UNIX COMMANDS (UNIX) OVERVIEW This section contains information on the interaction between POSTGRES and the operating system. In particular, the pages of this section describe the POSTGRES support programs that are executable as UNIX com- mands. TERMINOLOGY In the following documentation, the term _s_i_t_e may be interpreted as the host machine on which POSTGRES is installed. However, since it is pos- sible to install more than one set of POSTGRES databases on a single host, this term more precisely denotes any particular set of installed POSTGRES binaries and databases. The _P_O_S_T_G_R_E_S _s_u_p_e_r-_u_s_e_r is the user named "postgres" (usually) who owns the POSTGRES binaries and database files. As the database super-user, all protection mechanisms may be bypassed and any data accessed arbi- trarily. In addition, the POSTGRES super-user is allowed to execute some support programs which are generally not available to all users. Note that the POSTGRES super-user is _n_o_t the same as the UNIX super- user, _r_o_o_t, and should have a non-zero userid. The _d_a_t_a_b_a_s_e _b_a_s_e _a_d_m_i_n_i_s_t_r_a_t_o_r or DBA, is the person who is responsible for installing POSTGRES to enforce a security policy for a site. The DBA will add new users by the method described below, change the status of user-defined functions from untrusted to trusted as explained in _d_e_f_i_n_e _f_u_n_c_t_i_o_n(commands), and maintain a set of template databases for use by _c_r_e_a_t_e_d_b(unix). The _p_o_s_t_m_a_s_t_e_r is the process that acts as a clearing-house for requests to the POSTGRES system. Frontend applications connect to the _p_o_s_t_m_a_s_- _t_e_r, which keeps tracks of any system errors and communication between the backend processes. The _p_o_s_t_m_a_s_t_e_r can take several command-line arguments to tune its behavior, but supplying arguments is necessary only if you intend to run multiple sites or a non-default site. See _p_o_s_t_m_a_s_t_e_r(unix) for details. The _P_O_S_T_G_R_E_S _b_a_c_k_e_n_d (.../bin/postgres) may be executed directly from the user shell by the POSTGRES super-user (with the database name as an argument). However, doing this bypasses the shared buffer pool and lock table associated with a postmaster/site, so this is not recommended in a multiuser site. NOTATION ".../" at the front of a file name is used to represent the path to the POSTGRES super-user's home directory. Anything in brackets ("[" and "]") is optional. Anything in braces ("{" and "}") can be repeated 0 or more times. Parentheses ("(" and ")" ) are used to group boolean expressions. "|" is the boolean operator OR . 3 INTRODUCTION(UNIX) 01/23/93 USING POSTGRES FROM UNIX All POSTGRES commands that are executed directly from a UNIX shell are found in the directory ".../bin". Including this directory in your search path will make executing the commands easier. A collection of system catalogs exist at each site. These include a class ("pg_user") that contains an instance for each valid POSTGRES user. The instance specifies a set of POSTGRES privileges, such as the ability to act as POSTGRES super-user, the ability to create/destroy databases, and the ability to update the system catalogs. A UNIX user cannot do anything with POSTGRES until an appropriate instance is installed in this class. Further information on the system catalogs is available by running queries on the appropriate classes. USER AUTHENTICATION _A_u_t_h_e_n_t_i_c_a_t_i_o_n is the process by which the backend server and _p_o_s_t_m_a_s_t_e_r ensure that the user requesting access to data is in fact who he/she claims to be. All users who invoke POSTGRES are checked against the contents of the "pg_user" class to ensure that they are authorized to do so. However, verification of the user's actual identity is performed in a variety of ways. From the user shell A backend server started from a user shell notes the user's (real) user-id before performing a _s_e_t_u_i_d(3) to the user-id of user "postgres". The real user-id is used as the basis for access control checks. No other authentication is conducted. From the network If the POSTGRES system is built as distributed, access to the Internet TCP port of the _p_o_s_t_m_a_s_t_e_r process is completely unrestricted. That is, any user may connect to this port, spoof the _p_o_s_t_m_a_s_t_e_r, pose as an authorized user and issue any commands desired. However, since this port is configurable and not normally advertised in any public files (e.g., /_e_t_c/_s_e_r_v_i_c_e_s), some administrators may be satisfied with security-by-obscurity. If greater security is desired, POSTGRES and its clients may be modified to use a network authentication system. For example, the _p_o_s_t_m_a_s_t_e_r, _m_o_n_i_t_o_r and _l_i_b_p_q have already been configured to use either Version 4 or Version 5 of the _K_e_r_b_e_r_o_s authentication system from the Mas- sachusetts Institute of Technology. For more information on using _K_e_r_- _b_e_r_o_s with POSTGRES, see the appendix below. ACCESS CONTROL POSTGRES provides mechanisms to allow users to limit the access to their data that is provided to other users. Database superusers Database super-users (i.e., users who have "pg_user.usesuper" set) silently bypass all of the access controls described below with two 4 01/23/93 INTRODUCTION(UNIX) exceptions: manual system catalog updates are not permitted if the user does not have "pg_user.usecatupd" set, and destruction of system cata- logs (or modification of their schemas) is never allowed. Access control lists The use of access control lists to limit reading, writing and setting of rules on classes is covered in _c_h_a_n_g_e _a_c_l(commands). Class removal and schema modification Commands that destroy or modify the structure of an existing class, such as _a_d_d_a_t_t_r, _d_e_s_t_r_o_y, _r_e_n_a_m_e, and _r_e_m_o_v_e _i_n_d_e_x, only operate for the owner of the class. As mentioned above, these operations are never per- mitted on system catalogs. FUNCTIONS AND RULES Functions and rules allow users to insert code into the backend server that other users may execute without knowing it. Hence, both mechanisms permit users to trojan horse others with relative impunity. The only real protection is tight control over who can define functions (e.g., write to relations with POSTQUEL fields) and rules. Audit trails and alerters on "pg_class", "pg_user" and "pg_group" are also recommended. Functions Functions written in any language except POSTQUEL with "pg_proc.proistrusted" set run inside the backend server process with the permissions of the user "postgres" (the backend server runs with its real and effective user-id set to "postgres"). It is possible for users to change the server's internal data structures from inside of trusted functions. Hence, among many other things, such functions can circum- vent any system access controls. This is an inherent problem with trusted functions. Non-POSTQUEL functions that do not have "pg_proc.proistrusted" set run in a separate _u_n_t_r_u_s_t_e_d-_f_u_n_c_t_i_o_n _p_r_o_c_e_s_s spawned by the backend server. If correctly installed, this process runs with real and effective user- id set to "nobody" (or some other user with strictly limited permis- sions). It should be noted, however, that the primary purpose of untrusted functions is actually to simplify debugging of user-defined functions (since buggy functions will only crash or corrupt the untrusted-function process instead of the server process). The current RPC protocol only works in one direction, so any function that make function-manager calls (e.g., access method calls) or performs other database file operations must be trusted. Since untrusted functions are a new feature in Version 4.1, the define function command still defaults to making new functions trusted. This is a massive security hole that will be removed in a later release, once the (mis)features and interface of untrusted functions have stabilized. (An additional access control will be added for defining functions, analogous to the access control on defining rules.) 5 INTRODUCTION(UNIX) 01/23/93 Like other functions that perform database file operations, POSTQUEL functions must run in the same address space as the backend server. The "pg_proc.proistrusted" field has no effect for POSTQUEL functions, which always run with the permissions of the user who invoked the backend server. (Otherwise, users could circumvent access controls - the "nobody" user may well be allowed greater access to a particular object than a given user.) Rules Like POSTQUEL functions, rules always run with the idendity and permis- sions of the user who invoked the backend server. SEE ALSO postmaster(unix), addattr(commands), append(commands), change acl(commands), copy(commands), define rule(commands), delete(commands), destroy(commands), remove index(commands), remove rule(commands), rename(commands), replace(commands), retrieve(commands), kerberos(1), kinit(1), kerberos(3) CAVEATS There are no plans to explicitly support encrypted data inside of POSTGRES (though there is nothing to prevent users from encrypting data within user-defined functions). There are no plans to explicitly sup- port encrypted network connections, either, pending a total rewrite of the frontend/backend protocol. User names, group names and associated system identifiers (e.g., the contents of "pg_user.usesysid") are assumed to be unique throughout a database. Unpredictable results may occur if they are not. User system identifiers are currently UNIX user-ids. This may change at some time in the future. APPENDIX: USING KERBEROS Availability The _K_e_r_b_e_r_o_s authentication system is not distributed with POSTGRES, nor is it available from the University of California at Berkeley. Versions of _K_e_r_b_e_r_o_s are typically available as optional software from operating system vendors. In addition, a source code distribution may be obtained through MIT Project Athena by anonymous FTP from ATHENA-DIST.MIT.EDU (18.71.0.38). (You may wish to obtain the MIT version even if your ven- dor provides a version, since some vendor ports have been deliberately crippled or rendered non-interoperable with the MIT version.) Users located outside the United States of America and Canada are warned that distribution of the actual encryption code in _K_e_r_b_e_r_o_s is restricted by U. S. government export regulations. Any additional inquiries should be directed to your vendor or MIT Pro- ject Athena ("info-kerberos@ATHENA.MIT.EDU"). Note that FAQLs (Frequently-Asked Questions Lists) are periodically posted to the 6 01/23/93 INTRODUCTION(UNIX) _K_e_r_b_e_r_o_s mailing list, "kerberos@ATHENA.MIT.EDU" (send mail to "kerberos-request@ATHENA.MIT.EDU" to subscribe), and USENET news group, "comp.protocols.kerberos". Installation Installation of _K_e_r_b_e_r_o_s itself is covered in detail in the _K_e_r_b_e_r_o_s _I_n_s_t_a_l_l_a_t_i_o_n _N_o_t_e_s. Make sure that the server key file (the _s_r_v_t_a_b or _k_e_y_t_a_b) is somehow readable by user "postgres". POSTGRES and its clients can be compiled to use either Version 4 or Ver- sion 5 of the MIT _K_e_r_b_e_r_o_s protocols by setting the KRBVERS variable in the file ".../src/Makefile.global" to the appropriate value. You can also change the location where POSTGRES expects to find the associated libraries, header files and its own server key file. After compilation is complete, POSTGRES must be registered as a _K_e_r_b_e_r_o_s service. See the _K_e_r_b_e_r_o_s _O_p_e_r_a_t_i_o_n_s _N_o_t_e_s and related manual pages for more details on registering services. Operation After initial installation, POSTGRES should operate in all ways as a normal _K_e_r_b_e_r_o_s service. For details on the use of authentication, see the manual pages for _p_o_s_t_m_a_s_t_e_r(unix) and _m_o_n_i_t_o_r(unix). In the _K_e_r_b_e_r_o_s Version 5 hooks, the following assumptions are made about user and service naming: (1) user principal names (anames) are assumed to contain the actual UNIX/POSTGRES user name in the first com- ponent; (2) the POSTGRES service is assumed to be have two components, the service name and a hostname, canonicalized as in Version 4 (i.e., all domain suffixes removed). user example: frew@S2K.ORG user example: aoki/HOST=miyu.S2K.Berkeley.EDU@S2K.ORG host example: postgres_dbms/ucbvax@S2K.ORG Support for Version 4 will disappear sometime after the production release of Version 5 by MIT. 7 CREATEDB(UNIX) 01/23/93 NAME createdb - create a database SYNOPSIS createdb [-a system] [-h host] [-p port] [dbname] DESCRIPTION _C_r_e_a_t_e_d_b creates a new database. The person who executes this command becomes the database administrator, or DBA, for this database and is the only person, other than the POSTGRES super-user, who can destroy it. _C_r_e_a_t_e_d_b is a shell script that invokes the POSTGRES _m_o_n_i_t_o_r. Hence, a _p_o_s_t_m_a_s_t_e_r process must be running on the database server host before _c_r_e_a_t_e_d_b is executed. In addition, the PGOPTION and PGREALM environment variables will be passed on to _m_o_n_i_t_o_r and processed as described in _m_o_n_i_t_o_r(unix). The optional argument _d_b_n_a_m_e specifies the name of the database to be created. The name must be unique among all POSTGRES databases. _D_b_n_a_m_e defaults to the value of the USER environment variable. _C_r_e_a_t_e_d_b understands the following command-line options: -a system Specifies an authentication system _s_y_s_t_e_m (see _i_n_t_r_o_d_u_c_t_i_o_n(unix)) to use in connecting to the _p_o_s_t_m_a_s_t_e_r process. The default is site-specific. -h host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -p port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listen- ing for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). EXAMPLES # create the demo database createdb demo # create the demo database using the postmaster on host eden, # port 1234, using the Kerberos authentication system. createdb -a kerberos -p 1234 -h eden demo FILES $PGDATA/base/_d_b_n_a_m_e The location of the files corresponding to the database _d_b_n_a_m_e. 8 01/23/93 CREATEDB(UNIX) SEE ALSO createdb(commands), destroydb(unix), initdb(unix), monitor(unix), postmaster(unix). DIAGNOSTICS Error: Failed to connect to backend (host=_x_x_x, port=_x_x_x) _C_r_e_a_t_e_d_b could not attach to the _p_o_s_t_m_a_s_t_e_r process on the speci- fied host and port. If you see this message, ensure that the _p_o_s_t_- _m_a_s_t_e_r is running on the proper host and that you have specified the proper port. If your site uses an authentication system, ensure that you have obtained the required authentication creden- tials. user username is not You do not have a valid entry in the relation "pg_user" and cannot do anything with POSTGRES at all; contact your POSTGRES site administrator. user username is not You do not have permission to create new databases; contact your POSTGRES site administrator. _d_b_n_a_m_e already exists The database already exists. database creation failed on _d_b_n_a_m_e An internal error occurred in _m_o_n_i_t_o_r or the backend server. Ensure that your POSTGRES site administrator has properly installed POSTGRES and initialized the site with _i_n_i_t_d_b. 9 CREATEUSER(UNIX) 01/23/93 NAME createuser - create a POSTGRES user SYNOPSIS createuser [-a system] [-h host] [-p port] [username] DESCRIPTION _C_r_e_a_t_e_u_s_e_r creates a new POSTGRES user. Only users with "usesuper" set in the "pg_user" class can create new POSTGRES users. As shipped, the user "postgres" can create users. _C_r_e_a_t_e_u_s_e_r is a shell script that invokes _m_o_n_i_t_o_r. Hence, a _p_o_s_t_m_a_s_t_e_r process must be running on the database server host before _c_r_e_a_t_e_u_s_e_r is executed. In addition, the PGOPTION and PGREALM environment variables will be passed on to _m_o_n_i_t_o_r and processed as described in _m_o_n_i_t_o_r(unix). The optional argument _u_s_e_r_n_a_m_e specifies the name of the POSTGRES user to be created. (The invoker will be prompted for a name if none is specified on the command line.) This name must be unique among all POSTGRES users. _C_r_e_a_t_e_u_s_e_r understands the following command-line options: -a system Specifies an authentication system _s_y_s_t_e_m (see _i_n_t_r_o_d_u_c_t_i_o_n(unix)) to use in connecting to the _p_o_s_t_m_a_s_t_e_r process. The default is site-specific. -h host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -p port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listen- ing for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). INTERACTIVE QUESTIONS Once invoked with the above options, _c_r_e_a_t_e_u_s_e_r will ask a series of questions. The new users's login name (if not given on the command line) and user-id must be specified. (Note that the POSTGRES user-id must be the same as the user's UNIX user-id.) In addition, you must describe the security capabilities of the new user. Specifically, you will be asked whether the new user should be able to act as POSTGRES super-user, create new databases and update the system catalogs manu- ally. SEE ALSO destroyuser(unix), monitor(unix), postmaster(unix). 10 01/23/93 CREATEUSER(UNIX) DIAGNOSTICS Error: Failed to connect to backend (host=_x_x_x, port=_x_x_x) _C_r_e_a_t_e_u_s_e_r could not attach to the _p_o_s_t_m_a_s_t_e_r process on the speci- fied host and port. If you see this message, ensure that the _p_o_s_t_- _m_a_s_t_e_r is running on the proper host and that you have specified the proper port. If your site uses an authentication system, ensure that you have obtained the required authentication creden- tials. user username is not You do not have a valid entry in the relation "pg_user" and cannot do anything with POSTGRES at all; contact your POSTGRES site administrator. _u_s_e_r_n_a_m_e cannot create users. You do not have permission to create new users; contact your POSTGRES site administrator. user username already exists The user to be added already has an entry in the "pg_user" class. database access failed An internal error occurred in _m_o_n_i_t_o_r or the backend server. Ensure that your POSTGRES site administrator has properly installed POSTGRES and initialized the site with _i_n_i_t_d_b. BUGS POSTGRES user-id's and user names should not have anything to do with the constraints of UNIX. 11 DESTROYDB(UNIX) 01/23/93 NAME destroydb - destroy an existing database SYNOPSIS destroydb [-a system] [-h host] [-p port] [dbname] DESCRIPTION _D_e_s_t_r_o_y_d_b destroys an existing database. To execute this command, the user must be the database administrator, or DBA, for this database. The program runs silently; no confirmation message will be displayed. After the database is destroyed, a UNIX shell prompt will reappear. _D_e_s_t_r_o_y_d_b is a shell script that invokes _m_o_n_i_t_o_r. Hence, a _p_o_s_t_m_a_s_t_e_r process must be running on the database server host before _d_e_s_t_r_o_y_d_b is executed. In addition, the PGOPTION and PGREALM environment variables will be passed on to _m_o_n_i_t_o_r and processed as described in _m_o_n_i_t_o_r(unix). The optional argument _d_b_n_a_m_e specifies the name of the database to be destroyed. All references to the database are removed, including the directory containing this database and its associated files. _D_b_n_a_m_e defaults to the value of the USER environment variable. _D_e_s_t_r_o_y_d_b understands the following command-line options: -a system Specifies an authentication system _s_y_s_t_e_m (see _i_n_t_r_o_d_u_c_t_i_o_n(unix)) to use in connecting to the _p_o_s_t_m_a_s_t_e_r process. The default is site-specific. -h host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -p port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listen- ing for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). EXAMPLES # destroy the demo database destroydb demo # destroy the demo database using the postmaster on host eden, # port 1234, using the Kerberos authentication system. destroydb -a kerberos -p 1234 -h eden demo 12 01/23/93 DESTROYDB(UNIX) FILES $PGDATA/base/_d_b_n_a_m_e The location of the files corresponding to the database _d_b_n_a_m_e. SEE ALSO destroydb(commands), createdb(unix), initdb(unix), monitor(unix). postmaster(unix). DIAGNOSTICS Error: Failed to connect to backend (host=_x_x_x, port=_x_x_x) _D_e_s_t_r_o_y_d_b could not attach to the _p_o_s_t_m_a_s_t_e_r process on the speci- fied host and port. If you see this message, ensure that the _p_o_s_t_- _m_a_s_t_e_r is running on the proper host and that you have specified the proper port. If your site uses an authentication system, ensure that you have obtained the required authentication creden- tials. user username is not You do not have a valid entry in the relation "pg_user" and cannot do anything with POSTGRES at all; contact your POSTGRES site administrator. user username is not You do not have permission to destroy databases; contact your POSTGRES site administrator. database dbname does notexist" The database to be removed does not have an entry in the "pg_database" class. database dbname is not You are not DBA for the specified database. database destroy failed on _d_b_n_a_m_e An internal error occurred in _m_o_n_i_t_o_r or the backend server. Con- tact your POSTGRES site administrator to ensure that ensure that the files and database entries associated with the database are completely removed. 13 DESTROYUSER(UNIX) 01/23/93 NAME destroyuser - destroy a POSTGRES user and associated databases SYNOPSIS destroyuser [-a system] [-h host] [-p port] [username] DESCRIPTION _D_e_s_t_r_o_y_u_s_e_r destroys an existing POSTGRES user and the databases for which that user is database administrator. Only users with "usesuper" set in the "pg_user" class can destroy new POSTGRES users. As shipped, the user "postgres" can destroy users. _D_e_s_t_r_o_y_u_s_e_r is a shell script that invokes _m_o_n_i_t_o_r. Hence, a _p_o_s_t_m_a_s_t_e_r process must be running on the database server host before _d_e_s_t_r_o_y_u_s_e_r is executed. In addition, the PGOPTION and PGREALM environment vari- ables will be passed on to _m_o_n_i_t_o_r and processed as described in _m_o_n_i_t_o_r(unix). The optional argument _u_s_e_r_n_a_m_e specifies the name of the POSTGRES user to be destroyed. (The invoker will be prompted for a name if none is specified on the command line.) _D_e_s_t_r_o_y_u_s_e_r understands the following command-line options: -a system Specifies an authentication system _s_y_s_t_e_m (see _i_n_t_r_o_d_u_c_t_i_o_n(unix)) to use in connecting to the _p_o_s_t_m_a_s_t_e_r process. The default is site-specific. -h host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -p port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listen- ing for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). INTERACTIVE QUESTIONS Once invoked with the above options, _d_e_s_t_r_o_y_u_s_e_r will warn you about the databases that will be destroyed in the process and permit you to abort the removal of the user if desired. SEE ALSO createuser(unix), monitor(unix), postmaster(unix). DIAGNOSTICS Error: Failed to connect to backend (host=_x_x_x, port=_x_x_x) _D_e_s_t_r_o_y_u_s_e_r could not attach to the _p_o_s_t_m_a_s_t_e_r process on the 14 01/23/93 DESTROYUSER(UNIX) specified host and port. If you see this message, ensure that the _p_o_s_t_m_a_s_t_e_r is running on the proper host and that you have speci- fied the proper port. If your site uses an authentication system, ensure that you have obtained the required authentication creden- tials. user username is not You do not have a valid entry in the relation "pg_user" and cannot do anything with POSTGRES at all; contact your POSTGRES site administrator. _u_s_e_r_n_a_m_e cannot delete users. You do not have permission to delete users; contact your POSTGRES site administrator. user username does not The user to be removed does not have an entry in the "pg_user" class. database access failed destroydb on _d_b_n_a_m_e failed - exiting delete of user _u_s_e_r_n_a_m_e was UNSUCCESSFUL An internal error occurred in _m_o_n_i_t_o_r or the backend server. Con- tact your POSTGRES site administrator to ensure that the files and database entries associated with the user and his/her associated databases are completely removed. 15 ICOPY(UNIX) 03/11/93 NAME icopy - copy files between Unix and Inversion file systems SYNOPSIS icopy _d_i_r_e_c_t_i_o_n -d dbname -s smgr [-R] [-a] [-h host] [-p portnum] [-v] _s_r_c_f_i_l_e _d_e_s_t_f_i_l_e DESCRIPTION Icopy copies files between the Inversion file system and the UNIX file system. This program is a _l_i_b_p_q client program, and the Inversion file system is a transaction-protected file system used by the Sequoia 2000 research project at UC Berkeley. Inversion provides the same file sys- tem services provided by the UNIX fast file system, but does not support an NFS interface at present. In order to make it easier to use Inver- sion, a suite of utility programs, including icopy, has been written to manage files. The user specifies the host and port on which POSTGRES is running, and the database and storage manager to use for file storage. The _d_i_r_e_c_t_i_o_n of the copy specifies whether files should be copied from UNIX to Inver- sion (_i_n), or from Inversion to UNIX (_o_u_t). The user also supplies two file names for the source and destination of the copy. ARGUMENTS The first five arguments listed here are required. _d_i_r_e_c_t_i_o_n The direction of the copy. If the direction is _i_n, then the file is copied from UNIX into Inversion. If the direction is _o_u_t, then the file is copied out of Inversion to UNIX. The _d_i_r_e_c_t_i_o_n argument affects the interpretation of the source and destination file names, and may make some other flags (such as -s) optional (see below). This argument must immedi- ately follow the program name. -d dbname The database to use for file storage. The user should have permission to create objects in _d_b_n_a_m_e. The database name must be supplied; there is no default. -s smgr Use _s_m_g_r as the storage manager for the file. Storage managers in POSTGRES manage physical devices, so this flag gives the user a way of controlling the device on which his file should be stored. If the direction of the copy is _i_n, then the storage manager must be specified. If the direction of the copy is _o_u_t, then the storage manager flag is optional, and is ignored if it is supplied. The list of available storage managers may be obtained by typ- ing icopy with no options; the resulting usage message includes a list of storage managers supported. 16 03/11/93 ICOPY(UNIX) _s_r_c_f_i_l_e The file from which to copy. If _d_i_r_e_c_t_i_o_n is _i_n, then this is the name of a file or directory on the UNIX file system. If _d_i_r_e_c_t_i_o_n is _o_u_t, then this is the name of a file or directory on the Inversion file system. If _s_r_c_f_i_l_e is a directory and the -R flag is supplied, then the tree rooted at _s_r_c_f_i_l_e is copied. It is an error to specify a directory to copy without supplying the -R flag. _d_e_s_t_f_i_l_e The file to which to copy. If _d_i_r_e_c_t_i_o_n is _i_n, then this is the name of an Inversion file or directory. If _d_i_r_e_c_t_i_o_n is _o_u_t, then _d_e_s_t_f_i_l_e is the name of a UNIX file or directory. If _d_e_s_t_f_i_l_e already exists and is a directory, then _s_r_c_f_i_l_e will be created in the directory _d_e_s_t_f_i_l_e. The following arguments are optional. -h host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -p port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listening for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). -R Copy a directory tree recursively. Rather than copying a sin- gle file, the tree rooted at _s_r_c_f_i_l_e is copied to a tree rooted at _d_e_s_t_f_i_l_e. -a Copy all files, including those beginning with a dot. This flag is useful only in conjunction with -R. Normally, recur- sive copies of a directory tree will not copy files or direc- tories whose names begin with a dot. -v Turn verbose mode on. Icopy will report its progress as it moves files to or from Inversion. EXAMPLES The command icopy in -h myhost -p 4321 -d mydb -s d /vmunix /inv_vmunix copies the UNIX file "/vmunix" to the Inversion file "/inv_vmunix". The Inversion file is stored in the database "mydb" by the POSTGRES backend running on machine "myhost" and listening on port number 4321. The command icopy out -h myhost -p 4321 -d mydb /inv_vmunix /vmunix.dup copies it back out again, putting the copy in the UNIX file "/vmunix.dup". 17 ICOPY(UNIX) 03/11/93 BUGS The POSTGRES file system code should support operations via NFS, so this program actually has no right to exist. 18 01/23/93 INITDB(UNIX) NAME initdb - initalize the database templates and primary directories SYNOPSIS initdb [-v ] [-d ] [-n ] DESCRIPTION _I_n_i_t_d_b sets up the initial template databases and is normally executed as part of the installation process. The template database is created under the directory specified by the the environment variable PGDATA , or to a default specified at compile-time. _I_n_i_t_d_b is a shell script that invokes the backend server directly. Hence, it must be executed by the POSTGRES super-user. _I_n_i_t_d_b understands the following command-line options: -v Produce verbose output, printing messages stating where the direc- tories are being created, etc. -d Print debugging output from the backend server. This option gen- erates a tremendous amount of information. -n Run in "noclean" mode. By default, _i_n_i_t_d_b cleans up (recursively unlinks) the data directory if any error occurs, which also removes any core files left by the backend server. This option inhibits any tidying-up. FILES $PGDATA/base The location of global (shared) classes. $PGDATA/base/template1 The location of the template database. $PGDATA/files/{global1,local1_template1}.bki Command files used to generate the global and template databases, generated and installed by the initial compilation process. SEE ALSO createdb(unix), bki(files), template(files). 19 IPCCLEAN(UNIX) 01/23/93 NAME ipcclean - clean up shared memory and semaphores from aborted backends SYNOPSIS ipcclean DESCRIPTION _I_p_c_c_l_e_a_n cleans up shared memory and semaphore space from aborted back- ends by deleting all instances owned by user "postgres". Only the DBA should execute this program as it can cause bizarre behavior (i.e., crashes) if run during multi-user execution. This program should be executed if messages such as semget: No space left on device are encoun- tered when starting up the _p_o_s_t_m_a_s_t_e_r or the backend server. BUGS If this command is executed while a _p_o_s_t_m_a_s_t_e_r is running, the shared memory and semaphores allocated by the _p_o_s_t_m_a_s_t_e_r will be deleted. This will result in a general failure of the backends servers started by that _p_o_s_t_m_a_s_t_e_r. This script is a hack, but in the many years since it was written, no one has come up with an equally effective and portable solution. Suggestions are welcome. 20 01/23/93 MONITOR(UNIX) NAME monitor - run the interactive terminal monitor SYNOPSIS monitor [-N ] [-Q ] [-T ] [-a system] [-c query] [-d path] [-h hostname] [-p port] [-q ] [-t tty_device] [dbname] DESCRIPTION The interactive terminal monitor is a simple frontend to POSTGRES. It enables one to formulate, edit and review queries before issuing them to POSTGRES. If changes must be made, a UNIX editor may be called to edit the query buffer managed by the terminal monitor. The editor used is determined by the value of the EDITOR environment variable. If EDITOR is not set, then vi is used by default. _M_o_n_i_t_o_r is a frontend application, like any other. Hence, a _p_o_s_t_m_a_s_t_e_r process must be running on the database server host before _m_o_n_i_t_o_r is executed. In addition, the correct _p_o_s_t_m_a_s_t_e_r port number must be specified as described below. The optional argument _d_b_n_a_m_e specifies the name of the database to be accessed. This database must already have been created using _c_r_e_a_t_e_d_b. _D_b_n_a_m_e defaults to the value of the USER environment variable. _M_o_n_i_t_o_r understands the following command-line options: -N Specifies that query results will be dumped to the screen without any attempt at formatting. This is useful in conjunction with the -c option in shell scripts. -Q Produces extremely unverbose output. This is useful in conjunction with the -c option in shell scripts. -T Specifies that attribute names will not be printed. This is useful in conjunction with the -c option in shell scripts. -a system Specifies an authentication system _s_y_s_t_e_m (see _i_n_t_r_o_d_u_c_t_i_o_n(unix)) to use in connecting to the _p_o_s_t_m_a_s_t_e_r process. The default is site-specific. -c query Specifies that _m_o_n_i_t_o_r is to execute one query string, _q_u_e_r_y, and then exit. This is useful for shell scripts, typically in conjunc- tion with the -N and -T options. Examples of shell scripts in the POSTGRES distribution using _m_o_n_i_t_o_r -c include _c_r_e_a_t_e_d_b, _d_e_s_t_r_o_y_d_b, _c_r_e_a_t_e_u_s_e_r, _d_e_s_t_r_o_y_u_s_e_r, and _v_a_c_u_u_m. -d path _p_a_t_h specifies the path name of the file or tty to which frontend (i.e., _m_o_n_i_t_o_r) debugging messages are to be written; the default is not to generate any debugging messages. 21 MONITOR(UNIX) 01/23/93 -h hostname Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -p port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listen- ing for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). -q Specifies that the monitor should do its work quietly. By default, it prints welcome and exit messages and the queries it sends to the backend. If this option is used, none of this happens. -t tty_device _t_t_y__d_e_v_i_c_e specifies the path name to the file or tty to which backend (i.e., _p_o_s_t_g_r_e_s) debugging messages are to be written; the default is /_d_e_v/_n_u_l_l. You may set environment variables to avoid typing some of the above options. See the ENVIRONMENT VARIABLES section below. MESSAGES AND PROMPTS The terminal monitor gives a variety of messages to keep the user informed of the status of the monitor and the query buffer. When the terminal monitor is executed, it displays the current date and time as well as a prompt. The terminal monitor displays two kinds of messages: go The query buffer is empty and the terminal monitor is ready for input. Anything typed will be added to the buffer. * This prompt is typed at the beginning of each line when the termi- nal monitor is waiting for input. TERMINAL MONITOR COMMANDS \e Enter the editor to edit the query buffer. \g Submit query buffer to POSTGRES for execution. \h Get on-line help. \i _f_i_l_e_n_a_m_e Include the file _f_i_l_e_n_a_m_e into the query buffer. \p Print the current contents of the query buffer. \q Exit from the terminal monitor. \r Reset (clear) the query buffer. 22 01/23/93 MONITOR(UNIX) \s Escape to a UNIX subshell. To return to the terminal monitor, type "exit" at the shell prompt. \t Print the current time. \w _f_i_l_e_n_a_m_e Store (write) the query buffer to an external file _f_i_l_e_n_a_m_e. \\ Produce a single backslash at the current location in query buffer. ENVIRONMENT VARIABLES You may set any of the following environment variables to avoid specify- ing command-line options: hostname: PGHOST port: PGPORT tty: PGTTY options: PGOPTION realm: PGREALM If PGOPTION is specified, then the options it contains are parsed before any command-line options. PGREALM only applies if _K_e_r_b_e_r_o_s authentication is in use. If this environment variable is set, POSTGRES will attempt authentication with servers for this realm and use separate ticket files to avoid conflicts with local ticket files. See _i_n_t_r_o_d_u_c_t_i_o_n(unix) for additional informa- tion on _K_e_r_b_e_r_o_s. See _i_n_t_r_o_d_u_c_t_i_o_n(libpq) for additional details. RETURN VALUE When executed with the -c option, _m_o_n_i_t_o_r returns 0 to the shell on suc- cessful query completion, 1 otherwise. SEE ALSO introduction(libpq), createdb(unix), createuser(unix), postgres(unix), postmaster(unix). 23 PAGEDOC(UNIX) 01/23/93 NAME pagedoc - POSTGRES data page editor SYNOPSIS pagedoc [-h|b|r ] [-d level] [-s start] [-n count] filename DESCRIPTION The _p_a_g_e_d_o_c program understands the layout of data on POSTGRES data pages and can be used to view contents of a relation _f_i_l_e_n_a_m_e if it becomes corrupted. Contents are printed to standard output, and prob- able errors are flagged with four asterisks ("****") and a description of the problem. Several levels of detail are available. Level zero prints only a single summary line per data page in the relation. The summary line includes the number of items on the page, some allocation information, and what- ever additional detail is appropriate for the relation type being exam- ined. Level one also prints a single summary line for each tuple that appears on each page. The tuple summary includes the tuple's position on the page, its length, and some allocation information. Level two (or higher) prints all of the information printed by level one, and prints tuple headers for every tuple on the page. The header information displayed depends on the type of relation being viewed; either HeapTuple or IndexTuple structure entries are possible. If the relation's contents are badly damaged, then only level zero is likely to work. Finer levels of detail assume that more page structure is correct, and so are more sensitive to corruption. _P_a_g_e_d_o_c understands the following command-line options: -h|b|r The type of the relation. Type _h is heap, _b is btree, and _r is rtree. The default is _h. -d level The detail level to use in displaying pages. -s start Start at page number _s_t_a_r_t (zero-based) rather than on page zero. -n count Display data for _c_o_u_n_t pages rather than all of them. EXAMPLES Print page and line pointer summaries and tuple headers for a btree index "pg_typeidind": pagedoc -b -d2 pg_typeidind Show the default (level zero) summary of a heap relation "pg_user": pagedoc pg_user 24 01/23/93 PAGEDOC(UNIX) SEE ALSO page(files). BUGS Finer levels of detail produce a lot of output. There's no way to skip forward to a page that shows some corruption. You can only examine contents, you can't actually fix them. 25 PCAT(UNIX) 01/23/93 NAME pcat - cat an Inversion file to stdout SYNOPSIS pcat [-D database] [-H host] [-P port] _f_i_l_e_n_a_m_e {, _f_i_l_e_n_a_m_e ... } DESCRIPTION Pcat catenates files from the Inversion file system to standard output. ARGUMENTS _f_i_l_e_n_a_m_e The name of the Inversion file to copy to standard output. If _f_i_l_e_n_a_m_e is "-" (a single dash), then standard input is copied. -D database Specifies the database to use. Defaults to the value of the environment variable DATABASE (see below). -H host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -P port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listening for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). EXAMPLES The command pcat /myfile1 - /myfile2 copies the contents of Inversion file "/myfile1", standard input, and the contents of Inversion file "/myfile2" to standard output. ENVIRONMENT If no database is given on the command line, the environment variable DATABASE is checked. If no environment variable DATABASE is present, the command exits with an error status. 26 01/23/93 PCD(UNIX) NAME pcd - change directories in an Inversion file system SYNOPSIS pcd [-D database] [-H host] [-P port] [ _p_a_t_h_n_a_m_e ] DESCRIPTION Pcd updates the current working directory environment variable. ARGUMENTS _p_a_t_h_n_a_m_e The name of the directory to change to. If no pathname is given, the path is assumed to be "/". -D database Specifies the database to use. Defaults to the value of the environment variable DATABASE (see below). -H host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -P port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listening for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). ENVIRONMENT The environment variable PFCWD is checked and updated. If no database is given on the command line, the environment variable DATABASE is checked. If no environment variable DATABASE is present, the command exits with an error status. 27 PLS(UNIX) 01/23/93 NAME pls - list contents of the Inversion file system SYNOPSIS pls < _l_s _f_l_a_g_s > DESCRIPTION Pls prints directory listings of the Inversion file system. It takes the same arguments as the UNIX _l_s command. EXAMPLES The command pls -lsga / prints a long-format listing of all the files in the root directory of Inversion, including size and ownership information. ENVIRONMENT The environment variable DATABASE is checked to determine the name of the database to use to find Inversion files. PGHOST and PGPORT must be used to specify the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running (defaults to the name of the local host) and the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listening for connections (defaults to 4321), respectively. BUGS The database name, port number, and host name to use for database accesses should be passed on the command line. Unfortunately, almost all the available option letters are already used by _l_s. 28 01/23/93 PMKDIR(UNIX) NAME pmkdir - create a new Inversion file system directory SYNOPSIS pmkdir [-D database] [-H host] [-P port] _p_a_t_h { _p_a_t_h ... } DESCRIPTION Pmkdir creates new directories on the Inversion file system. The Inver- sion file system has a hierarchical namespace with the same rules as that of the Unix filesystem: components in a pathname are separated by slashes, and an initial slash refers to the root directory of the file system. ARGUMENTS _p_a_t_h_n_a_m_e The name of the directory to create. -D database Specifies the database to use. Defaults to the value of the environment variable DATABASE (see below). -H host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -P port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listening for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). EXAMPLES The command pmkdir /a/b/c/d creates a new directory "d" as a child of "/a/b/c", which must exist. ENVIRONMENT If no database is given on the command line, the environment variable DATABASE is checked. If no environment variable DATABASE is present, the command exits with an error status. The environment variable PFCWD is used for the current directory if the pathname specified is relative. 29 PMV(UNIX) 01/23/93 NAME pmv - rename an Inversion file or directory SYNOPSIS pmv [-D database] [-H host] [-P port] _o_l_d_p_a_t_h _n_e_w_p_a_t_h DESCRIPTION Pmv changes the name of an existing file or directory on the Inversion file system. In the case that a directory is moved, the children of the original directory remain children of the directory under its new name. ARGUMENTS _o_l_d_p_a_t_h The path name of the file or directory to rename. This must be a fully-qualified path rooted at "/", and the named file or directory must exist. _n_e_w_p_a_t_h The new pathname for the file or directory. Again, this must be fully qualified, and intermediate components must exist - that is, you cannot move a file to a directory which does not yet exist. -D database Specifies the database to use. Defaults to the value of the environment variable DATABASE (see below). -H host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -P port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listening for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). EXAMPLES The command pmv c/d b/c/longname renames the Inversion file "d" in directory "c" to "b/c/longname". ENVIRONMENT If no database is given on the command line, the environment variable DATABASE is checked. If no environment variable DATABASE is present, the command exits with an error status. The environment variable PFCWD is used for the current directory if the pathname specified is relative. 30 01/23/93 POSTGRES(UNIX) NAME postgres - the POSTGRES backend server SYNOPSIS postgres [-B n_buffers] [-E ] [-P filedes] [-Q ] [-d debug_level] [-o output_file] [-s ] [dbname] DESCRIPTION The POSTGRES backend server can be executed directly from the user shell. This should be done only while debugging by the DBA, and should not be done while other POSTGRES backends are being managed by a _p_o_s_t_- _m_a_s_t_e_r on this set of databases. The optional argument _d_b_n_a_m_e specifies the name of the database to be accessed. _D_b_n_a_m_e defaults to the value of the USER environment vari- able. The _p_o_s_t_g_r_e_s server understands the following command-line options: -B n_buffers If the backend is running under the _p_o_s_t_m_a_s_t_e_r, _n__b_u_f_f_e_r_s is the number of shared-memory buffers that the _p_o_s_t_m_a_s_t_e_r has allocated for the backend server processes that it starts. If the backend is running standalone, this specifies the number of buffers to allo- cate. This value defaults to 64. -E Echo all queries. -P filedes _f_i_l_e_d_e_s specifies the file descriptor that corresponds to the socket (port) on which to communicate to the frontend process. This option is not useful for interactive use. -Q Specifies "quiet" mode. -d debug_level Turns on debugging at the numeric level _d_e_b_u_g__l_e_v_e_l -o output_file Sends all debugging and error output to _o_u_t_p_u_t__f_i_l_e. If the back- end is running under the _p_o_s_t_m_a_s_t_e_r, error messages are still sent to the frontend process as well as to _o_u_t_p_u_t__f_i_l_e, but debugging output is sent to the controlling tty of the _p_o_s_t_m_a_s_t_e_r (since only one file descriptor can be sent to an actual file). -s Print time information and other statistics at the end of each query. This is useful for benchmarking or for use in tuning the number of buffers. UNRECOMMENDED COMMAND OPTIONS There are several other options that may be specified, used mainly for debugging purposes. These are listed here only for the use of POSTGRES system developers. Use of any of these options is highly discouraged. 31 POSTGRES(UNIX) 01/23/93 Furthermore, any of these options may disappear or change at any time. -An|r|b|Q_n|X_n Turns on memory manager tracing; An prints allocations/deallocation events when they occur, Ar enables silent record-collection, Ab enables both record-collection and event-printing, AQ_n prints recorded events each _n tuples processed, and AX_n prints recorded events each _n transactions processed. This option generates a tremendous amount of output. -C Don't check whether database metadescriptions (i.e., PG_VERSION files) are consistent. -L Turns off the locking system. -N Disables use of newline as a query delimiter. -S Indicates that the transaction system can run with the assumption of stable main memory, thereby avoiding the necessary flushing of data and log pages to disk at the end of each transaction system. This is only used for performance comparisons for stable vs. non- stable storage. Do not use this in other cases, as recovery after a system crash may be impossible when this option is specified in the absence of stable main memory. -b Enables generation of bushy query plan trees (as opposed to left- deep query plans trees). These query plans are not intended for actual execution; in addition, this flag often causes POSTGRES to run out of memory. -f Forbids the use of particular scan and join methods: _s and _i dis- able sequential and index scans respectively, while _n, _m and _h dis- able nested-loop, merge and hash joins respectively. This is another feature that may not necessarily produce executable plans. -p Indicates to the backend server that it has been started by a _p_o_s_t_- _m_a_s_t_e_r and make different assumptions about buffer pool management, file descriptors, etc. -tpa[rser]|pl[anner]|e[xecutor] Print timing statistics for each query relating to each of the major system modules. This option cannot be used with -s. SEE ALSO ipcclean(unix), monitor(unix), postmaster(unix). DIAGNOSTICS Of the nigh-infinite number of error messages you may see when you exe- cute the backend server directly, the most common will probably be: semget: No space left on device If you see this message, you should run the _i_p_c_c_l_e_a_n command. After doing this, try starting _p_o_s_t_g_r_e_s again. If this still 32 01/23/93 POSTGRES(UNIX) doesn't work, you probably need to configure your kernel for shared memory and semaphores as described in the installation notes. 33 POSTMASTER(UNIX) 01/23/93 NAME postmaster - run the POSTGRES postmaster SYNOPSIS postmaster [-B n_buffers] [-D data_dir] [-a system] [-b backend_pathname] [-d [debug_level]] [-n ] [-o backend_options] [-p port] [-s ] DESCRIPTION The _p_o_s_t_m_a_s_t_e_r manages the communication between frontend and backend processes, as well as allocating the shared buffer pool and semaphores (on machines without a test-and-set instruction). The _p_o_s_t_m_a_s_t_e_r does not itself interact with the user and should be started as a background process. Only one postmaster should be run on a machine. The _p_o_s_t_m_a_s_t_e_r understands the following command-line options: -B n_buffers _n__b_u_f_f_e_r_s is the number of shared-memory buffers for the _p_o_s_t_m_a_s_t_e_r to allocate and manage for the backend server processes that it starts. This value defaults to 64. -D data_dir Specifies the directory to use as the root of the tree of database directories. This directory uses the value of the environment variable PGDATA. If PGDATA is not set, then the directory used is $POSTGRESHOME/data. If neither environment variable is set and this command-line option is not specified, the default directory that was set at compile-time is used. -a system Specifies whether or not to use the authentication system _s_y_s_t_e_m (see _i_n_t_r_o_d_u_c_t_i_o_n(unix)) for frontend applications to use in con- necting to the _p_o_s_t_m_a_s_t_e_r process. Specify _s_y_s_t_e_m to enable a sys- tem, or no_s_y_s_t_e_m to disable a system. For example, to permit users to use _K_e_r_b_e_r_o_s authentication, use -a kerberos; to deny any unau- thenticated connections, use -a nounauth . The default is site- specific. -b backend_pathname _b_a_c_k_e_n_d__p_a_t_h_n_a_m_e is the full pathname of the POSTGRES backend server executable file that the _p_o_s_t_m_a_s_t_e_r will invoke when it receives a connection from a frontend application. If this option is not used, then the _p_o_s_t_m_a_s_t_e_r tries to find this executable file in the directory in which its own executable is located (this is done by looking at the pathname under which the _p_o_s_t_m_a_s_t_e_r was invoked. If no pathname was specified, then the PATH environment variable is searched for an executable named "postgres"). -d [debug_level] The optional argument _d_e_b_u_g__l_e_v_e_l determines the amount of debug- ging output the backend servers will produce. Note that if no file is specified for backend servers to send their debugging output 34 01/23/93 POSTMASTER(UNIX) (e.g., using the -t option of _m_o_n_i_t_o_r or the -o option of _p_o_s_t_g_r_e_s) then this output will appear on the controlling tty of their parent _p_o_s_t_m_a_s_t_e_r. -n, -s The -_s and -_n options control the behavior of the _p_o_s_t_m_a_s_t_e_r when a backend dies abnormally. Neither option is intended for use in ordinary operation. The ordinary strategy for this situation is to notify all other backends that they must terminate and then reinitialize the shared memory and semaphores. This is because an errant backend could have corrupted some shared state before terminating. If the -_s option is supplied, then the _p_o_s_t_m_a_s_t_e_r will stop all other backend processes by sending the signal SIGSTOP , but will not cause them to terminate. This permits system programmers to collect core dumps from all backend processes by hand. If the -_n option is supplied, then the _p_o_s_t_m_a_s_t_e_r does not reini- tialize shared data structures. A knowledgable system programmer can then use the _s_h_m_e_m_d_o_c program to examine shared memory and semaphore state. -o backend_options The _p_o_s_t_g_r_e_s(unix) options specified in _b_a_c_k_e_n_d__o_p_t_i_o_n_s are passed to all backend server processes started by this _p_o_s_t_m_a_s_t_e_r. If the option string contains any spaces, the entire string must be quoted. -p port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is to listen for connections from frontend applications. Defaults to 4321, or the value of the PGPORT environment variable (if set). If you specify a port other than the default port then all frontend application users must specify the same port (using command-line options or PGPORT ) when starting any libpq application, including the terminal monitor. EXAMPLES # start postmaster using default values postmaster & This command will start up _p_o_s_t_m_a_s_t_e_r on the default port (4321) and will search $PATH to find an executable file called "postgresq. This is the simplest and most common way to start the _p_o_s_t_m_a_s_t_e_r. # start with specific port and executable name postmaster -p 1234 -b /usr/postgres/bin/postgres & This command will start up a postmaster communicating through the port 1234, and will attempt to use the backend located at "/usr/postgres/bin/postgres". In order to connect to this _p_o_s_t_m_a_s_t_e_r 35 POSTMASTER(UNIX) 01/23/93 using the terminal monitor, you would need to either specify -p 1234 on the _m_o_n_i_t_o_r command-line or set the environment variable PGPORT to 1234. SEE ALSO ipcclean(unix), monitor(unix), postgres(unix), shmemdoc(unix). DIAGNOSTICS semget: No space left on device If you see this message, you should run the _i_p_c_c_l_e_a_n command. After doing this, try starting the _p_o_s_t_m_a_s_t_e_r again. If this still doesn't work, you probably need to configure your kernel for shared memory and semaphores as described in the installation notes. StreamServerPort: cannot bind to port If you see this message, you should be certain that there is no other _p_o_s_t_m_a_s_t_e_r process already running. The easiest way to determine this is by using the command ps -ax | grep postmaster If you are sure that no other _p_o_s_t_m_a_s_t_e_r processes are running and you still get this error, try specifying a different port using the -p option. You may also get this error if you terminate the _p_o_s_t_- _m_a_s_t_e_r and immediately restart it using the same port; in this case, you must simply wait a few seconds until the operating system closes the port before trying again. 36 01/23/93 PPWD(UNIX) NAME ppwd - return Inversion file system working directory name SYNOPSIS ppwd DESCRIPTION Ppwd writes the absolute pathname of the current working directory to the standard output. Ppwd exits with status 0 on success, and >0 if an error occurs. ENVIRONMENT The environment variable PFCWD stores the current Inversion working directory. SEE ALSO pcd(unix), p_getwd(large_objects). 37 PRM(UNIX) 01/23/93 NAME prm - remove an Inversion file SYNOPSIS prm [-D database] [-H host] [-P port] _p_a_t_h_n_a_m_e DESCRIPTION Prm removes a file stored by the Inversion file system. Directories must be removed using the prmdir command. ARGUMENTS _p_a_t_h_n_a_m_e The fully-qualified pathname of the file to remove, rooted at "/". -D database Specifies the database to use. Defaults to the value of the environment variable DATABASE (see below). -H host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -P port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listening for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). EXAMPLES The command prm b/c/d removes file "d" from directory "b/c". ENVIRONMENT If no database is given on the command line, the environment variable DATABASE is checked. If no environment variable DATABASE is present, the command exits with an error status. The environment variable PFCWD is used for the current directory if the pathname specified is relative. BUGS It is not possible to remove files stored on write-once storage managers (e.g., the Sony optical disk jukebox at Berkeley). 38 01/23/93 PRMDIR(UNIX) NAME prmdir - remove an Inversion directory SYNOPSIS prmdir [-D database] [-H host] [-P port] _p_a_t_h_n_a_m_e DESCRIPTION Prmdir removes a directory from the Inversion file system. The direc- tory must be empty. Files in directories may be removed by using the prm command. ARGUMENTS _p_a_t_h_n_a_m_e The fully-qualified pathname of the directory to remove, rooted at "/". -D database Specifies the database to use. Defaults to the value of the environment variable DATABASE (see below). -H host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is running. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). -P port Specifies the Internet TCP port on which the _p_o_s_t_m_a_s_t_e_r is listening for connections. Defaults to 4321, or the value of the PGPORT environment variable (if set). EXAMPLES The command prmdir b/c removes directory "b/c" from the Inversion file system. ENVIRONMENT If no database is given on the command line, the environment variable DATABASE is checked. If no environment variable DATABASE is present, the command exits with an error status. The environment variable PFCWD is used for the current directory if the pathname specified is relative. 39 S2KINIT(UNIX) 01/23/93 NAME s2kutils - scripts to allow operation with a different Kerberos realm SYNOPSIS s2kinit s2klist s2kdestroy DESCRIPTION _s_2_k_i_n_i_t, _s_2_k_l_i_s_t and _s_2_k_d_e_s_t_r_o_y are wrappers around the _K_e_r_b_e_r_o_s pro- grams _k_i_n_i_t(1), _k_l_i_s_t(1) and _k_d_e_s_t_r_o_y(1) that cause them to operate in the realm indicated by the environment variable PGREALM. This includes the use of ticket files distinct from those obtained for use in the local realm. The PGREALM environment variable is also understood by the authentica- tion code invoked by LIBPQ applications. Hence, if PGREALM is set, tickets obtained using _s_2_k_i_n_i_t are used by _m_o_n_i_t_o_r and the Inversion file system utilities. If PGREALM is not set, then the programs display the usual _K_e_r_b_e_r_o_s behavior. SEE ALSO monitor(UNIX), kerberos(1), kinit(1), klist(1), kdestroy(1) BUGS These have almost nothing to do with POSTGRES. They are here as a con- venience to Sequoia 2000 researchers who do not work in the Sequoia 2000 realm except to use POSTGRES. You still have to insert the correct realm-server mapping into /_e_t_c/_k_r_b._c_o_n_f . 40 01/23/93 SHMEMDOC(UNIX) NAME shmemdoc - POSTGRES shared memory editor SYNOPSIS shmemdoc [-p port] [-B nbuffers] DESCRIPTION The shmemdoc program understands the layout of POSTGRES data in shared memory and can be used to examine these shared structures. This program is intended only for debugging POSTGRES, and should not be used in nor- mal operation. When some backend server dies abnormally, the postmaster normally reini- tializes shared memory and semaphores and forces all peers of the dead process to exit. If _p_o_s_t_m_a_s_t_e_r is started with the -n flag, then shared memory will not be reinitialized and shmemdoc can be used to examine shared state after the crash. _S_h_m_e_m_d_o_c understands the following command-line options: -B nbuffers The number of buffers used by the backend. This value is ignored in the present implementation of _s_h_m_e_m_d_o_c, but is important if you choose to change the number allocated by POSTGRES. In that case, you're out of luck for now. -p port The port on which the postmaster was listening. This value is used to compute the shared memory key used by the postmaster when shared memory was initialized. A simple command interpreter reads user commands from standard input and prints results on standard output. The available commands are: semstat Show the status of system semaphores. Status includes semaphore names and values, the process id of the last process to change each semaphore, and a count of processes sleeping on each semaphore. semset _n _v_a_l Set the value of semaphore number _n (with zero being the first semaphore named by semstat) to _v_a_l. This is really only useful for resetting system state manually after a crash, which is something you don't really want to do. bufdescs Print the contents of the shared buffer descriptor table. bufdesc _n Print the shared buffer descriptor table entry for buffer _n. buffer _n _t_y_p_e _l_e_v_e_l Print the contents of buffer number _n in the shared buffer table. 41 SHMEMDOC(UNIX) 01/23/93 The buffer is interpreted as a page from a _t_y_p_e relation, where _t_y_p_e may be _h_e_a_p, _b_t_r_e_e, or _r_t_r_e_e. The _l_e_v_e_l argument controls the amount of detail presented. Level zero prints only page headers, level one prints page headers and line pointer tables, and level two (or higher) prints headers, line pointer tables, and tuples. linp _n _w_h_i_c_h Print line pointer table entry _w_h_i_c_h of buffer _n. tuple _n _t_y_p_e _w_h_i_c_h Print tuple _w_h_i_c_h of buffer _n. The buffer is interpreted as a page from a _t_y_p_e relation, where _t_y_p_e may be _h_e_a_p, _b_t_r_e_e, or _r_t_r_e_e. setbase _p_t_r Set the logical base address of shared memory for _s_h_m_e_m_d_o_c to _p_t_r. Normally, _s_h_m_e_m_d_o_c uses the address of each structure in its own address space when interpreting commands and printing results. If _s_e_t_b_a_s_e is used, then on input and output, addresses are translated so that the shared memory segment appears to start at address _p_t_r. This is useful when a debugger is examining a core file produced by POSTGRES and you want to use the shared memory addresses that appear in the core file. The base of shared memory in POSTGRES is stored in the variable _S_h_m_e_m_B_a_s_e, which may be examined by a debugger. _P_t_r may be expressed in octal (leading zero), decimal, or hexadecimal (leading 0x). shmemstat Print shared memory layout and allocation statistics. whatis _p_t_r Identify the shared memory structure pointed at by _p_t_r. help Print a brief command summary. quit Exit _s_h_m_e_m_d_o_c. SEE ALSO ipcclean(unix). BUGS Probably doesn't work on anything but DECstations. All of the sizes, offsets, and values for shared data are hardwired into this program; it shares no code with the ordinary POSTGRES system, so changes to shared memory layout will require changes to this program, as well. 42 01/23/93 UGIDD(UNIX) NAME ugidd - uid/gid mapping daemons SYNOPSIS /etc/ugidd -d or from SunOS inetd(8): rpc udp /usr/etc/rpc.ugidd 545580417 1 DESCRIPTION _u_g_i_d_d starts a daemon that handles rpc requests to map uid/gids to string names and vice versa. It is called by a _u_n_f_s_d(8) server when the client and server do not share the same _p_a_s_s_w_d(5) file. The -d flag to run as a standalone server. Otherwise it assumes it has been started from _i_n_e_t_d(8) and communicates on file descriptor 0. When started from inetd, the daemon exits after 5 minutes of inactivity. SEE ALSO unfsd_exports(files), inetd(8). BUGS The rpc program number is legal in that it lies in the 0x20000000 to 0x3fffffff "Defined by user" range, but is completely arbitrary. 43 INTRODUCTION(BUILT-INS) 01/23/93 NAME SECTION 3 - WHAT COMES WITH POSTGRES (BUILT-INS) DESCRIPTION This section describes the data types, functions and operators available to users in POSTGRES as it is distributed. BUILT-IN AND SYSTEM TYPES This section describes both built-in and system data types. Built-in types are required for POSTGRES to run. System types are installed in every database, but are not strictly required. Built-in types are marked with asterisks in the table below. Users may add new types to POSTGRES using the _d_e_f_i_n_e _t_y_p_e command described in this manual. User-defined types are not described in this section. POSTGRES Type Meaning Required abstime absolute date and time * aclitem access control list item * bool boolean * box 2-dimensional rectangle bytea variable length array of bytes * char character * char16 array of 16 characters * cid command identifier type * filename large object filename * int2 two-byte signed integer * int28 array of 8 int2 * int4 four-byte signed integer * float4 single-precision floating-point number * float8 double-precision floating-point number * lseg 2-dimensional line segment oid object identifier type * oid8 array of 8 oid * oidchar16 oid and char16 composed * oidint4 oid and int4 composed * path variable-length array of lseg point 2-dimensional geometric point polygon 2-dimensional polygon regproc registered procedure * reltime relative date and time * smgr storage manager * text variable length array of characters * tid tuple identifier type * tinterval time interval * xid transaction identifier type * These types all have obvious formats except for the three time types, explained in the following. 44 01/23/93 INTRODUCTION(BUILT-INS) ABSOLUTE TIME Absolute time is specified using the following syntax: Month Day [ Hour : Minute : Second ] Year [ Timezone ] where Month is Jan, Feb, ..., Dec Day is 1, 2, ..., 31 Hour is 01, 02, ..., 24 Minute is 00, 01, ..., 59 Second is 00, 01, ..., 59 Year is 1970, 1971, ..., 2038 Valid dates are, therefore, Jan 1 00:00:00 1970 GMT to Jan 1 00:00:00 2038 GMT. As of Version 3.0, times are no longer read and written using Greenwich Mean Time; the input and output routines default to the local time zone. The special absolute time "now" is provided as a convenience. The spe- cial absolute time "epoch" means Jan 1 00:00:00 1970 GMT. RELATIVE TIME Relative time is specified with the following syntax: @ Quantity Unit [Direction] where Quantity is `1', `2', ... Unit is ``second'', ``minute'', ``hour'', ``day'', ``week'', ``month'' (30-days), or ``year'' (365-days), or PLURAL of these units. Direction is ``ago'' (Note: Valid relative times are less than or equal to 68 years.) In addition, the special relative time "Undefined RelTime" is provided. TIME RANGES Time ranges are specified as: [abstime, abstime] [ , abstime] [abstime, ""] ["", ""] where _a_b_s_t_i_m_e is a time in the absolute time format. Pairs of double- quotes ("") will cause the time interval to either start or end at the least or greatest time allowable, that is, either Jan 1 00:00:00 1902 or Jan 1 00:00:00 2038, respectively. OPERATORS POSTGRES provides a large number of built-in operators on system types. These operators are declared in the system catalog "pg_operator". Every entry in "pg_operator" includes the object ID of the procedure that implements the operator. Users may invoke operators using the operator name, as in 45 INTRODUCTION(BUILT-INS) 01/23/93 retrieve (emp.all) where emp.salary < 40000 Alternatively, users may call the functions that implement the operators directly. In this case, the query above would be expressed as retrieve (emp.all) where int4lt(emp.salary, 40000) The rest of this section provides a list of the built-in operators and the functions that implement them. Binary operators are listed first, followed by unary operators. BINARY OPERATORS This list was generated from the POSTGRES system catalogs with the query retrieve (argtype = t1.typname, o.oprname, t0.typname, p.proname, ltype=t1.typname, rtype=t2.typname) from p in pg_proc, t0 in pg_type, t1 in pg_type, t2 in pg_type, o in pg_operator where p.prorettype = t0.oid and RegprocToOid(o.oprcode) = p.oid and p.pronargs = 2 and o.oprleft = t1.oid and o.oprright = t2.oid The list is sorted by the built-in type name of the first operand. The _f_u_n_c_t_i_o_n _p_r_o_t_o_t_y_p_e column gives the return type, function name, and argument types for the procedure that implements the operator. Note that these function prototypes are cast in terms of POSTQUEL types and so are not directly usable as C function prototypes. Type Operator POSTGRES Function Prototype Operation abstime != bool abstimene(abstime, abstime) inequality + abstime timepl(abstime, reltime) addition - abstime timemi(abstime, reltime) subtraction <= bool abstimele(abstime, abstime) less or equal bool ininterval(abstime, tinterval) abstime in tinterval? < bool abstimelt(abstime, abstime) less than = bool abstimeeq(abstime, abstime) equality >= bool abstimege(abstime, abstime) greater or equal > bool abstimegt(abstime, abstime) greater than 46 01/23/93 INTRODUCTION(BUILT-INS) bool = bool booleq(bool, bool) equality != bool boolne(bool, bool) inequality box && bool box_overlap(box, box) boxes over- lap &< bool box_overleft(box, box) box A over- laps box B, but does not extend to right of box B &> bool box_overright(box, box) box A over- laps box B, but does not extend to left of box B << bool box_left(box, box) A is left of B <= bool box_le(box, box) area less or equal < bool box_lt(box, box) area less than = bool box_eq(box, box) area equal >= bool box_ge(box, box) area greater or equal >> bool box_right(box, box) A is right of B > bool box_gt(box, box) area greater than @ bool box_contained(box, box) A is con- tained in B ~= bool box_same(box, box) box equality ~ bool box_contain(box, box) A contains B char != bool charne(char, char) inequality * bool charmul(char, char) multiplication + bool charpl(char, char) addition 47 INTRODUCTION(BUILT-INS) 01/23/93 - bool charmi(char, char) subtraction / bool chardiv(char, char) division <= bool charle(char, char) less or equal < bool charlt(char, char) less than = bool chareq(char, char) equality >= bool charge(char, char) greater or equal > bool chargt(char, char) greater than char16 != bool char16ne(char16, char16) inequality !~ bool char16regexne(char16, char16) A does not match regu- lar expres- sion B (POSTGRES uses the libc regexp calls for this opera- tion) <= bool char16le(char16, char16) less or equal < bool char16lt(char16, char16) less than = bool char16eq(char16, char16) equality >= bool char16ge(char16, char16) greater or equal > bool char16gt(char16, char16) greater than ~ bool char16regexeq(char16, char16) A matches regular expression B (POSTGRES uses the libc regexp calls for this opera- tion) float4 != bool float4ne(float4, float4) inequality * float4 float4mul(float4, float4) multiplication 48 01/23/93 INTRODUCTION(BUILT-INS) + float4 float4pl(float4, float4) addition - float4 float4mi(float4, float4) subtraction / float4 float4div(float4, float4) division <= bool float4le(float4, float4) less or equal < bool float4lt(float4, float4) less than = bool float4eq(float4, float4) equality >= bool float4ge(float4, float4) greater or equal > bool float4gt(float4, float4) greater than float8 != bool float8ne(float8, float8) inequality * float8 float8mul(float8, float8) multiplication + float8 float8pl(float8, float8) addition - float8 float8mi(float8, float8) subtraction / float8 float8div(float8, float8) division <= bool float8le(float8, float8) less or equal < bool float8lt(float8, float8) less than1 = bool float8eq(float8, float8) equality >= bool float8ge(float8, float8) greater or equal > bool float8gt(float8, float8) greater than ^ float8 dpow(float8, float8) exponentiation int2 != bool int2ne(int2, int2) inequality != int4 int24ne(int2, int4) inequality % int2 int2mod(int2, int2) modulus % int4 int24mod(int2, int4) modulus * int2 int2mul(int2, int2) multiplication * int4 int24mul(int2, int4) multiplication 49 INTRODUCTION(BUILT-INS) 01/23/93 + int2 int2pl(int2, int2) addition + int4 int24pl(int2, int4) addition - int2 int2mi(int2, int2) subtraction - int4 int24mi(int2, int4) subtraction / int2 int2div(int2, int2) division / int4 int24div(int2, int4) division <= bool int2le(int2, int2) less or equal <= int4 int24le(int2, int4) less or equal < bool int2lt(int2, int2) less than < int4 int24lt(int2, int4) less than = bool int2eq(int2, int2) equality = int4 int24eq(int2, int4) equality >= bool int2ge(int2, int2) greater or equal >= int4 int24ge(int2, int4) greater or equal > bool int2gt(int2, int2) greater than > int4 int24gt(int2, int4) greater than int2 int2inc(int2) increment int4 !!= bool int4notin(int4, char16) This is the relational ``not in'' operator, and is not intended for public use. != bool int4ne(int4, int4) inequality != int4 int42ne(int4, int2) inequality % int4 int42mod(int4, int2) modulus % int4 int4mod(int4, int4) modulus 50 01/23/93 INTRODUCTION(BUILT-INS) * int4 int42mul(int4, int2) multiplication * int4 int4mul(int4, int4) multiplication + int4 int42pl(int4, int2) addition + int4 int4pl(int4, int4) addition - int4 int42mi(int4, int2) subtraction - int4 int4mi(int4, int4) subtraction / int4 int42div(int4, int2) division / int4 int4div(int4, int4) division <= bool int4le(int4, int4) less or equal <= int4 int42le(int4, int2) less or equal < bool int4lt(int4, int4) less than < int4 int42lt(int4, int2) less than = bool int4eq(int4, int4) equality = int4 int42eq(int4, int2) equality >= bool int4ge(int4, int4) greater or equal >= int4 int42ge(int4, int2) greater or equal > bool int4gt(int4, int4) greater than > int4 int42lt(int4, int2) less than int4 int4inc(int4) increment oid !!= bool oidnotin(oid, char16) This is the relational ``not in'' operator, and is not intended for public use. != bool oidne(oid, oid) inequality != bool oidne(oid, regproc) inequality 51 INTRODUCTION(BUILT-INS) 01/23/93 <= bool oidle(oid, oid) less or equal < bool oidlt(oid, oid) less than = bool oideq(oid, oid) equality = bool oideq(oid, regproc) equality >= bool oidge(oid, oid) greater or equal > bool oidgt(oid, oid) greater than oidchar16 != bool oidchar16ne(oidchar16, oidchar16) inequality < bool oidchar16lt(oidchar16, oidchar16) less than <= bool oidchar16le(oidchar16, oidchar16) less or equal = bool oidchar16eq(oidchar16, oidchar16) equality > bool oidchar16gt(oidchar16, oidchar16) greater than >= bool oidchar16ge(oidchar16, oidchar16) greater or equal oidint4 != bool oidint4ne(oidint4, oidint4) inequality < bool oidint4lt(oidint4, oidint4) less than <= bool oidint4le(oidint4, oidint4) less or equal = bool oidint4eq(oidint4, oidint4) equality > bool oidint4gt(oidint4, oidint4) greater than >= bool oidint4ge(oidint4, oidint4) greater or equal point !< bool point_left(point, point) A is left of B !> bool point_right(point, point) A is right of B !^ bool point_above(point, point) A is above B !| bool point_below(point, point) A is below B =|= bool point_eq(point, point) equality 52 01/23/93 INTRODUCTION(BUILT-INS) ---> bool on_pb(point, box) point inside box ---` bool on_ppath(point, path) point on path <---> int4 pointdist(point, point) distance between points polygon && bool poly_overlap(polygon, polygon) polygons overlap &< bool poly_overleft(polygon, polygon) A overlaps B but does not extend to right of B &> bool poly_overright(polygon, polygon) A overlaps B but does not extend to left of B << bool poly_left(polygon, polygon) A is left of B >> bool poly_right(polygon, polygon) A is right of B @ bool poly_contained(polygon, polygon) A is con- tained by B ~= bool poly_same(polygon, polygon) equality ~ bool poly_contain(polygon, polygon) A contains B reltime != bool reltimene(reltime, reltime) inequality <= bool reltimele(reltime, reltime) less or equal < bool reltimelt(reltime, reltime) less than = bool reltimeeq(reltime, reltime) equality >= bool reltimege(reltime, reltime) greater or equal > bool reltimegt(reltime, reltime) greater than text != bool textne(text, text) inequality !~ bool textregexne(text, text) A does not contain the 53 INTRODUCTION(BUILT-INS) 01/23/93 regular expression B. POSTGRES uses the libc regexp interface for this operator. <= bool text_le(text, text) less or equal < bool text_lt(text, text) less than = bool texteq(text, text) equality >= bool text_ge(text, text) greater or equal > bool text_gt(text, text) greater than ~ bool textregexeq(text, text) A contains the regular expression B. POSTGRES uses the libc regexp interface for this operator. tinterval #!= bool intervallenne(tinterval, reltime) interval length not equal to reltime. #<= bool intervallenle(tinterval, reltime) interval length less or equal reltime #< bool intervallenlt(tinterval, reltime) interval length less than reltime #= bool intervalleneq(tinterval, reltime) interval length not equal to reltime #>= bool intervallenge(tinterval, reltime) interval length greater or equal 54 01/23/93 INTRODUCTION(BUILT-INS) reltime #> bool intervallengt(tinterval, reltime) interval length greater than reltime && bool intervalov(tinterval, tinterval) intervals overlap << bool intervalct(tinterval, tinterval) A contains B = bool intervaleq(tinterval, tinterval) equality UNARY OPERATORS The tables below give right and left unary operators. Left unary opera- tors have the operator precede the operand; right unary operators have the operator follow the operand. Right Unary Operators Type Operator POSTGRES Function Prototype Operation float8 % float8 dround(float8) round to nearest integer Left Unary Operators Type Operator POSTGRES Function Prototype Operation box @@ point box_center(box) center of box float4 @ float4 float4abs(float4) absolute value float8 @ float8 float8abs(float8) absolute value % float8 dtrunc(float8) truncate to integer |/ float8 dsqrt(float8) square root ||/ float8 dcbrt(float8) cube root : float8 dexp(float8) exponential function 55 INTRODUCTION(BUILT-INS) 01/23/93 ; float8 dlog1(float8) natural log- arithm tinterval | abstime intervalstart(tinterval) start of interval SEE ALSO For examples on specifying literals of built-in types, see _p_o_s_t_q_u_e_l(commands). BUGS The lists of types, functions, and operators are accurate only for Ver- sion 4.1. The lists will be incomplete and contain extraneous entries in future versions of POSTGRES. 56 01/23/93 INTRODUCTION(COMMANDS) NAME SECTION 4 - POSTQUEL COMMANDS (COMMANDS) DESCRIPTION The following is a description of the general syntax of POSTQUEL. Indi- vidual POSTQUEL statements and commands are treated separately in the document; this section describes the syntactic classes from which the constituent parts of POSTQUEL statements are drawn. Comments A _c_o_m_m_e_n_t is an arbitrary sequence of characters bounded on the left by "/*" and on the right by "*/", e.g: /* This is a comment */ Names _N_a_m_e_s in POSTQUEL are sequences of not more than 16 alphanumeric charac- ters, starting with an alphabetic. The underscore character ("_") is considered an alphabetic. Keywords The following identifiers are reserved for use as _k_e_y_w_o_r_d_s and may not be used otherwise: abort define is quel acl delete ISNULL relation addattr demand key remove after descending leftouter rename aggregate destroy light replace all destroydb listen retrieve always do load returns and empty merge rewrite append end move rightouter archive execute never rule arch_store extend new setof arg fetch none sort as forward nonulls stdin ascending from not stdout attachas function notify store backward group NOTNULL to before heavy NULL transaction begin in on type binary index once union by indexable operator unique cfunction inherits or user change input_proc output_proc using close instance parallel vacuum cluster instead pfunction version copy intersect portal view create into postquel where createdb intotemp priority with current iportal purge 57 INTRODUCTION(COMMANDS) 01/23/93 In addition, all POSTGRES classes have several predefined attributes used by the system. For a list of these, see the section Fields, below. Constants There are six types of _c_o_n_s_t_a_n_t_s for use in POSTQUEL. They are described below. Character Constants Single _c_h_a_r_a_c_t_e_r _c_o_n_s_t_a_n_t_s may be used in POSTQUEL by surrounding them by single quotes, e.g., `n'. String Constants _S_t_r_i_n_g_s in POSTQUEL are arbitrary sequences of ASCII characters bounded by double quotes (" "). Upper case alphabetics within strings are accepted literally. Non-printing characters may be embedded within strings by prepending them with a backslash, e.g., `\n'. Also, in order to embed quotes within strings, it is necessary to prefix them with `\' . The same convention applies to `\' itself. Because of the limita- tions on instance sizes, string constants are currently limited to a length of a little less than 8192 bytes. Larger objects may be created using the POSTGRES Large Object interface. Integer Constants _I_n_t_e_g_e_r _c_o_n_s_t_a_n_t_s in POSTQUEL are collection of ASCII digits with no decimal point. Legal values range from -2147483647 to +2147483647. This will vary depending on the operating system and host machine. Floating Point Constants _F_l_o_a_t_i_n_g _p_o_i_n_t _c_o_n_s_t_a_n_t_s consist of an integer part, a decimal point, and a fraction part or scientific notation of the following format: {} .{} [e [+-] {}] Where is a digit. You must include at least one after the period and after the [+-] if you use those options. An exponent with a missing mantissa has a mantissa of 1 inserted. There may be no extra characters embedded in the string. Floating constants are taken to be double-precision quantities with a range of approximately -10**38 to +10**38 and a precision of 17 decimal digits. This will vary depending on the operating system and host machine. Constants of POSTGRES User Defined Types A constant of an _a_r_b_i_t_r_a_r_y type can be entered using the notation: "string"::type-name In this case the value inside the string is passed to the input conver- sion routine for the type called type-name. The result is a constant of the indicated type. 58 01/23/93 INTRODUCTION(COMMANDS) Array constants _A_r_r_a_y _c_o_n_s_t_a_n_t_s are arrays of any POSTGRES type, including other arrays, string constants, etc. The general format of an array constant is the following: "{}" An example of an array constant is "{{1,2,3},{4,5},{6,7,8,9}}" This constant is an array consisting of three sub-arrays of integers. Individual array elements can and should be placed between quotation marks whenever possible to avoid ambiguity problems with respect to leading white space. Elements of single-element arrays (e.g., "{"1"}") must be quoted. Fields A _f_i_e_l_d is either an attribute of a given class or one of the following: all oid tmin tmax xmin xmax cmin cmax vtype As in INGRES, _a_l_l is a shorthand for all normal attributes in a class, and may be used profitably in the target list of a retrieve statement. _O_i_d stands for the unique identifier of an instance which is added by POSTGRES to all instances automatically. Oids are not reused and are 32 bit quantities. _T_m_i_n, _t_m_a_x, _x_m_i_n, _c_m_i_n, _x_m_a_x and _c_m_a_x stand respectively for the time that the instance was inserted, the time the instance was deleted, the identity of the inserting transaction, the command identifier within the transaction, the identity of the deleting transaction and its associated deleting command. For further information on these fields consult [STON87]. Times are represented internally as instances of the "abs- time" data type. Transaction identifiers are 32 bit quantities which are assigned sequentially starting at 512. Command identifiers are 16 bit objects; hence, it is an error to have more than 65535 POSTQUEL com- mands within one transaction. Attributes An _a_t_t_r_i_b_u_t_e is a construct of the form: Instance-variable{.composite_field}.field `['number`]' 59 INTRODUCTION(COMMANDS) 01/23/93 _I_n_s_t_a_n_c_e-_v_a_r_i_a_b_l_e identifies a particular class and can be thought of as standing for the instances of that class. An instance variable is either a class name, a surrogate for a class defined by means of a _f_r_o_m clause, or the keyword new or current. New and current can only appear in the action portion of a rule, while other instance variables can be used in any POSTQUEL command. _C_o_m_p_o_s_i_t_e__f_i_e_l_d is a field of of one of the POSTGRES composite types indicated in the _i_n_f_o_r_m_a_t_i_o_n(commands) sec- tion, while successive composite fields address attributes in the class(s) to which the composite field evaluates. Lastly, _f_i_e_l_d is a normal (base type) field in the class(s) last addressed. If _f_i_e_l_d is of type array, then the optional _n_u_m_b_e_r designator indicates a specific element in the array. If no number is indicated, then all array ele- ments are returned. Operators Any built-in system, or user defined operator may be used in POSTQUEL. For the list of built-in and system operators consult built-in types(commands) and system types(commands). For a list of user defined operators consult your system administrator or run a query on the pg_operator class. Parentheses may be used for arbitrary grouping of operators. Expressions (a_expr) An _e_x_p_r_e_s_s_i_o_n is one of the following: ( a_expr ) constant attribute a_expr binary_operator a_expr a_expr right_unary_operator left_unary_operator a_expr parameter functional expressions aggregate expressions set expressions (not in Version 4.1) class expression (not in Version 4.1) We have already discussed constants and attributes. The two kinds of operator expressions indicate respectively binary and left_unary expres- sions. The following sections discuss the remaining options. Parameters A _p_a_r_a_m_e_t_e_r is used to indicate a parameter in a POSTQUEL function. Typically this is used in POSTQUEL function definition statement. The form of a parameter is: '$' number For example, consider the definition of a function, DEPT, as define function DEPT (language="postquel", returntype = dept) arg is (char16) as retrieve (dept.all) where dept.name = $1 60 01/23/93 INTRODUCTION(COMMANDS) Functional Expressions A _f_u_n_c_t_i_o_n_a_l _e_x_p_r_e_s_s_i_o_n is the name of a legal POSTQUEL function, fol- lowed by its argument list enclosed in parentheses, e.g.: fn-name (a_expr{ , a_expr}) For example, the following computes the square root of an employee salary. sqrt(emp.salary) Aggregate Expression An _a_g_g_r_e_g_a_t_e _e_x_p_r_e_s_s_i_o_n represents a simple aggregate (i.e., one that computes a single value) or an aggregate function (i.e., one that com- putes a set of values). The syntax is the following: aggregate_name `{' [unique [using] opr] a_expr [from from_list] [where qualification]`}' Here, _a_g_g_r_e_g_a_t_e__n_a_m_e must be a previously defined aggregate. The _f_r_o_m__l_i_s_t indicates the class to be aggregated over while _q_u_a_l_i_f_i_c_a_t_i_o_n gives restrictions which must be satisfied by the instances to be aggre- gated. Next, the _a__e_x_p_r gives the expression to be aggregated, while the _u_n_i_q_u_e tag indicates whether all values should be aggregated or just the unique values of _a__e_x_p_r. Two expressions, _a__e_x_p_r_1 and _a__e_x_p_r_2 are the same if _a__e_x_p_r_1 _o_p_r _a__e_x_p_r_2 evaluates to true. In the case that all instance variables used in the aggregate expression are defined in the _f_r_o_m list, a simple aggregate has been defined. For example, to sum employee salaries whose age is greater than 30, one would write: retrieve (total = sum {e.salary from e in emp where e.age > 30} ) or retrieve (total = sum {emp.salary where emp.age > 30}) In either case, POSTGRES is instructed to find the instances in the _f_r_o_m__l_i_s_t which satisfy the qualification and then compute the aggregate of the _a__e_x_p_r indicated. On the other hand, if there are variables used in the aggregate expres- sion that are not defined in the _f_r_o_m list, e.g: avg {emp.salary where emp.age = e.age} then this aggregate function has a value for each possible value taken on by "e.age". For example, the following complete query finds the 61 INTRODUCTION(COMMANDS) 01/23/93 average salary of each possible employee age over 18: retrieve (e.age, avg {emp.salary where emp.age = e.age}) from e in emp where e.age > 18 Aggregate functions are not supported in Version 4.1. In general, the following aggregates (i.e., the expression within the braces) will not work: Aggregate functions of any kind. Aggregates containing more than one range variable. Aggregates that refer to range variables that use class inheritance (e.g., "e from emp*"). Aggregate containing clauses other than _a__e_x_p_r and _w_h_e_r_e- qualification clauses. (In other words, _f_r_o_m clauses within aggre- gates are not supported.) Therefore, of the three example queries given, only the second is actu- ally supported. Set Expressions Set expressions are not supported in Version 4.1. A _s_e_t _e_x_p_r_e_s_s_i_o_n defines a collection of instances from some class and uses the following syntax: {target_list from from_list where qualification} For example, the set of all employee names over 40 is: {emp.name where emp.age > 40} In addition, it is legal to construct set expressions which have an instance variable which is defined outside the scope of the expression. For example, the following expression is the set of employees in each department: {emp.name where emp.dept = dept.dname} Set expressions can be used in class expressions which are defined below. Class Expression Class expressions are not supported in Version 4.1. A _c_l_a_s_s _e_x_p_r_e_s_s_i_o_n is an expression of the form: class_constructor binary_class_operator class_constructor unary_class_operator class_constructor 62 01/23/93 INTRODUCTION(COMMANDS) where binary_class_operator is one of the following: union union of two classes intersect intersection of two classes - difference of two classes >> left class contains right class << right class contains left class == right class equals left class and unary_class_operator can be: empty right class is empty A _c_l_a_s_s__c_o_n_s_t_r_u_c_t_o_r is either an instance variable, a class name, the value of a composite field or a set expression. An example of a query with a class expression is one to find all the departments with no employees: retrieve (dept.dname) where empty {emp.name where emp.dept = dept.dname} Target_list A _t_a_r_g_e_t _l_i_s_t is a parenthesized, comma-separated list of one or more elements, each of which must be of the form: [result_attname =] a_expr Here, result_attname is the name of the attribute to be created (or an already existing attribute name in the case of update statements.) If _r_e_s_u_l_t__a_t_t_n_a_m_e is not present, then _a__e_x_p_r must contain only one attri- bute name which is assumed to be the name of the result field. In Ver- sion 4.1 default naming is only used if _a__e_x_p_r is an attribute. Qualification A _q_u_a_l_i_f_i_c_a_t_i_o_n consists of any number of clauses connected by the logi- cal operators: not and or A clause is an _a__e_x_p_r that evaluates to a Boolean over a set of instances. From List The _f_r_o_m _l_i_s_t is a comma-separated list of _f_r_o_m _e_x_p_r_e_s_s_i_o_n_s. Each _f_r_o_m _e_x_p_r_e_s_s_i_o_n is of the form: instance_variable-1 {, instance_variable-2} in class_reference 63 INTRODUCTION(COMMANDS) 01/23/93 where _c_l_a_s_s__r_e_f_e_r_e_n_c_e is of the form class_name [time_expression] [*] The _f_r_o_m _e_x_p_r_e_s_s_i_o_n defines one or more instance variables to range over the class indicated in _c_l_a_s_s__r_e_f_e_r_e_n_c_e. Adding a _t_i_m_e__e_x_p_r_e_s_s_i_o_n will indicate that a historical class is desired. One can also request the instance variable to range over all classes that are beneath the indi- cated class in the inheritance hierarchy by postpending the designator "*". Time Expressions A _t_i_m_e _e_x_p_r_e_s_s_i_o_n is in one of two forms: [date] [date-1, date-2] The first case requires instances that are valid at the indicated time. The second case requires instances that are valid at some time within the date range specified. If no time expression is indicated, the default is "now". In each case, the date is a character string of the form [MON-FRI] "MMM DD [HH:MM:SS] YYYY" [Timezone] where MMM is the month (Jan - Dec), DD is a legal day number in the specified month, HH:MM:SS is an optional time in that day (24-hour clock), and YYYY is the year. If the time of day HH:MM:SS is not speci- fied, it defaults to midnight at the start of the specified day. As of Version 3.0, times are no longer read and written using Greenwich Mean Time; the input and output routines default to the local time zone. For example, ["Jan 1 1990"] ["Mar 3 00:00:00 1980", "Mar 3 23:59:59 1981"] are valid time specifications. SEE ALSO append(commands), delete(commands), execute(commands), replace(commands), retrieve(commands), monitor(unix). BUGS The following constructs are not available in Version 4.1: class expressions set expressions 64 01/23/93 ABORT(COMMANDS) NAME abort - abort the current transaction SYNOPSIS abort DESCRIPTION This command aborts the current transaction and causes all the updates made by the transaction to be discarded. SEE ALSO begin(commands), end(commands). 65 ADDATTR(COMMANDS) 01/23/93 NAME addattr - add attributes to a class SYNOPSIS addattr ( attname1 = type1 {, attname-i = type-i} ) to classname{*} DESCRIPTION The addattr command causes new attributes to be added to an existing class, _c_l_a_s_s_n_a_m_e. The new attributes and their types are specified in the same style and with the the same restrictions as in _c_r_e_a_t_e(commands). The new attributes will not be added to any classes which inherit attri- butes from _c_l_a_s_s_n_a_m_e, unless the "*" is present. The initial value of each added attribute for all instances is "null". For efficiency reasons, default values for added attributes are not placed in existing instances of a class. If default values are desired, a subsequent _r_e_p_l_a_c_e(commands) query should be run. You must own the class in order to change its schema. EXAMPLE /* * add the date of hire to the emp class */ addattr (hiredate = abstime) to emp SEE ALSO create(commands), replace(commands). BUGS "*" is not supported in Version 4.1. 66 01/23/93 APPEND(COMMANDS) NAME append - append tuples to a relation SYNOPSIS append classname ( att_name1 = expression1 {, att_name-i = expression-i} ) [ from from_list ] [ where qual ] DESCRIPTION Append adds instances which satisfy the qualification, _q_u_a_l, to _c_l_a_s_s_n_a_m_e. _C_l_a_s_s_n_a_m_e must be the name of an existing class. The target list specifies the values of the fields to be appended to _c_l_a_s_s_n_a_m_e. The fields may be listed in any order. Fields of the result class which do not appear in the target list are default a null value. If the expression for each field is not of the correct data type, automatic type coercion will be attempted. The keyword all can be used when it is desired to append all domains of a class to another class. You must have write or append access to a class in order to append to it, as well as read access on any class whose values are read in the target list or qualification (see _c_h_a_n_g_e _a_c_l(commands)). EXAMPLE /* * Make a new employee Jones work for Smith */ append emp (newemp.name, newemp.salary, mgr = "Smith", bdate = 1990 - newemp.age) where newemp.name = "Jones" /* * same command using the from list clause */ append emp (n.name, n.salary, mgr = "Smith", bdate = 1990 - n.age) from n in newemp where n.name = "Jones" /* * Append the newemp1 class to newemp */ append newemp (newemp1.all) SEE ALSO postquel(commands), define type(commands), retrieve(commands). 67 ATTACHAS(COMMANDS) 01/23/93 NAME attachas - reestablish communication using an exising portal SYNOPSIS attachas name DESCRIPTION This command allows application programs to use a logical name, _n_a_m_e, in interactions with POSTGRES. Suppose the user of an application program specifies a collection of rules that retrieve data and that the program fails for some reason. Then, under ordinary circumstances, all the rules would need to be reentered when the program is restored. Alterna- tively, the attachas command may be used before defining the rules the first time. Then, upon restoring the program, the attachas command will reattach the user to the active rules. BUGS Attachas is not implemented in Version 4.1. 68 01/23/93 BEGIN(COMMANDS) NAME begin - begins a transaction SYNOPSIS begin DESCRIPTION This command begins a user transaction which POSTGRES will guarantee is serializable with respect to all concurrently executing transactions. POSTGRES uses two-phase locking to perform this task. If the transac- tion is committed, POSTGRES will ensure that all updates are done or none of them are done. Transactions have the standard ACID (atomic, consistent, isolatable, and durable) property. SEE ALSO abort(commands), end(commands). 69 CHANGE ACL(COMMANDS) 01/23/93 NAME change acl - change access control list(s) SYNOPSIS change acl [group|user] [name]+{a|r|w|R} class-1 {, class-i} change acl [group|user] [name]-{a|r|w|R} class-1 {, class-i} change acl [group|user] [name]={a|r|w|R} class-1 {, class-i} DESCRIPTION Introduction An _a_c_c_e_s_s _c_o_n_t_r_o_l _l_i_s_t (ACL) specifies the access modes that are permit- ted on a given class for a set of users and groups of users. These modes are: a - append data to a class r - read data from a class w - write data (append, delete, replace) to a class R - define rules on a class Application of ACLs to users Each entry in an ACL consists of an identifier and a set of permitted access modes. The identifier may apply to a single _u_s_e_r, a _g_r_o_u_p of users, or all _o_t_h_e_r users. If a user has a personal entry in an ACL, then only the listed access modes are permitted. If a user does not have a personal entry but is a member of some group(s) listed in the ACL, then access is permitted if all of the listed groups of which the user is a member have the desired access mode. Finally, if a user does not have a personal entry and is not a member of any listed groups, then the desired access mode is checked against the "other" entry. Database superusers (i.e., users who have pg_user.usesuper set) silently bypass all access controls with one exception: manual system catalog updates are never permitted if the user does not have pg_user.usecatupd set. This is intended as a convenience (safety net) for careless superusers. Application of ACLs through time The access control system always uses the ACLs that are currently valid, i.e., time travel is not supported. This may change if/when a more gen- eral notion of time-travel is documented. CHANGING ACLS In the syntax shown above, _n_a_m_e is a user or group identifier. If the user or group keywords are left out, _n_a_m_e is assumed to be a user name. If no _n_a_m_e is listed at all, then the ACL entry applies to the "other" category. Access modes are added, deleted or explicitly set using exactly one of the +, - and = mode-change flags. The access modes themselves are specified using any number of the single-letter mode flags listed above. 70 01/23/93 CHANGE ACL(COMMANDS) Only the owner of a class (or a database superuser) may change an ACL. By default, classes start without any ACLs. Classes created using the inheritance mechanism do not inherit ACLs. EXAMPLES /* * Deny any access to "other" to classes "gcmdata" and "btdata". */ change acl = gcmdata, btdata /* * Grant "dozier" all permissions to "gcmdata" and "btdata". */ change acl user dozier=arwR gcmdata, btdata /* * Allow group "sequoia" to read and append data to "gcmdata". */ change acl group sequoia+ra gcmdata /* * Deny "frew" the ability to define rules on "gcmdata". */ change acl frew-R gcmdata SEE ALSO introduction(unix), append(commands), copy(commands), delete(commands), define rule(commands), replace(commands), retrieve(commands). CAVEATS The command syntax, patterned after _c_h_m_o_d(1), is admittedly somewhat cryptic. A facility like _u_m_a_s_k(2) will be added in the future. User authentication is only conducted if the frontend process and back- end server have been compiled with the _k_e_r_b_e_r_o_s(5) libraries. See _i_n_t_r_o_d_u_c_t_i_o_n(unix). As shipped, the system does not have any installed ACLs. An access control mode for defining trusted functions (analogous to the access control on defining rules) will be added after the (mis)features and interface of untrusted functions have stabilized. User names, group names and associated system identifiers (e.g., the contents of pg_user.usesysid) are assumed to be unique throughout a database. Unpredictable results may occur if they are not. 71 CHANGE ACL(COMMANDS) 01/23/93 User system identifiers, as mentioned in a previous section of the manual, are currently UNIX user-id's. This may change at some time in the future. It is possible for users to change the server's internal data structures from inside of trusted (fast path) C functions. Hence, among many other things, such functions can circumvent any system access controls. This is an inherent problem with trusted functions. No POSTQUEL command is provided to clean up ACLs by removing entries (as opposed to removing the associated permissions). However, the built-in ACL functions provided make most administrative tasks fairly trivial. For example, to remove all ACL references to a user "mao" who is about to be fired, use: replace pg_class (relacl = pg_class.relacl - "mao="::aclitem) Security should be implemented with a clever query modification or rule-based scheme. 72 01/23/93 CLOSE(COMMANDS) NAME close - close a portal SYNOPSIS close [ portal_name ] DESCRIPTION Close frees the resources associated with a portal, _p_o_r_t_a_l__n_a_m_e. After this portal is closed, no subsequent operations are allowed on it. A portal should be closed when it is no longer needed. If _p_o_r_t_a_l__n_a_m_e is not specified, then the blank portal is closed. EXAMPLE /* * close the portal FOO */ close FOO SEE ALSO fetch(commands), move(commands), retrieve(commands). 73 CLUSTER(COMMANDS) 01/23/93 NAME cluster - give storage clustering advice to POSTGRES SYNOPSIS cluster classname on attname [ using operator ] DESCRIPTION This command instructs POSTGRES to keep the class specified by _c_l_a_s_s_n_a_m_e approximately sorted on _a_t_t_n_a_m_e using the specified operator to deter- mine the sort order. The operator must be a binary operator and both operands must be of type _a_t_t_n_a_m_e and the operator must produce a result of type boolean. If no operator is specified, then "<" is used by default. A class can be reclustered at any time on a different _a_t_t_n_a_m_e and/or with a different _o_p_e_r_a_t_o_r. POSTGRES will try to keep the heap data structure which stores the instances of this class approximately in sorted order. If the user specifies an operator which does not define a linear ordering, this com- mand will produce unpredictable orderings. Also, if there is no index for the clustering attribute, then this com- mand will have no effect. EXAMPLE /* * cluster employees in salary order */ cluster emp on salary BUGS _C_l_u_s_t_e_r has no effect in Version 4.1. 74 01/23/93 COPY(COMMANDS) NAME copy - copy data to or from a class from or to a UNIX file. SYNOPSIS copy [binary] [nonulls] classname to|from "filename"|stdin|stdout DESCRIPTION Copy moves data between POSTGRES classes and standard UNIX files. The keyword binary changes the behavior of field formatting, as described below. _C_l_a_s_s_n_a_m_e is the name of an existing class. _F_i_l_e_n_a_m_e is the UNIX pathname of the file. In place of a filename, the keywords stdin and stdout can be used so that input to copy can be written by a LIBPQ application and output from the copy command can be read by a LIBPQ application. The binary keyword will force all data to be stored/read as binary objects rather than as ASCII text. It is somewhat faster than the normal copy command, but is not generally portable, and the files generated are somewhat larger, although this factor is highly dependent on the data itself. You must have read access on any class whose values are read by the copy command, and either write or append access to a class to which values are being appended by the copy command. FORMAT OF OUTPUT FILES ASCII COPY FORMAT When copy is used without the binary keyword, the file generated will have each instance on a line, with each attribute separated by tabs (\t). Embedded tabs will be preceeded by a backslash character (\). The attribute values themselves are strings generated by the output function associated with each attribute type. The output function for a type should not try to generate the backslash character; this will be handled by copy itself. Note that on input to copy, backslashes are considered to be special control characters, and should be doubled if you want to embed a backslash, i.e., the string "12\\19\88" will be converted by copy to "12\1988". The actual format for each instance is ... If copy is sending its output to standard output instead of a file, it will send a period (.) followed immediately by a newline, on a line by themselves, when it is done. Similarly, if copy is reading from stan- dard input, it will expect a period (.) followed by a newline, as the first two characters on a line, to denote end-of-file. However, copy will terminate (followed by the backend itself) if a true EOF is encoun- tered. NULL attributes are handled simply as null strings, that is, consecutive tabs in the input file denote a NULL attribute. 75 COPY(COMMANDS) 01/23/93 BINARY COPY FORMAT In the case of copy binary, the first four bytes in the file will be the number of instances in the file. If this number is _z_e_r_o, the copy binary command will read until end of file is encountered. Otherwise, it will stop reading when this number of instances has been read. Remaining data in the file will be ignored. The format for each instance in the file is as follows. Note that this format must be followed EXACTLY. Unsigned four-byte integer quantities are called uint32 in the below description. uint32 totallength (not including itself), uint32 number of null attributes [uint32 attribute number of first null attribute ... uint32 attribute number of nth null attribute], ALIGNMENT OF BINARY DATA On Sun-3's, 2-byte attributes are aligned on two-byte boundaries, and all larger attributes are aligned on four-byte boundaries. Character attributes are aligned on single-byte boundaries. On other machines, all attributes larger than 1 byte are aligned on four-byte boundaries. Note that variable length attributes are preceded by the attribute's length; arrays are simply contiguous streams of the array element type. SEE ALSO append(commands), create(commands), vacuum(commands), libpq. BUGS Files used as arguments to the copy command must reside on or be access- able to the the database server machine by being either on local disks or a networked file system. Copy stops operation at the first error. This should not lead to prob- lems in the event of a copy from, but the target relation will, of course, be partially modified in a copy to. The _v_a_c_u_u_m(commands) query should be used to clean up after a failed copy. Because POSTGRES operates out of a different directory than the user's working directory at the time POSTGRES is invoked, the result of copying to a file "foo" (without additional path information) may yield unex- pected results for the naive user. In this case, "foo" will wind up in $PGDATA/foo. In general, the full pathname should be used when specify- ing files to be copied. Copy has virtually no error checking, and a malformed input file will likely cause the backend to crash. Humans should avoid using copy for input whenever possible. 76 01/23/93 CREATE(COMMANDS) NAME create - create a new class SYNOPSIS create classname (attname-1 = type-1 {, attname-i = type-i}) [key (attname-1 [using operator-1] {, attname-i [using operator-i]})] [inherits ( classname-1 {, classname-i} )] [archive = archive_mode] [store = "smgr_name"] [arch_store = "smgr_name"] DESCRIPTION Create will enter a new class into the current data base. The class will be "owned" by the user issuing the command. The name of the class is _c_l_a_s_s_n_a_m_e and the attributes are as specified in the list of _a_t_t_n_a_m_es. The _ith attribute is created with the type specified by _t_y_p_e-i. The optional key clause is used to specify that a field or a collection of fields is unique. If no key clause is specified, POSTGRES will still give every instance a unique object-id (OID ). This clause allows other fields to be additional keys. The using part of the clause allows the user to specify what operator should be used for the uniqueness test. For example, integers are all unique if "=" is used for the check, but not if "<" is used instead. If no operator is specified, "=" is used by default. Any specified operator must be a binary operator returning a boolean. If there is no compatible index to allow the key clause to be rapidly checked, POSTGRES defaults to not checking rather than perform- ing an exhaustive search on each key update. The optional inherits clause specifies a collection of class names from which this class automatically inherits all fields. If any inherited field name appears more than once, POSTGRES reports an error. POSTGRES automatically allows the created class to inherit functions on classes above it in the inheritance hierarchy. Inheritance of functions is done according to the conventions of the Common Lisp Object System (CLOS). Each new class _c_l_a_s_s_n_a_m_e is automatically created as a type. Therefore, one or more instances from the class are automatically a type and can be used in _a_d_d_a_t_t_r(commands) or other create statements. See _i_n_t_r_o_d_u_c_t_i_o_n(commands) for a further discussion of this point. The optional store and arch_store keywords may be used to specify a storage manager to use for the new class. The released version of POSTGRES supports only "magnetic diskq as a storage manager name; the research system at Berkeley provides additional storage managers. Store controls the location of current data, and arch_store controls the loca- tion of historical data. Arch_store may only be specified if archive is also specified. If either store or arch_store is not declared, it defaults to "magnetic disk". 77 CREATE(COMMANDS) 01/23/93 The new class is created as a heap with no initial data. A class can have no more than 1600 domains (realistically, this is limited by the fact that tuple sizes must be less than 8192 bytes), but this limit may be configured lower at some sites. A class cannot have the same name as a system catalog class. The archive keyword specifies whether historical data is to be saved or discarded. _A_r_c_h__m_o_d_e may be one of: _n_o_n_e No historical access is supported. _l_i_g_h_t Historical access is allowed and optimized for light update activity. _h_e_a_v_y Historical access is allowed and optimized for heavy update activity. _A_r_c_h__m_o_d_e defaults to "none". Once the archive status is set, there is no way to change it. For details of the optimization, see [STON87]. EXAMPLE /* * Create class emp with attributes name, sal and bdate */ create emp (name = char16, salary = float4, bdate = abstime) /* * Create class permemp with pension information that * inherits all fields of emp */ create permemp (plan = char16) inherits (emp) /* * Create a class foo on mag disk and archive historical data */ create foo (bar = int4) archive = heavy store = "magnetic disk" SEE ALSO destroy(commands). BUGS The key clause is not implemented in Version 4.1. Optional specifications (inherits, archive, store) must be supplied in the order given above, if they are supplied at all. 78 01/23/93 CREATEDB(COMMANDS) NAME createdb - create a new database SYNOPSIS createdb dbname DESCRIPTION Createdb creates a new POSTGRES database. The creator becomes the administrator of the new database. SEE ALSO createdb(unix), destroydb(commands), destroydb(unix), initdb(unix). BUGS This command should NOT be executed interactively. The _c_r_e_a_t_e_d_b(unix) script should be used instead. 79 CREATE VERSION(COMMANDS) 01/23/93 NAME create version - construct a version class SYNOPSIS create version classname1 from classname2 [[abstime]] DESCRIPTION This command creates a version class _c_l_a_s_s_n_a_m_e_1 which is related to its parent class, _c_l_a_s_s_n_a_m_e_2. Initially, _c_l_a_s_s_n_a_m_e_1 has the same contents as _c_l_a_s_s_n_a_m_e_2. As updates to _c_l_a_s_s_n_a_m_e_1 occur, however, the content of _c_l_a_s_s_n_a_m_e_1 diverges from _c_l_a_s_s_n_a_m_e_2. On the other hand, any updates to _c_l_a_s_s_n_a_m_e_2 show transparently through to _c_l_a_s_s_n_a_m_e_1, unless the instance in question has already been updated in _c_l_a_s_s_n_a_m_e_1. If the optional _a_b_s_t_i_m_e clause is specified, then the version is con- structed relative to a snapshot of _c_l_a_s_s_n_a_m_e_2 as of the time specified. POSTGRES uses the query rewrite rule system to ensure that _c_l_a_s_s_n_a_m_e_1 is differentially encoded relative to _c_l_a_s_s_n_a_m_e_2. Moreover, _c_l_a_s_s_n_a_m_e_1 is automatically constructed to have the same indexes as _c_l_a_s_s_n_a_m_e_2. It is legal to cascade versions arbitrarily, so a tree of versions can ulti- mately result. The algorithms that control versions are explained in [ONG90]. EXAMPLE /* * create a version foobar from a snapshot of * barfoo as of January 17, 1990 */ create version foobar from barfoo [ "Jan 17 1990" ] SEE ALSO define view(commands), merge(commands), postquel(commands). BUGS Snapshots (i.e., the optional _a_b_s_t_i_m_e clause) are not implemented in Version 4.1. 80 01/23/93 DEFINE AGGREGATE(COMMANDS) NAME define aggregate - define a new aggregate SYNOPSIS define aggregate agg-name [as] ([sfunc1 = state-transition-function-1] [, sfunc2 = state-transition-function-2] [, finalfunc = final-function] [, initcond1 = initial-condition-1] [, initcond2 = initial-condition-2]) DESCRIPTION An aggregate function can use up to three functions, two _s_t_a_t_e _t_r_a_n_s_i_- _t_i_o_n functions, X1 and X2: X1( internal-state1, next-data_item ) ---> next-internal-state1 X2( internal-state2 ) ---> next-internal-state2 and a final calculation function, F: F(internal-state1, internal-state2) ---> aggregate-value These functions are required to have the following three properties: The return type of each state-transition-function and the arguments of the final-calculation-function must be the same type (_t). The return type of the final-calculation-function must be a POSTGRES base type. The first argument to state-transition-function1 must be of type _t, while the second argument must match the data type of the object being aggregated. Note that it is possible to specify aggregate functions that have vary- ing combinations of state and final functions. For example, the "count" aggregate requires sfunc2 (an incrementing function) but not sfunc1 or finalfunc, whereas the "sum" aggregate requires sfunc1 (an addition function) but not sfunc2 or finalfunc and the "average" aggregate requires both of the above state functions as well as a finalfunc (a division function) to produce its answer. In any case, at least one state function must be defined, and any sfunc2 must have a corresponding initcond2. Aggregates also require two initial conditions, one for each transition function. These are specified and stored in the database as fields of type _t_e_x_t. EXAMPLE This _a_v_g aggregate consists of two state transition functions, a addi- tion function and a incrementing function. These modify the internal state of the aggregate through a running sum and and the number of values seen so far. It accepts a new employee salary, increments the count, and adds the new salary to produce the next state. The state transition functions must be passed correct initialization values. The final calculation then divides the sum by the count to produce the final answer. 81 DEFINE AGGREGATE(COMMANDS) 01/23/93 /* * Define an aggregate for int4 average */ define aggregate avg (sfunc1 = int4add, sfunc2 = int4inc, finalfunc = int4div, initcond1 = "0", initcond2 = "0") SEE ALSO define function(commands), remove aggregate(commands). 82 01/23/93 DEFINE FUNCTION(COMMANDS) NAME define function - define a new function SYNOPSIS define function function_name ( language = {"c" | "postquel"}, returntype = type-r [ , iscachable ] [ , percall_cpu = "costly{!*}" ] [ , perbyte_cpu = "costly{!*}" ] [ , outin_ratio = percentage ] [ , byte_pct = percentage ] ) [ arg is ( type-1 { , type-n } ) ] as {"/full/path/to/objectfile" | "list-of-postquel-queries"} DESCRIPTION With this command, a POSTGRES user can register a function with POSTGRES. Subsequently, this user is treated as the owner of the func- tion. When defining a function with arguments, the input data types, _t_y_p_e-_1, _t_y_p_e-_2, ..., _t_y_p_e-_n, and the return data type, _t_y_p_e-_r must be specified, along with the language, which may be or (The _a_r_g _i_s clause may be left out if the function has no arguments.) The input types may be base or complex types. The output type may be specified as a base type, com- plex type, or _s_e_t_o_f <_t_y_p_e>. The _s_e_t_o_f modifier indicates that the func- tion will return a set of items, rather than a single item. The _a_s clause of the command is treated differently for C and POSTQUEL func- tions, as explained below. C FUNCTIONS Functions written in C can be defined to POSTGRES, which will dynami- cally load them into its address space. The loading happens either using _l_o_a_d(commands) or automatically the first time the function is necessary for execution. Repeated execution of a function will cause negligible additional overhead, as the function will remain in a main memory cache. The iscachable flag indicates to the system that the return value of the function can be associatively cached. The trusted flag specifies that the function can run inside the POSTGRES server's address space with the user-id of the POSTGRES super-user. If this flag is not specified, the function will be run in a separate pro- cess. The percall_cpu, perbyte_cpu, outin_ratio, and byte_pct flags are pro- vided for C functions to give a rough estimate of the function's running time, allowing the query optimizer to postpone applying expensive func- tions used in a query's where clause. The percall_cpu flag captures the overhead of the function's invocation (regardless of input size), while the perbyte_cpu flag captures the sensitivity of the function's running 83 DEFINE FUNCTION(COMMANDS) 01/23/93 time to the size of its inputs. The magnitude of these two parameters is determined by the number of exclamation points appearing after the word costly: specifically, each exclamation point can be thought of as another order of magnitude in cost, i.e., cost = 10 ** number-of-exclamation-points The default value for percall_cpu and perbyte_cpu is 0. Examples of reasonable cost values may be found in the system catalog "pg_proc"; most simple functions on base types have costs of 0. The _o_u_t_i_n__r_a_t_i_o is provided for functions which return variable-length types, such as _t_e_x_t or _b_y_t_e_a. It should be set to the size of the function's output as a percentage of the size of the input. For exam- ple, a function which compresses its operands by 2 should have outin_ratio = 50. The default value is 100. The byte_pct flag should be set to the percentage of the bytes of the arguments that actually need to be examined in order to compute the function. This flag is particularly useful for functions which gen- erally take a large object as an argument, but only examine a small fixed portion of the object. The default value is 100. The body of a C function following as should be the FULL PATH of the object code (.o file) for the function, bracketed by quotation marks. (POSTGRES will not compile a function automatically - it must be com- piled before it is used in a define function command.) C functions with base type arguments can be written in a straightforward fashion. The C equivalents of built-in POSTGRES types are accessible in a C file if .../src/backend/utils/builtins.h is included as a header file. This can be achieved by having #include at the top of the C source file and by compiling all C files with the following include options: -I.../src/backend -I.../src/backend/port/ -I.../src/backend/obj before any ".c" programs in the _c_c command line, e.g.: cc -I.../src/backend \ -I.../src/backend/port/ \ -I.../src/backend/obj \ -c progname.c where "..." is the path to the installed POSTGRES source tree and 84 01/23/93 DEFINE FUNCTION(COMMANDS) "" is the name of the port for which the source tree has been built. The convention for passing arguments to and from the user's C functions is to use pass-by-value for data types that are 32 bits (4 bytes) or smaller, and pass-by-reference for data types that require more than 32 bits. Complex arguments to C functions are passed into the C function as a special C type, TUPLE, defined in .../src/libpq/libpq-fe.h. Given a variable _t of this type, the C function may extract attributes from the function using the function call: GetAttributeByName(t, "fieldname", &isnull) where _i_s_n_u_l_l is a pointer to a _b_o_o_l, which the function sets to _t_r_u_e if the field is null. The result of this function should be cast appropri- ately as shown in the examples below. POSTQUEL FUNCTIONS POSTQUEL functions execute an arbitrary list of POSTQUEL queries, returning the results of the last query in the list. POSTQUEL functions in general return sets. If their returntype is not specified as a _s_e_t_o_f, then an arbitrary element of the last query's result will be returned. The expensive function parameters percall_cpu, perbyte_cpu, outin_ratio, and byte_pct are not used for POSTQUEL functions; their costs are determined dynamically by the query optimizer. The body of a POSTQUEL function following as should be a list of queries separated by whitespace characters and bracketed within quotation marks. Note that quotation marks used in the queries must be escaped, by preceding them with two backslashes (i.e. \\"). Arguments to the POSTQUEL function may be referenced in the queries using a $n syntax: $1 refers to the first argument, $2 to the second, and so on. If an argument is complex, then a "dot" notation may be used to access attributes of the argument (e.g. "$1.emp"), or to invoke func- tions via a nested-dot syntax. EXAMPLES: C Functions The following command defines a C function, overpaid, of two basetype arguments. define function overpaid (language = "c", returntype = bool) arg is (float8, int4) as "/usr/postgres/src/adt/overpaid.o" The C file "overpaid.c" might look something like: #include 85 DEFINE FUNCTION(COMMANDS) 01/23/93 bool overpaid(salary, age) float8 *salary; int4 age; { if (*salary > 200000.00) return(TRUE); if ((age < 30) && (*salary > 100000.00)) return(TRUE); return(FALSE) } The overpaid function can be used in a query, e.g: retrieve (EMP.name) where overpaid(EMP.salary, EMP.age) One can also write this as a function of a single argument of type EMP: define function overpaid_2 (language = "c", returntype = bool) arg is (EMP) as "/usr/postgres/src/adt/overpaid_2.o" The following query is now accepted: retrieve (EMP.name) where overpaid_2(EMP) In this case, in the body of the overpaid_2 function, the fields in the EMP record must be extracted. The C file "overpaid_2.c" might look something like: #include #include bool overpaid_2(t) TUPLE t; { float8 *salary; int4 age; bool salnull, agenull; salary = (float8 *)GetAttributeByName(t, "salary", &salnull); age = (int4)GetAttributeByName(t, "age", &agenull); if (!salnull && *salary > 200000.00) return(TRUE); if (!agenull && (age<30) && (*salary > 100000.00)) return(TRUE); return(FALSE) } 86 01/23/93 DEFINE FUNCTION(COMMANDS) EXAMPLES: POSTQUEL Functions To illustrate a simple POSTQUEL function, consider the following, which might be used to debit a bank account: define function TP1 (language = "postquel", returntype = int4) arg is (int4, float8) as "replace BANK (balance = BANK.balance - $2) where BANK.accountno = $1 retrieve(x = 1)" A user could execute this function to debit account 17 by $100.00 as follows: retrieve (x = TP1( 17,100.0)) The following more interesting examples take a single argument of type EMP, and retrieve multiple results: define function hobbies (language = "postquel", returntype = setof HOBBIES) arg is (EMP) as "retrieve (HOBBIES.all) where $1.name = HOBBIES.person" define function children (language = "postquel", returntype = setof KIDS) arg is (EMP) as "retrieve (KIDS.all) where $1.name = KIDS.dad or $1.name = KIDS.mom" Then the following query retrieves overpaid employees, their hobbies, and their children: retrieve (name=name(EMP), hobby=name(hobbies(EMP)), kid=name(children(EMP))) where overpaid_2(EMP) Note that attributes can be projected using function syntax (e.g. name(EMP)), as well as the traditional dot syntax (e.g. EMP.name). An equivalent expression of the previous query is: retrieve (EMP.name, hobby=EMP.hobbies.name, kid=EMP.children.name) where overpaid_2(EMP) This "nested dot" notation for functions can be used to cascade func- tions of single arguments. Note that the function after a dot must have only one argument, of the type returned by the function before the dot. POSTGRES _f_l_a_t_t_e_n_s the target list of the queries above. That is, it produces the cross-product of the hobbies and the children of the 87 DEFINE FUNCTION(COMMANDS) 01/23/93 employees. For example, given the schema: create BANK (accountno = int4, balance = float8) append BANK (accountno = 17, balance = "10000.00"::float8) create EMP (name = char16, salary = float8, dept = char16, age = int4) create HOBBIES (name = char16, person = char16) create KIDS (name = char16, dad = char16, mom = char16) append EMP (name = "joey", salary = "100000.01"::float8, dept = "toy", age = 24) append EMP (name = "jeff", salary = "100000.01"::float8, dept = "shoe", age = 23) append EMP (name = "wei", salary = "100000"::float8, dept = "tv", age = 30) append EMP (name = "mike", salary = "500000"::float8, dept = "appliances", age = 30) append HOBBIES (name = "biking", person = "jeff" ) append HOBBIES (name = "jamming", person = "joey" ) append HOBBIES (name = "basketball", person = "wei") append HOBBIES (name = "swimming", person = "mike") append HOBBIES (name = "philately", person = "mike") append KIDS (name = "matthew", dad = "mike", mom = "teresa") append KIDS (name = "calvin", dad = "mike", mom = "teresa") The query above returns name hobby kid jeff biking (null) joey jamming (null) mike swimming matthew mike philately matthew mike swimming calvin mike philately calvin Note that flattening preserves the name and hobby fields even when the "kid" field is null. SEE ALSO information(unix), load(commands), remove function(commands). NOTES The percall_cpu and perbyte_cpu flags can take integers surrounded by quotes instead of the syntax described above. This allows a finer grain of distinction between function costs, but is not encouraged since such distinctions are difficult to estimate accurately. Under Ultrix, all object files that POSTGRES is expected to load dynami- cally must be compiled under /_b_i_n/_c_c with the "-G 0" option turned on. 88 01/23/93 DEFINE FUNCTION(COMMANDS) RESTRICTIONS The name of the C function must be a legal C function name, and the name of the function in C code must be exactly the same as the name used in define function. BUGS Function names must be unique per database, except for the fact that there may be attributes of the same name as a function. In the case that a there is an ambiguity between a function on a complex type and an attribute of the complex type, the attribute will always be used. The iscachable flag does not do anything in Version 4.1. Untrusted functions cannot make any function calls using access methods or built-in functions that have not been loaded into the untrusted- function process. Untrusted functions must be explicitly designated as such in a separate query, e.g.: replace pg_proc (proistrusted = "f"::bool) where pg_proc.proname = "mynewfunction" Untrusted functions will become the default in a future release. C functions cannot return a set of values. The dynamic loader for DECstation Ultrix is very slow. 89 DEFINE INDEX(COMMANDS) 02/08/93 NAME define index - construct a secondary index SYNOPSIS define [archive] index index-name on classname using am-name ( attname-1 type_class-1 { , attname-i type_class-i } ) [where qual] DESCRIPTION This command constructs an index called _i_n_d_e_x-_n_a_m_e. If the archive key- word is absent, the _c_l_a_s_s_n_a_m_e class is indexed. When archive is present, an index is created on the archive class associated with the _c_l_a_s_s_n_a_m_e class. _A_m-_n_a_m_e is the name of the access method which is used for the index. The key fields for the index are specified as a collection of attribute names and associated _o_p_e_r_a_t_o_r _c_l_a_s_s_e_s. An operator class is used to specify the operators to be used for a particular index. For example, a btree index on four-byte integers would use the _i_n_t_4__o_p_s class; this operator class includes comparison functions for four-byte integers. If a _q_u_a_l is given, the index will be a _p_a_r_t_i_a_l _i_n_d_e_x, which will index only those instances in _c_l_a_s_s_n_a_m_e for which the predicate specified by _q_u_a_l is true. Note that the predicate may only refer to attributes of the indexed class, _c_l_a_s_s_n_a_m_e. POSTGRES may use a partial index as an access path only for queries that include a restriction that implies that the predicate is true. For example, if the index predicate is emp.age < 30 then the index can be used for a query with the restriction where emp.age < 25 but not for a query with the restriction where emp.age < 40 and so forth. Although partial indexes cannot be used to satisfy as wide a range of queries as complete indexes, they can be constructed more quickly and extended incrementally (see the extend index command). POSTGRES Version 4.1 provides btree and rtree access methods for secon- dary indices. The operator classes defined on btrees are int2_ops char_ops int4_ops char16_ops int24_ops oid_ops int42_ops text_ops float4_ops abstime_ops float8_ops 90 02/08/93 DEFINE INDEX(COMMANDS) The _i_n_t_2_4__o_p_s operator class is useful for constructing indices on int2 data, and doing comparisons against int4 data in query qualifications. Similarly, _i_n_t_4_2__o_p_s support indices on int4 data that is to be compared against int2 data in queries. The POSTGRES query optimizer will consider using btree indices in a scan whenever an indexed attribute is involved in a comparison using one of < <= = >= > The operator classes defined on rtrees are box_ops bigbox_ops poly_ops Both box classes support indices on the datatype in POSTGRES. The difference between them is that _b_i_g_b_o_x__o_p_s scales box coordinates down, to avoid floating point exceptions from doing multiplication, addition, and subtraction on very large floating-point coordinates. If the field on which your rectangles lie is about 20,000 units square or larger, you should use _b_i_g_b_o_x__o_p_s. The _p_o_l_y__o_p_s operator class supports rtree indices on data. The POSTGRES query optimizer will consider using an rtree index whenever an indexed attribute is involved in a comparison using one of << &< &> >> @ ~= && EXAMPLES /* * Create a btree index on the emp class using the age attribute. */ define index empindex on emp using btree (age int4_ops) /* * Create a btree index on employee name. */ define index empname on emp using btree (name char16_ops) /* * Create an rtree index on the bounding rectangle of cities. */ define index cityrect on city using rtree (boundbox box_ops) 91 DEFINE INDEX(COMMANDS) 02/08/93 /* * Create a partial btree index on employee salaries for * employees over age 50 */ define index empsal on emp using btree (salary int4_ops) where emp.age > 49 Note: if the partial-index predicate refers to an attribute of a discrete-valued type (such as integers), it is slightly preferable to express the predicate as, e.g., "emp.age > 49" rather than as "emp.age >= 50", because even though both indexes would, in theory, be equally usable, POSTGRES would only be able to use a partial index with the former predicate in the event of a query that had the exact restriction "emp.age > 49". BUGS Archive indices are not supported in Version 4.1. There should be an access method designer's guide. Indices may only be defined on a single key. This can be hacked around by defining composite types and using the POSTGRES support for indices on functional values of attributes. The only kind of partial index predicates POSTGRES Version 4.1 under- stands are those made up of boolean combinations of simple clauses of the form ATTR OP CONST where ATTR is a single attribute of the indexed class, and OP is an operator in a btree operator class defined on the types of ATTR and CONST. If some other form of predicate is specified, Version 4.1 will never use the resulting partial index. 92 01/23/93 DEFINE OPERATOR(COMMANDS) NAME define operator - define a new user operator SYNOPSIS define operator operator_name ( arg1 = type-1 [ , arg2 = type-2 ] , procedure = func_name [, precedence = number ] [, associativity = (left | right | none | any) ] [, commutator = com_op ] [, negator = neg_op ] [, restrict = res_proc ] [, hashes] [, join = join_proc ] [, sort = sor_op1 {, sor_op2 } ] ) DESCRIPTION This command defines a new user operator, _o_p_e_r_a_t_o_r__n_a_m_e. The user who defines an operator becomes its owner. The name of the operator, _o_p_e_r_a_t_o_r__n_a_m_e, can be composed of symbols only. Also, the _f_u_n_c__n_a_m_e procedure must have been previously defined using define function and must have one or two arguments. The types of the arguments for the operator and the type of the answer are as defined by the function. Precedence refers to the order that multiple instances of the same operator are evaluated. The next several fields are pri- marily for the use of the query optimizer. The associativity value is used to indicate how an expression containing this operator should be evaluated when precedence and explicit grouping are insufficient to produce a complete order of evaluation. Left and right indicate that expressions containing the operator are to be evaluated from left to right or from right to left, respectively. None means that it is an error for this operator to be used without explicit grouping when there is ambiguity. And any, the default, indicates that the optimizer may choose to evaluate an expression which contains this operator arbitrarily. The commutator operator is present so that POSTGRES can reverse the order of the operands if it wishes. For example, the operator area- less-than, >>>, would have a commutator operator, area-greater-than, <<<. Suppose that an operator, area-equal, ===, exists, as well as an area not equal, !==. Hence, the query optimizer could freely convert: "0,0,1,1"::box >>> MYBOXES.description to MYBOXES.description <<< "0,0,1,1"::box This allows the execution code to always use the latter representation 93 DEFINE OPERATOR(COMMANDS) 01/23/93 and simplifies the query optimizer somewhat. The negator operator allows the query optimizer to convert not MYBOXES.description === "0,0,1,1"::box to MYBOXES.description !== "0,0,1,1"::box If a commutator operator name is supplied, POSTGRES searches for it in the catalog. If it is found and it does not yet have a commutator itself, then the commutator's entry is updated to have the current (new) operator as its commutator. This applies to the negator, as well. This is to allow the definition of two operators that are the commuta- tors or the negators of each other. The first operator should be defined without a commutator or negator (as appropriate). When the second operator is defined, name the first as the commutator or negator. The first will be updated as a side effect. The next two specifications are present to support the query optimizer in performing joins. POSTGRES can always evaluate a join (i.e., pro- cessing a clause with two tuple variables separated by an operator that returns a boolean) by iterative substitution [WONG76]. In addition, POSTGRES is planning on implementing a hash-join algorithm along the lines of [SHAP86]; however, it must know whether this strategy is appli- cable. For example, a hash-join algorithm is usable for a clause of the form: MYBOXES.description === MYBOXES2.description but not for a clause of the form: MYBOXES.description <<< MYBOXES2.description. The hashes flag gives the needed information to the query optimizer con- cerning whether a hash join strategy is usable for the operator in ques- tion. Similarly, the two sort operators indicate to the query optimizer whether merge-sort is a usable join strategy and what operators should be used to sort the two operand classes. For the === clause above, the optimizer must sort both relations using the operator, <<<. On the other hand, merge-sort is not usable with the clause: MYBOXES.description <<< MYBOXES2.description If other join strategies are found to be practical, POSTGRES will change the optimizer and run-time system to use them and will require addi- tional specification when an operator is defined. Fortunately, the research community invents new join strategies infrequently, and the added generality of user-defined join strategies was not felt to be worth the complexity involved. 94 01/23/93 DEFINE OPERATOR(COMMANDS) The last two pieces of the specification are present so the query optim- izer can estimate result sizes. If a clause of the form: MYBOXES.description <<< "0,0,1,1"::box is present in the qualification, then POSTGRES may have to estimate the fraction of the instances in MYBOXES that satisfy the clause. The func- tion res_proc must be a registered function (meaning it is already defined using define function) which accepts one argument of the correct data type and returns a floating point number. The query optimizer sim- ply calls this function, passing the parameter "0,0,1,1" and multiplies the result by the relation size to get the desired expected number of instances. Similarly, when the operands of the operator both contain instance vari- ables, the query optimizer must estimate the size of the resulting join. The function join_proc will return another floating point number which will be multiplied by the cardinalities of the two classes involved to compute the desired expected result size. The difference between the function my_procedure_1 (MYBOXES.description, "0,0,1,1"::box) and the operator MYBOXES.description === "0,0,1,1"::box is that POSTGRES attempts to optimize operators and can decide to use an index to restrict the search space when operators are involved. How- ever, there is no attempt to optimize functions, and they are performed by brute force. Moreover, functions can have any number of arguments while operators are restricted to one or two. EXAMPLE /* * The following command defines a new operator, * area-equality, for the BOX data type. */ define operator === ( arg1 = box, arg2 = box, procedure = area_equal_procedure, precedence = 30, associativity = left, commutator = ===, negator = !==, restrict = area_restriction_procedure, hashes, join = area-join-procedure, 95 DEFINE OPERATOR(COMMANDS) 01/23/93 sort = <<<, <<<) SEE ALSO define function(commands), remove operator(commands). BUGS Operator names cannot be composed of alphabetic characters in Version 4.1. Operator precedence and associativity are not implemented in Version 4.1. 96 01/23/93 DEFINE RULE(COMMANDS) NAME define rule - define a new rule SYNOPSIS define [instance | rewrite] rule rule_name [as exception to rule_name_2] is on event to object [[from clause] where clause] do [instead] [action | nothing | [actions...]] DESCRIPTION Define rule is used to define a new rule. There are two implementations of the rules system, one based on query rewrite and the other based on instance-level processing. In general, the instance-level system is more efficient if there are many rules on a single class, each covering a small subset of the instances. The rewrite system is more efficient if large scope rules are being defined. The user can optionally choose which rule system to use by specifying rewrite or instance in the com- mand. If the user does not specify which system to use, POSTGRES defaults to using the instance-level system. In the long run POSTGRES will automatically decide which rules system to use and the possibility of user selection will be removed. Here, _e_v_e_n_t is one of _r_e_t_r_i_e_v_e, _r_e_p_l_a_c_e, _d_e_l_e_t_e or _a_p_p_e_n_d. _O_b_j_e_c_t is either: a class name _o_r class.column The from clause, the where clause, and the _a_c_t_i_o_n are respectively nor- mal POSTQUEL from clauses, where clauses and collections of POSTQUEL commands with the following change: new or current can appear instead of an instance variable whenever an instance variable is permissible in POSTQUEL. The semantics of a rule is that at the time an individual instance is accessed, updated, inserted or deleted, there is a current instance (for retrieves, replaces and deletes) and a new instance (for replaces and appends). If the event specified in the on clause and the condition specified in the where clause are true for the current instance, then the _a_c_t_i_o_n part of the rule is executed. First, however, values from fields in the current instance and/or the new instance are substituted for: current.attribute-name new.attribute-name The _a_c_t_i_o_n part of the rule executes with same command and transaction identifier as the user command that caused activation. A note of caution about POSTQUEL rules is in order. If the same class name or instance variable appears in the event, where clause and the _a_c_t_i_o_n parts of a rule, they are all considered different tuple vari- ables. More accurately, new and current are the only tuple variables 97 DEFINE RULE(COMMANDS) 01/23/93 that are shared between these clauses. For example, the following two rules have the same semantics: on replace to EMP.salary where EMP.name = "Joe" do replace EMP ( ... ) where ... on replace to EMP-1.salary where EMP-2.name = "Joe" do replace EMP-3 ( ... ) where ... Each rule can have the optional tag instead. Without this tag _a_c_t_i_o_n will be performed in addition to the user command when the event in the condition part of the rule occurs. Alternately, the _a_c_t_i_o_n part will be done instead of the user command. In this later case, the action can be the keyword nothing. When choosing between the rewrite and instance rule systems for a par- ticular rule application, remember that in the rewrite system current refers to a relation and some qualifiers whereas in the instance system it refers to an instance (tuple). You must have _r_u_l_e _d_e_f_i_n_i_t_i_o_n access to a class in order to define a rule on it (see _c_h_a_n_g_e _a_c_l(commands). EXAMPLES /* * Make Sam get the same salary adjustment as Joe */ define rule example_1 is on replace to EMP.salary where current.name = "Joe" do replace EMP (salary = new.salary) where EMP.name = "Sam" At the time Joe receives a salary adjustment, the event will become true and Joe's current instance and proposed new instance are available to the execution routines. Hence, his new salary is substituted into the _a_c_t_i_o_n part of the rule which is subsequently executed. This propagates Joe's salary on to Sam. /* * Make Bill get Joe's salary when it is accessed */ define rule example_2 is on retrieve to EMP.salary where current.name = "Bill" do instead retrieve (EMP.salary) where EMP.name = "Joe" /* * Deny Joe access to the salary of employees in the shoe * department. (pg_username() returns the name of the current user) */ define rule example_3 is 98 01/23/93 DEFINE RULE(COMMANDS) on retrieve to EMP.salary where current.dept = "shoe" and pg_username() = "Joe" do instead nothing /* * Create a view of the employees working in the toy department. */ create TOYEMP(name = char16, salary = int4) define rule example_4 is on retrieve to TOYEMP do instead retrieve (EMP.name, EMP.salary) where EMP.dept = "toy" /* * All new employees must make 5,000 or less */ define rule example_5 is on append to EMP where new.salary > 5000 do replace new(salary = 5000) SEE ALSO postquel(commands), remove rule(commands), define view(commands). BUGS Exceptions are not implemented in Version 4.1. The object in a POSTQUEL rule cannot be an array reference and cannot have parameters. Aside from the "oid" field, system attributes cannot be referenced any- where in a rule. Among other things, this means that functions of instances (e.g., "foo(emp)" where "emp" is a class) cannot be called anywhere in a rule. The where clause cannot have a from clause. Only one POSTQUEL command can be specified in the _a_c_t_i_o_n part of a tuple rule and it can only be a _r_e_p_l_a_c_e, _a_p_p_e_n_d, _r_e_t_r_i_e_v_e or _d_e_l_e_t_e command. The rewrite rule system does support multiple action rules as long as _e_v_e_n_t is not _r_e_t_r_i_e_v_e. The query rewrite rule system now supports most rule semantics, and closely parallels the tuple system. It also attempts to avoid odd semantics by running instead rules before non-instead rules. 99 DEFINE TYPE (COMMANDS) 01/23/93 NAME define type - define a new base data type SYNOPSIS define type typename (externallength = (number | variable), [ externallength = (number | variable), ] input = input_function, output = output_function [, element = typename] [, delimiter = ] [, default = "string" ] [, send = procedure ] [, receive = procedure ] [, passedbyvalue]) DESCRIPTION Define type allows the user to register a new user data type with POSTGRES for use in the current data base. The user who defines a type becomes its owner. _T_y_p_e_n_a_m_e is the name of the new type and must be unique within the types defined for this database. _D_e_f_i_n_e _t_y_p_e requires the registration of two functions (using _d_e_f_i_n_e _f_u_n_c_t_i_o_n) before defining the type. The representation of a new base type is determined by the function _i_n_p_u_t, which converts the type's external representation to an internal representation usable by the operators and functions defined for the type. Naturally, _o_u_t_p_u_t per- forms the reverse transformation. New base data types can be fixed length, in which case _i_n_t_e_r_n_a_l _l_e_n_g_t_h is a positive integer, or variable length, in which case POSTGRES assumes that the new type has the same format as the POSTGRES-supplied data type, text. To indicate that a type is variable-length, set _i_n_t_e_r_- _n_a_l _l_e_n_g_t_h to -1. The external representation is similarly specified using _e_x_t_e_r_n_a_l _l_e_n_g_t_h. To indicate that a type is an array and to indicate that a type has array elements, indicate the type of the array element using the _e_l_e_m_e_n_t attribute. For example, to define an array of 4 byte integers (int4), set the _e_l_e_m_e_n_t attribute equal to int4. To indicate the delimiter to be used on arrays of this type, the _d_e_l_i_m_- _i_t_e_r attribute can be set to a specific character. The default delim- iter is the comma (",") character. A _d_e_f_a_u_l_t value is optionally available in case a user wants some specific bit pattern to mean "data not present." The optional functions _s_e_n_d and _r_e_c_e_i_v_e are used when the application program requesting POSTGRES services resides on a different machine. In this case, the machine on which POSTGRES runs may use a different format for the data type than used on the remote machine. In this case it is appropriate to convert data items to a standard form on output _s_e_n_d and convert from the standard format to the machine specific format on input 100 01/23/93 DEFINE TYPE (COMMANDS) _r_e_c_e_i_v_e. If these functions are not specified, then it is assumed that the internal format of the type is acceptable on all relevant machine architectures (for example, single characters do not have to be con- verted if passed from a Sun-3 to a DECstation). The optional _p_a_s_s_e_d_b_y_v_a_l_u_e flag indicates that operators and functions which use this data type should be passed an argument by value rather than by reference. Note that only types whose internal representation is at most four bytes may be passed by value. For new base types, a user can define operators, functions and aggre- gates using the appropriate facilities described in this section. ARRAY TYPES Two generalized built-in functions, array_in and array_out, exist for quick creation of variable length array types. These functions operate on any existing POSTGRES type. LARGE OBJECT TYPES A "regular" POSTGRES type can only be 8192 bytes in length. If you need a larger type you must create a Large Object type. The interface for these types is discussed at length in Section 7, the Large Object Back- end Interface. The length of all large object types is always variable, meaning the internallength for large objects is always -1. EXAMPLES /* * This command creates the box data type and then uses the * type in a class definition */ define type box (internallength = 8, input = my_procedure_1, output = my_procedure_2) create MYBOXES (id = int4, description = box) /* * This command creates a variable length array type with * integer elements. */ define type int4array (input = array_in, output = array_out, internallength = variable, element = int4) create MYARRAYS (id = int4, numbers = int4array) /* * This command creates a large object type and uses it in * a class definition. */ define type bigobj (input = lo_filein, output = lo_fileout, 101 DEFINE TYPE (COMMANDS) 01/23/93 internallength = variable) create BIG_OBJS (id = int4, obj = bigobj) SEE ALSO define function(commands), define operator(commands), remove type(commands), Large Object Backend Interface. 102 01/23/93 DEFINE VIEW(COMMANDS) NAME define view - construct a virtual class SYNOPSIS define view view_name ( [ dom_name_1 =] expression_1 {, [dom_name_i =] expression_i} ) [ from from_list ] [ where qual ] DESCRIPTION Define view will define a view of a class. This view is not physically materialized; instead the rule system is used to support view processing as in [STON90]. Specifically, a query rewrite retrieve rule is automat- ically generated to support retrieve operations on views. Then, the user can add as many update rules as he wishes to specify the processing of update operations to views. See [STON90] for a detailed discussion of this point. EXAMPLE /* * define a view consisting of toy department employees */ define view toyemp (e.name) from e in emp where e.dept = "toy" /* * Specify deletion semantics for toyemp */ define rewrite rule example1 is on delete to toyemp then do instead delete emp where emp.OID = current.OID SEE ALSO create(commands), define rule(commands), postquel(commands). 103 DELETE(COMMANDS) 01/23/93 NAME delete - delete instances from a class SYNOPSIS delete instance_variable [ from from_list ] [ where qual ] DESCRIPTION Delete removes instances which satisfy the qualification, _q_u_a_l, from the class specified by _i_n_s_t_a_n_c_e__v_a_r_i_a_b_l_e. _I_n_s_t_a_n_c_e__v_a_r_i_a_b_l_e is either a class name or a variable assigned by _f_r_o_m__l_i_s_t. If the qualification is absent, the effect is to delete all instances in the class. The result is a valid, but empty class. You must have write access to the class in order to modify it, as well as read access to any class whose values are read in the qualification (see _c_h_a_n_g_e _a_c_l(commands). EXAMPLE /* * Remove all employees who make over $30,000 */ delete emp where emp.sal > 30000 /* * Clear the hobbies class */ delete hobbies SEE ALSO destroy(commands). 104 01/23/93 DESTROY(COMMANDS) NAME destroy - destroy existing classes SYNOPSIS destroy classname-1 { , classname-i } DESCRIPTION Destroy removes classes from the data base. Only its owner may destroy a class. A class may be emptied of instances, but not destroyed, by using the delete statement. If a class being destroyed has secondary indices on it, then they will be removed first. The removal of just a secondary index will not affect the indexed class. This command may be used to destroy a version class which is not a parent of some other version. Destroying a class which is a parent of a version class is disallowed. Instead, the merge command should be used. Moreover, destroying a qclass whose fields are inherited by other classes is similarly disallowed. An inheritance hierarchy must be des- troyed from leaf level to root level. The destruction of classes is not reversable. Thus, a destroyed class will not be recovered if a transaction which destroys this class fails to commit. In addition, historical access to instances in a destroyed class is not possible. EXAMPLE /* * Destroy the emp class */ destroy emp /* * Destroy the emp and parts classes */ destroy emp, parts SEE ALSO delete(commands), merge(commands), remove index(commands). 105 DESTROYDB(COMMANDS) 01/23/93 NAME destroydb - destroy an existing database SYNOPSIS destroydb dbname DESCRIPTION Destroydb removes the catalog entries for an existing database and deletes the directory containing the data. It can only be executed by the database administrator (see _c_r_e_a_t_e_d_b(commands) for details). SEE ALSO createdb(commands), destroydb(unix). BUGS This query should NOT be executed interactively. The _d_e_s_t_r_o_y_d_b(unix) script should be used instead. 106 01/23/93 END(COMMANDS) NAME end - commit the current transaction SYNOPSIS end DESCRIPTION This commands commits the current transaction. All changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs. SEE ALSO abort(commands), begin(commands). 107 EXTEND INDEX(COMMANDS) 02/08/93 NAME extend index - extend a partial secondary index SYNOPSIS extend index index-name [where qual] DESCRIPTION This command extends the existing partial index called _i_n_d_e_x-_n_a_m_e. If a _q_u_a_l is given, the index will be extended to cover all instances that satisfy the predicate specified by _q_u_a_l (in addition to the instances the index already covers). If no _q_u_a_l is given, the index will be extended to be a complete index. Note that the predicate may only refer to attributes of the class on which the specified partial index was defined (see the define index command). EXAMPLE /* * Extend a partial index on employee salaries to include all employees over 40 */ extend index empsal where emp.age > 39 108 01/23/93 FETCH(COMMANDS) NAME fetch - fetch instance(s) from a portal SYNOPSIS fetch [ (forward | backward) ] [ ( number | all) ] [in portal_name] DESCRIPTION Fetch allows a user to retrieve instances from a portal named _p_o_r_t_a_l__n_a_m_e. The number of instances retrieved is specified by _n_u_m_b_e_r. If the number of instances remaining in the portal is less than _n_u_m_b_e_r, then only those available are fetched. Substituting the keyword all in place of a number will cause all remaining instances in the portal to be retrieved. Instances may be fetched in both _f_o_r_w_a_r_d and _b_a_c_k_w_a_r_d direc- tions. The default direction is _f_o_r_w_a_r_d. Updating data in a portal is not supported by POSTGRES, because mapping portal updates back to base classes is impossible in general as with view updates. Consequently, users must issue explicit replace commands to update data. Portals may only be used inside of begin/end transaction blocks, since the data that they store spans multiple user queries. EXAMPLE /* * set up and use a portal */ begin \g retrieve portal myportal (pg_user.all) \g fetch 2 in myportal \g fetch all in myportal \g close myportal \g end \g /* * Fetch all the instances available in the portal FOO */ fetch all in FOO /* * Fetch 5 instances backward in the portal FOO */ fetch backward 5 in FOO SEE ALSO close(commands), move(commands), retrieve(commands). 109 FETCH(COMMANDS) 01/23/93 BUGS Currently, the smallest transaction in POSTGRES is a single POSTQUEL command. It should be possible for a single fetch to be a transaction. 110 01/23/93 LISTEN(COMMANDS) NAME listen - listen for notification on a relation SYNOPSIS listen relation_name DESCRIPTION listen is used to register the current backend as a listener on the relation relation_name. When the command notify _r_e_l_a_t_i_o_n__n_a_m_e is called either from within a rule or at the query level, the frontends corresponding to the listening backends are notified. SEE ALSO define rule(commands), notify(commands), retrieve(commands), libpq. BUGS Listen is not implemented in Version 4.1. 111 LOAD(COMMANDS) 01/23/93 NAME load - dynamically load an object file SYNOPSIS load "filename" DESCRIPTION Load loads an object (or ".o") file into POSTGRES's address space. Once a file is loaded, all functions in that file can be accessed. This function is used in support of ADT's. If a file is not loaded using the load command, the file will be loaded automatically the first time the function is called by POSTGRES. Load can also be used to reload an object file if it has been edited and recompiled. Only objects created from C language files are supported at this time. EXAMPLE /* * Load the file /usr/postgres/demo/circle.o */ load "/usr/postgres/demo/circle.o" CAVEATS Functions in loaded object files should not call functions in other object files loaded through the load command, meaning, for example, that all functions in file A should call each other, functions in the stan- dard or math libraries, or in POSTGRES itself. They should not call functions defined in a different loaded file B. This is because if B is reloaded, the POSTGRES loader is not "smart" enough to relocate the calls from the functions in A into the new address space of B. If B is not reloaded, however, there will not be a problem. On diskless platforms or when running across NFS, load can take two or three minutes or more, depending on network traffic. On diskful plat- forms, load takes from a few seconds on Suns and Sparcs to several minutes on DECstations. On DECstations, you must use /_b_i_n/_c_c with the "-G O" option when compil- ing object files to be loaded. Note that if you are porting POSTGRES to a new platform, the load com- mand will have to work in order to support ADTs. 112 01/23/93 MERGE(COMMANDS) NAME merge - merge two classes SYNOPSIS merge classname1 into classname2 DESCRIPTION Merge will combine a version class, _c_l_a_s_s_n_a_m_e_1, with its parent, _c_l_a_s_s_n_a_m_e_2. If _c_l_a_s_s_n_a_m_e_2 is a base class, then this operation merges a differently encoded offset, _c_l_a_s_s_n_a_m_e_1, into its parent. On the other hand, if _c_l_a_s_s_n_a_m_e_2 is also a version, then this operation combines two differentially encoded offsets together into a single one. In either case any children of _c_l_a_s_s_n_a_m_e_1 becomes children of _c_l_a_s_s_n_a_m_e_2. It is disallowed for a version class to be merged into its parent class when the parent class is also the parent of another version class. Moreover, merging in the reverse direction is also allowed. Specifi- cally, merging the parent, _c_l_a_s_s_n_a_m_e_1, with a version, _c_l_a_s_s_n_a_m_e_2, causes _c_l_a_s_s_n_a_m_e_2 to become disassociated from its parent. As a side effect, _c_l_a_s_s_n_a_m_e_1 will be destroyed if is not the parent of some other version class. EXAMPLE /* * Combine office class and employee class */ merge office into employee SEE ALSO create version(commands), destroy(commands). BUGS Merge is not implemented in Version 4.1. 113 MOVE(COMMANDS) 01/23/93 NAME move - move the pointer in a portal SYNOPSIS move [ ( forward | backward ) ] [ ( number | all | to ( number | record_qual ) ) ] [ in portal_name ] DESCRIPTION Move allows a user to move the _i_n_s_t_a_n_c_e _p_o_i_n_t_e_r within the portal named _p_o_r_t_a_l__n_a_m_e. Each portal has an instance pointer, which points to the previous instance to be fetched. It always points to before the first instance when the portal is first created. The pointer can be moved _f_o_r_w_a_r_d or _b_a_c_k_w_a_r_d. It can be moved to an absolute position or over a certain distance. An absolute position may be specified by using to; distance is specified by a number. _R_e_c_o_r_d__q_u_a_l is a qualification without instance variables, aggregates, or set expressions which can be evaluated completely on a single instance in the portal. EXAMPLE /* * Move backwards 5 instances in the portal FOO */ move backward 5 in FOO /* * Move to the 6th instance in the portal FOO */ move to 6 in FOO SEE ALSO close(commands), fetch(commands), retrieve(commands). BUGS Move is not implemented in Version 4.1. The portal pointer may be moved using the fetch command and ignoring its return values. 114 01/23/93 NOTIFY(COMMANDS) NAME notify - signal all frontends and backends listening on a relation SYNOPSIS notify relation_name DESCRIPTION notify is used to awaken all backends and consequently all frontends listening on the relation _r_e_l_a_t_i_o_n__n_a_m_e. This can be used either within a tuple level rule as part of the action body or from a normal query. When used from within a normal query, this can be thought of as IPC. When used from within a rule, this can be thought of as the alerter mechanism. SEE ALSO define rule(commands), listen(commands), libpq. BUGS Notify is not implemented in Version 4.1. 115 PURGE(COMMANDS) 01/23/93 NAME purge - discard historical data SYNOPSIS purge classname [ before abstime ] [ after reltime ] DESCRIPTION Purge allows a user to specify the historical retention properties of a class. If the date specified is an absolute time such as "Jan 1 1987", POSTGRES will discard tuples whose validity expired before the indicated time. Purge with no _b_e_f_o_r_e clause is equivalent to "purge before now". Until specified with a purge command, instance preservation defaults to "forever". The user may purge a class at any time as long as the purge date never decreases. POSTGRES will enforce this restriction, silently. EXAMPLE /* * Always discard data in the EMP class * prior to January 1, 1989 */ purge EMP before "Jan 1 1989" /* * Retain only the current data in EMP */ purge EMP BUGS Error messages are quite unhelpful. A complaint about "inconsistent times" followed by several nine-digit numbers indicates an attempt to "back up" a purge date on a relation. 116 01/23/93 REMOVE AGGREGATE(COMMANDS) NAME remove aggregate - remove the definition of an aggregate SYNOPSIS remove aggregate aggname DESCRIPTION Remove aggregate will remove all reference to an existing aggregate definition. To execute this command the current user must be the the owner of the aggregate. EXAMPLE /* * Remove the average aggregate */ remove aggregate avg SEE ALSO define aggregate(commands). 117 REMOVE FUNCTION(COMMANDS) 01/23/93 NAME remove function - remove a user-defined C function SYNOPSIS remove function function_name DESCRIPTION Remove function will remove references to an existing C function. To execute this command the user must be the owner of the function. EXAMPLE /* * this command removes the square root function */ remove function sqrt SEE ALSO define function(commands). BUGS No support is currently provided for removing POSTQUEL functions. No checks are made to ensure that types, operators or access methods that rely on the function have been removed first. 118 01/23/93 REMOVE INDEX(COMMANDS) NAME remove index - removes an index from POSTGRES SYNOPSIS remove index index_name DESCRIPTION This command drops an existing index from the POSTGRES system. To exe- cute this command you must be the owner of the index. EXAMPLE /* * this command will remove the EMP-INDEX index */ remove index emp_index SEE ALSO define index(commands). 119 REMOVE OPERATOR(COMMANDS) 01/23/93 NAME remove operator - remove an operator from the system SYNOPSIS remove operator opr_desc DESCRIPTION This command drops an existing operator from the database. To execute this command you must be the owner of the operator. _O_p_r__d_e_s_c is the name of the operator to be removed followed by a parenthesized list of the operand types for the operator. It is the user's responsibility to remove any access methods, operator classes, etc. that rely on the deleted operator. EXAMPLE /* * Remove power operator a^n for 4 byte integers */ remove operator ^ (int4, int4) SEE ALSO define operator(commands). 120 01/23/93 REMOVE RULE(COMMANDS) NAME remove rule - removes a current rule from POSTGRES SYNOPSIS remove [ instance | rewrite ] rule rule_name DESCRIPTION This command drops the rule named rule_name from the specified POSTGRES rule system. POSTGRES will immediately cease enforcing it and will purge its definition from the system catalogs. EXAMPLE /* * This example drops the rewrite rule example_1 */ remove rewrite rule example_1 SEE ALSO define rule(commands), remove view(commands). BUGS Once a rule is dropped, access to historical information the rule has written may disappear. 121 REMOVE TYPE(COMMANDS) 01/23/93 NAME remove type - remove a user-defined type from the system catalogs SYNOPSIS remove type typename DESCRIPTION This command removes a user type from the system catalogs. Only the owner of a type can remove it. It is the user's responsibility to remove any operators, functions, aggregates, access methods, sub-types, classes, etc. that use a deleted type. EXAMPLE /* * remove the box type */ remove type box SEE ALSO introduction(commands), define type(commands), remove operator(commands). BUGS It is still possible to remove built-in types. 122 01/23/93 RENAME(COMMANDS) NAME rename - rename a class or an attribute in a class SYNOPSIS rename classname1 to classname2 rename attname1 in classname to attname2 DESCRIPTION The rename command causes the name of a class or attribute to change without changing any of the data contained in the affected class. Thus, the class or attribute will remain of the same type and size after this command is executed. You must own the class being modified in order to rename it or part of its schema. Renaming any part of the schema of a system catalog is not permitted. EXAMPLE /* * change the emp class to personnel */ rename emp to personnel /* * change the sports attribute to hobbies */ rename sports in emp to hobbies BUGS Execution of historical queries using classes and attributes whose names have changed will produce incorrect results in many situations. Renaming of types, operators, rules, etc. should also be supported. 123 REPLACE(COMMANDS) 01/23/93 NAME replace - replace values of attributes in a class SYNOPSIS replace instance_variable ( att_name1 = expression1 {, att_name-i = expression-i } ) [ from from_list ] [ where qual ] DESCRIPTION Replace changes the values of the attributes specified in _t_a_r_g_e_t__l_i_s_t for all instances which satisfy the qualification, _q_u_a_l. Only attri- butes which are to be modified need appear in _t_a_r_g_e_t__l_i_s_t. You must have write access to the class in order to modify it, as well as read access to any class whose values are read in the target list or qualification (see _c_h_a_n_g_e _a_c_l(commands). EXAMPLE /* * Give all employees who work for Smith a 10% raise */ replace emp(sal = 1.1 * emp.sal) where emp.mgr = "Smith" 124 01/23/93 RETRIEVE(COMMANDS) NAME retrieve - retrieve instances from a class SYNOPSIS retrieve [ (into classname [ archive_mode ] | portal portal_name ) | iportal portal_name ] [unique] ( [ attr_name1 =] expression1 {, [attr_name-i =] expression-i} ) [ from from_list ] [ where qual ] [ sort by attr_name-1 [using operator] { , attr_name-j [using operator] } ] DESCRIPTION Retrieve will get all instances which satisfy the qualification, _q_u_a_l, compute the value of each element in the target list, and either return them to an application program through one of two different kinds of portals or store them in a new class. If _c_l_a_s_s_n_a_m_e is specified, the result of the query will be stored in a new class with the indicated name. If an archive specification, _a_r_c_h_i_v_e__m_o_d_e of _l_i_g_h_t, _h_e_a_v_y, or _n_o_n_e is not specifed, then it defaults to _l_i_g_h_t archiving. (This default may be changed at a site by the DBA). The current user will be the owner of the new class. The class will have attribute names as specified in the target list. A class with this name owned by the user must not already exist. The keyword all can be used when it is desired to retrieve all fields of a class. If no result _c_l_a_s_s_n_a_m_e is specified, then the result of the query will be available on the specified portal and will not be saved. If no por- tal name is specified, the blank portal is used by default. For a por- tal specified with the iportal keyword, retrieve passes data to an application without conversion to external format. For a portal speci- fied with the portal keyword, retrieve passes data to an application after first converting it to the external representation. For the blank portal, all data is converted to external format. Duplicate instances are not removed when the result is displayed through a portal unless the optional unique tag is appended, in which case the instances in the tar- get list are sorted according to the sort clause and duplicates are removed before being returned. Instances retrieved into a portal may be fetched in subsequent queries by using the fetch command. Since the results of a retrieve portal span queries, retrieve portal may only be executed inside of a begin/end transaction block. Attempts to use named portals outside of a transac- tion block will result in a warning message from the parser, and the query will be discarded. The sort clause allows a user to specify that he wishes the instances sorted according to the corresponding operator. This operator must be a binary one returning a boolean. Multiple sort fields are allowed and are applied from left to right. 125 RETRIEVE(COMMANDS) 01/23/93 You must have read access to a class to read its values (see _c_h_a_n_g_e _a_c_l(commands). EXAMPLE /* * Find all employees who make more than their manager */ retrieve (e.name) from e, m in emp where e.mgr = m.name and e.sal > m.sal /* * Retrieve all fields for those employees who make * more than the average salary */ retrieve into avgsal(ave = float8ave {emp.sal}) \g retrieve (e.all) from e in emp where e.sal > avgsal.ave \g /* * retrieve employees's names sorted */ retrieve unique (emp.name) sort by name using < /* * retrieve all employees's names that were valid on 1/7/85 * in sorted order */ retrieve (e.name) from e in emp["January 7 1985"] sort by name using < /* * construct a new class, raise, containing 1.1 * times all employee's salaries */ retrieve into raise (salary = 1.1 * emp.salary) /* * do a retrieve into a portal */ begin \g retrieve portal myportal (pg_user.all) \g fetch 2 in myportal \g 126 01/23/93 RETRIEVE(COMMANDS) fetch all in myportal \g close myportal \g end \g SEE ALSO close(commands), create(commands), fetch(commands), postquel(commands). BUGS Retrieve into does not delete duplicates in Version 4.1. _A_r_c_h_i_v_e__m_o_d_e is not implemented in Version 4.1. If the backend crashes in the course of executing a retrieve into, the class file will remain on disk. It can be safely removed by the data- base DBA, but a subsequent retrieve into to the same name will fail with a cryptic error message about "BlockExtend". A solution to this problem is being investigated and will be released in later version. Retrieve iportal returns data in an architecture dependent format, namely that of the server on which the backend is running. A standard data format should be adopted, most likely XDR. At that point, there will be no need to distinguish among external and internal data. Aggregate functions must appear in the target list. 127 VACUUM(COMMANDS) 01/23/93 NAME vacuum - vacuum a database SYNOPSIS vacuum DESCRIPTION Vacuum is the POSTGRES vacuum cleaner. It opens every class in the database, moves deleted records to the archive for archived relations, cleans out records from aborted transactions, and updates statistics in the system catalogs. The statistics maintained include the number of tuples and number of pages stored in all classes. Running vacuum periodically will increase POSTGRES's speed in processing user queries. The open database is the one that is vacuumed. This is a new POSTQUEL command in Version 4.1; earlier versions of POSTGRES had a separate pro- gram for vacuuming databases. That program has been replaced by the _v_a_c_u_u_m(unix) shell script. We recommend that production databases be vacuumed nightly, in order to keep statistics relatively current. The vacuum query may be executed at any time, however. In particular, after copying a large class into POSTGRES or deleting a large number of records, it may be a good idea to issue a vacuum query. This will update the system catalogs with the results of all recent changes, and allow the POSTGRES query optimizer to make better choices in planning user queries. SEE ALSO vacuum(unix). 128 01/23/93 INTRODUCTION(LIBPQ) NAME SECTION 5 - LIBPQ DESCRIPTION LIBPQ is the programmer's interface to POSTGRES. LIBPQ is a set of library routines which allow queries to pass to the POSTGRES backend and instances to return through an IPC channel. This version of the documentation is based on the C library. Three short programs are listed at the end of this section as examples of LIBPQ programming (though not necessarily of good programming). CONTROL AND INITIALIZATION Environment Variables The following environment variables can be used to set up default values for an environment and to avoid hard-coding database names into an application program: PGHOST sets the default server name. PGDATABASE sets the default POSTGRES database name. PGPORT sets the default communication port with the POSTGRES backend. PGTTY sets the file or tty on which debugging messages from the backend server are displayed. PGREALM sets the _K_e_r_b_e_r_o_s realm to use with POSTGRES, if it is different from the local realm. If PGREALM 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 environ- ment variable is only used if _K_e_r_b_e_r_o_s authentication is enabled; see _i_n_t_r_o_d_u_c_t_i_o_n(unix) for additional informa- tion on _K_e_r_b_e_r_o_s. Internal Variables The following internal variables of LIBPQ can be accessed by the pro- grammer: char *PQhost; /* the server on which POSTGRES backend is running. */ char *PQport = NULL; /* The communication port with the POSTGRES backend. */ char *PQtty; /* The tty on the PQhost backend on which backend messages are displayed. */ 129 INTRODUCTION(LIBPQ) 01/23/93 char *PQoption; /* Optional arguements to the backend */ char *PQdatabase; /* backend database to access */ int PQportset = 0; /* 1 if communication with backend is established */ int PQxactid = 0; /* Transaction ID of the current transaction */ int PQtracep = 0; /* 1 to print out front-end debugging messages */ int PQAsyncNotifyWaiting = 0; /* 1 if one or more asynchronous notifications have been triggered */ QUERY EXECUTION FUNCTIONS The following routines control the execution of queries from a C pro- gram. PQsetdb Make the specified database the current database and reset communication using _P_Q_r_e_s_e_t (see below). void PQsetdb(dbname) char *dbname; PQdb Returns the name of the POSTGRES database being accessed, or NULL if no database is open. Only one database can be accessed at a time. The database name is a string lim- ited to 16 characters. char *PQdb() PQreset Reset the communication port with the backend in case of errors. This function will close the IPC socket connec- tion to the backend thereby causing the next _P_Q_e_x_e_c call to ask for a new one from the _p_o_s_t_m_a_s_t_e_r. When the back- end notices the socket was closed it will exit, and when the _p_o_s_t_m_a_s_t_e_r is asked for the new connection it will start a new backend. void PQreset() PQfinish Close communication ports with the backend. Terminates communications and frees up the memory taken up by the LIBPQ buffer. void PQfinish() 130 01/23/93 INTRODUCTION(LIBPQ) PQfn Send a function call to the POSTGRES backend. Provides access to the POSTGRES fast path facility, a trapdoor into the system internals. See the _F_A_S_T _P_A_T_H section of the manual. char *PQfn(fnid, result_buf, result_len, result_is_int, args, nargs) int fnid; int *result_buf; int result_len; int result_is_int; PQArgBlock *args; int nargs; PQexec Submit a query to POSTGRES. Returns a status indicator or an error message. If the query returns data (e.g., _f_e_t_c_h), _P_Q_e_x_e_c returns a string consisting of the charac- ter "P" followed by the name of the portal buffer. When the query does not return instances, _P_Q_e_x_e_c will return a string consisting of the character "C" followed by the command tag (e.g., "CREPLACE"). If an error occured dur- ing the execution of the query _P_Q_e_x_e_c will return (for historical reasons) the character "R". char *PQexec(query) char *query; PORTAL FUNCTIONS A portal is a POSTGRES buffer from which instances can be fetched. Each portal has a string name (currently limited to 16 bytes). A portal is initialized by submitting a _r_e_t_r_i_e_v_e statement using the _P_Q_e_x_e_c func- tion, for example: retrieve portal foo (EMP.all) The programmer can then move data from the portal into LIBPQ by execut- ing a _f_e_t_c_h statement, e.g: fetch 10 in foo fetch all in foo If no portal name is specified in a query, the default portal name is the string "blank", known as the blank portal. All qualifying instances in a blank portal are fetched immediately, without the need for the pro- grammer to issue a separate _f_e_t_c_h command. Data fetched from a portal into LIBPQ is moved into a portal buffer. Portal names are mapped to portal buffers through an internal table. Each instance in a portal buffer has an index number locating its 131 INTRODUCTION(LIBPQ) 01/23/93 position in the buffer. In addition, each field in an instance has a name (attribute name) and a field index (attribute number). A single _r_e_t_r_i_e_v_e command can return multiple types of instances. This can happen if a POSTGRES function is executed in the evaluation of a query or if the query returns multiple instance types from an inheri- tance hierarchy. Consequently, the instances in a portal are set up in groups. Instances in the same group are guaranteed to have the same instance format. Portals that are associated with normal user commands are called syn- chronous. In this case, the application program is expected to issue a retrieval followed by one or more fetch commands. The functions that follow can now be used to manipulate data in the portal. PQnportals Return the number of open portals. If _r_u_l_e__p is not 0, then only return the number of asynchronous portals. int PQnportals(rule_p) int rule_p ; PQpnames Return all portal names. If _r_u_l_e__p is not 0, then only return the names of asynchronous portals. void PQpnames(pnames, rule_p) char *pnames[MAXPORTALS]; int rule_p; PQparray Return the portal buffer given a portal name, _p_n_a_m_e. PortalBuffer *PQparray(pname) char *pname; PQclear Free storage claimed by portal _p_n_a_m_e. void PQclear(pname) char *pname; PQntuples Return the number of instances (tuples) in a portal buffer _p_o_r_t_a_l. int PQntuples(portal) PortalBuffer *portal; PQngroups Return the number of instance groups in a portal buffer _p_o_r_t_a_l. int PQngroups(portal) PortalBuffer *portal 132 01/23/93 INTRODUCTION(LIBPQ) PQntuplesGroup Return the number of instances in an instance group _g_r_o_u_p__i_n_d_e_x associated with a portal buffer _p_o_r_t_a_l. int PQntuplesGroup(portal, group_index) PortalBuffer *portal; int group_index; PQnfieldsGroup Return the number of fields (attributes) for the instances (tuples) in instance group _g_r_o_u_p__i_n_d_e_x associ- ated with portal buffer _p_o_r_t_a_l. int PQnfieldsGroup(portal, group_index) PortalBuffer *portal; int group_index; PQfnameGroup Return the field (attribute) name for the instances (tuples) in instance group _g_r_o_u_p__i_n_d_e_x (associated with portal buffer _p_o_r_t_a_l) and the field index _f_i_e_l_d__n_u_m_b_e_r. char *PQfnameGroup(portal, group_index, field_number) PortalBuffer *portal; int group_index; int field_number; PQfnumberGroup Return the field index (attribute number) given the instance group _g_r_o_u_p__i_n_d_e_x (associated with portal buffer _p_o_r_t_a_l) and the field (attribute) name _f_i_e_l_d__n_a_m_e. int PQfnumberGroup(portal, group_index, field_name) PortalBuffer *portal; int group_index; char *field_name; PQgetgroup Returns the index of the instance group (associated with portal buffer _p_o_r_t_a_l) that contains a particular instance _t_u_p_l_e__i_n_d_e_x. int PQgetgroup(portal, tuple_index) PortalBuffer *portal; int tuple_index; PQnfields Returns the number of fields (attributes) in an instance _t_u_p_l_e__i_n_d_e_x contained in portal buffer _p_o_r_t_a_l. int PQnfields(portal, tuple_index) PortalBuffer *portal; int tuple_index; 133 INTRODUCTION(LIBPQ) 01/23/93 PQfnumber Returns the field index (attribute number) of a given field name _f_i_e_l_d__n_a_m_e within an instance _t_u_p_l_e__i_n_d_e_x con- tained in portal buffer _p_o_r_t_a_l. int PQfnumber(portal, tuple_index, field_name) PortalBuffer *portal; int tuple_index; char *field_name; PQfname Returns the name of a field (attribute) _f_i_e_l_d__n_u_m_b_e_r of instance _t_u_p_l_e__i_n_d_e_x contained in portal buffer _p_o_r_t_a_l. char *PQfname(portal, tuple_index, field_number) PortalBuffer *portal; int tuple_index; int field_number; PQftype Returns the type of a field (attribute) _f_i_e_l_d__n_u_m_b_e_r of instance _t_u_p_l_e__i_n_d_e_x contained in portal buffer _p_o_r_t_a_l. The type returned is an internal coding of a type. int PQftype(portal, tuple_index, field_number) PortalBuffer *portal; int tuple_index; int field_number; PQsametype Returns 1 if two instances _t_u_p_l_e__i_n_d_e_x_1 and _t_u_p_l_e__i_n_d_e_x_2, both contained in portal buffer _p_o_r_t_a_l, have the same field (attribute) types. int PQsametype(portal, tuple_index1, tuple_index2) PortalBuffer *portal; int tuple_index1; int tuple_index2; PQgetvalue Returns a field (attribute) value. char *PQgetvalue(portal, tuple_index, field_number) PortalBuffer *portal; int tuple_index; int field_number; PQgetlength Return the length of a field (attribute) value in bytes. If the field is a _s_t_r_u_c_t _v_a_r_l_e_n_a, the length returned here does not include the size field of the varlena, i.e., it is 4 bytes less. 134 01/23/93 INTRODUCTION(LIBPQ) char *PQgetlength(portal, tuple_index, field_number) PortalBuffer *portal; int tuple_index; int field_number; If the portal is blank, or the portal was specified with the portal key- word, all values are returned as null-delimited strings. It is the programmer's responsibility to convert them to the correct type. If the portal is specified with the iportal keyword, all values are returned in an architecture-dependent internal (binary) format, namely, the format generated by the _i_n_p_u_t function specified through _d_e_f_i_n_e _t_y_p_e(commands). Again, it is the programmer's responsibility to convert the data to the correct type. ASYNCHRONOUS PORTALS AND NOTIFICATION Asynchronous portals - query results of rules - are implemented using two mechanisms: relations and notification. The query result is transferred through a relation. The notification is done with special POSTQUEL commands and the frontend/backend protocol. The first step in using asynchronous portals is to _l_i_s_t_e_n(commands) on a given class name. The fact that a process is listening on the class is shared with all backend servers running on a database; when one sets off the rule, it signals its peers. The backend server associated with the listening frontend process then sends its client an IPC message, which the frontend process must explicitly catch by polling the variable _P_Q_A_s_y_n_c_N_o_t_i_f_y_W_a_i_t_i_n_g. When this variable is non-zero, the frontend pro- cess should execute a null (empty) query, i.e., PQexec(" "); in order to retrieve the notification data held for it by the backend server. The frontend must call _P_Q_N_o_t_i_f_i_e_s in order to find out which classes the data corresponds to (i.e., which notification events have been set off). These events must then be individually cleared by cal- ling _P_Q_R_e_m_o_v_e_N_o_t_i_f_y on each element of the list returned by _P_Q_N_o_t_i_f_i_e_s. The second sample program gives an example of the use of asynchronous portals. PQNotifies Return the list of relations on which notification has occurred. PQNotifyList *PQNotifies() PQRemoveNotify Remove the notification from the list of unhandled notif- ications. PQNotifyList *PQRemoveNotify(pqNotify) PQNotifyList *pqNotify; 135 INTRODUCTION(LIBPQ) 01/23/93 FUNCTIONS ASSOCIATED WITH THE COPY COMMAND The _c_o_p_y command in POSTGRES has options to read from or write to the network connection used by LIBPQ. Therefore, functions are necessary to access this network connection directly so applications may take full advantage of this capability. For more information about the _c_o_p_y command, see _c_o_p_y(commands). PQgetline Reads a null-terminated line into a buffer _s_t_r_i_n_g of size _l_e_n_g_t_h. PQgetline(string, length) char *string; int length PQputline Sends a null-terminated _s_t_r_i_n_g. PQputline(string) char *string; PQendcopy Syncs with the backend. This function waits until the backend has finished processing the copy. It should either be issued when the last string has been sent to the backend using _P_Q_p_u_t_l_i_n_e or when the last string has been received from the backend using _P_G_g_e_t_l_i_n_e. 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. int PQendcopy() As an example: PQexec("create foo (a=int4, b=char16, d=float8)"); PQexec("copy foo from stdin"); PQputline("3hello world4.50); PQputline("4goodbye world7.11"); ... PQputline(".\n"); PQendcopy(); LIBPQ TRACING FUNCTIONS PQtrace Enable tracing. The routine sets the _P_Q_t_r_a_c_e_p variable to 1 which causes debug messages to be printed. You should note that the messages will be printed to stdout by default. If you would like different behavior you must set the variable 136 01/23/93 INTRODUCTION(LIBPQ) FILE *debug_port to the appropriate stream. void PQtrace() PQuntrace Disable tracing started by _P_Q_t_r_a_c_e. void PQuntrace() USER AUTHENTICATION FUNCTIONS If the user has generated the appropriate authentication credentials (e.g., obtaining _K_e_r_b_e_r_o_s tickets), the frontend/backend authentication process is handled by _P_Q_e_x_e_c without any further intervention. The fol- lowing routines may be called by LIBPQ programs to tailor the behavior of the authentication process. fe_getauthname Returns a pointer to static space containing whatever name the user has authenticated. Use of this routine in place of calls to _g_e_t_e_n_v(3) or _g_e_t_p_w_u_i_d(3) by applica- tions is highly recommended, as it is entirely possible that the authenticated user name is not the same as value of the USER environment variable or the user's entry in /_e_t_c/_p_a_s_s_w_d. This becomes an important issue if the user name is being used as a value in a database interaction (e.g., using the user name as the default database name, as is done by _m_o_n_i_t_o_r(unix). char *fe_getauthname() fe_setauthsvc Specifies that LIBPQ should use authentication service _n_a_m_e rather than its compiled-in default. This value is typically taken from a command-line switch. void fe_setauthsvc(name) char *name; BUGS The query buffer is 8192 bytes long, and queries over that length will be silently truncated. Asynchronous portals are not fully implemented in Version 4.1. 137 INTRODUCTION(LIBPQ) 01/23/93 SAMPLE PROGRAM 1 /* * testlibpq.c - * Test the C version of LIBPQ, the POSTGRES frontend library. */ #include #include "tmp/libpq.h" main () { int i, j, k, g, n, m, t; PortalBuffer *p; char pnames[MAXPORTALS][portal_name_length]; /* Specify the database to access. */ PQsetdb ("pic_demo"); /* Start a transaction block for eportal */ PQexec ("begin"); /* Fetch instances from the EMP class. */ PQexec ("retrieve portal eportal (EMP.all)"); PQexec ("fetch all in eportal"); /* Examine all the instances fetched. */ p = PQparray ("eportal"); g = PQngroups (p); t = 0; for (k = 0; k < g; k++) { printf ("\nA new instance group:\n"); n = PQntuplesGroup (p, k); m = PQnfieldsGroup (p, k); /* Print out the attribute names. */ for (i = 0; i < m; i++) printf ("%-15s", PQfnameGroup (p, k, i)); printf ("\n"); /* Print out the instances. */ for (i = 0; i < n; i++) { for (j = 0; j < m; j++) printf("%-15s", PQgetvalue(p, t+i, j)); printf ("\n"); } t += n; } /* Close the portal. */ PQexec ("close eportal"); /* End the transaction block */ 138 01/23/93 INTRODUCTION(LIBPQ) PQexec("end"); /* Try out some other functions. */ /* Print out the number of portals. */ printf ("\nNumber of portals open: %d.\n", PQnportals ()); /* If any tuples are returned by rules, print out * the portal name. */ if (PQnportals (1)) { printf ("Tuples are returned by rules. \n"); PQpnames (pnames, 1); for (i = 0; i < MAXPORTALS; i++) if (pnames[i] != NULL) printf ("portal used by rules: %s\n", pnames[i]); } /* finish execution. */ PQfinish (); } 139 INTRODUCTION(LIBPQ) 01/23/93 SAMPLE PROGRAM 2 /* * Testing of asynchronous portal interface. * * Do the following at the monitor: * * * create test1 (i = int4) \g * * create test1a (i = int4) \g * * * define rule r1 is on append to test1 do * [append test1a (i = new.i) * notify test1a] \g * * Then start up this process. * * * append test1 (i = 10) \g * * The value i=10 should be printed by this process. */ #include "tmp/simplelists.h" #include "tmp/libpq.h" void main() { extern int PQAsyncNotifyWaiting; PQNotifyList *l; PortalBuffer *portalbuf; char *res; int ngroups,tupno, grpno, ntups, nflds; PQsetdb(getenv("USER")); PQexec("listen test1a"); while(1) { sleep(1); if (PQAsyncNotifyWaiting) { PQAsyncNotifyWaiting = 0; PQexec(" "); l = PQnotifies(); if (l != NULL) { printf("notification on relation %s\n", l->relname); res = PQexec("retrieve (test1a.i)"); if (*res == 'E') { fprintf(stderr,"%s\nfailed",++res); goto exit_error; } if (*res != 'P') { fprintf(stderr,"%s\nno portal",++res); } /* get tuples in relation */ portalbuf = PQparray(++res); 140 01/23/93 INTRODUCTION(LIBPQ) ngroups = PQngroups(portalbuf); for (grpno = 0; grpno < ngroups; grpno++) { ntups = PQntuplesGroup(portalbuf,grpno); nflds = PQnfieldsGroup(portalbuf,grpno); if (nflds != 1) { fprintf(stderr, "expected 1 attributes, got %d\n", nflds); goto exit_error; } for (tupno = 0; tupno < ntups; tupno++) { printf("got i=%s\n", PQgetvalue(portalbuf,tupno,0)); } } break; } } } PQfinish(); exit(0); exit_error: PQfinish(); exit(1); } 141 INTRODUCTION(LIBPQ) 01/23/93 SAMPLE PROGRAM 3 /* * Test program for the binary portal interface. * * Create a test database and do the following at the monitor: * * * create test1 (i = int4, d = float4, p = polygon)\g * * append test1 (i = 1, d = 3.567, * p = "(3.0,4.0,1.0,2.0)"::polygon)\g * * append test1 (i = 2, d = 89.05, * p = "(4.0,3.0,2.0,1.0)"::polygon)\g * * adding as many tuples as desired. * * Start up this program. The contents of class "test1" should be * printed, e.g.: tuple 0: got i=(4 bytes) 1, d=(4 bytes) 3.567000, p=(72 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.05000, p=(72 bytes) 2 points, boundbox=(hi=4.000000,3.000000 / lo=2.000000,1.000000) */ #include "tmp/simplelists.h" #include "tmp/libpq.h" #include "utils/geo-decls.h" void main() { PortalBuffer *portalbuf; char *res; int ngroups, tupno, grpno, ntups, nflds; PQsetdb("test"); /* change this to your database name */ PQexec("begin"); res = (char *) PQexec("retrieve iportal junk (test1.all)"); if (*res == 'E') { fprintf(stderr,"\nError: %s\n",++res); goto exit_error; } res = (char *) PQexec("fetch all in junk"); if (*res != 'P') { fprintf(stderr,"\nError: no portal\n"); goto exit_error; } /* get tuples in relation */ 142 01/23/93 INTRODUCTION(LIBPQ) portalbuf = PQparray(++res); ngroups = PQngroups(portalbuf); for (grpno = 0; grpno < ngroups; grpno++) { ntups = PQntuplesGroup(portalbuf, grpno); if ((nflds = PQnfieldsGroup(portalbuf, grpno)) != 3) { fprintf(stderr, "\nError: expected 3 attributes, got %d\n", nflds); goto exit_error; } for (tupno = 0; tupno < ntups; tupno++) { int *ival; /* 4 bytes */ float *fval; /* 4 bytes */ unsigned plen; POLYGON *pval; ival = (int *) PQgetvalue(portalbuf, tupno, 0); fval = (float *) PQgetvalue(portalbuf, tupno, 1); plen = PQgetlength(portalbuf, tupno, 2); if (!(pval = (POLYGON *) palloc(plen + sizeof(long)))) { fprintf(stderr, "\nError: palloc returned zero bytes\n"); goto exit_error; } pval->size = plen + sizeof(long); bcopy(PQgetvalue(portalbuf, tupno, 2), (char *) &pval->npts, plen); printf ("tuple %d: got\n\ \t i=(%d bytes) %d,\n\ \t d=(%d bytes) %f,\n\ \t p=(%d bytes) %d points,\n\ \t\t boundbox=(hi=%f,%f / lo=%f,%f)\n", tupno, PQgetlength(portalbuf, tupno, 0), *ival, PQgetlength(portalbuf, tupno, 1), *fval, PQgetlength(portalbuf, tupno, 2), pval->npts, pval->boundbox.xh, pval->boundbox.yh, pval->boundbox.xl, pval->boundbox.yl); } } PQexec("end"); PQfinish(); exit(0); exit_error: PQexec("end"); PQfinish(); exit(1); } 143 INTRODUCTION(FAST PATH) 01/23/93 NAME SECTION 6 - FAST PATH SYNOPSIS retrieve (retval = function([ arg {, arg } ])) DESCRIPTION POSTGRES allows any valid POSTGRES function to be called in this way. Prior implementations of fast path allowed user functions to be called directly. For now, the above syntax should be used, with arguments cast into the appropriate types. By executing the above type of query, con- trol transfers completely to the user function; any user function can access any POSTGRES function or any global variable in the POSTGRES address space. There are six levels at which calls can be performed: 1) Traffic cop level If a function wants to execute a POSTGRES command and pass a string representation, this level is appropriate. 2) Parser A function can access the POSTGRES parser, passing a string and getting a parse tree in return. 3) Query optimizer A function can call the query optimizer, passing it a parse tree and obtaining a query plan in return. 4) Executor A function can call the executor and pass it a query plan to be executed. 5) Access methods A function can directly call the access methods if it wishes. 6) Function manager A function can call other functions using this level. Documentation of layers 1-6 will appear at some future time. Meanwhile, fast path users must consult the source code for function names and arguments at each level. It should be noted that users who are concerned with ultimate perfor- mance can bypass the query language completely and directly call func- tions that in turn interact with the access methods. On the other hand, a user can implement a new query language by coding a function with an internal parser that then calls the POSTGRES optimizer and executor. Complete flexibility to use the pieces of POSTGRES as a tool kit is thereby provided. 144 01/23/93 INTRODUCTION(LARGE OBJECTS) NAME SECTION 7 - LARGE OBJECTS DESCRIPTION In POSTGRES, data values are stored in tuples, and individual tuples cannot span multiple 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 _l_a_r_g_e _o_b_j_e_c_t interface. This interface provides file-oriented access to user data that has been explicitly declared to be a large type. Version 4.1 of POSTGRES supports two different implementations of large objects. These two implementations allow users to trade off speed of access against transaction protection and crash recovery on large object data. Applications that can tolerate lost data may store object data in conventional files that are fast to access, but cannot be recovered in the case of system crashes. For applications that require stricter guarantees of durability, a transaction-protected large object implemen- tation is available. This section describes the two implementations and the programmatic and query language interfaces to large object data. Unlike the BLOB support provided by most commercial relational database management systems, POSTGRES allows users to define specific large object types. POSTGRES large objects are first-class objects in the database, and any operation that can be applied to a conventional (small) abstract data type (ADT) may also be applied to a large one. For example, two different large object types, such as _i_m_a_g_e and _v_o_i_c_e, may be created. Functions that operate on image data, and other func- tions that operate on voice data, may be declared to the database sys- tem. The data manager will distinguish between image and voice data automatically, and will allow users to invoke the appropriate functions on values of each of these types. In addition, indices may be created large data values, or on functions of them. Finally, operators may be defined that operate on large values. Users may invoke these functions and operators from the query language. The database system will enforce type restrictions on large object data values. The POSTGRES large object interface is modeled after the Unix file sys- tem interface, with analogs of _o_p_e_n(2), _r_e_a_d(2), _w_r_i_t_e(2), _l_s_e_e_k(2), etc. User functions call these routines to retrieve only the data of interest from a large object. For example, if a large object type called _m_u_g_s_h_o_t existed that stored photographs of faces, then a function called _b_e_a_r_d could be declared on _m_u_g_s_h_o_t data. _B_e_a_r_d 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 buf- fered, or even examined, by the _b_e_a_r_d function. As mentioned above, POSTGRES supports functional indices on large object data. In this example, the results of the _b_e_a_r_d function could be stored in a B-tree index to provide fast searches for people with red beards. 145 INTRODUCTION(LARGE OBJECTS) 01/23/93 UNIX FILES AS LARGE OBJECT ADTS The simplest large object interface supplied with POSTGRES is also the least robust. It does not support transaction protection, crash recovery, or time travel. On the other hand, it can be used on existing data files (such as word-processor files) that must be accessed simul- taneously by the database system and existing application programs. This implementation stores large object data in a UNIX file, and stores only the file name in the database. Importing a large object into the database is as simple as storing the file name in a distinguished "large object name" relation. Interface routines allow the database system to open, seek, read, write, and close these UNIX files by an internal large object identifier. The functions _l_o__f_i_l_e_i_n and _l_o__f_i_l_e_o_u_t convert between UNIX filenames and internal large object identifiers. These functions are POSTGRES registered functions, meaning they can be used directly in Postquel queries as well as from dynamically loaded C functions. If you are defining a simple large object ADT, these functions can be used as your "input" and "output" functions (see _d_e_f_i_n_e _t_y_p_e(commands) and the POSTGRES Manual sections concerning user-defined types for details). The routine char *lo_filein(filename) char *filename; imports a new UNIX file storing large object data into the database sys- tem. This routine stores the filename in a large object naming relation and assigns it a unique large object identifier. The converse routine, char *lo_fileout(object) LargeObject *object; returns the UNIX filename associated with a large object. The file storing the large object must be accessible on the machine on which POSTGRES is running. The data is not copied into the database system, so if the file is later removed, it is unrecoverable. Large objects are accessible from both the POSTGRES backend, using dynamically-loaded functions, and from the front-end, using the LIBPQ interface. These interfaces will be described in detail below. INVERSION LARGE OBJECTS In contrast to UNIX files as large objects, the Inversion large object implementation guarantees transaction protection, crash recovery, and time travel on user large object data. This implementation breaks large objects up into "chunks" and stores the chunks in tuples in the data- base. A B-tree index guarantees fast searches for the correct chunk number when doing random access reads and writes. 146 01/23/93 INTRODUCTION(LARGE OBJECTS) If a transaction that has made changes to an Inversion large object sub- sequently aborts, the changes are backed out in the normal way. Inver- sion large objects are stored in the database, and so are not directly accessible to other programs. Only programs that use the POSTGRES data manager can read and write Inversion large objects. To use Inversion large objects, a new large object should be created using the _L_O_c_r_e_a_t() interface, defined below. Afterwards, the name of the large object can be stored in an ordinary tuple. The next section describes the programmatic interface to both UNIX and Inversion large objects. BACKEND INTERFACE TO LARGE OBJECTS Large object data is accessible from front-end programs linked with the LIBPQ library, and from dynamically-loaded routines that execute in the POSTGRES backend. This section describes access from dynamically loaded C functions. Creating New Large Objects The routine int LOcreat(path, mode, objtype) char *path; int mode; int objtype; creates a new large object. The pathname is a slash-separated list of components, and must be a unique pathname in the POSTGRES large object namespace. There is a vir- tual root directory ("/") in which objects may be placed. The _o_b_j_t_y_p_e parameter can be one of _I_n_v_e_r_s_i_o_n or _U_n_i_x, which are sym- bolic constants defined in .../include/catalog/pg_lobj.h The interpretation of the _m_o_d_e argument depends on the _o_b_j_t_y_p_e selected. For UNIX files, _m_o_d_e is the mode used to protect the file on the UNIX file system. On creation, the file is open for reading and writing. For Inversion large objects, _m_o_d_e is a bitmask describing several dif- ferent attributes of the new object. The symbolic constants listed here are defined in .../include/tmp/libpq-fs.h The access type (read, write, or both) is controlled by OR'ing together the bits INV_READ and INV_WRITE. 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 INV_ARCHIVE bit should be set. The low-order sixteen bits of _m_a_s_k are the storage manager number on 147 INTRODUCTION(LARGE OBJECTS) 01/23/93 which the large object should reside. In the distributed version of POSTGRES, only the magnetic disk storage manager is supported. For users running POSTGRES at UC Berkeley, additional storage managers are available. For sites other than Berkeley, these bits should always be zero. At Berkeley, storage manager zero is magnetic disk, storage manager one is a Sony optical disk jukebox, and storage manager two is main memory. The commands below open large objects of the two types for writing and reading. The Inversion large object is not archived, and is located on magnetic disk: unix_fd = LOcreat("/my_unix_obj", 0600, Unix); inv_fd = LOcreat("/my_inv_obj", INV_READ|INV_WRITE, Inversion); Opening Large Objects Existing large objects may be opened for reading or writing by calling the routine int LOopen(path, mode) char *path; int mode; The _p_a_t_h argument specifies the large object's pathname, and is the same as the pathname used to create the object. The _m_o_d_e argument is inter- preted by the two implementations differently. For UNIX large objects, values should be chosen from the set of mode bits passed to the _o_p_e_n system call; that is, O_CREAT, O_RDONLY, O_WRONLY, O_RDWR, and O_TRUNC. For Inversion large objects, only the bits INV_READ and INV_WRITE have any meaning. To open the two large objects created in the last example, a programmer would issue the commands unix_fd = LOopen("/my_unix_obj", O_RDWR); inv_fd = LOopen("/my_inv_obj", INV_READ|INV_WRITE); If a large object is opened before it has been created, then a new large object is created using the UNIX implementation, and the new object is opened. Seeking on Large Objects The command int LOlseek(fd, offset, whence) int fd; int offset; int whence; 148 01/23/93 INTRODUCTION(LARGE OBJECTS) moves the current location pointer for a large object to the specified position. The _f_d parameter is the file descriptor returned by either _L_O_c_r_e_a_t or _L_O_o_p_e_n. _O_f_f_s_e_t is the byte offset in the large object to which to seek. The only legal value for _w_h_e_n_c_e in the current release of the system is L_SET, as defined in <_s_y_s/_f_i_l_e_s._h>. UNIX large objects allow holes to exist in objects; that is, a program may seek well past the end of the object and write bytes. Intervening blocks will not be created; reading them will return zero-filled blocks. Inversion large objects do not support holes. The following code seeks to byte location 100000 of the example large objects: unix_status = LOlseek(unix_fd, 100000, L_SET); inv_status = LOlseek(inv_fd, 100000, L_SET); On error, _L_O_l_s_e_e_k returns a value less than zero. On success, the new offset is returned. Writing to Large Objects Once a large object has been created, it may be filled by calling int LOwrite(fd, wbuf) int fd; struct varlena *wbuf; Here, _f_d is the file descriptor returned by _L_O_c_r_e_a_t or _L_O_o_p_e_n, and _w_b_u_f describes the data to write. The _v_a_r_l_e_n_a structure in POSTGRES consists of four bytes in which the length of the datum is stored, followed by the data itself. The four length bytes include themselves. For example, to write 1024 bytes of zeroes to the sample large objects: struct varlena *vl; vl = (struct varlena *) palloc(1028); VARSIZE(vl) = 1028; bzero(VARDATA(vl), 1024); nwrite_unix = LOwrite(unix_fd, vl); nwrite_inv = LOwrite(inv_fd, vl); _L_O_w_r_i_t_e returns the number of bytes actually written, or a negative number on error. For Inversion large objects, the entire write is guaranteed to succeed or fail. That is, if the number of bytes written is non-negative, then it equals _V_A_R_S_I_Z_E(vl). The _V_A_R_S_I_Z_E() and _V_A_R_D_A_T_A() macros are declared in the file .../include/tmp/postgres.h 149 INTRODUCTION(LARGE OBJECTS) 01/23/93 Reading from Large Objects Data may be read from large objects by calling the routine struct varlena * LOread(fd, len) int fd; int len; This routine returns the byte count actually read and the data in a varlena structure. For example, struct varlena *unix_vl, *inv_vl; int nread_ux, nread_inv; char *data_ux, *data_inv; unix_vl = LOread(unix_fd, 100); nread_ux = VARSIZE(unix_vl); data_ux = VARDATA(unix_vl); inv_vl = LOread(inv_fd, 100); nread_inv = VARSIZE(inv_vl); data_inv = VARDATA(inv_vl); The returned varlena structures have been allocated by the POSTGRES memory manager _p_a_l_l_o_c, and may be _p_f_r_e_ed when they are no longer needed. Closing a Large Object Once a large object is no longer needed, it may be closed by calling int LOclose(fd) int fd; where _f_d is the file descriptor returned by _L_O_o_p_e_n or _L_O_c_r_e_a_t. On suc- cess, _L_O_c_l_o_s_e returns zero. A negative return value indicates an error. For example, if (LOclose(unix_fd) < 0) /* error */ if (LOclose(inv_fd) < 0) /* error */ LIBPQ LARGE OBJECT INTERFACE Large objects may also be accessed from database client programs that link the LIBPQ library. This library provides a set of routines that support opening, reading, writing, closing, and seeking on large objects. The interface is similar to that provided via the backend, but rather than using varlena structures, a more conventional UNIX-style buffer scheme is used. 150 01/23/93 INTRODUCTION(LARGE OBJECTS) In version 4.1 of POSTGRES, large object operations must be enclosed in a transaction block. This is true even for UNIX large objects, which are not transaction-protected. This is due to a shortcoming in the memory management scheme for large objects, and will be rectified in the future. The end of this section shows a short example program that correctly transaction-protects its file system operations. This section describes the LIBPQ interface in detail. Creating a Large Object The routine int p_creat(path, mode, objtype) char *path; int mode; int objtype; creates a new large object. The _p_a_t_h argument specifies a large-object system pathname. The _o_b_j_t_y_p_e parameter can be one of _I_n_v_e_r_s_i_o_n or _U_n_i_x, which are sym- bolic constants defined in .../include/catalog/pg_lobj.h The interpretation of the _m_o_d_e argument depends on the _o_b_j_t_y_p_e selected. For UNIX files, _m_o_d_e is the mode used to protect the file on the UNIX file system. On creation, the file is open for reading and writing. For Inversion large objects, _m_o_d_e is a bitmask describing several dif- ferent attributes of the new object. The symbolic constants listed here are defined in .../include/tmp/libpq-fs.h The access type (read, write, or both) is controlled by OR'ing together the bits INV_READ and INV_WRITE. 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 INV_ARCHIVE bit should be set. The low-order sixteen bits of _m_a_s_k are the storage manager number on which the large object should reside. For sites other than Berkeley, these bits should always be zero. At Berkeley, storage manager zero is magnetic disk, storage manager one is a Sony optical disk jukebox, and storage manager two is main memory. The commands below open large objects of the two types for writing and reading. The Inversion large object is not archived, and is located on magnetic disk: unix_fd = p_creat("/my_unix_obj", 0600, Unix); 9 inv_fd = p_creat("/my_inv_obj", INV_READ|INV_WRITE, Inversion); 9 151 INTRODUCTION(LARGE OBJECTS) 01/23/93 Opening an Existing Large Object To open an existing large object, call int p_open(path, mode) char *path; int mode; The _p_a_t_h argument specifies the large object pathname for the object to open. The mode bits control whether the object is opened for reading, writing, or both. For UNIX large objects, the appropriate flags are O_CREAT, O_RDONLY, O_WRONLY, O_RDWR, and O_TRUNC. For Inversion large objects, only INV_READ and INV_WRITE are recognized. If a large object is opened before it is created, it is created by default using the UNIX file implementation. Writing Data to a Large Object The routine int p_write(fd, buf, len) int fd; char *buf; int len; writes _l_e_n bytes from _b_u_f to large object _f_d. The _f_d argument must have been returned by a previous _p__c_r_e_a_t or _p__o_p_e_n. The number of bytes actually written is returned. In the event of an error, the return value is negative. Reading Data from a Large Object The routine int p_read(fd, buf, nbytes) int fd; char *buf; int nbytes; reads _n_b_y_t_e_s bytes into buffer _b_u_f from the large object descriptor _f_d. The number of bytes actually read is returned. In the event of an error, the return value is less than zero. Seeking on a Large Object To change the current read or write location on a large object, call int p_lseek(fd, offset, whence) int fd; int offset; 152 01/23/93 INTRODUCTION(LARGE OBJECTS) int whence; This routine moves the current location pointer for the large object described by _f_d to the new location specified by _o_f_f_s_e_t. For this release of POSTGRES, only L_SET is a legal value for _w_h_e_n_c_e. Closing a Large Object A large object may be closed by calling int p_close(fd) int fd; where _f_d is a large object descriptor returned by _p__c_r_e_a_t or _p__o_p_e_n. On success, _p__c_l_o_s_e returns zero. On error, the return value is negative. SAMPLE LARGE OBJECT PROGRAMS The POSTGRES large object implementation serves as the basis for a file system (the "Inversion file system") built on top of the data manager. This file system provides time travel, transaction protection, and fast crash recovery to clients of ordinary file system services. It uses the Inversion large object implementation to provide these services. The programs that comprise the Inversion file system are included in the POSTGRES source distribution, in the directory .../src/bin/fsutils These directories contain a set of programs for manipulating files and directories. These programs are based on the Berkeley Software Distri- bution NET-2 release. These programs are useful in manipulating Inversion files, but they also serve as examples of how to code large object accesses in LIBPQ. All of the programs are LIBPQ clients, and all use the interfaces that have been described in this section. Interested readers should refer to the files in the directory .../src/bin/fsutils for in-depth examples of the use of large objects. Below, a more terse example is provided. This code fragment creates a new large object managed by Inversion, fills it with data from a UNIX file, and closes it. 153 INTRODUCTION(LARGE OBJECTS) 01/23/93 #include "tmp/c.h" #include "tmp/libpq-fe.h" #include "tmp/libpq-fs.h" #include "catalog/pg_lobj.h" #define MYBUFSIZ 1024 main() { int inv_fd; int fd; char *qry_result; char buf[MYBUFSIZ]; int nbytes; int tmp; PQsetdb("mydatabase"); /* large object accesses must be */ /* transaction-protected */ qry_result = PQexec("begin"); if (*qry_result == 'E') /* error */ exit (1); /* open the UNIX file */ fd = open("/my_unix_file", O_RDONLY, 0666); if (fd < 0) /* error */ exit (1); /* open the Inversion file */ inv_fd = p_open("/inv_file", INV_WRITE, Inversion); if (inv_fd < 0) /* error */ exit (1); /* copy the UNIX file to the Inversion */ /* large object */ while ((nbytes = read(fd, buf, MYBUFSIZ)) > 0) { tmp = p_write(inv_fd, buf, nbytes); if (tmp < nbytes) /* error */ exit (1); } (void) close(fd); (void) close(inv_fd); /* commit the transaction */ qry_result = PQexec("end"); if (*qry_result == 'E') /* error */ exit (1); 154 01/23/93 INTRODUCTION(LARGE OBJECTS) /* by here, success */ exit (0); } BUGS Shouldn't have to distinguish between Inversion and UNIX large objects when you open an existing large object. The system knows which imple- mentation was used. The flags argument should be the same in these two cases. SEE ALSO introduction(commands), define function(commands), define type(commands), load(commands). 155 INFORMATION(FILES) 01/23/93 NAME SECTION 8 - FILES OVERVIEW This section describes some of the important files used by POSTGRES. NOTATION ".../" at the front of file names represents the path to the postgres user's home directory. Anything in square brackets ( [" " " " " " and ] ) is optional. Anything in braces ( {" " " " " " and } ) can be repeated 0 or more times. Parentheses ( (" " " " " " and ) ) are used to group boolean expressions. | is the boolean operator OR. BUGS The descriptions of .../data/PG_VERSION, .../data/base/*/PG_VERSION, the temporary sort files, and the database debugging trace files are absent. 156 01/23/93 BKI(FILES) NAME .../src/backend/obj/{local,dbdb}.bki - template scripts DESCRIPTION Backend Interface (BKI) files are scripts that describe the contents of the initial POSTGRES database. This database is constructed during sys- tem installation, by the _i_n_i_t_d_b command. _I_n_i_t_d_b executes the POSTGRES backend with a special set of flags, that cause it to consume the BKI scripts and bootstrap a database. These files are automatically generated from system header files during installation. They are not intended for use by humans, and you do not need to understand their contents in order to use POSTGRES. These files are copied to .../files/{global1,local1_XXX}.bki during system installation. All new user databases will be created by copying the template database that POSTGRES constructs from the BKI files. Thus, a simple way to cus- tomize the template database is to let the POSTGRES initialization script create it for you, and then to run the terminal monitor to make the changes you want. The POSTGRES backend interprets BKI files as described below. This description will be easier to understand if the example in ".../files/global1.bki" is at hand. Commands are composed of a command name followed by space separated arguments. Arguments to a command which begin with a "$" are treated specially. If "$$" are the first two characters, then the first "$" is ignored and the argument is then processed normally. If the "$" is fol- lowed by space, then it is treated as a NULL value. Otherwise, the characters following the "$" are interpreted as the name of a macro causing the argument to be replaced with the macro's value. It is an error for this macro to be undefined. Macros are defined using define macro macro_name = macro_value and are undefined using undefine macro macro_name and redefined using the same syntax as define. Lists of general commands and macro commands follow. 157 BKI(FILES) 01/23/93 GENERAL COMMANDS open classname Open the class called _c_l_a_s_s_n_a_m_e for further manipulation. close [classname] Close the open class called _c_l_a_s_s_n_a_m_e. It is an error if _c_l_a_s_s_n_a_m_e is not already opened. If no _c_l_a_s_s_n_a_m_e is given, then the currently open class is closed. print Print the currently open class. insert [oid=oid_value] ( value1 value2 ... ) Insert a new instance to the open class using _v_a_l_u_e_1, _v_a_l_u_e_2, etc., for its attribute values and _o_i_d__v_a_l_u_e for its OID. If _o_i_d__v_a_l_u_e is not "0", then this value will be used as the instance's object identifier. Otherwise, it is an error. insert ( value1 value2 ... ) As above, but the system generates a unique object identifier. create classname ( name1 = type1, name2 = type2, ... ) Create a class named _c_l_a_s_s_n_a_m_e with the attributes given in parentheses. open ( name1 = type1, name2 = type2,... ) as classname Open a class named _c_l_a_s_s_n_a_m_e for writing but do not record its existence in the system catalogs. (This is primarily to aid in bootstrapping.) destroy classname Destroy the class named _c_l_a_s_s_n_a_m_e. define index index-name on class-name using amname ( opclass attr | function({attr}) ) Create an index named _i_n_d_e_x__n_a_m_e on the class named _c_l_a_s_s_n_a_m_e using the _a_m_n_a_m_e access method. The fields to index are called _n_a_m_e_1, _n_a_m_e_2, etc., and the operator collections to use are _c_o_l_l_e_c_t_i_o_n__1, _c_o_l_l_e_c_t_i_o_n__2, etc., respectively. MACRO COMMANDS define function macro_name as rettype function_name ( args ) Define a function prototype for a function named _m_a_c_r_o__n_a_m_e which has its value of type _r_e_t_t_y_p_e computed from the execution _f_u_n_c_t_i_o_n__n_a_m_e with the arguments _a_r_g_s declared in a C-like manner. define macro macro_name from file filename Define a macro named _m_a_c_n_a_m_e which has its value read from the file called _f_i_l_e_n_a_m_e. 158 01/23/93 BKI(FILES) EXAMPLE The following set of commands will create the "pg_opclass" class con- taining the _i_n_t__o_p_s collection as object _4_2_1, print out the class, and then close it. create pg_opclass (opcname=char16) open pg_opclass insert oid=421 (int_ops) print close pg_opclass SEE ALSO initdb(unix), createdb(unix), createdb(commands), template(files). 159 PAGE(FILES) 01/23/93 NAME page structure - POSTGRES database file default page format DESCRIPTION This section provides an overview of the page format used by POSTGRES classes. User-defined access methods need not use this page format. In the following explanation, a byte is assumed to contain 8 bits. In addition, the term item refers to data which is stored in POSTGRES classes. The first 8 bytes of each page consists of a page header (PageHeader- Data). Within the header, the first three 2-byte integer fields, _l_o_w_e_r, _u_p_p_e_r, and _s_p_e_c_i_a_l, represent byte offsets to the start of unallocated space, to the end of unallocated space, and to the start of "special space." Special space is a region at the end of the page which is allo- cated at page initialization time and which contains information specific to an access method. The last 2 bytes of the page header, _o_p_a_q_u_e, encode the page size and information on the internal fragmenta- tion of the page. Page size is stored in each page because frames in the buffer pool may be subdivided into equal sized pages on a frame by frame basis within a class. The internal fragmentation information is used to aid in determining when page reorganization should occur. Following the page header are item identifiers (ItemIdData). New item identifiers are allocated from the first four bytes of unallocated space. Because an item identifier is never moved until it is freed, its index may be used to indicate the location of an item on a page. In fact, every pointer to an item (ItemPointer) created by POSTGRES con- sists of a frame number and an index of an item identifier. An item identifier contains a byte-offset to the start of an item, its length in bytes, and a set of attribute bits which affect its interpretation. The items, themselves, are stored in space allocated backwards from the end of unallocated space. Usually, the items are not interpreted. How- ever when the item is too long to be placed on a single page or when fragmentation of the item is desired, the item is divided and each piece is handled as distinct items in the following manner. The first through the next to last piece are placed in an item continuation structure (ItemContinuationData). This structure contains _i_t_e_m_P_o_i_n_t_e_r_D_a_t_a which points to the next piece and the piece itself. The last piece is han- dled normally. FILES .../data/... Location of shared (global) database files. .../data/base/... Location of local database files. 160 01/23/93 PAGE(FILES) BUGS The page format may change in the future to provide more efficient access to large objects. This section contains insufficient detail to be of any assistance in writing a new access method. 161 TEMPLATE(FILES) 01/23/93 NAME .../data/files/global1.bki - global database template .../data/files/local1_XXX.bki - local database template .../data/files/template1/* - default database template DESCRIPTION These files contain scripts which direct the construction of databases. Note that the "global1.bki" and "template1_local.bki" files are installed automatically when the POSTGRES super-user runs _i_n_i_t_d_b. These files are copied from .../src/backend/obj/{dbdb,local}.bki The databases which are generated by the template scripts are normal databases. Consequently, you can use the terminal monitor or some other frontend on a template database to simplify the customization task. That is, there is no need to express everything about your desired ini- tial database state using a BKI template script, because the database state can be tuned interactively. The system catalogs consist of classes of two types: global and local. There is one copy of each global class that is shared among all data- bases at a site. Local classes, on the other hand, are not accessible except from their own database. The file .../data/files/global1.bki specifies the process used in the creation of global (shared) classes by _c_r_e_a_t_e_d_b. Similarly, the .../files/local1_XXX.bki files specify the process used in the creation of local (unshared) cata- log classes for the "XXX" template database. "XXX" may be any string of 16 or fewer printable characters. If no template is specified in a _c_r_e_a_t_e_d_b command, then the template in .../files/local1_template1.bki is used. The .bki files are generated from C source code by an inscrutable set of AWK scripts. BUGS POSTGRES Version 4.1 does not permit users to have separate template databases. 162 01/23/93 TEMPLATE(FILES) SEE ALSO bki(files), initdb(unix), createdb(unix). 163 UNFSD_EXPORTS(FILES) 01/23/93 NAME unfsd_exports - NFS file systems being exported by user-level NFS server SYNOPSIS /etc/unfsd_exports DESCRIPTION The file /_e_t_c/_u_n_f_s_d__e_x_p_o_r_t_s describes the file systems which are being exported to nfs clients. It is processed by the _u_s_e_r-_l_e_v_e_l _N_F_S daemon _u_n_f_s_d(8C) when the daemon is started. The file format is similar to that of the SunOS _e_x_p_o_r_t_s(5) file. The file is organized by lines. A # introduces a comment to the end of the line, a \ preceding a new line disables the line break making the entry of long input lines more convenient. Each line consists of a mount point and list of machine names allowed to remote mount the server's file hierarchy at that mount point. A machine name is optionally fol- lowed by a list of mount parameters enclosed in parentheses. These are the parameters that are currently recognized. secure * Reject requests that originate on an internet port >_ IPPORT_RESERVED. insecure Accept requests originating on any port. root_squash Map requests from uid 0 on the client to uid -2 on the server. no_root_squash * Don't map requests from uid 0. ro * Mount file hierarchy read-only. rw + Mount file hierarchy read-write. link_relative * Convert symbolic links starting with a slash into relative links by prepending the necessary number of ../'s to get from the link directory to the file hierarchy root on the server. link_absolute Leave symbolic links starting with a slash as they are. map_identity * Assume the client and server share the same uid/gid space. map_daemon Map local and remote names and numeric ids using a lname/uid map daemon on the client from which the NFS request originated, to map between the client and server uid spaces (see ugidd(8)). ( * indicates defaults, + indicates currently unimplemented features) 164 01/23/93 UNFSD_EXPORTS(FILES) EXAMPLE / snail whelk(map_identity) tusk(root_squash, map_daemon, ro) /usr usage(root_squash, map_daemon, ro) FILES /etc/unfsd_exports SEE ALSO mountd(8C) unfsd(8C) ugidd(8C) BUGS The mount point at the start of each line is currently ignored. Author- ized clients may mount at any point in the server's hierarchy. 165 REFERENCES(MANUAL) 01/23/93 NAME REFERENCES The following technical reports are referred to in this document. For information on ordering technical reports, see the installation notes that accompany the POSTGRES distribution. [ONG90] Ong, L. and Goh, J., "A Unified Framework for Version Modeling Using Production Rules in a Database System," Electronics Research Laboratory, University of Califor- nia, Berkeley, ERL Memo M90/33, April 1990. [ROWE87] Rowe, L. and Stonebraker, M., Proc. 1987 VLDB Conference, Brighton, England, Sept. 1987. [SHAP86] Shapiro, L., ACM-TODS, Sept. 1986. [STON87] Stonebraker, M., Proc. 1987 VLDB Conference, Brighton, England, Sept. 1987. [STON90] 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. [WONG76] Wong, E., ACM-TODS, Sept. 1976. 166 01/23/93 REFERENCES(MANUAL) 167 REFERENCES(MANUAL) 01/23/93 0 02/08/93 CONTENTS(MANUAL) NAME Table of Contents i 99