diff options
| author | Bruce Momjian <bruce@momjian.us> | 1998-02-28 15:08:15 +0000 |
|---|---|---|
| committer | Bruce Momjian <bruce@momjian.us> | 1998-02-28 15:08:15 +0000 |
| commit | 8afae11406fb322b609d8180bcdee397b279d57b (patch) | |
| tree | 1c05ddf455ec1d569dd6e3d6b042e9e520adc96d /doc/FAQ | |
| parent | 4af1e537d699e425ba6ae73c0486eb480f75d185 (diff) | |
| download | postgresql-8afae11406fb322b609d8180bcdee397b279d57b.tar.gz postgresql-8afae11406fb322b609d8180bcdee397b279d57b.zip | |
Update for 6.3 release.
Diffstat (limited to 'doc/FAQ')
| -rw-r--r-- | doc/FAQ | 1712 |
1 files changed, 779 insertions, 933 deletions
@@ -1,717 +1,597 @@ -<HTML> -<HEAD> -<TITLE>PostgreSQL FAQ</title> -</HEAD> -<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#FF0000" VLINK="#A00000" ALINK="#0 -000FF"> -<H1> -Frequently Asked Questions (FAQ) for PostgreSQL -</H1> -<P> -Last updated: Wed Feb 25 14:37:07 EST 1998 -<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.shtml">http://postgreSQL.org/docs -/faq-linux.shtml</a>. -<P> -Irix-specific questions are answered in -<a href="http://postgreSQL.org/docs/faq-irix.shtml">http://postgreSQL.org/docs/ -faq-irix.shtml</a>. -<P> -<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.8">1.8</a>) What documentation is available for 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>) Are there ODBC drivers for -PostgreSQL?<BR> -<a href="#1.12">1.12</a>) What tools are available for hooking -postgres to Web pages?<BR> -<a href="#1.13">1.13</a>) Does PostgreSQL have a graphical user interface -? -A report generator? A embedded query language interface?<BR> -<a href="#1.14">1.14</a>) What is a good book to learn SQL?<BR> - -<H3> 2) Installation/Configuration 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> -<a href="#2.8">2.8</a>) How do I prevent other hosts from accessing my -PostgreSQL<BR> -<a href="#2.9">2.9</a>) I can't access the database as the -'root' user.<BR> -<a href="#2.10">2.10</a>) All my servers crash under concurrent -table access. Why?<BR> -<a href="#2.11">2.11</a>) How do I tune the database engine for -better performance?<BR> -<a href="#2.12">2.12</a>) What debugging features are available in -PostgreSQL?<BR> -<a href="#2.13">2.13</a>) How do I enable more than 32 concurrent -backends? -<H3> 3) Operational questions</H3> -<a href="#3.1">3.1</a>) Does PostgreSQL support nested subqueries?<BR> -<a href="#3.2">3.2</a>) I've having a lot of problems using rules.<BR> -<a href="#3.3">3.3</a>) I can't seem to write into the middle of large -objects reliably.<BR> -<a href="#3.4">3.4</a>) How can I write client applications to -PostgreSQL?<BR> -<a href="#3.5">3.5</a>) How do I set up a pg_group?<BR> -<a href="#3.6">3.6</a>) What is the exact difference between -binary cursors and normal cursors?<BR> -<a href="#3.7">3.7</a>) What is a R-tree index and what is it -used for?<BR> -<a href="#3.8">3.8</a>) What is the maximum size for a -tuple?<BR> -<a href="#3.9">3.9</a>) I defined indices but my queries don't -seem to make use of them. Why?<BR> -<a href="#3.10">3.10</a>) How do I do regular expression searches? -case-insensitive regexp searching?<BR> -<a href="#3.11">3.11</a>) I experienced a server crash during a -vacuum. How do I remove the lock file?<BR> -<a href="#3.12">3.12</a>) What is the difference between the -various character types?<BR> -<a href="#3.13">3.13</a>) In a query, how do I detect if a field -is NULL?<BR> -<a href="#3.14">3.14</a>) How do I see how the query optimizer is -evaluating my query?<BR> -<a href="#3.15">3.15</a>) How do I create a serial field?<BR> -<a href="#3.16">3.16</a>) What are the pg_psort.XXX files in my -database directory?<BR> -<a href="#3.17">3.17</a>) Why can't I connect to my database from -another machine?<BR> -<a href="#3.18">3.18</a>) How do I find out what indexes or -operations are defined in the database?<BR> -<a href="#3.19">3.19</a>) What is the time-warp feature and how -does it relate to vacuum?<BR> -<a href="#3.20">3.20</a>) What is an oid? What is a tid?<BR> -<a href="#3.21">3.21</a>) What is the meaning of some of the terms -used in Postgres?<BR> -<a href="#3.22">3.22</a>) What is Genetic Query Optimization?<BR> -<a href="#3.23">3.23</a>) How do you remove a column from a table?<BR> -<a href="#3.24">3.24</a>) How do SELECT only the first few rows of -a query?<BR> -<a href="#3.25">3.25</a>) Why can't I create a column named "time"?<BR> -<a href="#3.26">3.26</a>) How much database disk space is required -to store data from a typical flat file?<BR> -<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? -<P> - -<HR> -<H2> Section 1: General Questions</H2> <H3><a -name="1.1">1.1</a>) What is PostgreSQL?</H3> -<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 or 4.x -<LI> alpha - DEC Alpha AXP on Digital Unix 2.0, 3.2, 4.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.4R4.11 -<LI> hpux - HP PA-RISC on HP-UX 9.0, 10 -<LI> i386_solaris - i386 Solaris -<LI> irix5 - SGI MIPS on IRIX 5.3 -<LI> linux - Intel x86 on Linux 2.0 and Linux ELF - SPARC on Linux ELF - PPC on Linux Elf - (For non-ELF Linux, see LINUX_ELF below). -<LI> sco - SCO 3.2v5 -<LI> sparc_solaris - SUN SPARC on Solaris 2.4, 2.5, 2.5.1 -<LI> sunos4 - SUN SPARC on SunOS 4.1.3 -<LI> svr4 - Intel x86 on Intel SVR4 and MIPS -<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/p -ostgres95</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.studen -t.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> + + Frequently Asked Questions (FAQ) for PostgreSQL + + Last updated: Sat Feb 28 10:04:28 EST 1998 + + 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.shtml. + + Irix-specific questions are answered in + http://postgreSQL.org/docs/faq-irix.shtml. + _________________________________________________________________ + +Questions answered: + + 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.8) What documentation is available for PostgreSQL? + 1.9) What version of SQL does PostgreSQL use? + 1.10) Does PostgreSQL work with databases from earlier versions of + postgres? + 1.11) Are there ODBC drivers for PostgreSQL? + 1.12) What tools are available for hooking postgres to Web pages? + 1.13) Does PostgreSQL have a graphical user interface? A report + generator? A embedded query language interface? + 1.14) What is a good book to learn SQL? + + 2) Installation/Configuration 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? + 2.8) How do I prevent other hosts from accessing my PostgreSQL + 2.9) I can't access the database as the 'root' user. + 2.10) All my servers crash under concurrent table access. Why? + 2.11) How do I tune the database engine for better performance? + 2.12) What debugging features are available in PostgreSQL? + 2.13) How do I enable more than 32 concurrent backends? + + 3) Operational questions + + 3.1) Does PostgreSQL support nested subqueries? + 3.2) I've having a lot of problems using rules. + 3.3) I can't seem to write into the middle of large objects reliably. + 3.4) How can I write client applications to PostgreSQL? + 3.5) How do I set up a pg_group? + 3.6) What is the exact difference between binary cursors and normal + cursors? + 3.7) What is a R-tree index and what is it used for? + 3.8) What is the maximum size for a tuple? + 3.9) I defined indices but my queries don't seem to make use of them. + Why? + 3.10) How do I do regular expression searches? case-insensitive regexp + searching? + 3.11) I experienced a server crash during a vacuum. How do I remove + the lock file? + 3.12) What is the difference between the various character types? + 3.13) In a query, how do I detect if a field is NULL? + 3.14) How do I see how the query optimizer is evaluating my query? + 3.15) How do I create a serial field? + 3.16) What are the pg_psort.XXX files in my database directory? + 3.17) Why can't I connect to my database from another machine? + 3.18) How do I find out what indexes or operations are defined in the + database? + 3.19) What is the time-warp feature and how does it relate to vacuum? + 3.20) What is an oid? What is a tid? + 3.21) What is the meaning of some of the terms used in Postgres? + 3.22) What is Genetic Query Optimization? + 3.23) How do you remove a column from a table? + 3.24) How do SELECT only the first few rows of a query? + 3.25) Why can't I create a column named "time"? + 3.26) How much database disk space is required to store data from a + typical flat file? + + 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 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 Digital Unix 2.0, 3.2, 4.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.4R4.11 + * hpux - HP PA-RISC on HP-UX 9.0, 10 + * i386_solaris - i386 Solaris + * irix5 - SGI MIPS on IRIX 5.3 + * linux - Intel x86 on Linux 2.0 and Linux ELF SPARC on Linux ELF + PPC on Linux Elf (For non-ELF Linux, see LINUX_ELF below). + * sco - SCO 3.2v5 + * sparc_solaris - SUN SPARC on Solaris 2.4, 2.5, 2.5.1 + * sunos4 - SUN SPARC on SunOS 4.1.3 + * svr4 - Intel x86 on Intel SVR4 and MIPS + * 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 -</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> + + 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 -</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> + + 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 -</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.2.1, which was released on -October 17th, 1997. We are testing 6.3 beta. For information about -what is new in 6.3, see our TODO list on our WWW page. -<P> -We plan to have major releases every three months. -<P> -<H3><a name="1.7">1.7</a>) Is there a commercial version of PostgreSQL?</H -3> -<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 HAVING clause under a GROUP BY -</UL> -<P> -On the other hand, you get to create user-defined types, functions, -inheritance etc. -<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. -<P> -Upgrading to 6.3 from earlier releases requires a dump and restore. -<P> -Upgrading to 6.2.1 from pre-6.2 requires a dump and restore. -<P> -Upgrading to 6.2.1 from 6.2 does not require a dump, but see the -appropriate /migration file in the distribution. -<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.2.1 or 6.3 beta. -<P> -<H3><a name="1.11">1.11</a>) Are there ODBC drivers for PostgreSQL?</H3> -<P> -There are two ODBC drivers available, PostODBC and OpenLink ODBC. -<P> -PostODBC is included in the distribution. -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@lists -e -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 very popular. You can get it from <a -href="http://www.openlinksw.com/"> -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="1.12">1.12</a>) What tools are available for hooking -postgres to Web pages?</H3> -<P> -A nice introduction to Database-backed Web pages can be seen at: -<a href="http://www.webtools.com">http://www.webtools.com</a> -<P> -For web integration, PHP is an excellent interface. The URL for that -is <a href="http://www.php.net">http://www.php.net</a> -<P> -PHP is great for simple stuff, but for more complex stuff, some still -use the perl interface and CGI.pm. -<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/wd -b --p95</a> -</UL> -<H3><a name="1.13">1.13</a>) Does PostgreSQL have a graphical user interface -? -A report generator? A embedded query language interface?</H3> -<P> -We have a nice graphical user interface called pgaccess, which is -shipped as part of the distribtion. Pgaccess also has a report -generator. -<P> -We also have ecpg, which is an embedded SQL query language interface for -C. This is also included. -<P> -<H3><a name="1.14">1.14</a>) What is a good book to learn SQL?</H3> -<P> -Many of our users like <I>The Practical SQL Handbook</I>, -Bowman et al, Addison Wesley. -<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 tha -n -/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> -<H3><a name="2.8">2.8</a>) How do I prevent other hosts from accessing my -PostgreSQL backend?</H3> -<P> -By default, PostgreSQL only allows connections from the local machine -using unix domain. You must add the -i flag to the postmaster, and -enable host-based authentication by modifying the file $PGDATA/pg_hba -accordingly. -<P> -<H3><a name="2.9">2.9</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="2.10">2.10</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="2.11">2.11</a>) How do I tune the database engine for -better performance?</H3> -<P> -There are two things that can be done. You can 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> -You can also use the postgres -S option to increase the maximum -amount of memory used by each backend process for temporary sorts. -Each buffer is 1K and the defualt is 512 buffers. -<P> -<H3><a name="2.12">2.12</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 running configure with the -enable-cassert option, -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> + + 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.3, which was released on + March 1st, 1998. + + We plan to have major releases every three months. + + 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 HAVING clause under a GROUP BY + + On the other hand, you get to create user-defined types, functions, + inheritance etc. + + 1.10) Does PostgreSQL work with databases from earlier versions of postgres? + + PostgreSQL v1.09 is compatible with databases created with v1.01. + + Upgrading to 6.3 from earlier releases requires a dump and restore. + + Upgrading to 6.2.1 from pre-6.2 requires a dump and restore. + + Upgrading to 6.2.1 from 6.2 does not require a dump, but see the + appropriate /migration file in the distribution. + + 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.2.1 or 6.3 beta. + + 1.11) Are there ODBC drivers for PostgreSQL? + + There are two ODBC drivers available, PostODBC and OpenLink ODBC. + + PostODBC is included in the distribution. 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 very popular. 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. + + 1.12) What tools are available for hooking postgres to Web pages? + + A nice introduction to Database-backed Web pages can be seen at: + http://www.webtools.com + + For web integration, PHP is an excellent interface. The URL for that + is http://www.php.net + + PHP is great for simple stuff, but for more complex stuff, some still + use the perl interface and CGI.pm. + + An WWW gatway based on WDB using perl can be downloaded from: + * http://www.eol.ists.ca/~dunlop/wdb -p95 + + 1.13) Does PostgreSQL have a graphical user interface? A report generator? A + embedded query language interface? + + We have a nice graphical user interface called pgaccess, which is + shipped as part of the distribtion. Pgaccess also has a report + generator. + + We also have ecpg, which is an embedded SQL query language interface + for C. This is also included. + + 1.14) What is a good book to learn SQL? + + Many of our users like The Practical SQL Handbook, Bowman et al, + Addison Wesley. + _________________________________________________________________ + +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'. + + 2.8) How do I prevent other hosts from accessing my PostgreSQL backend? + + By default, PostgreSQL only allows connections from the local machine + using unix domain. You must add the -i flag to the postmaster, and + enable host-based authentication by modifying the file $PGDATA/pg_hba + accordingly. + + 2.9) 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. + + 2.10) All my servers crash under concurrent table access. Why? + + This problem can be caused by a kernel that is not configured to + support semaphores. + + 2.11) How do I tune the database engine for better performance? + + There are two things that can be done. You can 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. + + You can also use the postgres -S option to increase the maximum amount + of memory used by each backend process for temporary sorts. Each + buffer is 1K and the defualt is 512 buffers. + + 2.12) What debugging features are available in PostgreSQL? + + PostgreSQL has several features that report status information that + can be valuable for debugging purposes. + + First, by running configure with the -enable-cassert option, 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 & -</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. Be warned that a debug level -of 3 generates large log files. -<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 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> -You can also compile with profiling to see what functions are taking -execution time. The backend profile files will be deposited in the -pgsql/data/base/dbname directory. The client profile file will be put -in the current directory. -<P> -The EXPLAIN command (see this FAQ) allows you to see how PostgreSQL is -interpreting your query. -<P> -<H3><a name="2.13">2.13</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 3: PostgreSQL Features -</H2> -<P> -<H3><a name="3.1">3.1</a>) Does PostgreSQL support nested subqueries?</H3> -<P> -Yes. -<P> -<H3><a name="3.2">3.2</a>) I've having a lot of problems using rules.</H3> -<P> -Currently, the rule system in PostgreSQL has some limitations. It works -enough to support the view mechanism, but does not handle -Insert/Update/Delete well. -<P> -<H3><a name="3.3">3.3</a>) I can't seem to write into the middle of large -objects reliably.</H3> -<P> -The Inversion large object system now works perfectly. You should no -longer have problems with large objects. -<P> -<H3><a name="3.4">3.4</a>) How can I write client applications to PostgreS -QL?</H3> -<P> -PostgreSQL supports a C-callable library interface called libpq as well -as many others. See the /src/interfaces directory. -<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.5">3.5</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}'); + ./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. Be warned that a debug level of 3 + 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 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. + + You can also compile with profiling to see what functions are taking + execution time. The backend profile files will be deposited in the + pgsql/data/base/dbname directory. The client profile file will be put + in the current directory. + + The EXPLAIN command (see this FAQ) allows you to see how PostgreSQL is + interpreting your query. + + 2.13) 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 3: PostgreSQL Features + + 3.1) Does PostgreSQL support nested subqueries? + + Yes. + + 3.2) I've having a lot of problems using rules. + + Currently, the rule system in PostgreSQL has some limitations. It + works enough to support the view mechanism, but does not handle + Insert/Update/Delete well. + + 3.3) I can't seem to write into the middle of large objects reliably. + + The Inversion large object system now works perfectly. You should no + longer have problems with large objects. + + 3.4) How can I write client applications to PostgreSQL? + + PostgreSQL supports a C-callable library interface called libpq as + well as many others. See the /src/interfaces directory. + + Others have contributed a perl interface and a WWW gateway to + PostgreSQL. See the PostgreSQL home pages for more details. + + 3.5) 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; + 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.6">3.6</a>) What is the exact difference between binary -cursors and normal cursors?</H3> -<P> -See the <I>declare</I> manual page for a description. -<P> -<H3><a name="3.7">3.7</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> -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.8">3.8</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.9">3.9</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). For column-specific optimization -statistics, use 'vacuum analyze'. -<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.10">3.10</a>) How do I do regular expression searches? -case-insensitive regexp searching?</H3> -<P> -See the <I>pgbuiltin</I> manual page. Search for <I>regular expression.</I> -<P> -<H3><a name="3.11">3.11</a>) I experienced a server crash during a -vacuum. How do I remove the lock file?</H3> -<P> -See the <I>vacuum</I> manual page. -<P> -<H3><a name="3.12">3.12</a>) What is the difference between the various -character types?</H3> -<PRE> + 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.6) What is the exact difference between binary cursors and normal cursors? + + See the declare manual page for a description. + + 3.7) 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" + + 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.8) 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.9) 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). For column-specific optimization + statistics, use 'vacuum analyze'. + + 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.10) How do I do regular expression searches? case-insensitive regexp + searching? + + See the pgbuiltin manual page. Search for regular expression. + + 3.11) I experienced a server crash during a vacuum. How do I remove the lock + file? + + See the vacuum manual page. + + 3.12) What is the difference between the various character types? + Type Internal Name Notes -------------------------------------------------- CHAR char 1 character } @@ -723,185 +603,166 @@ 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> -You need to use the internal name when doing internal operations. -<P> -The last four types above are "varlena" types (i.e. the first four bytes -are the length, followed by the data). CHAR(#) allocates the maximum -number of bytes no matter how much data is stored in the field. -TEXT, VARCHAR(#), and BYTEA all have variable length on the disk, and -because of this, there is a small performance penalty for using them. -Specifically, the penalty is for access to any columns after the first -column of this type. -<P> -<H3><a name="3.13">3.13</a>) In a query, how do I detect if a field is NULL? -</H3> -<P> -You test the column with IS NULL and IS NOT NULL. -<P> -<H3><a name="3.14">3.14</a>) How do I see how the query optimizer is -evaluating my query?</H3> -<P> -See the <I>explain</I> manual page. -<P> -<H3><a name="3.15">3.15</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 use -pgdump's -o option or COPY's WITH OIDS option to preserver the oids. -<P> -We also have a SEQUENCE function that is similar to SERIAL. See the -create_sequence manual page. -<P> -Another valid way of doing this is to create a function: -<PRE> -<CODE> + + You need to use the internal name when doing internal operations. + + The last four types above are "varlena" types (i.e. the first four + bytes are the length, followed by the data). CHAR(#) allocates the + maximum number of bytes no matter how much data is stored in the + field. TEXT, VARCHAR(#), and BYTEA all have variable length on the + disk, and because of this, there is a small performance penalty for + using them. Specifically, the penalty is for access to any columns + after the first column of this type. + + 3.13) In a query, how do I detect if a field is NULL? + + You test the column with IS NULL and IS NOT NULL. + + 3.14) How do I see how the query optimizer is evaluating my query? + + See the explain manual page. + + 3.15) 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 use + pgdump's -o option or COPY's WITH OIDS option to preserver the oids. + + We also have a SEQUENCE function that is similar to SERIAL. See the + create_sequence manual page. + + 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'; -</CODE> -</PRE> -<P> -then: -<PRE> -<CODE> + + then: + 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> -Yet another way is to use general trigger function autoinc() -from contrib/spi/autoinc.c. -<P> -<H3><a name="3.16">3.16</a>) What are the pg_psort.XXX files in my database -directory?</H3> -<P> -They are temporary sort 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 pg_psort.XXX files. -<P> -<H3><a name="3.17">3.17</a>) Why can't I connect to my database from -another machine?</H3> -<P> -The default configuration allows only unix domain socket connections -from the local machine. To enable TCP/IP connections, use the -postmaster -i option You need to add a host entry to the file -pgsql/data/pg_hba. See the <I>hba_conf</I> manual page. -<P> -<H3><a name="3.18">3.18</a>) How do I find out what indexes or -operations are defined in the database?</H3> -<P> -psql has a variety of backslash commands to show such information. Use -\? to see them. -<P> -Also try 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.19">3.19</a>) What is the time-warp feature and how does -it relate to vacuum?</H3> -<P> -PostgreSQL no longer supports this feature. All support code has been -removed. This was done to improve performance and reduce disk storage -overhead. -<P> -<H3><a name="3.20">3.20</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. You can create an index on the oid field for faster access. -<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.21">3.21</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.22">3.22</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.23">3.23</a>) How do you remove a column from a table?</H3> -We do not support ALTER TABLE DROP COLUMN, but do this: -<PRE> -<CODE> + + 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. + + Yet another way is to use general trigger function autoinc() from + contrib/spi/autoinc.c. + + 3.16) What are the pg_psort.XXX files in my database directory? + + They are temporary sort 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 pg_psort.XXX files. + + 3.17) Why can't I connect to my database from another machine? + + The default configuration allows only unix domain socket connections + from the local machine. To enable TCP/IP connections, use the + postmaster -i option You need to add a host entry to the file + pgsql/data/pg_hba. See the hba_conf manual page. + + 3.18) How do I find out what indexes or operations are defined in the + database? + + psql has a variety of backslash commands to show such information. Use + \? to see them. + + Also try 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.19) What is the time-warp feature and how does it relate to vacuum? + + PostgreSQL no longer supports this feature. All support code has been + removed. This was done to improve performance and reduce disk storage + overhead. + + 3.20) 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. You can create an index on the oid field for faster access. + + 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.21) 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.22) 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.23) How do you remove a column from a table? + + We do not support ALTER TABLE DROP COLUMN, but do this: + SELECT ... -- select all columns but the one you want to remove INTO TABLE new_table FROM old_table; DROP TABLE old_table; ALTER TABLE new_table RENAME TO old_table; -</CODE> -</PRE> -<P> -<H3><a name="3.24">3.24</a>) How do SELECT only the first few rows of -a query?</H3> -<P> -See the <I>fetch</I> manual page. -<P> -This only prevents all row results from being transfered to the client. -The entire query must be evaluated, even if you only want just first few -rows. -Consider a query that has and ORDER BY. There is no way to return any -rows until the entire query is evaluated and sorted. -<P> -<H3><a name="3.25">3.25</a>) Why can't I create a column named -"time"?<BR></H3> -<P> -6.2.1 has added some new restricted keywords as we make PostgreSQL more -ANSI-92 compilant. The next release will have this restriction removed. -There is a patch on ftp.postgresql.org that will allow this feature now. -<P> -<H3><a name="3.26">3.26</a>)How much database disk space is required -to store data from a typical flat file?<BR></H3> -<P> -Consider a file with 300,000 lines with two integers on each line. -The flat file is 2.4MB. The size of the PostgreSQL database file -containing this data can be estimated: -<PRE> + + 3.24) How do SELECT only the first few rows of a query? + + See the fetch manual page. + + This only prevents all row results from being transfered to the + client. The entire query must be evaluated, even if you only want just + first few rows. Consider a query that has and ORDER BY. There is no + way to return any rows until the entire query is evaluated and sorted. + + 3.25) Why can't I create a column named "time"? + + 6.2.1 has added some new restricted keywords as we make PostgreSQL + more ANSI-92 compilant. The next release will have this restriction + removed. There is a patch on ftp.postgresql.org that will allow this + feature now. + + 3.26)How much database disk space is required to store data from a typical + flat file? + + Consider a file with 300,000 lines with two integers on each line. The + flat file is 2.4MB. The size of the PostgreSQL database file + containing this data can be estimated: + 40 bytes + each row header (approximate) 8 bytes + two int fields @ 4 bytes each 4 bytes + pointer on page to tuple @@ -919,67 +780,52 @@ The data page size in PostgreSQL is 8192(8k) bytes, so: 157 rows per page 1911 database pages * 8192 bytes per page = 15,654,912 or 15.5MB -<P> + Indexes do not contain as much overhead, but do contain the data that is being indexed, so they can be large also. -</PRE> -<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> + _________________________________________________________________ + +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. |