PPOOSSTTGGRREESS VVeerrssiioonn 44..00..11 RReelleeaassee NNootteess 11999922//0088//2266 11.. IInnttrroodduuccttiioonn These are the release notes for version 4.0.1 of the POSTGRES database system from UC 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 ~postgres/doc/postgres-setup.me. Here, we cover only the most important differences from release 4.0 and earlier versions of the system. 22.. AAiimm The main focus of this release was an attempt to fix the reported bugs in version 4.0. Because of this, no new functionality has been added to the system. The sole development effort was in fixing bugs reported by users of version 4, and bugs uncovered by a first cut at system validation test suites. 33.. EExxiissttiinngg 44..00 DDaattaabbaasseess There have been no changes to the tuple header struc- tures or the system catalogs since POSTGRES Version 4.0. Because of this you do not have to do the POSTGRES data conversion dance after installing 4.0.1. The entire data directory can be copied from the 4.0 installation to the 4.0.1 installation. Here is a suggested means of conver- sion (if you have the disk space). OOnnccee 44..00..11 iiss iinnssttaalllleedd iinn //uussrr//ppoossttggrreess:: rrmm --rrff //uussrr//ppoossttggrreess//ddaattaa ccdd //uussrr//ppoossttggrreess44..00 ttaarr ccff -- ddaattaa || ((ccdd //uussrr//ppoossttggrreess;; ttaarr xxvvff --)) If there are any problems, remove /usr/postgres/data, run initdb and fall back to the tried method of copying. NNoottee tthhaatt tthhiiss wwiillll oonnllyy wwoorrkk wwiitthh VVeerrssiioonn 44 ddaattaabbaasseess.. Databases from 3.1 (or earlier releases) must still be copied out to unix files, then copied back in once 4.0.1 has been successfully installed. PPOOSSTTGGRREESS VV44..00..11 RReelleeaassee NNootteess 22 11999922//0088//2266 44.. NNeeww DDeebbuuggggiinngg TToooollss Both of the following utilities are built and installed in $POSTGRESHOME/bin during the standard installation pro- cedure. If you think you are experiencing database cor- ruption, we encourage you to try them out. 44..11.. ppaaggeeddoocc Pagedoc is a postgres relation page doctor. It requires the path name of a heap or an index relation as an argument. It understands postgres page formats and can be used to dump the pages. Pagedoc doesn't know about user data in tuples; it only knows about tuple headers which are uniform across the installation. UUssaaggee:: ppaaggeeddoocc [[--hh||bb||rr]] [[--dd lleevveell]] ffiilleennaammee -h, -b, and -r are for heap, btree, and rtree files, respectively. -d level sets the detail level: 0 - page summaries. 1 - page summaries and line pointer summaries. 2 - all of 1 plus information about each tuple. -h and -d0 are the defaults. 44..22.. sshhmmeemmddoocc Shmemdoc is a shared memory doctor program. It allows you to view the state of shared memory and semaphores after an abnormal termination (i.e. a backend crash). It assumes that state is static -- that is, no other process should be changing shared memory while shmemdoc is running. In order to use this, you should start the postmaster with the -n option (no reinitial- ization) in order to avoid reinitializing shared struc- tures after a backend terminates abnormally. For infor- mation on the available commands type 'help' at the shmemdoc prompt. 55.. KKnnoowwnn BBuuggss There are a few known bugs that we did not fix in this release. 55..11.. AAssyynncchhrroonnoouuss PPoorrttaallss A flaw in the implementation of asynchronous portals was discovered a day or so before shipping 4.0.1. Noti- fies were handled via signals, and processing them asyn- chronously can lead to a variety of problems. A cleaner PPOOSSTTGGRREESS VV44..00..11 RReelleeaassee NNootteess 33 11999922//0088//2266 implementation has been conceived, but there was no way to do it for this release. For this reason async por- tals have been backed out of the system. The cleaner version will be ready by the next release. 55..22.. HHaasshh JJooiinnss The current implementation of hash joins in POSTGRES attempts to put the entire hash table in virtual memory. 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 dependent 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 vacuum your database after any and all large append/copy commands. 55..33.. IISSNNUULLLL//NNOOTTNNUULLLL aanndd IInnddeexx SSccaannss Query qualifications based on null detection will cause a crash if there is an index defined on that attribute. One specific example is as follows: ccrreeaattee ffoooo ((aa == iinntt44)) \\gg ddeeffiinnee iinnddeexx ffooooiinndd oonn ffoooo uussiinngg bbttrreeee ((aa iinntt44__ooppss)) \\gg rreettrriieevvee ((ffoooo..aallll)) wwhheerree ffoooo..aa IISSNNUULLLL \\gg 55..44.. CCaacchhee IInnvvaalliiddaattiioonn There is a subtle bug relating to cache invalidation that can cause POSTGRES to violate transaction semantics in transactions/queries involving multiple commands. If the backend has a relation open that needs to be invali- dated when one command is finished the invalidation mes- sage 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. 55..55.. DDeeffiinnee OOppeerraattoorr At the very last moment we discovered that you cannot successfully declare the commutator and/or negator of an operator if they are not already defined. This makes it tricky to define an operator class to allow index scans over a user defined data type. You have to do define all the operators in the operator class, and then update the negator and commutator fields by hand (i.e. using the terminal monitor). PPOOSSTTGGRREESS VV44..00..11 RReelleeaassee NNootteess 44 11999922//0088//2266 55..66.. SSeett FFuunnccttiioonnss aanndd IInnssttaannccee RRuulleess The instance level rule system requires the ability to convert an arbitrary plan into its string equivalent, as well as the ability to convert the string representa- tion back to a valid plan tree. There were two new plan nodes added to the system for the implementation of postquel functions returning sets of results. The string to plan conversion functions do not know about these nodes and therefore cannot convert them. For this reason you cannot have such functions in the body of an instance level rule. You have to use rewrite rules. 55..77.. IInnddiicceess aanndd tthhee IInnssttaannccee LLeevveell RRuullee SSyysstteemm As ever, the Instance Level Rule System essentially ignores indices, so if you are defining a rule on an indexed attribute, you should use the Query Rewrite rule system. 55..88.. RReettrriieevvee IInnttoo aanndd ffaaiilleedd bbaacckkeennddss If a backend fails while in the course of executing a Retrieve Into query, a spurious file, with the same name as the target class of the Retrieve Into, will be left in the database directory. This file can be safely deleted by the database DBA. 55..99.. LLaarrggee OObbjjeeccttss aanndd ffaaiilleedd bbaacckkeennddss 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 by func- tions but not stored in instances. 66.. KKnnoowwnn bbuuggss lliisstt A list of known bugs and suggested work-arounds can be anonymously ftp'ed. This list is kept in the file ~ftp/pub/postgres-v4r0r1.bugs. We will make every attempt to keep this list up to date. 77.. MMaacchhiinnee--ddeeppeennddeenntt PPrroobblleemmss 77..11.. SSppaarrccssttaattiioonnss rruunnnniinngg SSuunnOOSS 44..00..33 Postgres has been known to crash SunOS 4.0.3 on Sparcstations, due to a SunOS bug in shared memory. It appears to work on SunOS 4.1 and higher, so any reports of crashes on SunOS 4.1 and higher are appreciated.