FAQ.html 62.3 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: Fri Dec 24 12:18:49 EST 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 21
    "http://www.postgresql.org/files/documentation/faqs/FAQ.html">
    http://www.postgresql.org/files/documentation/faqs/FAQ.html</A>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
22

Bruce Momjian's avatar
Bruce Momjian committed
23
    <P>Platform-specific questions are answered at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
24 25
    "http://www.postgresql.org/docs/faq/">
    http://www.postgresql.org/docs/faq/</A>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
26 27
    <HR>

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

Bruce Momjian's avatar
Bruce Momjian committed
50
    <H2 align="center">User Client Questions</H2>
Bruce Momjian's avatar
Bruce Momjian committed
51 52 53 54 55 56 57 58
    <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
59 60
     

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

Bruce Momjian's avatar
Bruce Momjian committed
83

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

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

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

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

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

Bruce Momjian's avatar
Bruce Momjian committed
166
    <P>PostgreSQL is an enhancement of the POSTGRES database management
Bruce Momjian's avatar
Bruce Momjian committed
167 168
    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
169 170 171 172 173
    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>

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

    <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
201 202 203 204 205

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

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

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

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

Bruce Momjian's avatar
Bruce Momjian committed
236
    <P>In general, any modern Unix-compatible platform should be able to
Bruce Momjian's avatar
Bruce Momjian committed
237 238 239
    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
240 241 242

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

243 244 245 246
    <P>Starting with version 8.0, PostgreSQL now runs natively on
    Microsoft Windows NT-based operating systems like Win2000, WinXP,
    and Win2003.  A prepackaged installer is available at <a href=
    "http://pgfoundry.org/projects/pginstaller">
247 248
    http://pgfoundry.org/projects/pginstaller</a>.  MSDOS-based versions
    of Windows (Win95, Win98, WinMe) can run PostgreSQL using Cygwin.
Bruce Momjian's avatar
Bruce Momjian committed
249 250 251

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

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

Bruce Momjian's avatar
Bruce Momjian committed
255 256 257
    <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>
258

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

Bruce Momjian's avatar
Bruce Momjian committed
261 262 263 264 265
    <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
266 267 268 269
<PRE>
    subscribe
    end
</PRE>
270

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

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

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

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

Bruce Momjian's avatar
Bruce Momjian committed
295 296 297 298
    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
299 300 301 302
<PRE>
    subscribe
    end
</PRE>
303

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

Bruce Momjian's avatar
Bruce Momjian committed
307 308 309
    <BLOCKQUOTE>
      <A href="http://www.PostgreSQL.org">http://www.PostgreSQL.org</A>
    </BLOCKQUOTE>
310

Bruce Momjian's avatar
Bruce Momjian committed
311 312 313
    <P>The major IRC channel is <I>#postgresql</I> on Freenode
    (<I>irc.freenode.net</I>).  To connect you can use the Unix
    command <CODE>irc -c '#postgresql' "$USER" irc.freenode.net</CODE>
314
    or use any of the other popular IRC clients.  A Spanish one also exists
