diff options
| author | Bruce Momjian <bruce@momjian.us> | 1997-07-13 20:00:14 +0000 |
|---|---|---|
| committer | Bruce Momjian <bruce@momjian.us> | 1997-07-13 20:00:14 +0000 |
| commit | 39cefa66c9765fa8bfd8cde7897ed16ea0cda139 (patch) | |
| tree | bb5e25328690f55f83701f8cc8bb9011d8477ce7 /doc/FAQ | |
| parent | 69e5d63839a551a931933bce9a4a06c06732a480 (diff) | |
| download | postgresql-39cefa66c9765fa8bfd8cde7897ed16ea0cda139.tar.gz postgresql-39cefa66c9765fa8bfd8cde7897ed16ea0cda139.zip | |
Updates for 6.1.1.
Diffstat (limited to 'doc/FAQ')
| -rw-r--r-- | doc/FAQ | 1925 |
1 files changed, 897 insertions, 1028 deletions
@@ -1,670 +1,585 @@ -<HTML> -<HEAD> -<TITLE>PostgreSQL FAQ</title> -</HEAD> -<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#FF0000" VLINK="#A00000" ALINK="#0000FF"> -<H1> -Frequently Asked Questions (FAQ) for PostgreSQL -</H1> -<P> -Last updated: Wed Jun 11 10:44:40 EDT 1997 -<BR> -Version: 6.1 -<P> -Current maintainer: Bruce Momjian (<a -href="mailto:maillist@candle.pha.pa.us">maillist@candle.pha.pa.us</a>)<BR> -<P> -The most recent version of this document can be viewed at -the postgreSQL Web site, <a -href="http://postgreSQL.org">http://postgreSQL.org</a>. -<P> -Linux-specific questions are answered in -<a href="http://postgreSQL.org/docs/FAQ-Linux.phtml">http://postgreSQL.org/docs/FAQ-Linux.phtml</a>. -<P> -Irix-specific questions are answered in -<a href="http://postgreSQL.org/docs/FAQ-Irix.phtml">http://postgreSQL.org/docs/FAQ-Irix.phtml</a>. -<P> -Changes in this version (* = modified, + = new): -<UL> -<LI>3.42) What is Genetic Query Optimization?<BR> -<LI>3.43) I am running Solaris and my dates display wrong. Why?<BR> -</UL> -<HR> -<P> -<H2>Questions answered:</H2> -<H3> 1) General questions</H3> -<a href="#1.1">1.1</a>) What is PostgreSQL?<BR> -<a href="#1.2">1.2</a>) What does PostgreSQL run on?<BR> -<a href="#1.3">1.3</a>) Where can I get PostgreSQL?<BR> -<a href="#1.4">1.4</a>) What's the copyright on PostgreSQL?<BR> -<a href="#1.5">1.5</a>) Support for PostgreSQL<BR> -<a href="#1.6">1.6</a>) Latest release of PostgreSQL<BR> -<a href="#1.7">1.7</a>) Is there a commercial version of PostgreSQL?<BR> -<a href="#1.9">1.9</a>) What version of SQL does PostgreSQL use?<BR> -<a href="#1.10">1.10</a>) Does PostgreSQL work with databases from -earlier versions of postgres?<BR> -<a href="#1.11">1.11</a>) How many people use PostgreSQL?<BR> -<H3> 2) Installation questions</H3> -<a href="#2.1">2.1</a>) initdb doesn't run<BR> -<a href="#2.2">2.2</a>) when I start up the postmaster, I get - "FindBackend: could not find a backend to execute..." - "postmaster: could not find backend to execute..."<BR> -<a href="#2.3">2.3</a>) The system seems to be confused about commas, -decimal points, and date formats.<BR> -<a href="#2.4">2.4</a>) How do I install PostgreSQL somewhere other than -/usr/local/pgsql?<BR> -<a href="#2.5">2.5</a>) When I run postmaster, I get a Bad System Call -core dumped message.<BR> -<a href="#2.6">2.6</a>) When I try to start the postmaster, I get -IpcMemoryCreate errors.<BR> -<a href="#2.7">2.7</a>) I have changed a source file, but a -recompile does not see the change?<BR> -<H3> 3) Operational questions</H3> -<a href="#3.1">3.1</a>) How do I specify a KEY or other constraints on a -column?<BR> -<a href="#3.2">3.2</a>) Does PostgreSQL support nested subqueries?<BR> -<a href="#3.3">3.3</a>) How do I define a unique indices?<BR> -<a href="#3.4">3.4</a>) I've having a lot of problems using rules.<BR> -<a href="#3.5">3.5</a>) I can't seem to write into the middle of large -objects reliably.<BR> -<a href="#3.6">3.6</a>) Does PostgreSQL have a graphical user interface? -A report generator? A embedded query language interface?<BR> -<a href="#3.7">3.7</a>) How can I write client applications to -PostgreSQL?<BR> -<a href="#3.8">3.8</a>) How do I prevent other hosts from accessing my -PostgreSQL<BR> -<a href="#3.9">3.9</a>) How do I set up a pg_group?<BR> -<a href="#3.10">3.10</a>) What is the exact difference between -binary cursors and normal cursors?<BR> -<a href="#3.11">3.11</a>) Why doesn't the != operator work?<BR> -<a href="#3.12">3.12</a>) What is a R-tree index and what is it -used for?<BR> -<a href="#3.13">3.13</a>) What is the maximum size for a -tuple?<BR> -<a href="#3.14">3.14</a>) I defined indices but my queries don't -seem to make use of them. Why?<BR> -<a href="#3.15">3.15</a>) Are there ODBC drivers for -PostgreSQL?<BR> -<a href="#3.16">3.16</a>) How do I use postgres for -multi-dimensional indexing (> 2 dimensions)?<BR> -<a href="#3.17">3.17</a>) How do I do regular expression searches? -case-insensitive regexp searching?<BR> -<a href="#3.18">3.18</a>) I can't access the database as the -'root' user.<BR> -<a href="#3.19">3.19</a>) I experienced a server crash during a -vacuum. How do I remove the lock file?<BR> -<a href="#3.20">3.20</a>) What is the difference between the -various character types?<BR> -<a href="#3.21">3.21</a>) In a query, how do I detect if a field -is NULL?<BR> -<a href="#3.22">3.22</a>) How do I see how the query optimizer is -evaluating my query?<BR> -<a href="#3.23">3.23</a>) How do I create a serial field?<BR> -<a href="#3.24">3.24</a>) How do I create a multi-column -index?<BR> -<a href="#3.25">3.25</a>) What are the temp_XXX files in my -database directory?<BR> -<a href="#3.26">3.26</a>) Why are my table files not getting any -smaller after a delete?<BR> -<a href="#3.27">3.27</a>) Why can't I connect to my database from -another machine?<BR> -<a href="#3.28">3.28</a>) I get the error 'default index class -unsupported' when creating an index. How do I do it?<BR> -<a href="#3.29">3.29</a>) Why does creating an index crash the -backend server?<BR> -<a href="#3.30">3.30</a>) How do I find out what indexes or -operations are defined in the database?<BR> -<a href="#3.31">3.31</a>) Why do statements require an extra character at -the end? Why does 'createuser' return 'unexpected last match in -input()'? Why does pg_dump fail?<BR> -<a href="#3.32">3.32</a>) All my servers crash under concurrent -table access. Why?<BR> -<a href="#3.33">3.33</a>) What tools are available for hooking -postgres to Web pages?<BR> -<a href="#3.34">3.34</a>) What is the time-warp feature and how -does it relate to vacuum?<BR> -<a href="#3.35">3.35</a>) How do I tune the database engine for -better performance?<BR> -<a href="#3.36">3.36</a>) What debugging features are available in -PostgreSQL?<BR> -<a href="#3.37">3.37</a>) What is an oid? What is a tid?<BR> -<a href="#3.38">3.38</a>) What is the meaning of some of the terms -used in Postgres?<BR> -<a href="#3.39">3.39</a>) What is Genetic Query Optimization?<BR> -<a href="#3.40">3.40</a>) I am running Solaris and my dates -display wrong. Why? -<a href="#3.41">3.41</a>) How do I enable more than 32 concurrent -backends? -<H3> 4) Questions about extending PostgreSQL</H3> -<a href="#4.1">4.1</a>) I wrote a user-defined function and when I run -it in psql, it dumps core.<BR> -<a href="#4.2">4.2</a>) I get messages of the type -NOTICE:PortalHeapMemoryFree: 0x402251d0<BR> -<a href="#4.3">4.3</a>) I've written some nifty new types and functions -for PostgreSQL.<BR> -<a href="#4.4">4.4</a>) How do I write a C function to return a -tuple?<BR> -<H3> 5) Bugs</H3> -<a href="#5.1">5.1</a>) How do I make a bug report? -<HR> <H2> Section 1: General Questions</H2> <H3><a -name="1.1">1.1</a>) What is PostgreSQL?</H3> + Frequently Asked Questions (FAQ) for PostgreSQL + + Last updated: Sun Jul 13 15:26:53 EDT 1997 + Version: 6.1.1 + + 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): + * 3.42) What is Genetic Query Optimization? + * 3.43) I am running Solaris and my dates display wrong. Why? + + _________________________________________________________________ + +Questions answered: -<P> -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. -<P> -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 (<a -href="mailto:scrappy@postgreSQL.org">scrappy@postgreSQL.org</a>). (See -below on how to join). This team is now responsible for all current and -future development of PostgreSQL. -<P> -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. -<P> -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. -<P> -<H3><a name="1.2">1.2</a>) What does PostgreSQL run -on?</H3> -<P> -The authors have compiled and tested PostgreSQL on the following -platforms(some of these compiles require gcc 2.7.0): -<UL> -<LI> aix - IBM on AIX 3.2.5 -<LI> alpha - DEC Alpha AXP on OSF/1 2.0 -<LI> BSD44_derived - OSs derived from 4.4-lite BSD (NetBSD, FreeBSD) -<LI> bsdi - BSD/OS 2.0, 2.01, 2.1, 3.0 -<LI> dgux - DG/UX 5.4R3.10 -<LI> hpux - HP PA-RISC on HP-UX 9.0 -<LI> i386_solaris - i386 Solaris -<LI> irix5 - SGI MIPS on IRIX 5.3 -<LI> linux - Intel x86 on Linux 1.2 and Linux ELF - (For non-ELF Linux, see LINUX_ELF below). -<LI> sparc_solaris - SUN SPARC on Solaris 2.4 -<LI> sunos4 - SUN SPARC on SunOS 4.1.3 -<LI> svr4 - Intel x86 on Intel SVR4 -<LI> ultrix4 - DEC MIPS on Ultrix 4.4 -</UL> -The following platforms have known problems/bugs: -<UL> -<LI> nextstep - Motorola MC68K or Intel x86 on NeXTSTEP 3.2 -</UL> -<P> -<H3><a name="1.3">1.3</a>) Where can I get PostgreSQL?</H3> -<P> The primary anonymous ftp site for PostgreSQL is: -<UL> -<LI> <a -href="ftp://ftp.postgreSQL.org/pub">ftp://ftp.postgreSQL.org/pub</a> -</UL> -<P> A mirror site exists at: -<UL> -<LI> <a -href="ftp://postgres95.vnet.net/pub/postgres95">ftp://postgres95.vnet.net/pub/postgres95</a> -<LI> <a -href="ftp://ftp.luga.or.at/pub/postgres95">ftp://ftp.luga.or.at/pub/postgres95</a> -<LI> <a -href="ftp://cal011111.student.utwente.nl/pub/postgres95">ftp://cal011111.student.utwente.nl/pub/postgres95</a> -<LI> <a -href="ftp://ftp.uni-trier.de/pub/database/rdbms/postgres/postgres95">ftp://ftp.uni-trier.de/pub/database/rdbms/postgres/postgres95</a> -<LI> <a -href="ftp://rocker.sch.bme.hu">ftp://rocker.sch.bme.hu</a> -</UL> -<H3><a name="1.4">1.4</a>) What's the copyright on -PostgreSQL?</H3> -<P> -PostgreSQL is subject to the following COPYRIGHT. -<P> -PostgreSQL Data Base Management System -<P> -Copyright (c) 1994-6 Regents of the University of California -<P> -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. -<P> -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. -<P> -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. -<P> -<H3><a name="1.5">1.5</a>) Support for PostgreSQL </H3> -<P> -There is no official support for PostgreSQL from the original -maintainers or from University of California, Berkeley. It is -maintained through volunteer effort only. -<P> -The main mailing list is: <a -href="mailto:questions@postgreSQL.org">questions@postgreSQL.org</a>. 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) -<PRE> -<CODE> - subscribe - end -</CODE> -</PRE> -<P> -to <a -href="mailto:questions-request@postgreSQL.org">questions-request@postgreSQL.org</a>. -<P> -There is also a digest list available. To subscribe to this list, send -email to: -<a -href="mailto:questions-digest-request@postgreSQL.org"> -questions-digest-request@postgreSQL.org</a> with a BODY of: -<PRE> -<KBD> - subscribe - end -</KBD> -</PRE> -Digests are sent out to members of this list whenever the main list has -received around 30k of messages. -<P> -There is a bugs mailing list available. To subscribe to this -list, send email to <a -href="mailto:bugs-request@postgreSQL.org">bugs-request@postgreSQL.org</a> -with a BODY of: -<P> -There is also a developers discussion mailing list available. To subscribe to this -list, send email to <a -href="mailto:hackers-request@postgreSQL.org">hackers-request@postgreSQL.org</a> -with a BODY of: -<P> -<PRE> -<KBD> - subscribe - end -</KBD> -</PRE> -<P> -Additional information about PostgreSQL can be found via the PostgreSQL -WWW home page at: -<BLOCKQUOTE> - <a -href="http://postgreSQL.org">http://postgreSQL.org</a> -</BLOCKQUOTE> -<P> -<H3><a name="1.6">1.6</a>) Latest release of PostgreSQL</H3> -<P> -The latest release of PostgreSQL is version 6.0, which was released on -January 31, 1997. 6.1 is scheduled for release soon. For information -about what is new in 6.1, see our TODO list on our WWW page. -<P> -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. -<P> -<H3><a name="1.7">1.7</a>) Is there a commercial version of PostgreSQL?</H3> -<P> -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 <a -href="mailto:sales@illustra.com">sales@illustra.com</a> -<P> -<H3><a name="1.8">1.8</a>) What documentation is available for PostgreSQL?</H3> -<P> -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. -<P> -The www page contains pointers to an implementation guide and five -papers written about postgres design concepts and features. -<P> -<H3><a name="1.9">1.9</a>) What version of SQL does PostgreSQL use?</H3> -<P> -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: -<UL> -<LI> no support for nested subqueries -<LI> no HAVING clause under a GROUP BY -</UL> -<P> -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. -<P> -<H3><a name="1.10">1.10</a>) Does PostgreSQL work with databases from -earlier versions of postgres?</H3> -<P> -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. -<P> -Upgrading to 6.0 requires a dump and restore from previous releases. -<P> -Upgrading to 6.1 requires a dump and restore from previous releases. -<P> -Those ugrading from versions earlier than 1.09 must upgrade to 1.09 -first without a dump/reload, then dump the data from 1.09, and then load -it into 6.0 or 6.1. -<P> -<H3><a name="1.11">1.11</a>) How many people use PostgreSQL?</H3> -<P> -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. -<P> -<HR> -<H2> Section 2: Installation Questions -</H2> -<P> -<H3><a name="2.1">2.1</a>) initdb doesn't run</H3> -<P> -<UL> -<LI> check to see that you have the proper paths set -<LI> check that the 'postgres' user owns all the right files -<LI> 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 -</UL> -<P> -<H3><a name="2.2">2.2</a>) when I start up the postmaster, I get - "FindBackend: could not find a backend to execute..." - "postmaster: could not find backend to execute..."</H3> -<P> -You probably do not have the right path set up. The 'postgres' -executable needs to be in your path. -<P> -<H3><a name="2.3">2.3</a>) The system seems to be confused about commas, -decimal points, and date formats.</H3> -<P> -Check your locale configuration. PostgreSQL uses the locale settings of -the user that ran the postmaster process. Set those accordingly for -your operating environment. -<P> -<H3><a name="2.4">2.4</a>) How do I install PostgreSQL somewhere other than -/usr/local/pgsql?</H3> -<P> -You need to edit Makefile.global and change POSTGRESDIR accordingly, or -create a Makefile.custom and define POSTGRESDIR there. -<P> -<H3><a name="2.5">2.5</a>) When I run postmaster, I get a Bad System -Call core dumped message.</H3> -<P> -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. -<P> -<H3><a name="2.6">2.6</a>) When I try to start the postmaster, I get -IpcMemoryCreate errors.</H3> -<P> -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. -<P> -<H3><a name="2.7">2.7</a>) I have changed a source file, but a -recompile does not see the change?</H3> -<P> -The Makefiles do not have the proper dependencies for include files. You -have to do a 'make clean' and then another 'make'. -<P> -<HR> -<H2> Section 3: PostgreSQL Features -</H2> + 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) When I run postmaster, I get a Bad System Call core dumped + message. + 2.6) When I try to start the postmaster, I get IpcMemoryCreate errors. + 2.7) I have changed a source file, but a recompile does not see the + change? + + 3) Operational 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 find out what indexes or operations are defined in the + database? + 3.31) Why do statements require an extra character at the end? Why + does 'createuser' return 'unexpected last match in input()'? Why does + pg_dump fail? + 3.32) All my servers crash under concurrent table access. Why? + 3.33) What tools are available for hooking postgres to Web pages? + 3.34) What is the time-warp feature and how does it relate to vacuum? + 3.35) How do I tune the database engine for better performance? + 3.36) What debugging features are available in PostgreSQL? + 3.37) What is an oid? What is a tid? + 3.38) What is the meaning of some of the terms used in Postgres? + 3.39) What is Genetic Query Optimization? + 3.40) I am running Solaris and my dates display wrong. Why? 3.41) How + do I enable more than 32 concurrent backends? + + 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 -<P> -<H3><a name="3.1">3.1</a>) How do I specify a KEY or other constraints on a -column?</H3> -<P> -Column constraints are not supported in PostgreSQL. As a consequence, -the system does not check for duplicates. -<P> -Under 6.0, create a unique index on the column. Attempts to create -duplicate of that column will report an error. -<P> -<H3><a name="3.2">3.2</a>) Does PostgreSQL support nested subqueries?</H3> -<P> -Subqueries are not implemented, but they can be simulated using sql -functions. -<P> -<H3><a name="3.3">3.3</a>) How do I define a unique indices?</H3> -<P> -PostgreSQL 6.0 supports unique indices. -<P> -<H3><a name="3.4">3.4</a>) I've having a lot of problems using rules.</H3> -<P> -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. -<P> -<H3><a name="3.5">3.5</a>) I can't seem to write into the middle of large -objects reliably.</H3> -<P> -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. -<P> -<H3><a name="3.6">3.6</a>) Does PostgreSQL have a graphical user interface? -A report generator? A embedded query language interface?</H3> -<P> -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. -<P> -<H3><a name="3.7">3.7</a>) How can I write client applications to PostgreSQL?</H3> -<P> -PostgreSQL supports a C-callable library interface called libpq as well -as a Tcl-based library interface called libtcl. -<P> -Others have contributed a perl interface and a WWW gateway to -PostgreSQL. See the PostgreSQL home pages for more details. -<P> -<H3><a name="3.8">3.8</a>) How do I prevent other hosts from accessing my -PostgreSQL backend?</H3> -<P> -Use host-based authentication by modifying the file $PGDATA/pg_hba -accordingly. -<P> -<H3><a name="3.9">3.9</a>) How do I set up a pg_group?</H3> -<P> -Currently, there is no easy interface to set up user groups. You have to -explicitly insert/update the pg_group table. For example: -<PRE> -<CODE> - jolly=> insert into pg_group (groname, grosysid, grolist) - jolly=> values ('posthackers', '1234', '{5443, 8261}'); - INSERT 548224 - jolly=> grant insert on foo to group posthackers; - CHANGE - jolly=> -</CODE> -</PRE> -<P> - The fields in pg_group are: -<UL> -<LI> groname: the group name. This a char16 and should - be purely alphanumeric. Do not include underscores - or other punctuation. -<LI> grosysid: the group id. This is an int4. - This should be unique for each group. -<LI> grolist: the list of pg_user id's that belong in the group. - This is an int4[]. -</UL> -<P> -<H3><a name="3.10">3.10</a>) What is the exact difference between binary -cursors and normal cursors?</H3> -<P> -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. -<P> -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. -<P> -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. -<P> -<H3><a name="3.11">3.11</a>) Why doesn't the != operator work?</H3> -<P> -SQL specifies <> as the inequality operator, and that is what we -have defined for the built-in types. -<P> -In 6.0, != is equivalent to <>. -<P> -<H3><a name="3.12">3.12</a>) What is a R-tree index and what is it used for?</H3> -<P> -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. -<P> -The canonical paper that describes the original R-Tree design is: -<P> -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. -<P> -You can also find this paper in Stonebraker's "Readings in Database -Systems" -<P> -<H3><a name="3.13">3.13</a>) What is the maximum size for a tuple?</H3> -<P> -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. -<P> -Tuples do not cross 8k boundaries so a 5k tuple will require 8k of -storage. -<P> -<H3><a name="3.14">3.14</a>) I defined indices but my queries don't seem -to make use of them. Why?</H3> -<P> -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). -<P> -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. -<P> -See the create_index manual page for information on what type classes -are available. It must match the field type. -<P> -Postgres does not warn the user when the improper index is created. -<P> -Indexes not used for ORDER BY operations. -<P> -<H3><a name="3.15">3.15</a>) Are there ODBC drivers for PostgreSQL?</H3> -<P> -There are two ODBC drivers available, PostODBC and OpenLink ODBC. -<P> -For all people being interested in PostODBC, there are now two mailing -lists devoted to the discussion of PostODBC. The mailing lists are: -<UL> -<LI> <a -href="mailto:postodbc-users@listserv.direct.net">postodbc-users@listserv.direct. -net</a> -<LI> <a -href="mailto:postodbc-developers@listserv.direct.net">postodbc-developers@listse -rv.direct.net</a> -</UL> -<P> -these lists are ordinary majordomo mailing lists. You can subscribe by -sending a mail to: -<UL> -<LI> <a -href="mailto:majordomo@listserv.direct.net">majordomo@listserv.direct.net</a> -</UL> -<P> -OpenLink ODBC is currently in beta under Linux. You can get it from <a -href="http://www.openlinksw.com/postgres.html"> -http://www.openlinksw.com/postgres.html</a>. 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). -<P> -We will probably be selling this product to people who need -commercial-quality support, but a freeware version will always be -available. Questions to <a -href="mailto:postgres95@openlink.co.uk">postgres95@openlink.co.uk</a>. -<P> -<H3><a name="3.16">3.16</a>) How do I use postgres for multi-dimensional -indexing (> 2 dimensions)?</H3> -<P> -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. -<P> -<H3><a name="3.17">3.17</a>) How do I do regular expression searches? -case-insensitive regexp searching?</H3> -<P> -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. -<P> -<H3><a name="3.18">3.18</a>) I can't access the database as the 'root' user.</H3> -<P> -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. -<P> -<H3><a name="3.19">3.19</a>) I experienced a server crash during a -vacuum. How do I remove the lock file?</H3> -<P> -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 -<PRE> -<SAMP> - WARN:can't create lock file -- another vacuum cleaner running? -</SAMP> -</PRE> -<P> -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/<dbName>) -<P> -<H3><a name="3.20">3.20</a>) What is the difference between the various -character types?</H3> -<PRE> + 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): + * aix - IBM on AIX 3.2.5 or 4.x + * alpha - DEC Alpha AXP on OSF/1 2.0 + * BSD44_derived - OSs derived from 4.4-lite BSD (NetBSD, FreeBSD) + * bsdi - BSD/OS 2.0, 2.01, 2.1, 3.0 + * dgux - DG/UX 5.4R3.10 + * hpux - HP PA-RISC on HP-UX 9.0 + * i386_solaris - i386 Solaris + * irix5 - SGI MIPS on IRIX 5.3 + * linux - Intel x86 on Linux 1.2 and Linux ELF (For non-ELF Linux, + see LINUX_ELF below). + * sparc_solaris - SUN SPARC on Solaris 2.4 + * sunos4 - SUN SPARC on SunOS 4.1.3 + * svr4 - Intel x86 on Intel SVR4 + * ultrix4 - DEC MIPS on Ultrix 4.4 + + The following platforms have known problems/bugs: + * nextstep - Motorola MC68K or Intel x86 on NeXTSTEP 3.2 + + 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) + + + subscribe + end + + to questions-request@postgreSQL.org. + + There is also a digest list available. To subscribe to this list, send + email to: questions-digest-request@postgreSQL.org with a BODY of: + + + subscribe + end + + Digests are sent out to members of this list whenever the main list + has received around 30k of messages. + + There is a bugs mailing list available. To subscribe to this list, + send email to bugs-request@postgreSQL.org with a BODY of: + + There is also a developers discussion mailing list available. To + subscribe to this list, send email to hackers-request@postgreSQL.org + with a BODY of: + + + subscribe + end + + 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 6.1.1, which will be + released in July, 1997. For information about what is new in 6.1.1, + 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 from previous releases. + + Upgrading to 6.1 requires a dump and restore from previous releases. + + Upgrading from 6.1 to 6.1.1 requires a compile of the new release, + recompile of all your custom applications to use the new libpq, and + then an install while the postmaster is temporarily stopped. + + Those ugrading from versions earlier than 1.09 must upgrade to 1.09 + first without a dump/reload, then dump the data from 1.09, and then + load it into 6.1.1. + + 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 edit Makefile.global and change POSTGRESDIR accordingly, + or create a Makefile.custom and define POSTGRESDIR there. + + 2.5) 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.6) 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.7) 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: + + + jolly=> insert into pg_group (groname, grosysid, grolist) + jolly=> values ('posthackers', '1234', '{5443, 8261}'); + INSERT 548224 + jolly=> grant insert on foo to group posthackers; + 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. + + 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 + + + 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/<dbName>) + + 3.20) What is the difference between the various character types? + Type Internal Name Notes -------------------------------------------------- CHAR char 1 character } @@ -676,368 +591,322 @@ CHAR(#) bpchar blank padded to the specified fixed length VARCHAR(#) varchar size specifies maximum length, no padding TEXT text length limited only by maximum tuple length BYTEA bytea variable-length array of bytes -</PRE> -<P> -Remember, you need to use the internal name when creating indexes on -these fields or when doing other internal operations. -<P> -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. -<P> -<H3><a name="3.21">3.21</a>) In a query, how do I detect if a field is NULL?</H3> -<P> -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. -<P> -<H3><a name="3.22">3.22</a>) How do I see how the query optimizer is -evaluating my query?</H3> -<P> -Place the word 'EXPLAIN' at the beginning of the query, for example: -<PRE> -<CODE> - EXPLAIN SELECT * FROM table1 WHERE age = 23; -</CODE> -</PRE> -<P> -<H3><a name="3.23">3.23</a>) How do I create a serial field?</H3> -<P> -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. -<P> -Another valid way of doing this is to create a function: -<PRE> -<CODE> - create table my_oids (f1 int4); - insert into my_oids values (1); - create function new_oid () returns int4 as - 'update my_oids set f1 = f1 + 1; select f1 from my_oids; ' - language 'sql'; -</CODE> -</PRE> -<P> -then: -<PRE> -<CODE> - create table my_stuff (my_key int4, value text); - insert into my_stuff values (new_oid(), 'hello'); -</CODE> -</PRE> -<P> -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. -<P> -Sequences are implemented in 6.1 -<P> -<H3><a name="3.24">3.24</a>) How do I create a multi-column index?</H3> -<P> -In 6.0, 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. -<P> -In 6.1, this feature is available. -<P> -<H3><a name="3.25">3.25</a>) What are the temp_XXX files in my database -directory?</H3> -<P> -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. -<P> -If you have no transactions or sorts running at the time, it is safe to -delete the temp_ files. -<P> -<H3><a name="3.26">3.26</a>) Why are my table files not getting any -smaller after a delete?</H3> -<P> -If you run vacuum in pre-6.0, unused rows will be marked for reuse, but -the file blocks are not released. -<P> -In 6.0, vacuum properly shrinks tables. -<P> -<H3><a name="3.27">3.27</a>) Why can't I connect to my database from -another machine?</H3> -<P> -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. -<P> -<H3><a name="3.28">3.28</a>) I get the error 'default index class -unsupported' when creating an index. How do I do it?</H3> -<P> -You probably used: -<PRE> -<CODE> - create index idx1 on person using btree (name); -</CODE> -</PRE> -<P> -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). -<P> -Version 6.0, if you do not specify a class_type, it defaults to the -proper type for the column. -<P> -<H3><a name="3.29">3.29</a>) Why does creating an index crash the -backend server?</H3> -<P> -You have probably defined an incorrect *_ops type class for the field -you are indexing. -<P> -<H3><a name="3.30">3.30</a>) How do I find out what indexes or -operations are defined in the database?</H3> -<P> -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. -<P> -<H3><a name="3.31">3.31</a>) Why do statements require an extra character at -the end? Why does 'createuser' return 'unexpected last match in input()'? -Why does pg_dump fail?</H3> -<P> -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. -<P> -<H3><a name="3.32">3.32</a>) All my servers crash under concurrent table -access. Why?</H3> -<P> -This problem can be caused by a kernel that is not configured to support -semaphores. -<P> -<H3><a name="3.33">3.33</a>) What tools are available for hooking -postgres to Web pages?</H3> -<P> -For web integration, PHP/FI is an excellent interface. The URL for that -is <a href="http://www.vex.net/php/">http://www.vex.net/php/</a> -<P> -PHP is great for simple stuff, but for more complex stuff, some still -use the perl interface and CGI.pm. -<P> -An example of using WWW with C to talk to Postgres is can be tried at: -<UL> -<LI> - <a href="http://postgreSQL.org/%7Emlc">http://postgreSQL.org/~mlc</a> -</UL> -<P> -An WWW gatway based on WDB using perl can be downloaded from: -<UL> -<LI> - <a -href="http://www.eol.ists.ca/~dunlop/wdb-p95">http://www.eol.ists.ca/~dunlop/wdb --p95</a> -</UL> -<H3><a name="3.34">3.34</a>) What is the time-warp feature and how does -it relate to vacuum?</H3> -<P> -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. -<PRE> -<CODE> - SELECT * - FROM employees ['July 24, 1996 09:00:00'] -</CODE> -</PRE> -<P> -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. -<P> -INSERTed rows get a timestamp too, so rows that were not in the table at -the desired time will not appear. -<P> -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. -<P> -In 6.0, once a table is vacuumed, the creation time of a row may be -incorrect, causing time-traval to fail. -<P> -The time-travel feature will be removed in 7.0. -<P> -<H3><a name="3.35">3.35</a>) How do I tune the database engine for -better performance?</H3> -<P> -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. -<P> -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. -<P> -<H3><a name="3.36">3.36</a>) What debugging features are available in -PostgreSQL?</H3> -<P> -PostgreSQL has several features that report status information that can -be valuable for debugging purposes. -<P> -First, by compiling with DEBUG defined, many assert()'s monitor the -progress of the backend and halt the program when something unexpected -occurs. -<P> -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: -<PRE> -<KBD> - cd /usr/local/pgsql - ./bin/postmaster >server.log 2>&1 & -</KBD> -</PRE> -<P> -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. -<P> -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. -<P> -The postgres program has a -s, -A, -t options that can be very usefull -for debugging and performance measurements. -<P> -The EXPLAIN command (see this FAQ) allows you to see how PostgreSQL is -iterpreting your query. -<P> -<H3><a name="3.37">3.37</a>) What is an oid? What is a tid?</H3> -<P> -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. -<P> -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. -<P> -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. -<P> -<H3><a name="3.38">3.38</a>) What is the meaning of some of the terms -used in Postgres?</H3> -<P> -Some of the source code and older documentation use terms that have more -common usage. Here are some: -<UL> -<LI> row, record, tuple -<LI> attribute, field, column -<LI> table, class -<LI> retrieve, select -<LI> replace, update -<LI> append, insert -<LI> oid, serial value -<LI> portal, cursor -<LI> range variable, table name, table alias -</UL> -<P> -Please let me know if you think of any more. -<P> -<H3><a name="3.39">3.39</a>) What is Genetic Query Optimization?</H3> -<P> -The GEQO module in PostgreSQL is intended to solve the query -optimization problem of joining many tables by means of a Genetic -Algorithm (GA). It allows the handling of large join queries through -non-exhaustive search. -<P> -For further information see README.GEQO <utesch@aut.tu-freiberg.de>. -<P> -<H3><a name="3.40">3.40</a>) I am running Solaris and my dates -display wrong. Why?</H3> -<P> -There was a bug in 6.0 that caused this problem under Solaris with -O2 -optimization. -Upgrade to 6.1. -<P> -<H3><a name="3.41">3.41</a>) How do I enable more than 32 concurrent -backends?</H3> -<P> -Edit include/storage/sinvaladt.h, and change the value of MaxBackendId. -In the future, we plan to make this a configurable prameter. -<P> -<HR> -<H2> Section 4: Extending PostgreSQL -</H2> -<P> -<H3><a name="4.1">4.1</a>) I wrote a user-defined function and when I run it -in psql, it dumps core.</H3> -<P> -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 -<P> -<H3><a name="4.2">4.2</a>) I get messages of the type - NOTICE:PortalHeapMemoryFree: 0x402251d0 not in alloc set!</H3> -<P> -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. -<P> -<H3><a name="4.3">4.3</a>) I've written some nifty new types and functions for -PostgreSQL.</H3> -<P> -Please share them with other PostgreSQL users. Send your extensions to -mailing list, and they will eventually end up in the contrib/ -subdirectory. -<P> -<H3><a name="4.4">4.4</a>) How do I write a C function to return a tuple?</H3> -<P> -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. -<P> -<HR> -<H2> Section 5: Bugs -</H2> -<P> -<H3><a name="5.1">5.1</a>) How do I make a bug report?</H3> -<P> -Check the current FAQ at <a -href="http://postgreSQL.org">http://postgreSQL.org</a> -<P> -Also check out our ftp site <a -href="ftp://ftp.postgreSQL.org/pub">ftp://ftp.postgreSQL.org/pub</a> to -see if there is a more recent PostgreSQL version. -<P> -You can also fill out the "bug-template" file and send it to: -<UL> -<LI> <a href="mailto:bugs@postgreSQL.org">bugs@postgreSQL.org</a> -</UL> -<P> -This is the address of the developers mailing list. -</BODY> -</HTML> + 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: + + 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 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); + insert into my_oids values (1); + create function new_oid () returns int4 as + 'update my_oids set f1 = f1 + 1; select f1 from my_oids; ' + language 'sql'; + + 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. + + Sequences were implemented in 6.1 + + 3.24) How do I create a multi-column index? + + In 6.0, 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. + + In 6.1, this feature is available. + + 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: + + create index idx1 on person using btree (name); + + 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 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.31) Why do statements require an extra character at the end? Why does + 'createuser' return 'unexpected last match in input()'? Why does pg_dump + fail? + + 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.32) 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.33) 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.34) 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. + + + 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. + + 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.35) 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.36) 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 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.37) 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.38) 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. + + 3.39) What is Genetic Query Optimization? + + The GEQO module in PostgreSQL is intended to solve the query + optimization problem of joining many tables by means of a Genetic + Algorithm (GA). It allows the handling of large join queries through + non-exhaustive search. + + For further information see README.GEQO <utesch@aut.tu-freiberg.de>. + + 3.40) I am running Solaris and my dates display wrong. Why? + + There was a bug in 6.0 that caused this problem under Solaris with -O2 + optimization. Upgrade to 6.1.1. + + 3.41) How do I enable more than 32 concurrent backends? + + Edit include/storage/sinvaladt.h, and change the value of + MaxBackendId. In the future, we plan to make this a configurable + prameter. + + _________________________________________________________________ + +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: + * bugs@postgreSQL.org + + This is the address of the developers mailing list. |