diff options
| -rw-r--r-- | HISTORY | 115 | ||||
| -rw-r--r-- | doc/FAQ | 1924 | ||||
| -rw-r--r-- | doc/TODO | 221 |
3 files changed, 1356 insertions, 904 deletions
@@ -1,3 +1,118 @@ +PostgreSQL 6.0 beta Thu Dec 26 16:47:43 EST 1996 +------------------------------------------------------------- + +A dump/restore is required for those wishing to migrate data from +previous releases of PostgreSQL. + +Major contributors: +------------------- + Bruce is Bruce Momjian<maillist@candle.pha.pa.us> + Bryan is Bryan Henderson<bryanh@giraffe.netgate.net> + Dan is Dan McGuirk <mcguirk@indirect.com> + Darren is Darren King <darrenk@insightdist.com> + Edmund is Edmund Mergl <E.Mergl@bawue.de> + Erich Stamberger <eberger@gewi.kfunigraz.ac.at> + Kurt is "Kurt J. Lidl" <lidl@va.pubnix.com> + Martin is Martin S. Utesch <utesch@aut.tu-freiberg.de> + Vadim is "Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su> + Vivek is Vivek Khera <khera@kci.kciLink.com> + +Changes in this release: +------------------------ +Restructured object file generation/location(Bryan, Marc) +Restructured port-specific file locations(Bryan, Marc) +Makefile restructuring(Bryan, Marc) +all functions now have prototypes that are compared against the calls +unused/uninialized variables corrected +gcc -Wall compiles cleanly with warnings only from unfixable constructs +major include file reorganization/reduction(Marc) +reduced the number of #define's, centeralized #define's +removed many os-specific #define's +fixed a few small memory leaks +removed duplicate system oid's +overhauled parser/analyze code to properly report errors and increase speed +properly report errors when INSERT ... SELECT columns did not match +properly report errors when insert column names were not correct +minor improvements to rules system +execute lowercase function names if not found with exact case +change oid constants used in code to #define names +Memory leaks (hunt and destroy with tools like Purify(Kurt) +fix unitialized reads of memory(Kurt) +fix array over-runs of memory writes(Kurt) +Change default decimal constant representation from float4 to float8(Bruce) +Allow libpq to distinguish between text value '' and null(Bruce) +Starting quote in insert string errors(Bruce) +ALTER TABLE bug - running postgress process needs to re-read table definition +Allow non-postgres users with createdb privs to destroydb's +Prevent SELECT NULL from crashing server (Bruce) +Fix elusive btree range/non-range bug(Dan) +Remove duplicate system catalog info or report mismatches(Dan) +Remove duplicate OIDS in system tables(Dan) +Prevent postmaster from being run as root +Implement IN qualifier(Bruce) +Implement BETWEEN qualifier(Bruce) +add synonym of != for <>(Bruce) +add UNIQUE index capability(Dan) +Re-visit and fix vacuum(Vadim) +speed up vacuum(Vadim) +can't shrink tables, pg_time and pg_log(Vadim & Erich) +allow actual compression, not just reuse on the same page(Vadim) +allow vacuum to be run on one table or entire database(Bruce) +Reverse meaning of HBA masks(Bryan) +Add hostname/user level access control rather than just hostname and user +Allow restriction on who can create C functions(Bryan) +Allow installation-configuration option to auto-add all local users(Bryan) +Allow restriction on who can do backend COPY(Bryan) +allow COPY from the frontend(Bryan) +Secure Authentication of local users(Bryan) +Idend authentication of local users(Bryan) +Change debug level 2 to print queries only, changed debug heading layout(Bruce) +Reduce size of regression diffs, remove timezone name difference(Bruce) +Remove compile-time parameters to enable binary distributions(Bryan) +Merge bsdi_2_1 to bsdi(Bruce) +pg_dump(Bruce) + allow dump of oid's + create indexes after tables are loaded for speed + pg_dump -d now handles NULL's properly +psql(Bruce) + fixed problem with multiple statements on one line with multiple outputs + changed command prompt for lines in query or in quotes + allow backslashed, semicolons anywhere on the line + \h and \? is now readable + \g filename now works + updated help syntax + char(3) now displays as (bp)char in \d output + return code now more accurate(Bryan?) +new config.h file(Marc, Bryan) +fix permissions on lo_export()(Bruce) +Make now stops on compile failure(Bryan) +submiting an empty query now returns empty status, not just " " query(Bruce) +several fixes for queries that crashed the backend +portability additions, including Ultrix, DG/UX, AIX, and Solaris +fix for pg_log size explosion +decoupled sparc and solaris defines(Kurt) +new options to initdb(Bryan) +allow asserts to be disabled easly from Makefile.global(Bruce) +allow "select oid,* from table" +NOTIFY fixes +PG_VERSION now set to 6.0 and used by postmaster +libpq has PQgetisnull()(Bruce) +pginterface additions for NULL values(Bruce) +fixed ALTER TABLE ... char(3) bug(Bruce) +fixed output of group acl permissions +new asserts for run-checking +name change from Postgres95 to PostgreSQL +monitor program removed +fix for hash indexes on some types like time and date +gist now included in the distrubution(Marc) +array fixes +fixes for aggregate/GROUP processing, allow 'select sum(func(x),sum(x+y) from z' +libpq++ improvements +Allow BY,ORDER BY to specify columns by number, or by non-alias table.column(Bruce) +Allow GROUP BY to use alias column name(Bruce) + + + Postgres95 1.02 Thu Aug 1 18:00:00 EDT 1996 ------------------------------------------------------------- @@ -1,453 +1,544 @@ -Frequently Asked Questions (FAQ) for Postgres95 - -Last updated: Mon Oct 14 08:05:23 EDT 1996 -Version: 2.0 - -Current maintainer: Bruce Momjian (maillist@candle.pha.pa.us) - -The most recent version of this document can be viewed at the postgres95 Web -site, http://www.ki.net/postgres95. - -Linux-specific questions are answered in -http://www.ki.net/postgres95/docs/FAQ-Linux.phtml. - -Irix-specific questions are answered in -http://www.ki.net/postgres95/docs/FAQ-Irix.phtml. - -Changes in this version (* = modified, + = new): - - * *3.41) What is the meaning of some of the terms used in Postgres? - ----------------------------------------------------------------------------- + html> + + FREQUENTLY ASKED QUESTIONS (FAQ) FOR POSTGRESQL + + + + Last updated: Thu Dec 12 21:30:37 EST 1996 + Version: 6.0 + + Current maintainer: Bruce Momjian (maillist@candle.pha.pa.us) + + + The most recent version of this document can be viewed at the + postgreSQL Web site, http://postgreSQL.org. + + Linux-specific questions are answered in + http://postgreSQL.org/docs/FAQ-Linux.phtml. + + Irix-specific questions are answered in + http://postgreSQL.org/docs/FAQ-Irix.phtml. + + Changes in this version (* = modified, + = new): + + *1.1) What is PostgreSQL? + + *1.5) Support for PostgreSQL + + *1.6) Latest release of PostgreSQL + + *1.10 Does PostgreSQL work with databases from earlier versions of + postgres? + + *3.1) How do I specify a KEY or other constraints on a column? + + *3.3) How do I define a unique indices? + + *3.11) Why doesn't the != operator work? + + *3.26) Why are my table files not getting any smaller after a delete? + + *3.28) I get the error 'default index class unsupported' when creating + an index. How do I do it? + + *3.37) What is the time-warp feature and how does it relate to vacuum? + + *5.1) How do I make a bug report? + _________________________________________________________________ + + + Questions answered: -1) General questions - -1.1) What is Postgres95? -1.2) What does Postgres95 run on? -1.3) Where can I get Postgres95? -1.4) What's the copyright on Postgres95? -1.5) Support for Postgres95 -1.6) Latest release of Postgres95 -1.7) Is there a commercial version of Postgres95? -1.9) What version of SQL does Postgres95 use? -1.10) Does Postgres95 work with databases from earlier versions of postgres? -1.11) How many people use Postgres95? - -2) Installation questions - -2.1) I get the error "cpp: command not found" when I try to compile -2.2) I get 'yy_flush_buffer undefined' when I try to compile the backend -2.3) initdb doesn't run -2.4) when I start up the postmaster, I get -2.5) The system seems to be confused about commas, decimal points, and date -formats. -2.6) How do I install postgres95 somewhere other than /usr/local/postgres95? -2.7) The backend compiled successfully, but compiling libpq resulted in a -complaint: "libpq/pqcomm.h" not found when compiling fe-auth.c. -2.8) Where can I find the bug fixes for postgres95? -2.9) I can't apply the patches even though everything looks like it should -work. -2.10) When I run postmaster, I get a Bad System Call core dumped message. -2.11) I get the error message "obj/fmgr.h: No such file or directory" -2.12) When I try to start the postmaster, I get IpcMemoryCreate errors. -2.13) I get the strange make errors right at the beginning: -2.14) I have changed a source file, but a recompile does not see the change. - -3) Postgres95 Features questions - -3.1) How do I specify a KEY or other constraints on a column? -3.2) Does Postgres95 support nested subqueries? -3.3) How do I define a unique indices? -3.4) I've having a lot of problems with using rules. -3.5) I can't seem to write into the middle of large objects reliably. -3.6) Does postgres95 have a graphical user interface? A report -3.7) How can I write client applications to Postgres95? -3.8) How do I prevent other hosts from accessing my Postgres95 -3.9) How do I set up a pg_group? -3.10) What is the exact difference between binary cursors and normal -cursors? -3.11) Why doesn't the != operator work? -3.12) What is a R-tree index and what is it used for? -3.13) What is the maximum size for a tuple? -3.14) I defined indices but my queries don't seem to make use of them. Why? -3.15) Are there ODBC drivers for Postgres95? -3.16) How do I use postgres for multi-dimensional indexing (> 2 dimensions)? -3.17) How do I do regular expression searches? case-insensitive regexp -searching? -3.18) I can't access the database as the 'root' user. -3.19) I experienced a server crash during a vacuum. How do I remove the lock -file? -3.20) What is the difference between the various character types? -3.21) In a query, how do I detect if a field is NULL? -3.22) How do I see how the query optimizer is evaluating my query? -3.23) How do I create a serial field? -3.24) How do I create a multi-column index? -3.25) What are the temp_XXX files in my database directory? -3.26) Why are my table files not getting any smaller after a delete? -3.27) Why can't I connect to my database from another machine? -3.28) I get the error 'default index class unsupported' when creating an -index. How do I do it? -3.29) Why does creating an index crash the backend server? -3.30) How do I specify a decimal constant as a float8, or a string as a -text? Why am I getting poor precision? -3.30) How do I specify a decimal constant as a float8, or a string as a -text? Why am I getting poor precision? -3.31) How do I find out what indexes or operations are defined in the -database? -3.32) My database is corrupt. I can't do anything. What should I do? -3.33) Createdb, destroydb, createuser,destroyuser don't run. Why? -3.34) Why does 'createuser' return 'unexpected last match in input()'? -3.35) All my servers crash under concurrent table access. Why? -3.36) What tools are available for hooking postgres to Web pages? -3.37) What is the time-warp feature and how does it relate to vacuum? -3.38) How do I tune the database engine for better performance? -3.39) What debugging features are available in Postgres95? -3.40) What is an oid? What is a tid? -3.41) What is the meaning of some of the terms used in Postgres? - -4) Questions about extending Postgres95 - -4.1) I wrote a user-defined function and when I run it in psql, it dumps -core. -4.2) I get messages of the type NOTICE:PortalHeapMemoryFree: 0x402251d0 -4.3) I've written some nifty new types and functions for Postgres95. -4.4) How do I write a C function to return a tuple? - -5) Bugs - -5.1) How do I find out about bug fixes? -5.2) How do I make a bug report? ----------------------------------------------------------------------------- - + 1) General questions + + 1.1) What is PostgreSQL? + 1.2) What does PostgreSQL run on? + 1.3) Where can I get PostgreSQL? + 1.4) What's the copyright on PostgreSQL? + 1.5) Support for PostgreSQL + 1.6) Latest release of PostgreSQL + 1.7) Is there a commercial version of PostgreSQL? + 1.9) What version of SQL does PostgreSQL use? + 1.10) Does PostgreSQL work with databases from earlier versions of + postgres? + 1.11) How many people use PostgreSQL? + + 2) Installation questions + + 2.1) initdb doesn't run + 2.2) when I start up the postmaster, I get "FindBackend: could not + find a backend to execute..." "postmaster: could not find backend to + execute..." + 2.3) The system seems to be confused about commas, decimal points, and + date formats. + 2.4) How do I install PostgreSQL somewhere other than + /usr/local/pgsql? + 2.5) The backend compiled successfully, but compiling libpq resulted + in a complaint: "libpq/pqcomm.h" not found when compiling fe-auth.c. + 2.6) When I run postmaster, I get a Bad System Call core dumped + message. + 2.7) When I try to start the postmaster, I get IpcMemoryCreate errors. + 2.8) I have changed a source file, but a recompile does not see the + change. + + 3) PostgreSQL Features questions + + 3.1) How do I specify a KEY or other constraints on a column? + 3.2) Does PostgreSQL support nested subqueries? + 3.3) How do I define a unique indices? + 3.4) I've having a lot of problems using rules. + 3.5) I can't seem to write into the middle of large objects reliably. + 3.6) Does PostgreSQL have a graphical user interface? A report + generator? A embedded query language interface? + 3.7) How can I write client applications to PostgreSQL? + 3.8) How do I prevent other hosts from accessing my PostgreSQL + 3.9) How do I set up a pg_group? + 3.10) What is the exact difference between binary cursors and normal + cursors? + 3.11) Why doesn't the != operator work? + 3.12) What is a R-tree index and what is it used for? + 3.13) What is the maximum size for a tuple? + 3.14) I defined indices but my queries don't seem to make use of them. + Why? + 3.15) Are there ODBC drivers for PostgreSQL? + 3.16) How do I use postgres for multi-dimensional indexing (> 2 + dimensions)? + 3.17) How do I do regular expression searches? case-insensitive regexp + searching? + 3.18) I can't access the database as the 'root' user. + 3.19) I experienced a server crash during a vacuum. How do I remove + the lock file? + 3.20) What is the difference between the various character types? + 3.21) In a query, how do I detect if a field is NULL? + 3.22) How do I see how the query optimizer is evaluating my query? + 3.23) How do I create a serial field? + 3.24) How do I create a multi-column index? + 3.25) What are the temp_XXX files in my database directory? + 3.26) Why are my table files not getting any smaller after a delete? + 3.27) Why can't I connect to my database from another machine? + 3.28) I get the error 'default index class unsupported' when creating + an index. How do I do it? + 3.29) Why does creating an index crash the backend server? + 3.30) How do I specify a decimal constant as a float8, or a string as + a text? Why am I getting poor precision? + 3.31) How do I find out what indexes or operations are defined in the + database? + 3.32) My database is corrupt. I can't do anything. What should I do? + 3.33) Createdb, destroydb, createuser, destroyuser don't run. Why? + 3.34) Why does 'createuser' return 'unexpected last match in input()'? + 3.35) All my servers crash under concurrent table access. Why? + 3.36) What tools are available for hooking postgres to Web pages? + 3.37) What is the time-warp feature and how does it relate to vacuum? + 3.38) How do I tune the database engine for better performance? + 3.39) What debugging features are available in PostgreSQL? + 3.40) What is an oid? What is a tid? + 3.41) What is the meaning of some of the terms used in Postgres? + + 4) Questions about extending PostgreSQL + + 4.1) I wrote a user-defined function and when I run it in psql, it + dumps core. + 4.2) I get messages of the type NOTICE:PortalHeapMemoryFree: + 0x402251d0 + 4.3) I've written some nifty new types and functions for PostgreSQL. + 4.4) How do I write a C function to return a tuple? + + 5) Bugs + + 5.1) How do I make a bug report? + _________________________________________________________________ + Section 1: General Questions -1.1) What is Postgres95? - -Postgres95 is an enhancement of the POSTGRES database management system, a -next-generation DBMS research prototype. While Postgres95 retains the -powerful data model and rich data types of POSTGRES, it replaces the -PostQuel query language with an extended subset of SQL. Postgres95 is free -and the complete source is available. - -Postgres95 development is being performed by a team of Internet developers -who all subscribe to the Postgres95 development mailing list. The current -coordinator is Marc G. Fournier (scrappy@ki.net). (See below on how to -join). This team is now responsible for all current and future development -of Postgres95. - -The authors of Postgres95 1.01 were Andrew Yu and Jolly Chen. Many others -have contributed to the porting, testing, debugging and enhancement of the -code. The original Postgres code, from which Postgres95 is derived, was the -effort of many graduate students, undergraduate students, and staff -programmers working under the direction of Professor Michael Stonebraker at -the University of California, Berkeley. - -1.2) What does Postgres95 run on? - -The authors have compiled and tested Postgres95 on the following -platforms(some of these compiles require gcc 2.7.0): - - * DEC Alpha AXP on OSF/1 2.0 - * HP PA-RISC on HP-UX 9.0 - * i386 Solaris - * SUN SPARC on Solaris 2.4 - * SUN SPARC on SunOS 4.1.3 - * DEC MIPS on Ultrix 4.4 - * Intel x86 on Linux 1.2 and Linux ELF - * OSs derived from 4.4-lite BSD (NetBSD, FreeBSD) - * IBM on AIX 3.2.5 - * BSD/OS 2.0, 2.01 & 2.1 - * SGI MIPS on IRIX 5.3 - -The following ports are bundled with the Postgres95 distribution. The -authors do not have handy access to these platforms but the ports have been -tested by the others. - - * Motorola MC68K or Intel x86 on NeXTSTEP 3.2 - * Intel x86 on Intel SVR4 - -1.3) Where can I get Postgres95? - -The primary anonymous ftp site for postgres95 is: - - * ftp://ftp.ki.net/pub/postgres95 - -A mirror site exists at: - - * ftp://postgres95.vnet.net/pub/postgres95 - * ftp://ftp.luga.or.at/pub/postgres95 - * ftp://cal011111.student.utwente.nl/pub/postgres95 - -1.4) What's the copyright on Postgres95? - -Postgres95 is subject to the following COPYRIGHT. - -POSTGRES95 Data Base Management System - -Copyright (c) 1994-6 Regents of the University of California - -Permission to use, copy, modify, and distribute this software and its -documentation for any purpose, without fee, and without a written agreement -is hereby granted, provided that the above copyright notice and 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 DOCUMENTATION, -EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF -SUCH DAMAGE. - -THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, -INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND -FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN -"AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO -PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + 1.1) What is PostgreSQL? + + + + PostgreSQL is an enhancement of the POSTGRES database management + system, a next-generation DBMS research prototype. While PostgreSQL + retains the powerful data model and rich data types of POSTGRES, it + replaces the PostQuel query language with an extended subset of SQL. + PostgreSQL is free and the complete source is available. + + PostgreSQL development is being performed by a team of Internet + developers who all subscribe to the PostgreSQL development mailing + list. The current coordinator is Marc G. Fournier + (scrappy@postgreSQL.org). (See below on how to join). This team is + now responsible for all current and future development of PostgreSQL. + + The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. Many + others have contributed to the porting, testing, debugging and + enhancement of the code. The original Postgres code, from which + PostgreSQL is derived, was the effort of many graduate students, + undergraduate students, and staff programmers working under the + direction of Professor Michael Stonebraker at the University of + California, Berkeley. + + The original name of the software at Berkeley was Postgres. When SQL + functionality was added in 1995, its name was changed to Postgres95. + The name was changed at the end of 1996 to PostgreSQL. + + 1.2) What does PostgreSQL run on? + + + + The authors have compiled and tested PostgreSQL on the following + platforms(some of these compiles require gcc 2.7.0): + * DEC Alpha AXP on OSF/1 2.0 + * HP PA-RISC on HP-UX 9.0 + * i386 Solaris + * SUN SPARC on Solaris 2.4 + * SUN SPARC on SunOS 4.1.3 + * DEC MIPS on Ultrix 4.4 + * Intel x86 on Linux 1.2 and Linux ELF + * OSs derived from 4.4-lite BSD (NetBSD, FreeBSD) + * IBM on AIX 3.2.5 + * BSD/OS 2.0, 2.01 & 2.1 + * SGI MIPS on IRIX 5.3 + + + + The following ports are bundled with the PostgreSQL distribution. The + authors do not have handy access to these platforms but the ports have + been tested by the others. + * Motorola MC68K or Intel x86 on NeXTSTEP 3.2 + * Intel x86 on Intel SVR4 + + + + 1.3) Where can I get PostgreSQL? + + + + The primary anonymous ftp site for PostgreSQL is: + * ftp://ftp.postgreSQL.org/pub + + + + A mirror site exists at: + * ftp://postgres95.vnet.net/pub/postgres95 + * ftp://ftp.luga.or.at/pub/postgres95 + * ftp://cal011111.student.utwente.nl/pub/postgres95 + * ftp://ftp.uni-trier.de/pub/database/rdbms/postgres/postgres95 + * ftp://rocker.sch.bme.hu + + 1.4) What's the copyright on PostgreSQL? + + + + PostgreSQL is subject to the following COPYRIGHT. + + PostgreSQL Data Base Management System + + Copyright (c) 1994-6 Regents of the University of California + + Permission to use, copy, modify, and distribute this software and its + documentation for any purpose, without fee, and without a written + agreement is hereby granted, provided that the above copyright notice + and 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 DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN + ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + + THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, + INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF + MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE + PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF + CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, + UPDATES, ENHANCEMENTS, OR MODIFICATIONS. + + 1.5) Support for PostgreSQL + + + + There is no official support for PostgreSQL from the original + maintainers or from University of California, Berkeley. It is + maintained through volunteer effort only. + + The main mailing list is: questions@postgreSQL.org. It is available + for discussion o f matters pertaining to PostgreSQL, including but not + limited to bug reports and fixes. For info on how to subscribe, send a + mail with the lines in the body (not the subject line) -1.5) Support for Postgres95 - -There is no official support for Postgres95 from the original maintainers or -from University of California, Berkeley. It is maintained through volunteer -effort only. - -With the generosity of the Jason Wright and others at Vnet, the postgres95 -mailing list now has a new home -- postgres95@postgres95.vnet.net. It is -available for discussion of matters pertaining to Postgres95, including but -not limited to bug reports and fixes. For info on how to subscribe, send a -mail with the lines in the body (not the subject line) help - info postgres95 - -to majordomo@postgres95.vnet.net. - -There is also a digest list available. To subscribe to this list, send email -to majordomo@postgres95.vnet.net with a BODY of: - - subscribe postgres95-digest - -Digests are sent out to members of this list whenever the main list has -received around 30k of messages. - -Archives and digests of the new mailing list at Vnet can be found at: - - * ftp://postgres95.vnet.net/pub/postgres95/archives - * ftp://postgres95.vnet.net/pub/postgres95/digests - -There is also a developers mailing list available. To subscribe to this -list, send email to pg95-dev-request@ki.net with a BODY of: - - subscribe pg95-dev - -Additional information about Postgres95 can be found via the postgres95 WWW -home page at: - - http://www.ki.net/postgres95 - -1.6) Latest release of Postgres95 - -The latest release of postgres95 is version 1.08. - -1.7) Is there a commercial version of Postgres95? - -Illustra Information Technology (a wholly owned subsidiary of Informix -Software, Inc.) sells an object-relational DBMS called Illustra that was -originally based on postgres. Illustra has cosmetic similarities to -postgres95 but has more features, is more robust, performs better, and -offers real documentation and support. On the flip side, it costs money. For -more information, contact sales@illustra.com - -1.8) What documentation is available for Postgres95? - -A user manual, manual pages, and some small test examples are included in -the distribution. The sql and built-in manual pages are particularly -important. - -The www page contains pointers to an implementation guide and five papers -written about postgres design concepts and features. - -1.9) What version of SQL does Postgres95 use? - -Postgres95 supports a subset of SQL-92. It has most of the important -constructs but lacks some of the functionality. The most visible differences -are: - - * no support for primary keys or column constraints - * no support for nested subqueries - * no HAVING clause under a GROUP BY - -On the other hand, you get to create user-defined types, functions, -inheritance etc. If you're willing to help with postgres95 coding, -eventually we can also add the missing features listed above. - -1.10) Does Postgres95 work with databases from earlier versions of postgres? - -Postgres95 v1.08 is compatible with databases created with v1.01. Those -upgrading from 1.0 should read the directions in the MIGRATION_1.0_TO_1.02 -directory. - -1.11) How many people use Postgres95? - -Since we don't have any licensing or registration scheme, it's impossible to -tell. We do know hundreds copies of postgres95 v1.* have been downloaded, -and that there many hundreds of subscribers to the mailing lists. - ----------------------------------------------------------------------------- + info questions + + + + to majordomo@postgreSQL.org. + + There is also a digest list available. To subscribe to this list, send + email to: majordomo@postgreSQL.org with a BODY of: + + + subscribe questions-digest + + Digests are sent out to members of this list whenever the main list + has received around 30k of messages. + + There is also a developers mailing list available. To subscribe to + this list, send email to hackers-request@postgreSQL.org with a BODY + of: + + + + subscribe hackers + + + + Additional information about PostgreSQL can be found via the + PostgreSQL WWW home page at: + + http://postgreSQL.org + + + + 1.6) Latest release of PostgreSQL + + + + The latest release of PostgreSQL is version 1.09. The next release + will be numbered 6.0 for historical reasons. This release is in beta + and is available at our ftp site. We expect the beta period to be + complete during the week of January 2, 1997. For information about + what is new in 6.0, see our TODO list on our WWW page. + + We expect a 7.0 release in several months that will remove time-travel + and reduce by 50% the size of on-disk system columns maintained for + each row in a table. This release will also require a dump and + restore. + + 1.7) Is there a commercial version of PostgreSQL? + + + + Illustra Information Technology (a wholly owned subsidiary of Informix + Software, Inc.) sells an object-relational DBMS called Illustra that + was originally based on postgres. Illustra has cosmetic similarities + to PostgreSQL but has more features, is more robust, performs better, + and offers real documentation and support. On the flip side, it costs + money. For more information, contact sales@illustra.com + + 1.8) What documentation is available for PostgreSQL? + + + + A user manual, manual pages, and some small test examples are included + in the distribution. The sql and built-in manual pages are + particularly important. + + The www page contains pointers to an implementation guide and five + papers written about postgres design concepts and features. + + 1.9) What version of SQL does PostgreSQL use? + + + + PostgreSQL supports a subset of SQL-92. It has most of the important + constructs but lacks some of the functionality. The most visible + differences are: + * no support for nested subqueries + * no HAVING clause under a GROUP BY + + + + On the other hand, you get to create user-defined types, functions, + inheritance etc. If you're willing to help with PostgreSQL coding, + eventually we can also add the missing features listed above. + + 1.10) Does PostgreSQL work with databases from earlier versions of postgres? + + + + PostgreSQL v1.09 is compatible with databases created with v1.01. + Those upgrading from 1.0 should read the directions in the + MIGRATION_1.0_TO_1.02 directory. + + Upgrading to 6.0 requires a dump and restore. + + 1.11) How many people use PostgreSQL? + + + + Since we don't have any licensing or registration scheme, it's + impossible to tell. We do know hundreds copies of PostgreSQL v1.* have + been downloaded, and that there many hundreds of subscribers to the + mailing lists. + + + _________________________________________________________________ + +Section 2: Installation Questions + + + + 2.1) initdb doesn't run + + + + * check to see that you have the proper paths set + * check that the 'postgres' user owns all the right files + * ensure that there are files in $PGDATA/files, and that they are + non-empty. If they aren't, then "gmake install" failed for some + reason + + + + 2.2) when I start up the postmaster, I get "FindBackend: could not find a + backend to execute..." "postmaster: could not find backend to execute..." + + + + You probably do not have the right path set up. The 'postgres' + executable needs to be in your path. + + 2.3) The system seems to be confused about commas, decimal points, and date + formats. + + + + Check your locale configuration. PostgreSQL uses the locale settings + of the user that ran the postmaster process. Set those accordingly for + your operating environment. + + 2.4) How do I install PostgreSQL somewhere other than /usr/local/pgsql? + + + + You need to manually edit the paths in src/Makefile.global to your + site configuration. + + 2.5) The backend compiled successfully, but compiling libpq resulted in a + complaint: "libpq/pqcomm.h" not found when compiling fe-auth.c. + + + + You've probably installed PostgreSQL somewhere other than + /usr/local/pgsql, but didn't edit the src/Makefile.global accordingly. + See question 2.6. + + 2.6) When I run postmaster, I get a Bad System Call core dumped message. + + + + It could be a variety of problems, but first check to see that you + have system V extensions installed on your kernel. PostgreSQL requires + kernel support for shared memory. + + 2.7) I get the error message "obj/fmgr.h: No such file or directory" + + + + This indicates that you did not generate the file fmgr.h properly. + Something failed in the running of the + src/backend/utils/Gen_fmgrtab.sh script. Check to see the paths used + in that script is appropriate to your system. + + 2.8) When I try to start the postmaster, I get IpcMemoryCreate errors. + + + + You either do not have shared memory configured properly in kernel or + you need to enlarge the shared memory available in the kernel. The + exact amount you need depends on your architecture and how many + buffers you configure postmaster to run with. For most systems, with + default buffer sizes, you need a minimum of ~760K. + + 2.10) I have changed a source file, but a recompile does not see the change? + + + + The Makefiles do not have the proper dependencies for include files. + You have to do a 'make clean' and then another 'make'. + + + _________________________________________________________________ + +Section 3: PostgreSQL Features + + + + 3.1) How do I specify a KEY or other constraints on a column? + + + + Column constraints are not supported in PostgreSQL. As a consequence, + the system does not check for duplicates. + + Under 6.0, create a unique index on the column. Attempts to create + duplicate of that column will report an error. + + 3.2) Does PostgreSQL support nested subqueries? + + + + Subqueries are not implemented, but they can be simulated using sql + functions. + + 3.3) How do I define a unique indices? + + + + PostgreSQL 6.0 supports unique indices. + + 3.4) I've having a lot of problems using rules. + + + + Currently, the rule system in PostgreSQL is mostly broken. It works + enough to support the view mechanism, but that's about it. Use + PostgreSQL rules at your own peril. + + 3.5) I can't seem to write into the middle of large objects reliably. + + + + The Inversion large object system in PostgreSQL is also mostly broken. + It works well enough for storing large wads of data and reading them + back out, but the implementation has some underlying problems. Use + PostgreSQL large objects at your own peril. + + 3.6) Does PostgreSQL have a graphical user interface? A report generator? A + embedded query language interface? + + + + No. No. No. Not in the official distribution at least. Some users have + reported some success at using 'pgbrowse' and 'onyx' as frontends to + PostgreSQL. Several contributions are working on tk based frontend + tools. Ask on the mailing list. + + 3.7) How can I write client applications to PostgreSQL? + + + + PostgreSQL supports a C-callable library interface called libpq as + well as a Tcl-based library interface called libtcl. + + Others have contributed a perl interface and a WWW gateway to + PostgreSQL. See the PostgreSQL home pages for more details. + + 3.8) How do I prevent other hosts from accessing my PostgreSQL backend? + + + + Use host-based authentication by modifying the file $PGDATA/pg_hba + accordingly. + + 3.9) How do I set up a pg_group? + + + + Currently, there is no easy interface to set up user groups. You have + to explicitly insert/update the pg_group table. For example: -Section 2: Installation Questions - -2.1) I get the error "cpp: command not found" when I try to compile the -backend. - -Edit the src/backend/utils/Gen_fmgrtab.sh script to include the path for the -cpp for your particular site. - -2.2) I get 'yy_flush_buffer undefined' when I try to compile the backend - -Use a more recent version of flex, version 2.5.2. Version 2.5.3 has a known -bug. - -2.3) initdb doesn't run - - * check to see that you have the proper paths set - * check that the 'postgres' user owns all the right files - * ensure that there are files in $PGDATA/files, and that they are - non-empty. If they aren't, then "gmake install" failed for some reason - -2.4) when I start up the postmaster, I get "FindBackend: could not find a -backend to execute..." "postmaster: could not find backend to execute..." - -You probably do not have the right path set up. The 'postgres' executable -needs to be in your path. - -2.5) The system seems to be confused about commas, decimal points, and date -formats. - -Check your locale configuration. postgres95 uses the locale settings of the -user that ran the postmaster process. Set those accordingly for your -operating environment. - -2.6) How do I install postgres95 somewhere other than /usr/local/postgres95? - -You need to manually edit the paths in src/Makefile.global to your site -configuration. - -2.7) The backend compiled successfully, but compiling libpq resulted in a -complaint: "libpq/pqcomm.h" not found when compiling fe-auth.c. - -You've probably installed postgres95 somewhere other than -/usr/local/postgres, but didn't edit the src/Makefile.global accordingly. -See question 2.6. - -2.8) Where can I find the bug fixes for postgres95? - -The patches should be applied in the order listed. The patch files can be -ftp'ed directly from the directory: - - * ftp://ftp.ki.net/pub/postgres95 - -2.9) I can't apply the patches even though everything looks like it should -work. - -If you cut and paste directly off your web browser, tabs and whitespaces may -not be preserved properly. Use the 'save as file' option from your web -browser instead. - -2.10) When I run postmaster, I get a Bad System Call core dumped message. - -It could be a variety of problems, but first check to see that you have -system V extensions installed on your kernel. Postgres95 requires kernel -support for shared memory. - -2.11) I get the error message "obj/fmgr.h: No such file or directory" - -This indicates that you did not generate the file fmgr.h properly. Something -failed in the running of the src/backend/utils/Gen_fmgrtab.sh script. Check -to see the paths used in that script is appropriate to your system. - -2.12) When I try to start the postmaster, I get IpcMemoryCreate errors. - -You either do not have shared memory configured properly in kernel or you -need to enlarge the shared memory available in the kernel. The exact amount -you need depends on your architecture and how many buffers you configure -postmaster to run with. For most systems, with default buffer sizes, you -need a minimum of ~760K. - -2.13) I get the strange make errors right at the beginning: - - warning: NUL character seen; rest of line ignored - *** missing separator. Stop. - -or - - Is a directory. Stop. - -Eliminate any whitespaces at the end of the PORTNAME line in -Makefile.global. Extraneous tabs or spaces will confuse the make templates. - -2.14) I have changed a source file, but a recompile does not see the change? - -The Makefiles do not have the proper dependencies. You have to do a 'make -clean' and then another 'make'. - ----------------------------------------------------------------------------- - -Section 3: Postgres95 Features - -3.1) How do I specify a KEY or other constraints on a column? - -Column constraints are not supported in postgres95. As a consequence, the -system does not check for duplicates. - -3.2) Does Postgres95 support nested subqueries? - -Subqueries are not implemented, but they can be simulated using sql -functions. - -3.3) How do I define a unique indices? - -Postgres95 does not support unique indices. Defining an index does not -preclude insertion of duplicate index key values. - -3.4) I've having a lot of problems with using rules. - -Currently, the rule system in postgres95 is mostly broken. It works enough -to support the view mechanism, but that's about it. Use postgres95 rules at -your own peril. - -3.5) I can't seem to write into the middle of large objects reliably. - -The Inversion large object system in postgres95 is also mostly broken. It -works well enough for storing large wads of data and reading them back out, -but the implementation has some underlying problems. Use postgres95 large -objects at your own peril. - -3.6) Does postgres95 have a graphical user interface? A report generator? A -embedded query language interface? - -No. No. No. Not in the official distribution at least. Some users have -reported some success at using 'pgbrowse' and 'onyx' as frontends to -postgres95. Several contributions are working on tk based frontend tools. -Ask on the mailing list. - -3.7) How can I write client applications to Postgres95? - -Postgres95 supports a C-callable library interface called libpq as well as a -Tcl-based library interface called libtcl. - -Others have contributed a perl interface and a WWW gateway to postgres95. -See the postgres95 home pages for more details. - -3.8) How do I prevent other hosts from accessing my Postgres95 backend? - -Use host-based authentication by modifying the file $PGDATA/pg_hba -accordingly. - -3.9) How do I set up a pg_group? - -Currently, there is no easy interface to set up user groups. You have to -explicitly insert/update the pg_group table. For example: jolly=> insert into pg_group (groname, grosysid, grolist) jolly=> values ('posthackers', '1234', '{5443, 8261}'); @@ -456,144 +547,180 @@ explicitly insert/update the pg_group table. For example: CHANGE jolly=> -The fields in pg_group are: - - * groname: the group name. This a char16 and should be purely - alphanumeric. Do not include underscores or other punctuation. - * grosysid: the group id. This is an int4. This should be unique for each - group. - * grolist: the list of pg_user id's that belong in the group. This is an - int4[]. - -3.10) What is the exact difference between binary cursors and normal -cursors? - -Normal cursors return data back in ASCII format. Since data is stored -natively in binary format, the system must do a conversion to produce the -ASCII format. In addition, ASCII formats are often large in size than binary -format. Once the attributes come back in ASCII, often the client application -then has to convert it to a binary format to manipulate it anyway. + + + The fields in pg_group are: + * groname: the group name. This a char16 and should be purely + alphanumeric. Do not include underscores or other punctuation. + * grosysid: the group id. This is an int4. This should be unique for + each group. + * grolist: the list of pg_user id's that belong in the group. This + is an int4[]. + + + + 3.10) What is the exact difference between binary cursors and normal cursors? + + + + Normal cursors return data back in ASCII format. Since data is stored + natively in binary format, the system must do a conversion to produce + the ASCII format. In addition, ASCII formats are often large in size + than binary format. Once the attributes come back in ASCII, often the + client application then has to convert it to a binary format to + manipulate it anyway. + + Binary cursors give you back the data in the native binary + representation. Thus, binary cursors will tend to be a little faster + since there's less overhead of conversion. + + However, ASCII is architectural neutral whereas binary representation + can differ between different machine architecture. Thus, if your + client machine uses a different representation than you server + machine, getting back attributes in binary format is probably not what + you want. Also, if your main purpose is displaying the data in ASCII, + then getting it back in ASCII will save you some effort on the client + side. + + 3.11) Why doesn't the != operator work? + + + + SQL specifies <> as the inequality operator, and that is what we have + defined for the built-in types. + + In 6.0, != is equivalent to <>. + + 3.12) What is a R-tree index and what is it used for? + + + + An r-tree index is used for indexing spatial data. A hash index can't + handle range searches. A B-tree index only handles range searches in a + single dimension. R-tree's can handle multi-dimensional data. For + example, if a R-tree index can be built on an attribute of type + 'point', the system can more efficient answer queries like select all + points within a bounding rectangle. + + The canonical paper that describes the original R-Tree design is: + + Guttman, A. "R-Trees: A Dynamic Index Structure for Spatial + Searching." Proc of the 1984 ACM SIGMOD Int'l Conf on Mgmt of Data, + 45-57. + + You can also find this paper in Stonebraker's "Readings in Database + Systems" + + 3.13) What is the maximum size for a tuple? + + + + Tuples are limited to 8K bytes. Taking into account system attributes + and other overhead, one should stay well shy of 8,000 bytes to be on + the safe side. To use attributes larger than 8K, try using the large + objects interface. + + Tuples do not cross 8k boundaries so a 5k tuple will require 8k of + storage. + + 3.14) I defined indices but my queries don't seem to make use of them. Why? + + + + PostgreSQL does not automatically maintain statistics. One has to make + an explicit 'vacuum' call to update the statistics. After statistics + are updated, the optimizer has a better shot at using indices. Note + that the optimizer is limited and does not use indices in some + circumstances (such as OR clauses). + + If the system still does not see the index, it is probably because you + have created an index on a field with the improper *_ops type. For + example, you have created a CHAR(4) field, but have specified a + char_ops index type_class. + + See the create_index manual page for information on what type classes + are available. It must match the field type. + + Postgres does not warn the user when the improper index is created. + + Indexes not used for ORDER BY operations. + + 3.15) Are there ODBC drivers for PostgreSQL? + + + + There are two ODBC drivers available, PostODBC and OpenLink ODBC. + + For all people being interested in PostODBC, there are now two mailing + lists devoted to the discussion of PostODBC. The mailing lists are: + * postodbc-users@listserv.direct. net + * postodbc-developers@listse rv.direct.net + + + + these lists are ordinary majordomo mailing lists. You can subscribe by + sending a mail to: + * majordomo@listserv.direct.net + + + + OpenLink ODBC is currently in beta under Linux. You can get it from + http://www.openlinksw.com/postgres.html. It works with our standard + ODBC client software so you'll have Postgres ODBC available on every + client platform we support (Win, Mac, Unix, VMS). + + We will probably be selling this product to people who need + commercial-quality support, but a freeware version will always be + available. Questions to postgres95@openlink.co.uk. + + 3.16) How do I use postgres for multi-dimensional indexing (> 2 + dimensions">)? + + + + Builtin R-Trees can handle polygons and boxes. In theory, R-trees can + be extended to handle higher number of dimensions. In practice, + extending R-trees require a bit of work and we don't currently have + any documentation on how to do it. + + 3.17) How do I do regular expression searches? case-insensitive regexp + searching? + + + + PostgreSQL supports the SQL LIKE syntax as well as more general + regular expression searching with the ~ operator. The !~ is the + negated regexp operator. ~* and !~* are the case-insensitive regular + expression operators. + + 3.18) I can't access the database as the 'root' user. + + + + You should not create database users with user id 0(root). They will + be unable to access the database. This is a security precaution + because of the ability of any user to dynamically link object modules + into the database engine. + + 3.19) I experienced a server crash during a vacuum. How do I remove the lock + file? + + + + If the server crashes during a vacuum command, chances are it will + leave a lock file hanging around. Attempts to re-run the vacuum + command result in -Binary cursors give you back the data in the native binary representation. -Thus, binary cursors will tend to be a little faster since there's less -overhead of conversion. - -However, ASCII is architectural neutral whereas binary representation can -differ between different machine architecture. Thus, if your client machine -uses a different representation than you server machine, getting back -attributes in binary format is probably not what you want. Also, if your -main purpose is displaying the data in ASCII, then getting it back in ASCII -will save you some effort on the client side. - -3.11) Why doesn't the != operator work? - -SQL specifies <> as the inequality operator, and that is what we have -defined for the built-in types. You are free, however, to extend postgres95 -to include the != operator if you like. - -3.12) What is a R-tree index and what is it used for? - -An r-tree index is used for indexing spatial data. A hash index can't handle -range searches. A B-tree index only handles range searches in a single -dimension. R-tree's can handle multi-dimensional data. For example, if a -R-tree index can be built on an attribute of type 'point', the system can -more efficient answer queries like select all points within a bounding -rectangle. - -The canonical paper that describes the original R-Tree design is: - -Guttman, A. "R-Trees: A Dynamic Index Structure for Spatial Searching." Proc -of the 1984 ACM SIGMOD Int'l Conf on Mgmt of Data, 45-57. - -You can also find this paper in Stonebraker's "Readings in Database Systems" - -3.13) What is the maximum size for a tuple? - -Tuples are limited to 8K bytes. Taking into account system attributes and -other overhead, one should stay well shy of 8,000 bytes to be on the safe -side. To use attributes larger than 8K, try using the large objects -interface. - -Tuples do not cross 8k boundaries so a 5k tuple will require 8k of storage. - -3.14) I defined indices but my queries don't seem to make use of them. Why? - -Postgres95 does not automatically maintain statistics. One has to make an -explicit 'vacuum' call to update the statistics. After statistics are -updated, the optimizer has a better shot at using indices. Note that the -optimizer is limited and does not use indices in some circumstances (such as -OR clauses). - -If the system still does not see the index, it is probably because you have -created an index on a field with the improper *_ops type. For example, you -have created a CHAR(4) field, but have specified a char_ops index -type_class. - -See the create_index manual page for information on what type classes are -available. It must match the field type. - -Postgres does not warn the user when the improper index is created. - -Indexes not used for ORDER BY operations. - -3.15) Are there ODBC drivers for Postgres95? - -There are two ODBC drivers available, PostODBC and OpenLink ODBC. - -For all people being interested in PostODBC, there are now two mailing lists -devoted to the discussion of PostODBC. The mailing lists are: - - * postodbc-users@listserv.direct. net - * postodbc-developers@listse rv.direct.net - -these lists are ordinary majordomo mailing lists. You can subscribe by -sending a mail to: - - * majordomo@listserv.direct.net - -OpenLink ODBC is currently in beta under Linux. You can get it from -http://www.openlinksw.com/postgres.html. It works with our standard ODBC -client software so you'll have Postgres ODBC available on every client -platform we support (Win, Mac, Unix, VMS). - -We will probably be selling this product to people who need -commercial-quality support, but a freeware version will always be available. -Questions to postgres95@openlink.co.uk. - -3.16) How do I use postgres for multi-dimensional indexing (> 2 -dimensions">)? - -Builtin R-Trees can handle polygons and boxes. In theory, R-trees can be -extended to handle higher number of dimensions. In practice, extending -R-trees require a bit of work and we don't currently have any documentation -on how to do it. - -3.17) How do I do regular expression searches? case-insensitive regexp -searching? - -Postgres95 supports the SQL LIKE syntax as well as more general regular -expression searching with the ~ operator. The !~ is the negated regexp -operator. ~* and !~* are the case-insensitive regular expression operators. - -3.18) I can't access the database as the 'root' user. - -You should not create database users with user id 0(root). They will be -unable to access the database. This is a security precaution because of the -ability of any user to dynamically link object modules into the database -engine. - -3.19) I experienced a server crash during a vacuum. How do I remove the lock -file? - -If the server crashes during a vacuum command, chances are it will leave a -lock file hanging around. Attempts to re-run the vacuum command result in WARN:can't create lock file -- another vacuum cleaner running? -If you are sure that no vacuum is actually running, you can remove the file -called "pg_vlock" in your database directory (which is $PGDATA/base/) - -3.20) What is the difference between the various character types? + + + If you are sure that no vacuum is actually running, you can remove the + file called "pg_vlock" in your database directory (which is + $PGDATA/base/) + + 3.20) What is the difference between the various character types? Type Internal Name Notes -------------------------------------------------- @@ -607,35 +734,47 @@ VARCHAR(#) varchar size specifies maximum length, no padding TEXT text length limited only by maximum tuple length BYTEA bytea variable-length array of bytes -Remember, you need to use the internal name when creating indexes on these -fields or when doing other internal operations. - -The last four types above are "varlena" types (i.e. the first four bytes is -the length, followed by the data). CHAR(#) and VARCHAR(#) allocate the -maximum number of bytes no matter how much data is stored in the field. TEXT -and BYTEA are the only character types that have variable length on the -disk. - -3.21) In a query, how do I detect if a field is NULL? + + + Remember, you need to use the internal name when creating indexes on + these fields or when doing other internal operations. + + The last four types above are "varlena" types (i.e. the first four + bytes is the length, followed by the data). CHAR(#) and VARCHAR(#) + allocate the maximum number of bytes no matter how much data is stored + in the field. TEXT and BYTEA are the only character types that have + variable length on the disk. + + 3.21) In a query, how do I detect if a field is NULL? + + + + PostgreSQL has two builtin keywords, "isnull" and "notnull" (note no + spaces). Version 1.05 and later and 6.* understand IS NULL and IS NOT + NULL. + + 3.22) How do I see how the query optimizer is evaluating my query? + + + + Place the word 'EXPLAIN' at the beginning of the query, for example: -Postgres95 has two builtin keywords, "isnull" and "notnull" (note no -spaces). Version 1.05 and later and 2.* understand IS NULL and IS NOT NULL. - -3.22) How do I see how the query optimizer is evaluating my query? - -Place the word 'EXPLAIN' at the beginning of the query, for example: EXPLAIN SELECT * FROM table1 WHERE age = 23; -3.23) How do I create a serial field? - -Postgres does not allow the user to specifiy a user column as type SERIAL. -Instead, you can use each row's oid field as a unique value. However, if you -need to dump and reload the database, you need to be using postgres version -1.07 or later or 2.* with pgdump's -o option or COPY's WITH OIDS option to -preserver the oids. - -Another valid way of doing this is to create a function: + + + 3.23) How do I create a serial field? + + + + Postgres does not allow the user to specifiy a user column as type + SERIAL. Instead, you can use each row's oid field as a unique value. + However, if you need to dump and reload the database, you need to be + using postgres version 1.07 or later or 6.* with pgdump's -o option or + COPY's WITH OIDS option to preserver the oids. + + Another valid way of doing this is to create a function: create table my_oids (f1 int4); @@ -644,271 +783,358 @@ Another valid way of doing this is to create a function: 'update my_oids set f1 = f1 + 1; select f1 from my_oids; ' language 'sql'; -then: + + + then: + create table my_stuff (my_key int4, value text); insert into my_stuff values (new_oid(), 'hello'); -However, keep in mind there is a race condition here where one server could -do the update, then another one do an update, and they both could select the -same new id. This statement should be performed within a transaction. - -3.24) How do I create a multi-column index? - -You can not directly create a multi-column index using create index. You -need to define a function which acts on the multiple columns, then use -create index with that function. + + + However, keep in mind there is a race condition here where one server + could do the update, then another one do an update, and they both + could select the same new id. This statement should be performed + within a transaction. + + 3.24) How do I create a multi-column index? + + + + You can not directly create a multi-column index using create index. + You need to define a function which acts on the multiple columns, then + use create index with that function. + + 3.25) What are the temp_XXX files in my database directory? + + + + They are temp_ files generated by the query executor. For example, if + a sort needs to be done to satisfy an ORDER BY, some temp files are + generated as a result of the sort. + + If you have no transactions or sorts running at the time, it is safe + to delete the temp_ files. + + 3.26) Why are my table files not getting any smaller after a delete? + + + + If you run vacuum in pre-6.0, unused rows will be marked for reuse, + but the file blocks are not released. + + In 6.0, vacuum properly shrinks tables. + + 3.27) Why can't I connect to my database from another machine? + + + + The default configuration allows only connections from tcp/ip host + localhost. You need to add a host entry to the file pgsql/data/pg_hba. + + + 3.28) I get the error 'default index class unsupported' when creating an + index. How do I do it? + + + + You probably used: -3.25) What are the temp_XXX files in my database directory? - -They are temp_ files generated by the query executor. For example, if a sort -needs to be done to satisfy an ORDER BY, some temp files are generated as a -result of the sort. - -If you have no transactions or sorts running at the time, it is safe to -delete the temp_ files. - -3.26) Why are my table files not getting any smaller after a delete? - -If you run vacuum, unused rows will be marked for reuse, but the file blocks -are not released. We could move the unused rows to the end of the file and -use ftruncate() to decrease the file size, but no one has implemented this -yet. - -3.27) Why can't I connect to my database from another machine? - -The default configuration allows only connections from tcp/ip host -localhost. You need to add a host entry to the file postgres95/data/pg_hba. - -3.28) I get the error 'default index class unsupported' when creating an -index. How do I do it? - -You probably used: create index idx1 on person using btree (name); -Postgres95 indexes are extensible, and therefore you must specify a -class_type when creating an index. Read the manual page for create index -(called create_index). Version 2.0 will correct this deficiency. - -3.29) Why does creating an index crash the backend server? - -You have probably defined an incorrect *_ops type class for the field you -are indexing. - -3.30) How do I specify a decimal constant as a float8, or a string as a -text? Why am I getting poor precision? + + + PostgreSQL indexes are extensible, and therefore in pre-6.0, you must + specify a class_type when creating an index. Read the manual page for + create index (called create_index). + + Version 6.0, if you do not specify a class_type, it defaults to the + proper type for the column. + + 3.29) Why does creating an index crash the backend server? + + + + You have probably defined an incorrect *_ops type class for the field + you are indexing. + + 3.30) How do I specify a decimal constant as a float8, or a string as a text? + Why am I getting poor precision? + + + + Use the :: operator. It is needed only when the default promotion + rules fail. i.e.: -Use the :: operator. It is needed only when the default promotion rules -fail. i.e.: insert into tab1 values (4.23::float8, '2343'::text) -The default floating-point constant is a float4 in releases prior to 1.05. -Later releases default to float8. - -3.31) How do I find out what indexes or operations are defined in the -database? - -Run the file postgres95/src/tutorial/syscat.source. It illustrates many of -the 'select's needed to get information out of the database system tables. - -3.32) My database is corrupt. I can't do anything. What should I do? + + + The default floating-point constant is a float4 in releases prior to + 1.05. Later releases default to float8. + + 3.31) How do I find out what indexes or operations are defined in the + database? + + + + Run the file pgsql/src/tutorial/syscat.source. It illustrates many of + the 'select's needed to get information out of the database system + tables. + + 3.32) My database is corrupt. I can't do anything. What should I do? + + + + The 1.02 release has a README file and utility that describes a + possible cause of the problem and a workaround. + + This bug is fixed in 1.02.1. + + 3.33) Createdb, destroydb, createuser,destroyuser don't run. Why? + + + + Release 1.02 does not have this problem. + + The 1.01 release of PostgreSQL uses a variable called PAGER to filter + the output of SELECT statements. Unfortunately, this PAGER is used + even when the standard output is not a terminal. + + 3.34) Why does 'createuser' return 'unexpected last match in input(">)'? + + + + You have compile postgres with flex version 2.5.3. There is bug in + this version of flex. Use flex version 2.5.2 or flex 2.5.4 instead. + There is a doc/README.flex file which will properly patch the flex + 2.5.3 source code. + + 3.35) All my servers crash under concurrent table access. Why? + + + + This problem can be caused by a kernel that is not configured to + support semaphores. + + 3.36) What tools are available for hooking postgres to Web pages? + + + + For web integration, PHP/FI is an excellent interface. The URL for + that is http://www.vex.net/php/ + + PHP is great for simple stuff, but for more complex stuff, some still + use the perl interface and CGI.pm. + + An example of using WWW with C to talk to Postgres is can be tried at: + * http://postgreSQL.org/~mlc + + + + An WWW gatway based on WDB using perl can be downloaded from: + * http://www.eol.ists.ca/~dunlop/wdb -p95 + + 3.37) What is the time-warp feature and how does it relate to vacuum? + + + + PostgreSQL handles data changes differently than most database + systems. When a row is changed in a table, the original row is marked + with the time it was changed, and a new row is created with the + current data. By default, only current rows are used in a table. If + you specify a date/time after the table name in a FROM clause, you can + access the data that was current at that time, i.e. -The 1.02 release has a README file and utility that describes a possible -cause of the problem and a workaround. See the file -postgres95/contrib/zap_ltv/README for more information. Also please contact -the README author to help generate a complete fix for this bug. - -This bug may be fixed in 1.02. - -3.33) Createdb, destroydb, createuser,destroyuser don't run. Why? - -Release 1.02 does not have this problem. - -The 1.01 release of postgres95 uses a variable called PAGER to filter the -output of SELECT statements. Unfortunately, this PAGER is used even when the -standard output is not a terminal. - -3.34) Why does 'createuser' return 'unexpected last match in input(">)'? - -You have compile postgres with flex version 2.5.3. There is bug in this -version of flex. Use flex version 2.5.2 instead. There is a doc/README.flex -file which will properly patch the flex 2.5.3 source code. - -3.35) All my servers crash under concurrent table access. Why? - -This problem can be caused by a kernel that is not configured to support -semaphores. - -3.36) What tools are available for hooking postgres to Web pages? - -For web integration, PHP/FI is an excellent interface. The URL for that is -http://www.vex.net/php/ - -PHP is great for simple stuff, but for more complex stuff, some still use -the perl interface and CGI.pm. - -An example of using WWW with C to talk to Postgres is can be tried at: - - * http://www.ki.net/~mlc - -An WWW gatway based on WDB using perl can be downloaded from: - - * http://www.eol.ists.ca/~dunlop/wdb -p95 - -3.37) What is the time-warp feature and how does it relate to vacuum? - -Postgres95 handles data changes differently than most database systems. When -a row is changed in a table, the original row is marked with the time it was -changed, and a new row is created with the current data. By default, only -current rows are used in a table. If you specify a date/time after the table -name in a FROM clause, you can access the data that was current at that -time, i.e. SELECT * FROM employees ['July 24, 1996 09:00:00'] -displays employee rows in the table at the specified time. You can specify -intervals like [date,date], [date,], [,date], or [,]. This last option -accesses all rows that ever existed. - -INSERTed rows get a timestamp too, so rows that were not in the table at the -desired time will not appear. - -Vacuum removes rows that are no longer current. This time-warp feature is -used by the engine for rollback and crash recovery. Expiration times can be -set with purge. - -3.38) How do I tune the database engine for better performance? - -There are two things that can be done. You can use Openlink's option to -disable fsync() by starting the postmaster with a '-o -F' option. This will -prevent fsync()'s from flushing to disk after every transaction. - -You can also use the postmaster -B option to increase the number of shared -memory buffers shared among the backend processes. If you make this -parameter too high, the process will not start or crash unexpectedly. Each -buffer is 8K and the defualt is 64 buffers. - -3.39) What debugging features are available in Postgres95? - -Postgres95 has several features that report status information that can be -valuable for debugging purposes. - -First, by compiling with DEBUG defined, many assert()'s monitor the progress -of the backend and halt the program when something unexpected occurs. - -Both postmaster and postgres have several debug options available. First, -whenever you start the postmaster, make sure you send the standard output -and error to a log file, like: - - cd /usr/local/postgres95 + + + displays employee rows in the table at the specified time. You can + specify intervals like [date,date], [date,], [,date], or [,]. This + last option accesses all rows that ever existed. + + INSERTed rows get a timestamp too, so rows that were not in the table + at the desired time will not appear. + + Vacuum removes rows that are no longer current. This time-warp feature + is used by the engine for rollback and crash recovery. Expiration + times can be set with purge. + + In 6.0, once a table is vacuumed, the creation time of a row may be + incorrect, causing time-traval to fail. + + The time-travel feature will be removed in 7.0. + + 3.38) How do I tune the database engine for better performance? + + + + There are two things that can be done. You can use Openlink's option + to disable fsync() by starting the postmaster with a '-o -F' option. + This will prevent fsync()'s from flushing to disk after every + transaction. + + You can also use the postmaster -B option to increase the number of + shared memory buffers shared among the backend processes. If you make + this parameter too high, the process will not start or crash + unexpectedly. Each buffer is 8K and the defualt is 64 buffers. + + 3.39) What debugging features are available in PostgreSQL? + + + + PostgreSQL has several features that report status information that + can be valuable for debugging purposes. + + First, by compiling with DEBUG defined, many assert()'s monitor the + progress of the backend and halt the program when something unexpected + occurs. + + Both postmaster and postgres have several debug options available. + First, whenever you start the postmaster, make sure you send the + standard output and error to a log file, like: + + + cd /usr/local/pgsql ./bin/postmaster >server.log 2>&1 & -This will put a server.log file in the top-level postgres95 directory. This -file can contain useful information about problems or errors encountered by -the server. Postmaster has a -d option that allows even more detailed -information to be reported. The -d option takes a number 1-3 that specifies -the debug level. The query plans in a verbose debug file can be formatted -using the 'indent' program. (You may need to remove the '====' lines.) Be -warned that a debug level greater than one generates large log files. - -You can actuall run the postgres backend from the command line, and type -your SQL statement directly. This is recommended ONLY for debugging -purposes. Note that a newline terminates the query, not a semicolon. If you -have compiled with debugging symbols, you can perhaps use a debugger to see -what is happening. Because the backend was not started from the postmaster, -it is not running in an identical environment and locking/backend -interaction problems may not be duplicated. Some operating system can attach -to a running backend directly to diagnose problems. - -The postgres program has a -s, -A, -t options that can be very usefull for -debugging and performance measurements. - -The EXPLAIN command (see this FAQ) allows you to see how postgres95 is -iterpreting your query. - -3.40) What is an oid? What is a tid? - -Oids are Postgres's answer to unique row ids or serial columns. Every row -that is created in Postgres gets a unique oid. All oids generated by initdb -are less than 16384 (from backend/access/transam.h). All post-initdb -(user-created) oids are equal or greater that this. All these oids are -unique not only within a table, or database, but unique within the entire -postgres installation. - -Postgres uses oids in its internal system tables to link rows in separate -tables. These oids can be used to identify specific user rows and used in -joins. It is recommended you use column type oid to store oid values. See -the sql(l) manual page to see the other internal columns. - -Tids are used to indentify specific physical rows with block and offset -values. Tids change after rows are modified or reloaded. They are used by -index entries to point to physical rows. They can not be accessed through -sql. - -3.41) What is the meaning of some of the terms used in Postgres? - -Some of the source code and older documentation use terms that have more -common usage. Here are some: - - * row, record, tuple - * attribute, field, column - * table, class - * retrieve, select - * replace, update - * oid, serial value - * portal, cursor - * range variable, table name, table alias - -Please let me know if you think of any more. - ----------------------------------------------------------------------------- - -Section 4: Extending Postgres95 - -4.1) I wrote a user-defined function and when I run it in psql, it dumps -core. - -The problem could be a number of things. Try testing your user-defined -function in a stand alone test program first. Also, make sure you are not -sending elog NOTICES when the front-end is expecting data, such as during a -type_in() or type_out() functions - -4.2) I get messages of the type NOTICE:PortalHeapMemoryFree: 0x402251d0 not -in alloc set! - -You are pfree'ing something that was not palloc'ed. When writing -user-defined functions, do not include the file "libpq-fe.h". Doing so will -cause your palloc to be a malloc instead of a free. Then, when the backend -pfrees the storage, you get the notice message. - -4.3) I've written some nifty new types and functions for Postgres95. - -Please share them with other postgres95 users. Send your extensions to -mailing list, and they will eventually end up in the contrib/ subdirectory. - -4.4) How do I write a C function to return a tuple? - -This requires extreme wizardry, so extreme that the authors have not ever -tried it, though in principle it can be done. The short answer is ... you -can't. This capability is forthcoming in the future. - ----------------------------------------------------------------------------- - -Section 5: Bugs - -5.1) How do I find out about bug fixes? - -The directory ftp://ftp.ki.net/pub/postgres95 contains patches for the -latest release. - -5.2) How do I make a bug report? - -First, check to see that your bug is not one that has already been fixed -(question 5.1). Then, fill out the "bug-template" file and send it to: - - * pg95-dev@ki.net - -This is the address of the developers mailing list. + + + This will put a server.log file in the top-level PostgreSQL directory. + This file can contain useful information about problems or errors + encountered by the server. Postmaster has a -d option that allows even + more detailed information to be reported. The -d option takes a number + 1-3 that specifies the debug level. The query plans in a verbose debug + file can be formatted using the 'indent' program. (You may need to + remove the '====' lines in 1.* releases.) Be warned that a debug level + greater than one generates large log files in 1.* releases. + + You can actuall run the postgres backend from the command line, and + type your SQL statement directly. This is recommended ONLY for + debugging purposes. Note that a newline terminates the query, not a + semicolon. If you have compiled with debugging symbols, you can + perhaps use a debugger to see what is happening. Because the backend + was not started from the postmaster, it is not running in an identical + environment and locking/backend interaction problems may not be + duplicated. Some operating system can attach to a running backend + directly to diagnose problems. + + The postgres program has a -s, -A, -t options that can be very usefull + for debugging and performance measurements. + + The EXPLAIN command (see this FAQ) allows you to see how PostgreSQL is + iterpreting your query. + + 3.40) What is an oid? What is a tid? + + + + Oids are Postgres's answer to unique row ids or serial columns. Every + row that is created in Postgres gets a unique oid. All oids generated + by initdb are less than 16384 (from backend/access/transam.h). All + post-initdb (user-created) oids are equal or greater that this. All + these oids are unique not only within a table, or database, but unique + within the entire postgres installation. + + Postgres uses oids in its internal system tables to link rows in + separate tables. These oids can be used to identify specific user rows + and used in joins. It is recommended you use column type oid to store + oid values. See the sql(l) manual page to see the other internal + columns. + + Tids are used to indentify specific physical rows with block and + offset values. Tids change after rows are modified or reloaded. They + are used by index entries to point to physical rows. They can not be + accessed through sql. + + 3.41) What is the meaning of some of the terms used in Postgres? + + + + Some of the source code and older documentation use terms that have + more common usage. Here are some: + * row, record, tuple + * attribute, field, column + * table, class + * retrieve, select + * replace, update + * append, insert + * oid, serial value + * portal, cursor + * range variable, table name, table alias + + + + Please let me know if you think of any more. + + + _________________________________________________________________ + +Section 4: Extending PostgreSQL + + + + 4.1) I wrote a user-defined function and when I run it in psql, it dumps + core. + + + + The problem could be a number of things. Try testing your user-defined + function in a stand alone test program first. Also, make sure you are + not sending elog NOTICES when the front-end is expecting data, such as + during a type_in() or type_out() functions + + 4.2) I get messages of the type NOTICE:PortalHeapMemoryFree: 0x402251d0 not + in alloc set! + + + + You are pfree'ing something that was not palloc'ed. When writing + user-defined functions, do not include the file "libpq-fe.h". Doing so + will cause your palloc to be a malloc instead of a free. Then, when + the backend pfrees the storage, you get the notice message. + + 4.3) I've written some nifty new types and functions for PostgreSQL. + + + + Please share them with other PostgreSQL users. Send your extensions to + mailing list, and they will eventually end up in the contrib/ + subdirectory. + + 4.4) How do I write a C function to return a tuple? + + + + This requires extreme wizardry, so extreme that the authors have not + ever tried it, though in principle it can be done. The short answer is + ... you can't. This capability is forthcoming in the future. + + + _________________________________________________________________ + +Section 5: Bugs + + + + 5.1) How do I make a bug report? + + + + Check the current FAQ at http://postgreSQL.org + + Also check out our ftp site ftp://ftp.postgreSQL.org/pub to see if + there is a more recent PostgreSQL version. + + You can also fill out the "bug-template" file and send it to: + * hackers@postgreSQL.org + + + + This is the address of the developers mailing list. @@ -1,54 +1,56 @@ ==================================================== -TODO list (FAQ) for Postgres95 +TODO list (FAQ) for PostgreSQL ==================================================== -last updated: Thu Oct 3 17:59:06 EDT 1996 +last updated: Thu Dec 26 09:03:38 EST 1996 current maintainer: Bruce Momjian (maillist@candle.pha.pa.us) The most recent version of this document can be viewed at -the postgres95 WWW site, http://www.ki.net/postgres95. +the postgreSQL WWW site, http://www.postgreSQL.org. -Dashed items(-) are being worked on or are ready for the 2.0 release. +Changes included in the 6.0 release are listed at the end of this page. + +Dashed items(-) are being worked on for the post-6.0 release. + +Developers who have claimed items are: + Bruce is Bruce Momjian<maillist@candle.pha.pa.us> + Bryan is Bryan Henderson<bryanh@giraffe.netgate.net> + Dan is Dan McGuirk <mcguirk@indirect.com> + Darren is Darren King <darrenk@insightdist.com> + Edmund is Edmund Mergl <E.Mergl@bawue.de> + Erich Stamberger <eberger@gewi.kfunigraz.ac.at> + Kurt is "Kurt J. Lidl" <lidl@va.pubnix.com> + Martin is Martin S. Utesch <utesch@aut.tu-freiberg.de> + Vadim is "Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su> + Vivek is Vivek Khera <khera@kci.kciLink.com> RELIABILITY ----------- --Overhaul mdmgr/smgr to fix double unlinking and double opens, cleanup --Overhaul bufmgr/lockmgr/transaction manager -- remove -S (stable memory) option or fix memory manager --Memory leaks (hunt and destroy with tools like Purify) -- fix unitialized reads of memory -- fix array over-runs of memory writes --Fix CLUSTER --Prevent improper index creation --Change default decimal constant representation from float4 to float8 --Allow libpq to distinguish between text value '' and null -- using null bitmask returned from backend? +-Overhaul mdmgr/smgr to fix double unlinking and double opens, cleanup(Erich) +-Overhaul bufmgr/lockmgr/transaction manager(Vadim) +remove -S (stable memory) option or fix memory manager +Fix CLUSTER Fix all NULL features allow psql to print nulls meaningfully --Starting quote in insert string errors -ALTER TABLE bug - running postgress process needs to re-read table definition -Allow non-postgres users with createdb privs to destroydb's -Prevent SELECT NULL from crashing server - (error on first attempt, crash after another query) --Fix elusive btree range/non-range bug --Remove duplicate system catalog info or report mismatches Fix compile and security of Kerberos V code --Remove duplicate OIDS in system tables Dropping a table twice causes corruption, drop/create not rollback-able -Change index creation to process deleted and current rows +SUM aggregate on no rows or null value should return NULL, not zero +SELECT on two tables where zero or one table in WHERE and target + clause returns no rows +fix system to use oid index using constant without cast to oid +COUNT on VIEW always returns zero (maybe because there is no oid for views?) +SELECT ... INTO TABLE ... GROUP BY ... generates unlink error + if done three times with same INTO TABLE name (failure cleanup problem?) +CREATE VIEW requires super-user priviledge ENHANCEMENTS ------------ -Add full ANSI SQL capabilities ( a vendor has offered to help) +Add full ANSI SQL capabilities add subselects, possibility using temporary SQL functions Implement HAVING clause - Implement IN qualifier Implement EXISTS qualifier - Implement BETWEEN qualifier - add synonym of != for <> column constraints (using rules), esp. primary keys add DEFAULT, RESTRAINT, and CHECK capabilities - -add UNIQUE index capability report "Not implemented" if valid syntax is supplied add OUTER joins, left and right make VIEWs updateable where possible @@ -60,12 +62,13 @@ Add full ANSI SQL capabilities ( a vendor has offered to help) add the concept of dataspaces allow conversion type casts on select target fields Allow compression of large fields or a compressed field type -Fix the rules system +Fix the rules system(Jan?) robust making INSTEAD rules work + add CONSTRAINT Full set of text operations and functions word searches, concat, upper/lower(), max() on text, char --Replace table-level locking with row or page-level locking +Replace table-level locking with row or page-level locking(Vadim) Large objects overwriting blocks has problems there are other problems, too. @@ -75,9 +78,10 @@ Better interface for adding to pg_group Make multi-field indexes easier to create allow optimizer to effectively use parameters without accessing table Add int8 type +Add MONEY/DECIMAL type with defined precision Add table comments -Add support for tables >2G -Incorporate the PERL PG95 interface library into source tree +Add support for tables >2G, or test current version +Incorporate the PERL PG95 interface library into source tree(Edmund) Threaded version of the server or libpq Allow libpq to cancel query requests Add REGEX internationalization @@ -85,38 +89,40 @@ Add other language types for built-in functions expand to allow tcl, perl, java, generalize the function manager switch to pass function sources to interpreter engines. --Re-visit and fix vacuum -- can't vacuum large objects -- can't shrink tables, pg_time and pg_log -- allow actual compression, not just reuse on the same page -- allow vacuum to be run on one table or entire database + remove time-travel feature(Vadim) + reduce system column overhead(Vadmin) + remove pg_time table(Vadim) + allow row re-use without vacuum, maybe?(Vadim) split apart row removal function from statistics function - allow time-travel to be turned off so superceeded/deleted row are reused --Reverse meaning of HBA masks -Add hostname/user level access control rather than just hostname and user -Allow BY,ORDER BY to specify columns by number, or by non-alias table.column -Allow GROUP BY to use alias column name -Remove restriction that ORDER BY field must be in SELECT list? + can't vacuum large objects +Remove restriction that ORDER BY field must be in SELECT list(?) Allow queries about owner of datbases, tables like: SELECT u.usesysid FROM postgres.pg_user u; -DROP AGGREGATE should take in basetype as an arg +DROP AGGREGATE should take in basetype as an arg(Darren) Add word index for text fields, maybe with trigrams, i.e.: ' (cat | dog) & ! fox ' meaning text has cat or dog, but not fox Add common-sense constant type promotions --Allow uppercase agregates by lowercasing function names on input Allow readline-type or editor command editing of multi-line SQL commands Allow pg_dump to dump all databases at a site in one command --Allow restriction on who can create C functions -Allow restriction on who can create copy tables --Allow installation-configuration option to auto-add all local users Allow function result to be used in GROUP BY --Fontend COPY --Secure Authentication of local users --Idend authentication of local users +Populate backend status area and write program to dump status data +Add ALTER TABLE DROP COLUMN feature +Remove stale files upon startup(Vivek) +Add command to show privileges +Allow INSERT INTO ... SELECT to convert column types +Add syslog functionality +Allow aggregate for All types, like count(Darren) +Improve optimizer plan choice(Darren) +Add STDDEV/VARIANCE() function for standard deviation computation/variance +Add upper/lower functions +-Add table/column/function discription table indexed by oid +-make all identifiers case-insensitive(Bruce) PERFORMANCE ----------- Optimizing disjunctive queries +-Add Genetic Query Optimzation for many tables (Martin) +-Fix bushy-plans (Martin) Other optimizer bugs Is fsync use optimized? Multi-representational types, a la Illustra. For example, have a @@ -128,18 +134,123 @@ Speed up hash creation by using btree's fast sort/insert routines Cache most recent query plan(s?) Allow compression of log and meta data Allow LIKE/wildcard matches to use indexes if the wildcard character is not first +Add FILLFACTOR to index creation +Allow indexes to be used with OR clauses +-Add column optimization statistics to vacuum(Bruce) +-Change pg_attribute.attnvals name to attdispursion and change type float4 DOCUMENTATION ------------- Update usermanual source -Reduce size of regression diffs +remove time-travel in documentation(Bruce) +added features used in grammer but not in docs, like :: and CAST +add DECLARE manual pages +add EXPLAIN manual page PORTABILITY ----------- Windows NT port Mariposa project at Berkeley has a person who's working on this Win95 port, use GNU port or remove it? -Binary distributions for linux -Merge bsdi_2_1 to bsdi Merge i386_solaris and sparc_solaris into solaris Switch from PORTNAME to individual feature defines (use configure/autoconf?) +Change c.h "Index" and "bool" so they do not conflict with c++ + + +--------------------------------------------------------------------------- + + CHANGES IN 6.0 RELEASE + + +Restructured object file generation/location(Bryan, Marc) +Restructured port-specific file locations(Bryan, Marc) +Makefile restructuring(Bryan, Marc) +all functions now have prototypes that are compared against the calls +unused/uninialized variables corrected +gcc -Wall compiles cleanly with warnings only from unfixable constructs +major include file reorganization/reduction(Marc) +reduced the number of #define's, centeralized #define's +removed many os-specific #define's +fixed a few small memory leaks +removed duplicate system oid's +overhauled parser/analyze code to properly report errors and increase speed +properly report errors when INSERT ... SELECT columns did not match +properly report errors when insert column names were not correct +minor improvements to rules system +execute lowercase function names if not found with exact case +change oid constants used in code to #define names +Memory leaks (hunt and destroy with tools like Purify(Kurt) +fix unitialized reads of memory(Kurt) +fix array over-runs of memory writes(Kurt) +Change default decimal constant representation from float4 to float8(Bruce) +Allow libpq to distinguish between text value '' and null(Bruce) +Starting quote in insert string errors(Bruce) +ALTER TABLE bug - running postgress process needs to re-read table definition +Allow non-postgres users with createdb privs to destroydb's +Prevent SELECT NULL from crashing server (Bruce) +Fix elusive btree range/non-range bug(Dan) +Remove duplicate system catalog info or report mismatches(Dan) +Remove duplicate OIDS in system tables(Dan) +Prevent postmaster from being run as root +Implement IN qualifier(Bruce) +Implement BETWEEN qualifier(Bruce) +add synonym of != for <>(Bruce) +add UNIQUE index capability(Dan) +Re-visit and fix vacuum(Vadim) +speed up vacuum(Vadim) +can't shrink tables, pg_time and pg_log(Vadim & Erich) +allow actual compression, not just reuse on the same page(Vadim) +allow vacuum to be run on one table or entire database(Bruce) +Reverse meaning of HBA masks(Bryan) +Add hostname/user level access control rather than just hostname and user +Allow restriction on who can create C functions(Bryan) +Allow installation-configuration option to auto-add all local users(Bryan) +Allow restriction on who can do backend COPY(Bryan) +allow COPY from the frontend(Bryan) +Secure Authentication of local users(Bryan) +Idend authentication of local users(Bryan) +Change debug level 2 to print queries only, changed debug heading layout(Bruce) +Reduce size of regression diffs, remove timezone name difference(Bruce) +Remove compile-time parameters to enable binary distributions(Bryan) +Merge bsdi_2_1 to bsdi(Bruce) +pg_dump(Bruce) + allow dump of oid's + create indexes after tables are loaded for speed + pg_dump -d now handles NULL's properly +psql(Bruce) + fixed problem with multiple statements on one line with multiple outputs + changed command prompt for lines in query or in quotes + allow backslashed, semicolons anywhere on the line + \h and \? is now readable + \g filename now works + updated help syntax + char(3) now displays as (bp)char in \d output + return code now more accurate(Bryan?) +new config.h file(Marc, Bryan) +fix permissions on lo_export()(Bruce) +Make now stops on compile failure(Bryan) +submiting an empty query now returns empty status, not just " " query(Bruce) +several fixes for queries that crashed the backend +portability additions, including Ultrix, DG/UX, AIX, and Solaris +fix for pg_log size explosion +decoupled sparc and solaris defines(Kurt) +new options to initdb(Bryan) +allow asserts to be disabled easly from Makefile.global(Bruce) +allow "select oid,* from table" +NOTIFY fixes +PG_VERSION now set to 6.0 and used by postmaster +libpq has PQgetisnull()(Bruce) +pginterface additions for NULL values(Bruce) +fixed ALTER TABLE ... char(3) bug(Bruce) +fixed output of group acl permissions +new asserts for run-checking +name change from Postgres95 to PostgreSQL +monitor program removed +fix for hash indexes on some types like time and date +gist now included in the distrubution(Marc) +array fixes +fixes for aggregate/GROUP processing, allow 'select sum(func(x),sum(x+y) from z' +libpq++ improvements +Allow BY,ORDER BY to specify columns by number, or by non-alias table.column(Bruce) +Allow GROUP BY to use alias column name(Bruce) + |