Bruce Momjian's avatar
Bruce Momjian committed
315 316
    on the same network, (<I>#postgresql-es</I>), and a French one,
    (<I>#postgresqlfr</I>).  There is also a PostgreSQL channel on EFNet.
317

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

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

323
    <P>The latest release of PostgreSQL is version 7.4.6.</P>
324

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

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

Bruce Momjian's avatar
Bruce Momjian committed
329 330
    <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
331 332
    can also browse the manuals online at <A href=
    "http://www.PostgreSQL.org/docs">http://www.PostgreSQL.org/docs</A>.</P>
333

Bruce Momjian's avatar
Bruce Momjian committed
334 335 336 337
    <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
338 339
    There is a list of PostgreSQL books available for purchase at <A
    href=
Bruce Momjian's avatar
Bruce Momjian committed
340
    "http://techdocs.postgresql.org/techdocs/bookreviews.php">http://techdocs.PostgreSQL.org/techdocs/bookreviews.php</A>.
Bruce Momjian's avatar
Bruce Momjian committed
341 342
    There is also a collection of PostgreSQL technical articles at <A
    href=
343
    "http://techdocs.PostgreSQL.org/">http://techdocs.PostgreSQL.org/</A>.</P>
344

Bruce Momjian's avatar
Bruce Momjian committed
345 346 347
    <P>The command line client program <I>psql</I> has some \d commands to show
    information about types, operators, functions, aggregates, etc. - use \? to 
    display the available commands.</P>
348

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

Bruce Momjian's avatar
Bruce Momjian committed
351 352
    <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
353

Bruce Momjian's avatar
Bruce Momjian committed
354 355 356
    <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>
357

Bruce Momjian's avatar
Bruce Momjian committed
358 359
    <H4><A name="1.10">1.10</A>) How can I learn
    <SMALL>SQL</SMALL>?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
360

Bruce Momjian's avatar
Bruce Momjian committed
361 362
    <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
363 364 365
    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
366 367 368
    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
369
    "http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM">
Bruce Momjian's avatar
Bruce Momjian committed
370 371 372
    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
373

Bruce Momjian's avatar
Bruce Momjian committed
374 375 376
    <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
377

Bruce Momjian's avatar
Bruce Momjian committed
378 379 380
    <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
381 382 383

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

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

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

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

Bruce Momjian's avatar
Bruce Momjian committed
396 397 398 399 400
    <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
401 402 403

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

404
    <P>Visit the PostgreSQL bug form at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
405 406
    "http://www.postgresql.org/support/submitbug">
    http://www.postgresql.org/support/submitbug</A>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
407

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

Bruce Momjian's avatar
Bruce Momjian committed
412 413
    <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
414

Bruce Momjian's avatar
Bruce Momjian committed
415 416
    <P>There are several ways of measuring software: features,
    performance, reliability, support, and price.</P>
Bruce Momjian's avatar
Bruce Momjian committed
417 418 419 420

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

Bruce Momjian's avatar
Bruce Momjian committed
421 422 423 424 425 426
      <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
427 428 429 430 431
      <BR>
      </DD>

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

Bruce Momjian's avatar
Bruce Momjian committed
432 433
      <DD>PostgreSQL's performance is comparable to other commercial and
      open source databases. It is faster for some things, slower for
Bruce Momjian's avatar
Bruce Momjian committed
434
      others. In comparison to MySQL or leaner database systems, we are
Bruce Momjian's avatar
Bruce Momjian committed
435
      faster for multiple users, complex queries, and a read/write query
Bruce Momjian's avatar
Bruce Momjian committed
436
      load.  MySQL is faster for simple SELECT queries done by a few users.
Bruce Momjian's avatar
Bruce Momjian committed
437
      Of course, MySQL does not have most of the features mentioned in the
Bruce Momjian's avatar
Bruce Momjian committed
438
      <I>Features</I> section above. We are built for reliability and
Bruce Momjian's avatar
Bruce Momjian committed
439
      features, and we continue to improve performance in every
Bruce Momjian's avatar
Bruce Momjian committed
440
      release. <BR>
Bruce Momjian's avatar
Bruce Momjian committed
441 442 443 444 445
      <BR>
      </DD>

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

Bruce Momjian's avatar
Bruce Momjian committed
446 447 448 449 450 451 452
      <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
453 454 455 456 457
      <BR>
      </DD>

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

458 459
      <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
460 461 462 463 464
      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.
465
      (See <A href="#1.6">FAQ section 1.6</A>.)<BR>
Bruce Momjian's avatar
Bruce Momjian committed
466 467 468 469 470
      <BR>
      </DD>

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

Bruce Momjian's avatar
Bruce Momjian committed
471 472 473
      <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
474 475 476
      <BR>
      </DD>
    </DL>
Bruce Momjian's avatar
Bruce Momjian committed
477

Bruce Momjian's avatar
Bruce Momjian committed
478 479
    <H4><A name="1.15">1.15</A>) How can I financially assist
    PostgreSQL?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
