diff options
Diffstat (limited to 'doc/src/FAQ/FAQ.html')
| -rw-r--r-- | doc/src/FAQ/FAQ.html | 1349 |
1 files changed, 0 insertions, 1349 deletions
diff --git a/doc/src/FAQ/FAQ.html b/doc/src/FAQ/FAQ.html deleted file mode 100644 index 32762e38ea2..00000000000 --- a/doc/src/FAQ/FAQ.html +++ /dev/null @@ -1,1349 +0,0 @@ -<!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"> - - <TITLE>PostgreSQL FAQ</TITLE> - </HEAD> - - <BODY bgcolor="#ffffff" text="#000000" link="#ff0000" vlink="#a00000" - alink="#0000ff"> - <H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1> - - <P>Last updated: Tue Jun 11 06:36:10 EDT 2002</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>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> - <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.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="#1.4">1.4</A>) What non-Unix ports are available?<BR> - <A href="#1.5">1.5</A>) Where can I get PostgreSQL?<BR> - <A href="#1.6">1.6</A>) Where can I get support?<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.10">1.10</A>) How can I learn <SMALL>SQL</SMALL>?<BR> - <A href="#1.11">1.11</A>) Is PostgreSQL Y2K compliant?<BR> - <A href="#1.12">1.12</A>) How do I join the development team?<BR> - <A href="#1.13">1.13</A>) How do I submit a bug report?<BR> - <A href="#1.14">1.14</A>) How does PostgreSQL compare to other - <SMALL>DBMS</SMALL>s?<BR> - <A href="#1.15">1.15</A>) How can I financially assist - PostgreSQL?<BR> - - - <H2 align="center">User Client Questions</H2> - <A href="#2.1">2.1</A>) Are there <SMALL>ODBC</SMALL> drivers for - PostgreSQL?<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> - - - <H2 align="center">Administrative Questions</H2> - <A href="#3.1">3.1</A>) How do I install PostgreSQL somewhere other - than <I>/usr/local/pgsql</I>?<BR> - <A href="#3.2">3.2</A>) When I start <I>postmaster</I>, I get a - <I>Bad System Call</I> or core dumped message. Why?<BR> - <A href="#3.3">3.3</A>) When I try to start <I>postmaster</I>, I - get <I>IpcMemoryCreate</I> errors. Why?<BR> - <A href="#3.4">3.4</A>) When I try to start <I>postmaster</I>, I - get <I>IpcSemaphoreCreate</I> errors. Why?<BR> - <A href="#3.5">3.5</A>) How do I control connections from other - hosts?<BR> - <A href="#3.6">3.6</A>) How do I tune the database engine for - better performance?<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> - - - <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> - <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> - <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 - to store data from a typical text file?<BR> - <A href="#4.7">4.7</A>) How do I find out what tables, indexes, - databases, and users are defined?<BR> - <A href="#4.8">4.8</A>) My queries are slow or don't make use of - the indexes. Why?<BR> - <A href="#4.9">4.9</A>) How do I see how the query optimizer is - evaluating my query?<BR> - <A href="#4.10">4.10</A>) What is an R-tree index?<BR> - <A href="#4.11">4.11</A>) What is the Genetic Query Optimizer?<BR> - <A href="#4.12">4.12</A>) How do I perform regular expression - searches and case-insensitive regular expression searches? How do I - use an index for case-insensitive searches?<BR> - <A href="#4.13">4.13</A>) In a query, how do I detect if a field - is <SMALL>NULL</SMALL>?<BR> - <A href="#4.14">4.14</A>) What is the difference between the - various character types?<BR> - <A href="#4.15.1">4.15.1</A>) How do I create a - serial/auto-incrementing field?<BR> - <A href="#4.15.2">4.15.2</A>) How do I get the value of a - <SMALL>SERIAL</SMALL> insert?<BR> - <A href="#4.15.3">4.15.3</A>) Don't <I>currval()</I> and - <I>nextval()</I> lead to a race condition with other users?<BR> - <A href="#4.15.4">4.15.4</A>) Why aren't my sequence numbers - reused on transaction abort? Why are there gaps in the numbering of - my sequence/SERIAL column?<BR> - <A href="#4.16">4.16</A>) What is an <SMALL>OID</SMALL>? What is a - <SMALL>TID</SMALL>?<BR> - <A href="#4.17">4.17</A>) What is the meaning of some of the terms - used in PostgreSQL?<BR> - <A href="#4.18">4.18</A>) Why do I get the error <I>"ERROR: Memory - exhausted in AllocSetAlloc()"</I>?<BR> - <A href="#4.19">4.19</A>) How do I tell what PostgreSQL version I - am running?<BR> - <A href="#4.20">4.20</A>) Why does my large-object operations get - <I>"invalid large obj descriptor"</I>?<BR> - <A href="#4.21">4.21</A>) How do I create a column that will - default to the current time?<BR> - <A href="#4.22">4.22</A>) Why are my subqueries using - <CODE><SMALL>IN</SMALL></CODE> so slow?<BR> - <A href="#4.23">4.23</A>) How do I perform an outer join?<BR> - <A href="#4.24">4.24</A>) How do I perform queries using multiple - databases?<BR> - <A href="#4.25">4.25</A>) How do I return multiple rows or columns - 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> - - - <H2 align="center">Extending PostgreSQL</H2> - <A href="#5.1">5.1</A>) I wrote a user-defined function. When I run - it in <I>psql</I>, why does it dump core?<BR> - <A href="#5.2">5.2</A>) How can I contribute some nifty new types - and functions to PostgreSQL?<BR> - <A href="#5.3">5.3</A>) How do I write a C function to return a - tuple?<BR> - <A href="#5.4">5.4</A>) I have changed a source file. Why does the - recompile not see the change?<BR> - - <HR> - - <H2 align="center">General Questions</H2> - - <H4><A name="1.1">1.1</A>) What is PostgreSQL?</H4> - - <P>PostgreSQL is pronounced <I>Post-Gres-Q-L</I>.</P> - - <P>PostgreSQL is an enhancement of the POSTGRES database management - system, 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 - 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> - - <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 - PostgreSQL?</H4> - - <P>PostgreSQL is subject to the following COPYRIGHT:</P> - - <P>PostgreSQL Data Base Management System</P> - - <P>Portions copyright (c) 1996-2002, PostgreSQL Global Development - Group Portions Copyright (c) 1994-6 Regents of the University of - California</P> - - <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> - - <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> - - <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> - - <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 Unix platforms does PostgreSQL run - on?</H4> - - <P>In general, a 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><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> - - <H4><A name="1.5">1.5</A>) Where can I get PostgreSQL?</H4> - - <P>The primary anonymous ftp site for PostgreSQL is <A href= - "ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub</A>. - For mirror sites, see our main web site.</P> - - <H4><A name="1.6">1.6</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>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>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> - - <H4><A name="1.7">1.7</A>) What is the latest release?</H4> - - <P>The latest release of PostgreSQL is version 7.2.1.</P> - - <P>We plan to have major releases every four 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> - - <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> - and <A href= - "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>. - There is also a collection of PostgreSQL technical articles at <A - href= - "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> - - <P>Our web site contains even more documentation.</P> - - <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.10">1.10</A>) How can I learn - <SMALL>SQL</SMALL>?</H4> - - <P>The PostgreSQL book at <A href= - "http://www.PostgreSQL.org/docs/awbook.html">http://www.PostgreSQL.org/docs/awbook.html</A> - teaches <SMALL>SQL</SMALL>. There is another PostgreSQL book at <A - href= - "http://www.commandprompt.com/ppbook/">http://www.commandprompt.com/ppbook.</A> - There is a nice tutorial at <A href= - "http://www.intermedia.net/support/sql/sqltut.shtm">http://www.intermedia.net/support/sql/sqltut.shtm,</A> - at <A href= - "http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM"> - http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM,</A> - and at <A href= - "http://sqlcourse.com/">http://sqlcourse.com.</A></P> - - <P>Another one is "Teach Yourself SQL in 21 Days, Second Edition" - at <A href= - "http://members.tripod.com/er4ebus/sql/index.htm">http://members.tripod.com/er4ebus/sql/index.htm</A></P> - - <P>Many of our users like <I>The Practical SQL Handbook</I>, - 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.11">1.11</A>) Is PostgreSQL Y2K compliant?</H4> - - <P>Yes, we easily handle dates past the year 2000 AD, and before - 2000 BC.</P> - - <H4><A name="1.12">1.12</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 - <I>pgsql-patches</I> mailing lists. Third, submit high quality - patches to pgsql-patches.</P> - - <P>There are about a dozen people who have commit privileges to the - PostgreSQL <SMALL>CVS</SMALL> archive. They each have submitted so - many high-quality patches that it was impossible for the existing - committers to keep up, and we had confidence that patches they - committed were of high quality.</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>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.14">1.14</A>) How does PostgreSQL compare to other - <SMALL>DBMS</SMALL>s?</H4> - - <P>There are several ways of measuring software: features, - performance, reliability, support, and price.</P> - - <DL> - <DT><B>Features</B></DT> - - <DD>PostgreSQL has most features present in large commercial - <SMALL>DBMS</SMALL>s, like transactions, subselects, triggers, - views, foreign key referential integrity, and sophisticated - locking. We have some features they do not have, like - user-defined types, inheritance, rules, and multi-version - concurrency control to reduce lock contention.<BR> - <BR> - </DD> - - <DT><B>Performance</B></DT> - - <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 - <I>Features</I> section above. We are built for reliability and - features, though 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> - - <BR> - </DD> - - <DT><B>Reliability</B></DT> - - <DD>We realize that a <SMALL>DBMS</SMALL> must be reliable, or it - is worthless. We strive to release well-tested, stable code that - has a minimum of bugs. Each release has at least one month of - beta testing, and our release history shows that we can provide - stable, solid releases that are ready for production use. We - believe we compare favorably to other database software in this - area.<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 - 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> - <BR> - </DD> - - <DT><B>Price</B></DT> - - <DD>We are free for all use, both commercial and non-commercial. - You can add our code to your product with no limitations, except - those outlined in our BSD-style license stated above.<BR> - <BR> - </DD> - </DL> - - <H4><A name="1.15">1.15</A>) How can I financially assist - 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 - 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://www.pgsql.com/pg_goodies">http://www.pgsql.com/pg_goodies</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> - <HR> - - <H2 align="center">User Client Questions</H2> - - <H4><A name="2.1">2.1</A>) Are there <SMALL>ODBC</SMALL> drivers - for PostgreSQL?</H4> - - <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>OpenLink <SMALL>ODBC</SMALL> can be gotten from <A href= - "http://www.openlinksw.com/">http://www.openlinksw.com</A>. It - works with their standard <SMALL>ODBC</SMALL> client software so - you'll have PostgreSQL <SMALL>ODBC</SMALL> available on every - client platform they support (Win, Mac, Unix, VMS).</P> - - <P>They will probably be selling this product to people who need - commercial-quality support, but a freeware version will always be - 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> - - <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>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 available to - communicate with PostgreSQL?</H4> - - <P>We have:</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> - <HR> - - <H2 align="center">Administrative Questions</H2> - - <H4><A name="3.1">3.1</A>) How do I install PostgreSQL somewhere - other than <I>/usr/local/pgsql</I>?</H4> - - <P>Specify the <I>--prefix</I> option when running - <I>configure</I>.</P> - - <H4><A name="3.2">3.2</A>) When I start <I>postmaster</I>, I get a - <I>Bad System Call</I> or core dumped message. Why?</H4> - - <P>It could be a variety of problems, but first check to see that - you have System V extensions installed in your kernel. PostgreSQL - requires kernel support for shared memory and semaphores.</P> - - <H4><A name="3.3">3.3</A>) When I try to start <I>postmaster</I>, I - get <I>IpcMemoryCreate</I> errors. Why?</H4> - - <P>You either do not have shared memory configured properly in your - 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 and backend processes you configure for - <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 - Administrator's Guide</A> for more detailed information about - shared memory and semaphores.</P> - - <H4><A name="3.4">3.4</A>) When I try to start <I>postmaster</I>, I - get <I>IpcSemaphoreCreate</I> errors. Why?</H4> - - <P>If the error message is <I>IpcSemaphoreCreate: semget failed (No - space left on device)</I> then your kernel is not configured with - enough semaphores. Postgres needs one semaphore per potential - backend process. A temporary solution is to start <I>postmaster</I> - with a smaller limit on the number of backend processes. Use - <I>-N</I> with a parameter less than the default of 32. A more - permanent solution is to increase your kernel's - <SMALL>SEMMNS</SMALL> and <SMALL>SEMMNI</SMALL> parameters.</P> - - <P>Inoperative semaphores can also cause crashes during heavy - database access.</P> - - <P>If the error message is something else, you might not have - semaphore support configured in your kernel at all. See the - PostgreSQL Administrator's Guide for more detailed information - about shared memory and semaphores.</P> - - <H4><A name="3.5">3.5</A>) How do I control connections from other - 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> - - <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 - 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. You can disable <I>fsync()</I> - by starting <I>postmaster</I> with a <I>-o -F</I> option. This will - prevent <I>fsync()</I>s from flushing to disk after every - transaction.</P> - - <P>You can also use the <I>postmaster</I> <I>-B</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 64 buffers.</P> - - <P>You can also use the backend <I>-S</I> option to increase the - maximum amount of memory used by the backend process for temporary - sorts. The <I>-S</I> value is measured in kilobytes, and the - default is 512 (i.e. 512K).</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> - - <H4><A name="3.7">3.7</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>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>If <I>postmaster</I> is not running, you can actually run the - <I>postgres</I> backend from the command line, and type your - <SMALL>SQL</SMALL> statement directly. This is recommended - <B>only</B> 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 <I>postmaster</I>, it is not - running in an identical environment and locking/backend interaction - problems may not be duplicated.</P> - - <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 - 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 - so you can attach to the process with the debugger, set any - breakpoints, and continue through the startup sequence.</P> - - <P>The <I>postgres</I> program has <I>-s, -A</I>, and <I>-t</I> - options that 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 - file will be put in the client's current directory. Linux requires - a compile with <I>-DLINUX_PROFILE</I> for proper profiling.</P> - - <H4><A name="3.8">3.8</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>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> - - <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>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> - <HR> - - <H2 align="center">Operational Questions</H2> - - <H4><A name="4.1">4.1</A>) What is the difference between binary - cursors and normal cursors?</H4> - - <P>See the <SMALL>DECLARE</SMALL> manual page for a - 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> - - <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 - 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> - - <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> - - <H4><A name="4.4">4.4</A>) How do you remove a column from a - table?</H4> - - <P>We do not support <SMALL>ALTER TABLE DROP COLUMN,</SMALL> but 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.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 (500 GB databases exist) - Maximum size for a table? 16 TB - Maximum size for a row? unlimited in 7.1 and later - Maximum size for a field? 1 GB in 7.1 and later - 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 - 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 - 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> - - <H4><A name="4.6">4.6</A>) How much database disk space is required - to store data from a typical text file?</H4> - - <P>A PostgreSQL database may require up to five times the disk - space to store data from a text file.</P> - - <P>As an example, consider a file of 100,000 lines with an integer - and text description on each line. Suppose the text string - avergages twenty bytes in length. The flat file would be 2.8 MB. - 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 - + 4 bytes: pointer on page to tuple - ---------------------------------------- - 64 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 - - 100000 data rows - -------------------- = 782 database pages (rounded up) - 128 rows per page - -782 database pages * 8192 bytes per page = 6,406,144 bytes (6.4 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> - - <H4><A name="4.7">4.7</A>) How do I find out what tables, indexes, - databases, and users are defined?</H4> - - <P><I>psql</I> has a variety of backslash commands to show such - information. Use \? to see them. There are also system tables - beginning with <I>pg_</I> that describe these too. Also, <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.8">4.8</A>) My queries are slow or don't make use of - the indexes. Why?</H4> - Indexes are not automatically used by every query. Indexes are only - used if the table is larger than a minimum size, and the query - selects only a small percentage of the rows in the table. This is - because the random disk access caused by an index scan is sometimes - slower than a straight read through the table, or sequential scan. - - <P>To determine if an index should be used, PostgreSQL must have - statistics about the table. These statistics are collected using - <SMALL>VACUUM ANALYZE</SMALL>, or simply <SMALL>ANALYZE</SMALL>. - Using statistics, the optimizer knows how many rows are in the - table, and can better determine if indexes should be used. - Statistics are also valuable in determining optimal join order and - join methods. Statistics collection should be performed - periodically as the contents of the table change.</P> - - <P>Indexes are normally not used for <SMALL>ORDER BY</SMALL> or to - perform joins. A sequential scan followed by an explicit sort is - usually faster than an index scan of a large table.</P> - However, <SMALL>LIMIT</SMALL> combined with <SMALL>ORDER BY</SMALL> - often will use an index because only a small portion of the table - is returned. - - <P>When using wild-card operators such as <SMALL>LIKE</SMALL> or - <I>~</I>, indexes can only be used if the beginning of the search - is anchored to the start of the string. Therefore, to use indexes, - <SMALL>LIKE</SMALL> patterns must not start with <I>%</I>, and - <I>~</I>(regular expression) patterns must start with <I>^</I>.</P> - - <H4><A name="4.9">4.9</A>) How do I see how the query optimizer is - evaluating my query?</H4> - - <P>See the <SMALL>EXPLAIN</SMALL> manual page.</P> - - <H4><A name="4.10">4.10</A>) What is an R-tree index?</H4> - - <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-trees can handle - multi-dimensional data. For example, if an R-tree index can be - built on an attribute of type <I>point</I>, the system can more - efficiently answer queries such as "select all points within a - bounding rectangle."</P> - - <P>The canonical paper that describes the original R-tree design - is:</P> - - <P>Guttman, A. "R-trees: A Dynamic Index Structure for Spatial - Searching." Proceedings of the 1984 ACM SIGMOD Int'l Conf on Mgmt - of Data, 45-57.</P> - - <P>You can also find this paper in Stonebraker's "Readings in - Database Systems".</P> - - <P>Built-in 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 requires a bit of work and we don't - currently have any documentation on how to do it.</P> - - <H4><A name="4.11">4.11</A>) What is the Genetic Query - Optimizer?</H4> - - <P>The <SMALL>GEQO</SMALL> module speeds query optimization when - joining many tables by means of a Genetic Algorithm (GA). It allows - the handling of large join queries through nonexhaustive - search.</P> - - <H4><A name="4.12">4.12</A>) How do I perform regular expression - searches and case-insensitive regular expression searches? How do I - use an index for case-insensitive searches?</H4> - - <P>The <I>~</I> operator does regular expression matching, and - <I>~*</I> does case-insensitive regular expression matching. The - case-insensitive variant of <SMALL>LIKE</SMALL> is called - <SMALL>ILIKE</SMALL> in PostgreSQL 7.1 and later.</P> - - <P>Case-insensitive equality comparisons are normally expressed - as:</P> -<PRE> - SELECT * - FROM tab - 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)); - -</PRE> - - <H4><A name="4.13">4.13</A>) In a query, how do I detect if a field - is <SMALL>NULL</SMALL>?</H4> - - <P>You test the column with <SMALL>IS NULL</SMALL> and <SMALL>IS - NOT NULL</SMALL>.</P> - - <H4><A name="4.14">4.14</A>) What is the difference between the - various character types?</H4> -<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 -TEXT text no specific upper limit on length -BYTEA bytea variable-length byte array (null-byte safe) -</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 - 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> - - <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, - this:</P> -<PRE> - CREATE TABLE person ( - id SERIAL, - name TEXT - ); -</PRE> - is automatically translated into this: -<PRE> - CREATE SEQUENCE person_id_seq; - 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 - 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. - - <H4><A name="4.15.2">4.15.2</A>) How do I get the value of a - <SMALL>SERIAL</SMALL> insert?</H4> - - <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> -<PRE> - new_id = output of "SELECT nextval('person_id_seq')" - INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal'); -</PRE> - You would then also have the new value stored in - <CODE>new_id</CODE> for use in other queries (e.g., as a foreign - key to the <CODE>person</CODE> table). Note that the name of the - 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. - - <P>Alternatively, you could retrieve the assigned - <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')"; -</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. - 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>. - - <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 - backend, not by all users.</P> - - <H4><A name="4.15.4">4.15.4</A>) Why aren't my sequence numbers - reused on transaction abort? Why are there gaps in the numbering of - my sequence/SERIAL column?</H4> - - <P>To improve concurrency, sequence values are given out to running - transactions as needed and are not locked until the transaction - completes. This causes gaps in numbering from aborted - transactions.</P> - - <H4><A name="4.16">4.16</A>) What is an <SMALL>OID</SMALL>? What is - a <SMALL>TID</SMALL>?</H4> - - <P><SMALL>OID</SMALL>s are PostgreSQL's answer to unique row ids. - 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 - <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> - - <P>PostgreSQL uses <SMALL>OID</SMALL>s in its internal system - tables to link rows between tables. These <SMALL>OID</SMALL>s can - be used to identify specific user rows and used in joins. It is - recommended you use column type <SMALL>OID</SMALL> to store - <SMALL>OID</SMALL> values. You can create an index on the - <SMALL>OID</SMALL> field for faster access.</P> - - <P>O<SMALL>ID</SMALL>s are assigned to all new rows from a central - area that is used by all databases. If you want to change the - <SMALL>OID</SMALL> to something else, or if you want to make a copy - 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; ---> -</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> - - <P>T<SMALL>ID</SMALL>s are used to identify specific physical rows - with block and offset values. T<SMALL>ID</SMALL>s change after rows - are modified or reloaded. They are used by index entries to point - to physical rows.</P> - - <H4><A name="4.17">4.17</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://www.comptechnews.com/~reaster/dbdesign.html">http://www.comptechnews.com/~reaster/dbdesign.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> - - <P>If you are running a version older than 7.1, an upgrade may fix - the problem. Also it is possible you have run out of virtual memory - on your system, or your kernel has a low limit for certain - resources. Try this before starting <I>postmaster</I>:</P> -<PRE> - ulimit -d 262144 - limit datasize 256m -</PRE> - Depending on your shell, only one of these may succeed, but it will - set your process data segment limit much higher and perhaps allow - the query to complete. This command applies to the current process, - 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. - - <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> - - <H4><A name="4.20">4.20</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> - around any use of a large object handle, that is, surrounding - <CODE>lo_open</CODE> ... <CODE>lo_close.</CODE></P> - - <P>Currently PostgreSQL enforces the rule by closing large object - handles at transaction commit. So the first attempt to do anything - with the handle will draw <I>invalid large obj descriptor</I>. So - code that used to work (at least most of the time) will now - generate that error message if you fail to use a transaction.</P> - - <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.21">4.21</A>) How do I create a column that will - default to the current time?</H4> - - <P>Use <I>CURRENT_TIMESTAMP</I>:</P> -<PRE> -<CODE>CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP ); -</CODE> -</PRE> - - <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. A workaround is to replace <CODE>IN</CODE> with - <CODE>EXISTS</CODE>:</P> -<PRE> -<CODE>SELECT * - FROM tab - WHERE col1 IN (SELECT col2 FROM TAB2) -</CODE> -</PRE> - to: -<PRE> -<CODE>SELECT * - FROM tab - WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2) -</CODE> -</PRE> - We hope to fix this limitation in a future release. - - <H4><A name="4.23">4.23</A>) How do I perform an outer join?</H4> - - <P>PostgreSQL 7.1 and later supports outer joins using the SQL - standard syntax. Here are two examples:</P> -<PRE> - SELECT * - FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col); -</PRE> - or -<PRE> - SELECT * - FROM t1 LEFT OUTER JOIN t2 USING (col); -</PRE> - - <P>These identical queries join t1.col to t2.col, and also return - any unjoined rows in t1 (those with no match in t2). A - <SMALL>RIGHT</SMALL> join would add unjoined rows of t2. A - <SMALL>FULL</SMALL> join would return the matched rows plus all - unjoined rows from t1 and t2. The word <SMALL>OUTER</SMALL> is - optional and is assumed in <SMALL>LEFT</SMALL>, - <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.24">4.24</A>) How do I perform queries using - multiple databases?</H4> - - <P>There is no way to query any database except the current one. - Because PostgreSQL loads database-specific system catalogs, it is - uncertain how a cross-database query should even behave.</P> - - <P>Of course, a client can make simultaneous connections to - different databases and merge the information that way.</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> - - <H4><A name="4.26">4.26</A>) Why can't I reliably create/drop - temporary tables in PL/PgSQL functions?</H4> - PL/PgSQL caches function contents, 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 - <SMALL>EXECUTE</SMALL> for temporary table access in PL/PgSQL. This - will cause the query to be reparsed every time. - - <HR> - - <H2 align="center">Extending PostgreSQL</H2> - - <H4><A name="5.1">5.1</A>) I wrote a user-defined function. When I - run it in <I>psql</I>, why does it dump core?</H4> - - <P>The problem could be a number of things. Try testing your - user-defined function in a stand-alone test program first.</P> - - <H4><A name="5.2">5.2</A>) How can I contribute some nifty new - types and functions to PostgreSQL?</H4> - - <P>Send your extensions to the <I>pgsql-hackers</I> mailing list, - and they will eventually end up in the <I>contrib/</I> - subdirectory.</P> - - <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> - - <H4><A name="5.4">5.4</A>) I have changed a source file. Why does - the recompile not see the change?</H4> - - <P>The <I>Makefiles</I> do not have the proper dependencies for - include files. You have to do a <I>make clean</I> and then another - <I>make</I>. If you are using <SMALL>GCC</SMALL> you can use the - <I>--enable-depend</I> option of <I>configure</I> to have the - compiler compute the dependencies automatically.</P> - </BODY> -</HTML> - |