FAQ.html 62.8 KB
Newer Older
1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
Bruce Momjian's avatar
Bruce Momjian committed
2
<HTML>
Bruce Momjian's avatar
Bruce Momjian committed
3 4
  <HEAD>
    <META name="generator" content="HTML Tidy, see www.w3.org">
Bruce Momjian's avatar
Bruce Momjian committed
5
    <META http-equiv="Content-Type" content="text/html; charset=US-ASCII">
Bruce Momjian's avatar
Bruce Momjian committed
6 7 8
    <TITLE>PostgreSQL FAQ</TITLE>
  </HEAD>

Bruce Momjian's avatar
Bruce Momjian committed
9 10
  <BODY bgcolor="#ffffff" text="#000000" link="#ff0000" vlink="#a00000"
  alink="#0000ff">
11
    <H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
Bruce Momjian's avatar
Bruce Momjian committed
12

Bruce Momjian's avatar
Bruce Momjian committed
13
    <P>Last updated: Thu May  6 10:22:34 EDT 2004</P>
Bruce Momjian's avatar
Bruce Momjian committed
14

Bruce Momjian's avatar
Bruce Momjian committed
15 16
    <P>Current maintainer: Bruce Momjian (<A href=
    "mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
Bruce Momjian's avatar
Bruce Momjian committed
17 18
    </P>

19
    <P>The most recent version of this document can be viewed at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
20
    "http://www.PostgreSQL.org/docs/faqs/FAQ.html">http://www.PostgreSQL.org/docs/faqs/FAQ.html</A>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
21

Bruce Momjian's avatar
Bruce Momjian committed
22
    <P>Platform-specific questions are answered at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
23
    "http://www.PostgreSQL.org/docs/index.html">http://www.PostgreSQL.org/docs/index.html</A>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
24 25
    <HR>

Bruce Momjian's avatar
Bruce Momjian committed
26
    <H2 align="center">General Questions</H2>
27
    <A href="#1.1">1.1</A>) What is PostgreSQL? How is it pronounced?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
28
     <A href="#1.2">1.2</A>) What is the copyright on PostgreSQL?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
29 30
     <A href="#1.3">1.3</A>) What Unix platforms does PostgreSQL run
    on?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
31
     <A href="#1.4">1.4</A>) What non-Unix ports are available?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
32 33 34 35
     <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>
Bruce Momjian's avatar
Bruce Momjian committed
36 37
     <A href="#1.9">1.9</A>) How do I find out about known bugs or
    missing features?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
38
     <A href="#1.10">1.10</A>) How can I learn <SMALL>SQL</SMALL>?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
39 40 41
     <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>
Bruce Momjian's avatar
Bruce Momjian committed
42 43 44 45
     <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>
Bruce Momjian's avatar
Bruce Momjian committed
46 47
     

Bruce Momjian's avatar
Bruce Momjian committed
48
    <H2 align="center">User Client Questions</H2>
Bruce Momjian's avatar
Bruce Momjian committed
49 50 51 52 53 54 55 56
    <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?<BR>
     <A href="#2.4">2.4</A>) What languages are available to
    communicate with PostgreSQL?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
57 58
     

Bruce Momjian's avatar
Bruce Momjian committed
59
    <H2 align="center">Administrative Questions</H2>
Bruce Momjian's avatar
Bruce Momjian committed
60 61 62 63 64 65 66 67
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
68 69
     <A href="#3.5">3.5</A>) How do I control connections from other
    hosts?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
70
     <A href="#3.6">3.6</A>) How do I tune the database engine for
Bruce Momjian's avatar
Bruce Momjian committed
71
    better performance?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
72 73
     <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
Bruce Momjian's avatar
Bruce Momjian committed
74
    clients"</I> when trying to connect?<BR>
75 76
     <A href="#3.9">3.9</A>) What is in the  <I>pgsql_tmp</I>
    directory?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
77
     <A href="#3.10">3.10</A>) Why do I need to do a dump and restore
78
    to upgrade PostgreSQL releases?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
79

Bruce Momjian's avatar
Bruce Momjian committed
80

Bruce Momjian's avatar
Bruce Momjian committed
81
    <H2 align="center">Operational Questions</H2>
Bruce Momjian's avatar
Bruce Momjian committed
82 83 84
    <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
85
    first few rows of a query?  A random row?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
86 87 88
     <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
89
    table, or change it's data type?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
90 91 92 93
     <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>
94 95
     <A href="#4.7">4.7</A>) How do I find out what tables, indexes,
    databases, and users are defined?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
96 97 98 99
     <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>
Bruce Momjian's avatar
Bruce Momjian committed
100 101
     <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>
Bruce Momjian's avatar
Bruce Momjian committed
102 103 104 105 106 107 108 109 110 111 112 113 114
     <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>
Bruce Momjian's avatar
Bruce Momjian committed
115 116 117
     <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>
Bruce Momjian's avatar
Bruce Momjian committed
118 119 120 121 122 123 124 125 126 127 128 129 130 131
     <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>
Bruce Momjian's avatar
Bruce Momjian committed
132
     <A href="#4.23">4.23</A>) How do I perform an outer join?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
133 134
     <A href="#4.24">4.24</A>) How do I perform queries using multiple
    databases?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
135
     <A href="#4.25">4.25</A>) How do I return multiple rows or columns
136 137 138
    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>
139
     <A href="#4.27">4.27</A>) What replication options are available?<BR>
140
     <A href="#4.28">4.28</A>) What encryption options are available?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
141 142
     

Bruce Momjian's avatar
Bruce Momjian committed
143
    <H2 align="center">Extending PostgreSQL</H2>
Bruce Momjian's avatar
Bruce Momjian committed
144 145 146 147 148 149 150 151
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
152 153 154
     
    <HR>

Bruce Momjian's avatar
Bruce Momjian committed
155
    <H2 align="center">General Questions</H2>
Bruce Momjian's avatar
Bruce Momjian committed
156

157
    <H4><A name="1.1">1.1</A>) What is PostgreSQL? How is it pronounced?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
158

Bruce Momjian's avatar
Bruce Momjian committed
159 160 161 162
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
163

Bruce Momjian's avatar
Bruce Momjian committed
164
    <P>PostgreSQL is an enhancement of the POSTGRES database management
Bruce Momjian's avatar
Bruce Momjian committed
165 166
    system (and is still sometimes reffered to as simply "Postgres"), 
    a next-generation <SMALL>DBMS</SMALL> research prototype.
Bruce Momjian's avatar
Bruce Momjian committed
167 168 169 170 171
    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>

172
    <P>PostgreSQL development is performed by a team of
Bruce Momjian's avatar
Bruce Momjian committed
173 174 175
    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
176
    section <a href="#1.6">1.6</a> on how to join). This team is now
177
    responsible for all development of PostgreSQL.  It is a community
178
    project and is not controlled by any company.  To get involved, see
Bruce Momjian's avatar
Bruce Momjian committed
179
    the developer's FAQ at <A href=
180 181
    "http://www.PostgreSQL.org/docs/faqs/FAQ_DEV.html">http://www.PostgreSQL.org/docs/faqs/FAQ_DEV.html</A>
    </P>
Bruce Momjian's avatar
Bruce Momjian committed
182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197

    <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>
