TTyyppee OOppeerraattoorr PPOOSSTTGGRREESS FFuunnccttiioonn PPrroottoottyyppee OOppeerraattiioonn abstimene(abstime, abs- time) abstime <> bool inequality time timepl(abstime, rel- time) + abs- addition time timemi(abstime, rel- time) - abs- subtraction abstimele(abstime, abs- time) <= bool less or equal ininterval(abstime, tin- ter- val) bool abstime in tin- ter- val? abstimelt(abstime, abs- time) < bool less than abstimeeq(abstime, abs- time) = bool equality abstimege(abstime, abs- time) >= bool greater or equal abstimegt(abstime, abs- time) > bool greater than booleq(bool, bool) bool = bool equality boolne(bool, bool) <> bool inequality box_overlap(box, box) box && bool boxes over- lap box_overleft(box, box) &< bool box A overlaps box B, but does not extend to right of box B box_overright(box, box) &> bool box A over- laps box B, but does not extend to left of box B box_left(box, box) << bool A is left of B box_le(box, box) <= bool area less or equal box_lt(box, box) < bool area less than box_eq(box, box) = bool area equal box_ge(box, box) >= bool area greater or equal box_right(box, box) >> bool A is right of B box_gt(box, box) > bool area greater than box_contained(box, box) @ bool A is con- tained in B box_same(box, box) ~= bool box equal- ity box_contain(box, box) ~ bool A con- tains B charne(char, char) char <> bool inequality charmul(char, char) * bool multiplication charpl(char, char) + bool addition charmi(char, char) - bool subtraction chardiv(char, char) / bool division charle(char, char) <= bool less or equal charlt(char, char) < bool less than chareq(char, char) = bool equality charge(char, char) >= bool greater or equal chargt(char, char) > bool greater than char2ne(char2, char2) char2 <> bool inequality char2regexne(char2, text) !~ bool A does not match reg- ular expres- sion B, case- sen- sitive char2icregexne(char2, text) !~* bool A does not match reg- ular expres- sion B, case- insen- sitive char2le(char2, char2) <= bool less or equal char2lt(char2, char2) < bool less than char2eq(char2, char2) = bool equality char2ge(char2, char2) >= bool greater or equal char2gt(char2, char2) > bool greater than char2regexeq(char2, text) ~ bool A matches reg- ular expres- sion B, case- sen- sitive. char2icregexeq(char2, text) ~* bool A matches reg- ular expres- sion B, case- insensitive. char4ne(char4, char4) char4 <> bool inequality char4regexne(char4, text) !~ bool A does not match reg- ular expres- sion B, case- sen- sitive char4icregexne(char4, text) !~* bool A does not match reg- ular expres- sion B, case- insen- sitive char4le(char4, char4) <= bool less or equal char4lt(char4, char4) < bool less than char4eq(char4, char4) = bool equality char4ge(char4, char4) >= bool greater or equal char4gt(char4, char4) > bool greater than char4regexeq(char4, text) ~ bool A matches reg- ular expres- sion B, case- sen- sitive char4icregexeq(char4, text) ~* bool A matches reg- ular expres- sion B, case- insen- sitive char8ne(char8, char8) char8 <> bool inequality char8regexne(char8, text) !~ bool A does not match reg- ular expres- sion B, case sen- sitive char8icregexne(char8, text) !~* bool A does not match reg- ular expres- sion B, case insen- sitive char8le(char8, char8) <= bool less or equal char8lt(char8, char8) < bool less than char8eq(char8, char8) = bool equality char8ge(char8, char8) >= bool greater or equal char8gt(char8, char8) > bool greater than char8regexeq(char8, text) ~ bool A matches reg- ular expres- sion B, case sen- sitive char8icregexeq(char8, text) ~* bool A matches reg- ular expres- sion B, case insen- sitive char16ne(char16, char16) char16 <> bool inequality char16regexne(char16, text) !~ bool A does not match reg- ular expres- sion B, case sen- sitive char16icregexne(char16, text) !~* bool A does not match reg- ular expres- sion B, case insen- sitive char16le(char16, char16) <= bool less or equal char16lt(char16, char16) < bool less than char16eq(char16, char16) = bool equality char16ge(char16, char16) >= bool greater or equal char16gt(char16, char16) > bool greater than char16regexeq(char16, text) ~ bool A matches reg- ular expres- sion B, case sen- sitive char16regexeq(char16, text) ~* bool A matches reg- ular expres- sion B, case insen- sitive float4ne(float4, float4) float4 <> bool inequality float4mul(float4, float4) * float4 multiplication float4pl(float4, float4) + float4 addition float4mi(float4, float4) - float4 subtraction float4div(float4, float4) / float4 division float4le(float4, float4) <= bool less or equal float4lt(float4, float4) < bool less than float4eq(float4, float4) = bool equality float4ge(float4, float4) >= bool greater or equal float4gt(float4, float4) > bool greater than float8ne(float8, float8) float8 <> bool inequality float8mul(float8, float8) * float8 multiplication float8pl(float8, float8) + float8 addition float8mi(float8, float8) - float8 subtraction float8div(float8, float8) / float8 division float8le(float8, float8) <= bool less or equal float8lt(float8, float8) < bool less than1 float8eq(float8, float8) = bool equality float8ge(float8, float8) >= bool greater or equal float8gt(float8, float8) > bool greater than dpow(float8, float8) ^ float8 exponentiation int2ne(int2, int2) int2 <> bool inequality int24ne(int2, int4) <> int4 inequality int2mod(int2, int2) % int2 modulus int24mod(int2, int4) % int4 modulus int2mul(int2, int2) * int2 multiplication int24mul(int2, int4) * int4 multiplication int2pl(int2, int2) + int2 addition int24pl(int2, int4) + int4 addition int2mi(int2, int2) - int2 subtraction int24mi(int2, int4) - int4 subtraction int2div(int2, int2) / int2 division int24div(int2, int4) / int4 division int2le(int2, int2) <= bool less or equal int24le(int2, int4) <= bool less or equal int2lt(int2, int2) < bool less than int24lt(int2, int4) < bool less than int2eq(int2, int2) = bool equality int24eq(int2, int4) = bool equality int2ge(int2, int2) >= bool greater or equal int24ge(int2, int4) >= bool greater or equal int2gt(int2, int2) > bool greater than int24gt(int2, int4) > bool greater than int4ne(int4, int4) int4 <> bool inequality int42ne(int4, int2) <> bool inequality int42mod(int4, int2) % int4 modulus int4mod(int4, int4) % int4 modulus int42mul(int4, int2) * int4 multiplication int4mul(int4, int4) * int4 multiplication int42pl(int4, int2) + int4 addition int4pl(int4, int4) + int4 addition int42mi(int4, int2) - int4 subtraction int4mi(int4, int4) - int4 subtraction int42div(int4, int2) / int4 division int4div(int4, int4) / int4 division int4le(int4, int4) <= bool less or equal int42le(int4, int2) <= bool less or equal int4lt(int4, int4) < bool less than int42lt(int4, int2) < bool less than int4eq(int4, int4) = bool equality int42eq(int4, int2) = bool equality int4eqoid(int4, oid) = bool equality int4ge(int4, int4) >= bool greater or equal int42ge(int4, int2) >= bool greater or equal int4gt(int4, int4) > bool greater than int42lt(int4, int2) > bool less than namene(name, name) name <> bool inequality nameregexne(name, text) !~ bool A does not match reg- ular expres- sion B, case sen- sitive nameicregexne(name, text) !~* bool A does not match reg- ular expres- sion B, case insen- sitive namele(name, name) <= bool less or equal namelt(name, name) < bool less than nameeq(name, name) = bool equality namege(name, name) >= bool greater or equal namegt(name, name) > bool greater than nameregexeq(name, text) ~ bool A matches reg- ular expres- sion B, case sen- sitive nameregexeq(name, text) ~* bool A matches reg- ular expres- sion B, case insen- sitive oidne(oid, oid) oid <> bool inequality oidne(oid, reg- proc) <> bool inequality oidle(oid, oid) <= bool less or equal oidlt(oid, oid) < bool less than oideq(oid, oid) = bool equality oideqint4(oid, int4) = bool equality oideq(oid, reg- proc) = bool equality oidge(oid, oid) >= bool greater or equal oidgt(oid, oid) > bool greater than oidnamene(oidname, oid- name) oidname <> bool inequality oidnamelt(oidname, oid- name) < bool less than oidnamele(oidname, oid- name) <= bool less or equal oidnameeq(oidname, oid- name) = bool equality oidnamegt(oidname, oid- name) > bool greater than oidnamege(oidname, oid- name) >= bool greater or equal oidint2ne(oidint2, oidint2) oidint2 <> bool inequality oidint2lt(oidint2, oidint2) < bool less than oidint2le(oidint2, oidint2) <= bool less or equal oidint2eq(oidint2, oidint2) = bool equality oidint2gt(oidint2, oidint2) > bool greater than oidint2ge(oidint2, oidint2) >= bool greater or equal oidint4ne(oidint4, oidint4) oidint4 <> bool inequality oidint4lt(oidint4, oidint4) < bool less than oidint4le(oidint4, oidint4) <= bool less or equal oidint4eq(oidint4, oidint4) = bool equality oidint4gt(oidint4, oidint4) > bool greater than oidint4ge(oidint4, oidint4) >= bool greater or equal point_left(point, point) point !< bool A is left of B point_right(point, point) !> bool A is right of B point_above(point, point) !^ bool A is above B point_below(point, point) !| bool A is below B point_eq(point, point) =|= bool equality on_pb(point, box) ===> bool point inside box on_ppath(point, path) ===` bool point on path pointdist(point, point) <===> int4 distance between points poly_overlap(polygon, polygon) polygon && bool polygons over- lap poly_overleft(polygon, polygon) &< bool A over- laps B but does not extend to right of B poly_overright(polygon, polygon) &> bool A over- laps B but does not extend to left of B poly_left(polygon, polygon) << bool A is left of B poly_right(polygon, polygon) >> bool A is right of B poly_contained(polygon, polygon) @ bool A is con- tained by B poly_same(polygon, polygon) ~= bool equality poly_contain(polygon, polygon) ~ bool A con- tains B reltimene(reltime, rel- time) reltime <> bool inequality reltimele(reltime, rel- time) <= bool less or equal reltimelt(reltime, rel- time) < bool less than reltimeeq(reltime, rel- time) = bool equality reltimege(reltime, rel- time) >= bool greater or equal reltimegt(reltime, rel- time) > bool greater than textne(text, text) text <> bool inequality textregexne(text, text) !~ bool A does not con- tain the reg- ular expres- sion B, case sen- sitive texticregexne(text, text) !~* bool A does not con- tain the reg- ular expres- sion B, case insen- sitive text_le(text, text) <= bool less or equal text_lt(text, text) < bool less than texteq(text, text) = bool equality text_ge(text, text) >= bool greater or equal text_gt(text, text) > bool greater than textregexeq(text, text) ~ bool A con- tains the reg- ular expres- sion B. case sensitive texticregexeq(text, text) ~* bool A con- tains the reg- ular expres- sion B. case insen- sitive intervallenne(tinterval, rel- time) tinterval #<> bool interval length not equal to rel- time. intervallenle(tinterval, rel- time) #<= bool interval length less or equal rel- time intervallenlt(tinterval, rel- time) #< bool interval length less than rel- time intervalleneq(tinterval, rel- time) #= bool interval length equal to rel- time intervallenge(tinterval, rel- time) #>= bool interval length greater or equal rel- time intervallengt(tinterval, rel- time) #> bool interval length greater than rel- time intervalov(tinterval, tin- ter- val) && bool intervals over- lap intervalct(tinterval, tin- ter- val) << bool A con- tains B intervaleq(tinterval, tin- ter- val) = bool equality ter- val mktinterval(abstime, abs- time) <> tin- interval bounded by two abs- times FFuunncc-- ttiioonn PPrroo-- ttoottyyppee TTyyppee OOppeerraattoorr PPOOSSTTGGRREESS OOppeerraattiioonn box_center(box) box @@ point center of box float4abs(float4) float4 @ float4 absolute value float8abs(float8) float8 @ float8 absolute value dtrunc(float8) % float8 truncate to integer dsqrt(float8) |/ float8 square root dcbrt(float8) ||/ float8 cube root dexp(float8) : float8 exponential func- tion dlog1(float8) ; float8 natural log- arithm intervalstart(tinterval) tinterval | abstime start of inter- val FFuunncc-- ttiioonn PPrroo-- ttoottyyppee TTyyppee OOppeerraattoorr PPOOSSTTGGRREESS OOppeerraattiioonn dround(float8) float8 % float8 round to nearest integer INTRODUCTION(POSTGRES95) INTRODUCTION(POSTGRES95) NAME SECTION 1 - INTRODUCTION OVERVIEW This document is the reference manual for the POSTGRES95 database manage- ment system under development at the University of California at Berkeley. The POSTGRES95 project, led by Professor Michael Stonebraker, has been sponsored by the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and Digital Equipment Corporation This reference manual describes Version 1.01 of POSTGRES95. It assumes pro- ficiency in C, C++, and SQL programming. POSTGRES95 is copyrighted by the Regents of the University of California. However, the source code is freely modifiable and distributable. Information on obtaining POSTGRES95 source code is available from the POSTGRES95 home page at the following URL: http://s2k-ftp.cs.berkeley.edu:8000/postgres95 POSTGRES95 is based on the last official release of POSTGRES, Version 4.2 and provides enhanced performance and managibility. The code has been trimmed by 25% and runs 30-50% faster than POSTGRES v.4.2 on the Wisconsin Benchmark. More significantly POSTGRES95 no longer uses POSTQUEL query language but rather employs standard query language, SQL. For a more detailed description of specific changes to the code please consult the POSTGRES95 User's Manual. POSTGRES95 has been complied and tested on allbox center; c c c l l l. Architecture Processor Operating System = Intel x86 Pentium Linux 1.3.42 DECstation 3000 Alpha AXP OSF/2.0, 3.0 DECstation 5000 MIPS ULTRIX 4.4 Sun4 SPARC SunOS 4.1.3, 4.1.3_UI, Solaris 2.4 HP 9000/700 & 800 PA-RISC HP-UX 9.00, 9.01, 9.03 Application developers should note that this manual contains only the specification for the low-level call-oriented application program inter- face, LIBPQ. A companion volume, the POSTGRES95 User's Manual, contains tutorial examples of the ways in which the system can be extended. The remainder of this reference manual is structured as follows. Section 2 (UNIX), discusses the POSTGRES95 capabilities that are available directly from the operating system. Section 3 (BUILT-INS) describes POSTGRES95 internal data types, functions, and operators. Section 4 (COMMANDS) describes SQL, the language by which a user interacts with a POSTGRES95 database. Then, Section 5 (LIBPQ) describes a library of low level rou- tines through which a user can formulate SQL queries from a C program and get appropriate return information back to his program. Section 6 (LARGE OBJECTS) describes the internal POSTGRES95 interface for accessing large objects. Section 7 (SYSTEM CATALOGS) gives a brief explanation of the tables used internally by POSTGRES95. The reference concludes with Section 8 (FILES), a collection of file format descriptions for files used by POSTGRES95. ACKNOWLEDGEMENTS The primary authors of POSTGRES95 are Jolly Chen and Andrew Yu. Previous versions of POSTGRES was constructed by a team of undergraduate, graduate, and staff programmers: Jeff Anton, Paul Aoki, James Bell, Jennifer Caetta, Philip Chang, Jolly Chen, Ron Choi, 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, Marcel Kornacker, Case Larsen, Boris Livshitz, Jeff Meredith, Ginger Ogle, Michael Olson, Nels Olson, Lay-Peng Ong, Carol Paxson, Avi Pfeffer, Spyros Potamianos, Sunita Sarawagi, David Muir Sharnoff, Mark Sullivan, Cimarron Taylor, Marc Teitelbaum, Yongdong Wang, Kristin Wright and Andrew Yu. This reference was collectively written by the above implementation team, assisted by Claire Mosher and Dorothy Moore. LEGAL NOTICES POSTGRES95 is copyrighted by the Regents of the University of California. Permission to use, copy, modify, and distribute this software and its docu- mentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice, this paragraph, and the following two paragraphs appear in all copies. IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTA- TION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSI- BILITY OF SUCH DAMAGE. THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUD- ING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FIT- NESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PRO- VIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. UNIX is a trademark of X/Open, Ltd. Sun4, SPARC, SunOS and Solaris are trademarks of Sun Microsystems, Inc. DEC, DECstation, Alpha AXP and ULTRIX are trademarks of Digital Equipment Corp. PA-RISC and HP-UX are trademarks of Hewlett-Packard Co. RS/6000, POWER and AIX are trademarks of Interna- tional Business Machines Corp. OSF/1 is a trademark of the Open Systems Foundation. NeXTSTEP is a trademark of NeXT Computer, Inc. MIPS and IRIX are trademarks of Silicon Graphics, Inc. INTRODUCTION(UNIX) INTRODUCTION(UNIX) NAME SECTION 2 - UNIX COMMANDS (UNIX) OVERVIEW This section outlines the interaction between POSTGRES95 and the operating system. In particular, this section describes the POSTGRES95 support pro- grams that are executable as UNIX commands. TERMINOLOGY In the following documentation, the term _s_i_t_e may be interpreted as the host machine on which POSTGRES95 is installed. Since it is possible to install more than one set of POSTGRES95 databases on a single host, this term more precisely denotes any particular set of installed POSTGRES95 binaries and databases. The _P_O_S_T_G_R_E_S_9_5 _s_u_p_e_r-_u_s_e_r is the user named "postgres" who owns the POSTGRES95 binaries and database files. As the database super-user, all protection mechanisms may be bypassed and any data accessed arbitrarily. In addition, the POSTGRES95 super-user is allowed to execute some support programs which are generally not available to all users. Note that the POSTGRES95 super-user is _n_o_t the same as the UNIX super-user, _r_o_o_t, and should have a non-zero userid for security reasons. 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 POSTGRES95 to enforce a security policy for a site. The DBA can add new users by the method described below and maintain a set of tem- plate 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 POSTGRES95 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 back- end processes. The _p_o_s_t_m_a_s_t_e_r can take several command-line arguments to tune its behavior. However, 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_9_5 _b_a_c_k_e_n_d (the actual executable program called "postgres") may be executed directly from the user shell by the POSTGRES95 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, there- fore this is not recommended in a multiuser site. NOTATION ".../" at the front of a file name is used to represent the path to the POSTGRES95 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 . USING POSTGRES95 FROM UNIX All POSTGRES95 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 POSTGRES95 user. The instance specifies a set of POSTGRES95 privileges, such as the ability to act as POSTGRES95 super-user, the ability to create/destroy databases, and the ability to update the system catalogs. A UNIX user cannot do anything with POSTGRES95 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 POSTGRES95 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 (effective) user-id before performing a _s_e_t_u_i_d(3) to the user-id of user "postgres". The effective user-id is used as the basis for access control checks. No other authentication is conducted. From the network If the POSTGRES95 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 available to anyone. However, POSTGRES95 offers optional host-based authentication where only access from certain hosts are allowed. Of course, host-based authentication is not fool-proof in Unix, either. It is possible for determined intruders to also masquerade the origination host. Those security issues are beyond the scope of POSTGRES95. If greater security is desired, POSTGRES95 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, _p_s_q_l and the _l_i_b_p_q library 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 POSTGRES95, see the appendix below. ACCESS CONTROL POSTGRES95 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 exceptions: manual system catalog updates are not permitted if the user does not have "pg_user.usecatupd" set, and destruction of system catalogs (or modifica- tion of their schemas) is never allowed. Access Privilege The use of access privilege to limit reading, writing and setting of rules on classes is covered in _g_r_a_n_t/_r_e_v_o_k_e(commands). Class removal and schema modification Commands that destroy or modify the structure of an existing class, such as _a_l_t_e_r, _d_r_o_p _t_a_b_l_e, and _d_r_o_p _i_n_d_e_x, only operate for the owner of the class. As mentioned above, these operations are nneevveerr permitted on system cata- logs. 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 ttrroojjaann hhoorrssee others with relative impunity. The only real protec- tion is tight control over who can define functions (e.g., write to rela- tions with SQL 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 SQL 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 possi- ble for users to change the server's internal data structures from inside of trusted functions. Hence, among many other things, such functions can circumvent any system access controls. This is an inherent problem with user-defined C functions. Rules Like SQL functions, rules always run with the identity and permissions of the user who invoked the backend server. SEE ALSO postmaster(unix), alter(commands), insert(commands), grant/revoke(commands), copy(commands), create(commands), delete(commands), drop table(commands), drop index(commands), drop rule(commands), update(commands), select(commands), kerberos(1), kinit(1), kerberos(3) CAVEATS There are no plans to explicitly support encrypted data inside of POSTGRES95 (though there is nothing to prevent users from encrypting data within user-defined functions). There are no plans to explicitly support encrypted network connections, either, pending a total rewrite of the frontend/backend protocol. User names, group names and associated system identifiers (e.g., the con- tents of "pg_user.usesysid") are assumed to be unique throughout a data- base. Unpredictable results may occur if they are not. APPENDIX: USING KERBEROS Availability The _K_e_r_b_e_r_o_s authentication system is not distributed with POSTGRES95, 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 vendor provides a ver- sion, 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 regu- lations. Any additional inquiries should be directed to your vendor or MIT Project Athena ("info-kerberos@ATHENA.MIT.EDU"). Note that FAQLs (Frequently-Asked Questions Lists) are periodically posted to the _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". POSTGRES95 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 POSTGRES95 expects to find the associated libraries, header files and its own server key file. After compilation is complete, POSTGRES95 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, POSTGRES95 should operate in all ways as a nor- mal _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/POSTGRES95 user name in the first component; (2) the POSTGRES95 service is assumed to be have two components, the service name and a hostname, canonicalized as in Version 4 (i.e., all domain suf- fixes 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. CLEARDBDIR(UNIX) CLEARDBDIR(UNIX) NAME cleardbdir - completely destroys all database files SYNOPSIS cclleeaarrddbbddiirr DESCRIPTION _c_l_e_a_r_d_b_d_i_r destroys all the database files. It is used only by the POSTGRES95 super-user before re-initializing the entire installation for a particular site. Normal database users should never use this command. The POSTGRES95 super-user should ensure the _p_o_s_t_m_a_s_t_e_r process is not run- ning before running cleardbdir. SEE ALSO initdb(unix) CREATEDB(UNIX) CREATEDB(UNIX) NAME createdb - create a database SYNOPSIS ccrreeaatteeddbb [--aa system] [--hh host] [--pp 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 POSTGRES95 super-user, who can destroy it. _C_r_e_a_t_e_d_b is a shell script that invokes the POSTGRES95 _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 POSTGRES95 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: --aa 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. --hh host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is run- ning. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). --pp 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 5432, or the value of the PGPORT environment variable (if set). EXAMPLES # create 5432 demo database createdb demo # create the demo database using the postmaster on host eden, # port using the Kerberos authentication system. createdb -a kerberos -p 5432 -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. SEE ALSO createdb(commands), destroydb(unix), initdb(unix), monitor(unix), postmaster(unix). DIAGNOSTICS EErrrroorr:: FFaaiilleedd ttoo ccoonnnneecctt ttoo bbaacckkeenndd ((hhoosstt==_x_x_x,, ppoorrtt==_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 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 specified the proper port. If your site uses an authentication system, ensure that you have obtained the required authentication credentials. uusseerr uusseerrnnaammee iiss nnoott You do not have a valid entry in the relation "pg_user" and cannot do anything with POSTGRES95 at all; contact your POSTGRES95 site adminis- trator. uusseerr uusseerrnnaammee iiss nnoott You do not have permission to create new databases; contact your POSTGRES95 site administrator. _d_b_n_a_m_e aallrreeaaddyy eexxiissttss The database already exists. ddaattaabbaassee ccrreeaattiioonn ffaaiilleedd oonn _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 POSTGRES95 site administrator has properly installed POSTGRES95 and initialized the site with _i_n_i_t_d_b. CREATEUSER(UNIX) CREATEUSER(UNIX) NAME createuser - create a POSTGRES95 user SYNOPSIS ccrreeaatteeuusseerr [--aa system] [--hh host] [--pp port] [username] DESCRIPTION _C_r_e_a_t_e_u_s_e_r creates a new POSTGRES95 user. Only users with "usesuper" set in the "pg_user" class can create new POSTGRES95 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 POSTGRES95 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 POSTGRES95 users. _C_r_e_a_t_e_u_s_e_r understands the following command-line options: --aa 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. --hh host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is run- ning. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). --pp 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 5432, 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 ques- tions. The new users's login name (if not given on the command line) and user-id must be specified. (Note that the POSTGRES95 user-id must be the same as the user's UNIX user-id.) In addition, you must describe the secu- rity capabilities of the new user. Specifically, you will be asked whether the new user should be able to act as POSTGRES95 super-user, create new databases and update the system catalogs manually. SEE ALSO destroyuser(unix), monitor(unix), postmaster(unix). DIAGNOSTICS EErrrroorr:: FFaaiilleedd ttoo ccoonnnneecctt ttoo bbaacckkeenndd ((hhoosstt==_x_x_x,, ppoorrtt==_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 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 specified the proper port. If your site uses an authentication system, ensure that you have obtained the required authentication credentials. uusseerr uusseerrnnaammee iiss nnoott You do not have a valid entry in the relation "pg_user" and cannot do anything with POSTGRES95 at all; contact your POSTGRES95 site adminis- trator. _u_s_e_r_n_a_m_e ccaannnnoott ccrreeaattee uusseerrss.. You do not have permission to create new users; contact your POSTGRES95 site administrator. uusseerr uusseerrnnaammee aallrreeaaddyy eexxiissttss The user to be added already has an entry in the "pg_user" class. ddaattaabbaassee aacccceessss ffaaiilleedd An internal error occurred in _m_o_n_i_t_o_r or the backend server. Ensure that your POSTGRES95 site administrator has properly installed POSTGRES95 and initialized the site with _i_n_i_t_d_b. BUGS POSTGRES95 user-ids and user names should not have anything to do with the constraints of UNIX. DESTROYDB(UNIX) DESTROYDB(UNIX) NAME destroydb - destroy an existing database SYNOPSIS ddeessttrrooyyddbb [--aa system] [--hh host] [--pp 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 data- base 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 pro- cess must be running on the database server host before _d_e_s_t_r_o_y_d_b is exe- cuted. 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 des- troyed. All references to the database are removed, including the direc- tory 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: --aa 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. --hh host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is run- ning. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). --pp 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 5432, or the value of the PGPORT environment variable (if set). EXAMPLES # destroy the demo database destroydb demo # destroy 5432 demo database using the postmaster on host eden, # port using the Kerberos authentication system. destroydb -a kerberos -p 5432 -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. SEE ALSO destroydb(commands), createdb(unix), initdb(unix), monitor(unix). postmaster(unix). DIAGNOSTICS EErrrroorr:: FFaaiilleedd ttoo ccoonnnneecctt ttoo bbaacckkeenndd ((hhoosstt==_x_x_x,, ppoorrtt==_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 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 specified the proper port. If your site uses an authentication system, ensure that you have obtained the required authentication credentials. uusseerr uusseerrnnaammee iiss nnoott You do not have a valid entry in the relation "pg_user" and cannot do anything with POSTGRES95 at all; contact your POSTGRES95 site adminis- trator. uusseerr uusseerrnnaammee iiss nnoott You do not have permission to destroy databases; contact your POSTGRES95 site administrator. ddaattaabbaassee dbname does noteexxiisstt"" The database to be removed does not have an entry in the "pg_database" class. ddaattaabbaassee ddbbnnaammee iiss nnoott You are not DBA for the specified database. ddaattaabbaassee ddeessttrrooyy ffaaiilleedd oonn _d_b_n_a_m_e An internal error occurred in _m_o_n_i_t_o_r or the backend server. Contact your POSTGRES95 site administrator to ensure that ensure that the files and database entries associated with the database are completely removed. DESTROYUSER(UNIX) DESTROYUSER(UNIX) NAME destroyuser - destroy a POSTGRES95 user and associated databases SYNOPSIS ddeessttrrooyyuusseerr [--aa system] [--hh host] [--pp port] [username] DESCRIPTION _D_e_s_t_r_o_y_u_s_e_r destroys an existing POSTGRES95 user and the databases for which that user is database administrator. Only users with "usesuper" set in the "pg_user" class can destroy new POSTGRES95 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 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 POSTGRES95 user to be destroyed. (The invoker will be prompted for a name if none is speci- fied on the command line.) _D_e_s_t_r_o_y_u_s_e_r understands the following command-line options: --aa 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. --hh host Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is run- ning. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). --pp 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 5432, 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 EErrrroorr:: FFaaiilleedd ttoo ccoonnnneecctt ttoo bbaacckkeenndd ((hhoosstt==_x_x_x,, ppoorrtt==_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 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 credentials. uusseerr uusseerrnnaammee iiss nnoott You do not have a valid entry in the relation "pg_user" and cannot do anything with POSTGRES95 at all; contact your POSTGRES95 site adminis- trator. _u_s_e_r_n_a_m_e ccaannnnoott ddeelleettee uusseerrss.. You do not have permission to delete users; contact your POSTGRES95 site administrator. uusseerr uusseerrnnaammee ddooeess nnoott The user to be removed does not have an entry in the "pg_user" class. ddaattaabbaassee aacccceessss ffaaiilleedd ddeessttrrooyyddbb oonn _d_b_n_a_m_e ffaaiilleedd -- eexxiittiinngg ddeelleettee ooff uusseerr _u_s_e_r_n_a_m_e wwaass UUNNSSUUCCCCEESSSSFFUULL An internal error occurred in _m_o_n_i_t_o_r or the backend server. Contact your POSTGRES95 site administrator to ensure that the files and data- base entries associated with the user and his/her associated databases are completely removed. INITDB(UNIX) INITDB(UNIX) NAME initdb - initalize the database templates and primary directories SYNOPSIS iinniittddbb [--vv ] [--dd ] [--nn ] 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. The template database is then vvaaccuuuummed. _I_n_i_t_d_b is a shell script that invokes the backend server directly. Hence, it must be executed by the POSTGRES95 super-user. _I_n_i_t_d_b understands the following command-line options: --vv Produce verbose output, printing messages stating where the direc- tories are being created, etc. --dd Print debugging output from the backend server. This option generates a tremendous amount of information. This option also turns off the final vacuuming step. --nn 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, gen- erated and installed by the initial compilation process. SEE ALSO createdb(unix), vacuum(commands), bki(files), template(files). IPCCLEAN(UNIX) IPCCLEAN(UNIX) NAME ipcclean - clean up shared memory and semaphores from aborted backends SYNOPSIS iippcccclleeaann DESCRIPTION _I_p_c_c_l_e_a_n cleans up shared memory and semaphore space from aborted backends 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 mes- sages such as sseemmggeett:: NNoo ssppaaccee lleefftt oonn ddeevviiccee are encountered 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. The script makes assumption about the format of output of the iippccss utility which may not be true across different operating systems. Therefore, it may not work on your particular OS. MONITOR(UNIX) MONITOR(UNIX) NAME monitor - run the interactive terminal monitor SYNOPSIS mmoonniittoorr [--NN ] [--QQ ] [--TT ] [--aa system] [--cc query] [--dd path] [--hh hostname] [--pp port] [--qq ] [--tt tty_device] [dbname] DESCRIPTION The interactive terminal monitor is a simple frontend to POSTGRES95 retained for backwards compatiblity. Users are encouraged to the use the _p_s_q_l interface instead. _m_o_n_i_t_o_r enables you to formulate, edit and review queries before issuing them to POSTGRES95. If changes must be made, a UNIX editor may be called to edit the qquueerryy bbuuffffeerr managed by the terminal monitor. The editor used is determined by the value of the EDITOR environment variable. If EDITOR is not set, then vvii 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 exe- cuted. 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: --NN Specifies that query results will be dumped to the screen without any attempt at formatting. This is useful in with the --cc option in shell scripts. --QQ Produces extremely unverbose output. This is useful with the --cc option in shell scripts. --TT Specifies that attribute names will not be printed. This is useful with the --cc option in shell scripts. --aa 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. --cc 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 conjunction with the --NN and --TT options. Examples of shell scripts in the POSTGRES95 distribution using _m_o_n_i_t_o_r --cc 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, and _d_e_s_t_r_o_y_u_s_e_r, --dd 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. --hh hostname Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is run- ning. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). --pp 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 5432, or the value of the PGPORT environment variable (if set). --qq Specifies that the monitor should do its work quietly. By default, it prints welcome and exit messages and the queries it sends to the back- end. If this option is used, none of this happens. --tt 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. --ss parses after each query (enables "single step" mode). --SS Turns off sending query when ";" is encountered. 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. The terminal monitor displays two kinds of messages: ggoo 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 terminal monitor is waiting for input. TERMINAL MONITOR COMMANDS \\ee Enter the editor to edit the query buffer. \\gg Submit query buffer to POSTGRES95 for execution. \\hh Get on-line help. \\ii _f_i_l_e_n_a_m_e Include the file _f_i_l_e_n_a_m_e into the query buffer. \\pp Print the current contents of the query buffer. \\qq Exit from the terminal monitor. \\rr Reset (clear) the query buffer. \\ss Escape to a UNIX subshell. To return to the terminal monitor, type "exit" at the shell prompt. \\tt Print the current time. \\ww _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. \\;; Produce a single semi-colon at the current location in query buffer. ENVIRONMENT VARIABLES You may set any of the following environment variables to avoid specifying command-line options: hostname: PGHOST port: PGPORT tty: PGTTY options: PGOPTION realm: PGREALM If PGOPTION is specified, then the options it contains are parsed bbeeffoorree 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, POSTGRES95 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 information 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 --cc 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). BUGS Does not poll for asynchronous notification events generated by _l_i_s_t_e_n(commands) and _n_o_t_i_f_y(commands). Escapes (backslash characters) cannot be commented out. SEE ALSO psql(unix) PG_DUMP(UNIX) PG_DUMP(UNIX) NAME pg_dump - dumps out a POSTGRES95 database into a script file SYNOPSIS ppgg__dduummpp [--ff filename ] [--HH hostname ] [--pp port] [--vv ] [--SS help] dbname DESCRIPTION _p_g__d_u_m_p is a utility for dumping out a POSTGRES95 database into a script file containing query commands. The script files are in a ASCII format and can be used to reconstruct the database, even on other machines and other architectures. _p_g__d_u_m_p will produce the queries necessary to re-generate all user-defined types, functions, tables, indices, aggregates, and opera- tors. In addition, all the data is copied out in ASCII format so that it can be readily copied in again, as well, as imported into tools for textual editing. _p_g__d_u_m_p is useful for dumping out the contents of a database to move from one postgres95 installation to another. After running _p_g__d_u_m_p , one should examine the output script file for any warnings, especially in light of the limitations listed below. CAVEATS AND LIMITATIONS _p_g__d_u_m_p has a few limitations. The limitations mostly stem from difficulty in extracting certain meta-information from the system catalogs. rruulleess aanndd vviieewwss pg_dump does not understand user-defined rules and views and will fail to dump them properly. (This is due to the fact that rules are stored as plans in the catalogs and not textually) ppaarrttiiaall iinnddiicceess pg_dump does not understand partial indices. (The reason is the same as above. Partial index predicates are stored as plans) llaarrggee oobbjjeeccttss pg_dump does not handle large objects. Large objects are ignored and must be dealt with manually. ooiidd pprreesseerrvvaattiioonn pg_dump does not preserve oid's while dumping. If you have stored oid's explicitly in tables in user-defined attributes, and are using them as keys, then the output scripts will not regenerate your data- base correctly. SEE ALSO copy(commands) POSTGRES95(UNIX) POSTGRES95(UNIX) NAME postgres - the POSTGRES95 backend server SYNOPSIS ppoossttggrreess [--BB n_buffers] [--EE ] [--PP filedes] [--QQ ] [--dd debug_level] [--oo output_file] [--ss ] [dbname] DESCRIPTION The POSTGRES95 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 POSTGRES95 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 variable. The _p_o_s_t_g_r_e_s server understands the following command-line options: --BB 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 run- ning standalone, this specifies the number of buffers to allocate. This value defaults to 64. --EE Echo all queries. --PP 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 nnoott useful for interactive use. --QQ Specifies "quiet" mode. --dd debug_level Turns on debugging at the numeric level _d_e_b_u_g__l_e_v_e_l. Turning on debugging will cause query parse trees and query plans to be displayed. --oo output_file Sends all debugging and error output to _o_u_t_p_u_t__f_i_l_e. If the backend 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). --ss 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. DEPRECATED COMMAND OPTIONS There are several other options that may be specified, used mainly for debugging purposes. These are listed here only for the use by POSTGRES95 system developers. UUssee ooff aannyy ooff tthheessee ooppttiioonnss iiss hhiigghhllyy ddiissccoouurraaggeedd. Furthermore, any of these options may disappear or change at any time. --AAn|r|b|Q_n|X_n This option generates a tremendous amount of output. --LL Turns off the locking system. --NN Disables use of newline as a query delimiter. --SS 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. --bb 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 POSTGRES95 to run out of memory. --ff Forbids the use of particular scan and join methods: _s and _i disable sequential and index scans respectively, while _n, _m and _h disable nested-loop, merge and hash joins respectively. This is another feature that may not necessarily produce executable plans. --pp 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. --ttpa[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 --ss. SEE ALSO ipcclean(unix), monitor(unix), postmaster(unix). DIAGNOSTICS Of the nigh-infinite number of error messages you may see when you execute the backend server directly, the most common will probably be: sseemmggeett:: NNoo ssppaaccee lleefftt oonn ddeevviiccee 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 doesn't work, you probably need to configure your kernel for shared memory and sema- phores as described in the installation notes. POSTMASTER(UNIX) POSTMASTER(UNIX) NAME postmaster - run the POSTGRES95 postmaster SYNOPSIS ppoossttmmaasstteerr [--BB n_buffers] [--DD data_dir] [--SS] [--aa system] [--bb backend_pathname] [--dd [debug_level]] [--nn] [--oo backend_options] [--pp port] [--ss] 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 pro- cess. OOnnllyy oonnee ppoossttmmaasstteerr sshhoouulldd bbee rruunn oonn aa mmaacchhiinnee.. The _p_o_s_t_m_a_s_t_e_r understands the following command-line options: --BB 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. --DD data_dir Specifies the directory to use as the root of the tree of database directories. This directory uses the value of the environment vari- able 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. --SS Specifies that the _p_o_s_t_m_a_s_t_e_r process should start up in silent mode. That is, it will disassociate from the user's (controlling) tty and start its own process group. This should not be used in combination with debugging options because any messages printed to standard output and standard error are discarded. --aa 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 connecting to the _p_o_s_t_m_a_s_t_e_r process. Specify _s_y_s_t_e_m to enable a system, or nnoo_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 --aa kkeerrbbeerrooss; to deny any unauthenticated connec- tions, use --aa nnoouunnaauutthh .. The default is site-specific. --bb backend_pathname _b_a_c_k_e_n_d__p_a_t_h_n_a_m_e is the full pathname of the POSTGRES95 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 direc- tory 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 path- name was specified, then the PATH environment variable is searched for an executable named "postgres"). --dd [debug_level] The optional argument _d_e_b_u_g__l_e_v_e_l determines the amount of debugging output the backend servers will produce. If _d_e_b_u_g__l_e_v_e_l is one, the postmaster will trace all connection traffic, and nothing else. For levels two and higher, debugging is turned on in the backend process and the postmaster displays more information, including the backend environment and process traffic. Note that if no file is specified for backend servers to send their debugging output then this output will appear on the controlling tty of their parent _p_o_s_t_m_a_s_t_e_r. --nn, --ss 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. NNeeiitthheerr ooppttiioonn iiss iinntteennddeedd ffoorr uussee iinn oorrddii-- nnaarryy ooppeerraattiioonn. The ordinary strategy for this situation is to notify all other back- ends that they must terminate and then reinitialize the shared memory and semaphores. This is because an errant backend could have cor- rupted 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 reinitial- ize 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. --oo 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. --pp 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 5432, 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 psql. WARNINGS If at all possible, ddoo nnoott use SIGKILL when killing the _p_o_s_t_m_a_s_t_e_r. SIGHUP, SIGINT, or SIGTERM (the default signal for _k_i_l_l(1)) should be used instead. Hence, avoid kill -KILL or its alternative form kill -9 as this will prevent the _p_o_s_t_m_a_s_t_e_r from freeing the system resources (e.g., shared memory and semaphores) that it holds before dying. This prevents you from having to deal with the problem with _s_h_m_a_t(2) described below. EXAMPLES # start postmaster using default values postmaster -S This command will start up _p_o_s_t_m_a_s_t_e_r on the default port (5432) and will search $PATH to find an executable file called "postgres". 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 -b /usr/postgres/bin/postgres & This command will start up a _p_o_s_t_m_a_s_t_e_r 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 using psql, you would need to either specify --pp 11223344 on the _p_s_q_l command-line or set the environment variable PGPORT to 1234. SEE ALSO ipcs(1), ipcrm(1), ipcclean(unix), psql(unix), postgres(unix), DIAGNOSTICS FFiinnddBBaacckkeenndd:: ccoouulldd nnoott ffiinndd aa bbaacckkeenndd ttoo eexxeeccuuttee...... If you see this message, you do not have the _p_o_s_t_g_r_e_s executable in your path. Add the directoy in which postgres resides to your path. sseemmggeett:: NNoo ssppaaccee lleefftt oonn ddeevviiccee 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. If you run multi- ple _p_o_s_t_m_a_s_t_e_rs on a single host, or have reduced the shared memory and semaphore parameters from the defaults in the generic kernel, you may have to go back and increase the shared memory and semaphores con- figured into your kernel. SSttrreeaammSSeerrvveerrPPoorrtt:: ccaannnnoott bbiinndd ttoo ppoorrtt 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 on BSD-based systems ps -e | grep postmast (the equivalent syntax is on System V-like or POSIX-compliant systems such as HP-UX). 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 --pp option. You may also get this error if you ter- minate 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. Finally, you may get this error if you specify a port number that your operating system consid- ers to be reserved. For example, many versions of UNIX consider port numbers under 1024 to be "trusted" and only permit the UNIX superuser to access them. IIppccMMeemmoorryyAAttttaacchh:: sshhmmaatt(()) ffaaiilleedd:: PPeerrmmiissssiioonn ddeenniieedd A likely explanation is that another user attempted to start a _p_o_s_t_- _m_a_s_t_e_r process on the same port which acquired shared resources and then died. Since POSTGRES95 shared memory keys are based on the port number assigned to the _p_o_s_t_m_a_s_t_e_r, such conflicts are likely if there is more than one installation on a single host. If there are no other _p_o_s_t_m_a_s_t_e_r processes currently running (see above), run _i_p_c_c_l_e_a_n and try again. If other _p_o_s_t_m_a_s_t_e_rs are running, you will have to find the owners of those processes to coordinate the assignment of port numbers and/or removal of unused shared memory segments. PSQL(UNIX) PSQL(UNIX) NAME psql - run the interactive query front-end SYNOPSIS ppssqqll [--aa authsvc ] [--AA ] [--cc query ] [--dd dbName] [--ee ] [--ff filename] [--hh help] [--HH hostname] [--ll port] [--nn ] [--oo filename ] [--pp port] [--qq ] [--ss ] [--SS ] [--TT ] [dbname] DESCRIPTION psql is a interactive query front-end to POSTGRES95. It enables you to type in queries interactively, issue them to POSTGRES95, and see the query results. It is designed to be an enhanced version of the older _m_o_n_i_t_o_r program. _p_s_q_l is a frontend application, like any other. Hence, a _p_o_s_t_m_a_s_t_e_r pro- cess must be running on the database server host before _p_s_q_l 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. _d_b_n_a_m_e defaults to the value of the USER environment variable or, if that's not set, to the Unix account name of the current user. _p_s_q_l understands the following command-line options: --aa 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. --AA Turn off fill justification when printing out attributes. --cc query Specifies that _p_s_q_l is to execute one query string, _q_u_e_r_y, and then exit. This is useful for shell scripts, typically in conjunction with the --qq options. --cc option in shell scripts. --dd dbName Specifies the name of the database to connect to. --ee Echo the query sent to the backend --ff filename Use the file _f_i_l_e_n_a_m_e as the source of queries instead of reading queries interactively. --hh Give help information about psql. --HH hostname Specifies the hostname of the machine on which the _p_o_s_t_m_a_s_t_e_r is run- ning. Defaults to the name of the local host, or the value of the PGHOST environment variable (if set). --ll Lists all available databases --nn Do not use the readline library for input line editing and command history. --pp 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 5432, or the value of the PGPORT environment variable (if set). --qq Specifes that _p_s_q_l should do its work quietly. By default, it prints welcome and exit messages and prompts for each query, and prints out the number of rows returned from a query. If this option is used, none of this happens. This is useful with the --cc option in shell scripts. --ss Run in single-step mode where the user at prompted for each query before it is sent to the backend. --SS Run ins single-line mode where each query is terminated by a newline, instead of a semicolon. --TT Turn off printing of attributes names. This is useful with the --cc option in shell scripts. You may set environment variables to avoid typing some of the above options. See the ENVIRONMENT VARIABLES section below. CONNECTING TO A DATABASE _p_s_q_l attempts to make a connection to the database at the hostname and port number specified on the command line. If the connection could not be made for any reason (e.g. insufficient privileges, postmaster is not running on the server, etc) _p_s_q_l will return an error that says Connection to database failed. The reason for the connection failure is not provided. ENTERING QUERIES In normal operation, psql provides a prompt with the name of the database that psql is current connected to followed by the string "=>". For exam- ple, Welcome to the POSTGRES95 interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRES95 type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: testdb testdb=> At the prompt, the user may type in SQL queries. Unless the -S option is set, input lines are sent to the backend when a query-terminating semicolon is reached. Whenever a query is executed, psql also polls for asynchronous notification events generated by _l_i_s_t_e_n(commands) and _n_o_t_i_f_y(commands). PSQL COMMANDS \\aa Toggle fill justification when printing out attributes. \\cc _d_b_n_a_m_e Establish a connection to a new database. The previous connection is closed. \\dd [[_t_a_b_l_e] List tables in the database, or if _t_a_b_l_e is specified, list the columns in _t_a_b_l_e. If table name is *, list all tables and column information for each tables. \\ff _s_e_p_a_r_a_t_o_r Set the field separator. Default is a single blank space. \\hh _c_o_m_m_a_n_d Give syntax help on the specified SQL command. If the _c_o_m_m_a_n_d is not specified, list all the commands for which syntax help is available. If the _c_o_m_m_a_n_d is *, give syntax help on all SQL commands. \\gg Send the current query input buffer to the backend. \\ii _f_i_l_e_n_a_m_e Read queries from _f_i_l_e_n_a_m_e into the query input buffer. \\ll List all the databases in the server. \\oo _f_i_l_e_n_a_m_e Send query results to _f_i_l_e_n_a_m_e. If _f_i_l_e_n_a_m_e is not specified, send query results to _s_t_d_o_u_t. \\pp Print the current query buffer. \\qq Quit the psql program. \\ss _h_i_s_t_o_r_y Save the command line history to a file. (Only available if psql is configured to use readline) \\tt Toggle display of output attribute name headers (defaults to on). \\!! _c_o_m_m_a_n_d Escape to shell and execute _c_o_m_m_a_n_d. \\?? Get help information about the \ commands. ENVIRONMENT VARIABLES You may set any of the following environment variables to avoid specifying command-line options: hostname: PGHOST port: PGPORT tty: PGTTY options: PGOPTION realm: PGREALM If PGOPTION is specified, then the options it contains are parsed bbeeffoorree 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, POSTGRES95 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 information 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 --cc option, _p_s_q_l returns 0 to the shell on successful query completion, 1 otherwise. _p_s_q_l will also return 1 if the connection to a database could not be made for any reason. SEE ALSO introduction(libpq), monitor(unix) postgres(unix), postmaster(unix). BUGS If multiple queries are sent to the backend at once without semicolon ter- mination after each query, psql gets confused about the query results. The queries will still be processed correctly by the backend. INTRODUCTION(BUILT-INS) INTRODUCTION(BUILT-INS) NAME SECTION 3 - WHAT COMES WITH POSTGRES95 (BUILT-INS) DESCRIPTION This section describes the data types, functions and operators available to users in POSTGRES95 as it is distributed. BUILT-IN TYPES This section describes both bbuuiilltt--iinn data types. These Built-in types are are installed in every database. Users may add new types to POSTGRES95 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. PPOOSSTTGGRREESS TTyyppee MMeeaanniinngg abstime absolute date and time aclitem access control list item bool boolean box 2-dimensional rectangle bpchar blank-padded characters bytea variable length array of bytes char character char2 array of 2 characters char4 array of 4 characters char8 array of 8 characters char16 array of 16 characters cid command identifier type date ANSI SQL date type filename large object filename int alias for int4 integer alias for int4 int2 two-byte signed integer int28 array of 8 int2 int4 four-byte signed integer float alias for float4 float4 single-precision floating-point number float8 double-precision floating-point number lseg 2-dimensional line segment name a multi-character type for storing system identifiers oid object identifier type oid8 array of 8 oid oidchar16 oid and char16 composed oidint2 oid and int2 composed oidint4 oid and int4 composed path variable-length array of lseg point 2-dimensional geometric point polygon 2-dimensional polygon real alias for float4 regproc registered procedure reltime relative date and time smgr storage manager smallint alias for int2 text variable length array of characters tid tuple identifier type time ANSI SQL time type tinterval time interval varchar variable-length characters xid transaction identifier type As a rule, the built-in types are all either (1) internal types, in which case the user should not worry about their external format, or (2) have obvious formats. The exceptions to this rule are the three time types. 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 1901, 1902, ..., 2038 Valid dates are from Dec 13 20:45:53 1901 GMT to Jan 19 03:14:04 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 values "current", "infinity" and "-infinity" are also provided. "infinity" specifies a time later than any valid time, and "-infinity" specifies a time earlier than any valid time. "current" indi- cates that the current time should be substituted whenever this value appears in a computation. The strings "now" and "epoch" can be used to specify time values. "now" means the current time, and differs from "current" in that the current time is immediately substituted for it. "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'' (NNoottee: Valid relative times are less than or equal to 68 years.) In addi- tion, the special relative time "Undefined RelTime" is provided. TIME RANGES Time ranges are specified as: [ 'abstime' 'abstime'] where _a_b_s_t_i_m_e is a time in the absolute time format. Special abstime values such as "current", "infinity" and "-infinity" can be used. OPERATORS POSTGRES95 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 imple- ments the operator. Users may invoke operators using the operator name, as in select * from emp where salary < 40000; Alternatively, users may call the functions that implement the operators directly. In this case, the query above would be expressed as select * from emp where int4lt(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 POSTGRES95 system catalogs with the query select t1.typname as argtype, o.oprname, t0.typname, p.proname, t1.typname as 1type, t2.typname as rtype from pg_proc p, pg_type t0, pg_type t1, pg_type t2, pg_operator o 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 argu- ment types for the procedure that implements the operator. Note that these function prototypes are cast in terms of SQL types and so are nnoott directly usable as C function prototypes. TTyyppee OOppeerraattoorr PPOOSSTTGGRREESS FFuunnccttiioonn PPrroottoottyyppee OOppeerraattiioonn 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 tin- terval? < bool abstimelt(abstime, abstime) less than = bool abstimeeq(abstime, abstime) equality >= bool abstimege(abstime, abstime) greater or equal > bool abstimegt(abstime, abstime) greater than bool = bool booleq(bool, bool) equality <> bool boolne(bool, bool) inequality box && bool box_overlap(box, box) boxes overlap &< bool box_overleft(box, box) box A overlaps box B, but does not extend to right of box B &> bool box_overright(box, box) box A overlaps 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 contained 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 - 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 char2 <> bool char2ne(char2, char2) inequality !~ bool char2regexne(char2, text) A does not match regular expression B, case-sensitive !~* bool char2icregexne(char2, text) A does not match regular expression B, case- insensitive <= bool char2le(char2, char2) less or equal < bool char2lt(char2, char2) less than = bool char2eq(char2, char2) equality >= bool char2ge(char2, char2) greater or equal > bool char2gt(char2, char2) greater than ~ bool char2regexeq(char2, text) A matches regu- lar expression B, case- sensitive. ~* bool char2icregexeq(char2, text) A matches regu- lar expression B, case- insensitive. char4 <> bool char4ne(char4, char4) inequality !~ bool char4regexne(char4, text) A does not match regular expression B, case-sensitive !~* bool char4icregexne(char4, text) A does not match regular expression B, case- insensitive <= bool char4le(char4, char4) less or equal < bool char4lt(char4, char4) less than = bool char4eq(char4, char4) equality >= bool char4ge(char4, char4) greater or equal > bool char4gt(char4, char4) greater than ~ bool char4regexeq(char4, text) A matches regu- lar expression B, case- sensitive ~* bool char4icregexeq(char4, text) A matches regu- lar expression B, case- insensitive char8 <> bool char8ne(char8, char8) inequality !~ bool char8regexne(char8, text) A does not match regular expression B, case sensitive !~* bool char8icregexne(char8, text) A does not match regular expression B, case insensi- tive <= bool char8le(char8, char8) less or equal < bool char8lt(char8, char8) less than = bool char8eq(char8, char8) equality >= bool char8ge(char8, char8) greater or equal > bool char8gt(char8, char8) greater than ~ bool char8regexeq(char8, text) A matches regu- lar expression B, case sensi- tive ~* bool char8icregexeq(char8, text) A matches regu- lar expression B, case insen- sitive char16 <> bool char16ne(char16, char16) inequality !~ bool char16regexne(char16, text) A does not match regular expression B, case sensitive !~* bool char16icregexne(char16, text) A does not match regular expression B, case insensi- tive <= 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, text) A matches regu- lar expression B, case sensi- tive ~* bool char16regexeq(char16, text) A matches regu- lar expression B, case insen- sitive float4 <> bool float4ne(float4, float4) inequality * float4 float4mul(float4, float4) multiplication + 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 + 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 <= bool int24le(int2, int4) less or equal < bool int2lt(int2, int2) less than < bool int24lt(int2, int4) less than = bool int2eq(int2, int2) equality = bool int24eq(int2, int4) equality >= bool int2ge(int2, int2) greater or equal >= bool int24ge(int2, int4) greater or equal > bool int2gt(int2, int2) greater than > bool int24gt(int2, int4) greater than int4 <> bool int4ne(int4, int4) inequality <> bool int42ne(int4, int2) inequality % int4 int42mod(int4, int2) modulus % int4 int4mod(int4, int4) modulus * 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 <= bool int42le(int4, int2) less or equal < bool int4lt(int4, int4) less than < bool int42lt(int4, int2) less than = bool int4eq(int4, int4) equality = bool int42eq(int4, int2) equality = bool int4eqoid(int4, oid) equality >= bool int4ge(int4, int4) greater or equal >= bool int42ge(int4, int2) greater or equal > bool int4gt(int4, int4) greater than > bool int42lt(int4, int2) less than name <> bool namene(name, name) inequality !~ bool nameregexne(name, text) A does not match regular expression B, case sensitive !~* bool nameicregexne(name, text) A does not match regular expression B, case insensi- tive <= bool namele(name, name) less or equal < bool namelt(name, name) less than = bool nameeq(name, name) equality >= bool namege(name, name) greater or equal > bool namegt(name, name) greater than ~ bool nameregexeq(name, text) A matches regu- lar expression B, case sensi- tive ~* bool nameregexeq(name, text) A matches regu- lar expression B, case insen- sitive oid <> bool oidne(oid, oid) inequality <> bool oidne(oid, regproc) inequality <= bool oidle(oid, oid) less or equal < bool oidlt(oid, oid) less than = bool oideq(oid, oid) equality = bool oideqint4(oid, int4) equality = bool oideq(oid, regproc) equality >= bool oidge(oid, oid) greater or equal > bool oidgt(oid, oid) greater than oidname <> bool oidnamene(oidname, oidname) inequality < bool oidnamelt(oidname, oidname) less than <= bool oidnamele(oidname, oidname) less or equal = bool oidnameeq(oidname, oidname) equality > bool oidnamegt(oidname, oidname) greater than >= bool oidnamege(oidname, oidname) greater or equal oidint2 <> bool oidint2ne(oidint2, oidint2) inequality < bool oidint2lt(oidint2, oidint2) less than <= bool oidint2le(oidint2, oidint2) less or equal = bool oidint2eq(oidint2, oidint2) equality > bool oidint2gt(oidint2, oidint2) greater than >= bool oidint2ge(oidint2, oidint2) 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 ===> 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 over- lap &< 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 contained 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 con- tain the regu- lar expression B, case sensi- tive !~* bool texticregexne(text, text) A does not con- tain the regu- lar expression B, case insen- sitive <= 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 expres- sion B. case sensitive ~* bool texticregexeq(text, text) A contains the regular expres- sion B. case insensitive 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 rel- time #= bool intervalleneq(tinterval, reltime) interval length equal to rel- time #>= bool intervallenge(tinterval, reltime) interval length greater or equal reltime #> bool intervallengt(tinterval, reltime) interval length greater than reltime && bool intervalov(tinterval, tinterval) intervals over- lap << bool intervalct(tinterval, tinterval) A contains B = bool intervaleq(tinterval, tinterval) equality <> tinterval mktinterval(abstime, abstime) interval bounded by two abstimes UNARY OPERATORS The tables below give right and left unary operators. Left unary operators have the operator precede the operand; right unary operators have the operator follow the operand. Right Unary Operators TTyyppee OOppeerraattoorr PPOOSSTTGGRREESS FFuunnccttiioonn PPrroottoottyyppee OOppeerraattiioonn float8 % float8 dround(float8) round to nearest integer Left Unary Operators TTyyppee OOppeerraattoorr PPOOSSTTGGRREESS FFuunnccttiioonn PPrroottoottyyppee OOppeerraattiioonn 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 ; float8 dlog1(float8) natural loga- rithm tinterval | abstime intervalstart(tinterval) start of inter- val AGGREGATE FUNCTIONS The table below gives the aggregate functions that are registered in the system catalogs. They operate on int2, int4, float4, and float8 types. NNaammee OOppeerraattiioonn arg average sum sum max maximum min minimum count count SEE ALSO For examples on specifying literals of built-in types, see _S_Q_L(_c_o_m_m_a_n_d_s). BUGS Although most of the input and output functions correponding to the base types (e.g., integers and floating point numbers) do some error-checking, none of them are particularly rigorous about it. More importantly, almost none of the operators and functions (e.g., addition and multiplication) perform any error-checking at all. Consequently, many of the numeric operations will (for example) silently underflow or overflow. Some of the input and output functions are not invertible. That is, the result of an output function may lose precision when compared to the origi- nal input. INTRODUCTION(COMMANDS) INTRODUCTION(COMMANDS) NAME SECTION 4 - SQL COMMANDS (COMMANDS) DESCRIPTION The following is a description of the general syntax of SQL. Individual SQL statements and commands are treated separately in the document; this section describes the syntactic classes from which the constituent parts of SQL statements are drawn. Comments A _c_o_m_m_e_n_t is an arbitrary sequence of characters following double dashes up to the end of the line e.g: -- This is a comment Names _N_a_m_e_s in SQL are sequences of not more than NAMEDATALEN alphanumeric char- acters, starting with an alphabetic character. By default, NAMEDATALEN is set to 16, but at the time the system is built, NAMEDATALEN can be changed by changing the #ifdef in src/backend/include/postgres.h. Underscore ("_") is considered an alphabetic character. Keywords The following identifiers are reserved for use as _k_e_y_w_o_r_d_s and may not be used otherwise: In addition, all POSTGRES95 classes have several predefined attributes used by the system. Constants There are six types of _c_o_n_s_t_a_n_t_s for use in SQL. They are described below. String Constants _S_t_r_i_n_g_s in SQL are arbitrary sequences of ASCII characters bounded by sin- gle quotes (' '). Uppercase 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 limitations 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 POSTGRES95 Large Object interface. Integer Constants _I_n_t_e_g_e_r _c_o_n_s_t_a_n_t_s in SQL 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 char- acters embedded in the string. Floating point constaints are of type float4. Constants of POSTGRES95 User-Defined Types A constant of an _a_r_b_i_t_r_a_r_y type can be entered using the notation: or CAST 'string' AS type-name The value inside the string is passed to the input conversion routine for the type called type-name. The result is a constant of the indicated type. The explicit typecast may be omitted if there is no ambiguity as to the type the constant must be, in which case it is automatically coerced. Array constants _A_r_r_a_y _c_o_n_s_t_a_n_t_s are arrays of any POSTGRES95 type, including other arrays, string constants, etc. The general format of an array constant is the fol- lowing: {} Where <_d_e_l_i_m> is the delimiter for the type stored in the "pg_type" class. (For built-in types, this is the comma character, ",".) An example of an array constant is {{1,2,3},{4,5,6},{7,8,9}} This constant is a two-dimensional, 3 by 3 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. Fields A _f_i_e_l_d is either an attribute of a given class or one of the following: oid tmin tmax xmin xmax cmin cmax _O_i_d stands for the unique identifier of an instance which is added by POSTGRES95 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 transac- tion, 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 "abstime" data type. Tran- saction 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 SQL commands within one transaction. Columns A _c_o_l_u_m_n is a construct of the form: Instance-variable{.composite_field}.field `['number`]' _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 nneeww or ccuurrrreenntt.. New and current can only appear in the action portion of a rule, while other instance variables can be used in any SQL statement. _C_o_m_p_o_s_i_t_e__f_i_e_l_d is a field of of one of the POSTGRES95 compo- site types indicated in the _i_n_f_o_r_m_a_t_i_o_n(commands) section, 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 elements are returned. Operators Any built-in system, or user-defined operator may be used in SQL. For the list of built-in and system operators consult iinnttrroodduuccttiioonn(built-ins). 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 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 SQL function. Typically this is used in SQL function definition statement. The form of a parameter is: '$' number For example, consider the definition of a function, DEPT, as create function DEPT (char16) returns dept as 'select * from dept where name=$1' language 'sql' 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 SQL function, followed 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 com- putes a single value) or an aggregate function (i.e., one that computes a set of values). The syntax is the following: aggregate.name (attribute) Here, _a_g_g_r_e_g_a_t_e__n_a_m_e must be a previously defined aggregate. Target_list A _t_a_r_g_e_t _l_i_s_t is a parenthesized, comma-separated list of one or more ele- ments, each of which must be of the form: a_expr[AS result_attname] 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 attribute name which is assumed to be the name of the result field. In POSTGRES95 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 logical 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: [class_reference] instance_variable {, [class_ref] instance_variable...} 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 indicated 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 speci- fied 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 specified, 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 1981r'] are valid time specifications. Note that this syntax is slightly different than that used by the time- range type. SEE ALSO insert(commands), delete(commands), execute(commands), update(commands), select(commands), monitor(unix). ABORT(COMMANDS) ABORT(COMMANDS) NAME abort - abort the current transaction SYNOPSIS aabboorrtt [[ttrraannssaaccttiioonn]] DESCRIPTION This command aborts the current transaction and causes all the updates made by the transaction to be discarded. _a_b_o_r_t is functionally equivalent to _r_o_l_l_b_a_c_k. SEE ALSO begin(commands), end(commands), rollback(commands). ALTER(TABLE) ALTER(TABLE) NAME alter table - add attributes to a class SYNOPSIS aalltteerr ttaabbllee classname [*] aadddd ccoolluummnn attname type DESCRIPTION The aalltteerr ttaabbllee command causes a new attribute 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_etable(_c_o_m_m_a_n_d_s). In order to add an attribute to each class in an entire inheritance hierar- chy, use the _c_l_a_s_s_n_a_m_e of the superclass and append a "*". (By default, the attribute will not be added to any of the subclasses.) This should aallwwaayyss be done when adding an attribute to a superclass. If it is not, queries on the inheritance hierarchy such as select * from super* s will not work because the subclasses will be missing an attribute found in the superclass. For efficiency reasons, default values for added attributes are not placed in existing instances of a class. That is, existing instances will have NULL values in the new attributes. If non-NULL values are desired, a sub- sequent _u_p_d_a_t_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 -- alter table emp add column hiredate abstime -- -- add a health-care number to all persons -- (including employees, students, ...) -- alter table person * add column health_care_id int4 SEE ALSO create table (commands), update (commands). BEGIN(COMMANDS) BEGIN(COMMANDS) NAME begin - begins a transaction SYNOPSIS bbeeggiinn [[ttrraannssaaccttiioonn||wwoorrkk]] DESCRIPTION This command begins a user transaction which POSTGRES95 will guarantee is serializable with respect to all concurrently executing transactions. POSTGRES95 uses two-phase locking to perform this task. If the transaction is committed, POSTGRES95 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). CLOSE(COMMANDS) CLOSE(COMMANDS) NAME close - close a cursor SYNOPSIS cclloossee [cursor_name] DESCRIPTION CClloossee frees the resources associated with a cursor, _c_u_r_s_o_r__n_a_m_e. After this cursor is closed, no subsequent operations are allowed on it. A cur- sor should be closed when it is no longer needed. If _c_u_r_s_o_r__n_a_m_e. is not specified, then the blank cursor is closed. EXAMPLE /* * close the cursor FOO */ close FOO SEE ALSO fetch(commands), select(commands). COMMIT(COMMANDS) COMMIT(COMMANDS) NAME commit - commit the current transaction SYNOPSIS ccoommmmiitt [[ttrraannssaaccttiioonn||wwoorrkk]] 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. _c_o_m_m_i_t is functionally equivalent to the _e_n_d command SEE ALSO abort(commands), begin(commands), end(commands), rollback(commands). COPY(COMMANDS) COPY(COMMANDS) NAME copy - copy data to or from a class from or to a UNIX file. SYNOPSIS ccooppyy [bbiinnaarryy] [nnoonnuullllss] classname ttoo|ffrroomm "filename"|ssttddiinn|ssttddoouutt [UUSSIINNGG DDEELLIIMMIITTEERRSS delim] DESCRIPTION CCooppyy moves data between POSTGRES95 classes and standard UNIX files. The keyword bbiinnaarryy 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 ssttddiinn and ssttddoouutt can be used so that input to ccooppyy can be written by a LIBPQ applica- tion and output from the ccooppyy command can be read by a LIBPQ application. The bbiinnaarryy keyword will force all data to be stored/read as binary objects rather than as ASCII text. It is somewhat faster than the normal ccooppyy com- mand, but is not generally portable, and the files generated are somewhat larger, although this factor is highly dependent on the data itself. By default, ccooppyy uses a tab (\t) character as a delimiter. The delimiter may also be changed to any other single-character with the use of UUSSIINNGG DDEELLIIMMIITTEERRSS Characters in data fields which happen to match the delimiter character will be quoted. You must have read access on any class whose values are read by the ccooppyy command, and either write or append access to a class to which values are being appended by the ccooppyy command. FORMAT OF OUTPUT FILES ASCII COPY FORMAT When ccooppyy is used without the bbiinnaarryy keyword, the file generated will have each instance on a line, with each attribute separated by the delimiter character. Embedded delimiter characters 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 func- tion for a type should not try to generate the backslash character; this will be handled by ccooppyy itself. Note that on input to ccooppyy, backslashes are considered to be special con- trol characters, and should be doubled if you want to embed a backslash, i.e., the string "12\\19\88" will be converted by ccooppyy to "12\1988". The actual format for each instance is ... If ccooppyy 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 them- selves, when it is done. Similarly, if ccooppyy is reading from standard input, it will expect a period (.) followed by a newline, as the first two characters on a line, to denote end-of-file. However, ccooppyy will terminate (followed by the backend itself) if a true EOF is encountered. NNUULLLL attributes are handled simply as null strings, that is, consecutive tabs in the input file denote a NNUULLLL attribute. BINARY COPY FORMAT In the case of ccooppyy bbiinnaarryy, 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 ccooppyy bbiinnaarryy 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 EEXXAACCTTLLYY. 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-3s, 2-byte attributes are aligned on two-byte boundaries, and all larger attributes are aligned on four-byte boundaries. Character attri- butes are aligned on single-byte boundaries. On other machines, all attri- butes 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 insert(commands), create table(commands), vacuum(commands), libpq. BUGS Files used as arguments to the ccooppyy command must reside on or be accessible to the the database server machine by being either on local disks or a networked file system. CCooppyy stops operation at the first error. This should not lead to problems in the event of a ccooppyy ffrroomm, but the target relation will, of course, be partially modified in a ccooppyy ttoo. The _v_a_c_u_u_m(commands) query should be used to clean up after a failed ccooppyy. Because POSTGRES95 operates out of a different directory than the user's working directory at the time POSTGRES95 is invoked, the result of copying to a file "foo" (without additional path information) may yield unexpected results for the naive user. In this case, "foo" will wind up in $PGDATA/foo. In general, the full pathname should be used when specifying files to be copied. CCooppyy has virtually no error checking, and a malformed input file will likely cause the backend to crash. You should avoid using ccooppyy for input whenever possible. CREATE AGGREGATE(COMMANDS) CREATE AGGREGATE(COMMANDS) NAME create aggregate - define a new aggregate SYNOPSIS ccrreeaattee aaggggrreeggaattee agg-name [aass] (([ssffuunncc11 == state-transition-function-1 , bbaasseettyyppee == data-type , ssttyyppee11 == sfunc1-return-type] [,, ssffuunncc22 == state-transition-function-2 , ssttyyppee22 == sfunc2-return-type] [,, ffiinnaallffuunncc == final-function] [,, iinniittccoonndd11 == initial-condition-1] [,, iinniittccoonndd22 == 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 ffiinnaall ccaallccuullaattiioonn function, F: F(internal-state1, internal-state2) ---> aggregate-value These functions are required to have the following properties: The arguments to state-transition-function-1 must be ((stype1,,basetype)), and its return value must be stype1. The argument and return value of state-transition-function-2 must be ssttyyppee22. The arguments to the final-calculation-function must be ((stype1,,stype2)), and its return value must be a POSTGRES base type (not necessarily the same as basetype. The final-calculation-function should be specified if and only if both state-transition functions are specified. Note that it is possible to specify aggregate functions that have varying combinations of state and final functions. For example, the "count" aggre- gate requires ssffuunncc22 (an incrementing function) but not ssffuunncc11 or ffiinnaall-- ffuunncc, whereas the "sum" aggregate requires ssffuunncc11 (an addition function) but not ssffuunncc22 or ffiinnaallffuunncc and the "average" aggregate requires both of the above state functions as well as a ffiinnaallffuunncc (a division function) to produce its answer. In any case, at least one state function must be defined, and any ssffuunncc22 must have a corresponding iinniittccoonndd22. 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 addition 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. -- --Create an aggregate for int4 average -- create aggregate avg (sfunc1 = int4add, basetype = int4, stype1 = int4, sfunc2 = int4inc, stype2 = int4, finalfunc = int4div, initcond1 = "0", initcond2 = "0") SEE ALSO create function(commands), remove aggregate(commands). CREATE FUNCTION(COMMANDS) CREATE FUNCTION(COMMANDS) NAME create function - define a new function SYNOPSIS ccrreeaattee ffuunnccttiioonn function_name (( ([type1 {, type-n}]) rreettuurrnnss type-r aass {'/full/path/to/objectfile' | 'sql-queries'} llaanngguuaaggee {'c' 'sql' 'internal'} DESCRIPTION With this command, a POSTGRES95 user can register a function with POSTGRES95. 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 or (The _a_r_g _i_s clause may be left out if the function has no arguments, or alternatively the argument list may be left empty.) The input types may be base or complex types, or _o_p_a_q_u_e. _O_p_a_q_u_e indicates that the function accepts arguments of an invalid type such as (char *). The output type may be specified as a base type, complex type, _s_e_t_o_f <_t_y_p_e>, or _o_p_a_q_u_e. The _s_e_t_o_f modifier indicates that the function will return a set of items, rather than a single item. The _a_s clause of the command is treated differently for C and SQL functions, as explained below. C FUNCTIONS Functions written in C can be defined to POSTGRES95, which will dynamically 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 addi- tional overhead, as the function will remain in a main memory cache. Internal functions are functions written in C which have been statically linked into the postgres backend process. The aass clause must still be specified when defining an internal function but the contents are ignored. Writing C Functions The body of a C function following aass should be the FFUULLLL PPAATTHH of the object code (.o file) for the function, bracketed by quotation marks. (POSTGRES95 will not compile a function automatically - it must be compiled before it is used in a ddeeffiinnee ffuunnccttiioonn command.) C functions with base type arguments can be written in a straightforward fashion. The C equivalents of built-in POSTGRES95 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 fol- lowing 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 POSTGRES95 source tree and "" 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 spe- cial 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 appropriately as shown in the examples below. Compiling Dynamically-Loaded C Functions Different operating systems require different procedures for compiling C source files so that POSTGRES95 can load them dynamically. This section discusses the required compiler and loader options on each system. Under Linux ELF, object files can be generated by specifing the compiler flag -fpic. Under Ultrix, all object files that POSTGRES95 is expected to load dynami- cally must be compiled using /_b_i_n/_c_c with the "-G 0" option turned on. The object file name in the _a_s clause should end in ".o". Under HP-UX, DEC OSF/1, AIX and SunOS 4, all object files must be turned into _s_h_a_r_e_d _l_i_b_r_a_r_i_e_s using the operating system's native object file loader, _l_d(1). Under HP-UX, an object file must be compiled using the native HP-UX C com- piler, /_b_i_n/_c_c, with both the "+z" and "+u" flags turned on. The first flag turns the object file into "position-independent code" (PIC); the second flag removes some alignment restrictions that the PA-RISC architec- ture normally enforces. The object file must then be turned into a shared library using the HP-UX loader, /_b_i_n/_l_d. The command lines to compile a C source file, "foo.c", look like: cc +z +u -c foo.c ld -b -o foo.sl foo.o The object file name in the aass clause should end in ".sl". An extra step is required under versions of HP-UX prior to 9.00. If the POSTGRES95 header file include/c.h is not included in the source file, then the following line must also be added at the top of every source file: #pragma HP_ALIGN HPUX_NATURAL_S500 However, this line must not appear in programs compiled under HP-UX 9.00 or later. Under DEC OSF/1, an object file must be compiled and then turned into a shared library using the OSF/1 loader, /_b_i_n/_l_d. In this case, the command lines look like: cc -c foo.c ld -shared -expect_unresolved '*' -o foo.so foo.o The object file name in the aass clause should end in ".so". Under SunOS 4, an object file must be compiled and then turned into a shared library using the SunOS 4 loader, /_b_i_n/_l_d. The command lines look like: cc -PIC -c foo.c ld -dc -dp -Bdynamic -o foo.so foo.o The object file name in the aass clause should end in ".so". Under AIX, object files are compiled normally but building the shared library requires a couple of steps. First, create the object file: cc -c foo.c You must then create a symbol "exports" file for the object file: mkldexport foo.o `pwd` > foo.exp Finally, you can create the shared library: ld -H512 -T512 -o foo.so -e _nostart \ -bI:.../lib/postgres.exp -bE:foo.exp foo.o \ -lm -lc 2>/dev/null You should look at the POSTGRES95 User's Manual for an explanation of this procedure. SQL FUNCTIONS SQL functions execute an arbitrary list of SQL queries, returning the results of the last query in the list. SQL 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 body of a SQL function following aass 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 SQL 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 attri- butes of the argument (e.g. "$1.emp"), or to invoke functions via a nested-dot syntax. EXAMPLES: C Functions The following command defines a C function, overpaid, of two basetype argu- ments. create function overpaid (float8, int4) returns bool as '/usr/postgres/src/adt/overpaid.o' language 'c' The C file "overpaid.c" might look something like: #include 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: select name from EMP where overpaid(salary, age) One can also write this as a function of a single argument of type EMP: create function overpaid_2 (EMP) returns bool as '/usr/postgres/src/adt/overpaid_2.o' language 'c' The following query is now accepted: select name from EMP 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) } EXAMPLES: SQL Functions To illustrate a simple SQL function, consider the following, which might be used to debit a bank account: create function TP1 (int4, float8) returns int4 as 'update BANK set balance = BANK.balance - $2 where BANK.acctountno = $1 select(x = 1)' language 'sql' A user could execute this function to debit account 17 by $100.00 as fol- lows: select (x = TP1( 17,100.0)) The following more interesting examples take a single argument of type EMP, and retrieve multiple results: select function hobbies (EMP) returns set of HOBBIES as 'select (HOBBIES.all) from HOBBIES where $1.name = HOBBIES.person' language 'sql' SEE ALSO information(unix), load(commands), drop function(commands). NOTES Name Space Conflicts More than one function may be defined with the same name, as long as the arguments they take are different. In other words, function names can be _o_v_e_r_l_o_a_d_e_d. A function may also have the same name as an attribute. In the case that there is an ambiguity between a function on a complex type and an attribute of the complex type, the attribute will always be used. 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 ccrreeaattee ffuunnccttiioonn. There is a subtle implication of this restriction: while the dynamic loading routines in most operating systems are more than happy to allow you to load any number of shared libraries that contain conflicting (identically-named) function names, they may in fact botch the load in interesting ways. For example, if you define a dynamically-loaded function that happens to have the same name as a function built into POSTGRES95, the DEC OSF/1 dynamic loader causes POSTGRES95 to call the function within itself rather than allowing POSTGRES95 to call your function. Hence, if you want your function to be used on different architectures, we recommend that you do not overload C function names. There is a clever trick to get around the problem just described. Since there is no problem overloading SQL functions, you can define a set of C functions with different names and then define a set of identically-named SQL function wrappers that take the appropriate argument types and call the matching C function. _o_p_a_q_u_e cannot be given as an argument to a SQL function. BUGS C functions cannot return a set of values. CREATE INDEX(COMMANDS) CREATE INDEX(COMMANDS) NAME create index - construct a secondary index SYNOPSIS ccrreeaattee iinnddeexx index-name oonn classname uussiinngg am-name (( attname type_class )) ccrreeaattee iinnddeexx index-name oonn classname uussiinngg am-name (( funcname (( attname-1 { , attname-i } )) type_class )) DESCRIPTION This command constructs an index called _i_n_d_e_x-_n_a_m_e. _A_m-_n_a_m_e is the name of the access method which is used for the index. In the first syntax shown above, the key field for the index is specified as an attribute name and an associated _o_p_e_r_a_t_o_r _c_l_a_s_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. In the second syntax shown above, an index can be defined on the result of a user-defined function _f_u_n_c_n_a_m_e applied to one or more attributes of a single class. These _f_u_n_c_t_i_o_n_a_l _i_n_d_i_c_e_s are primarily useful in two situa- tions. First, functional indices can be used to simulate multikey indices. That is, the user can define a new base type (a simple combination of, say, "oid" and "int2") and the associated functions and operators on this new type such that the access method can use it. Once this has been done, the standard techniques for interfacing new types to access methods (described in the POSTGRES95 user manual) can be applied. Second, functional indices can be used to obtain fast access to data based on operators that would normally require some transformation to be applied to the base data. For example, say you have an attribute in class "myclass" called "pt" that con- sists of a 2D point type. Now, suppose that you would like to index this attribute but you only have index operator classes for 2D polygon types. You can define an index on the point attribute using a function that you write (call it "point_to_polygon") and your existing polygon operator class; after that, queries using existing polygon operators that reference "point_to_polygon(myclass.pt)" on one side will use the precomputed polygons stored in the functional index instead of computing a polygon for each and every instance in "myclass" and then comparing it to the value on the other side of the operator. Obviously, the decision to build a func- tional index represents a tradeoff between space (for the index) and execu- tion time. POSTGRES95 provides btree, rtree and hash access methods for secondary indices. The btree access method is an implementation of the Lehman-Yao high-concurrency btrees. The rtree access method implements standard rtrees using Guttman's quadratic split algorithm. The hash access method is an implementation of Litwin's linear hashing. We mention the algorithms used solely to indicate that all of these access methods are fully dynamic and do not have to be optimized periodically (as is the case with, for example, static hash access methods). The operator classes defined on btrees are int2_ops char2_ops oidint2_ops int4_ops char4_ops oidint4_ops int24_ops char8_ops oidname_ops int42_ops char16_ops abstime_ops float4_ops name_ops float8_ops oid_ops char_ops text_ops 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 operator classes _o_i_d_i_n_t_2__o_p_s, _o_i_d_i_n_t_4__o_p_s, and _o_i_d_c_h_a_r_1_6__o_p_s represent the use of _f_u_n_c_t_i_o_n_a_l _i_n_d_i_c_e_s to simulate multi-key indices. The POSTGRES95 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 "box" datatype in POSTGRES95. 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 "polygon" data. The POSTGRES95 query optimizer will consider using an rtree index whenever an indexed attribute is involved in a comparison using one of << &< &> >> @ ~= && The operator classes defined on the hash access method are char_ops int2_ops char2_ops int4_ops char4_ops float4_ops char8_ops float8_ops char16_ops oid_ops name_ops text_ops The POSTGRES95 query optimizer will consider using a hash index whenever an indexed attribute is involved in a comparison using the = operator. EXAMPLES -- --Create a btree index on the emp class using the age attribute. -- create index empindex on emp using btree (age int4_ops) -- --Create a btree index on employee name. -- create index empname on emp using btree (name char16_ops) -- --Create an rtree index on the bounding rectangle of cities. -- create index cityrect on city using rtree (boundbox box_ops) -- --Create a rtree index on a point attribute such that we --can efficiently use box operators on the result of the --conversion function. Such a qualification might look --like "where point2box(points.pointloc) = boxes.box". -- create index pointloc on points using rtree (point2box(location) box_ops) CREATE OPERATOR(COMMANDS) CREATE OPERATOR(COMMANDS) NAME create operator - define a new user operator SYNOPSIS ccrreeaattee ooppeerraattoorr operator_name (([ lleeffttaarrgg == type-1 ] [ ,, rriigghhttaarrgg == type-2 ] , pprroocceedduurree == func_name [,, ccoommmmuuttaattoorr == com_op ] [,, nneeggaattoorr == neg_op ] [,, rreessttrriicctt == res_proc ] [,, hhaasshheess] [,, jjooiinn == join_proc ] [,, ssoorrtt == 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 _o_p_e_r_a_t_o_r__n_a_m_e is a sequence of up to sixteen punctuation characters. The following characters are valid for single-character operator names: ~ ! @ # % ^ & ` ? If the operator name is more than one character long, it may consist of any combination of the above characters or the following additional characters: | $ : + - * / < > = At least one of _l_e_f_t_a_r_g and _r_i_g_h_t_a_r_g must be defined. For binary opera- tors, both should be defined. For right unary operators, only _a_r_g_1 should be defined, while for left unary operators only _a_r_g_2 should be defined. 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 _c_r_e_a_t_e _f_u_n_c_t_i_o_n(commands) and must have one or two arguments. The commutator operator is present so that POSTGRES95 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 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, POSTGRES95 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 commutators 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. POSTGRES95 can always evaluate a join (i.e., processing a clause with two tuple variables separated by an operator that returns a boolean) by iterative substitution [WONG76]. In addition, POSTGRES95 is planning on implementing a hash-join algorithm along the lines of [SHAP86]; however, it must know whether this strategy is applicable. 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 hhaasshheess 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, POSTGRES95 will change the optimizer and run-time system to use them and will require additional specification when an operator is defined. Fortunately, the research com- munity invents new join strategies infrequently, and the added generality of user-defined join strategies was not felt to be worth the complexity involved. The last two pieces of the specification are present so the query optimizer can estimate result sizes. If a clause of the form: MYBOXES.description <<< "0,0,1,1"::box is present in the qualification, then POSTGRES95 may have to estimate the fraction of the instances in MYBOXES that satisfy the clause. The function res_proc must be a registered function (meaning it is already defined using _d_e_f_i_n_e _f_u_n_c_t_i_o_n(commands)) which accepts one argument of the correct data type and returns a floating point number. The query optimizer simply 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 POSTGRES95 attempts to optimize operators and can decide to use an index to restrict the search space when operators are involved. However, there is no attempt to optimize functions, and they are performed by brute force. Moreover, functions can have any number of arguments while opera- tors are restricted to one or two. EXAMPLE -- --The following command defines a new operator, --area-equality, for the BOX data type. -- create operator === ( leftarg = box, rightarg = box, procedure = area_equal_procedure, commutator = ===, negator = !==, restrict = area_restriction_procedure, hashes, join = area-join-procedure, sort = <<<, <<<) SEE ALSO create function(commands), drop operator(commands). BUGS Operator names cannot be composed of alphabetic characters in POSTGRES95. If an operator is defined before its commuting operator has been defined (a case specifically warned against above), a dummy operator with invalid fields will be placed in the system catalogs. This may interfere with the definition of later operators. CREATE RULE(COMMANDS) CREATE RULE(COMMANDS) NAME create rule - define a new rule SYNOPSIS ccrreeaattee rruullee rule_name aass oonn event ttoo object [wwhheerree clause] ddoo [iinnsstteeaadd] [action | nothing | [[actions...]]] DESCRIPTION _T_h_e _c_u_r_r_e_n_t _r_u_l_e _s_y_s_t_e_m _i_m_p_l_e_m_e_n_t_a_t_i_o_n _i_s _v_e_r_y _b_r_i_t_t_l_e _a_n_d _i_s _u_n_s_t_a_b_l_e. _U_s_e_r_s _a_r_e _d_i_s_c_o_u_r_a_g_e_d _f_r_o_m _u_s_i_n_g _r_u_l_e_s _a_t _t_h_i_s _t_i_m_e. CCrreeaattee rruullee is used to define a new rule. Here, _e_v_e_n_t is one of _s_e_l_e_c_t, _u_p_d_a_t_e, _d_e_l_e_t_e or _i_n_s_e_r_t. _O_b_j_e_c_t is either: a class name _o_r class.column The ffrroomm clause, the wwhheerree clause, and the _a_c_t_i_o_n are respectively normal SQL ffrroomm clauses, wwhheerree clauses and collections of SQL commands with the following change: nneeww or ccuurrrreenntt can appear instead of an instance variable whenever an instance variable is permissible in SQL. The semantics of a rule is that at the time an individual instance is accessed, updated, inserted or deleted, there is a ccuurrrreenntt instance (for retrieves, updates and deletes) and a nneeww instance (for updates and appends). If the event specified in the oonn clause and the condition speci- fied in the wwhheerree 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 SQL rules is in order. If the same class name or instance variable appears in the event, wwhheerree clause and the _a_c_t_i_o_n parts of a rule, they are all considered different tuple variables. More accu- rately, nneeww and ccuurrrreenntt are the only tuple variables that are shared between these clauses. For example, the following two rules have the same semantics: on update to EMP.salary where EMP.name = "Joe" do update EMP ( ... ) where ... on update to EMP-1.salary where EMP-2.name = "Joe" do update EMP-3 ( ... ) where ... Each rule can have the optional tag iinnsstteeaadd. Without this tag _a_c_t_i_o_n will be performed in addition to the user command when the event in the condi- tion 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 nnootthhiinngg. When choosing between the rewrite and instance rule systems for a particu- lar rule application, remember that in the rewrite system ccuurrrreenntt refers to a relation and some qualifiers whereas in the instance system it refers to an instance (tuple). It is very important to note that the rreewwrriittee rule system will neither detect nor process circular rules. For example, though each of the follow- ing two rule definitions are accepted by POSTGRES95, the _r_e_t_r_i_e_v_e command will cause POSTGRES95 to _c_r_a_s_h: -- --Example of a circular rewrite rule combination. -- create rule bad_rule_combination_1 is on select to EMP do instead select to TOYEMP create rule bad_rule_combination_2 is on select to TOYEMP do instead select to EMP -- --This attempt to retrieve from EMP will cause POSTGRES95 to crash. -- select * from EMP 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 -- create rule example_1 is on update EMP.salary where current.name = "Joe" do update 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 -- create rule example_2 is on select to EMP.salary where current.name = "Bill" do instead select (EMP.salary) from EMP 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) -- create rule example_3 is on select 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) create rule example_4 is on select to TOYEMP do instead select (EMP.name, EMP.salary) from EMP where EMP.dept = "toy" -- --All new employees must make 5,000 or less -- create rule example_5 is on insert to EMP where new.salary > 5000 do update newset salary = 5000 SEE ALSO drop rule(commands), create view(commands). BUGS iinnsstteeaadd rules do not work properly. The object in a SQL rule cannot be an array reference and cannot have parameters. Aside from the "oid" field, system attributes cannot be referenced anywhere 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 rule system store the rule text and query plans as text attributes. This implies that creation of rules may fail if the rule plus its various internal representations exceed some value that is on the order of one page (8KB). CREATE(TABLE) CREATE(TABLE) NAME create table - create a new class SYNOPSIS ccrreeaattee ttaabbllee classname ((attname-1 type-1 {,, attname-i type-i})) [iinnhheerriittss (( classname-1 {,, classname-i} ))] [aarrcchhiivvee == archive_mode] [ssttoorree == "smgr_name"] [aarrcchh__ssttoorree == "smgr_name"] DESCRIPTION CCrreeaattee ttaabbllee 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. Each type may be a simple type, a complex type (set) or an array type. Each array attribute stores arrays that must have the same number of dimen- sions but may have different sizes and array index bounds. An array of dimension _n is specified by appending _n pairs of square brackets: att_name = type[][]..[] The optional iinnhheerriittss clause specifies a collection of class names from which this class automatically inherits all fields. If any inherited field name appears more than once, POSTGRES95 reports an error. POSTGRES95 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_l_t_e_rtable(commands) or other ccrreeaattee ttaabbllee statements. See _i_n_t_r_o_d_u_c_t_i_o_n(commands) for a further discussion of this point. The optional ssttoorree and aarrcchh__ssttoorree keywords may be used to specify a storage manager to use for the new class. The released version of POSTGRES95 sup- ports only "magnetic disk" as a storage manager name; the research system at UC Berkeley provides additional storage managers. SSttoorree controls the location of current data, and aarrcchh__ssttoorree controls the location of histori- cal data. AArrcchh__ssttoorree may only be specified if aarrcchhiivvee is also specified. If either ssttoorree or aarrcchh__ssttoorree is not declared, it defaults to "magnetic disk". The new class is created as a heap with no initial data. A class can have no more than 1600 attributes (realistically, this is limited by the fact that tuple sizes must be less than 8192 bytes), but this limit may be con- figured lower at some sites. A class cannot have the same name as a system catalog class. The aarrcchhiivvee 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]. EXAMPLES -- -- Create class emp with attributes name, sal and bdate -- create table emp (name char16, salary float4, bdate abstime) -- --Create class permemp with pension information that --inherits all fields of emp -- create table permemp (plan char16) inherits (emp) -- --Create class foo on magnetic disk and archive historical data -- create table foo (bar int4) archive = heavy store = "magnetic disk" -- --Create class tictactoe to store noughts-and-crosses --boards as a 2-dimensional array -- create table tictactoe (game int4, board = char[][]) -- --Create a class newemp with a set attribute "manager". A --set (complex) attribute may be of the same type as the --relation being defined (as here) or of a different complex --type. The type must exist in the "pg_type" catalog or be --the one currently being defined. -- create table newemp (name text, manager = newemp) SEE ALSO drop table(commands). CREATE TYPE (COMMANDS) CREATE TYPE (COMMANDS) NAME create type - define a new base data type SYNOPSIS ccrreeaattee ttyyppee typename ((iinntteerrnnaalllleennggtthh = (number | vvaarriiaabbllee), [ eexxtteerrnnaalllleennggtthh = (number | vvaarriiaabbllee),, ] iinnppuutt = input_function, oouuttppuutt = output_function [,, eelleemmeenntt = typename] [,, ddeelliimmiitteerr = ] [,, ddeeffaauulltt = "string" ] [,, sseenndd = send_function ] [,, rreecceeiivvee = receive_function ] [,, ppaasssseeddbbyyvvaalluuee])) DESCRIPTION CCrreeaattee ttyyppee allows the user to register a new user data type with POSTGRES95 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. CCrreeaattee ttyyppee requires the registration of two functions (using _c_r_e_a_t_e _f_u_n_c_t_i_o_n(commands)) before defining the type. The representation of a new base type is determined by _i_n_p_u_t__f_u_n_c_t_i_o_n, which converts the type's exter- nal representation to an internal representation usable by the operators and functions defined for the type. Naturally, _o_u_t_p_u_t__f_u_n_c_t_i_o_n performs the reverse transformation. Both the input and output functions must be declared to take one or two arguments of type "opaque". New base data types can be fixed length, in which case iinntteerrnnaalllleennggtthh is a positive integer, or variable length, in which case POSTGRES95 assumes that the new type has the same format as the POSTGRES95-supplied data type, "text". To indicate that a type is variable-length, set iinntteerrnnaalllleennggtthh to _v_a_r_i_a_b_l_e. The external representation is similarly specified using the _e_x_t_e_r_n_a_l_l_e_n_g_t_h keyword. 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 eelleemmeenntt keyword. For example, to define an array of 4 byte integers ("int4"), specify element = int4 To indicate the delimiter to be used on arrays of this type, ddeelliimmiitteerr can be set to a specific character. The default delimiter is the comma (",") character. A ddeeffaauulltt 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__f_u_n_c_t_i_o_n and _r_e_c_e_i_v_e__f_u_n_c_t_i_o_n are used when the application program requesting POSTGRES95 services resides on a different machine. In this case, the machine on which POSTGRES95 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 when sseenndding from the server to the client and converting from the standard for- mat to the machine specific format when the server rreecceeiivvees the data from the client. 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 converted if passed from a Sun-4 to a DECstation, but many other types do. The optional ppaasssseeddbbyyvvaalluuee 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 aggregates using the appropriate facilities described in this section. ARRAY TYPES Two generalized built-in functions, aarrrraayy__iinn and aarrrraayy__oouutt,, exist for quick creation of variable-length array types. These functions operate on arrays of any existing POSTGRES95 type. LARGE OBJECT TYPES A "regular" POSTGRES95 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 interface. The length of all large object types is always _v_a_r_i_a_b_l_e, meaning the iinntteerrnnaall-- lleennggtthh for large objects is always -1. EXAMPLES -- --This command creates the box data type and then uses the --type in a class definition -- create type box (internallength = 8, input = my_procedure_1, output = my_procedure_2) create table MYBOXES (id = int4, description = box) -- --This command creates a variable length array type with --integer elements. -- create type int4array (input = array_in, output = array_out, internallength = variable, element = int4) create table MYARRAYS (id = int4, numbers = int4array) -- --This command creates a large object type and uses it in --a class definition. -- create type bigobj (input = lo_filein, output = lo_fileout, internallength = variable) create table BIG_OBJS (id = int4, obj = bigobj) RESTRICTIONS Type names cannot begin with the underscore character ("_") and can only be 15 characters long. This is because POSTGRES95 silently creates an array type for each base type with a name consisting of the base type's name prepended with an underscore. SEE ALSO create function(commands), create operator(commands), drop type(commands), introduction(large objects). CREATE VERSION(COMMANDS) CREATE VERSION(COMMANDS) NAME create version - construct a version class SYNOPSIS ccrreeaattee vveerrssiioonn classname1 ffrroomm classname2 [[[abstime]]] DESCRIPTION _C_u_r_r_e_n_t_l_y, _t_h_e _v_e_r_s_i_o_n_i_n_g _f_a_c_i_l_i_t_y _i_s _n_o_t _w_o_r_k_i_n_g. 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 ssnnaappsshhoott of _c_l_a_s_s_n_a_m_e_2 as of the time specified. POSTGRES95 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 ultimately 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 create view(commands), merge(commands). BUGS Snapshots (i.e., the optional _a_b_s_t_i_m_e clause) are not implemented in POSTGRES95. CREATE VIEW(COMMANDS) CREATE VIEW(COMMANDS) NAME create view - construct a virtual class SYNOPSIS ccrreeaattee vviieeww view_name aass sseelleecctt expression1 [aass attr_name1] {, expression_i [aass attr_namei]} [ffrroomm from.last] [wwhheerree qual] DESCRIPTION ccrreeaattee vviieeww 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 automatically generated to support retrieve operations on views. Then, the user can add as many update rules as desired to specify the processing of update opera- tions to views. See [STON90] for a detailed discussion of this point. EXAMPLE -- --create a view consisting of toy department employees -- create view toyemp as select e.name from emp e where e.dept = 'toy' -- --Specify deletion semantics for toyemp -- create rule example1 as on delete to toyemp do instead delete emp where emp.oid = current.oid SEE ALSO create table(commands), create rule(commands), CREATE(DATABASE) CREATE(DATABASE) NAME create database - create a new database SYNOPSIS ccrreeaattee ddaattaabbaassee dbname DESCRIPTION CCrreeaattee ddaattaabbaassee creates a new POSTGRES95 database. The creator becomes the administrator of the new database. SEE ALSO createdb(unix), drop database(commands), destroydb(unix), initdb(unix). BUGS This command should NNOOTT be executed interactively. The _c_r_e_a_t_e_d_b(unix) script should be used instead. DELETE(COMMANDS) DELETE(COMMANDS) NAME delete - delete instances from a class SYNOPSIS ddeelleettee instance_variable [ ffrroomm from_list ] [ wwhheerree qual ] DESCRIPTION DDeelleettee 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 from emp where emp.sal > 30000 -- --Clear the hobbies class -- delete from hobbies SEE ALSO drop(commands). DESTROYDB(COMMANDS) DESTROYDB(COMMANDS) NAME drop database - destroy an existing database SYNOPSIS ddrroopp ddaattaabbaassee dbname DESCRIPTION DDrroopp ddaattaabbaassee 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 create database(commands), destroydb(unix). BUGS This query should NNOOTT be executed interactively. The _d_e_s_t_r_o_y_d_b(unix) script should be used instead. DROP(TABLE) DROP(TABLE) NAME drop table - destroy existing classes SYNOPSIS ddrroopp ttaabbllee classname-1 { ,, classname-i } DESCRIPTION DDrroopp TTaabbllee 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 _d_e_l_e_t_e(commands). 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. The destruction of classes is not reversable. Thus, a destroyed class will not be recovered if a transaction which destroys this class fails to com- mit. In addition, historical access to instances in a destroyed class is not possible. EXAMPLE -- --Destroy the emp class -- drop table emp -- --Destroy the emp and parts classes -- drop table emp, parts SEE ALSO delete(commands), drop index(commands). DROP AGGREGATE(COMMANDS) DROP AGGREGATE(COMMANDS) NAME drop aggregate - remove the definition of an aggregate SYNOPSIS ddrroopp aaggggrreeggaattee aggname DESCRIPTION ddrroopp aaggggrreeggaattee will remove all reference to an existing aggregate defini- tion. To execute this command the current user must be the the owner of the aggregate. EXAMPLE -- --Remove the average aggregate -- drop aggregate avg SEE ALSO create aggregate(commands). DROP FUNCTION(COMMANDS) DROP FUNCTION(COMMANDS) NAME drop function - remove a user-defined C function SYNOPSIS ddrroopp ffuunnccttiioonn function_name ( [[ ttyyppee--11 {{ ,, ttyyppee--nn }} ]] )) DESCRIPTION ddrroopp ffuunnccttiioonn will remove references to an existing C function. To execute this command the user must be the owner of the function. The input argu- ment types to the function must be specified, as only the function with the given name and argument types will be removed. EXAMPLE -- --this command removes the square root function -- drop function sqrt(int4) SEE ALSO create function(commands). BUGS No checks are made to ensure that types, operators or access methods that rely on the function have been removed first. DROP INDEX(COMMANDS) DROP INDEX(COMMANDS) NAME drop index - removes an index from POSTGRES95 SYNOPSIS ddrroopp iinnddeexx index_name DESCRIPTION This command drops an existing index from the POSTGRES95 system. To exe- cute this command you must be the owner of the index. EXAMPLE -- --this command will remove the "emp_index" index -- drop index emp_index SEE ALSO create index(commands). DROP OPERATOR(COMMANDS) DROP OPERATOR(COMMANDS) NAME drop operator - remove an operator from the system SYNOPSIS ddrroopp ooppeerraattoorr 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. The left or right type of a left or right unary operator, respectively, may be speci- fied as _n_o_n_e. 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 int4 -- drop operator ^ (int4, int4) -- --Remove left unary operator !a for booleans -- drop operator ! (none, bool) -- --Remove right unary factorial operator a! for int4 -- drop operator ! (int4, none) SEE ALSO create operator(commands). DROP RULE(COMMANDS) DROP RULE(COMMANDS) NAME drop rule - removes a current rule from POSTGRES95 SYNOPSIS ddrroopp rruullee rule_name DESCRIPTION This command drops the rule named rule_name from the specified POSTGRES95 rule system. POSTGRES95 will immediately cease enforcing it and will purge its definition from the system catalogs. EXAMPLE -- --This example drops the rewrite rule example_1 -- drop rule example_1 SEE ALSO create rule(commands), drop view(commands). BUGS Once a rule is dropped, access to historical information the rule has writ- ten may disappear. DROP TYPE(COMMANDS) DROP TYPE(COMMANDS) NAME drop type - remove a user-defined type from the system catalogs SYNOPSIS ddrroopp ttyyppee 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, aggre- gates, access methods, subtypes, classes, etc. that use a deleted type. EXAMPLE -- --remove the box type -- drop type box SEE ALSO introduction(commands), create type(commands), drop operator(commands). BUGS If a built-in type is removed, the behavior of the backend is unpredict- able. END(COMMANDS) END(COMMANDS) NAME end - commit the current transaction SYNOPSIS eenndd [[ttrraannssaaccttiioonn]] 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). FETCH(COMMANDS) FETCH(COMMANDS) NAME fetch - fetch instance(s) from a cursor SYNOPSIS ffeettcchh [ (ffoorrwwaarrdd | bbaacckkwwaarrdd) ] [ ( number | aallll) ] [iinn cursor_name] DESCRIPTION FFeettcchh allows a user to retrieve instances from a cursor named _c_u_r_s_o_r__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 cursor is less than _n_u_m_b_e_r, then only those available are fetched. Substituting the keyword _a_l_l in place of a number will cause all remaining instances in the cursor 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 directions. The default direction is _f_o_r_w_a_r_d. Updating data in a cursor is not supported by POSTGRES95, because mapping cursor 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 transaction blocks marked by _b_e_g_i_n(commands) and _e_n_d(commands) because the data that they store spans multiple user queries. EXAMPLE -- --set up and use a cursor -- begin declare myportal cursor for select * from pg-user end -- --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 begin(commands), end(commands), close(commands), move(commands), select(commands). BUGS Currently, the smallest transaction in POSTGRES95 is a single SQL command. It should be possible for a single fetch to be a transaction. INSERT(COMMANDS) INSERT(COMMANDS) NAME insert - insert tuples to a relation SYNOPSIS iinnsseerrtt into classname [(att.expr-1,{att_expr.i})] {vvaalluueess (expression1 {,expression-i}) | sseelleecctt expression1,{expression-i} [ffrroomm from-list] [wwhheerree qual] DESCRIPTION IInnsseerrtt adds instances that 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. That is, each _a_t_t__e_x_p_r specifies a field (either an attribute name or an attribute name plus an array specification) to which the corresponding _e_x_p_r_e_s_s_i_o_n should be assigned. The fields in the target list may be listed in any order. Fields of the result class which do not appear in the target list default to NULL. If the expression for each field is not of the correct data type, automatic type coercion will be attempted. An array initialization may take exactly one of the following forms: -- -- Specify a lower and upper index for each dimension -- att_name[lIndex-1:uIndex-1]..[lIndex-i:uIndex-i] = array_str -- --Specify only the upper index for each dimension --(each lower index defaults to 1) -- att_name[uIndex-1]..[uIndex-i] = array_str -- --Use the upper index bounds as specified within array_str --(each lower index defaults to 1) -- att_name = array_str where each _l_I_n_d_e_x or _u_I_n_d_e_x is an integer constant and _a_r_r_a_y__s_t_r is an array constant (see _i_n_t_r_o_d_u_c_t_i_o_n(commands)). If the user does not specify any array bounds (as in the third form) then POSTGRES95 will attempt to deduce the actual array bounds from the contents of _a_r_r_a_y__s_t_r. If the user does specify explicit array bounds (as in the first and second forms) then the array may be initialized partly or fully using a C-like syntax for array initialization. However, the uninitialized array elements will contain garbage. 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)). EXAMPLES -- --Make a new employee Jones work for Smith -- insert into emp select newemp.name, newemp.salary, "Smith", 1990-newemp.age from newemp where name = "Jones" -- --Insert into newemp class to newemp -- insert into newemp select * from newemp1 -- --Create an empty 3x3 gameboard for noughts-and-crosses --(all of these queries create the same board attribute) -- insert into tictactoe (game, board[1:3][1:3]) values(1,'{{"","",""},{},{"",""}}') insert into tictactoe (game, board[3][3]) values (2,'{}') insert into tictactoe (game, board) values (3,'{{,,},{,,},{,,}}') SEE ALSO create table(commands), create type(commands), update(commands), select(commands) LISTEN(COMMANDS) LISTEN(COMMANDS) NAME listen - listen for notification on a relation SYNOPSIS lliisstteenn class_name DESCRIPTION lliisstteenn is used to register the current backend as a listener on the rela- tion _c_l_a_s_s__n_a_m_e. When the command nnoottiiffyy _c_l_a_s_s__n_a_m_e is called either from within a rule or at the query level, the frontend applications correspond- ing to the listening backends are notified. When the backend process exits, this registration is cleared. This event notification is performed through the LIBPQ protocol and fron- tend application interface. The application program must explicitly poll a LIBPQ global variable, _P_Q_A_s_y_n_c_N_o_t_i_f_y_W_a_i_t_i_n_g, and call the routine _P_Q_n_o_t_i_- _f_i_e_s in order to find out the name of the class to which a given notifica- tion corresponds. If this code is not included in the application, the event notification will be queued and never be processed. SEE ALSO create rule(commands), notify(commands), select(commands), libpq. BUGS There is no way to un-lliisstteenn except to drop the connection (i.e., restart the backend server). The _m_o_n_i_t_o_r(unix) command does not poll for asynchronous events. LOAD(COMMANDS) LOAD(COMMANDS) NAME load - dynamically load an object file SYNOPSIS llooaadd "filename" DESCRIPTION LLooaadd loads an object (or ".o") file into POSTGRES95's address space. Once a file is loaded, all functions in that file can be accessed. This func- tion is used in support of ADT's. If a file is not loaded using the llooaadd command, the file will be loaded automatically the first time the function is called by POSTGRES95. LLooaadd can also be used to reload an object file if it has been edited and recom- piled. 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 llooaadd command, meaning, for example, that all func- tions in file A should call each other, functions in the standard or math libraries, or in POSTGRES95 itself. They should not call functions defined in a different loaded file B. This is because if B is reloaded, the POSTGRES95 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, how- ever, there will not be a problem. On DECstations, you must use /_b_i_n/_c_c with the "-G 0" option when compiling object files to be loaded. Note that if you are porting POSTGRES95 to a new platform, the llooaadd command will have to work in order to support ADTs. NOTIFY(COMMANDS) NOTIFY(COMMANDS) NAME notify - signal all frontends and backends listening on a class SYNOPSIS nnoottiiffyy class_name DESCRIPTION nnoottiiffyy is used to awaken all backends and consequently all frontends that have executed _l_i_s_t_e_n(commands) on _c_l_a_s_s__n_a_m_e. This can be used either within an instance-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 interprocess communication (IPC). When used from within a rule, this can be thought of as an alerter mechanism. Notice that the mere fact that a nnoottiiffyy has been executed does not imply anything in particular about the state of the class (e.g., that it has been updated), nor does the notification protocol transmit any useful informa- tion other than the class name. Therefore, all nnoottiiffyy does is indicate that some backend wishes its peers to examine _c_l_a_s_s__n_a_m_e in some application-specific way. This event notification is performed through the LIBPQ protocol and fron- tend application interface. The application program must explicitly poll a LIBPQ global variable, _P_Q_A_s_y_n_c_N_o_t_i_f_y_W_a_i_t_i_n_g, and call the routine _P_Q_n_o_t_i_- _f_i_e_s in order to find out the name of the class to which a given notifica- tion corresponds. If this code is not included in the application, the event notification will be queued and never be processed. SEE ALSO define rule(commands), listen(commands), libpq. PURGE(COMMANDS) PURGE(COMMANDS) NAME purge - discard historical data SYNOPSIS ppuurrggee classname [ bbeeffoorree abstime ] [ aafftteerr reltime ] DESCRIPTION PPuurrggee 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", POSTGRES95 will discard tuples whose validity expired before the indicated time. PPuurrggee 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. POSTGRES95 will enforce this restriction, silently. Note that the ppuurrggee command does not do anything except set a parameter for system operation. Use _v_a_c_u_u_m(commands) to enforce this parameter. 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 SEE ALSO vacuum(commands). BUGS AND CAVEATS 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. You cannot purge certain system catalogs (namely, "pg_class", "pg_attribute", "pg_am", and "pg_amop") due to circularities in the system catalog code. This definition of the ppuurrggee command is really only useful for non-archived relations, since tuples will not be discarded from archive relations (they are never vacuumed). DROP VIEW(COMMANDS) DROP VIEW(COMMANDS) NAME drop view - removes a view from POSTGRES95 SYNOPSIS ddrroopp vviieeww view_name DESCRIPTION This command drops an existing view from the POSTGRES95 system. To execute this command you must be the owner of the view. EXAMPLE /* * this command will remove the "myview" view */ drop view myview SEE ALSO create view(commands), drop rule(commands), RENAME(COMMANDS) RENAME(COMMANDS) NAME rename - rename a class or an attribute in a class SYNOPSIS aalltteerr ttaabbllee classname1 rreennaammee ttoo classname2 aalltteerr ttaabbllee classname1 [**] rreennaammee [[ccoolluummnn]] attname1 ttoo attname2 DESCRIPTION The rreennaammee 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. In order to rename an attribute in each class in an entire inheritance hierarchy, use the _c_l_a_s_s_n_a_m_e of the superclass and append a "*". (By default, the attribute will not be renamed in any of the subclasses.) This should aallwwaayyss be done when changing an attribute name in a superclass. If it is not, queries on the inheritance hierarchy such as select * from super* s will not work because the subclasses will be (in effect) missing an attri- bute found in the superclass. 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 permit- ted. EXAMPLE -- -- change the emp class to personnel -- alter table emp rename to personnel -- -- change the sports attribute to hobbies -- alter table emp rename column sports to hobbies -- -- make a change to an inherited attribute -- alter table person * rename column last_name to family_name 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. ROLLBACK(COMMANDS) ROLLBACK(COMMANDS) NAME rollback - rollback, e.g. aborts, the current transaction SYNOPSIS rroollllbbaacckk [[ttrraannssaaccttiioonn||wwoorrkk]] DESCRIPTION This command rolls back the current transaction and causes all the updates made by the transaction to be discarded. _r_o_l_l_b_a_c_k is functionally equivalent to the _a_b_o_r_t command. SEE ALSO abort(commands), begin(commands), end(commands). SELECT(COMMANDS) SELECT(COMMANDS) NAME select - retrieve instances from a class SYNOPSIS sseelleecctt [distinct] expression1 [aass attr_name-1] {, expression-1 [aass attr_name-i]} [iinnttoo ttaabbllee classname] [ffrroomm from-last] [ggrroouupp bbyy attr_name1 {, attr_name-i....} [oorrddeerr bbyy attr_name1 [uussiinngg oopp11] {, attr_namei [uussiinngg ooppii] } DESCRIPTION SSeelleecctt will get all instances which satisfy the qualification, _q_u_a_l, com- pute the value of each element in the target list, and either (1) return them to an application program through one of two different kinds of por- tals or (2) store them in a new class. If into table class name is specified, the result of the query will be stored in a new class with the indicated name. The oorrddeerr bbyy 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. The target list specifies the fields to be retrieved. Each _a_t_t_r__n_a_m_e specifies the desired attribute or portion of an array attribute. Thus, each _a_t_t_r__n_a_m_e takes the form class_name.att_name or, if the user only desires part of an array, -- --Specify a lower and upper index for each dimension --(i.e., clip a range of array elements) -- class_name.att_name[lIndex-1:uIndex-1]..[lIndex-i:uIndex-i] -- --Specify an exact array element -- class_name.att_name[uIndex-1]..[uIndex-i] where each _l_I_n_d_e_x or _u_I_n_d_e_x is an integer constant. When you retrieve an attribute which is of a complex type, the behavior of the system depends on whether you used "nested dots" to project out attri- butes of the complex type or not. See the examples below. You must have read access to a class to read its values (see _g_r_a_n_t/_r_e_v_o_k_e(commands). EXAMPLES -- --Find all employees who make more than their manager -- select e.name from emp e, emp m where e.mgr = m.name and e.sal > m.sal -- --Retrieve all fields for those employees who make --more than the average salary -- select avg(sal) as ave into table avgsal from emp; -- --Retrieve all employee names in sorted order -- select distinct name from emp order by name using < -- --Retrieve all employee names that were valid on 1/7/85 --in sorted order -- selec name from emp['January 7 1985'] e order by name using < -- --Construct a new class, raise, containing 1.1 --times all employee's salaries -- select 1.1 * emp.salary as salary into tables raise from emp SEE ALSO insert(commands), close(commands), create table(commands), fetch(commands), update(commands). BUGS RReettrriieevvee iinnttoo does not delete duplicates. If the backend crashes in the course of executing a rreettrriieevvee iinnttoo, the class file will remain on disk. It can be safely removed by the database DBA, but a subsequent rreettrriieevvee iinnttoo to the same name will fail with a cryp- tic error message about "BlockExtend". UPDATE(COMMANDS) UPDATE(COMMANDS) NAME update - replace values of attributes in a class SYNOPSIS uuppddaattee classname sseett attname-1 = expression-1, {attname-i = expression-i} [wwhheerree qual] DESCRIPTION UUppddaattee 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 the attributes to be modified need appear in _t_a_r_g_e_t__l_i_s_t. Array references use the same syntax found in _s_e_l_e_c_t(_c_o_m_m_a_n_d_s). That is, either single array elements, a range of array elements or the entire array may be replaced with a single query. You must have write access to the class in order to modify it, as well as read access to any class whose values are mentioned in the target list or qualification. EXAMPLES -- --Give all employees who work for Smith a 10% raise -- update emp set sal = 1.1 * sal where mgr = 'Smith' SEE ALSO create table(commands), select(commands). VACUUM(COMMANDS) VACUUM(COMMANDS) NAME vacuum - vacuum a database SYNOPSIS vvaaccuuuumm DESCRIPTION VVaaccuuuumm is the POSTGRES95 vacuum cleaner. It opens every class in the data- base, 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 vvaaccuuuumm periodically will increase POSTGRES95's speed in processing user queries. The open database is the one that is vacuumed. We recommend that production databases be vacuumed nightly, in order to keep statistics relatively current. The vvaaccuuuumm query may be executed at any time, however. In particular, after copying a large class into POSTGRES95 or deleting a large number of records, it may be a good idea to issue a vvaaccuuuumm query. This will update the system catalogs with the results of all recent changes, and allow the POSTGRES95 query optimizer to make better choices in planning user queries. SEE ALSO vacuum(unix). INTRODUCTION(LIBPQ) INTRODUCTION(LIBPQ) NAME SECTION 5 - LIBPQ DESCRIPTION LIBPQ is the programmer's interface to POSTGRES95. LIBPQ is a set of library routines that allows queries to pass to the POSTGRES95 backend and instances to return through an IPC channel. This version of the documentation describes the C interface library. Three short programs are included at the end of this section to show how to write programs that use LIBPQ. There are several examples of LIBPQ applications in the following direc- tories: ../src/test/regress ../src/test/examples ../src/bin/psql Frontend programs which use LIBPQ must include the header file and must link with the library. CONTROL AND INITIALIZATION The following environment variables can be used to set up default environ- ment values to avoid hard-coding database names into an application pro- gram: sets the default server name. sets additional runtime options for the POSTGRES95 backend. sets the default port for communicating with the POSTGRES95 backend. sets the file or tty on which debugging messages from the backend server are displayed. sets the default POSTGRES95 database name. sets the realm to use with POSTGRES95, if it is different from the local realm. If is set, POSTGRES95 applications will attempt authentica- tion with servers for this realm and use separate ticket files to avoid conflicts with local ticket files. This environment variable is only used if authentication is enabled. The following routines deal with making a connection to a backend from a C program. Makes a new connection to a backend. PGconn *PQsetdb(char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName); If any argument is NULL, then the corresponding environment variable is checked. If the environment variable is also not set, then hardwired defaults are used. always returns a valid PGconn pointer. The (see below) command should be called to ensure that a connection was properly made before queries are sent via the connection. LIBPQ programmers should be careful to maintain the PGconn abstraction. Use the accessor functions below to get at the contents of PGconn. Avoid directly referencing the fields of the PGconn structure as they are subject to change in the future. returns the database name of the connection. char *PQdb(PGconn *conn) returns the host name of the connection. char *PQhost(PGconn *conn) returns the pgoptions used in the connection. char *PQoptions(PGconn *conn) returns the pgport of the connection. char *PQport(PGconn *conn) returns the pgtty of the connection. char *PQtty(PGconn *conn) Returns the status of the connection. The status can be CONNECTION_OK or CONNECTION_BAD. ConnStatusType *PQstatus(PGconn *conn) returns the error message associated with the connection char *PQerrorMessage(PGconn* conn); Close the connection to the backend. Also frees memory used by the PGconn structure. The PGconn pointer should not be used after PQfinish has been called. void PQfinish(PGconn *conn) Reset the communication port with the backend. This function will close the IPC socket connection to the backend and attempt to reestablish a new connection to the same backend. void PQreset(PGconn *conn) Enables tracing of messages passed between the frontend and the backend. The messages are echoed to the debug_port file stream. void PQtrace(PGconn *conn, FILE* debug_port); Disables tracing of messages passed between the frontend and the backend. void PQuntrace(PGconn *conn); Submit a query to POSTGRES95. Returns a PGresult pointer if the query was successful or a NULL otherwise. If a NULL is returned, can be used to get more information about the error. PGresult *PQexec(PGconn *conn, char *query); The PGresult structure encapsulates the query result returned by the back- end. LIBPQ programmers should be careful to maintain the PGresult abstrac- tion. Use the accessor functions described below to retrieve the results of the query. Avoid directly referencing the fields of the PGresult structure as they are subject to change in the future. Returns the result status of the query. can return one of the following values: PGRES_EMPTY_QUERY, PGRES_COMMAND_OK, /* the query was a command */ PGRES_TUPLES_OK, /* the query successfully returned tuples */ PGRES_COPY_OUT, PGRES_COPY_IN, PGRES_BAD_RESPONSE, /* an unexpected response was received */ PGRES_NONFATAL_ERROR, PGRES_FATAL_ERROR If the result status is PGRES_TUPLES_OK, then the following routines can be used to retrieve the tuples returned by the query. returns the number of tuples (instances) in the query result. int PQntuples(PGresult *res); returns the number of fields (attributes) in the query result. int PQnfields(PGresult *res); returns the field (attribute) name associated with the given field index. Field indices start at 0. char *PQfname(PGresult *res, int field_index); returns the field (attribute) index associated with the given field name. int PQfnumber(PGresult *res, char* field_name); returns the field type associated with the given field index. The integer returned is an internal coding of the type. Field indices start at 0. Oid PQftype(PGresult *res, int field_num); returns the size in bytes of the field associated with the given field index. If the size returned is -1, the field is a variable length field. Field indices start at 0. int2 PQfsize(PGresult *res, int field_index); returns the field (attribute) value. For most queries, the value returned by is a null-terminated ASCII string representation of the attribute value. If the query was a result of a cursor, then the value returned by is the binary representation of the type in the internal format of the backend server. It is the programmer's responsibility to cast and convert the data to the correct C type. The value returned by points to storage that is part of the PGresult structure. One must explicitly copy the value into other storage if it is to be used past the lifetime of the PGresult struc- ture itself. char* PQgetvalue(PGresult *res, int tup_num, int field_num); returns the length of a field (attribute) in bytes. If the field is a the length returned here does include the size field of the varlena, i.e., it is 4 bytes less. int PQgetlength(PGresult *res, int tup_num, int field_num); Returns the command status associated with the last query command. char *PQcmdStatus(PGresult *res); Returns a string with the object id of the tuple inserted if the last query is an INSERT command. Otherwise, returns an empty string. char* PQoidStatus(PGresult *res); Prints out all the tuples and, optionally, the attribute names to the specified output stream. The programs and both use for output. void PQdisplayTuples( PGresult* res, FILE* fout, /* output stream */ int fillAlign, /* pad the fields with spaces? */ char *fieldSep, /* string to use as the field separator */ int printHeader, /* display attribute headers */ int quiet, /* print the number of rows returned ? */ ); Frees the storage associated with the PGresult. Every query result should be properly freed when it is no longer used. Failure to do this will result in memory leaks in the frontend application. The PQresult* passed in should be a value which is returned from PQexec(). Calling PQclear() on an uninitialized PQresult pointer will very likely result in a core dump. void PQclear(PQresult *res); POSTGRES95 provides a interface to send function calls to the backend. This is a trapdoor into system internals and can be a potential security hole. Most users will not need this feature. PGresult* PQfn(PGconn* conn, int fnid, int *result_buf, int *result_len, int result_is_int, PQArgBlock *args, int nargs); The argument is the object identifier of the function to be executed. is the buffer in which to load the return value. The caller must have allo- cated sufficient space to store the return value. The result length will be returned in the storage pointed to by If the result is to be an integer value, than should be set to 1; otherwise it should be set to 0. and specify the arguments to the function. typedef struct { int len; int isint; union { int *ptr; int integer; } u; } PQArgBlock; always returns a valid PGresult*. The resultStatus should be checked before the result is used. The caller is responsible for freeing the PGresult with when it is not longer needed. POSTGRES95 supports asynchronous notification via the and commands. A backend registers its interest in a particular relation with the LISTEN command. All backends listening on a particular relation will be notified asynchronously when a NOTIFY of that relation name is executed by another backend. No additional information is passed from the notifier to the listener. Thus, typically, any actual data that needs to be communicated is transferred through the relation. LIBPQ applications are notified whenever a connected backend has received an asynchronous notification. However, the communication from the backend to the frontend is not asynchronous. Notification comes piggy-backed on other query results. Thus, an application must submit queries, even empty ones, in order to receive notice of backend notification. In effect, the LIBPQ application must poll the backend to see if there is any pending notification information. After the execution of a query, a frontend may call to see if any notification data is available from the backend. returns the notification from a list of unhandled notifications from the backend. Returns NULL if there are no pending notifications from the back- end. behaves like the popping of a stack. Once a notification is returned from it is considered handled and will be removed from the list of notifi- cations. PGnotify* PQNotifies(PGconn *conn); The second sample program gives an example of the use of asynchronous notification. The command in POSTGRES95 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. Reads a newline-terminated line of characters (transmitted by the backend server) into a buffer of size Like this routine copies up to characters into It is like however, in that it converts the terminating newline into a null character. returns EOF at EOF, 0 if the entire line has been read, and 1 if the buffer is full but the terminating newline has not yet been read. Notice that the application must check to see if a new line consists of the single character ".", which indicates that the backend server has finished sending the results of the command. Therefore, if the application ever expects to receive lines that are more than characters long, the application must be sure to check the return value of very carefully. The code in ../src/bin/psql/psql.c contains routines that correctly handle the copy protocol. int PQgetline(PGconn *conn, char *string, int length) Sends a null-terminated to the backend server. The application must expli- citly send the single character "." to indicate to the backend that it has finished sending its data. void PQputline(PGconn *conn, char *string); Syncs with the backend. This function waits until the backend has finished the copy. It should either be issued when the last string has been sent to the backend using or when the last string has been received from the back- end using 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(PGconn *conn); As an example: PQexec(conn, "create table foo (a int4, b char16, d float8)"); PQexec(conn, "copy foo from stdin"); PQputline(conn, "3hello world4.5\n"); PQputline(conn,"4goodbye world7.11\n"); ... PQputline(conn,".\n"); PQendcopy(conn); Enable tracing of the frontend/backend communication to a debugging file stream. void PQtrace(PGconn *conn FILE *debug_port) Disable tracing started by void PQuntrace(PGconn *conn) If the user has generated the appropriate authentication credentials (e.g., obtaining tickets), the frontend/backend authentication process is handled by without any further intervention. The following routines may be called by LIBPQ programs to tailor the behavior of the authentication process. Returns a pointer to static space containing whatever name the user has authenticated. Use of this routine in place of calls to or by applications is highly recommended, as it is entirely possible that the authenticated user name is the same as value of the environment variable or the user's entry in char *fe_getauthname(char* errorMessage) Specifies that LIBPQ should use authentication service rather than its compiled-in default. This value is typically taken from a command-line switch. void fe_setauthsvc(char *name, char* errorMessage) Any error messages from the authentication attempts are returned in the errorMessage argument. The query buffer is 8192 bytes long, and queries over that length will be silently truncated. /* * testlibpq.c * Test the C version of LIBPQ, the POSTGRES95 frontend library. * * */ #include #include "libpq-fe.h" void exit_nicely(PGconn* conn) { PQfinish(conn); exit(1); } main() { char *pghost, *pgport, *pgoptions, *pgtty; char* dbName; int nFields; int i,j; /* FILE *debug; */ PGconn* conn; PGresult* res; /* begin, by setting the parameters for a backend connection if the parameters are null, then the system will try to use reasonable defaults by looking up environment variables or, failing that, using hardwired constants */ pghost = NULL; /* host name of the backend server */ pgport = NULL; /* port of the backend server */ pgoptions = NULL; /* special options to start up the backend server */ pgtty = NULL; /* debugging tty for the backend server */ dbName = "template1"; /* make a connection to the database */ conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); /* check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr,"Connection to database '%s' failed.0, dbName); fprintf(stderr,"%s",PQerrorMessage(conn)); exit_nicely(conn); } /* debug = fopen("/tmp/trace.out","w"); */ /* PQtrace(conn, debug); */ /* start a transaction block */ res = PQexec(conn,"BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"BEGIN command failed0); PQclear(res); exit_nicely(conn); } /* should PQclear PGresult whenever it is no longer needed to avoid memory leaks */ PQclear(res); /* fetch instances from the pg_database, the system catalog of databases*/ res = PQexec(conn,"DECLARE myportal CURSOR FOR select * from pg_database"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"DECLARE CURSOR command failed0); PQclear(res); exit_nicely(conn); } PQclear(res); res = PQexec(conn,"FETCH ALL in myportal"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr,"FETCH ALL command didn't return tuples properly0); PQclear(res); exit_nicely(conn); } /* first, print out the attribute names */ nFields = PQnfields(res); for (i=0; i < nFields; i++) { printf("%-15s",PQfname(res,i)); } printf("0); /* next, print out the instances */ for (i=0; i < PQntuples(res); i++) { for (j=0 ; j < nFields; j++) { printf("%-15s", PQgetvalue(res,i,j)); } printf("0); } PQclear(res); /* close the portal */ res = PQexec(conn, "CLOSE myportal"); PQclear(res); /* end the transaction */ res = PQexec(conn, "END"); PQclear(res); /* close the connection to the database and cleanup */ PQfinish(conn); /* fclose(debug); */ } /* * testlibpq2.c * Test of the asynchronous notification interface * populate a database with the following: CREATE TABLE TBL1 (i int4); CREATE TABLE TBL2 (i int4); CREATE RULE r1 AS ON INSERT TO TBL1 DO [INSERT INTO TBL2 values (new.i); NOTIFY TBL2]; * Then start up this program * After the program has begun, do INSERT INTO TBL1 values (10); * * */ #include #include "libpq-fe.h" void exit_nicely(PGconn* conn) { PQfinish(conn); exit(1); } main() { char *pghost, *pgport, *pgoptions, *pgtty; char* dbName; int nFields; int i,j; PGconn* conn; PGresult* res; PGnotify* notify; /* begin, by setting the parameters for a backend connection if the parameters are null, then the system will try to use reasonable defaults by looking up environment variables or, failing that, using hardwired constants */ pghost = NULL; /* host name of the backend server */ pgport = NULL; /* port of the backend server */ pgoptions = NULL; /* special options to start up the backend server */ pgtty = NULL; /* debugging tty for the backend server */ dbName = getenv("USER"); /* change this to the name of your test database*/ /* make a connection to the database */ conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); /* check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr,"Connection to database '%s' failed.0, dbName); fprintf(stderr,"%s",PQerrorMessage(conn)); exit_nicely(conn); } res = PQexec(conn, "LISTEN TBL2"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"LISTEN command failed0); PQclear(res); exit_nicely(conn); } /* should PQclear PGresult whenever it is no longer needed to avoid memory leaks */ PQclear(res); while (1) { /* async notification only come back as a result of a query*/ /* we can send empty queries */ res = PQexec(conn, " "); /* printf("res->status = %s0, pgresStatus[PQresultStatus(res)]); */ /* check for asynchronous returns */ notify = PQnotifies(conn); if (notify) { fprintf(stderr, "ASYNC NOTIFY of '%s' from backend pid '%d' received0, notify->relname, notify->be_pid); free(notify); break; } PQclear(res); } /* close the connection to the database and cleanup */ PQfinish(conn); } /* * testlibpq3.c * Test the C version of LIBPQ, the POSTGRES95 frontend library. * tests the binary cursor interface * * * populate a database by doing the following: CREATE TABLE test1 (i int4, d float4, p polygon); INSERT INTO test1 values (1, 3.567, '(3.0, 4.0, 1.0, 2.0)'::polygon); INSERT INTO test1 values (2, 89.05, '(4.0, 3.0, 2.0, 1.0)'::polygon); the expected output is: tuple 0: got i = (4 bytes) 1, d = (4 bytes) 3.567000, p = (4 bytes) 2 points boundbox = (hi=3.000000/4.000000, lo = 1.000000,2.000000) tuple 1: got i = (4 bytes) 2, d = (4 bytes) 89.050003, p = (4 bytes) 2 points boundbox = (hi=4.000000/3.000000, lo = 2.000000,1.000000) * */ #include #include "libpq-fe.h" #include "utils/geo-decls.h" /* for the POLYGON type */ void exit_nicely(PGconn* conn) { PQfinish(conn); exit(1); } main() { char *pghost, *pgport, *pgoptions, *pgtty; char* dbName; int nFields; int i,j; int i_fnum, d_fnum, p_fnum; PGconn* conn; PGresult* res; /* begin, by setting the parameters for a backend connection if the parameters are null, then the system will try to use reasonable defaults by looking up environment variables or, failing that, using hardwired constants */ pghost = NULL; /* host name of the backend server */ pgport = NULL; /* port of the backend server */ pgoptions = NULL; /* special options to start up the backend server */ pgtty = NULL; /* debugging tty for the backend server */ dbName = getenv("USER"); /* change this to the name of your test database*/ /* make a connection to the database */ conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); /* check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr,"Connection to database '%s' failed.0, dbName); fprintf(stderr,"%s",PQerrorMessage(conn)); exit_nicely(conn); } /* start a transaction block */ res = PQexec(conn,"BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"BEGIN command failed0); PQclear(res); exit_nicely(conn); } /* should PQclear PGresult whenever it is no longer needed to avoid memory leaks */ PQclear(res); /* fetch instances from the pg_database, the system catalog of databases*/ res = PQexec(conn,"DECLARE mycursor BINARY CURSOR FOR select * from test1"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"DECLARE CURSOR command failed0); PQclear(res); exit_nicely(conn); } PQclear(res); res = PQexec(conn,"FETCH ALL in mycursor"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr,"FETCH ALL command didn't return tuples properly0); PQclear(res); exit_nicely(conn); } i_fnum = PQfnumber(res,"i"); d_fnum = PQfnumber(res,"d"); p_fnum = PQfnumber(res,"p"); for (i=0;i<3;i++) { printf("type[%d] = %d, size[%d] = %d0, i, PQftype(res,i), i, PQfsize(res,i)); } for (i=0; i < PQntuples(res); i++) { int *ival; float *dval; int plen; POLYGON* pval; /* we hard-wire this to the 3 fields we know about */ ival = (int*)PQgetvalue(res,i,i_fnum); dval = (float*)PQgetvalue(res,i,d_fnum); plen = PQgetlength(res,i,p_fnum); /* plen doesn't include the length field so need to increment by VARHDSZ*/ pval = (POLYGON*) malloc(plen + VARHDRSZ); pval->size = plen; memmove((char*)&pval->npts, PQgetvalue(res,i,p_fnum), plen); printf("tuple %d: got0, i); printf(" i = (%d bytes) %d,0, PQgetlength(res,i,i_fnum), *ival); printf(" d = (%d bytes) %f,0, PQgetlength(res,i,d_fnum), *dval); printf(" p = (%d bytes) %d points boundbox = (hi=%f/%f, lo = %f,%f)0, PQgetlength(res,i,d_fnum), pval->npts, pval->boundbox.xh, pval->boundbox.yh, pval->boundbox.xl, pval->boundbox.yl); } PQclear(res); /* close the portal */ res = PQexec(conn, "CLOSE mycursor"); PQclear(res); /* end the transaction */ res = PQexec(conn, "END"); PQclear(res); /* close the connection to the database and cleanup */ PQfinish(conn); } 993/08/23 09:03:16 aoki Exp $ INTRODUCTION(LARGE OBJECTS) INTRODUCTION(LARGE OBJECTS) NAME SECTION 6 - LARGE OBJECTS DESCRIPTION In POSTGRES95, data values are stored in tuples and individual tuples can- not span data pages. Since the size of a data page is 8192 bytes, the upper limit on the size of a data value is relatively low. To support the storage of larger atomic values, POSTGRES95 provides a large object interface. This interface provides file-oriented access to user data that has been declared to be a large type. This section describes the implementation and the programmatic and query language interfaces to POSTGRES95 large object data. Originally, postgres 4.2 supports three standard implementations of large objects: as files external to POSTGRES95, as UNIX files managed by POSTGRES95, and as data stored within the POSTGRES95 database. It causes considerable confusion among users. As a result, we only support large objects as data stored within the POSTGRES95 database in POSTGRES95. Even though is is slower to access, it provides stricter data integrity and time travel. For historical reasons, they are called Inversion large objects. (We will use Inversion and large objects interchangeably to mean the same thing in this section.) The Inversion large object implementation breaks large objects up into "chunks" and stores the chunks in tuples in the database. A B-tree index guarantees fast searches for the correct chunk number when doing random access reads and writes. The facilities POSTGRES95 provides to access large objects, both in the backend as part of user-defined functions or the front end as part of an application using the interface, are described below. (For users familiar with postgres 4.2, POSTGRES95 has a new set of functions providing a more coherent interface. The interface is the same for dynamically-loaded C functions as well as for . The POSTGRES95 large object interface is modeled after the UNIX file system interface, with analogues of 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 existed that stored photographs of faces, then a function called could be declared on data. could look at the lower third of a photograph, and determine the color of the beard that appeared there, if any. The entire large object value need not be buffered, or even exam- ined, by the function. Large objects may be accessed from dynamically-loaded C functions or data- base client programs that link the LIBPQ library. POSTGRES95 provides a set of routines that support opening, reading, writing, closing, and seek- ing on large objects. The routine Oid lo_creat(PGconn *conn, int mode) creates a new large object. The is a bitmask describing several different attributes of the new object. The symbolic constants listed here are defined in /usr/local/postgres95/src/backend/libpq/libpq-fs.h The access type (read, write, or both) is controlled by ing together the bits and 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 bit should be set. The low-order sixteen bits of are the storage manager number on which the large object should reside. For sites other than Berkeley, these bits should always be zero. The commands below create an (Inversion) large object: inv_oid = lo_creat(INV_READ|INV_WRITE|INV_ARCHIVE); To import a UNIX file as a large object, call Oid lo_import(PGconn *conn, text *filename) The argument specifies the UNIX pathname of the file to be imported as a large object. To export a large object into UNIX file, call int lo_export(PGconn *conn, Oid lobjId, text *filename) The argument specifies the Oid of the large object to export and the argu- ment specifies the UNIX pathname of the file. To open an existing large object, call int lo_open(PGconn *conn, Oid lobjId, int mode, ...) The argument specifies the Oid of the large object to open. The mode bits control whether the object is opened for reading writing INV_WRITE ), ( or both. A large object cannot be opened before it is created. returns a large object descriptor for later use in and The routine int lo_write(PGconn *conn, int fd, char *buf, int len) writes bytes from to large object The argument must have been returned by a previous The number of bytes actually written is returned. In the event of an error, the return value is negative. To change the current read or write location on a large object, call int lo_lseek(PGconn *conn, int fd, int offset, int whence) This routine moves the current location pointer for the large object described by to the new location specified by The valid values for .i whence are and A large object may be closed by calling int lo_close(PGconn *conn, int fd) where is a large object descriptor returned by On success, returns zero. On error, the return value is negative. There are two built-in registered functions, and which are convenient for use in SQL queries. Here is an example of there use CREATE TABLE image ( name text, raster oid ); INSERT INTO image (name, raster) VALUES ('beautiful image', lo_import('/etc/motd')); SELECT lo_export(image.raster, "/tmp/motd") from image WHERE name = 'beautiful image'; Below is a sample program which shows how the large object interface in can be used. Parts of the program are commented out but are left in the source for the readers benefit. This program can be found in ../src/test/examples Frontend applications which use the large object interface in .PP should include the header file and link with the library. /*------------------------------------------------------------------------- * * testlo.c-- * test using large objects with libpq * * Copyright (c) 1994, Regents of the University of California * * * IDENTIFICATION * $Header: /usr/local/devel/pglite/cvs/src/doc/ref/large_objects.3pqsrc,v 1.1 1996/02/24 01:16:24 jolly Exp $ * *------------------------------------------------------------------------- */ #include #include "libpq-fe.h" #include "libpq/libpq-fs.h" #define BUFSIZE 1024 /* * importFile - * import file "in_filename" into database as large object "lobjOid" * */ Oid importFile(PGconn *conn, char *filename) { Oid lobjId; int lobj_fd; char buf[BUFSIZE]; int nbytes, tmp; int fd; /* * open the file to be read in */ fd = open(filename, O_RDONLY, 0666); if (fd < 0) { /* error */ fprintf(stderr, "can't open unix file } /* * create the large object */ lobjId = lo_creat(conn, INV_READ|INV_WRITE); if (lobjId == 0) { fprintf(stderr, "can't create large object"); } lobj_fd = lo_open(conn, lobjId, INV_WRITE); /* * read in from the Unix file and write to the inversion file */ while ((nbytes = read(fd, buf, BUFSIZE)) > 0) { tmp = lo_write(conn, lobj_fd, buf, nbytes); if (tmp < nbytes) { fprintf(stderr, "error while reading } } (void) close(fd); (void) lo_close(conn, lobj_fd); return lobjId; } void pickout(PGconn *conn, Oid lobjId, int start, int len) { int lobj_fd; char* buf; int nbytes; int nread; lobj_fd = lo_open(conn, lobjId, INV_READ); if (lobj_fd < 0) { fprintf(stderr,"can't open large object %d", lobjId); } lo_lseek(conn, lobj_fd, start, SEEK_SET); buf = malloc(len+1); nread = 0; while (len - nread > 0) { nbytes = lo_read(conn, lobj_fd, buf, len - nread); buf[nbytes] = ' '; fprintf(stderr,">>> %s", buf); nread += nbytes; } fprintf(stderr,"0); lo_close(conn, lobj_fd); } void overwrite(PGconn *conn, Oid lobjId, int start, int len) { int lobj_fd; char* buf; int nbytes; int nwritten; int i; lobj_fd = lo_open(conn, lobjId, INV_READ); if (lobj_fd < 0) { fprintf(stderr,"can't open large object %d", lobjId); } lo_lseek(conn, lobj_fd, start, SEEK_SET); buf = malloc(len+1); for (i=0;i 0) { nbytes = lo_write(conn, lobj_fd, buf + nwritten, len - nwritten); nwritten += nbytes; } fprintf(stderr,"0); lo_close(conn, lobj_fd); } /* * exportFile - * export large object "lobjOid" to file "out_filename" * */ void exportFile(PGconn *conn, Oid lobjId, char *filename) { int lobj_fd; char buf[BUFSIZE]; int nbytes, tmp; int fd; /* * create an inversion "object" */ lobj_fd = lo_open(conn, lobjId, INV_READ); if (lobj_fd < 0) { fprintf(stderr,"can't open large object %d", lobjId); } /* * open the file to be written to */ fd = open(filename, O_CREAT|O_WRONLY, 0666); if (fd < 0) { /* error */ fprintf(stderr, "can't open unix file filename); } /* * read in from the Unix file and write to the inversion file */ while ((nbytes = lo_read(conn, lobj_fd, buf, BUFSIZE)) > 0) { tmp = write(fd, buf, nbytes); if (tmp < nbytes) { fprintf(stderr,"error while writing filename); } } (void) lo_close(conn, lobj_fd); (void) close(fd); return; } void exit_nicely(PGconn* conn) { PQfinish(conn); exit(1); } int main(int argc, char **argv) { char *in_filename, *out_filename; char *database; Oid lobjOid; PGconn *conn; PGresult *res; if (argc != 4) { fprintf(stderr, "Usage: %s database_name in_filename out_filename0, argv[0]); exit(1); } database = argv[1]; in_filename = argv[2]; out_filename = argv[3]; /* * set up the connection */ conn = PQsetdb(NULL, NULL, NULL, NULL, database); /* check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr,"Connection to database '%s' failed.0, database); fprintf(stderr,"%s",PQerrorMessage(conn)); exit_nicely(conn); } res = PQexec(conn, "begin"); PQclear(res); printf("importing file /* lobjOid = importFile(conn, in_filename); */ lobjOid = lo_import(conn, in_filename); /* printf("as large object %d.0, lobjOid); printf("picking out bytes 1000-2000 of the large object0); pickout(conn, lobjOid, 1000, 1000); printf("overwriting bytes 1000-2000 of the large object with X's0); overwrite(conn, lobjOid, 1000, 1000); */ printf("exporting large object to file /* exportFile(conn, lobjOid, out_filename); */ lo_export(conn, lobjOid,out_filename); res = PQexec(conn, "end"); PQclear(res); PQfinish(conn); exit(0); } INTRODUCTION(SYSTEM CATALOGS) INTRODUCTION(SYSTEM CATALOGS) NAME SECTION 7 - SYSTEM CATALOGS DESCRIPTION In this section we list each of the attributes of the system catalogs and define their meanings. CLASS/TYPE SYSTEM CATALOGS These catalogs form the core of the extensibility system: nnaammee sshhaarreedd//llooccaall ddeessccrriippttiioonn pg_aggregate local aggregate functions pg_am local access methods pg_amop local operators usable with specific access methods pg_amproc local procedures used with specific access methods pg_attribute local class attributes pg_class local classes pg_index local secondary indices pg_inherits local class inheritance hierarchy pg_language local procedure implementation languages pg_opclass local operator classes pg_operator local query language operators pg_proc local procedures (functions) pg_type local data types ENTITIES These catalogs deal with identification of entities known throughout the site: nnaammee sshhaarreedd//llooccaall ddeessccrriippttiioonn pg_database shared current databases pg_group shared user groups pg_user shared valid users RULE SYSTEM CATALOGS nnaammee sshhaarreedd//llooccaall ddeessccrriippttiioonn pg_listener local processes waiting on alerters pg_prs2plans local instance system procedures pg_prs2rule local instance system rules pg_prs2stub local instance system ``stubs'' pg_rewrite local rewrite system information LARGE OBJECT CATALOGS These catalogs are specific to the Inversion file system and large objects in general: nnaammee sshhaarreedd//llooccaall ddeessccrriippttiioonn pg_lobj local description of a large object pg_naming local Inversion name space mapping pg_platter local jukebox platter inventory pg_plmap local jukebox platter extent map INTERNAL CATALOGS These catalogs are internal classes that are not stored as normal heaps and cannot be accessed through normal means (attempting to do so causes an error). nnaammee sshhaarreedd//llooccaall ddeessccrriippttiioonn pg_log shared transaction commit/abort log pg_magic shared magic constant pg_time shared commit/abort times pg_variable shared special variable values There are several other classes defined with "pg_" names. Aside from those that end in "ind" (secondary indices), these are all obsolete or otherwise deprecated. CLASS/TYPE SYSTEM CATALOGS The following catalogs relate to the class/type system. /* * aggregates * * see DEFINE AGGREGATE for an explanation of transition functions */ pg_aggregate NameData aggname /* aggregate name (e.g., "count") */ oid aggowner /* usesysid of creator */ regproc aggtransfn1 /* first transition function */ regproc aggtransfn2 /* second transition function */ regproc aggfinalfn /* final function */ oid aggbasetype /* type of data on which aggregate operates */ oid aggtranstype1 /* type returned by aggtransfn1 */ oid aggtranstype2 /* type returned by aggtransfn2 */ oid aggfinaltype /* type returned by aggfinalfn */ text agginitval1 /* external format of initial (starting) value of aggtransfn1 */ text agginitval2 /* external format of initial (starting) value of aggtransfn2 */ pg_am NameData amname /* access method name */ oid amowner /* usesysid of creator */ char amkind /* - deprecated */ /* originally: h=hashed o=ordered s=special */ int2 amstrategies /* total NUMBER of strategies by which we can traverse/search this AM */ int2 amsupport /* total NUMBER of support functions that this AM uses */ regproc amgettuple /* "next valid tuple" function */ regproc aminsert /* "insert this tuple" function */ regproc amdelete /* "delete this tuple" function */ regproc amgetattr /* - deprecated */ regproc amsetlock /* - deprecated */ regproc amsettid /* - deprecated */ regproc amfreetuple /* - deprecated */ regproc ambeginscan /* "start new scan" function */ regproc amrescan /* "restart this scan" function */ regproc amendscan /* "end this scan" function */ regproc ammarkpos /* "mark current scan position" function */ regproc amrestrpos /* "restore marked scan position" function */ regproc amopen /* - deprecated */ regproc amclose /* - deprecated */ regproc ambuild /* "build new index" function */ regproc amcreate /* - deprecated */ regproc amdestroy /* - deprecated */ pg_amop oid amopid /* access method with which this operator be used */ oid amopclaid /* operator class with which this operator can be used */ oid amopopr /* the operator */ int2 amopstrategy /* traversal/search strategy number to which this operator applies */ regproc amopselect /* function to calculate the operator selectivity */ regproc amopnpages /* function to calculate the number of pages that will be examined */ pg_amproc oid amid /* access method with which this procedure is associated */ oid amopclaid /* operator class with which this operator can be used */ oid amproc /* the procedure */ int2 amprocnum /* support function number to which this operator applies */ pg_class NameData relname /* class name */ oid relowner /* usesysid of owner */ oid relam /* access method */ int4 relpages /* # of 8KB pages */ int4 reltuples /* # of instances */ abstime relexpires /* time after which instances are deleted from non-archival storage */ reltime relpreserved /* timespan after which instances are deleted from non-archival storage */ bool relhasindex /* does the class have a secondary index? */ bool relisshared /* is the class shared or local? */ char relkind /* type of relation: i=index r=relation (heap) s=special u=uncatalogued (temporary) */ char relarch /* archive mode: h=heavy l=light n=none */ int2 relnatts /* current # of non-system attributes */ int2 relsmgr /* storage manager: 0=magnetic disk 1=sony WORM jukebox 2=main memory */ int28 relkey /* - unused */ oid8 relkeyop /* - unused */ aclitem relacl[1] /* access control lists */ pg_attribute oid attrelid /* class containing this attribute */ NameData attname /* attribute name */ oid atttypid /* attribute type */ oid attdefrel /* - deprecated */ int4 attnvals /* - deprecated */ oid atttyparg /* - deprecated */ int2 attlen /* attribute length, in bytes -1=variable */ int2 attnum /* attribute number >0=user attribute <0=system attribute */ int2 attbound /* - deprecated */ bool attbyval /* type passed by value? */ bool attcanindex /* - deprecated */ oid attproc /* - deprecated */ int4 attnelems /* # of array dimensions */ int4 attcacheoff /* cached offset into tuple */ bool attisset /* is attribute set-valued? */ pg_inherits oid inhrel /* child class */ oid inhparent /* parent class */ int4 inhseqno /* - deprecated */ oid indexrelid /* oid of secondary index class */ oid indrelid /* oid of indexed heap class */ oid indproc /* function to compute index key from attribute(s) in heap 0=not a functional index */ int28 indkey /* attribute numbers of key attribute(s) */ oid8 indclass /* opclass of each key */ bool indisclustered /* is the index clustered? - unused */ bool indisarchived /* is the index archival? - unused */ text indpred /* query plan for partial index predicate */ pg_type NameData typname /* type name */ oid typowner /* usesysid of owner */ int2 typlen /* length in internal form -1=variable-length */ int2 typprtlen /* length in external form */ bool typbyval /* type passed by value? */ char typtype /* kind of type: c=catalog (composite) b=base */ bool typisdefined /* defined or still a shell? */ char typdelim /* delimiter for array external form */ oid typrelid /* class (if composite) */ oid typelem /* type of each array element */ regproc typinput /* external-internal conversion function */ regproc typoutput /* internal-external conversion function */ regproc typreceive /* client-server conversion function */ regproc typsend /* server-client conversion function */ text typdefault /* default value */ pg_operator NameData oprname /* operator name */ oid oprowner /* usesysid of owner */ int2 oprprec /* - deprecated */ char oprkind /* kind of operator: b=binary l=left unary r=right unary */ bool oprisleft /* is operator left/right associative? */ bool oprcanhash /* is operator usable for hashjoin? */ oid oprleft /* left operand type */ oid oprright /* right operand type */ oid oprresult /* result type */ oid oprcom /* commutator operator */ oid oprnegate /* negator operator */ oid oprlsortop /* sort operator for left operand */ oid oprrsortop /* sort operator for right operand */ regproc oprcode /* function implementing this operator */ regproc oprrest /* function to calculate operator restriction selectivity */ regproc oprjoin /* function to calculate operator join selectivity */ pg_opclass NameData opcname /* operator class name */ pg_proc NameData proname /* function name */ oid proowner /* usesysid of owner */ oid prolang /* function implementation language */ bool proisinh /* - deprecated */ bool proistrusted /* run in server or untrusted function process? */ bool proiscachable /* can the function return values be cached? */ int2 pronargs /* # of arguments */ bool proretset /* does the function return a set? - unused */ oid prorettype /* return type */ oid8 proargtypes /* argument types */ int4 probyte_pct /* % of argument size (in bytes) that needs to be examined in order to compute the function */ int4 properbyte_cpu /* sensitivity of the function's running time to the size of its inputs */ int4 propercall_cpu /* overhead of the function's invocation (regardless of input size) */ int4 prooutin_ratio /* size of the function's output as a percentage of the size of the input */ text prosrc /* function definition (postquel only) */ bytea probin /* path to object file (C only) */ pg_language NameData lanname /* language name */ text lancompiler /* - deprecated */ ENTITIES pg_database NameData datname /* database name */ oid datdba /* usesysid of database administrator */ text datpath /* directory of database under $PGDATA */ pg_group NameData groname /* group name */ int2 grosysid /* group's UNIX group id */ int2 grolist[1] /* list of usesysids of group members */ pg_user NameData usename /* user's name */ int2 usesysid /* user's UNIX user id */ bool usecreatedb /* can user create databases? */ bool usetrace /* can user set trace flags? */ bool usesuper /* can user be POSTGRES superuser? */ bool usecatupd /* can user update catalogs? */ RULE SYSTEM CATALOGS pg_listener NameData relname /* class for which asynchronous notification is desired */ int4 listenerpid /* process id of server corresponding to a frontend program waiting for asynchronous notification */ int4 notification /* whether an event notification for this process id still pending */ pg_prs2rule NameData prs2name /* rule name */ char prs2eventtype /* rule event type: R=retrieve U=update (replace) A=append D=delete */ oid prs2eventrel /* class to which event applies */ int2 prs2eventattr /* attribute to which event applies */ float8 necessary /* - deprecated */ float8 sufficient /* - deprecated */ text prs2text /* text of original rule definition */ pg_prs2plans oid prs2ruleid /* prs2rule instance for which this plan is used */ int2 prs2planno /* plan number (one rule may invoke multiple plans) */ text prs2code /* external representation of the plan */ pg_prs2stub oid prs2relid /* class to which this rule applies */ bool prs2islast /* is this the last stub fragment? */ int4 prs2no /* stub fragment number */ stub prs2stub /* stub fragment */ pg_rewrite NameData rulename /* rule name */ char ev_type /* event type: RETRIEVE, REPLACE, APPEND, DELETE codes are parser-dependent (!?) */ oid ev_class /* class to which this rule applies */ int2 ev_attr /* attribute to which this rule applies */ bool is_instead /* is this an "instead" rule? */ text ev_qual /* qualification with which to modify (rewrite) the plan that triggered this rule */ text action /* parse tree of action */ LARGE OBJECT CATALOGS pg_lobj oid ourid /* 'ourid' from pg_naming that identifies this object in the Inversion file system namespace */ int4 objtype /* storage type code: 0=Inversion 1=Unix 2=External 3=Jaquith */ bytea object_descripto/* opaque object-handle structure */ pg_naming NameData filename /* filename component */ oid ourid /* random oid used to identify this instance in other instances (can't use the actual oid for obscure reasons */ oid parentid /* pg_naming instance of parent Inversion file system directory */ pg_platter NameData plname /* platter name */ int4 plstart /* the highest OCCUPIED extent */ pg_plmap oid plid /* platter (in pg_platter) on which this extent (of blocks) resides */ oid pldbid /* database of the class to which this extent (of blocks) belongs */ oid plrelid /* class to which this extend (of blocks) belongs */ int4 plblkno /* starting block number within the class */ int4 ploffset /* offset within the platter at which this extent begins */ int4 plextentsz /* length of this extent */ INFORMATION(FILES) INFORMATION(FILES) NAME SECTION 8 - FILES OVERVIEW This section describes some of the important files used by POSTGRES95. 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 expres- sions. || 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. BKI(FILES) 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 POSTGRES95 database. This database is constructed during system installation, by the _i_n_i_t_d_b command. _I_n_i_t_d_b executes the POSTGRES95 back- end 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 POSTGRES95. 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 POSTGRES95 constructs from the BKI files. Thus, a simple way to cus- tomize the template database is to let the POSTGRES95 initialization script create it for you, and then to run the terminal monitor to make the changes you want. The POSTGRES95 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 argu- ments. Arguments to a command which begin with a "$" are treated spe- cially. 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 charac- ters 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. GENERAL COMMANDS ooppeenn classname Open the class called _c_l_a_s_s_n_a_m_e for further manipulation. cclloossee [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. pprriinntt Print the currently open class. iinnsseerrtt [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 iden- tifier. Otherwise, it is an error. iinnsseerrtt (( value1 value2 ... )) As above, but the system generates a unique object identifier. ccrreeaattee classname (( name1 = type1, name2 = type2, ... )) Create a class named _c_l_a_s_s_n_a_m_e with the attributes given in parentheses. ooppeenn (( name1 = type1, name2 = type2,... )) aass 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.) ddeessttrrooyy classname Destroy the class named _c_l_a_s_s_n_a_m_e. ddeeffiinnee iinnddeexx index-name oonn class-name uussiinngg 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 ddeeffiinnee ffuunnccttiioonn macro_name aass 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. ddeeffiinnee mmaaccrroo macro_name ffrroomm ffiillee 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. EXAMPLE The following set of commands will create the "pg_opclass" class containing 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). PG_HBA(FILES) PG_HBA(FILES) NAME $PGDATA/pg_hba - host-based access control DESCRIPTION POSTGRES95 provides host-based access control through the file ppgg__hhbbaa. This file is stored in $PGDATA and should have proper file permissions so that casual users cannot modify it. The file stores permissions informa- tion about which hosts are authorized to access databases. The file consists of lines of three columns. The first column lists the name of the database. The second column lists the IP address of clients machines that are authorized to connect, and the third column provides a bit mask for the IP address. Lines which begin with '#' are considered comments. The keyword "all" can be used to apply to all database names. Each column can contain strings of at most 80 characters, and each database may have up to 255 configuration lines that apply. (80 and 255 are constants that can be changed at POSTGRES95 compile time by altering the #defines MAX_TOKEN and MAX_LINES in src/backend/libpq/auth.c) Below is the default pg_hba file that is installed. # # Example config file for Postgres95 host based access # # Lines starting with "all" apply to all databases. Otherwise the first # column has to match the name of the database being connected to. Up to # ten config lines can apply to each database. Mask specifies bits that # aren't counted. After those bits are taken out, the connection address # must match the address in the middle column. # #
# all 127.0.0.1 0.0.0.0 SEE ALSO introduction(unix). PAGE(FILES) PAGE(FILES) NAME page structure - POSTGRES95 database file default page format DESCRIPTION This section provides an overview of the page format used by POSTGRES95 classes. User-defined access methods need not use this page format. In the following explanation, a bbyyttee is assumed to contain 8 bits. In addition, the term iitteemm refers to data which is stored in POSTGRES95 classes. The first 8 bytes of each page consists of a page header (PPaaggeeHHeeaaddeerrDDaattaa). 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 allocated at page ini- tialization 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 fragmentation 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 (IItteemmIIddDDaattaa). 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 (IItteemmPPooiinntteerr) created by POSTGRES95 consists 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. However when the item is too long to be placed on a single page or when fragmenta- tion 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 ((ItemContinuation- Data)).. 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 handled normally. FILES .../data/... Location of shared (global) database files. .../data/base/... Location of local database 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 writ- ing a new access method. TEMPLATE(FILES) TEMPLATE(FILES) TEMPLATE(FILES) TEMPLATE(FILES) 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 POSTGRES95 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 data- bases. Consequently, you can use the terminal monitor or some other fron- tend on a template database to simplify the customization task. That is, there is no need to express everything about your desired initial 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 databases 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) catalog 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 com- mand, 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 POSTGRES95 Version 1.01 does not permit users to have separate template databases. SEE ALSO bki(files), initdb(unix), createdb(unix). REFERENCES(MANUAL) REFERENCES(MANUAL) 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 POSTGRES95 distribution. [ONG90] Ong, L. and Goh, J., "A Unified Framework for Version Model- ing Using Production Rules in a Database System," Electron- ics Research Laboratory, University of California, Berkeley, ERL Memo M90/33, April 1990. [ROWE87] Rowe, L. and Stonebraker, M., "The POSTGRES Data Model," Proc. 1987 VLDB Conference, Brighton, England, Sept. 1987. [SHAP86] Shapiro, L., "Join Processing in Database Systems with Large Main Memories," ACM-TODS, Sept. 1986. [STON87] Stonebraker, M., "The POSTGRES Storage System," 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., "Decomposition: A Strategy for Query Processing," ACM-TODS, Sept. 1976. NAME Table of Contents