480

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

Bruce Momjian's avatar
Bruce Momjian committed
485 486 487
    <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
488

Bruce Momjian's avatar
Bruce Momjian committed
489 490 491
    <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
492
    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
493
    and make a donation.</P>
Bruce Momjian's avatar
Bruce Momjian committed
494

Bruce Momjian's avatar
Bruce Momjian committed
495 496 497 498
    <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>
499

Bruce Momjian's avatar
Bruce Momjian committed
500 501 502
    <P>Also, if you have a success story about PostgreSQL, please email
    it to our advocacy list at <a href="mailto:pgsql-advocacy@postgresql.org">
    pgsql-advocacy@postgresql.org</a>.</P>
503

Bruce Momjian's avatar
Bruce Momjian committed
504
    <HR>
505

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

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

Bruce Momjian's avatar
Bruce Momjian committed
511 512
    <P>There are two <SMALL>ODBC</SMALL> drivers available, PsqlODBC
    and OpenLink <SMALL>ODBC</SMALL>.</P>
513

514 515 516
    <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>
517

Bruce Momjian's avatar
Bruce Momjian committed
518 519 520 521 522
    <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>
523

Bruce Momjian's avatar
Bruce Momjian committed
524 525 526 527
    <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>
528

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

Bruce Momjian's avatar
Bruce Momjian committed
532 533
    <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>
534

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

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

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

543
    <P>Yes, there are several graphical interfaces to PostgreSQL available.
Bruce Momjian's avatar
Bruce Momjian committed
544
    These include PgAccess <a href="http://www.pgaccess.org">
Bruce Momjian's avatar
Bruce Momjian committed
545
    http://www.pgaccess.org</a>), pgAdmin III (<a
546
    href="http://www.pgadmin.org">http://www.pgadmin.org</a>, RHDB Admin (<a
547
    href="http://sources.redhat.com/rhdb/">http://sources.redhat.com/rhdb/
