-<!DOCTYPE html PUBLIC "-//W3C//DTD html 4.01 transitional//EN">
-
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
- <!-- DOCTYPE html PUBLIC "-//W3C//DTD HTML 3.2//EN" -->
- <!-- HTML -->
-
<HEAD>
<META name="generator" content="HTML Tidy, see www.w3.org">
-
+ <META http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<TITLE>PostgreSQL FAQ</TITLE>
</HEAD>
alink="#0000ff">
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
- <P>Last updated: Fri Oct 11 00:59:31 EDT 2002</P>
+ <P>Last updated: Sun Nov 14 16:32:47 EST 2004</P>
<P>Current maintainer: Bruce Momjian (<A href=
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
</P>
- <P>The most recent version of this document can be viewed at <A
- href=
- "http://www.Postgresql.org/docs/faq-english.html">http://www.PostgreSQL.org/docs/faq-english.html</A>.</P>
+ <P>The most recent version of this document can be viewed at <A href=
+ "http://www.PostgreSQL.org/docs/faqs/FAQ.html">http://www.PostgreSQL.org/docs/faqs/FAQ.html</A>.</P>
<P>Platform-specific questions are answered at <A href=
- "http://www.PostgreSQL.org/users-lounge/docs/faq.html">http://www.PostgreSQL.org/users-lounge/docs/faq.html</A>.</P>
+ "http://www.PostgreSQL.org/docs/index.html">http://www.PostgreSQL.org/docs/index.html</A>.</P>
<HR>
<H2 align="center">General Questions</H2>
- <A href="#1.1">1.1</A>) What is PostgreSQL? How is it
- pronounced?<BR>
+ <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.3">1.3</A>) What Unix platforms does PostgreSQL run
on?<BR>
<A href="#2.2">2.2</A>) What tools are available for using
PostgreSQL with Web pages?<BR>
<A href="#2.3">2.3</A>) Does PostgreSQL have a graphical user
- interface? A report generator? An embedded query language
interface?<BR>
<A href="#2.4">2.4</A>) What languages are available to
communicate with PostgreSQL?<BR>
<A href="#3.7">3.7</A>) What debugging features are available?<BR>
<A href="#3.8">3.8</A>) Why do I get <I>"Sorry, too many
clients"</I> when trying to connect?<BR>
- <A href="#3.9">3.9</A>) What are the <I>pg_sorttempNNN.NN</I>
- files in my database directory?<BR>
- <A href="#3.10">3.10</A>) Why do I need to do a dump and restore
+ <A href="#3.9">3.9</A>) What is in the <I>pgsql_tmp</I>
+ directory?<BR>
+ <A href="#3.10">3.10</A>) Why do I need to do a dump and restore
to upgrade PostgreSQL releases?<BR>
-
+ <A href="#3.11">3.11</A>) What computer hardware should I use?<BR>
+
<H2 align="center">Operational Questions</H2>
<A href="#4.1">4.1</A>) What is the difference between binary
cursors and normal cursors?<BR>
<A href="#4.2">4.2</A>) How do I <SMALL>SELECT</SMALL> only the
- first few rows of a query?<BR>
+ first few rows of a query? A random row?<BR>
<A href="#4.3">4.3</A>) How do I get a list of tables or other
things I can see in <I>psql</I>?<BR>
<A href="#4.4">4.4</A>) How do you remove a column from a
- table?<BR>
+ table, or change it's data type?<BR>
<A href="#4.5">4.5</A>) What is the maximum size for a row, a
table, and a database?<BR>
<A href="#4.6">4.6</A>) How much database disk space is required
from a function?<BR>
<A href="#4.26">4.26</A>) Why can't I reliably create/drop
temporary tables in PL/PgSQL functions?<BR>
- <A href="#4.27">4.27</A>) What replication options are available?<BR>
- <A href="#4.28">4.28</A>) What encryption options are available?<BR>
+ <A href="#4.28">4.27</A>) What encryption options are available?<BR>
<H2 align="center">Extending PostgreSQL</H2>
<H2 align="center">General Questions</H2>
- <H4><A name="1.1">1.1</A>) What is PostgreSQL?</H4>
+ <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 pronounced <I>Post-Gres-Q-L</I>. An audio file is
+ available at http://www.postgresql.org/postgresql.mp3 for those
+ would like to hear the pronunciation.
+ </P>
<P>PostgreSQL is an enhancement of the POSTGRES database management
- system, a next-generation <SMALL>DBMS</SMALL> research prototype.
+ 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 Internet
+ <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
- below on how to join). This team is now responsible for all
- development of PostgreSQL.</P>
+ 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=
+ "http://www.PostgreSQL.org/docs/faqs/FAQ_DEV.html">http://www.PostgreSQL.org/docs/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,
<P>PostgreSQL Data Base Management System</P>
- <P>Portions copyright (c) 1996-2002, PostgreSQL Global Development
+ <P>Portions copyright (c) 1996-2004, PostgreSQL Global Development
Group Portions Copyright (c) 1994-6 Regents of the University of
California</P>
<H4><A name="1.3">1.3</A>) What Unix platforms does PostgreSQL run
on?</H4>
- <P>In general, a modern Unix-compatible platform should be able to
+ <P>In general, any modern Unix-compatible platform should be able to
run PostgreSQL. The platforms that had received explicit testing at
the time of release are listed in the installation
instructions.</P>
<H4><A name="1.4">1.4</A>) What non-Unix ports are available?</H4>
- <P><STRONG>Client</STRONG></P>
-
- <P>It is possible to compile the <I>libpq</I> C library, psql, and
- other interfaces and binaries to run on MS Windows platforms. In
- this case, the client is running on MS Windows, and communicates
- via TCP/IP to a server running on one of our supported Unix
- platforms. A file <I>win31.mak</I> is included in the distribution
- for making a Win32 <I>libpq</I> library and <I>psql</I>. PostgreSQL
- also communicates with <SMALL>ODBC</SMALL> clients.</P>
+ <P>Starting with version 8.0, PostgreSQL now runs natively on
+ Microsoft Windows NT-based operating systems like Win2000, WinXP,
+ and Win2003. A prepackaged installer is available at <a href=
+ "http://pgfoundry.org/projects/pginstaller">
+ http://pgfoundry.org/projects/pginstaller</a>.
- <P><STRONG>Server</STRONG></P>
-
- <P>The database server can run on Windows NT and Win2k using
- Cygwin, the Cygnus Unix/NT porting library. See
- <I>pgsql/doc/FAQ_MSWIN</I> in the distribution or the <A href=
- "http://www.postgresql.org/docs/faq-mswin.html">MS Windows FAQ</A>
- on our web site. We have no plan to do a native port to any
- Microsoft platform.</P>
+ <p>There is also a Novell Netware 6 port at
+ <a href="http://forge.novell.com">http://forge.novell.com</a>.</p>
<H4><A name="1.5">1.5</A>) Where can I get PostgreSQL?</H4>
<A href="http://www.PostgreSQL.org">http://www.PostgreSQL.org</A>
</BLOCKQUOTE>
- <P>There is also an IRC channel on EFNet, channel
- <I>#PostgreSQL</I>. I use the Unix command <CODE>irc -c
- '#PostgreSQL' "$USER" irc.phoenix.net.</CODE></P>
+ <P>There is also an IRC channel on Freenode and EFNet,
+ channel <I>#PostgreSQL</I>. You can use the Unix command <CODE>irc -c
+ '#PostgreSQL' "$USER" irc.phoenix.net.</CODE> or <CODE>irc -c
+ '#PostgreSQL' "$USER" irc.freenode.net.</CODE></P>
<P>A list of commercial support companies is available at <A href=
- "http://www.postgresql.org/users-lounge/commercial-support.html">http://www.postgresql.org/users-lounge/commercial-support.html</A>.</P>
+ "http://techdocs.postgresql.org/companies.php">http://techdocs.postgresql.org/companies.php</A>.</P>
<H4><A name="1.7">1.7</A>) What is the latest release?</H4>
- <P>The latest release of PostgreSQL is version 7.2.3.</P>
+ <P>The latest release of PostgreSQL is version 7.4.5.</P>
- <P>We plan to have major releases every four months.</P>
+ <P>We plan to have major releases every six to eight months.</P>
<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 manual online at <A href=
- "http://www.PostgreSQL.org/users-lounge/docs/">http://www.PostgreSQL.org/users-lounge/docs/</A>.</P>
+ can also browse the manual
s 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>
"http://www.commandprompt.com/ppbook/">http://www.commandprompt.com/ppbook/</A>.
There is a list of PostgreSQL books available for purchase at <A
href=
- "http://www.postgresql.org/books/">http://www.postgresql.org/books/</A>.
+ "http://techdocs.postgresql.org/techdocs/bookreviews.php">http://techdocs.PostgreSQL.org/techdocs/bookreviews.php</A>.
There is also a collection of PostgreSQL technical articles at <A
href=
- "http://techdocs.postgresql.org/">http://techdocs.postgresql.org/</A>.</P>
+ "http://techdocs.PostgreSQL.org/">http://techdocs.PostgreSQL.org/</A>.</P>
<P><I>psql</I> has some nice \d commands to show information about
types, operators, functions, aggregates, etc.</P>
<H4><A name="1.13">1.13</A>) How do I submit a bug report?</H4>
- <P>Please visit the <A href=
- "http://www.postgresql.org/bugs/bugs.php">PostgreSQL BugTool</A>
- page, which gives guidelines and directions on how to submit a
- bug.</P>
+ <P>Please visit the
PostgreSQL BugTool page at <A href=
+ "http://www.PostgreSQL.org/bugs/bugs.php">http://www.PostgreSQL.org/bugs/bugs.php</A>,
+ which gives guidelines and directions on how to submit a
+ bug report.</P>
<P>Also check out our ftp site <A href=
"ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub</A> to
<DD>PostgreSQL has performance similar to other commercial and
open source databases. it is faster for some things, slower for
others. In comparison to MySQL or leaner database systems, we are
- slower on inserts/updates because of transaction overhead. Of
- course, MySQL does not have any of the features mentioned in the
+ faster for multiple users, complex queries, and a read/write query
+ load. MySQL is faster for simple SELECT queries done by a few users.
+ Of course, MySQL does not have most of the features mentioned in the
<I>Features</I> section above. We are built for reliability and
- features, though we continue to improve performance in every
+ features, and we continue to improve performance in every
release. There is an interesting Web page comparing PostgreSQL to
- MySQL at <A href= "http://openacs.org/why-not-mysql.html">
-
- http://openacs.org/why-not-mysql.html</A><BR>
+ MySQL at <A href="http://openacs.org/philosophy/why-not-mysql.html">
+ http://openacs.org/philosophy/why-not-mysql.html</A> Also, MySQL is
+ is a company that distributes its products via open source, and requires
+ a commercial license for close-source software, not an
+ open source development community like PostgreSQL.<BR>
<BR>
</DD>
<DT><B>Support</B></DT>
- <DD>Our mailing list provides a large group of developers and
- users to help resolve any problems encountered. While we can not
+ <DD>Our mailing lists provide contact with a large group of developers
+ and users to help resolve any problems encountered. While we cannot
guarantee a fix, commercial <SMALL>DBMS</SMALL>s do not always
supply a fix either. Direct access to developers, the user
community, manuals, and the source code often make PostgreSQL
support superior to other <SMALL>DBMS</SMALL>s. There is
commercial per-incident support available for those who need it.
- (See <A href="#1.6">support FAQ item</A>.)<BR>
+ (See <A href="#1.6">FAQ section 1.6</A>.)<BR>
<BR>
</DD>
PostgreSQL?</H4>
<P>PostgreSQL has had a first-class infrastructure since we started
- six years ago. This is all thanks to Marc Fournier, who has created
+ 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
<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=
- "https://store.pgsql.com/shopping/index.php?id=1">
- https://store.pgsql.com/shopping/index.php?id=1</A>
+ 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
send a check to the contact address.</P>
<HR>
+ <P>Also, if you have a success story about PostgreSQL, please submit
+ it to our advocacy site at <a href="http://advocacy.postgresql.org">
+ http://advocacy.postgresql.org</a>.</P>
+
+
<H2 align="center">User Client Questions</H2>
<H4><A name="2.1">2.1</A>) Are there <SMALL>ODBC</SMALL> drivers
<P>There are two <SMALL>ODBC</SMALL> drivers available, PsqlODBC
and OpenLink <SMALL>ODBC</SMALL>.</P>
- <P>PsqlODBC is included in the distribution. More information about
- it can be gotten from <A href=
- "ftp://ftp.PostgreSQL.org/pub/odbc/">ftp://ftp.PostgreSQL.org/pub/odbc/</A>.</P>
+ <P>You can download PsqlODBC from <A href=
+ "http://gborg.postgresql.org/project/psqlodbc/projdisplay.php">
+ http://gborg.postgresql.org/project/psqlodbc/projdisplay.php</A>.</P>
<P>OpenLink <SMALL>ODBC</SMALL> can be gotten from <A href=
"http://www.openlinksw.com/">http://www.openlinksw.com</A>. It
available. Please send questions to <A href=
"mailto:postgres95@openlink.co.uk">postgres95@openlink.co.uk</A>.</P>
- <P>See also the <A href=
- "http://www.postgresql.org/devel-corner/docs/programmer/odbc.html">ODBC
- chapter of the Programmer's Guide</A>.</P>
-
<H4><A name="2.2">2.2</A>) What tools are available for using
PostgreSQL with Web pages?</H4>
<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>There is also one at <A href=
- "http://www.phone.net/home/mwm/hotlist/">http://www.phone.net/home/mwm/hotlist/.</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 complex cases, many use the Perl interface and CGI.pm.</P>
+ <P>For complex cases, many use the Perl interface and CGI.pm or mod_perl.</P>
<H4><A name="2.3">2.3</A>) Does PostgreSQL have a graphical user
- interface? A report generator? An embedded query language
interface?</H4>
- <P>We have a nice graphical user interface called <I>pgaccess</I>,
- which is shipped as part of the distribution. <I>pgaccess</I> also
- has a report generator. The Web page is <A href=
- "http://www.flex.ro/pgaccess">http://www.flex.ro/pgaccess</A></P>
+ <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>) and Rekall (<a href="http://www.thekompany.com/products/rekall/">
+ http://www.thekompany.com/products/rekall/</a>, proprietary). 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>We also include <I>ecpg</I>, which is an embedded SQL query
- language interface for C.</P>
+ <H4><A name="2.4">2.4</A>) What languages are able to communicate with
+ PostgreSQL?</H4>
- <H4><A name="2.4">2.4</A>) What languages are available to
- communicate with PostgreSQL?</H4>
+ <P>Most popular programming languages contain an interface to
+ PostgreSQL. Check your programming language's list of extension
+ modules.</P>
- <P>We have:</P>
+ <P>The following interfaces are included in the PostgreSQL
+ distribution:</P>
<UL>
<LI>C (libpq)</LI>
- <LI>C++ (libpq++)</LI>
-
<LI>Embedded C (ecpg)</LI>
<LI>Java (jdbc)</LI>
- <LI>Perl (perl5)</LI>
-
- <LI>ODBC (odbc)</LI>
-
<LI>Python (PyGreSQL)</LI>
<LI>TCL (libpgtcl)</LI>
- <LI>C Easy API (libpgeasy)</LI>
-
- <LI>Embedded <SMALL>HTML</SMALL> (<A href=
- "http://www.php.net">PHP from http://www.php.net</A>)</LI>
</UL>
- <P>Additional interfaces are available at <a
- href="http://www.postgresql.org/interfaces.html">
- http://www.postgresql.org/interfaces.html.</A>
+ <P>Additional interfaces are available at
+ <a href="http://gborg.postgresql.org">http://gborg.postgresql.org</A>
+ in the <I>Drivers/Interfaces</I> section.
</P>
<HR>
<I>postmaster</I>. For most systems, with default numbers of
buffers and processes, you need a minimum of ~1 MB. See the <A
href=
- "http://www.postgresql.org/idocs/index.php?kernel-resources.html">PostgreSQL
+ "http://www.PostgreSQL.org/docs/view.php?version=current&idoc=1&file=kernel-resources.html">PostgreSQL
Administrator's Guide</A> for more detailed information about
shared memory and semaphores.</P>
hosts?</H4>
<P>By default, PostgreSQL only allows connections from the local
- machine using Unix domain sockets. Other machines will not be able
- to connect unless you add the <I>-i</I> flag to <I>postmaster</I>,
- <B>and</B> enable host-based authentication by modifying the file
- <I>$PGDATA/pg_hba.conf</I> accordingly. This will allow TCP/IP
- connections.</P>
+ 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>
<H4><A name="3.6">3.6</A>) How do I tune the database engine for
better performance?</H4>
<P>Certainly, indexes can speed up queries. The
- <SMALL>EXPLAIN</SMALL> command allows you to see how PostgreSQL is
+ <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,
+ 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
<P>If <I>postmaster</I> is running, start <I>psql</I> in one
window, then find the <SMALL>PID</SMALL> of the <I>postgres</I>
process used by <I>psql</I>. Use a debugger to attach to the
- <I>postgres</I> <SMALL>PID.</SMALL> You can set breakpoints in the
+ <I>postgres</I> <SMALL>PID</SMALL>. You can set breakpoints in the
debugger and issue queries from <I>psql</I>. If you are debugging
<I>postgres</I> startup, you can set PGOPTIONS="-W n", then start
<I>psql</I>. This will cause startup to delay for <I>n</I> seconds
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
+ <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>In PostgreSQL versions prior to 6.5, the maximum number of
- backends was 64, and changing it required a rebuild after altering
- the MaxBackendId constant in
- <I>include/storage/sinvaladt.h</I>.</P>
-
- <H4><A name="3.9">3.9</A>) What are the <I>pg_sorttempNNN.NN</I>
- files in my database directory?</H4>
+ <H4><A name="3.9">3.9</A>) What is in the <I>pgsql_tmp</I> directory?</H4>
- <P>They are temporary files generated by the query executor. For
- example, if a sort needs to be done to satisfy an <SMALL>ORDER
- BY,</SMALL> and the sort requires more space than the backend's
- <I>-S</I> parameter allows, then temporary files are created to
- hold the extra data.</P>
+ <P>This directory contains temporary files generated by the query
+ executor. For example, if a sort needs to be done to satisfy an
+ <SMALL>ORDER BY</SMALL> and the sort requires more space than the
+ backend's <I>-S</I> parameter allows, then temporary files are created
+ here to hold the extra data.</P>
- <P>The temporary files should be deleted automatically, but might
- not if a backend crashes during a sort. If you have no backends
- running at the time, it is safe to delete the pg_tempNNN.NN
- files.</P>
+ <P>The temporary files are usually deleted automatically, but might
+ remain if a backend crashes during a sort. A stop and restart of the
+ <I>postmaster</I> will remove files from those directories.</P>
<H4><A name="3.10">3.10</A>) Why do I need to do a dump and restore
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 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 data
- in a generic format that can then be loaded in using the new internal
- format.
+ However, major releases (e.g. from 7.2 to 7.3) 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
+ 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.
+ <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.11">3.11</A>) What computer hardware should I use?</H4>
+
+ <P>Because PC hardware is mostly compatible, people tend to believe that
+ all PC hardware is of equal quality. It is not. ECC RAM, SCSI, and
+ quality motherboards are more reliable and have better performance than
+ less expensive hardware. PostgreSQL will run on almost any hardware,
+ but if reliability and performance are important it is wise to
+ research your hardware options thoroughly. Our email lists can be used
+ to discuss hardware options and tradeoffs.</P>
<HR>
description.</P>
<H4><A name="4.2">4.2</A>) How do I <SMALL>SELECT</SMALL> only the
- first few rows of a query?</H4>
+ 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 a query that has an <SMALL>ORDER
- BY.</SMALL> If there is an index that matches the <SMALL>ORDER
+ 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 <SMALL>SELECT</SMALL> a random row, use:
+<PRE>
+ SELECT col
+ FROM tab
+ ORDER BY random()
+ LIMIT 1;
+</PRE>
+
<H4><A name="4.3">4.3</A>) How do I get a list of tables or other
things I can see in <I>psql</I>?</H4>
- <P>You can read the source code for <I>psql</I> in file
- <I>pgsql/src/bin/psql/describe.c</I>. It contains
- <SMALL>SQL</SMALL> commands that generate the output for psql's
- backslash commands. You can also start <I>psql</I> with the
- <I>-E</I> option so it will print out the queries it uses to
- execute the commands you give.</P>
+ <P>Use the \dt command to see tables in <I>psql</I>. For a complete list of
+ commands inside psql you can use \?. Alternatively you can read the source
+ code for <I>psql</I> in file <I>pgsql/src/bin/psql/describe.c</I>, it
+ contains <SMALL>SQL</SMALL> commands that generate the output for
+ <I>psql</I>'s backslash commands. You can also start <I>psql</I> with the
+ <I>-E</I> option so it will print out the queries it uses to execute the
+ commands you give. PostgreSQL also provides an <SMALL>SQLi</SMALL> compliant
+ INFORMATION SCHEMA interface you can query to get information about the
+ database.</P>
<H4><A name="4.4">4.4</A>) How do you remove a column from a
- table?</H4>
+ table, or change its data type?</H4>
- <P>We do not support <SMALL>ALTER TABLE DROP COLUMN,</SMALL> but do
- this:</P>
+ <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;
COMMIT;
</PRE>
+ <P>To change the data type of a column, do this:</P>
+<PRE>
+ BEGIN;
+ ALTER TABLE tab ADD COLUMN new_col <i>new_data_type</i>;
+ UPDATE tab SET new_col = CAST(old_col AS <i>new_data_type</i>);
+ ALTER TABLE tab DROP COLUMN old_col;
+ COMMIT;
+</PRE>
+ <P>You might then want to do <I>VACUUM FULL tab</I> to reclaim the
+ disk space used by the expired rows.</P>
+
<H4><A name="4.5">4.5</A>) What is the maximum size for a row, a
table, and a database?</H4>
<P>These are the limits:</P>
<PRE>
- Maximum size for a database? unlimited (1 TB databases exist)
- Maximum size for a table? 16 TB
+ 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
available disk space and memory/swap space. Performance may suffer
when these values get unusually large.
- <P>The maximum table size of 16 TB does not require large file
+ <P>The maximum table size of 32 TB does not require large file
support from the operating system. Large tables are stored as
multiple 1 GB files so file system size limits are not
important.</P>
<P>The maximum table size and maximum number of columns can be
- increased if the default block size is increased to 32k.</P>
+ quadrupled by increasing the default block size to 32k.</P>
<H4><A name="4.6">4.6</A>) How much database disk space is required
to store data from a typical text file?</H4>
The size of the PostgreSQL database file containing this data can
be estimated as 6.4 MB:</P>
<PRE>
- 36 bytes: each row header (approximate)
- 24 bytes: one int field and one text filed
+ 32 bytes: each row header (approximate)
+ 24 bytes: one int field and one text field
+ 4 bytes: pointer on page to tuple
----------------------------------------
- 64 bytes per row
+ 60 bytes per row
The data page size in PostgreSQL is 8192 bytes (8 KB), so:
8192 bytes per page
- ------------------- = 128 rows per database page (rounded down)
- 64 bytes per row
+ ------------------- = 136 rows per database page (rounded down)
+ 60 bytes per row
100000 data rows
- -------------------- = 782 database pages (rounded up)
+ -------------------- = 735 database pages (rounded up)
128 rows per page
-782 database pages * 8192 bytes per page = 6,406,144 bytes (6.4 MB)
+735 database pages * 8192 bytes per page = 6,021,120 bytes (6 MB)
</PRE>
<P>Indexes do not require as much overhead, but do contain the data
that is being indexed, so they can be large also.</P>
- <P><SMALL>NULL</SMALL>s are stored in bitmaps, so they
+ <P><SMALL>NULL</SMALL>s are stored as bitmaps, so they
use very little space.</P>
<H4><A name="4.7">4.7</A>) How do I find out what tables, indexes,
SELECT col
FROM tab
ORDER BY col [ DESC ]
- LIMIT 1
+ LIMIT 1;
</PRE>
+ <P>If you believe the optimizer is incorrect in choosing a
+ sequential scan, use <CODE>SET enable_seqscan TO 'off'</CODE> and
+ run tests to see if an index scan is indeed faster.</P>
+
<P>When using wild-card operators such as <SMALL>LIKE</SMALL> or
- <I>~</I>, indexes can only be used in certain circumstances:
+ <I>~</I>, indexes can only be used in certain circumstances:</P>
<UL>
<LI>The beginning of the search string must be anchored to the start
- of the string, i.e.:</LI>
+ of the string, i.e.
<UL>
- <LI><SMALL>LIKE</SMALL> patterns must not start with <I>%.</I></LI>
+ <LI><SMALL>LIKE</SMALL> patterns must not start with <I>%</I>.</LI>
<LI><I>~</I> (regular expression) patterns must start with
- <I>^.</I></LI>
- </UL>
+ <I>^</I>.</LI>
+ </UL></LI>
<LI>The search string can not start with a character class,
e.g. [a-e].</LI>
- <LI>Case-insensitive searches like <SMALL>ILIKE</SMALL> and
- <I>~*</I> can not be used. Instead, use functional
- indexes, which are described later in this FAQ.</LI>
- <LI>The default <I>C</I> local must be used during
- <i>initdb.</i></LI>
+ <LI>Case-insensitive searches such as <SMALL>ILIKE</SMALL> and
+ <I>~*</I> do not utilise indexes. Instead, use functional
+ indexes, which are described in section <a href="#4.12">4.12</a>.</LI>
+ <LI>The default <I>C</I> locale must be used during
+ <i>initdb</i>.</LI>
</UL>
<P>
+ <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
+ true of int2, int8, and numeric column indexes.</P>
+
<H4><A name="4.9">4.9</A>) How do I see how the query optimizer is
evaluating my query?</H4>
<PRE>
SELECT *
FROM tab
- WHERE lower(col) = 'abc'
+ WHERE lower(col) = 'abc';
</PRE>
This will not use an standard index. However, if you create a
functional index, it will be used:
<PRE>
- CREATE INDEX tabindex on tab (lower(col));
+ CREATE INDEX tabindex ON tab (lower(col));
</PRE>
<H4><A name="4.13">4.13</A>) In a query, how do I detect if a field
<PRE>
Type Internal Name Notes
--------------------------------------------------
-"char" char 1 character
-CHAR(#) bpchar blank padded to the specified fixed length
-VARCHAR(#) varchar size specifies maximum length, no padding
+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>
<P>You will see the internal name when examining system catalogs
and in some error messages.</P>
- <P>The last four types above are "varlena" types (i.e., the first
+ <P>The first four types above are "varlena" types (i.e., the first
four bytes on disk are the length, followed by the data). Thus the
actual space used is slightly greater than the declared size.
However, these data types are also subject to compression or being
stored out-of-line by <SMALL>TOAST</SMALL>, so the space on disk
might also be less than expected.</P>
- <P><SMALL>CHAR()</SMALL> is best when storing strings that are
- usually the same length. <SMALL>VARCHAR()</SMALL> is best when
- storing variable-length strings but it limits how long a string can
- be. <SMALL>TEXT</SMALL> is for strings of unlimited length, maximum
- 1 gigabyte. <SMALL>BYTEA</SMALL> is for storing binary data,
- particularly values that include <SMALL>NULL</SMALL> bytes.</P>
+ <SMALL>VARCHAR(n)</SMALL> is best when storing variable-length
+ strings and it limits how long a string can be. <SMALL>TEXT</SMALL>
+ is for strings of unlimited length, with a maximum of one gigabyte.
+ <P><SMALL>CHAR(n)</SMALL> is for storing strings that are all the
+ same length. <SMALL>CHAR(n)</SMALL> pads with blanks to the specified
+ length, while <SMALL>VARCHAR(n)</SMALL> only stores the characters
+ supplied. <SMALL>BYTEA</SMALL> is for storing binary data,
+ particularly values that include <SMALL>NULL</SMALL> bytes. All the
+ types described here have similar performance characteristics.</P>
<H4><A name="4.15.1">4.15.1</A>) How do I create a
serial/auto-incrementing field?</H4>
<P>PostgreSQL supports a <SMALL>SERIAL</SMALL> data type. It
- auto-creates a sequence and index on the column. For example,
+ auto-creates a sequence. For example,
this:</P>
<PRE>
CREATE TABLE person (
id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
name TEXT
);
- CREATE UNIQUE INDEX person_id_key ON person ( id );
</PRE>
See the <I>create_sequence</I> manual page for more information
<P>One approach is to retrieve the next <SMALL>SERIAL</SMALL> value
from the sequence object with the <I>nextval()</I> function
<I>before</I> inserting and then insert it explicitly. Using the
- example table in <A href="#4.15.1">4.15.1</A>, that might look like
- this in Perl:</P>
+ example table in <A href="#4.15.1">4.15.1</A>, an example in a
+ pseudo-language would look like this:</P>
<PRE>
- new_id = output of "SELECT nextval('person_id_seq')"
- INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal');
+ new_id = execute("SELECT nextval('person_id_seq')");
+ execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
</PRE>
You would then also have the new value stored in
automatically created <SMALL>SEQUENCE</SMALL> object will be named
<<I>table</I>>_<<I>serialcolumn</I>>_<I>seq</I>, where
<I>table</I> and <I>serialcolumn</I> are the names of your table
- and your <SMALL>SERIAL</SMALL> column, respectively.
+ and your <SMALL>SERIAL</SMALL> column, respectively.
<P>Alternatively, you could retrieve the assigned
- <SMALL>SERIAL</SMALL> value with the <I>currval</I>() function
+ <SMALL>SERIAL</SMALL> value with the <I>currval()</I> function
<I>after</I> it was inserted by default, e.g.,</P>
<PRE>
- INSERT INTO person (name) VALUES ('Blaise Pascal');
- new_id = output of "SELECT currval('person_id_seq')";
+ execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
+ new_id = execute("SELECT currval('person_id_seq')");
</PRE>
Finally, you could use the <A href="#4.16"><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.
+ 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 Edmund Mergl's DBD::Pg module, the oid
value is made available via <I>$sth->{pg_oid_status}</I> after
- <I>$sth->execute()</I>.
+ <I>$sth->execute()</I>.
<H4><A name="4.15.3">4.15.3</A>) Don't <I>currval()</I> and
<I>nextval()</I> lead to a race condition with other users?</H4>
- <P>No. Currval() returns the current value assigned by your
+ <P>No. <I>currval()</I> returns the current value assigned by your
backend, not by all users.</P>
<H4><A name="4.15.4">4.15.4</A>) Why aren't my sequence numbers
Every row that is created in PostgreSQL gets a unique
<SMALL>OID</SMALL>. All <SMALL>OID</SMALL>s generated during
<I>initdb</I> are less than 16384 (from
- <I>backend/access/transam.h</I>). All user-created
+ <I>include/access/transam.h</I>). All user-created
<SMALL>OID</SMALL>s are equal to or greater than this. By default,
all these <SMALL>OID</SMALL>s are unique not only within a table or
database, but unique within the entire PostgreSQL installation.</P>
of the table, with the original <SMALL>OID</SMALL>s, there is no
reason you can't do it:</P>
<PRE>
- CREATE TABLE new_table(old_oid oid, mycol int);
- SELECT old_oid, mycol INTO new FROM old;
- COPY new TO '/tmp/pgtable';
- DELETE FROM new;
- COPY new WITH OIDS FROM '/tmp/pgtable';
-<!--
- CREATE TABLE new_table (mycol int);
- INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM old_table;
--->
+ CREATE TABLE new_table(mycol int);
+ SELECT oid AS old_oid, mycol INTO tmp_table FROM old_table;
+ COPY tmp_table TO '/tmp/pgtable';
+ COPY new_table WITH OIDS FROM '/tmp/pgtable';
+ DROP TABLE tmp_table;
</PRE>
-
<P>O<SMALL>ID</SMALL>s are stored as 4-byte integers, and will
overflow at 4 billion. No one has reported this ever happening, and
we plan to have the limit removed before anyone does.</P>
</UL>
<P>A list of general database terms can be found at: <A href=
- "http://www.comptechnews.com/~reaster/dbdesign.html">http://www.comptechnews.com/~reaster/dbdesign.html</A></P>
+ "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.18">4.18</A>) Why do I get the error <I>"ERROR:
Memory exhausted in AllocSetAlloc()"</I>?</H4>
and all subprocesses created after the command is run. If you are
having a problem with the <SMALL>SQL</SMALL> client because the
backend is returning too much data, try it before starting the
- client.
+ client.
<H4><A name="4.19">4.19</A>) How do I tell what PostgreSQL version
I am running?</H4>
- <P>From <I>psql</I>, type <CODE>select version();</CODE></P>
+ <P>From <I>psql</I>, type <CODE>SELECT version();</CODE></P>
<H4><A name="4.20">4.20</A>) Why does my large-object operations
get <I>"invalid large obj descriptor"</I>?</H4>
<H4><A name="4.22">4.22</A>) Why are my subqueries using
<CODE><SMALL>IN</SMALL></CODE> so slow?</H4>
- <P>Currently, we join subqueries to outer queries by sequentially
- scanning the result of the subquery for each row of the outer
- query. If the subquery returns only a few rows and the outer query
- returns many rows, <CODE><SMALL>IN</SMALL></CODE> is fastest. To
+ <P>In versions prior to 7.4, subqueries were joined to outer queries
+ by sequentially scanning the result of the subquery for each row of
+ the outer query. If the subquery returns only a few rows and the outer
+ query returns many rows, <CODE><SMALL>IN</SMALL></CODE> is fastest. To
speed up other queries, replace <CODE>IN</CODE> with
<CODE>EXISTS</CODE>:</P>
-<PRE>
-<CODE>SELECT *
+<PRE> SELECT *
FROM tab
- WHERE col IN (SELECT subcol FROM subtab)
-</CODE>
+ WHERE col IN (SELECT subcol FROM subtab);
</PRE>
- to:
-<PRE>
-<CODE>SELECT *
+ to:
+<PRE> SELECT *
FROM tab
- WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col)
-</CODE>
+ WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col);
</PRE>
For this to be fast, <CODE>subcol</CODE> should be an indexed column.
- We hope to fix this limitation in a future release.
+ <P>In version 7.4 and later, <CODE>IN</CODE> actually uses the same
+ sophisticated join techniques as normal queries, and is prefered
+ to using <CODE>EXISTS</CODE>.
<H4><A name="4.23">4.23</A>) How do I perform an outer join?</H4>
Because PostgreSQL loads database-specific system catalogs, it is
uncertain how a cross-database query should even behave.</P>
- <P><I>/contrib/dblink</I> allows cross-database queries using
+ <P><I>contrib/dblink</I> allows cross-database queries using
function calls. Of course, a client can make simultaneous
connections to different databases and merge the results on the
client side.</P>
<H4><A name="4.25">4.25</A>) How do I return multiple rows or
columns from a function?</H4>
- <P>You can return result sets from PL/pgSQL functions using
- <I>refcursors</I>. See <A href=
- "http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html">
- http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html,</A>
- section 23.7.3.3.</P>
+ <P>In 7.3, you can easily return multiple rows or columns from a
+ function,
+ <a href="http://techdocs.postgresql.org/guides/SetReturningFunctions">
+ http://techdocs.postgresql.org/guides/SetReturningFunctions</a>.
<H4><A name="4.26">4.26</A>) Why can't I reliably create/drop
temporary tables in PL/PgSQL functions?</H4>
<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.27">4.27</A>) What replication options are available?
- </H4>
- <P>There are several master/slave replication solutions available.
- These allow only the master to make database changes and the slave
- can only do database reads. The bottom of <a
- href="http://gborg.postgresql.org/genpage?replication_research">
- http://gborg.postgresql.org/genpage?replication_research</a> lists
- them. A multi-master replication solution is being worked on at <a
- href="http://gborg.postgresql.org/project/pgreplication/projdisplay.
- php">http://gborg.postgresql.org/project/pgreplication/projdisplay.php</a>.</P>
-
<H4><A name="4.27">4.27</A>) What encryption options are available?
</H4>
<UL>
- <LI><I>/contrib/pgcrypto</I> contains many encryption functions for
+ <LI><I>contrib/pgcrypto</I> contains many encryption functions for
use in <SMALL>SQL</SMALL> queries.</LI>
- <LI>The only way to encrypt transmission from the client to the
- server is by using <I>hostssl</I> in <I>pg_hba.conf</I>.</LI>
- <LI>Database user passwords are automatically encrypted when stored
- in version 7.3. In previous versions, you must enable
- <I>password_encryption</I> in <I>postgresql.conf</I>.</LI>
+ <LI>To encrypt transmission from the client to the server, the server
+ must have the <I>ssl</I> option set to <I>true</I> in <I>postgresql.conf,
+ </I> and an applicable <I>host</I> or <I>hostssl</I> record must exist in
+ <I>pg_hba.conf</I>, and the client <I>sslmode</I> must not be
+ <I>disable.</I> (Note that it is also possible to use a third-party
+ 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>
+ <LI>The server can run using an encrypted file system.</LI>
</UL>
<HR>
<H4><A name="5.3">5.3</A>) How do I write a C function to return a
tuple?</H4>
- <P>This requires wizardry so extreme that the authors have never
- tried it, though in principle it can be done.</P>
+ <P>In versions of PostgreSQL beginning with 7.3, table-returning
+ functions are fully supported in C, PL/PgSQL, and SQL. See the
+ Programmer's Guide for more information. An example of a
+ table-returning function defined in C can be found in
+ <I>contrib/tablefunc</I>.</P>
<H4><A name="5.4">5.4</A>) I have changed a source file. Why does
the recompile not see the change?</H4>
projects / postgresql / blobdiff