Bruce Momjian's avatar
Bruce Momjian committed
198 199 200 201 202

    <P>PostgreSQL is subject to the following COPYRIGHT:</P>

    <P>PostgreSQL Data Base Management System</P>

Bruce Momjian's avatar
Bruce Momjian committed
203
    <P>Portions copyright (c) 1996-2004, PostgreSQL Global Development
Bruce Momjian's avatar
Bruce Momjian committed
204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225
    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>

Bruce Momjian's avatar
Bruce Momjian committed
226 227 228
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
229

Bruce Momjian's avatar
Bruce Momjian committed
230 231 232
    <H4><A name="1.3">1.3</A>) What Unix platforms does PostgreSQL run
    on?</H4>

Bruce Momjian's avatar
Bruce Momjian committed
233
    <P>In general, any modern Unix-compatible platform should be able to
Bruce Momjian's avatar
Bruce Momjian committed
234 235 236
    run PostgreSQL. The platforms that had received explicit testing at
    the time of release are listed in the installation
    instructions.</P>
Bruce Momjian's avatar
Bruce Momjian committed
237 238 239 240 241

    <H4><A name="1.4">1.4</A>) What non-Unix ports are available?</H4>

    <P><STRONG>Client</STRONG></P>

Bruce Momjian's avatar
Bruce Momjian committed
242
    <P>It is possible to compile the <I>libpq</I> C library, psql, and
243 244
    other interfaces and client applications to run on MS Windows platforms.
    In this case, the client is running on MS Windows, and communicates
Bruce Momjian's avatar
Bruce Momjian committed
245
    via TCP/IP to a server running on one of our supported Unix
246
    platforms. A file <I>win32.mak</I> is included in the distribution
Bruce Momjian's avatar
Bruce Momjian committed
247 248
    for making a Win32 <I>libpq</I> library and <I>psql</I>. PostgreSQL
    also communicates with <SMALL>ODBC</SMALL> clients.</P>
Bruce Momjian's avatar
Bruce Momjian committed
249 250 251

    <P><STRONG>Server</STRONG></P>

Bruce Momjian's avatar
Bruce Momjian committed
252 253
    <P>The database server can run on Windows NT and Win2k using
    Cygwin, the Cygnus Unix/NT porting library. See
254
    <I>pgsql/doc/FAQ_MSWIN</I> in the distribution or the MS Windows FAQ
Bruce Momjian's avatar
Bruce Momjian committed
255 256
    at <A href="http://www.PostgreSQL.org/docs/faqs/text/FAQ_MSWIN">
    http://www.PostgreSQL.org/docs/faqs/text/FAQ_MSWIN</A>.</P>
257

258
    <p>A native port to MS Win NT/2000/XP is currently being worked
Bruce Momjian's avatar
Bruce Momjian committed
259 260
    on. For more details on the current status of PostgreSQL on Windows see
    <a href="http://techdocs.postgresql.org/guides/Windows">
Bruce Momjian's avatar
Bruce Momjian committed
261
    http://techdocs.postgresql.org/guides/Windows</a> and
262
    <a href="http://momjian.postgresql.org/main/writings/pgsql/win32.html">
Bruce Momjian's avatar
Bruce Momjian committed
263
    http://momjian.postgresql.org/main/writings/pgsql/win32.html</a>.</p>
Bruce Momjian's avatar
Bruce Momjian committed
264 265 266

    <p>There is also a Novell Netware 6 port at
    <a href="http://forge.novell.com">http://forge.novell.com</a>.</p>
267

Bruce Momjian's avatar
Bruce Momjian committed
268
    <H4><A name="1.5">1.5</A>) Where can I get PostgreSQL?</H4>
269

Bruce Momjian's avatar
Bruce Momjian committed
270 271 272
    <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>
273

Bruce Momjian's avatar
Bruce Momjian committed
274
    <H4><A name="1.6">1.6</A>) Where can I get support?</H4>
275

Bruce Momjian's avatar
Bruce Momjian committed
276 277 278 279 280
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
281 282 283 284
<PRE>
    subscribe
    end
</PRE>
285

Bruce Momjian's avatar
Bruce Momjian committed
286 287
    <P>to <A href=
    "mailto:pgsql-general-request@PostgreSQL.org">pgsql-general-request@PostgreSQL.org</A>.</P>
288

Bruce Momjian's avatar
Bruce Momjian committed
289 290 291 292
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
293 294 295 296
<PRE>
    subscribe
    end
</PRE>
297

Bruce Momjian's avatar
Bruce Momjian committed
298 299
    Digests are sent out to members of this list whenever the main list
    has received around 30k of messages. 
300

Bruce Momjian's avatar
Bruce Momjian committed
301 302 303 304
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
305 306 307 308
<PRE>
    subscribe
    end
</PRE>
309

Bruce Momjian's avatar
Bruce Momjian committed
310 311 312 313
    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: 
Bruce Momjian's avatar
Bruce Momjian committed
314 315 316 317
<PRE>
    subscribe
    end
</PRE>
318

Bruce Momjian's avatar
Bruce Momjian committed
319 320
    <P>Additional mailing lists and information about PostgreSQL can be
    found via the PostgreSQL WWW home page at:</P>
321

Bruce Momjian's avatar
Bruce Momjian committed
322 323 324
    <BLOCKQUOTE>
      <A href="http://www.PostgreSQL.org">http://www.PostgreSQL.org</A>
    </BLOCKQUOTE>
325

326
    <P>There is also an IRC channel on Freenode and EFNet,
Bruce Momjian's avatar
Bruce Momjian committed
327 328 329
    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>
330

Bruce Momjian's avatar
Bruce Momjian committed
331
    <P>A list of commercial support companies is available at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
332
    "http://techdocs.postgresql.org/companies.php">http://techdocs.postgresql.org/companies.php</A>.</P>
333

Bruce Momjian's avatar
Bruce Momjian committed
334
    <H4><A name="1.7">1.7</A>) What is the latest release?</H4>
335

336
    <P>The latest release of PostgreSQL is version 7.4.2.</P>
337

Bruce Momjian's avatar
Bruce Momjian committed
338
    <P>We plan to have major releases every six to eight months.</P>
339

Bruce Momjian's avatar
Bruce Momjian committed
340
    <H4><A name="1.8">1.8</A>) What documentation is available?</H4>
341

Bruce Momjian's avatar
Bruce Momjian committed
342 343
    <P>Several manuals, manual pages, and some small test examples are
    included in the distribution. See the <I>/doc</I> directory. You
Bruce Momjian's avatar
Bruce Momjian committed
344 345
    can also browse the manuals online at <A href=
    "http://www.PostgreSQL.org/docs">http://www.PostgreSQL.org/docs</A>.</P>
346

Bruce Momjian's avatar
Bruce Momjian committed
347 348 349 350
    <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>.
Bruce Momjian's avatar
Bruce Momjian committed
351 352
    There is a list of PostgreSQL books available for purchase at <A
    href=
Bruce Momjian's avatar
Bruce Momjian committed
353
    "http://techdocs.postgresql.org/techdocs/bookreviews.php">http://techdocs.PostgreSQL.org/techdocs/bookreviews.php</A>.
Bruce Momjian's avatar
Bruce Momjian committed
354 355
    There is also a collection of PostgreSQL technical articles at <A
    href=