548 549 550
    </a>), TORA (<a href="http://www.globecom.net/tora/">http://www.globecom.net/tora/
    (partly commercial)</a>, and Rekall
    (<a href="http://www.thekompany.com/products/rekall/">
551
    http://www.thekompany.com/products/rekall/</a>, proprietary). There is 
Bruce Momjian's avatar
Bruce Momjian committed
552
    also PhpPgAdmin (<a href="http://phppgadmin.sourceforge.net/">
553
    http://phppgadmin.sourceforge.net/ </a>), a web-based interface to 
554 555 556
    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>
557 558 559

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

561 562 563
    <P>Most popular programming languages contain an interface to
    PostgreSQL. Check your programming language's list of extension
    modules.</P>
564

565 566
    <P>The following interfaces are included in the PostgreSQL
    distribution:</P>
567

Bruce Momjian's avatar
Bruce Momjian committed
568 569
    <UL>
      <LI>C (libpq)</LI>
570

Bruce Momjian's avatar
Bruce Momjian committed
571
      <LI>Embedded C (ecpg)</LI>
572

Bruce Momjian's avatar
Bruce Momjian committed
573
      <LI>Java (jdbc)</LI>
574

Bruce Momjian's avatar
Bruce Momjian committed
575
      <LI>Python (PyGreSQL)</LI>
576

Bruce Momjian's avatar
Bruce Momjian committed
577
      <LI>TCL (libpgtcl)</LI>
578

Bruce Momjian's avatar
Bruce Momjian committed
579
    </UL>
580
    <P>Additional interfaces are available at 
581 582
    <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
583
    </P>
Bruce Momjian's avatar
Bruce Momjian committed
584
    <HR>
585

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

Bruce Momjian's avatar
Bruce Momjian committed
588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609
    <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
610 611 612
    href="http://www.postgresql.org/docs/current/static/kernel-resources.html">PostgreSQL
    Administrator's Guide/Server Run-time Environment/Managing Kernel Resources</A>
    section for more detailed information about shared memory and semaphores.</P>
Bruce Momjian's avatar
Bruce Momjian committed
613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633

    <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
634 635
    <H4><A name="3.5">3.5</A>) How do I control connections from other
    hosts?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
636 637

    <P>By default, PostgreSQL only allows connections from the local
Bruce Momjian's avatar
Bruce Momjian committed
638 639 640 641 642
    machine using Unix domain sockets or TCP/IP connections. Other
    machines will not be able to connect unless you modify
    listen_addresses in the postgresql.conf <B>and</B> enable
    host-based authentication by modifying the file
    <I>$PGDATA/pg_hba.conf</I> accordingly.</P>
Bruce Momjian's avatar
Bruce Momjian committed
643

Bruce Momjian's avatar
Bruce Momjian committed
644
    <H4><A name="3.6">3.6</A>) How do I tune the database engine for
Bruce Momjian's avatar
Bruce Momjian committed
645 646 647
    better performance?</H4>

    <P>Certainly, indexes can speed up queries. The
648 649 650
    <SMALL>EXPLAIN ANALYZE</SMALL> command allows you to see how
    PostgreSQL is interpreting your query, and which indexes are
    being used.</P>
Bruce Momjian's avatar
Bruce Momjian committed
651 652 653

    <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
654
    is much faster than individual <SMALL>INSERTS</SMALL>. Second,
Bruce Momjian's avatar
Bruce Momjian committed
655 656 657 658 659 660
    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>

661 662 663 664
    <P>There are several tuning options in the <a href=
    "http://www.postgresql.org/docs/current/static/runtime.html">
    Administration Guide/Server Run-time Environment/Run-time Configuration</a>.
    You can disable <I>fsync()</I> by using <i>fsync</I> option. This will
Bruce Momjian's avatar
Bruce Momjian committed
665 666 667
    prevent <I>fsync()</I>s from flushing to disk after every
    transaction.</P>

668
    <P>You can use the <I>shared_buffers</I> option to
Bruce Momjian's avatar
Bruce Momjian committed
669 670 671 672
    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
673
    default is 1000 buffers.</P>
Bruce Momjian's avatar
Bruce Momjian committed
674

Bruce Momjian's avatar
Bruce Momjian committed
675 676 677
    <P>You can also use the <I>sort_mem</I> (from PostgreSQL 8.0: <I>work_mem</I>)
    options to increase the maximum amount of memory used by the backend
    processes for each temporary sort. The default is 1024 (i.e. 1MB).</P>
Bruce Momjian's avatar
Bruce Momjian committed
678 679 680 681 682

    <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
683
    <H4><A name="3.7">3.7</A>) What debugging features are
Bruce Momjian's avatar
Bruce Momjian committed
684 685 686 687 688 689 690 691 692 693 694 695 696
    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>
697
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
698 699 700
    cd /usr/local/pgsql
    ./bin/postmaster &gt;server.log 2&gt;&amp;1 &amp;
</PRE>
701

Bruce Momjian's avatar
Bruce Momjian committed
702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721
    <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>
722 723 724 725 726 727 728 729 730 731 732 733
    process used by <I>psql</I> using <pre>SELECT pg_backend_pid()</pre>.
    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>There are several <pre>log_*</pre> server configuration variables
    that enable printing of process statistics which can be very useful
    for debugging and performance measurements.</P>
Bruce Momjian's avatar
Bruce Momjian committed
734 735 736 737

    <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
738
    file will be put in the client's current directory. Linux requires
739
    a compile with <I>-DLINUX_PROFILE</I> for proper profiling.</P>
Bruce Momjian's avatar
Bruce Momjian committed
740

Bruce Momjian's avatar
Bruce Momjian committed
741
    <H4><A name="3.8">3.8</A>) Why do I get <I>"Sorry, too many
Bruce Momjian's avatar
Bruce Momjian committed
742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761
    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
762
    <SMALL>NINODE</SMALL>. The reason that PostgreSQL has a limit on
