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; a kernel bug prevents POSTGRES from running under Ultrix 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, Chris Demitriou, 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 Kemnitz, Case Larsen, Jeff Meredith, Michael Olson, Nels Olson, Lay-Peng Ong, Carol Paxson, Spyros Potamianos, Sunita Sarawagi, David Sharnoff, Cimarron Taylor, and Yongdong Wang. 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 INITDB(UNIX) 01/23/93 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). 16 01/23/93 IPCCLEAN(UNIX) 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. 17 MONITOR(UNIX) 01/23/93 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. 18 01/23/93 MONITOR(UNIX) -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. 19 MONITOR(UNIX) 01/23/93 \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). 20 01/23/93 PAGEDOC(UNIX) 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 21 PAGEDOC(UNIX) 01/23/93 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. 22 01/23/93 PCAT(UNIX) 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. 23 PCD(UNIX) 01/23/93 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. 24 01/23/93 PLS(UNIX) 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. 25 PMKDIR(UNIX) 01/23/93 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. 26 01/23/93 PMV(UNIX) 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. 27 POSTGRES(UNIX) 01/23/93 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. 28 01/23/93 POSTGRES(UNIX) 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 29 POSTGRES(UNIX) 01/23/93 doesn't work, you probably need to configure your kernel for shared memory and semaphores as described in the installation notes. 30 01/23/93 POSTMASTER(UNIX) 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 31 POSTMASTER(UNIX) 01/23/93 (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 32 01/23/93 POSTMASTER(UNIX) 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. 33 PPWD(UNIX) 01/23/93 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). 34 01/23/93 PRM(UNIX) 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). 35 PRMDIR(UNIX) 01/23/93 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. 36 01/23/93 S2KINIT(UNIX) 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 . 37 SHMEMDOC(UNIX) 01/23/93 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. 38 01/23/93 SHMEMDOC(UNIX) 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. 39 UGIDD(UNIX) 01/23/93 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. 40 01/23/93 INTRODUCTION(BUILT-INS) 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. 41 INTRODUCTION(BUILT-INS) 01/23/93 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 42 01/23/93 INTRODUCTION(BUILT-INS) 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. 43 99