356
    "http://techdocs.PostgreSQL.org/">http://techdocs.PostgreSQL.org/</A>.</P>
357

Bruce Momjian's avatar
Bruce Momjian committed
358 359
    <P><I>psql</I> has some nice \d commands to show information about
    types, operators, functions, aggregates, etc.</P>
360

Bruce Momjian's avatar
Bruce Momjian committed
361
    <P>Our web site contains even more documentation.</P>
Bruce Momjian's avatar
Bruce Momjian committed
362

Bruce Momjian's avatar
Bruce Momjian committed
363 364
    <H4><A name="1.9">1.9</A>) How do I find out about known bugs or
    missing features?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
365

Bruce Momjian's avatar
Bruce Momjian committed
366 367 368
    <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>
369

Bruce Momjian's avatar
Bruce Momjian committed
370 371
    <H4><A name="1.10">1.10</A>) How can I learn
    <SMALL>SQL</SMALL>?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
372

Bruce Momjian's avatar
Bruce Momjian committed
373 374
    <P>The PostgreSQL book at <A href=
    "http://www.PostgreSQL.org/docs/awbook.html">http://www.PostgreSQL.org/docs/awbook.html</A>
Bruce Momjian's avatar
Bruce Momjian committed
375 376 377
    teaches <SMALL>SQL</SMALL>. There is another PostgreSQL book at <A
    href=
    "http://www.commandprompt.com/ppbook/">http://www.commandprompt.com/ppbook.</A>
Bruce Momjian's avatar
Bruce Momjian committed
378 379 380
    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=
Bruce Momjian's avatar
Bruce Momjian committed
381
    "http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM">
Bruce Momjian's avatar
Bruce Momjian committed
382 383 384
    http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM,</A>
    and at <A href=
    "http://sqlcourse.com/">http://sqlcourse.com.</A></P>
Bruce Momjian's avatar
Bruce Momjian committed
385

Bruce Momjian's avatar
Bruce Momjian committed
386 387 388
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
389

Bruce Momjian's avatar
Bruce Momjian committed
390 391 392
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
393 394 395

    <H4><A name="1.11">1.11</A>) Is PostgreSQL Y2K compliant?</H4>

Bruce Momjian's avatar
Bruce Momjian committed
396 397
    <P>Yes, we easily handle dates past the year 2000 AD, and before
    2000 BC.</P>
Bruce Momjian's avatar
Bruce Momjian committed
398

Bruce Momjian's avatar
Bruce Momjian committed
399 400
    <H4><A name="1.12">1.12</A>) How do I join the development
    team?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
401

Bruce Momjian's avatar
Bruce Momjian committed
402 403 404 405 406
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
407

Bruce Momjian's avatar
Bruce Momjian committed
408 409 410 411 412
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
413 414 415

    <H4><A name="1.13">1.13</A>) How do I submit a bug report?</H4>

416 417 418 419
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
420

Bruce Momjian's avatar
Bruce Momjian committed
421 422 423
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
424

Bruce Momjian's avatar
Bruce Momjian committed
425 426
    <H4><A name="1.14">1.14</A>) How does PostgreSQL compare to other
    <SMALL>DBMS</SMALL>s?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
427

Bruce Momjian's avatar
Bruce Momjian committed
428 429
    <P>There are several ways of measuring software: features,
    performance, reliability, support, and price.</P>
Bruce Momjian's avatar
Bruce Momjian committed
430 431 432 433

    <DL>
      <DT><B>Features</B></DT>

Bruce Momjian's avatar
Bruce Momjian committed
434 435 436 437 438 439
      <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>
Bruce Momjian's avatar
Bruce Momjian committed
440 441 442 443 444
      <BR>
      </DD>

      <DT><B>Performance</B></DT>

Bruce Momjian's avatar
Bruce Momjian committed
445 446
      <DD>PostgreSQL has performance similar to other commercial and
      open source databases. it is faster for some things, slower for
Bruce Momjian's avatar
Bruce Momjian committed
447
      others. In comparison to MySQL or leaner database systems, we are
Bruce Momjian's avatar
Bruce Momjian committed
448
      faster for multiple users, complex queries, and a read/write query
Bruce Momjian's avatar
Bruce Momjian committed
449
      load.  MySQL is faster for simple SELECT queries done by a few users.
Bruce Momjian's avatar
Bruce Momjian committed
450
      Of course, MySQL does not have most of the features mentioned in the
Bruce Momjian's avatar
Bruce Momjian committed
451
      <I>Features</I> section above. We are built for reliability and
Bruce Momjian's avatar
Bruce Momjian committed
452
      features, and we continue to improve performance in every
Bruce Momjian's avatar
Bruce Momjian committed
453
      release. There is an interesting Web page comparing PostgreSQL to
Bruce Momjian's avatar
Bruce Momjian committed
454
      MySQL at <A href="http://openacs.org/philosophy/why-not-mysql.html">
Bruce Momjian's avatar
Bruce Momjian committed
455
      http://openacs.org/philosophy/why-not-mysql.html</A>  Also, MySQL is
456 457
      is a company that distributes its products via open source, and requires
      a commercial license for close-source software, not an
Bruce Momjian's avatar
Bruce Momjian committed
458
      open source development community like PostgreSQL.<BR>
Bruce Momjian's avatar
Bruce Momjian committed
459

Bruce Momjian's avatar
Bruce Momjian committed
460 461 462 463 464
      <BR>
      </DD>

      <DT><B>Reliability</B></DT>

Bruce Momjian's avatar
Bruce Momjian committed
465 466 467 468 469 470 471
      <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>
Bruce Momjian's avatar
Bruce Momjian committed
472 473 474 475 476
      <BR>
      </DD>

      <DT><B>Support</B></DT>

477 478
      <DD>Our mailing lists provide contact with a large group of developers
      and users to help resolve any problems encountered. While we cannot
Bruce Momjian's avatar
Bruce Momjian committed
479 480 481 482 483
      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.
484
      (See <A href="#1.6">FAQ section 1.6</A>.)<BR>
Bruce Momjian's avatar
Bruce Momjian committed
485 486 487 488 489
      <BR>
      </DD>

      <DT><B>Price</B></DT>

Bruce Momjian's avatar
Bruce Momjian committed
490 491 492
      <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>
Bruce Momjian's avatar
Bruce Momjian committed
493 494 495
      <BR>
      </DD>
    </DL>
Bruce Momjian's avatar
Bruce Momjian committed
496

Bruce Momjian's avatar
Bruce Momjian committed
497 498
    <H4><A name="1.15">1.15</A>) How can I financially assist
    PostgreSQL?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
499

Bruce Momjian's avatar
Bruce Momjian committed
500
    <P>PostgreSQL has had a first-class infrastructure since we started
501
    in 1996. This is all thanks to Marc Fournier, who has created
Bruce Momjian's avatar
Bruce Momjian committed
502
    and managed this infrastructure over the years.</P>
Bruce Momjian's avatar
Bruce Momjian committed
503

Bruce Momjian's avatar
Bruce Momjian committed
504 505 506
    <P>Quality infrastructure is very important to an open-source
    project. It prevents disruptions that can greatly delay forward
    movement of the project.</P>
Bruce Momjian's avatar
Bruce Momjian committed
507

Bruce Momjian's avatar
Bruce Momjian committed
508 509 510
    <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