Bruce Momjian's avatar
Bruce Momjian committed
763 764 765
    the number of allowed backend processes is so your system won't run
    out of resources.</P>

Bruce Momjian's avatar
Bruce Momjian committed
766
    <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
767

Bruce Momjian's avatar
Bruce Momjian committed
768 769 770 771 772
    <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
773

774 775 776
    <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>
777

778 779 780 781 782
    <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.
783 784 785 786 787
    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>
788

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

794 795 796 797 798
    <H4><A name="3.11">3.11</A>) What computer hardware should I use?</H4>

    <P>Because PC hardware is mostly compatible, people tend to believe that
    all PC hardware is of equal quality.  It is not.  ECC RAM, SCSI, and
    quality motherboards are more reliable and have better performance than
Bruce Momjian's avatar
Bruce Momjian committed
799
    less expensive hardware.  PostgreSQL will run on almost any hardware,
Bruce Momjian's avatar
Bruce Momjian committed
800 801 802
    but if reliability and performance are important it is wise to
    research your hardware options thoroughly.  Our email lists can be used
    to discuss hardware options and tradeoffs.</P>
803

Bruce Momjian's avatar
Bruce Momjian committed
804 805
    <HR>

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

Bruce Momjian's avatar
Bruce Momjian committed
808 809
    <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
810

Bruce Momjian's avatar
Bruce Momjian committed
811 812
    <P>See the <SMALL>DECLARE</SMALL> manual page for a
    description.</P>
Bruce Momjian's avatar
Bruce Momjian committed
813

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

Bruce Momjian's avatar
Bruce Momjian committed
817 818
    <P>See the <SMALL>FETCH</SMALL> manual page, or use
    <SMALL>SELECT</SMALL> ... <SMALL>LIMIT</SMALL>....</P>
Bruce Momjian's avatar
Bruce Momjian committed
819

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

827 828 829 830 831 832 833 834
    <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
835 836
    <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
837

Bruce Momjian's avatar
Bruce Momjian committed
838 839 840 841 842 843 844 845 846
    <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
847

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

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

865 866 867 868 869
    <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
870
    ALTER TABLE tab DROP COLUMN old_col;
871 872 873 874 875
    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
876 877
    <H4><A name="4.5">4.5</A>) What is the maximum size for a row, a
    table, and a database?</H4>
878

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

Bruce Momjian's avatar
Bruce Momjian committed
890 891 892
    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. 
893

894
    <P>The maximum table size of 32 TB does not require large file
Bruce Momjian's avatar
Bruce Momjian committed
895 896 897
    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
898

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

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

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

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

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

   8192 bytes per page
Bruce Momjian's avatar
Bruce Momjian committed
923 924
   -------------------   =  136 rows per database page (rounded down)
     60 bytes per row
925

Bruce Momjian's avatar
Bruce Momjian committed
926
   100000 data rows
Bruce Momjian's avatar
Bruce Momjian committed
927
   --------------------  =  735 database pages (rounded up)
Bruce Momjian's avatar
Bruce Momjian committed
928
      128 rows per page
929

Bruce Momjian's avatar
Bruce Momjian committed
930
735 database pages * 8192 bytes per page  =  6,021,120 bytes (6 MB)
Bruce Momjian's avatar
Bruce Momjian committed
931
</PRE>
932

Bruce Momjian's avatar
Bruce Momjian committed
933 934
    <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
935

936
    <P><SMALL>NULL</SMALL>s are stored as bitmaps, so they
Bruce Momjian's avatar
Bruce Momjian committed
937 938
    use very little space.</P>
    
939 940
    <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
941

Bruce Momjian's avatar
Bruce Momjian committed
942
    <P><I>psql</I> has a variety of backslash commands to show such
943 944 945
    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
946

Bruce Momjian's avatar
Bruce Momjian committed
947 948 949
    <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
950

Bruce Momjian's avatar
Bruce Momjian committed
951 952
    <H4><A name="4.8">4.8</A>) My queries are slow or don't make use of
    the indexes. Why?</H4>
