diff options
| author | Bruce Momjian | 2005-02-01 02:42:03 +0000 |
|---|---|---|
| committer | Bruce Momjian | 2005-02-01 02:42:03 +0000 |
| commit | 925320fe3d31fd974a51c3257d4e6845b3d73066 (patch) | |
| tree | 49ad644f02246a9c4b7ba1e360cdd63b78f8aa5b /doc/src | |
| parent | b46fa4ba81d536720c278ac9bdd65a986e065d1c (diff) | |
Backpatch FAQ changes to 8.0.X.
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/FAQ/FAQ.html | 623 |
1 files changed, 230 insertions, 393 deletions
diff --git a/doc/src/FAQ/FAQ.html b/doc/src/FAQ/FAQ.html index 65fbc0446c8..b0ccf2853f3 100644 --- a/doc/src/FAQ/FAQ.html +++ b/doc/src/FAQ/FAQ.html @@ -10,7 +10,7 @@ alink="#0000ff"> <H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1> - <P>Last updated: Sat Jan 29 23:44:48 EST 2005</P> + <P>Last updated: Mon Jan 31 21:40:28 EST 2005</P> <P>Current maintainer: Bruce Momjian (<A href= "mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>) @@ -27,21 +27,19 @@ <H2 align="center">General Questions</H2> <A href="#1.1">1.1</A>) What is PostgreSQL? How is it pronounced?<BR> - <A href="#1.2">1.2</A>) What is the copyright on PostgreSQL?<BR> + <A href="#1.2">1.2</A>) What is the copyright of PostgreSQL?<BR> <A href="#1.3">1.3</A>) What platforms does PostgreSQL support?<BR> <A href="#1.4">1.4</A>) Where can I get PostgreSQL?<BR> <A href="#1.5">1.5</A>) Where can I get support?<BR> - <A href="#1.6">1.6</A>) What is the latest release?<BR> - <A href="#1.7">1.7</A>) What documentation is available?<BR> - <A href="#1.8">1.8</A>) How do I find out about known bugs or + <A href="#1.6">1.6</A>) How do I submit a bug report?<BR> + <A href="#1.7">1.7</A>) What is the latest release?<BR> + <A href="#1.8">1.8</A>) What documentation is available?<BR> + <A href="#1.9">1.9</A>) How do I find out about known bugs or missing features?<BR> - <A href="#1.9">1.9</A>) How can I learn <SMALL>SQL</SMALL>?<BR> - <A href="#1.10">1.10</A>) How do I join the development team?<BR> - <A href="#1.11">1.11</A>) How do I submit a bug report?<BR> + <A href="#1.10">1.10</A>) How can I learn <SMALL>SQL</SMALL>?<BR> + <A href="#1.11">1.11</A>) How do I join the development team?<BR> <A href="#1.12">1.12</A>) How does PostgreSQL compare to other <SMALL>DBMS</SMALL>s?<BR> - <A href="#1.13">1.13</A>) How can I financially assist - PostgreSQL?<BR> <H2 align="center">User Client Questions</H2> @@ -76,8 +74,7 @@ <A href="#4.2">4.2</A>) How do I find out what tables, indexes, databases, and users are defined? How do I see the queries used by <I>psql</I> to display them?<BR> - <A href="#4.3">4.3</A>) How do you remove a column from a - table, or change its data type?<BR> + <A href="#4.3">4.3</A>) How do you change a column's data type?<BR> <A href="#4.4">4.4</A>) What is the maximum size for a row, a table, and a database?<BR> <A href="#4.5">4.5</A>) How much database disk space is required @@ -104,24 +101,22 @@ my sequence/SERIAL column?<BR> <A href="#4.12">4.12</A>) What is an <SMALL>OID</SMALL>? What is a <SMALL>TID</SMALL>?<BR> - <A href="#4.13">4.13</A>) What is the meaning of some of the terms - used in PostgreSQL?<BR> - <A href="#4.14">4.14</A>) Why do I get the error <I>"ERROR: Memory + <A href="#4.12">4.13</A>) Why do I get the error <I>"ERROR: Memory exhausted in AllocSetAlloc()"</I>?<BR> - <A href="#4.15">4.15</A>) How do I tell what PostgreSQL version I + <A href="#4.14">4.14</A>) How do I tell what PostgreSQL version I am running?<BR> - <A href="#4.16">4.16</A>) Why does my large-object operations get + <A href="#4.15">4.15</A>) Why does my large-object operations get <I>"invalid large obj descriptor"</I>?<BR> - <A href="#4.17">4.17</A>) How do I create a column that will + <A href="#4.16">4.16</A>) How do I create a column that will default to the current time?<BR> - <A href="#4.18">4.18</A>) How do I perform an outer join?<BR> - <A href="#4.19">4.19</A>) How do I perform queries using multiple + <A href="#4.17">4.17</A>) How do I perform an outer join?<BR> + <A href="#4.18">4.18</A>) How do I perform queries using multiple databases?<BR> - <A href="#4.20">4.20</A>) How do I return multiple rows or columns + <A href="#4.19">4.19</A>) How do I return multiple rows or columns from a function?<BR> - <A href="#4.21">4.21</A>) Why can't I reliably create/drop + <A href="#4.20">4.20</A>) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?<BR> - <A href="#4.22">4.22</A>) What encryption options are available?<BR> + <A href="#4.21">4.21</A>) What encryption options are available?<BR> <H2 align="center">Extending PostgreSQL</H2> @@ -140,45 +135,31 @@ <H4><A name="1.1">1.1</A>) What is PostgreSQL? How is it pronounced?</H4> - <P>PostgreSQL is pronounced <I>Post-Gres-Q-L</I>.</P> - - <P>PostgreSQL is an enhancement of the POSTGRES database management - system (and is still sometimes reffered to as simply "Postgres"), - a next-generation <SMALL>DBMS</SMALL> 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 <SMALL>SQL</SMALL>. PostgreSQL is free and the - complete source is available.</P> - - <P>PostgreSQL development is performed by a team of - 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 - section <a href="#1.6">1.6</a> on how to join). This team is now - responsible for all development of PostgreSQL. It is a community - project and is not controlled by any company. To get involved, see - the developer's FAQ at <A href= + <P>PostgreSQL is pronounced <I>Post-Gres-Q-L</I>, also called just + <I>Postgres</I>.</P> + + <P>PostgreSQL is an object-relational database system that has the + features of traditional commercial database systems with + enhancements to be found in next-generation <SMALL>DBMS</SMALL> + systems. PostgreSQL is free and the complete source code is + available.</P> + + <P>PostgreSQL development is performed by a team of mostly volunteer + developers spread throughout the world and communicating via the + Internet. It is a community project and is not controlled by any + company. To get involved, see the developer's FAQ at <A href= "http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html"> http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html</A> </P> - <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> - - <P>The original name of the software at Berkeley was Postgres. When - <SMALL>SQL</SMALL> functionality was added in 1995, its name was - changed to Postgres95. The name was changed at the end of 1996 to - PostgreSQL.</P> - - <H4><A name="1.2">1.2</A>) What is the copyright on + <H4><A name="1.2">1.2</A>) What is the copyright of PostgreSQL?</H4> - <P>PostgreSQL is subject to the following COPYRIGHT:</P> + <P>PostgreSQL is distributed under the classic BSD license. It has + no restrictions on how the source code can be used. We like it and + have no intention of changing it.</P> + + <P>This is the BSD license we use:</P> <P>PostgreSQL Data Base Management System</P> @@ -204,10 +185,6 @@ UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.</P> - <P>The above is the BSD license, the classic open-source license. - It has no restrictions on how the source code may be used. We like - it and have no intention of changing it.</P> - <H4><A name="1.3">1.3</A>) What platforms does PostgreSQL support?</H4> <P>In general, any modern Unix-compatible platform should be able to @@ -236,78 +213,46 @@ <H4><A name="1.5">1.5</A>) Where can I get support?</H4> - <P>The main mailing list is: <A href= - "mailto:pgsql-general@PostgreSQL.org">pgsql-general@PostgreSQL.org</A>. - It is available for discussion of matters pertaining to PostgreSQL. - To subscribe, send mail with the following lines in the body (not - the subject line):</P> -<PRE> - subscribe - end -</PRE> - - <P>to <A href= - "mailto:pgsql-general-request@PostgreSQL.org">pgsql-general-request@PostgreSQL.org</A>.</P> - - <P>There is also a digest list available. To subscribe to this - list, send email to: <A href= - "mailto:pgsql-general-digest-request@PostgreSQL.org">pgsql-general-digest-request@PostgreSQL.org</A> - with a body of:</P> -<PRE> - subscribe - end -</PRE> - - Digests are sent out to members of this list whenever the main list - has received around 30k of messages. - - <P>The bugs mailing list is available. To subscribe to this list, - send email to <A href= - "mailto:pgsql-bugs-request@PostgreSQL.org">pgsql-bugs-request@PostgreSQL.org</A> - with a body of:</P> -<PRE> - subscribe - end -</PRE> - - There is also a developers discussion mailing list available. To - subscribe to this list, send email to <A href= - "mailto:pgsql-hackers-request@PostgreSQL.org">pgsql-hackers-request@PostgreSQL.org</A> - with a body of: -<PRE> - subscribe - end -</PRE> - - <P>Additional mailing lists and information about PostgreSQL can be - found via the PostgreSQL WWW home page at:</P> - - <BLOCKQUOTE> - <A href="http://www.PostgreSQL.org">http://www.PostgreSQL.org</A> - </BLOCKQUOTE> + <P>The PostgreSQL community provides assistance to many of its users + via email. The main web site to subscribe to the email lists is + <a href="http://www.postgresql.org/community/lists/"> + http://www.postgresql.org/community/lists/</a>. The <I>general</I> + or <I>bugs</I> lists are a good place to start. <P>The major IRC channel is <I>#postgresql</I> on Freenode (<I>irc.freenode.net</I>). To connect you can use the Unix - command <CODE>irc -c '#postgresql' "$USER" irc.freenode.net</CODE> + program <CODE>irc -c '#postgresql' "$USER" irc.freenode.net</CODE> or use any of the other popular IRC clients. A Spanish one also exists on the same network, (<I>#postgresql-es</I>), and a French one, (<I>#postgresqlfr</I>). There is also a PostgreSQL channel on EFNet. <P>A list of commercial support companies is available at <A href= - "http://techdocs.postgresql.org/companies.php">http://techdocs.postgresql.org/companies.php</A>.</P> + "http://techdocs.postgresql.org/companies.php">http://techdocs.postg + resql.org/companies.php</A>.</P> - <H4><A name="1.6">1.6</A>) What is the latest release?</H4> + <H4><A name="1.6">1.6</A>) How do I submit a bug report?</H4> + + <P>Visit the PostgreSQL bug form at <A href= + "http://www.postgresql.org/support/submitbug"> + http://www.postgresql.org/support/submitbug</A>.</P> + + <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> + + <H4><A name="1.7">1.7</A>) What is the latest release?</H4> <P>The latest release of PostgreSQL is version 8.0.0.</P> - <P>We plan to have major releases every six to eight months.</P> + <P>We plan to have major releases every ten to twelve months.</P> - <H4><A name="1.7">1.7</A>) What documentation is available?</H4> + <H4><A name="1.8">1.8</A>) What documentation is available?</H4> - <P>Several manuals, manual pages, and some small test examples are - included in the distribution. See the <I>/doc</I> directory. You - can also browse the manuals online at <A href= - "http://www.PostgreSQL.org/docs">http://www.PostgreSQL.org/docs</A>.</P> + <P>PostgreSQL includes extensive documentation, including a large + manual, manual pages, and some test examples. See the <I>/doc</I> + directory. You can also browse the manuals online at <A href= + "http://www.PostgreSQL.org/docs">http://www.PostgreSQL.org/docs</A>. + </P> <P>There are two PostgreSQL books available online at <A href= "http://www.PostgreSQL.org/docs/awbook.html">http://www.PostgreSQL.org/docs/awbook.html</A> @@ -326,14 +271,14 @@ <P>Our web site contains even more documentation.</P> - <H4><A name="1.8">1.8</A>) How do I find out about known bugs or + <H4><A name="1.9">1.9</A>) How do I find out about known bugs or missing features?</H4> <P>PostgreSQL supports an extended subset of <SMALL>SQL</SMALL>-92. See our <A href="http://developer.PostgreSQL.org/todo.php">TODO</A> list for known bugs, missing features, and future plans.</P> - <H4><A name="1.9">1.9</A>) How can I learn + <H4><A name="1.10">1.10</A>) How can I learn <SMALL>SQL</SMALL>?</H4> <P>The PostgreSQL book at <A href= @@ -357,12 +302,12 @@ Bowman, Judith S., et al., Addison-Wesley. Others like <I>The Complete Reference SQL</I>, Groff et al., McGraw-Hill.</P> - <H4><A name="1.10">1.10</A>) How do I join the development + <H4><A name="1.11">1.11</A>) How do I join the development team?</H4> <P>First, download the latest source and read the PostgreSQL - Developers documentation on our web site, or in the distribution. - Second, subscribe to the <I>pgsql-hackers</I> and + Developers FAQ and documentation on our web site, or in the + distribution. Second, subscribe to the <I>pgsql-hackers</I> and <I>pgsql-patches</I> mailing lists. Third, submit high quality patches to pgsql-patches.</P> @@ -372,16 +317,6 @@ committers to keep up, and we had confidence that patches they committed were of high quality.</P> - <H4><A name="1.11">1.11</A>) How do I submit a bug report?</H4> - - <P>Visit the PostgreSQL bug form at <A href= - "http://www.postgresql.org/support/submitbug"> - http://www.postgresql.org/support/submitbug</A>.</P> - - <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 or patches.</P> - <H4><A name="1.12">1.12</A>) How does PostgreSQL compare to other <SMALL>DBMS</SMALL>s?</H4> @@ -448,31 +383,6 @@ </DD> </DL> - <H4><A name="1.13">1.13</A>) How can I financially assist - PostgreSQL?</H4> - - <P>PostgreSQL has had a first-class infrastructure since we started - in 1996. This is all thanks to Marc Fournier, who has created - and managed this infrastructure over the years.</P> - - <P>Quality infrastructure is very important to an open-source - project. It prevents disruptions that can greatly delay forward - movement of the project.</P> - - <P>Of course, this infrastructure is not cheap. There are a variety - of monthly and one-time expenses that are required to keep it - going. If you or your company has money it can donate to help fund - this effort, please go to <A href="http://store.pgsql.com/shopping/">http://store.pgsql.com/shopping/</A> - and make a donation.</P> - - <P>Although the web page mentions PostgreSQL, Inc, the - "contributions" item is solely to support the PostgreSQL project - and does not fund any specific company. If you prefer, you can also - send a check to the contact address.</P> - - <P>Also, if you have a success story about PostgreSQL, please email - it to our advocacy list at <a href="mailto:pgsql-advocacy@postgresql.org"> - pgsql-advocacy@postgresql.org</a>.</P> <HR> @@ -499,27 +409,33 @@ <P>A nice introduction to Database-backed Web pages can be seen at: <A href="http://www.webreview.com">http://www.webreview.com</A></P> - <P>For Web integration, PHP is an excellent interface. It is at <A - href="http://www.php.net">http://www.php.net</A>.</P> + <P>For Web integration, PHP (<A + href="http://www.php.net">http://www.php.net</A>) is an excellent + interface.</P> - <P>For complex cases, many use the Perl interface and CGI.pm or mod_perl.</P> + <P>For complex cases, many use the Perl and CGI.pm or mod_perl.</P> <H4><A name="2.3">2.3</A>) Does PostgreSQL have a graphical user interface?</H4> - <P>Yes, there are several graphical interfaces to PostgreSQL available. - These include PgAccess <a href="http://www.pgaccess.org"> - http://www.pgaccess.org</a>), pgAdmin III (<a - href="http://www.pgadmin.org">http://www.pgadmin.org</a>, RHDB Admin (<a - href="http://sources.redhat.com/rhdb/">http://sources.redhat.com/rhdb/ - </a>), TORA (<a href="http://www.globecom.net/tora/">http://www.globecom.net/tora/</a>, - partly commercial), and Rekall (<a href="http://www.rekallrevealed.org/"> - http://www.rekallrevealed.org/</a>). There is also PhpPgAdmin - (<a href="http://phppgadmin.sourceforge.net/"> - http://phppgadmin.sourceforge.net/ </a>), a web-based interface to + <P>Yes, there are several graphical interfaces to PostgreSQL + available. These include pgAdmin III (<a + href="http://www.pgadmin.org">http://www.pgadmin.org</a>, PgAccess + <a href="http://www.pgaccess.org"> http://www.pgaccess.org</a>), + RHDB Admin (<a + href="http://sources.redhat.com/rhdb/">http://sources.redhat.com/rhd + b/ </a>), TORA (<a + href="http://www.globecom.net/tora/">http://www.globecom.net/tora/</a>, + partly commercial), and Rekall (<a + href="http://www.rekallrevealed.org/"> + http://www.rekallrevealed.org/</a>). There is also PhpPgAdmin (<a + href="http://phppgadmin.sourceforge.net/"> + http://phppgadmin.sourceforge.net/ </a>), a web-based interface to PostgreSQL.</P> - <P>See <a href="http://techdocs.postgresql.org/guides/GUITools">http://techdocs.postgresql.org/guides/GUITools</a> for a more detailed list.</P> + <P>See <a href="http://techdocs.postgresql.org/guides/GUITools"> + http://techdocs.postgresql.org/guides/GUITools</a> for a more + detailed list.</P> <HR> @@ -538,75 +454,85 @@ <P>By default, PostgreSQL only allows connections from the local machine using Unix domain sockets or TCP/IP connections. Other machines will not be able to connect unless you modify - listen_addresses in the postgresql.conf <B>and</B> enable - host-based authentication by modifying the file - <I>$PGDATA/pg_hba.conf</I> accordingly.</P> + <I>listen_addresses</I> in the <I>postgresql.conf</I> file, enable + host-based authentication by modifying the + <I>$PGDATA/pg_hba.conf</I> file, and restart the server.</P> <H4><A name="3.3">3.3</A>) How do I tune the database engine for better performance?</H4> - <P>Certainly, indexes can speed up queries. The - <SMALL>EXPLAIN ANALYZE</SMALL> command allows you to see how - PostgreSQL is interpreting your query, and which indexes are - being used.</P> - - <P>If you are doing many <SMALL>INSERTs</SMALL>, consider doing - them in a large batch using the <SMALL>COPY</SMALL> command. This - is much faster than individual <SMALL>INSERTS</SMALL>. Second, - statements not in a <SMALL>BEGIN WORK/COMMIT</SMALL> transaction - block are considered to be in their own transaction. Consider - performing several statements in a single transaction block. This - reduces the transaction overhead. Also, consider dropping and - recreating indexes when making large data changes.</P> - - <P>There are several tuning options in the <a href= + <P>There are three major areas for potential performance + improvement:</P> + + <DL> + <DT><B>Query Changes</B></DT> + + <DD>This involves modifying queries to obtain better + performance: + <ul> + <li>Creation of indexes, including expression and partial + indexes</li> + <li>Use of COPY instead of multiple <SMALL>INSERT</SMALL>s</li> + <li>Grouping of multiple statements into a single transaction to + reduce commit overhead</li> + <li>Use of <SMALL>CLUSTER</SMALL> when retrieving many rows from an + index</li> + <li>Use of <SMALL>LIMIT</SMALL> for returning a subset of a query's + output</li> + <li>Use of Prepared queries</li> + <li>Use of <SMALL>ANALYZE</SMALL> to maintain accurate optimizer + statistics</li> + <li>Regular use of <SMALL>VACUUM</SMALL> or <I>pg_autovacuum</I> + <li>Dropping of indexes during large data changes</li> + </ul><BR> + <BR> + </DD> + + <DT><B>Server Configuration</B></DT> + + <DD>A number of <I>postgresql.conf</I> settings affect performance. + For more details, see <a href= "http://www.postgresql.org/docs/current/static/runtime.html"> - Administration Guide/Server Run-time Environment/Run-time Configuration</a>. - You can disable <I>fsync()</I> by using <i>fsync</I> option. This will - prevent <I>fsync()</I>s from flushing to disk after every - transaction.</P> - - <P>You can use the <I>shared_buffers</I> option to - increase the number of shared memory buffers used by the backend - processes. If you make this parameter too high, the - <I>postmaster</I> may not start because you have exceeded your - kernel's limit on shared memory space. Each buffer is 8K and the - default is 1000 buffers.</P> - - <P>You can also use the <I>sort_mem</I> (from PostgreSQL 8.0: <I>work_mem</I>) - options to increase the maximum amount of memory used by the backend - processes for each temporary sort. The default is 1024 (i.e. 1MB).</P> - - <P>You can also use the <SMALL>CLUSTER</SMALL> command to group - data in tables to match an index. See the <SMALL>CLUSTER</SMALL> - manual page for more details.</P> + Administration Guide/Server Run-time Environment/Run-time + Configuration</a> for a full listing, and for commentary see <a + href="http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html"> + http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html</a> + and <a href="http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html"> + http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html</a>. + <BR> + <BR> + </DD> + + <DT><B>Hardware Selection</B></DT> + + <DD>The effect of hardware on performance is detailed in <a + href="http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html"> + http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html</a>. + <BR> + <BR> + </DD> + </DL> <H4><A name="3.4">3.4</A>) What debugging features are available?</H4> - <P>PostgreSQL has several features that report status information - that can be valuable for debugging purposes.</P> + <P>There are many <CODE>log_*</CODE> server configuration variables + that enable printing of query and process statistics which can be + very useful for debugging and performance measurements.</P> + + <P><B>The following detailed debug instructions are to be used to + provide more detailed information for server developers debugging a + problem.</B></P> - <P>First, by running <I>configure</I> with the --enable-cassert + <P>It is also possible to debug the server if it isn't operating + properly. First, by running <I>configure</I> with the --enable-cassert option, many <I>assert()</I>s monitor the progress of the backend and halt the program when something unexpected occurs.</P> - <P>Both <I>postmaster</I> and <I>postgres</I> have several debug - options available. First, whenever you start <I>postmaster</I>, - make sure you send the standard output and error to a log file, - like:</P> -<PRE> - cd /usr/local/pgsql - ./bin/postmaster >server.log 2>&1 & -</PRE> - - <P>This will put a server.log file in the top-level PostgreSQL - directory. This file contains useful information about problems or - errors encountered by the server. <I>Postmaster</I> has a <I>-d</I> - option that allows even more detailed information to be reported. - The <I>-d</I> option takes a number that specifies the debug level. - Be warned that high debug level values generate large log - files.</P> + <P>The <I>postmaster</I> has a <I>-d</I> option that allows even more + detailed information to be reported. The <I>-d</I> option takes a + number that specifies the debug level. Be warned that high debug + level values generate large log files.</P> <P>If <I>postmaster</I> is not running, you can actually run the <I>postgres</I> backend from the command line, and type your @@ -630,10 +556,6 @@ the debugger, set any breakpoints, and continue through the startup sequence.</P> - <P>There are several <CODE>log_*</CODE> server configuration variables - that enable printing of process statistics which can be very useful - for debugging and performance measurements.</P> - <P>You can also compile with profiling to see what functions are taking execution time. The backend profile files will be deposited in the <I>pgsql/data/base/dbname</I> directory. The client profile @@ -643,27 +565,11 @@ <H4><A name="3.5">3.5</A>) Why do I get <I>"Sorry, too many clients"</I> when trying to connect?</H4> - <P>You need to increase <I>postmaster</I>'s limit on how many - concurrent backend processes it can start.</P> - - <P>The default limit is 32 processes. You can increase it by - restarting <I>postmaster</I> with a suitable <I>-N</I> value or - modifying <I>postgresql.conf</I>.</P> - - <P>Note that if you make <I>-N</I> larger than 32, you must also - increase <I>-B</I> beyond its default of 64; <I>-B</I> must be at - least twice <I>-N</I>, and probably should be more than that for - best performance. For large numbers of backend processes, you are - also likely to find that you need to increase various Unix kernel - configuration parameters. Things to check include the maximum size - of shared memory blocks, <SMALL>SHMMAX;</SMALL> the maximum number - of semaphores, <SMALL>SEMMNS</SMALL> and <SMALL>SEMMNI;</SMALL> the - maximum number of processes, <SMALL>NPROC;</SMALL> the maximum - number of processes per user, <SMALL>MAXUPRC;</SMALL> and the - maximum number of open files, <SMALL>NFILE</SMALL> and - <SMALL>NINODE</SMALL>. The reason that PostgreSQL has a limit on - the number of allowed backend processes is so your system won't run - out of resources.</P> + <P>You have reached the default limit is 100 database sessions. You + need to increase the <I>postmaster</I>'s limit on how many + concurrent backend processes it can start by changing the + <I>max_connections</I> value in <I>postgresql.conf</I> and + restarting the <I>postmaster</I>.</P> <H4><A name="3.6">3.6</A>) What is in the <I>pgsql_tmp</I> directory?</H4> @@ -681,18 +587,13 @@ to upgrade between major PostgreSQL releases?</H4> <P>The PostgreSQL team makes only small changes between minor releases, - so upgrading from 7.2 to 7.2.1 does not require a dump and restore. - However, major releases (e.g. from 7.2 to 7.3) often change the internal + so upgrading from 7.4 to 7.4.1 does not require a dump and restore. + However, major releases (e.g. from 7.3 to 7.4) often change the internal format of system tables and data files. These changes are often complex, - so we don't maintain backward compatability for data files. A dump outputs + so we don't maintain backward compatibility for data files. A dump outputs data in a generic format that can then be loaded in using the new internal format.</P> - <P>In releases where the on-disk format does not change, the - <I>pg_upgrade</I> script can be used to upgrade without a dump/restore. - The release notes mention whether <I>pg_upgrade</I> is available for the - release.</P> - <H4><A name="3.8">3.8</A>) What computer hardware should I use?</H4> <P>Because PC hardware is mostly compatible, people tend to believe that @@ -710,15 +611,13 @@ <H4><A name="4.1">4.1</A>) How do I <SMALL>SELECT</SMALL> only the first few rows of a query? A random row?</H4> - <P>See the <SMALL>FETCH</SMALL> manual page, or use - <SMALL>SELECT</SMALL> ... <SMALL>LIMIT</SMALL>....</P> - - <P>The entire query may have to be evaluated, even if you only want - the first few rows. Consider using a query that has an <SMALL>ORDER - BY</SMALL>. If there is an index that matches the <SMALL>ORDER - BY</SMALL>, PostgreSQL may be able to evaluate only the first few - records requested, or the entire query may have to be evaluated - until the desired rows have been generated.</P> + <P>To retrieve only a few rows, if you know at the number of rows + needed at the time of the <SMALL>SELECT</SMALL> use + <SMALL>LIMIT</SMALL> . If an index matches the <SMALL>ORDER + BY</SMALL> it is possible the entire query does not have to be + executed. If you don't know the number of rows at + <SMALL>SELECT</SMALL> time, use a cursor and + <SMALL>FETCH</SMALL>.</P> <P>To <SMALL>SELECT</SMALL> a random row, use: <PRE> @@ -743,28 +642,15 @@ database.</P> <P>There are also system tables beginning with <I>pg_</I> that describe - these too. Use <I>psql -l</I> will list all databases.</P> + these too.</P> + + <P>Use <I>psql -l</I> will list all databases.</P> <P>Also try the file <I>pgsql/src/tutorial/syscat.source</I>. It illustrates many of the <SMALL>SELECT</SMALL>s needed to get information from the database system tables.</P> - <H4><A name="4.3">4.3</A>) How do you remove a column from a - table, or change its data type?</H4> - - <P><SMALL>DROP COLUMN</SMALL> functionality was added in release 7.3 - with <SMALL>ALTER TABLE DROP COLUMN</SMALL>. In earlier versions, - you can do this:</P> -<PRE> - BEGIN; - LOCK TABLE old_table; - 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; - COMMIT; -</PRE> + <H4><A name="4.3">4.3</A>) How do you change a column's data type?</H4> <P>Changing the data type of a column can be done easily in 8.0 and later with <SMALL>ALTER TABLE ALTER COLUMN TYPE</SMALL>. @@ -784,19 +670,25 @@ table, and a database?</H4> <P>These are the limits:</P> -<PRE> - Maximum size for a database? unlimited (32 TB databases exist) - Maximum size for a table? 32 TB - Maximum size for a row? 1.6TB - Maximum size for a field? 1 GB - Maximum number of rows in a table? unlimited - Maximum number of columns in a table? 250-1600 depending on column types - Maximum number of indexes on a table? unlimited -</PRE> - - Of course, these are not actually unlimited, but limited to +<CENTER> +<TABLE BORDER=1> +<TR><TD>Maximum size for a database?</TD><TD>unlimited (32 TB databases +exist)</TD></TR> +<TR><TD>Maximum size for a table?</TD><TD>32 TB</TD></TR> +<TR><TD>Maximum size for a row?</TD><TD>1.6TB</TD></TR> +<TR><TD>Maximum size for a field?</TD><TD>1 GB</TD></TR> +<TR><TD>Maximum number of rows in a table?</TD><TD>unlimited</TD></TR> +<TR><TD>Maximum number of columns in a table?</TD><TD>250-1600 depending +on column types</TD></TR> +<TR><TD>Maximum number of indexes on a +table?</TD><TD>unlimited</TD></TR> +</TABLE> +</CENTER> +<BR> + + <P>Of course, these are not actually unlimited, but limited to available disk space and memory/swap space. Performance may suffer - when these values get unusually large. + when these values get unusually large.</P> <P>The maximum table size of 32 TB does not require large file support from the operating system. Large tables are stored as @@ -893,10 +785,10 @@ <LI>The search string can not start with a character class, e.g. [a-e].</LI> <LI>Case-insensitive searches such as <SMALL>ILIKE</SMALL> and - <I>~*</I> do not utilize indexes. Instead, use functional - indexes, which are described in section <a href="#4.10">4.10</a>.</LI> + <I>~*</I> do not utilize indexes. Instead, use expression + indexes, which are described in section <a href="#4.8">4.8</a>.</LI> <LI>The default <I>C</I> locale must be used during - <i>initdb</i> because it is not possible to know the next-greater + <i>initdb</i> because it is not possible to know the next-greatest character in a non-C locale. You can create a special <CODE>text_pattern_ops</CODE> index for such cases that work only for <SMALL>LIKE</SMALL> indexing. @@ -904,7 +796,7 @@ </UL> <P>In pre-8.0 releases, indexes often can not be used unless the data - types exactly match the index's column types. This is particularly + types exactly match the index's column types. This was particularly true of int2, int8, and numeric column indexes.</P> <H4><A name="4.7">4.7</A>) How do I see how the query optimizer is @@ -930,7 +822,7 @@ </PRE> This will not use an standard index. However, if you create a - functional index, it will be used: + expresssion index, it will be used: <PRE> CREATE INDEX tabindex ON tab (lower(col)); </PRE> @@ -943,16 +835,20 @@ <H4><A name="4.10">4.10</A>) What is the difference between the various character types?</H4> -<PRE> -Type Internal Name Notes --------------------------------------------------- -VARCHAR(n) varchar size specifies maximum length, no padding -CHAR(n) bpchar blank padded to the specified fixed length -TEXT text no specific upper limit on length -BYTEA bytea variable-length byte array (null-byte safe) -"char" char one character -</PRE> - +<CENTER> +<TABLE BORDER=1> +<TR><TH>Type</TH><TH>Internal Name</TH><TH>Notes</TH></TR> +<TR><TD>VARCHAR(n)</TD><TD>varchar</TD><TD>size specifies maximum +length, no padding</TD></TR> +<TR><TD>CHAR(n)</TD><TD>bpchar</TD><TD>blank padded to the specified +fixed length</TD></TR> +<TR><TD>TEXT</TD><TD>text</TD><TD>no specific upper limit on +length</TD></TR> +<TR><TD>BYTEA</TD><TD>bytea</TD><TD>variable-length byte array +(null-byte safe)</TD></TR> +<TR><TD>"char"</TD><TD>char</TD><TD>one character</TD></TR> +</TABLE> +</CENTER> <P>You will see the internal name when examining system catalogs and in some error messages.</P> @@ -996,10 +892,7 @@ BYTEA bytea variable-length byte array (null-byte safe) </PRE> See the <I>create_sequence</I> manual page for more information - about sequences. You can also use each row's <I>OID</I> field as a - unique value. However, if you need to dump and reload the database, - you need to use <I>pg_dump</I>'s <I>-o</I> option or <SMALL>COPY - WITH OIDS</SMALL> option to preserve the <SMALL>OID</SMALL>s. + about sequences. <H4><A name="4.11.2">4.11.2</A>) How do I get the value of a <SMALL>SERIAL</SMALL> insert?</H4> @@ -1030,19 +923,11 @@ BYTEA bytea variable-length byte array (null-byte safe) new_id = execute("SELECT currval('person_id_seq')"); </PRE> - <P>Finally, you could use the <A href="#4.12"><SMALL>OID</SMALL></A> - returned from the <SMALL>INSERT</SMALL> statement to look up the - default value, though this is probably the least portable approach, - and the oid value will wrap around when it reaches 4 billion. - In Perl, using DBI with the DBD::Pg module, the oid value is made - available via <I>$sth->{pg_oid_status}</I> after - <I>$sth->execute()</I>.</P> - <H4><A name="4.11.3">4.11.3</A>) Doesn't <I>currval()</I> lead to a race condition with other users?</H4> <P>No. <I>currval()</I> returns the current value assigned by your - backend, not by all users.</P> + session, not by all sessions.</P> <H4><A name="4.11.4">4.11.4</A>) Why aren't my sequence numbers reused on transaction abort? Why are there gaps in the numbering of @@ -1076,36 +961,7 @@ BYTEA bytea variable-length byte array (null-byte safe) are modified or reloaded. They are used by index entries to point to physical rows.</P> - <H4><A name="4.13">4.13</A>) What is the meaning of some of the - terms used in PostgreSQL?</H4> - - <P>Some of the source code and older documentation use terms that - have more common usage. Here are some:</P> - - <UL> - <LI>table, relation, class</LI> - - <LI>row, record, tuple</LI> - - <LI>column, field, attribute</LI> - - <LI>retrieve, select</LI> - - <LI>replace, update</LI> - - <LI>append, insert</LI> - - <LI><SMALL>OID</SMALL>, serial value</LI> - - <LI>portal, cursor</LI> - - <LI>range variable, table name, table alias</LI> - </UL> - - <P>A list of general database terms can be found at: <A href= - "http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html">http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html</A></P> - - <H4><A name="4.14">4.14</A>) Why do I get the error <I>"ERROR: + <H4><A name="4.13">4.13</A>) Why do I get the error <I>"ERROR: Memory exhausted in AllocSetAlloc()"</I>?</H4> <P>You probably have run out of virtual memory on your system, @@ -1124,12 +980,12 @@ BYTEA bytea variable-length byte array (null-byte safe) backend is returning too much data, try it before starting the client. - <H4><A name="4.15">4.15</A>) How do I tell what PostgreSQL version + <H4><A name="4.14">4.14</A>) How do I tell what PostgreSQL version I am running?</H4> <P>From <I>psql</I>, type <CODE>SELECT version();</CODE></P> - <H4><A name="4.16">4.16</A>) Why does my large-object operations + <H4><A name="4.15">4.15</A>) Why does my large-object operations get <I>"invalid large obj descriptor"</I>?</H4> <P>You need to put <CODE>BEGIN WORK</CODE> and <CODE>COMMIT</CODE> @@ -1145,15 +1001,15 @@ BYTEA bytea variable-length byte array (null-byte safe) <P>If you are using a client interface like <SMALL>ODBC</SMALL> you may need to set <CODE>auto-commit off.</CODE></P> - <H4><A name="4.17">4.17</A>) How do I create a column that will + <H4><A name="4.16">4.16</A>) How do I create a column that will default to the current time?</H4> <P>Use <I>CURRENT_TIMESTAMP</I>:</P> <PRE> - CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP ); + CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); </PRE> - <H4><A name="4.18">4.18</A>) How do I perform an outer join?</H4> + <H4><A name="4.17">4.17</A>) How do I perform an outer join?</H4> <P>PostgreSQL supports outer joins using the SQL standard syntax. Here are two examples:</P> @@ -1176,24 +1032,7 @@ BYTEA bytea variable-length byte array (null-byte safe) <SMALL>RIGHT</SMALL>, and <SMALL>FULL</SMALL> joins. Ordinary joins are called <SMALL>INNER</SMALL> joins.</P> - <P>In previous releases, outer joins can be simulated using - <SMALL>UNION</SMALL> and <SMALL>NOT IN</SMALL>. For example, when - joining <I>tab1</I> and <I>tab2</I>, the following query does an - <I>outer</I> join of the two tables:<BR> - <BR> - </P> -<PRE> - SELECT tab1.col1, tab2.col2 - FROM tab1, tab2 - WHERE tab1.col1 = tab2.col1 - UNION ALL - SELECT tab1.col1, NULL - FROM tab1 - WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2) - ORDER BY col1 -</PRE> - - <H4><A name="4.19">4.19</A>) How do I perform queries using + <H4><A name="4.18">4.18</A>) How do I perform queries using multiple databases?</H4> <P>There is no way to query a database other than the current one. @@ -1201,29 +1040,29 @@ BYTEA bytea variable-length byte array (null-byte safe) uncertain how a cross-database query should even behave.</P> <P><I>contrib/dblink</I> allows cross-database queries using - function calls. Of course, a client can make simultaneous + function calls. Of course, a client can also make simultaneous connections to different databases and merge the results on the client side.</P> - <H4><A name="4.20">4.20</A>) How do I return multiple rows or + <H4><A name="4.19">4.19</A>) How do I return multiple rows or columns from a function?</H4> - <P>In 7.3, you can easily return multiple rows or columns from a - function, + <P>It is easy using set-returning functions, <a href="http://techdocs.postgresql.org/guides/SetReturningFunctions"> http://techdocs.postgresql.org/guides/SetReturningFunctions</a>. - <H4><A name="4.21">4.21</A>) Why can't I reliably create/drop + <H4><A name="4.20">4.20</A>) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?</H4> - <P>PL/PgSQL caches function contents, and an unfortunate side effect + + <P>PL/PgSQL caches function scripts, and an unfortunate side effect is that if a PL/PgSQL function accesses a temporary table, and that - table is later dropped and recreated, and the function called - again, the function will fail because the cached function contents - still point to the old temporary table. The solution is to use + table is later dropped and recreated, and the function called again, + the function will fail because the cached function contents still + point to the old temporary table. The solution is to use <SMALL>EXECUTE</SMALL> for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time.</P> - <H4><A name="4.22">4.22</A>) What encryption options are available? + <H4><A name="4.21">4.21</A>) What encryption options are available? </H4> <UL> <LI><I>contrib/pgcrypto</I> contains many encryption functions for @@ -1236,8 +1075,7 @@ BYTEA bytea variable-length byte array (null-byte safe) encrypted transport, such as stunnel or ssh, rather than PostgreSQL's native SSL connections.) <LI>Database user passwords are automatically encrypted when stored in - version 7.3. In previous versions, you must enable the option - <I>PASSWORD_ENCRYPTION</I> in <I>postgresql.conf</I>.</LI> + the system tables.</LI> <LI>The server can run using an encrypted file system.</LI> </UL> @@ -1277,4 +1115,3 @@ BYTEA bytea variable-length byte array (null-byte safe) compiler compute the dependencies automatically.</P> </BODY> </HTML> - |