511
    this effort, please go to <A href="http://store.pgsql.com/shopping/">http://store.pgsql.com/shopping/</A>
Bruce Momjian's avatar
Bruce Momjian committed
512
    and make a donation.</P>
Bruce Momjian's avatar
Bruce Momjian committed
513

Bruce Momjian's avatar
Bruce Momjian committed
514 515 516 517
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
518
    <HR>
519

520 521
    <P>Also, if you have a success story about PostgreSQL, please submit
    it to our advocacy site at <a href="http://advocacy.postgresql.org">
Bruce Momjian's avatar
Bruce Momjian committed
522
    http://advocacy.postgresql.org</a>.</P>
523 524


Bruce Momjian's avatar
Bruce Momjian committed
525
    <H2 align="center">User Client Questions</H2>
526

Bruce Momjian's avatar
Bruce Momjian committed
527 528
    <H4><A name="2.1">2.1</A>) Are there <SMALL>ODBC</SMALL> drivers
    for PostgreSQL?</H4>
529

Bruce Momjian's avatar
Bruce Momjian committed
530 531
    <P>There are two <SMALL>ODBC</SMALL> drivers available, PsqlODBC
    and OpenLink <SMALL>ODBC</SMALL>.</P>
532

533 534 535
    <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>
536

Bruce Momjian's avatar
Bruce Momjian committed
537 538 539 540 541
    <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>
542

Bruce Momjian's avatar
Bruce Momjian committed
543 544 545 546
    <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>
547

Bruce Momjian's avatar
Bruce Momjian committed
548 549
    <H4><A name="2.2">2.2</A>) What tools are available for using
    PostgreSQL with Web pages?</H4>
550

Bruce Momjian's avatar
Bruce Momjian committed
551 552
    <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>
553

Bruce Momjian's avatar
Bruce Momjian committed
554 555
    <P>For Web integration, PHP is an excellent interface. It is at <A
    href="http://www.php.net">http://www.php.net</A>.</P>
556

557
    <P>For complex cases, many use the Perl interface and CGI.pm or mod_perl.</P>
558

Bruce Momjian's avatar
Bruce Momjian committed
559 560
    <H4><A name="2.3">2.3</A>) Does PostgreSQL have a graphical user
    interface?</H4>
561

562
    <P>Yes, there are several graphical interfaces to PostgreSQL available.
Bruce Momjian's avatar
Bruce Momjian committed
563
    These include PgAccess <a href="http://www.pgaccess.org">
Bruce Momjian's avatar
Bruce Momjian committed
564
    http://www.pgaccess.org</a>), PgAdmin III (<a
565
    href="http://www.pgadmin.org">http://www.pgadmin.org</a>, RHDB Admin (<a
566 567 568
    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 
Bruce Momjian's avatar
Bruce Momjian committed
569
    also PhpPgAdmin (<a href="http://phppgadmin.sourceforge.net/">
570
    http://phppgadmin.sourceforge.net/ </a>), a web-based interface to 
571 572 573
    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>
574 575 576

    <H4><A name="2.4">2.4</A>) What languages are able to communicate with 
    PostgreSQL?</H4>
577

578 579 580
    <P>Most popular programming languages contain an interface to
    PostgreSQL. Check your programming language's list of extension
    modules.</P>
581

582 583
    <P>The following interfaces are included in the PostgreSQL
    distribution:</P>
584

Bruce Momjian's avatar
Bruce Momjian committed
585 586
    <UL>
      <LI>C (libpq)</LI>
587

Bruce Momjian's avatar
Bruce Momjian committed
588
      <LI>Embedded C (ecpg)</LI>
589

Bruce Momjian's avatar
Bruce Momjian committed
590
      <LI>Java (jdbc)</LI>
591

Bruce Momjian's avatar
Bruce Momjian committed
592
      <LI>Python (PyGreSQL)</LI>
593

Bruce Momjian's avatar
Bruce Momjian committed
594
      <LI>TCL (libpgtcl)</LI>
595

Bruce Momjian's avatar
Bruce Momjian committed
596
    </UL>
597
    <P>Additional interfaces are available at 
598 599
    <a href="http://gborg.postgresql.org">http://gborg.postgresql.org</A>
    in the <I>Drivers/Interfaces</I> section.
Bruce Momjian's avatar
Bruce Momjian committed
600
    </P>
Bruce Momjian's avatar
Bruce Momjian committed
601
    <HR>
602

Bruce Momjian's avatar
Bruce Momjian committed
603 604
    <H2 align="center">Administrative Questions</H2>

Bruce Momjian's avatar
Bruce Momjian committed
605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627
    <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=
Bruce Momjian's avatar
Bruce Momjian committed
628
    "http://www.PostgreSQL.org/docs/view.php?version=current&amp;idoc=1&amp;file=kernel-resources.html">PostgreSQL
Bruce Momjian's avatar
Bruce Momjian committed
629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651
    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>

Bruce Momjian's avatar
Bruce Momjian committed
652 653
    <H4><A name="3.5">3.5</A>) How do I control connections from other
    hosts?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
654 655 656

    <P>By default, PostgreSQL only allows connections from the local
    machine using Unix domain sockets. Other machines will not be able
Bruce Momjian's avatar
Bruce Momjian committed
657
    to connect unless you turn on tcpip_sockets in the postgresql.conf  
Bruce Momjian's avatar
Bruce Momjian committed
658 659 660 661
    <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>

Bruce Momjian's avatar
Bruce Momjian committed
662
    <H4><A name="3.6">3.6</A>) How do I tune the database engine for
Bruce Momjian's avatar
Bruce Momjian committed
663 664 665
    better performance?</H4>

    <P>Certainly, indexes can speed up queries. The
Bruce Momjian's avatar
Bruce Momjian committed
666
    <SMALL>EXPLAIN ANALYZE</SMALL> command allows you to see how PostgreSQL is
Bruce Momjian's avatar
Bruce Momjian committed
667 668 669 670
    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
Bruce Momjian's avatar
Bruce Momjian committed
671
    is much faster than individual <SMALL>INSERTS</SMALL>. Second,
Bruce Momjian's avatar
Bruce Momjian committed
672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698
    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>

Bruce Momjian's avatar
Bruce Momjian committed
699
    <H4><A name="3.7">3.7</A>) What debugging features are
Bruce Momjian's avatar
Bruce Momjian committed
700 701 702 703 704 705 706 707 708 709 710 711 712
    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>
713
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
714 715 716
    cd /usr/local/pgsql
    ./bin/postmaster &gt;server.log 2&gt;&amp;1 &amp;
</PRE>
717

Bruce Momjian's avatar
Bruce Momjian committed
718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738
    <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
Bruce Momjian's avatar
Bruce Momjian committed
739
    <I>postgres</I> <SMALL>PID</SMALL>. You can set breakpoints in the
Bruce Momjian's avatar
Bruce Momjian committed
740 741 742 743 744 745 746 747 748 749 750 751 752
    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
753
    file will be put in the client's current directory. Linux requires
754
    a compile with <I>-DLINUX_PROFILE</I> for proper profiling.</P>
Bruce Momjian's avatar
Bruce Momjian committed
755

Bruce Momjian's avatar
Bruce Momjian committed
756
    <H4><A name="3.8">3.8</A>) Why do I get <I>"Sorry, too many
