PPOOSSTTGGRREESS VVeerrssiioonn 44..22 RReelleeaassee NNootteess 2222::1199::4444 11.. IInnttrroodduuccttiioonn These are the release notes for version 4.2 of the POSTGRES database system from the University of California at Berkeley. The database system and its installation procedure are covered in detail in the setup document for this release, which can be found in the file ddoocc//ppoossttggrreess-- sseettuupp..{{ccaatt,,ppss}}. Here, we cover only the most important differences from release 4.1 and earlier versions of the system. 22.. AAiimm This is the last release of POSTGRES from the POSTGRES group at Berkeley. This release supports a few more plat- forms than the previous release, has a few more features, has many bug fixes, and has a bit better documentation. The aim was to increase the reliability as much as possi- ble before closing up shop, while providing a few more features that some of our users needed. 33.. CCoommppaattiibbiilliittyy WWiitthh EExxiissttiinngg 44..11 DDaattaabbaasseess There have been some changes to the the system catalogs since POSTGRES Version 4.1. Because of this, if you want to migrate your data from Version 4.1 you must copy all your databases out to flat ASCII files and then reload them into the 4.2 database directory. See the ccooppyy com- mand in the reference manual for details about how to copy data out of a class into a flat ASCII file. Note that this procedure does not preserve rules or large objects; rules must simply be redefined and large objects copied out separately. 44.. NNeeww PPllaattffoorrmmss This release supports the following platforms in addi- tion to the Sun SparcStation and Ultrix DECstation 3100/5000 ports already supported in the past: H-P 9000 Series 700/800 (PA-RISC) on HP-UX 9.00 or 9.01 DECstation 3000 (Alpha AXP) on DEC OSF/1 1.3 IBM RS/6000 (POWER) on AIX 3.2.5 PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 22 2222::1199::4444 55.. NNeeww FFeeaattuurreess This section highlights some of the new features. For complete documentation of these features refer to the POSTGRES User Manual and Reference Manual in the appropri- ate sections. 55..11.. FFuunnccttiioonn OOvveerrllooaaddiinngg Function overloading is now allowed. That is, the same function name may be used for functions that take different argument lists. The syntax for ddeeffiinnee ffuunncc-- ttiioonn is still the same; however, the rreemmoovvee ffuunnccttiioonn command has changed slightly. The argument types must now be specified, as in rreemmoovvee ffuunnccttiioonn ddiissttaannccee((ppooiinntt,, ppooiinntt)) Note that the restriction that dynamically-loaded C functions must have the same C name and POSTGRES name has not been removed, which limits the usefulness of this feature to the extent that duplicate symbol names cause dynamic loading problems on some systems. The main restriction we know about is that you can't define a dynamically-loaded function with the same name as a function that's already built into POSTGRES under OSF/1. This and most other examples involving duplicate func- tion names should work more-or-less as expected on other operating systems. 55..22.. AAuuttoommaattiicc CCoonnssttaanntt CCooeerrcciioonn In most cases, constants are cast to the correct type as long as there is no ambiguity and the explicit type- cast "::type" should not have to be used quite as often. However, additional typecasts may become necessary in a few cases due to the removal of some undocumented, buggy and inconsistent default typecasting (mostly having to do with cchhaarr1166 and tteexxtt). In addition, there are a few commands (notably ddeeffiinnee ffuunnccttiioonn) where parsing is han- dled in special (broken) ways and type coercion doesn't work correctly. 55..33.. TTiimmee DDaattaa TTyyppee CChhaannggeess Times prior to January 1, 1970 are now allowed. In addition, the special abstime values iinnffiinniittyy, --iinnffiinn-- iittyy, and ccuurrrreenntt have been added. The new built-in function mmkkttiinntteerrvvaall takes two abstime arguments and returns the ttiinntteerrvvaall with those times as its endpoints. PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 33 2222::1199::4444 55..44.. HHaasshh AAcccceessss MMeetthhoodd This release of POSTGRES includes a hashed access method for creating secondary indices. The POSTGRES query optimizer will consider using a hashed index when- ever an indexed attribute is involved in an equality comparison. The operator classes defined on hash indices are: iinntt22__ooppss ffllooaatt88__ooppss cchhaarr22__ooppss cchhaarr1166__ooppss iinntt44__ooppss ooiidd__ooppss cchhaarr44__ooppss tteexxtt__ooppss.. ffllooaatt44__ooppss cchhaarr__ooppss cchhaarr88__ooppss The syntax is similar to that for creating a B-tree or R-tree index. For example, the following POSTQUEL com- mand defines an index on the class EEMMPP using the salary attribute: ddeeffiinnee iinnddeexx EEMMPP__SSAALLAARRIIEESS oonn eemmpp uussiinngg hhaasshh ((ssaallaarryy iinntt44__ooppss)) Hashing of large objects is not supported. 55..55.. EExxtteerrnnaall LLaarrggee OObbjjeeccttss A user may now access files external to the database by the same mechanisms employed for private large objects. Application writers using the LIBPQ interface can now issue a call ffdd == pp__ccrreeaattee((ppaatthhnnaammee,, mmooddee,, EExxtteerrnnaall)) to "import" an external file into the database frame of reference. The other calls, such as pp__ooppeenn, pp__rreeaadd and pp__wwrriittee, continue to work exactly as before on "Unix" or "Inversion" files but will now work also on other files imported in this fashion. External files may be accessed according to common permissions of both the user for whom the backend is running and the backend's user id itself. Plainly, if you do not grant the post- gres uid access to your files, you cannot pp__ooppeenn or pp__ccrreeaatt them. Importing a file will automatically create shadow directory entries in the Inversion file system, and the existence of an Inversion file (as regular file) which has the same pathname as an intervening host system directory will abort the inclusion. However, doing a pp__uunnlliinnkk operation (or the pprrmm command) will not actu- ally remove the host system file; it will only make the database forget about its existence. PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 44 2222::1199::4444 We have made this extension configurable by the com- pile time option --DDEEXXTTEERRNNAALL__LLOO because it represents some security risk. If an application connects to a backend on a remote system masquerading as a user on that system, the application gains access to files according to the constraints mentioned above. Malicious applications could clobber database system catalogs, or obtain copies of the password file for cracking pur- poses, for example. An even greater level of risk is exposed if your com- puter is set up in such a way that allows outside users to place a file where the POSTGRES backend could load it as a user-defined function, as the user-defined function could issue direct calls to rreeaadd and wwrriittee and circum- vent even the additional checks imposed by the external large object system. Users who have no account on your machine may be able to place files in your file system using any number of mechanisms, including anonymous FTP and possibly aauuttoommoouunntt and aalleexx. POSTGRES should always be run with the Kerberos net- work authentication system turned on wherever external large objects are to be accessed or user-defined func- tions may be loaded. 55..66.. AArrrraayy CChhaannggeess This version provides a flexible support for multi- dimensional arrays of any base type. It supports two kinds of arrays: "large object arrays" that are stored as postgres large objects and "small arrays" that are stored in the same page as the tuple. The inclusion of large object arrays means that arrays can span multiple pages. The implementation allows update and retrieval of single array elements or sub parts of the array. See the reference manual under ccrreeaattee, aappppeenndd, rreettrriieevvee and rreeppllaaccee for details. For optimizing the retrieval of large arrays we pro- vide optional support for chunking the array as described in the paper [SARA94]. A copy of the paper is available with the source in ssrrcc//ddoocc//ppaappeerrss//aarrrraayyss//ppaappeerr..ppss. [SARA94] S. Sarawagi and M. Stonebraker, "Efficient Organi- zation of Large Multidimensional arrays," _P_r_o_c_. _T_e_n_t_h _I_n_t_. _C_o_n_f_e_r_e_n_c_e _o_n _D_a_t_a _E_n_g_i_n_e_e_r_i_n_g, Feb. 1994. PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 55 2222::1199::4444 55..77.. AAssyynncchhrroonnoouuss PPoorrttaallss This release has support for asynchronous portals. See the section in the reference manual under LIBPQ entitled "ASYNCHRONOUS PORTALS AND NOTIFICATION". 55..88.. DDyynnaammiicc LLooaaddeerr CChhaannggeess The user defined function facility has been changed to use the common ddllooppeenn interface available on most systems to support shared libraries. For the Ultrix platform (which does not have a ddllooppeenn interface), a version has been provided in ssrrcc//ttoooollss//lliibbddll. See the installation notes for details. 55..99.. EErrrroorr MMeessssaaggee CClleeaannuuppss An attempt was made to change some of the more arcane error messages into something more useful. 66.. DDooccuummeennttaattiioonn An attempt was made to document how major subsystems in POSTGRES work. This will be of some help to people attempting to modify (or debug) parts of POSTGRES inter- nals. The document is available in ssrrcc//ddoocc//iimmpplleemmeennttaattiioonn. We cannot guarantee that this document is either complete or entirely accurate (as the code may have changed even since the writing of the doc) but it should nevertheless be very useful to someone attempting to understand the code. The document on the access method interface (aamm..mmee) is particularly useful. In addition to the internals documentation, a pass was made over the User Manual and Reference Manual to clean things up. The User Manual is now more than twice as long and contains important information about creation of dynamically-loaded object code. We have actually tried all of the POSTQUEL queries. 77.. BBuugg FFiixxeess aanndd OOvveerrdduuee IImmpprroovveemmeennttss 77..11.. PPoorrttaabbiilliittyy IImmpprroovveemmeennttss Substantial effort has been invested in fixing bad prototypes and making the code more portable. It should now be easier to build POSTGRES using pedantic compilers such as ggcccc (on CISC platforms, anyway, for alignment reasons) and it is somewhat less dependent on non-POSIX library routines. PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 66 2222::1199::4444 77..22.. UUsseerr PPrrooggrraammss icopy +o Defaulted to Unix large objects instead of the docu- mented Inversion large objects. initdb +o Now runs vvaaccuuuumm on the template database by default. This makes queries on the system catalogs much faster. monitor +o Silently truncated ccooppyy iinn and ccooppyy oouutt lines at 100 characters. newbki +o Changed the POSTGRES superuser's UID, but did not change the name of the POSTGRES superuser if no user named "postgres" existed. pcat +o Treated command-line switches as filenames as well as switches. pmkdir +o Would often fail while executing pp__ssttaatt on files and directories. postmaster +o Would occasionally leak shared memory segments or exit while attempting to reacquire shared memory after reinitialization. (This appears to be fixed, feedback is desired.) +o Never detached from its original controlling termi- nal, resulting in error messages being printed to that tty. Now has a command-line option (-S) that causes it to detach and operate silently. reindexdb +o Now provided to assist in disaster recovery. See the Reference Manual. vacuum +o Now works. (The POSTQUEL command worked, but the script didn't.) 77..33.. CCoonnttrriibbuutteedd PPrrooggrraammss Most of the stuff that couldn't be compiled anymore (because it required out-of-date external software) or is currently being maintained outside Berkeley has been PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 77 2222::1199::4444 removed. The most current version of ttkkmmoonniittoorr, a Tcl/Tk-based replacement for the terminal monitor, is incorporated in the Lassen information retrieval system and can be retrieved as: ppuubb//sseeqquuooiiaa//ssrrcc//llaasssseenn..ttaarr..ZZ from s2k-ftp.CS.Berkeley.EDU (128.32.149.157). The lat- est version of ppggbbrroowwssee, a Tcl/Tk-based tool for query- ing a POSTGRES database that contains hooks for user extensions, is located in: ppuubb//ppggbbrroowwssee on crseo.ucsb.edu (128.111.100.50). 77..44.. GGeenneerraall BBuugg FFiixxeess +o A pass was made through the code to find and fix places where cchhaarr1166 was improperly used. Identifiers that are actually 16 characters long are more likely to work. +o Several serious memory leaks were fixed, increasing the number of queries that can be run in a single transaction. 77..55.. AAcccceessss MMeetthhooddss B-tree +o Insertion of a new minimal (lowest) value would fail under certain circumstances, causing queries that used the index to return incorrect results. Tuples would be visible using a non-index scan (e.g., one with no qualification) but would not be returned by a query with a qualification that caused an index scan. R-tree +o Variable-length index keys (e.g., polygons) didn't work at all. +o The calculation for estimating the growth of a bound- ing box corresponding to the union of two bounding boxes produced garbage values, leading to poor tree layout. +o Use of uninitialized variables caused several prob- lems (e.g., nested-loop joins with an R-tree on the inner relation crashed). 77..66.. QQuueerryy PPaarrsseerr +o Negative iinntt22 and ffllooaatt44 constants should now work normally, without being entered like: PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 88 2222::1199::4444 ""--33..1144115599""::::ffllooaatt44 +o Queries that contained multiple commands, such as those specified when defining new rules or POSTQUEL functions, would sometimes have their "from" clauses parsed incorrectly. 77..77.. QQuueerryy EExxeeccuuttoorr +o Now attempts to do a better job of detecting non- functional updates (repeated updates to the same tuple in the same transaction). +o Failed to do adequate pinning of buffers, meaning that buffers still in use could be flushed to disk and reused. This caused corruption of relations. This occurred not only in multiuser operation but also when certain operations were performed on large relations (e.g., mergejoin). +o rreeppllaaccee queries now acquire write locks before begin- ning their read operations, reducing concurrency somewhat but preventing many bogus deadlocks. +o Projecting more than one attribute from the result of a POSTQUEL function would crash the system if the attributes were of different types. 77..88.. QQuueerryy OOppttiimmiizzeerr +o Due to a botch in the system catalogs, the optimizer would never use an index on an ooiidd attribute of the inner join relation. +o The optimizer generated invalid query plans if exis- tential qualifications (clauses that are totally dis- connected from relations that are actually retrieved in the target list) were joined by an "or" clause. This caused the executor to crash. +o Various optimizer cost functions causes floating point underflow and divide-by-zero, which works bet- ter on some machines than others. +o Fixed various memory management bugs in the optimiza- tion of expensive functions. +o Archival (time travel) queries on a relation crashed the optimizer if you modified the schema of a rela- tion (e.g., using aaddddaattttrr). +o Applying "not" to the result of a function crashed the optimizer. 77..99.. SSyysstteemm CCaattaallooggss +o ppgg__ccllaassss..rreellnnaattttss was wrong for some system catalogs. +o ppgg__ggrroouupp is now correctly created at initialization time. PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 99 2222::1199::4444 +o Many function/operator entries contained invalid argument types. For example, the >> operator between iinntt22 and iinntt44 was not usable. +o The system thought it could hashjoin using the !!== operator for the bbooooll type, sometimes leading to incorrect results when joining bbooooll attributes. 77..1100.. PPOOSSTTQQUUEELL UUttiilliittiieess addattr +o Now accepts a ** option. Changing only a superclass in a hierarchy without changing the children caused traversals of the inheritance hierarchy to crash. define aggregate +o Now does substantially more error-checking. +o Current operation is closer to the definition used in 4.0.1. This is not what was originally designed or documented, but (for example) allows the aggregates used in the Sequoia 2000 benchmark to be defined again. define operator +o Made incorrect system catalog entries for unary oper- ators. extend index +o Consecutive eexxtteenndds didn't work. load +o No longer requires absolute pathnames. +o Object files that contained symbol names longer than 16 characters would often not be correctly loaded. purge +o Now does something besides crash. See the Reference Manual. rename +o Would allow you to rename your classes with the pre- fix ppgg__, which then prevented other operations (such as destroying them). +o Now accepts a ** option. Changing only a superclass in a hierarchy without changing the children caused traversals of the inheritance hierarchy to crash. 77..1111.. TTrraannssaaccttiioonn SSyysstteemm +o Multiple versions of the same tuple would become vis- ible during an update transaction. PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 1100 2222::1199::4444 +o The following error was caused by a race condition that has been fixed: NNOOTTIICCEE::JJaann 2200 1100::2266::3355::LLoocckkRReeppllaaccee:: xxiidd ttaabbllee ccoorrrruupptteedd +o Backends that aborted due to deadlock timeouts would often corrupt shared memory, causing other running backends to crash. +o If one backend sat idle while another backend per- formed many commands that modified the system cata- logs, the mostly-idle backend would (in some circum- stances) crash when it tried to begin a new transac- tion due to a bug in the shared cache invalidation code. 77..1122.. SSttoorraaggee SSyysstteemm +o Race conditions within the buffer manager would cause lost updates. +o On SunOS 4, the backend would crash after about 60 files had been opened due to a miscalculation of the number of available file descriptors. 77..1133.. IInnvveerrssiioonn FFiillee SSyysstteemm//LLaarrggee OObbjjeeccttss +o The current Inversion working directory was not always initialized in the frontend library routines. +o Appending 0-4 bytes using the pp__wwrriittee interface failed, sometimes causing crashes. +o Executing pp__ssttaatt on an empty Inversion file caused the system to crash. 77..1144.. LLIIBBPPQQ +o No longer calls eexxiitt for most errors, returning "R" and "E" (or NNUULLLL, depending on the documented inter- face) more often. +o Now sets PPQQEErrrroorrMMssgg more consistently. +o In the absence of authentication, now uses ggeettuuiidd to figure out who the user is instead of the USER envi- ronment variable. +o PPQQeexxeecc sometimes returned a pointer to an automatic variable as its return value. This would sometimes cause the frontend application to crash or get garbage. +o The number of open portals now grows dynamically (instead of being hardcoded to a maximum of 10). +o Retrieval of variable-length attributes through binary portals would return objects that were four bytes too large. This was often relatively innocuous unless the user code estimated values from the size of the variable-length attribute or the out-of-bounds PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 1111 2222::1199::4444 read in the server happened to cause a segmentation violation. 77..1155.. AAbbssttrraacctt DDaattaa TTyyppeess +o Error-checking in input/output routines has been improved in general. +o Insufficient space was allocated for many output rou- tines, causing mysterious errors when out-of-bounds writes occurred. +o The time types were overhauled. For example, time subtraction didn't work. +o Polygons with 0 points were not correctly handled in many routines. 88.. KKnnoowwnn BBuuggss aanndd PPrroobblleemmss There are several known bugs that we did not fix in this release. Note that unimplemented features and limi- tations in existing implementations are, for the most part, documented in the Reference Manual rather than here. A list of known bugs and suggested work-arounds (as we find them) will be made available for anonymous FTP. This list will be kept in the file ppuubb//ppoossttggrreess//ppoossttggrreess--vv44rr22//bbuuggss on s2k-ftp.CS.Berkeley.EDU (128.32.149.157). 88..11.. UUsseerr PPrrooggrraammss shmemdoc +o This program has become out of date and should not be used. It is provided in case someone needs the func- tionality so badly that they are willing to fix it. 88..22.. QQuueerryy EExxeeccuuttoorr +o The current implementation of hash joins in POSTGRES attempts to put the entire hash table in virtual mem- ory. If the hash table is too big to fit into memory the transaction will be aborted. The planner tries to take relation size into account when deciding whether or not to plan a hash join, but it is depen- dent on the most recent database statistics. If these are out of date POSTGRES might still exhibit this unfriendly behavior. To avoid this problem you should vvaaccuuuumm your database after any and all large aappppeenndd or ccooppyy commands. PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 1122 2222::1199::4444 +o If a backend fails while in the course of executing a rreettrriieevvee iinnttoo query, a spurious file, with the same name as the target class of the rreettrriieevvee iinnttoo, will be left in the database directory. This file can be safely deleted by the database DBA. 88..33.. CCrraasshh RReeccoovveerryy +o This isn't really so much a bug as a warning. POST- GRES does not implement standard crash recovery tech- niques such as write-ahead logging. The POSTGRES storage architecture permits fast crash recovery in the sense that the system can restart instantly with- out having to go through a lengthy analysis of disk blocks and a log file. However, POSTGRES requires that the data still be readable. This implies that if (1) a bug in POSTGRES scrambles the contents of a relation or (2) a disk block goes bad, there is no way of fixing the relation. You may be able to read some of the contents out or you may not (for example, if one of the shared relations goes bad, the entire database may be unusable). Do frequent offline back- ups. You are warned. 88..44.. TTrraannssaaccttiioonn SSyysstteemm +o There is a subtle bug relating to cache invalidation that can cause POSTGRES to violate transaction seman- tics in transactions/queries involving multiple com- mands. If the backend has a relation open that needs to be invalidated when one command is finished the invalidation message is ignored. Thus the relation descriptor can become out of date, and won't be updated until the next time it is invalidated. At the time of writing we believe that the odds you will ever notice this bug are small. 88..55.. RRuullee SSyysstteemm +o As ever, the instance rule system essentially ignores indices, so if you are defining a rule on an indexed attribute, you should use the query rewrite rule sys- tem. +o The instance rule system does not handle array refer- ences correctly. Don't use arrays in rules. 88..66.. IInnvveerrssiioonn FFiillee SSyysstteemm//LLaarrggee OObbjjeeccttss +o If a backend fails while it is manipulating large objects, spurious large object files will be left in the database directory. Also, there is no mechanism for getting rid of large objects which are returned PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 1133 2222::1199::4444 by functions but not stored in instances. +o Attempting to overwrite Inversion large objects doesn't work reliably. This is due to a major design flaw and hasn't been fixed. If you need rewritable large objects, you must use Unix large objects. 88..77.. LLIIBBPPQQ +o Again, this is more of a warning than a bug. Several mysterious-looking errors, such as: WWAARRNN::NNoovv 44 1144::4499::0099::iinniitt__ffccaacchhee:: CCaacchhee llooookkuupp ffaaiilleedd ffoorr pprroocceedduurree 00 are occasionally caused by a POSTQUEL command return- ing more return values than the frontend application expects. (In other words, the frontend and backend get out-of-sync in the protocol.) See the LIBPQ sec- tion of the Reference Manual under PPQQFFlluusshhII for details. 88..88.. AAbbssttrraacctt DDaattaa TTyyppeess +o While input/output routines have improved error- checking, many numeric operators and functions do not check for underflow, overflow and other exceptions. 99.. MMaacchhiinnee--DDeeppeennddeenntt CCaavveeaattss 99..11.. DDEECC OOSSFF//11 +o As previously discussed, you should not define a dynamically-loaded C function with the same name as a function that is already built into the POSTGRES server. Any calls to such a function will actually call the POSTGRES built-in function. 99..22.. HHPP--UUXX +o We could not test the H-P port on HP-UX 8.07 because all of the machines to which we had access were upgraded to HP-UX 9.0. However, the system did work prior to that point. If you find that some minor changes are required to build the system under 8.07, please let us know and we will archive your patch file on our FTP server. +o The standard HP-UX C compiler does not have the -M (make dependencies) option. If you want dependen- cies, you will need to install the GNU C compiler, ggcccc. (That is, if you attempt to build dependencies, the mmkkddeepp script will try to invoke ggcccc). PPOOSSTTGGRREESS VV44..22 RReelleeaassee NNootteess 1144 2222::1199::4444 +o HP-UX does not provide a version of the BSD iinnssttaallll program. We have provided a copy of the bbssddiinnsstt script from the MIT X11R5 distribution, which works acceptably. +o The HP-UX library doesn't handle timezones on dates before Jan. 1, 1970 correctly. +o Alpha-test users have reported that it's not hard to overflow the HP-UX kernel file table on a busy machine. Each POSTGRES server can use up to NNOOFFIILLEE (60) file descriptors. If you find a workaround or a kernel parameter that works acceptably for you, again, let us know and we'll pass on the information. 99..33.. SSuunnOOSS 44 +o The SunOS library doesn't handle timezones on dates before Jan. 1, 1970 correctly.