953 954

    <P>Indexes are not automatically used by every query. Indexes are only
Bruce Momjian's avatar
Bruce Momjian committed
955
    used if the table is larger than a minimum size, and the query
Bruce Momjian's avatar
Bruce Momjian committed
956
    selects only a small percentage of the rows in the table. This is
957
    because the random disk access caused by an index scan can be
Bruce Momjian's avatar
Bruce Momjian committed
958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973
    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
974 975 976 977 978 979
    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
980
    ORDER BY col [ DESC ]
981
    LIMIT 1;
982
</PRE>
Bruce Momjian's avatar
Bruce Momjian committed
983

Bruce Momjian's avatar
Bruce Momjian committed
984 985 986 987
    <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
988
    <P>When using wild-card operators such as <SMALL>LIKE</SMALL> or
Bruce Momjian's avatar
Bruce Momjian committed
989
    <I>~</I>, indexes can only be used in certain circumstances:</P>
990 991
    <UL>
    <LI>The beginning of the search string must be anchored to the start
Bruce Momjian's avatar
Bruce Momjian committed
992
    of the string, i.e.
993
    <UL>
Bruce Momjian's avatar
Bruce Momjian committed
994
    <LI><SMALL>LIKE</SMALL> patterns must not start with <I>%</I>.</LI>
Bruce Momjian's avatar
Bruce Momjian committed
995
    <LI><I>~</I> (regular expression) patterns must start with
Bruce Momjian's avatar
Bruce Momjian committed
996 997
    <I>^</I>.</LI>
    </UL></LI>
998 999
    <LI>The search string can not start with a character class,
    e.g. [a-e].</LI>
1000 1001 1002 1003
    <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
1004 1005 1006 1007 1008
    <i>initdb</i> because it is not possible to know the next-greater
    character in a non-C locale.  You can create a special
    <PRE>text_pattern_ops</PRE> index for such cases that work only
    for <PRE>LIKE</PRE> indexing.
    </LI>
1009 1010
    </UL>
    <P>
Bruce Momjian's avatar
Bruce Momjian committed
1011

Bruce Momjian's avatar
Bruce Momjian committed
1012 1013 1014 1015
    <P>In pre-8.0 releases, indexes often can not be used unless the data
    types exactly match the index's column types.  This is particularly
    true of int2, int8, and numeric column indexes.</P>

Bruce Momjian's avatar
Bruce Momjian committed
1016 1017
    <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
1018 1019 1020

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

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

Bruce Momjian's avatar
Bruce Momjian committed
1023 1024 1025 1026 1027 1028 1029
    <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
1030

Bruce Momjian's avatar
Bruce Momjian committed
1031 1032
    <P>The canonical paper that describes the original R-tree design
    is:</P>
Bruce Momjian's avatar
Bruce Momjian committed
1033

Bruce Momjian's avatar
Bruce Momjian committed
1034 1035 1036
    <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
1037

Bruce Momjian's avatar
Bruce Momjian committed
1038 1039
    <P>You can also find this paper in Stonebraker's "Readings in
    Database Systems".</P>
Bruce Momjian's avatar
Bruce Momjian committed
1040

Bruce Momjian's avatar
Bruce Momjian committed
1041 1042 1043 1044
    <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
1045

Bruce Momjian's avatar
Bruce Momjian committed
1046 1047
    <H4><A name="4.11">4.11</A>) What is the Genetic Query
    Optimizer?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1048

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

Bruce Momjian's avatar
Bruce Momjian committed
1054 1055 1056
    <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
1057

Bruce Momjian's avatar
Bruce Momjian committed
1058 1059 1060
    <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
1061
    <SMALL>ILIKE</SMALL>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1062

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

Bruce Momjian's avatar
Bruce Momjian committed
1071 1072
    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
1073
<PRE>
1074
    CREATE INDEX tabindex ON tab (lower(col));
Bruce Momjian's avatar
Bruce Momjian committed
1075
</PRE>
1076

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