Bruce Momjian's avatar
Bruce Momjian committed
757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776
    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
Bruce Momjian's avatar
Bruce Momjian committed
777
    <SMALL>NINODE</SMALL>. The reason that PostgreSQL has a limit on
Bruce Momjian's avatar
Bruce Momjian committed
778 779 780
    the number of allowed backend processes is so your system won't run
    out of resources.</P>

Bruce Momjian's avatar
Bruce Momjian committed
781
    <H4><A name="3.9">3.9</A>) What is in the <I>pgsql_tmp</I> directory?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
782

Bruce Momjian's avatar
Bruce Momjian committed
783 784 785 786 787
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
788

789 790 791
    <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>
792

793 794 795 796 797
    <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.
798 799 800 801 802
    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>
803

804 805 806 807
    <P>In releases where the on-disk format does not change, the
    <I>pg_upgrade</I> script can be used to upgrade without a dump/restore.
    The release notes mention whether <I>pg_upgrade</I> is available for the
    release.</P>
808

Bruce Momjian's avatar
Bruce Momjian committed
809 810
    <HR>

Bruce Momjian's avatar
Bruce Momjian committed
811
    <H2 align="center">Operational Questions</H2>
Bruce Momjian's avatar
Bruce Momjian committed
812

Bruce Momjian's avatar
Bruce Momjian committed
813 814
    <H4><A name="4.1">4.1</A>) What is the difference between binary
    cursors and normal cursors?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
815

Bruce Momjian's avatar
Bruce Momjian committed
816 817
    <P>See the <SMALL>DECLARE</SMALL> manual page for a
    description.</P>
Bruce Momjian's avatar
Bruce Momjian committed
818

Bruce Momjian's avatar
Bruce Momjian committed
819
    <H4><A name="4.2">4.2</A>) How do I <SMALL>SELECT</SMALL> only the
820
    first few rows of a query?  A random row?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
821

Bruce Momjian's avatar
Bruce Momjian committed
822 823
    <P>See the <SMALL>FETCH</SMALL> manual page, or use
    <SMALL>SELECT</SMALL> ... <SMALL>LIMIT</SMALL>....</P>
Bruce Momjian's avatar
Bruce Momjian committed
824

Bruce Momjian's avatar
Bruce Momjian committed
825
    <P>The entire query may have to be evaluated, even if you only want
826
    the first few rows. Consider using a query that has an <SMALL>ORDER
Bruce Momjian's avatar
Bruce Momjian committed
827
    BY</SMALL>. If there is an index that matches the <SMALL>ORDER
Bruce Momjian's avatar
Bruce Momjian committed
828 829 830
    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>
Bruce Momjian's avatar
Bruce Momjian committed
831

832 833 834 835 836 837 838 839
    <P>To <SMALL>SELECT</SMALL> a random row, use:
<PRE>
    SELECT col
    FROM tab
    ORDER BY random()
    LIMIT 1;
</PRE>

Bruce Momjian's avatar
Bruce Momjian committed
840 841
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
842

Bruce Momjian's avatar
Bruce Momjian committed
843 844 845 846 847 848 849 850 851
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
852

Bruce Momjian's avatar
Bruce Momjian committed
853
    <H4><A name="4.4">4.4</A>) How do you remove a column from a
854
    table, or change its data type?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
855

856
    <P><SMALL>DROP COLUMN</SMALL> functionality was added in release 7.3 with
857 858
    <SMALL>ALTER TABLE DROP COLUMN</SMALL>.  In earlier versions,
    you can do this:</P>
859
<PRE>
860 861
    BEGIN;
    LOCK TABLE old_table;
Bruce Momjian's avatar
Bruce Momjian committed
862 863 864 865 866
    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;
867
    COMMIT;
Bruce Momjian's avatar
Bruce Momjian committed
868
</PRE>
869

870 871 872 873 874
    <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>);
Bruce Momjian's avatar
Bruce Momjian committed
875
    ALTER TABLE tab DROP COLUMN old_col;
876 877 878 879 880
    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>
    
Bruce Momjian's avatar
Bruce Momjian committed
881 882
    <H4><A name="4.5">4.5</A>) What is the maximum size for a row, a
    table, and a database?</H4>
883

Bruce Momjian's avatar
Bruce Momjian committed
884
    <P>These are the limits:</P>
885
<PRE>
886
    Maximum size for a database?             unlimited (32 TB databases exist)
887
    Maximum size for a table?                32 TB
Bruce Momjian's avatar
Bruce Momjian committed
888 889
    Maximum size for a row?                  1.6TB
    Maximum size for a field?                1 GB
Bruce Momjian's avatar
Bruce Momjian committed
890 891 892
    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
893
</PRE>
894

Bruce Momjian's avatar
Bruce Momjian committed
895 896 897
    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. 
898

899
    <P>The maximum table size of 32 TB does not require large file
Bruce Momjian's avatar
Bruce Momjian committed
900 901 902
    support from the operating system. Large tables are stored as
    multiple 1 GB files so file system size limits are not
    important.</P>
Bruce Momjian's avatar
Bruce Momjian committed
903

Bruce Momjian's avatar
Bruce Momjian committed
904
    <P>The maximum table size and maximum number of columns can be
905
    quadrupled by increasing the default block size to 32k.</P>
Bruce Momjian's avatar
Bruce Momjian committed
906

Bruce Momjian's avatar
Bruce Momjian committed
907 908
    <H4><A name="4.6">4.6</A>) How much database disk space is required
    to store data from a typical text file?</H4>
909

Bruce Momjian's avatar
Bruce Momjian committed
910 911
    <P>A PostgreSQL database may require up to five times the disk
    space to store data from a text file.</P>
912

Bruce Momjian's avatar
Bruce Momjian committed
913
    <P>As an example, consider a file of 100,000 lines with an integer
Bruce Momjian's avatar
Bruce Momjian committed
914 915 916 917
    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>
918 919
<PRE>
    36 bytes: each row header (approximate)
920
    24 bytes: one int field and one text field
921 922
   + 4 bytes: pointer on page to tuple
   ----------------------------------------
Bruce Momjian's avatar
Bruce Momjian committed
923
    64 bytes per row
924 925 926 927

   The data page size in PostgreSQL is 8192 bytes (8 KB), so:

   8192 bytes per page
Bruce Momjian's avatar
Bruce Momjian committed
928 929
   -------------------   =  128 rows per database page (rounded down)
     64 bytes per row
930

Bruce Momjian's avatar
Bruce Momjian committed
931
   100000 data rows
Bruce Momjian's avatar
Bruce Momjian committed
932 933
   --------------------  =  782 database pages (rounded up)
      128 rows per page
934

Bruce Momjian's avatar
Bruce Momjian committed
935
782 database pages * 8192 bytes per page  =  6,406,144 bytes (6.4 MB)
Bruce Momjian's avatar
Bruce Momjian committed
936
</PRE>
937

Bruce Momjian's avatar
Bruce Momjian committed
938 939
    <P>Indexes do not require as much overhead, but do contain the data
    that is being indexed, so they can be large also.</P>
Bruce Momjian's avatar
Bruce Momjian committed
940

941
    <P><SMALL>NULL</SMALL>s are stored as bitmaps, so they
Bruce Momjian's avatar
Bruce Momjian committed
942 943
    use very little space.</P>
    
944 945
    <H4><A name="4.7">4.7</A>) How do I find out what tables, indexes,
    databases, and users are defined?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
946

Bruce Momjian's avatar
Bruce Momjian committed
947
    <P><I>psql</I> has a variety of backslash commands to show such
948 949 950
    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>
Bruce Momjian's avatar
Bruce Momjian committed
951

Bruce Momjian's avatar
Bruce Momjian committed
952 953 954
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
955

Bruce Momjian's avatar
Bruce Momjian committed
956 957
    <H4><A name="4.8">4.8</A>) My queries are slow or don't make use of
    the indexes. Why?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
958
    Indexes are not automatically used by every query. Indexes are only
Bruce Momjian's avatar
Bruce Momjian committed
959
    used if the table is larger than a minimum size, and the query
Bruce Momjian's avatar
Bruce Momjian committed
960
    selects only a small percentage of the rows in the table. This is
961
    because the random disk access caused by an index scan can be
Bruce Momjian's avatar
Bruce Momjian committed
962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977
    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
978 979 980 981 982 983
    is returned.  In fact, though MAX() and MIN() don't use indexes,
    it is possible to retrieve such values using an index with ORDER BY
    and LIMIT:
<PRE>
    SELECT col
    FROM tab
Bruce Momjian's avatar
Bruce Momjian committed
984
    ORDER BY col [ DESC ]
985
    LIMIT 1;
986
</PRE>
Bruce Momjian's avatar
Bruce Momjian committed
987

Bruce Momjian's avatar
Bruce Momjian committed
988 989 990 991
    <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>

Bruce Momjian's avatar
Bruce Momjian committed
992
    <P>When using wild-card operators such as <SMALL>LIKE</SMALL> or
Bruce Momjian's avatar
Bruce Momjian committed
993
    <I>~</I>, indexes can only be used in certain circumstances:</P>
994 995
    <UL>
    <LI>The beginning of the search string must be anchored to the start
Bruce Momjian's avatar
Bruce Momjian committed
996
    of the string, i.e.
997
    <UL>
Bruce Momjian's avatar
Bruce Momjian committed
998
    <LI><SMALL>LIKE</SMALL> patterns must not start with <I>%</I>.</LI>
Bruce Momjian's avatar
Bruce Momjian committed
999
    <LI><I>~</I> (regular expression) patterns must start with
Bruce Momjian's avatar
Bruce Momjian committed
1000 1001
    <I>^</I>.</LI>
    </UL></LI>
1002 1003
    <LI>The search string can not start with a character class,
    e.g. [a-e].</LI>
1004 1005 1006 1007
    <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
Bruce Momjian's avatar
Bruce Momjian committed
1008
    <i>initdb</i>.</LI>
1009 1010
    </UL>
    <P>
Bruce Momjian's avatar
Bruce Momjian committed
1011

Bruce Momjian's avatar
Bruce Momjian committed
1012 1013
    <H4><A name="4.9">4.9</A>) How do I see how the query optimizer is
    evaluating my query?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1014 1015 1016

    <P>See the <SMALL>EXPLAIN</SMALL> manual page.</P>

Bruce Momjian's avatar
Bruce Momjian committed
1017
    <H4><A name="4.10">4.10</A>) What is an R-tree index?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1018

Bruce Momjian's avatar
Bruce Momjian committed
1019 1020 1021 1022 1023 1024 1025
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
1026

Bruce Momjian's avatar
Bruce Momjian committed
1027 1028
    <P>The canonical paper that describes the original R-tree design
    is:</P>
Bruce Momjian's avatar
Bruce Momjian committed
1029

Bruce Momjian's avatar
Bruce Momjian committed
1030 1031 1032
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
1033

Bruce Momjian's avatar
Bruce Momjian committed
1034 1035
    <P>You can also find this paper in Stonebraker's "Readings in
    Database Systems".</P>
Bruce Momjian's avatar
Bruce Momjian committed
1036

Bruce Momjian's avatar
Bruce Momjian committed
1037 1038 1039 1040
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
1041

Bruce Momjian's avatar
Bruce Momjian committed
1042 1043
    <H4><A name="4.11">4.11</A>) What is the Genetic Query
    Optimizer?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1044

Bruce Momjian's avatar
Bruce Momjian committed
1045 1046 1047 1048
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
1049

Bruce Momjian's avatar
Bruce Momjian committed
1050 1051 1052
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
1053

Bruce Momjian's avatar
Bruce Momjian committed
1054 1055 1056
    <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
1057
    <SMALL>ILIKE</SMALL>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1058

Bruce Momjian's avatar
Bruce Momjian committed
1059 1060
    <P>Case-insensitive equality comparisons are normally expressed
    as:</P>
Bruce Momjian's avatar
Bruce Momjian committed
1061
<PRE>
1062 1063
    SELECT *
    FROM tab
1064
    WHERE lower(col) = 'abc';
Bruce Momjian's avatar
Bruce Momjian committed
1065
</PRE>
1066

Bruce Momjian's avatar
Bruce Momjian committed
1067 1068
    This will not use an standard index. However, if you create a
    functional index, it will be used: 
Bruce Momjian's avatar
Bruce Momjian committed
1069
<PRE>
1070
    CREATE INDEX tabindex ON tab (lower(col));
Bruce Momjian's avatar
Bruce Momjian committed
1071
</PRE>
1072

Bruce Momjian's avatar
Bruce Momjian committed
1073 1074
    <H4><A name="4.13">4.13</A>) In a query, how do I detect if a field
    is <SMALL>NULL</SMALL>?</H4>
1075

Bruce Momjian's avatar
Bruce Momjian committed
1076 1077
    <P>You test the column with <SMALL>IS NULL</SMALL> and <SMALL>IS
    NOT NULL</SMALL>.</P>
1078

Bruce Momjian's avatar
Bruce Momjian committed
1079 1080
    <H4><A name="4.14">4.14</A>) What is the difference between the
    various character types?</H4>
1081 1082 1083
<PRE>
Type            Internal Name   Notes
--------------------------------------------------
1084
VARCHAR(n)      varchar         size specifies maximum length, no padding
Bruce Momjian's avatar
Bruce Momjian committed
1085
CHAR(n)         bpchar          blank padded to the specified fixed length
1086
TEXT            text            no specific upper limit on length
Bruce Momjian's avatar
Bruce Momjian committed
1087
BYTEA           bytea           variable-length byte array (null-byte safe)
1088
"char"          char            one character
Bruce Momjian's avatar
Bruce Momjian committed
1089
</PRE>
1090

Bruce Momjian's avatar
Bruce Momjian committed
1091 1092 1093
    <P>You will see the internal name when examining system catalogs
    and in some error messages.</P>

1094
    <P>The first four types above are "varlena" types (i.e., the first
Bruce Momjian's avatar
Bruce Momjian committed
1095 1096 1097 1098 1099 1100
    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>

Bruce Momjian's avatar
Bruce Momjian committed
1101 1102 1103 1104 1105 1106 1107
    <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,
1108 1109
    particularly values that include <SMALL>NULL</SMALL> bytes. All the
    types described here have similar performance characteristics.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1110 1111 1112 1113 1114

    <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
1115
    auto-creates a sequence.  For example,
Bruce Momjian's avatar
Bruce Momjian committed
1116
    this:</P>
1117
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1118 1119 1120 1121
    CREATE TABLE person ( 
        id   SERIAL, 
        name TEXT 
    );
1122
</PRE>
1123

Bruce Momjian's avatar
Bruce Momjian committed
1124
    is automatically translated into this: 
1125
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1126 1127 1128 1129 1130
    CREATE SEQUENCE person_id_seq;
    CREATE TABLE person ( 
        id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),
        name TEXT 
    );