Bruce Momjian's avatar
Bruce Momjian committed
1080 1081
    <P>You test the column with <SMALL>IS NULL</SMALL> and <SMALL>IS
    NOT NULL</SMALL>.</P>
1082

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

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

1098
    <P>The first four types above are "varlena" types (i.e., the first
Bruce Momjian's avatar
Bruce Momjian committed
1099 1100 1101 1102 1103 1104
    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
1105 1106 1107 1108 1109 1110 1111
    <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,
1112 1113
    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
1114 1115 1116 1117 1118

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

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

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

Bruce Momjian's avatar
Bruce Momjian committed
1156 1157 1158 1159 1160 1161
    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
1162
    and your <SMALL>SERIAL</SMALL> column, respectively.
Bruce Momjian's avatar
Bruce Momjian committed
1163 1164

    <P>Alternatively, you could retrieve the assigned
1165
    <SMALL>SERIAL</SMALL> value with the <I>currval()</I> function
Bruce Momjian's avatar
Bruce Momjian committed
1166
    <I>after</I> it was inserted by default, e.g.,</P>
1167
<PRE>
1168 1169
    execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
    new_id = execute("SELECT currval('person_id_seq')");
1170
</PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1171 1172
    
    <P>Finally, you could use the <A href="#4.16"><SMALL>OID</SMALL></A>
Bruce Momjian's avatar
Bruce Momjian committed
1173
    returned from the <SMALL>INSERT</SMALL> statement to look up the
1174 1175
    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
1176 1177 1178
    In Perl, using DBI with the DBD::Pg module, the oid value is made
    available via <I>$sth-&gt;{pg_oid_status}</I> after
    <I>$sth-&gt;execute()</I>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1179

1180 1181
    <H4><A name="4.15.3">4.15.3</A>) Doesn't <I>currval()</I>
    lead to a race condition with other users?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1182

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

Bruce Momjian's avatar
Bruce Momjian committed
1186 1187 1188
    <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
1189 1190

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

Bruce Momjian's avatar
Bruce Momjian committed
1195 1196 1197 1198 1199 1200 1201
    <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
1202
    <I>include/access/transam.h</I>). All user-created
Bruce Momjian's avatar
Bruce Momjian committed
1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218
    <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>
1219
<PRE>
1220 1221 1222 1223 1224
        CREATE TABLE new_table(mycol int);
        SELECT oid AS old_oid, mycol INTO tmp_table FROM old_table;
        COPY tmp_table TO '/tmp/pgtable';
        COPY new_table WITH OIDS FROM '/tmp/pgtable';
        DROP TABLE tmp_table;
1225
</PRE>
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
    will cause the query to be reparsed every time.</P>

Bruce Momjian's avatar
Bruce Momjian committed
1407
    <H4><A name="4.27">4.27</A>) What encryption options are available?
1408 1409
    </H4>
    <UL>
Bruce Momjian's avatar
Bruce Momjian committed
1410
    <LI><I>contrib/pgcrypto</I> contains many encryption functions for
1411
    use in <SMALL>SQL</SMALL> queries.</LI>
1412 1413 1414 1415 1416 1417 1418 1419 1420
    <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
1421
    <I>PASSWORD_ENCRYPTION</I> in <I>postgresql.conf</I>.</LI>
Bruce Momjian's avatar
Bruce Momjian committed
1422
    <LI>The server can run using an encrypted file system.</LI>
1423
    </UL>
Bruce Momjian's avatar
Bruce Momjian committed
1424

1425 1426
    <HR>

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

Bruce Momjian's avatar
Bruce Momjian committed
1429 1430
    <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>
1431

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

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

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

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

1445 1446 1447 1448
    <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
1449
    <I>contrib/tablefunc</I>.</P>
1450

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

Bruce Momjian's avatar
Bruce Momjian committed
1454 1455 1456 1457 1458
    <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
1459
  </BODY>
1460
</HTML>
Bruce Momjian's avatar
Bruce Momjian committed
1461