1131
</PRE>
1132

Bruce Momjian's avatar
Bruce Momjian committed
1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144
    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
1145 1146
    example table in <A href="#4.15.1">4.15.1</A>, an example in a
    pseudo-language would look like this:</P>
1147
<PRE>
1148 1149
    new_id = execute("SELECT nextval('person_id_seq')");
    execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
1150
</PRE>
1151

Bruce Momjian's avatar
Bruce Momjian committed
1152 1153 1154 1155 1156 1157
    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
    &lt;<I>table</I>&gt;_&lt;<I>serialcolumn</I>&gt;_<I>seq</I>, where
    <I>table</I> and <I>serialcolumn</I> are the names of your table
1158
    and your <SMALL>SERIAL</SMALL> column, respectively.
Bruce Momjian's avatar
Bruce Momjian committed
1159 1160

    <P>Alternatively, you could retrieve the assigned
1161
    <SMALL>SERIAL</SMALL> value with the <I>currval()</I> function
Bruce Momjian's avatar
Bruce Momjian committed
1162
    <I>after</I> it was inserted by default, e.g.,</P>
1163
<PRE>
1164 1165
    execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
    new_id = execute("SELECT currval('person_id_seq')");
1166
</PRE>
1167

Bruce Momjian's avatar
Bruce Momjian committed
1168 1169
    Finally, you could use the <A href="#4.16"><SMALL>OID</SMALL></A>
    returned from the <SMALL>INSERT</SMALL> statement to look up the
1170 1171
    default value, though this is probably the least portable approach,
    and the oid value will wrap around when it reaches 4 billion.
Bruce Momjian's avatar
Bruce Momjian committed
1172 1173
    In Perl, using DBI with Edmund Mergl's DBD::Pg module, the oid
    value is made available via <I>$sth-&gt;{pg_oid_status}</I> after
1174
    <I>$sth-&gt;execute()</I>.
Bruce Momjian's avatar
Bruce Momjian committed
1175 1176 1177 1178

    <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>

1179
    <P>No. <I>currval()</I> returns the current value assigned by your
Bruce Momjian's avatar
Bruce Momjian committed
1180 1181
    backend, not by all users.</P>

Bruce Momjian's avatar
Bruce Momjian committed
1182 1183 1184
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
1185 1186

    <P>To improve concurrency, sequence values are given out to running
Bruce Momjian's avatar
Bruce Momjian committed
1187
    transactions as needed and are not locked until the transaction
Bruce Momjian's avatar
Bruce Momjian committed
1188 1189
    completes. This causes gaps in numbering from aborted
    transactions.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1190

Bruce Momjian's avatar
Bruce Momjian committed
1191 1192 1193 1194 1195 1196 1197
    <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
1198
    <I>include/access/transam.h</I>). All user-created
Bruce Momjian's avatar
Bruce Momjian committed
1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214
    <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>
1215 1216
<PRE>
        CREATE TABLE new_table(old_oid oid, mycol int);
Bruce Momjian's avatar
Bruce Momjian committed
1217
        SELECT old_oid, mycol INTO new FROM old;
1218 1219 1220
        COPY new TO '/tmp/pgtable';
        DELETE FROM new;
        COPY new WITH OIDS FROM '/tmp/pgtable';
1221
</PRE>
1222
<!--
Bruce Momjian's avatar
Bruce Momjian committed
1223 1224
    CREATE TABLE new_table (mycol int);
    INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM old_table;
1225
-->
Bruce Momjian's avatar
Bruce Momjian committed
1226 1227 1228
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
1229

Bruce Momjian's avatar
Bruce Momjian committed
1230 1231 1232 1233
    <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>
1234

Bruce Momjian's avatar
Bruce Momjian committed
1235 1236
    <H4><A name="4.17">4.17</A>) What is the meaning of some of the
    terms used in PostgreSQL?</H4>
1237

Bruce Momjian's avatar
Bruce Momjian committed
1238 1239
    <P>Some of the source code and older documentation use terms that
    have more common usage. Here are some:</P>
1240

Bruce Momjian's avatar
Bruce Momjian committed
1241 1242
    <UL>
      <LI>table, relation, class</LI>
1243

Bruce Momjian's avatar
Bruce Momjian committed
1244
      <LI>row, record, tuple</LI>
1245

Bruce Momjian's avatar
Bruce Momjian committed
1246
      <LI>column, field, attribute</LI>
Bruce Momjian's avatar
Bruce Momjian committed
1247

Bruce Momjian's avatar
Bruce Momjian committed
1248
      <LI>retrieve, select</LI>
1249

Bruce Momjian's avatar
Bruce Momjian committed
1250
      <LI>replace, update</LI>
1251

Bruce Momjian's avatar
Bruce Momjian committed
1252 1253 1254 1255 1256 1257 1258 1259 1260
      <LI>append, insert</LI>

      <LI><SMALL>OID</SMALL>, serial value</LI>

      <LI>portal, cursor</LI>

      <LI>range variable, table name, table alias</LI>
    </UL>

Bruce Momjian's avatar
Bruce Momjian committed
1261
    <P>A list of general database terms can be found at: <A href=
1262
    "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>
1263

Bruce Momjian's avatar
Bruce Momjian committed
1264 1265
    <H4><A name="4.18">4.18</A>) Why do I get the error <I>"ERROR:
    Memory exhausted in AllocSetAlloc()"</I>?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1266

1267 1268 1269
    <P>You probably 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>
Bruce Momjian's avatar
Bruce Momjian committed
1270 1271 1272 1273
<PRE>
    ulimit -d 262144
    limit datasize 256m
</PRE>
1274

Bruce Momjian's avatar
Bruce Momjian committed
1275 1276 1277 1278 1279 1280
    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
1281
    client.
Bruce Momjian's avatar
Bruce Momjian committed
1282

Bruce Momjian's avatar
Bruce Momjian committed
1283 1284
    <H4><A name="4.19">4.19</A>) How do I tell what PostgreSQL version
    I am running?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1285

1286
    <P>From <I>psql</I>, type <CODE>SELECT version();</CODE></P>
Bruce Momjian's avatar
Bruce Momjian committed
1287

Bruce Momjian's avatar
Bruce Momjian committed
1288 1289
    <H4><A name="4.20">4.20</A>) Why does my large-object operations
    get <I>"invalid large obj descriptor"</I>?</H4>
1290

Bruce Momjian's avatar
Bruce Momjian committed
1291 1292 1293
    <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>
1294

Bruce Momjian's avatar
Bruce Momjian committed
1295 1296 1297 1298 1299
    <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>
1300

Bruce Momjian's avatar
Bruce Momjian committed
1301 1302
    <P>If you are using a client interface like <SMALL>ODBC</SMALL> you
    may need to set <CODE>auto-commit off.</CODE></P>
Bruce Momjian's avatar
Bruce Momjian committed
1303

Bruce Momjian's avatar
Bruce Momjian committed
1304 1305
    <H4><A name="4.21">4.21</A>) How do I create a column that will
    default to the current time?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1306

Bruce Momjian's avatar
Bruce Momjian committed
1307
    <P>Use <I>CURRENT_TIMESTAMP</I>:</P>
Bruce Momjian's avatar
Bruce Momjian committed
1308
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1309
<CODE>CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
Bruce Momjian's avatar
Bruce Momjian committed
1310 1311
</CODE>
</PRE>
1312

Bruce Momjian's avatar
Bruce Momjian committed
1313 1314
    <H4><A name="4.22">4.22</A>) Why are my subqueries using
    <CODE><SMALL>IN</SMALL></CODE> so slow?</H4>
1315

1316 1317 1318 1319
    <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
Bruce Momjian's avatar
Bruce Momjian committed
1320
    speed up other queries, replace <CODE>IN</CODE> with
Bruce Momjian's avatar
Bruce Momjian committed
1321
    <CODE>EXISTS</CODE>:</P>
1322
<PRE>    SELECT *
Bruce Momjian's avatar
Bruce Momjian committed
1323
    FROM tab
1324
    WHERE col IN (SELECT subcol FROM subtab);
Bruce Momjian's avatar
Bruce Momjian committed
1325
</PRE>
1326 1327
    to:
<PRE>    SELECT *
Bruce Momjian's avatar
Bruce Momjian committed
1328
    FROM tab
1329
    WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col);
Bruce Momjian's avatar
Bruce Momjian committed
1330
</PRE>
1331

Bruce Momjian's avatar
Bruce Momjian committed
1332
    For this to be fast, <CODE>subcol</CODE> should be an indexed column.
1333 1334 1335
    <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>.
1336

Bruce Momjian's avatar
Bruce Momjian committed
1337
    <H4><A name="4.23">4.23</A>) How do I perform an outer join?</H4>
1338

1339 1340
    <P>PostgreSQL supports outer joins using the SQL standard syntax.
    Here are two examples:</P>
Bruce Momjian's avatar
Bruce Momjian committed
1341 1342 1343 1344
<PRE>
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
</PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1345
    or 
Bruce Momjian's avatar
Bruce Momjian committed
1346 1347 1348 1349
<PRE>
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 USING (col);
</PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1350

Bruce Momjian's avatar
Bruce Momjian committed
1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
1364
    <BR>
Bruce Momjian's avatar
Bruce Momjian committed
1365
    </P>
Bruce Momjian's avatar
Bruce Momjian committed
1366 1367 1368 1369 1370 1371 1372 1373
<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)
Bruce Momjian's avatar
Bruce Momjian committed
1374
    ORDER BY col1
Bruce Momjian's avatar
Bruce Momjian committed
1375
</PRE>
1376

Bruce Momjian's avatar
Bruce Momjian committed
1377 1378
    <H4><A name="4.24">4.24</A>) How do I perform queries using
    multiple databases?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1379

1380
    <P>There is no way to query a database other than the current one.
Bruce Momjian's avatar
Bruce Momjian committed
1381 1382
    Because PostgreSQL loads database-specific system catalogs, it is
    uncertain how a cross-database query should even behave.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1383

Bruce Momjian's avatar
Bruce Momjian committed
1384
    <P><I>contrib/dblink</I> allows cross-database queries using
1385 1386 1387
    function calls. Of course, a client can make simultaneous
    connections to different databases and merge the results on the
    client side.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1388

1389 1390 1391
    <H4><A name="4.25">4.25</A>) How do I return multiple rows or
    columns from a function?</H4>

Bruce Momjian's avatar
Bruce Momjian committed
1392 1393 1394 1395
    <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>.
1396

Bruce Momjian's avatar
Bruce Momjian committed
1397
    <H4><A name="4.26">4.26</A>) Why can't I reliably create/drop
1398
    temporary tables in PL/PgSQL functions?</H4>
1399
    <P>PL/PgSQL caches function contents, and an unfortunate side effect
1400 1401 1402 1403 1404
    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
1405 1406 1407 1408
    will cause the query to be reparsed every time.</P>

    <H4><A name="4.27">4.27</A>) What replication options are available?
    </H4>
Bruce Momjian's avatar
Bruce Momjian committed
1409
    <P>There are several master/slave replication options available.
1410 1411
    These allow only the master to make database changes and the slave
    can only do database reads. The bottom of <a
1412 1413
    href="http://gborg.PostgreSQL.org/genpage?replication_research">
    http://gborg.PostgreSQL.org/genpage?replication_research</a> lists
1414
    them. A multi-master replication solution is being worked on at <a
1415
    href="http://gborg.PostgreSQL.org/project/pgreplication/projdisplay.php">http://gborg.PostgreSQL.org/project/pgreplication/projdisplay.php</a>.</P>
1416

1417
    <H4><A name="4.28">4.28</A>) What encryption options are available?
1418 1419
    </H4>
    <UL>
Bruce Momjian's avatar
Bruce Momjian committed
1420
    <LI><I>contrib/pgcrypto</I> contains many encryption functions for
1421
    use in <SMALL>SQL</SMALL> queries.</LI>
1422 1423 1424 1425 1426 1427 1428 1429 1430
    <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
Bruce Momjian's avatar
Bruce Momjian committed
1431
    <I>PASSWORD_ENCRYPTION</I> in <I>postgresql.conf</I>.</LI>
Bruce Momjian's avatar
Bruce Momjian committed
1432
    <LI>The server can run using an encrypted file system.</LI>
1433
    </UL>
Bruce Momjian's avatar
Bruce Momjian committed
1434

1435 1436
    <HR>

Bruce Momjian's avatar
Bruce Momjian committed
1437
    <H2 align="center">Extending PostgreSQL</H2>
1438

Bruce Momjian's avatar
Bruce Momjian committed
1439 1440
    <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>
1441

Bruce Momjian's avatar
Bruce Momjian committed
1442 1443
    <P>The problem could be a number of things. Try testing your
    user-defined function in a stand-alone test program first.</P>
1444

Bruce Momjian's avatar
Bruce Momjian committed
1445 1446
    <H4><A name="5.2">5.2</A>) How can I contribute some nifty new
    types and functions to PostgreSQL?</H4>
1447

Bruce Momjian's avatar
Bruce Momjian committed
1448 1449 1450
    <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>
1451

Bruce Momjian's avatar
Bruce Momjian committed
1452 1453
    <H4><A name="5.3">5.3</A>) How do I write a C function to return a
    tuple?</H4>
1454

1455 1456 1457 1458
    <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
Bruce Momjian's avatar
Bruce Momjian committed
1459
    <I>contrib/tablefunc</I>.</P>
1460

Bruce Momjian's avatar
Bruce Momjian committed
1461 1462
    <H4><A name="5.4">5.4</A>) I have changed a source file. Why does
    the recompile not see the change?</H4>
1463

Bruce Momjian's avatar
Bruce Momjian committed
1464 1465 1466 1467 1468
    <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>
Bruce Momjian's avatar
Bruce Momjian committed
1469
  </BODY>
1470
</HTML>
Bruce Momjian's avatar
Bruce Momjian committed
1471