FAQ 50.2 KB
Newer Older
Bruce Momjian's avatar
Bruce Momjian committed
1

Bruce Momjian's avatar
Bruce Momjian committed
2
                Frequently Asked Questions (FAQ) for PostgreSQL
Bruce Momjian's avatar
Bruce Momjian committed
3
                                       
4
   Last updated: Fri Oct 10 17:27:02 EDT 2003
Bruce Momjian's avatar
Bruce Momjian committed
5
   
Bruce Momjian's avatar
Bruce Momjian committed
6
   Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
Bruce Momjian's avatar
Bruce Momjian committed
7
   
Bruce Momjian's avatar
Bruce Momjian committed
8
   The most recent version of this document can be viewed at
Bruce Momjian's avatar
Bruce Momjian committed
9
   http://www.PostgreSQL.org/docs/faqs/FAQ.html.
Bruce Momjian's avatar
Bruce Momjian committed
10
   
Bruce Momjian's avatar
Bruce Momjian committed
11
   Platform-specific questions are answered at
Bruce Momjian's avatar
Bruce Momjian committed
12
   http://www.PostgreSQL.org/docs/index.html.
Bruce Momjian's avatar
Bruce Momjian committed
13 14
     _________________________________________________________________
   
15
                             General Questions
Bruce Momjian's avatar
Bruce Momjian committed
16
                                      
Bruce Momjian's avatar
Bruce Momjian committed
17 18
   1.1) What is PostgreSQL? How is it pronounced?
   1.2) What is the copyright on PostgreSQL?
19
   1.3) What Unix platforms does PostgreSQL run on?
Bruce Momjian's avatar
Bruce Momjian committed
20
   1.4) What non-Unix ports are available?
21
   1.5) Where can I get PostgreSQL?
Bruce Momjian's avatar
Bruce Momjian committed
22 23 24
   1.6) Where can I get support?
   1.7) What is the latest release?
   1.8) What documentation is available?
25 26 27 28 29
   1.9) How do I find out about known bugs or missing features?
   1.10) How can I learn SQL?
   1.11) Is PostgreSQL Y2K compliant?
   1.12) How do I join the development team?
   1.13) How do I submit a bug report?
Bruce Momjian's avatar
Bruce Momjian committed
30
   1.14) How does PostgreSQL compare to other DBMSs?
Bruce Momjian's avatar
Bruce Momjian committed
31
   1.15) How can I financially assist PostgreSQL?
32 33
   
                           User Client Questions
Bruce Momjian's avatar
Bruce Momjian committed
34
                                      
35
   2.1) Are there ODBC drivers for PostgreSQL?
Bruce Momjian's avatar
Bruce Momjian committed
36
   2.2) What tools are available for using PostgreSQL with Web pages?
37
   2.3) Does PostgreSQL have a graphical user interface?
38 39 40 41
   2.4) What languages are available to communicate with PostgreSQL?
   
                          Administrative Questions
                                      
Bruce Momjian's avatar
Bruce Momjian committed
42
   3.1) How do I install PostgreSQL somewhere other than
Bruce Momjian's avatar
Bruce Momjian committed
43
   /usr/local/pgsql?
Bruce Momjian's avatar
Bruce Momjian committed
44 45 46 47 48
   3.2) When I start postmaster, I get a Bad System Call or core dumped
   message. Why?
   3.3) When I try to start postmaster, I get IpcMemoryCreate errors.
   Why?
   3.4) When I try to start postmaster, I get IpcSemaphoreCreate errors.
Bruce Momjian's avatar
Bruce Momjian committed
49
   Why?
Bruce Momjian's avatar
Bruce Momjian committed
50 51 52 53
   3.5) How do I control connections from other hosts?
   3.6) How do I tune the database engine for better performance?
   3.7) What debugging features are available?
   3.8) Why do I get "Sorry, too many clients" when trying to connect?
54
   3.9) What is in the pgsql_tmp directory?
55 56
   3.10) Why do I need to do a dump and restore to upgrade PostgreSQL
   releases?
57 58
   
                           Operational Questions
Bruce Momjian's avatar
Bruce Momjian committed
59
                                      
Bruce Momjian's avatar
Bruce Momjian committed
60
   4.1) What is the difference between binary cursors and normal cursors?
61
   4.2) How do I SELECT only the first few rows of a query? A random row?
Bruce Momjian's avatar
Bruce Momjian committed
62
   4.3) How do I get a list of tables or other things I can see in psql?
63 64
   4.4) How do you remove a column from a table, or change it's data
   type?
Bruce Momjian's avatar
Bruce Momjian committed
65
   4.5) What is the maximum size for a row, a table, and a database?
Bruce Momjian's avatar
Bruce Momjian committed
66
   4.6) How much database disk space is required to store data from a
Bruce Momjian's avatar
Bruce Momjian committed
67
   typical text file?
68 69
   4.7) How do I find out what tables, indexes, databases, and users are
   defined?
Bruce Momjian's avatar
Bruce Momjian committed
70 71 72 73 74
   4.8) My queries are slow or don't make use of the indexes. Why?
   4.9) How do I see how the query optimizer is evaluating my query?
   4.10) What is an R-tree index?
   4.11) What is the Genetic Query Optimizer?
   4.12) How do I perform regular expression searches and
Bruce Momjian's avatar
Bruce Momjian committed
75 76
   case-insensitive regular expression searches? How do I use an index
   for case-insensitive searches?
Bruce Momjian's avatar
Bruce Momjian committed
77 78 79 80 81
   4.13) In a query, how do I detect if a field is NULL?
   4.14) What is the difference between the various character types?
   4.15.1) How do I create a serial/auto-incrementing field?
   4.15.2) How do I get the value of a SERIAL insert?
   4.15.3) Don't currval() and nextval() lead to a race condition with
Bruce Momjian's avatar
Bruce Momjian committed
82
   other users?
Bruce Momjian's avatar
Bruce Momjian committed
83
   4.15.4) Why aren't my sequence numbers reused on transaction abort?
Bruce Momjian's avatar
Bruce Momjian committed
84
   Why are there gaps in the numbering of my sequence/SERIAL column?
Bruce Momjian's avatar
Bruce Momjian committed
85 86 87
   4.16) What is an OID? What is a TID?
   4.17) What is the meaning of some of the terms used in PostgreSQL?
   4.18) Why do I get the error "ERROR: Memory exhausted in
Bruce Momjian's avatar
Bruce Momjian committed
88
   AllocSetAlloc()"?
Bruce Momjian's avatar
Bruce Momjian committed
89
   4.19) How do I tell what PostgreSQL version I am running?
Bruce Momjian's avatar
Bruce Momjian committed
90 91
   4.20) Why does my large-object operations get "invalid large obj
   descriptor"?
Bruce Momjian's avatar
Bruce Momjian committed
92 93 94 95
   4.21) How do I create a column that will default to the current time?
   4.22) Why are my subqueries using IN so slow?
   4.23) How do I perform an outer join?
   4.24) How do I perform queries using multiple databases?
Bruce Momjian's avatar
Bruce Momjian committed
96
   4.25) How do I return multiple rows or columns from a function?
97 98
   4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
   functions?
99 100
   4.27) What replication options are available?
   4.28) What encryption options are available?
Bruce Momjian's avatar
Bruce Momjian committed
101
   
102
                            Extending PostgreSQL
Bruce Momjian's avatar
Bruce Momjian committed
103
                                      
104
   5.1) I wrote a user-defined function. When I run it in psql, why does
Bruce Momjian's avatar
Bruce Momjian committed
105
   it dump core?
Bruce Momjian's avatar
Bruce Momjian committed
106
   5.2) How can I contribute some nifty new types and functions to
107
   PostgreSQL?
Bruce Momjian's avatar
Bruce Momjian committed
108
   5.3) How do I write a C function to return a tuple?
Bruce Momjian's avatar
Bruce Momjian committed
109
   5.4) I have changed a source file. Why does the recompile not see the
Bruce Momjian's avatar
Bruce Momjian committed
110
   change?
Bruce Momjian's avatar
Bruce Momjian committed
111 112
     _________________________________________________________________
   
113 114
                             General Questions
                                      
115
    1.1) What is PostgreSQL? How is it pronounced?
116
    
Bruce Momjian's avatar
Bruce Momjian committed
117 118
   PostgreSQL is pronounced Post-Gres-Q-L.
   
Bruce Momjian's avatar
Bruce Momjian committed
119 120 121 122 123 124
   PostgreSQL is an enhancement of the POSTGRES database management
   system, a next-generation DBMS research prototype. While PostgreSQL
   retains the powerful data model and rich data types of POSTGRES, it
   replaces the PostQuel query language with an extended subset of SQL.
   PostgreSQL is free and the complete source is available.
   
125 126 127 128 129
   PostgreSQL development is performed by a team of developers who all
   subscribe to the PostgreSQL development mailing list. The current
   coordinator is Marc G. Fournier (scrappy@PostgreSQL.org). (See section
   1.6 on how to join). This team is now responsible for all development
   of PostgreSQL.
Bruce Momjian's avatar
Bruce Momjian committed
130 131
   
   The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. Many
Bruce Momjian's avatar
Bruce Momjian committed
132
   others have contributed to the porting, testing, debugging, and
Bruce Momjian's avatar
Bruce Momjian committed
133 134 135 136 137 138 139 140 141 142
   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.
   
   The original name of the software at Berkeley was Postgres. When SQL
   functionality was added in 1995, its name was changed to Postgres95.
   The name was changed at the end of 1996 to PostgreSQL.
   
Bruce Momjian's avatar
Bruce Momjian committed
143
    1.2) What is the copyright on PostgreSQL?
144
    
Bruce Momjian's avatar
Bruce Momjian committed
145
   PostgreSQL is subject to the following COPYRIGHT:
Bruce Momjian's avatar
Bruce Momjian committed
146 147 148
   
   PostgreSQL Data Base Management System
   
Bruce Momjian's avatar
Bruce Momjian committed
149
   Portions copyright (c) 1996-2002, PostgreSQL Global Development Group
Bruce Momjian's avatar
Bruce Momjian committed
150
   Portions Copyright (c) 1994-6 Regents of the University of California
Bruce Momjian's avatar
Bruce Momjian committed
151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170
   
   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.
   
   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.
   
   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.
   
Bruce Momjian's avatar
Bruce Momjian committed
171 172 173 174
   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.
   
175 176
    1.3) What Unix platforms does PostgreSQL run on?
    
Bruce Momjian's avatar
Bruce Momjian committed
177 178 179 180
   In general, a modern Unix-compatible platform should be able to run
   PostgreSQL. The platforms that had received explicit testing at the
   time of release are listed in the installation instructions.
   
Bruce Momjian's avatar
Bruce Momjian committed
181
    1.4) What non-Unix ports are available?
182
    
Bruce Momjian's avatar
Bruce Momjian committed
183 184
   Client
   
185
   It is possible to compile the libpq C library, psql, and other
186 187 188 189 190 191
   interfaces and client applications to run on MS Windows platforms. In
   this case, the client is running on MS Windows, and communicates via
   TCP/IP to a server running on one of our supported Unix platforms. A
   file win32.mak is included in the distribution for making a Win32
   libpq library and psql. PostgreSQL also communicates with ODBC
   clients.
192
   
Bruce Momjian's avatar
Bruce Momjian committed
193 194
   Server
   
Bruce Momjian's avatar
Bruce Momjian committed
195 196
   The database server can run on Windows NT and Win2k using Cygwin, the
   Cygnus Unix/NT porting library. See pgsql/doc/FAQ_MSWIN in the
197
   distribution or the MS Windows FAQ at
198
   http://www.PostgreSQL.org/docs/faqs/text/FAQ_MSWIN.
199
   
200 201
   A native port to MS Win NT/2000/XP is currently being worked on. For
   more details on the current status of PostgreSQL on Windows see
Bruce Momjian's avatar
Bruce Momjian committed
202
   http://techdocs.postgresql.org/guides/Windows and
Bruce Momjian's avatar
Bruce Momjian committed
203
   http://momjian.postgresql.org/main/writings/pgsql/win32.html.
204
   
205 206
   There is also a Novell Netware 6 port at http://forge.novell.com.
   
207 208 209
    1.5) Where can I get PostgreSQL?
    
   The primary anonymous ftp site for PostgreSQL is
Bruce Momjian's avatar
Bruce Momjian committed
210
   ftp://ftp.PostgreSQL.org/pub. For mirror sites, see our main web site.
211
   
Bruce Momjian's avatar
Bruce Momjian committed
212
    1.6) Where can I get support?
213
    
Bruce Momjian's avatar
Bruce Momjian committed
214
   The main mailing list is: pgsql-general@PostgreSQL.org. It is
Bruce Momjian's avatar
Bruce Momjian committed
215
   available for discussion of matters pertaining to PostgreSQL. To
Bruce Momjian's avatar
Bruce Momjian committed
216
   subscribe, send mail with the following lines in the body (not the
Bruce Momjian's avatar
Bruce Momjian committed
217
   subject line):
Bruce Momjian's avatar
Bruce Momjian committed
218 219
    subscribe
    end
Bruce Momjian's avatar
Bruce Momjian committed
220

Bruce Momjian's avatar
Bruce Momjian committed
221
   to pgsql-general-request@PostgreSQL.org.
Bruce Momjian's avatar
Bruce Momjian committed
222 223
   
   There is also a digest list available. To subscribe to this list, send
Bruce Momjian's avatar
Bruce Momjian committed
224
   email to: pgsql-general-digest-request@PostgreSQL.org with a body of:
Bruce Momjian's avatar
Bruce Momjian committed
225 226
    subscribe
    end
Bruce Momjian's avatar
Bruce Momjian committed
227 228 229 230

   Digests are sent out to members of this list whenever the main list
   has received around 30k of messages.
   
Bruce Momjian's avatar
Bruce Momjian committed
231
   The bugs mailing list is available. To subscribe to this list, send
Bruce Momjian's avatar
Bruce Momjian committed
232
   email to pgsql-bugs-request@PostgreSQL.org with a body of:
Bruce Momjian's avatar
Bruce Momjian committed
233 234
    subscribe
    end
Bruce Momjian's avatar
Bruce Momjian committed
235

Bruce Momjian's avatar
Bruce Momjian committed
236
   There is also a developers discussion mailing list available. To
Bruce Momjian's avatar
Bruce Momjian committed
237 238
   subscribe to this list, send email to
   pgsql-hackers-request@PostgreSQL.org with a body of:
Bruce Momjian's avatar
Bruce Momjian committed
239 240
    subscribe
    end
Bruce Momjian's avatar
Bruce Momjian committed
241

Bruce Momjian's avatar
Bruce Momjian committed
242 243
   Additional mailing lists and information about PostgreSQL can be found
   via the PostgreSQL WWW home page at:
Bruce Momjian's avatar
Bruce Momjian committed
244
   
Bruce Momjian's avatar
Bruce Momjian committed
245
     http://www.PostgreSQL.org
Bruce Momjian's avatar
Bruce Momjian committed
246
     
247 248 249
   There is also an IRC channel on EFNet and OpenProjects, channel
   #PostgreSQL. I use the Unix command irc -c '#PostgreSQL' "$USER"
   irc.phoenix.net.
Bruce Momjian's avatar
Bruce Momjian committed
250
   
Bruce Momjian's avatar
Bruce Momjian committed
251
   A list of commercial support companies is available at
Bruce Momjian's avatar
Bruce Momjian committed
252
   http://techdocs.postgresql.org/companies.php.
253
   
Bruce Momjian's avatar
Bruce Momjian committed
254
    1.7) What is the latest release?
255
    
256
   The latest release of PostgreSQL is version 7.3.4.
Bruce Momjian's avatar
Bruce Momjian committed
257
   
Bruce Momjian's avatar
Bruce Momjian committed
258
   We plan to have major releases every six to eight months.
Bruce Momjian's avatar
Bruce Momjian committed
259
   
Bruce Momjian's avatar
Bruce Momjian committed
260
    1.8) What documentation is available?
261
    
Bruce Momjian's avatar
Bruce Momjian committed
262
   Several manuals, manual pages, and some small test examples are
Bruce Momjian's avatar
Bruce Momjian committed
263
   included in the distribution. See the /doc directory. You can also
264
   browse the manuals online at http://www.PostgreSQL.org/docs.
Bruce Momjian's avatar
Bruce Momjian committed
265
   
Bruce Momjian's avatar
Bruce Momjian committed
266 267 268
   There are two PostgreSQL books available online at
   http://www.PostgreSQL.org/docs/awbook.html and
   http://www.commandprompt.com/ppbook/. There is a list of PostgreSQL
Bruce Momjian's avatar
Bruce Momjian committed
269 270 271
   books available for purchase at
   http://techdocs.PostgreSQL.org/techdocs/bookreviews.php. There is also
   a collection of PostgreSQL technical articles at
272
   http://techdocs.PostgreSQL.org/.
Bruce Momjian's avatar
Bruce Momjian committed
273 274 275
   
   psql has some nice \d commands to show information about types,
   operators, functions, aggregates, etc.
Bruce Momjian's avatar
Bruce Momjian committed
276
   
Bruce Momjian's avatar
Bruce Momjian committed
277
   Our web site contains even more documentation.
278 279 280
   
    1.9) How do I find out about known bugs or missing features?
    
Bruce Momjian's avatar
Bruce Momjian committed
281 282
   PostgreSQL supports an extended subset of SQL-92. See our TODO list
   for known bugs, missing features, and future plans.
283 284 285
   
    1.10) How can I learn SQL?
    
Bruce Momjian's avatar
Bruce Momjian committed
286
   The PostgreSQL book at http://www.PostgreSQL.org/docs/awbook.html
Bruce Momjian's avatar
Bruce Momjian committed
287 288 289 290 291
   teaches SQL. There is another PostgreSQL book at
   http://www.commandprompt.com/ppbook. There is a nice tutorial at
   http://www.intermedia.net/support/sql/sqltut.shtm, at
   http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM,
   and at http://sqlcourse.com.
Bruce Momjian's avatar
Bruce Momjian committed
292 293
   
   Another one is "Teach Yourself SQL in 21 Days, Second Edition" at
Bruce Momjian's avatar
Bruce Momjian committed
294
   http://members.tripod.com/er4ebus/sql/index.htm
295
   
Bruce Momjian's avatar
Bruce Momjian committed
296
   Many of our users like The Practical SQL Handbook, Bowman, Judith S.,
Bruce Momjian's avatar
Bruce Momjian committed
297
   et al., Addison-Wesley. Others like The Complete Reference SQL, Groff
Bruce Momjian's avatar
Bruce Momjian committed
298
   et al., McGraw-Hill.
299 300 301
   
    1.11) Is PostgreSQL Y2K compliant?
    
Bruce Momjian's avatar
Bruce Momjian committed
302
   Yes, we easily handle dates past the year 2000 AD, and before 2000 BC.
Bruce Momjian's avatar
Bruce Momjian committed
303
   
304 305
    1.12) How do I join the development team?
    
Bruce Momjian's avatar
Bruce Momjian committed
306
   First, download the latest source and read the PostgreSQL Developers
Bruce Momjian's avatar
Bruce Momjian committed
307
   documentation on our web site, or in the distribution. Second,
308
   subscribe to the pgsql-hackers and pgsql-patches mailing lists. Third,
Bruce Momjian's avatar
Bruce Momjian committed
309
   submit high quality patches to pgsql-patches.
Bruce Momjian's avatar
Bruce Momjian committed
310
   
Bruce Momjian's avatar
Bruce Momjian committed
311
   There are about a dozen people who have commit privileges to the
Bruce Momjian's avatar
Bruce Momjian committed
312
   PostgreSQL CVS archive. They each have submitted so many high-quality
Bruce Momjian's avatar
Bruce Momjian committed
313 314 315
   patches that it was impossible for the existing committers to keep up,
   and we had confidence that patches they committed were of high
   quality.
Bruce Momjian's avatar
Bruce Momjian committed
316
   
317 318
    1.13) How do I submit a bug report?
    
319 320 321
   Please visit the PostgreSQL BugTool page at
   http://www.PostgreSQL.org/bugs/bugs.php, which gives guidelines and
   directions on how to submit a bug report.
Bruce Momjian's avatar
Bruce Momjian committed
322
   
Bruce Momjian's avatar
Bruce Momjian committed
323
   Also check out our ftp site ftp://ftp.PostgreSQL.org/pub to see if
324 325
   there is a more recent PostgreSQL version or patches.
   
Bruce Momjian's avatar
Bruce Momjian committed
326
    1.14) How does PostgreSQL compare to other DBMSs?
327 328 329 330 331
    
   There are several ways of measuring software: features, performance,
   reliability, support, and price.
   
   Features
Bruce Momjian's avatar
Bruce Momjian committed
332 333 334 335
          PostgreSQL has most features present in large commercial DBMSs,
          like transactions, subselects, triggers, views, foreign key
          referential integrity, and sophisticated locking. We have some
          features they do not have, like user-defined types,
Bruce Momjian's avatar
Bruce Momjian committed
336
          inheritance, rules, and multi-version concurrency control to
Bruce Momjian's avatar
Bruce Momjian committed
337
          reduce lock contention.
338 339
          
   Performance
Bruce Momjian's avatar
Bruce Momjian committed
340 341
          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
342
          others. In comparison to MySQL or leaner database systems, we
Bruce Momjian's avatar
Bruce Momjian committed
343
          are faster for multiple users, complex queries, and a
Bruce Momjian's avatar
Bruce Momjian committed
344
          read/write query load. MySQL is faster for simple SELECT
Bruce Momjian's avatar
Bruce Momjian committed
345 346 347
          queries done by a few users. Of course, MySQL does not have
          most of the features mentioned in the Features section above.
          We are built for reliability and features, and we continue to
Bruce Momjian's avatar
Bruce Momjian committed
348 349
          improve performance in every release. There is an interesting
          Web page comparing PostgreSQL to MySQL at
Bruce Momjian's avatar
Bruce Momjian committed
350 351 352
          http://openacs.org/philosophy/why-not-mysql.html Also, MySQL is
          is a company that distributes its products via open source, not
          an open source development community ilke PostgreSQL.
353 354 355 356 357 358 359 360 361 362
          
   Reliability
          We realize that a DBMS 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.
          
   Support
363 364 365 366 367 368 369 370
          Our mailing lists provide contact with a large group of
          developers and users to help resolve any problems encountered.
          While we cannot guarantee a fix, commercial DBMSs 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 DBMSs. There is commercial
          per-incident support available for those who need it. (See FAQ
          section 1.6.)
371 372 373 374 375
          
   Price
          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.
Bruce Momjian's avatar
Bruce Momjian committed
376
          
Bruce Momjian's avatar
Bruce Momjian committed
377
    1.15) How can I financially assist PostgreSQL?
Bruce Momjian's avatar
Bruce Momjian committed
378
    
379
   PostgreSQL has had a first-class infrastructure since we started in
380
   1996. This is all thanks to Marc Fournier, who has created and managed
381
   this infrastructure over the years.
Bruce Momjian's avatar
Bruce Momjian committed
382 383 384 385 386 387 388 389
   
   Quality infrastructure is very important to an open-source project. It
   prevents disruptions that can greatly delay forward movement of the
   project.
   
   Of course, this infrastructure is not cheap. There are a variety of
   monthly and one-time expenses that are required to keep it going. If
   you or your company has money it can donate to help fund this effort,
390
   please go to http://store.pgsql.com/shopping/ and make a donation.
Bruce Momjian's avatar
Bruce Momjian committed
391
   
Bruce Momjian's avatar
Bruce Momjian committed
392 393 394 395
   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.
396
     _________________________________________________________________
Bruce Momjian's avatar
Bruce Momjian committed
397
   
398 399 400
   Also, if you have a success story about PostgreSQL, please submit it
   to our advocacy site at http://advocacy.postgresql.org.
   
401 402 403 404
                           User Client Questions
                                      
    2.1) Are there ODBC drivers for PostgreSQL?
    
Bruce Momjian's avatar
Bruce Momjian committed
405
   There are two ODBC drivers available, PsqlODBC and OpenLink ODBC.
Bruce Momjian's avatar
Bruce Momjian committed
406
   
407 408
   You can download PsqlODBC from
   http://gborg.postgresql.org/project/psqlodbc/projdisplay.php.
Bruce Momjian's avatar
Bruce Momjian committed
409
   
Bruce Momjian's avatar
Bruce Momjian committed
410 411 412 413 414 415
   OpenLink ODBC can be gotten from http://www.openlinksw.com. It works
   with their standard ODBC client software so you'll have PostgreSQL
   ODBC available on every client platform they support (Win, Mac, Unix,
   VMS).
   
   They will probably be selling this product to people who need
Bruce Momjian's avatar
Bruce Momjian committed
416
   commercial-quality support, but a freeware version will always be
Bruce Momjian's avatar
Bruce Momjian committed
417
   available. Please send questions to postgres95@openlink.co.uk.
Bruce Momjian's avatar
Bruce Momjian committed
418
   
Bruce Momjian's avatar
Bruce Momjian committed
419
    2.2) What tools are available for using PostgreSQL with Web pages?
420
    
Bruce Momjian's avatar
Bruce Momjian committed
421
   A nice introduction to Database-backed Web pages can be seen at:
Bruce Momjian's avatar
Bruce Momjian committed
422
   http://www.webreview.com
Bruce Momjian's avatar
Bruce Momjian committed
423
   
Bruce Momjian's avatar
Bruce Momjian committed
424
   For Web integration, PHP is an excellent interface. It is at
Bruce Momjian's avatar
Bruce Momjian committed
425
   http://www.php.net.
Bruce Momjian's avatar
Bruce Momjian committed
426
   
427
   For complex cases, many use the Perl interface and CGI.pm or mod_perl.
Bruce Momjian's avatar
Bruce Momjian committed
428
   
429
    2.3) Does PostgreSQL have a graphical user interface?
430
    
431
   Yes, there are several graphical interfaces to PostgreSQL available.
Bruce Momjian's avatar
Bruce Momjian committed
432
   These include PgAccess http://www.pgaccess.org), PgAdmin II
433 434 435 436 437 438
   (http://www.pgadmin.org, Win32-only), RHDB Admin
   (http://sources.redhat.com/rhdb/ ) and Rekall (
   http://www.thekompany.com/products/rekall/, proprietary). There is
   also PHPPgAdmin ( http://phppgadmin.sourceforge.net/ ), a web-based
   interface to PostgreSQL.
   
439 440 441
   See http://techdocs.postgresql.org/guides/GUITools for a more detailed
   list.
   
442
    2.4) What languages are able to communicate with PostgreSQL?
443
    
444 445 446 447
   Most popular programming languages contain an interface to PostgreSQL.
   Check your programming language's list of extension modules.
   
   The following interfaces are included in the PostgreSQL distribution:
Bruce Momjian's avatar
Bruce Momjian committed
448 449 450 451 452
     * C (libpq)
     * Embedded C (ecpg)
     * Java (jdbc)
     * Python (PyGreSQL)
     * TCL (libpgtcl)
Bruce Momjian's avatar
Bruce Momjian committed
453
       
454 455
   Additional interfaces are available at http://gborg.postgresql.org in
   the Drivers/Interfaces section.
Bruce Momjian's avatar
Bruce Momjian committed
456 457
     _________________________________________________________________
   
458 459
                          Administrative Questions
                                      
Bruce Momjian's avatar
Bruce Momjian committed
460
    3.1) How do I install PostgreSQL somewhere other than /usr/local/pgsql?
461
    
Bruce Momjian's avatar
Bruce Momjian committed
462
   Specify the --prefix option when running configure.
Bruce Momjian's avatar
Bruce Momjian committed
463
   
Bruce Momjian's avatar
Bruce Momjian committed
464
    3.2) When I start postmaster, I get a Bad System Call or core dumped
465 466
    message. Why?
    
Bruce Momjian's avatar
Bruce Momjian committed
467
   It could be a variety of problems, but first check to see that you
Bruce Momjian's avatar
Bruce Momjian committed
468
   have System V extensions installed in your kernel. PostgreSQL requires
Bruce Momjian's avatar
Bruce Momjian committed
469
   kernel support for shared memory and semaphores.
Bruce Momjian's avatar
Bruce Momjian committed
470
   
Bruce Momjian's avatar
Bruce Momjian committed
471
    3.3) When I try to start postmaster, I get IpcMemoryCreate errors. Why?
472
    
Bruce Momjian's avatar
Bruce Momjian committed
473 474 475
   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
Bruce Momjian's avatar
Bruce Momjian committed
476 477 478 479
   many buffers and backend processes you configure for postmaster. For
   most systems, with default numbers of buffers and processes, you need
   a minimum of ~1 MB. See the PostgreSQL Administrator's Guide for more
   detailed information about shared memory and semaphores.
Bruce Momjian's avatar
Bruce Momjian committed
480
   
Bruce Momjian's avatar
Bruce Momjian committed
481
    3.4) When I try to start postmaster, I get IpcSemaphoreCreate errors. Why?
482 483 484 485
    
   If the error message is IpcSemaphoreCreate: semget failed (No space
   left on device) then your kernel is not configured with enough
   semaphores. Postgres needs one semaphore per potential backend
Bruce Momjian's avatar
Bruce Momjian committed
486 487 488 489 490 491 492
   process. A temporary solution is to start postmaster with a smaller
   limit on the number of backend processes. Use -N with a parameter less
   than the default of 32. A more permanent solution is to increase your
   kernel's SEMMNS and SEMMNI parameters.
   
   Inoperative semaphores can also cause crashes during heavy database
   access.
493 494
   
   If the error message is something else, you might not have semaphore
Bruce Momjian's avatar
Bruce Momjian committed
495 496 497
   support configured in your kernel at all. See the PostgreSQL
   Administrator's Guide for more detailed information about shared
   memory and semaphores.
Bruce Momjian's avatar
Bruce Momjian committed
498
   
Bruce Momjian's avatar
Bruce Momjian committed
499
    3.5) How do I control connections from other hosts?
500
    
Bruce Momjian's avatar
Bruce Momjian committed
501
   By default, PostgreSQL only allows connections from the local machine
Bruce Momjian's avatar
Bruce Momjian committed
502
   using Unix domain sockets. Other machines will not be able to connect
Bruce Momjian's avatar
Bruce Momjian committed
503
   unless you add the -i flag to postmaster, and enable host-based
Bruce Momjian's avatar
Bruce Momjian committed
504
   authentication by modifying the file $PGDATA/pg_hba.conf accordingly.
505
   This will allow TCP/IP connections.
Bruce Momjian's avatar
Bruce Momjian committed
506
   
Bruce Momjian's avatar
Bruce Momjian committed
507
    3.6) How do I tune the database engine for better performance?
508
    
Bruce Momjian's avatar
Bruce Momjian committed
509
   Certainly, indexes can speed up queries. The EXPLAIN command allows
Bruce Momjian's avatar
Bruce Momjian committed
510
   you to see how PostgreSQL is interpreting your query, and which
Bruce Momjian's avatar
Bruce Momjian committed
511
   indexes are being used.
Bruce Momjian's avatar
Bruce Momjian committed
512
   
Bruce Momjian's avatar
Bruce Momjian committed
513 514 515 516 517 518 519
   If you are doing many INSERTs, consider doing them in a large batch
   using the COPY command. This is much faster than individual INSERTS.
   Second, statements not in a BEGIN WORK/COMMIT 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.
Bruce Momjian's avatar
Bruce Momjian committed
520
   
Bruce Momjian's avatar
Bruce Momjian committed
521
   There are several tuning options. You can disable fsync() by starting
Bruce Momjian's avatar
Bruce Momjian committed
522
   postmaster with a -o -F option. This will prevent fsync()s from
Bruce Momjian's avatar
Bruce Momjian committed
523
   flushing to disk after every transaction.
Bruce Momjian's avatar
Bruce Momjian committed
524 525
   
   You can also use the postmaster -B option to increase the number of
Bruce Momjian's avatar
Bruce Momjian committed
526
   shared memory buffers used by the backend processes. If you make this
Bruce Momjian's avatar
Bruce Momjian committed
527
   parameter too high, the postmaster may not start because you have
Bruce Momjian's avatar
Bruce Momjian committed
528 529
   exceeded your kernel's limit on shared memory space. Each buffer is 8K
   and the default is 64 buffers.
Bruce Momjian's avatar
Bruce Momjian committed
530
   
Bruce Momjian's avatar
Bruce Momjian committed
531
   You can also use the backend -S option to increase the maximum amount
Bruce Momjian's avatar
Bruce Momjian committed
532
   of memory used by the backend process for temporary sorts. The -S
Bruce Momjian's avatar
Bruce Momjian committed
533
   value is measured in kilobytes, and the default is 512 (i.e. 512K).
Bruce Momjian's avatar
Bruce Momjian committed
534
   
Bruce Momjian's avatar
Bruce Momjian committed
535
   You can also use the CLUSTER command to group data in tables to match
Bruce Momjian's avatar
Bruce Momjian committed
536
   an index. See the CLUSTER manual page for more details.
Bruce Momjian's avatar
Bruce Momjian committed
537
   
Bruce Momjian's avatar
Bruce Momjian committed
538
    3.7) What debugging features are available?
539
    
Bruce Momjian's avatar
Bruce Momjian committed
540 541 542
   PostgreSQL has several features that report status information that
   can be valuable for debugging purposes.
   
Bruce Momjian's avatar
Bruce Momjian committed
543
   First, by running configure with the --enable-cassert option, many
Bruce Momjian's avatar
Bruce Momjian committed
544
   assert()s monitor the progress of the backend and halt the program
Bruce Momjian's avatar
Bruce Momjian committed
545 546 547
   when something unexpected occurs.
   
   Both postmaster and postgres have several debug options available.
Bruce Momjian's avatar
Bruce Momjian committed
548 549
   First, whenever you start postmaster, make sure you send the standard
   output and error to a log file, like:
Bruce Momjian's avatar
Bruce Momjian committed
550 551
    cd /usr/local/pgsql
    ./bin/postmaster >server.log 2>&1 &
Bruce Momjian's avatar
Bruce Momjian committed
552 553

   This will put a server.log file in the top-level PostgreSQL directory.
Bruce Momjian's avatar
Bruce Momjian committed
554
   This file contains useful information about problems or errors
Bruce Momjian's avatar
Bruce Momjian committed
555 556
   encountered by the server. Postmaster has a -d option that allows even
   more detailed information to be reported. The -d option takes a number
Bruce Momjian's avatar
Bruce Momjian committed
557
   that specifies the debug level. Be warned that high debug level values
Bruce Momjian's avatar
Bruce Momjian committed
558
   generate large log files.
Bruce Momjian's avatar
Bruce Momjian committed
559
   
Bruce Momjian's avatar
Bruce Momjian committed
560
   If postmaster is not running, you can actually run the postgres
Bruce Momjian's avatar
Bruce Momjian committed
561 562 563 564
   backend from the command line, and type your SQL statement directly.
   This is recommended only 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.
Bruce Momjian's avatar
Bruce Momjian committed
565 566 567
   Because the backend was not started from postmaster, it is not running
   in an identical environment and locking/backend interaction problems
   may not be duplicated.
Bruce Momjian's avatar
Bruce Momjian committed
568
   
Bruce Momjian's avatar
Bruce Momjian committed
569 570 571
   If postmaster is running, start psql in one window, then find the PID
   of the postgres process used by psql. Use a debugger to attach to the
   postgres PID. You can set breakpoints in the debugger and issue
Bruce Momjian's avatar
Bruce Momjian committed
572 573
   queries from psql. If you are debugging postgres startup, you can set
   PGOPTIONS="-W n", then start psql. This will cause startup to delay
Bruce Momjian's avatar
Bruce Momjian committed
574 575
   for n seconds so you can attach to the process with the debugger, set
   any breakpoints, and continue through the startup sequence.
Bruce Momjian's avatar
Bruce Momjian committed
576
   
Bruce Momjian's avatar
Bruce Momjian committed
577 578
   The postgres program has -s, -A, and -t options that can be very
   useful for debugging and performance measurements.
Bruce Momjian's avatar
Bruce Momjian committed
579 580 581 582
   
   You can also compile with profiling to see what functions are taking
   execution time. The backend profile files will be deposited in the
   pgsql/data/base/dbname directory. The client profile file will be put
583 584
   in the client's current directory. Linux requires a compile with
   -DLINUX_PROFILE for proper profiling.
Bruce Momjian's avatar
Bruce Momjian committed
585
   
Bruce Momjian's avatar
Bruce Momjian committed
586
    3.8) Why do I get "Sorry, too many clients" when trying to connect?
587
    
Bruce Momjian's avatar
Bruce Momjian committed
588 589
   You need to increase postmaster's limit on how many concurrent backend
   processes it can start.
Bruce Momjian's avatar
Bruce Momjian committed
590
   
591
   The default limit is 32 processes. You can increase it by restarting
Bruce Momjian's avatar
Bruce Momjian committed
592
   postmaster with a suitable -N value or modifying postgresql.conf.
Bruce Momjian's avatar
Bruce Momjian committed
593 594 595 596 597 598
   
   Note that if you make -N larger than 32, you must also increase -B
   beyond its default of 64; -B must be at least twice -N, 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
Bruce Momjian's avatar
Bruce Momjian committed
599 600 601
   include the maximum size of shared memory blocks, SHMMAX; the maximum
   number of semaphores, SEMMNS and SEMMNI; the maximum number of
   processes, NPROC; the maximum number of processes per user, MAXUPRC;
Bruce Momjian's avatar
Bruce Momjian committed
602
   and the maximum number of open files, NFILE and NINODE. The reason
Bruce Momjian's avatar
Bruce Momjian committed
603
   that PostgreSQL has a limit on the number of allowed backend processes
Bruce Momjian's avatar
Bruce Momjian committed
604
   is so your system won't run out of resources.
Bruce Momjian's avatar
Bruce Momjian committed
605
   
Bruce Momjian's avatar
Bruce Momjian committed
606
    3.9) What is in the pgsql_tmp directory?
607
    
Bruce Momjian's avatar
Bruce Momjian committed
608 609 610 611
   This directory contains temporary files generated by the query
   executor. For example, if a sort needs to be done to satisfy an ORDER
   BY and the sort requires more space than the backend's -S parameter
   allows, then temporary files are created here to hold the extra data.
Bruce Momjian's avatar
Bruce Momjian committed
612
   
613 614 615
   The temporary files are usually deleted automatically, but might
   remain if a backend crashes during a sort. A stop and restart of the
   postmaster will remove files from those directories.
Bruce Momjian's avatar
Bruce Momjian committed
616
   
617 618
    3.10) Why do I need to do a dump and restore to upgrade between major
    PostgreSQL releases?
Bruce Momjian's avatar
Bruce Momjian committed
619
    
620 621
   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.
622 623 624 625 626
   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.
627 628 629 630
   
   In releases where the on-disk format does not change, the pg_upgrade
   script can be used to upgrade without a dump/restore. The release
   notes mention whether pg_upgrade is available for the release.
631
     _________________________________________________________________
Bruce Momjian's avatar
Bruce Momjian committed
632
   
633 634
                           Operational Questions
                                      
Bruce Momjian's avatar
Bruce Momjian committed
635
    4.1) What is the difference between binary cursors and normal cursors?
636
    
Bruce Momjian's avatar
Bruce Momjian committed
637
   See the DECLARE manual page for a description.
Bruce Momjian's avatar
Bruce Momjian committed
638
   
639
    4.2) How do I SELECT only the first few rows of a query? A random row?
640
    
Bruce Momjian's avatar
Bruce Momjian committed
641
   See the FETCH manual page, or use SELECT ... LIMIT....
Bruce Momjian's avatar
Bruce Momjian committed
642
   
Bruce Momjian's avatar
Bruce Momjian committed
643
   The entire query may have to be evaluated, even if you only want the
644 645 646 647
   first few rows. Consider using a query that has an ORDER BY. If there
   is an index that matches the ORDER BY, 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.
Bruce Momjian's avatar
Bruce Momjian committed
648
   
649 650 651 652 653 654
   To SELECT a random row, use:
    SELECT col
    FROM tab
    ORDER BY random()
    LIMIT 1;

Bruce Momjian's avatar
Bruce Momjian committed
655
    4.3) How do I get a list of tables or other things I can see in psql?
656
    
Bruce Momjian's avatar
Bruce Momjian committed
657
   You can read the source code for psql in file
Bruce Momjian's avatar
Bruce Momjian committed
658 659 660
   pgsql/src/bin/psql/describe.c. It contains SQL commands that generate
   the output for psql's backslash commands. You can also start psql with
   the -E option so it will print out the queries it uses to execute the
Bruce Momjian's avatar
Bruce Momjian committed
661
   commands you give.
Bruce Momjian's avatar
Bruce Momjian committed
662
   
663
    4.4) How do you remove a column from a table, or change its data type?
664
    
665 666
   DROP COLUMN functionality was added in release 7.3 with ALTER TABLE
   DROP COLUMN. In earlier versions, you can do this:
667 668
    BEGIN;
    LOCK TABLE old_table;
Bruce Momjian's avatar
Bruce Momjian committed
669 670 671 672 673
    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;
674
    COMMIT;
675

676 677 678 679
   To change the data type of a column, do this:
    BEGIN;
    ALTER TABLE tab ADD COLUMN new_col new_data_type;
    UPDATE tab SET new_col = CAST(old_col AS new_data_type);
Bruce Momjian's avatar
Bruce Momjian committed
680
    ALTER TABLE tab DROP COLUMN old_col;
681 682 683 684 685
    COMMIT;

   You might then want to do VACUUM FULL tab to reclaim the disk space
   used by the expired rows.
   
Bruce Momjian's avatar
Bruce Momjian committed
686
    4.5) What is the maximum size for a row, a table, and a database?
687
    
Bruce Momjian's avatar
Bruce Momjian committed
688
   These are the limits:
689
    Maximum size for a database?             unlimited (32 TB databases exist)
690
    Maximum size for a table?                32 TB
Bruce Momjian's avatar
Bruce Momjian committed
691 692
    Maximum size for a row?                  1.6TB
    Maximum size for a field?                1 GB
Bruce Momjian's avatar
Bruce Momjian committed
693 694 695
    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
Bruce Momjian's avatar
Bruce Momjian committed
696 697

   Of course, these are not actually unlimited, but limited to available
Bruce Momjian's avatar
Bruce Momjian committed
698 699
   disk space and memory/swap space. Performance may suffer when these
   values get unusually large.
Bruce Momjian's avatar
Bruce Momjian committed
700
   
701
   The maximum table size of 32 TB does not require large file support
Bruce Momjian's avatar
Bruce Momjian committed
702
   from the operating system. Large tables are stored as multiple 1 GB
Bruce Momjian's avatar
Bruce Momjian committed
703
   files so file system size limits are not important.
Bruce Momjian's avatar
Bruce Momjian committed
704
   
705 706
   The maximum table size and maximum number of columns can be quadrupled
   by increasing the default block size to 32k.
Bruce Momjian's avatar
Bruce Momjian committed
707
   
Bruce Momjian's avatar
Bruce Momjian committed
708
    4.6) How much database disk space is required to store data from a typical
Bruce Momjian's avatar
Bruce Momjian committed
709
    text file?
710
    
Bruce Momjian's avatar
Bruce Momjian committed
711 712
   A PostgreSQL database may require up to five times the disk space to
   store data from a text file.
713
   
Bruce Momjian's avatar
Bruce Momjian committed
714 715
   As an example, consider a file of 100,000 lines with an integer and
   text description on each line. Suppose the text string avergages
Bruce Momjian's avatar
Bruce Momjian committed
716 717 718
   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:
719
    36 bytes: each row header (approximate)
720
    24 bytes: one int field and one text field
721 722
   + 4 bytes: pointer on page to tuple
   ----------------------------------------
Bruce Momjian's avatar
Bruce Momjian committed
723
    64 bytes per row
724 725 726 727

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

   8192 bytes per page
Bruce Momjian's avatar
Bruce Momjian committed
728 729
   -------------------   =  128 rows per database page (rounded down)
     64 bytes per row
730

Bruce Momjian's avatar
Bruce Momjian committed
731
   100000 data rows
Bruce Momjian's avatar
Bruce Momjian committed
732 733
   --------------------  =  782 database pages (rounded up)
      128 rows per page
734

Bruce Momjian's avatar
Bruce Momjian committed
735
782 database pages * 8192 bytes per page  =  6,406,144 bytes (6.4 MB)
736

Bruce Momjian's avatar
Bruce Momjian committed
737
   Indexes do not require as much overhead, but do contain the data that
738 739
   is being indexed, so they can be large also.
   
Bruce Momjian's avatar
Bruce Momjian committed
740 741
   NULLs are stored in bitmaps, so they use very little space.
   
742 743
    4.7) How do I find out what tables, indexes, databases, and users are
    defined?
744 745
    
   psql has a variety of backslash commands to show such information. Use
746 747
   \? to see them. There are also system tables beginning with pg_ that
   describe these too. Also, psql -l will list all databases.
748 749
   
   Also try the file pgsql/src/tutorial/syscat.source. It illustrates
Bruce Momjian's avatar
Bruce Momjian committed
750
   many of the SELECTs needed to get information from the database system
751
   tables.
Bruce Momjian's avatar
Bruce Momjian committed
752
   
Bruce Momjian's avatar
Bruce Momjian committed
753
    4.8) My queries are slow or don't make use of the indexes. Why?
754
    
Bruce Momjian's avatar
Bruce Momjian committed
755
   Indexes are not automatically used by every query. Indexes are only
Bruce Momjian's avatar
Bruce Momjian committed
756
   used if the table is larger than a minimum size, and the query selects
Bruce Momjian's avatar
Bruce Momjian committed
757
   only a small percentage of the rows in the table. This is because the
758
   random disk access caused by an index scan can be slower than a
Bruce Momjian's avatar
Bruce Momjian committed
759 760 761 762 763 764 765 766 767 768 769 770 771 772
   straight read through the table, or sequential scan.
   
   To determine if an index should be used, PostgreSQL must have
   statistics about the table. These statistics are collected using
   VACUUM ANALYZE, or simply ANALYZE. 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.
   
   Indexes are normally not used for ORDER BY or to perform joins. A
   sequential scan followed by an explicit sort is usually faster than an
   index scan of a large table.
   However, LIMIT combined with ORDER BY often will use an index because
773 774 775 776 777
   only a small portion of the table 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:
    SELECT col
    FROM tab
Bruce Momjian's avatar
Bruce Momjian committed
778
    ORDER BY col [ DESC ]
779
    LIMIT 1;
780

781 782 783 784
   If you believe the optimizer is incorrect in choosing a sequential
   scan, use SET enable_seqscan TO 'off' and run tests to see if an index
   scan is indeed faster.
   
Bruce Momjian's avatar
Bruce Momjian committed
785
   When using wild-card operators such as LIKE or ~, indexes can only be
786 787
   used in certain circumstances:
     * The beginning of the search string must be anchored to the start
Bruce Momjian's avatar
Bruce Momjian committed
788 789 790 791 792 793 794 795 796
       of the string, i.e.
          + LIKE patterns must not start with %.
          + ~ (regular expression) patterns must start with ^.
     * The search string can not start with a character class, e.g.
       [a-e].
     * Case-insensitive searches such as ILIKE and ~* do not utilise
       indexes. Instead, use functional indexes, which are described in
       section 4.12.
     * The default C locale must be used during initdb.
797
       
Bruce Momjian's avatar
Bruce Momjian committed
798
    4.9) How do I see how the query optimizer is evaluating my query?
799
    
Bruce Momjian's avatar
Bruce Momjian committed
800
   See the EXPLAIN manual page.
801
   
Bruce Momjian's avatar
Bruce Momjian committed
802
    4.10) What is an R-tree index?
803
    
Bruce Momjian's avatar
Bruce Momjian committed
804
   An R-tree index is used for indexing spatial data. A hash index can't
805
   handle range searches. A B-tree index only handles range searches in a
Bruce Momjian's avatar
Bruce Momjian committed
806
   single dimension. R-trees can handle multi-dimensional data. For
807
   example, if an R-tree index can be built on an attribute of type
Bruce Momjian's avatar
Bruce Momjian committed
808 809
   point, the system can more efficiently answer queries such as "select
   all points within a bounding rectangle."
810
   
Bruce Momjian's avatar
Bruce Momjian committed
811
   The canonical paper that describes the original R-tree design is:
812
   
Bruce Momjian's avatar
Bruce Momjian committed
813
   Guttman, A. "R-trees: A Dynamic Index Structure for Spatial
Bruce Momjian's avatar
Bruce Momjian committed
814 815
   Searching." Proceedings of the 1984 ACM SIGMOD Int'l Conf on Mgmt of
   Data, 45-57.
816 817
   
   You can also find this paper in Stonebraker's "Readings in Database
Bruce Momjian's avatar
Bruce Momjian committed
818
   Systems".
819
   
Bruce Momjian's avatar
Bruce Momjian committed
820
   Built-in R-trees can handle polygons and boxes. In theory, R-trees can
821
   be extended to handle higher number of dimensions. In practice,
Bruce Momjian's avatar
Bruce Momjian committed
822
   extending R-trees requires a bit of work and we don't currently have
823 824
   any documentation on how to do it.
   
Bruce Momjian's avatar
Bruce Momjian committed
825
    4.11) What is the Genetic Query Optimizer?
826
    
Bruce Momjian's avatar
Bruce Momjian committed
827 828
   The GEQO module speeds query optimization when joining many tables by
   means of a Genetic Algorithm (GA). It allows the handling of large
Bruce Momjian's avatar
Bruce Momjian committed
829
   join queries through nonexhaustive search.
Bruce Momjian's avatar
Bruce Momjian committed
830
   
Bruce Momjian's avatar
Bruce Momjian committed
831
    4.12) How do I perform regular expression searches and case-insensitive
Bruce Momjian's avatar
Bruce Momjian committed
832 833
    regular expression searches? How do I use an index for case-insensitive
    searches?
834
    
Bruce Momjian's avatar
Bruce Momjian committed
835
   The ~ operator does regular expression matching, and ~* does
Bruce Momjian's avatar
Bruce Momjian committed
836
   case-insensitive regular expression matching. The case-insensitive
837
   variant of LIKE is called ILIKE.
Bruce Momjian's avatar
Bruce Momjian committed
838
   
Bruce Momjian's avatar
Bruce Momjian committed
839 840 841
   Case-insensitive equality comparisons are normally expressed as:
    SELECT *
    FROM tab
842
    WHERE lower(col) = 'abc';
Bruce Momjian's avatar
Bruce Momjian committed
843 844 845

   This will not use an standard index. However, if you create a
   functional index, it will be used:
846
    CREATE INDEX tabindex ON tab (lower(col));
Bruce Momjian's avatar
Bruce Momjian committed
847

Bruce Momjian's avatar
Bruce Momjian committed
848
    4.13) In a query, how do I detect if a field is NULL?
849
    
Bruce Momjian's avatar
Bruce Momjian committed
850
   You test the column with IS NULL and IS NOT NULL.
851
   
Bruce Momjian's avatar
Bruce Momjian committed
852
    4.14) What is the difference between the various character types?
853
    
Bruce Momjian's avatar
Bruce Momjian committed
854
Type            Internal Name   Notes
Marc G. Fournier's avatar
Marc G. Fournier committed
855
--------------------------------------------------
856
VARCHAR(n)      varchar         size specifies maximum length, no padding
Bruce Momjian's avatar
Bruce Momjian committed
857
CHAR(n)         bpchar          blank padded to the specified fixed length
858
TEXT            text            no specific upper limit on length
Bruce Momjian's avatar
Bruce Momjian committed
859
BYTEA           bytea           variable-length byte array (null-byte safe)
860
"char"          char            one character
Bruce Momjian's avatar
Bruce Momjian committed
861

Bruce Momjian's avatar
Bruce Momjian committed
862 863
   You will see the internal name when examining system catalogs and in
   some error messages.
Bruce Momjian's avatar
Bruce Momjian committed
864
   
865
   The first four types above are "varlena" types (i.e., the first four
Bruce Momjian's avatar
Bruce Momjian committed
866 867
   bytes on disk are the length, followed by the data). Thus the actual
   space used is slightly greater than the declared size. However, these
868 869
   data types are also subject to compression or being stored out-of-line
   by TOAST, so the space on disk might also be less than expected.
Bruce Momjian's avatar
Bruce Momjian committed
870
   VARCHAR(n) is best when storing variable-length strings and it limits
Bruce Momjian's avatar
Bruce Momjian committed
871
   how long a string can be. TEXT is for strings of unlimited length,
Bruce Momjian's avatar
Bruce Momjian committed
872
   with a maximum of one gigabyte.
Bruce Momjian's avatar
Bruce Momjian committed
873 874
   
   CHAR(n) is for storing strings that are all the same length. CHAR(n)
Bruce Momjian's avatar
Bruce Momjian committed
875 876
   pads with blanks to the specified length, while VARCHAR(n) only stores
   the characters supplied. BYTEA is for storing binary data,
877 878
   particularly values that include NULL bytes. All the types described
   here have similar performance characteristics.
Bruce Momjian's avatar
Bruce Momjian committed
879
   
Bruce Momjian's avatar
Bruce Momjian committed
880
    4.15.1) How do I create a serial/auto-incrementing field?
Bruce Momjian's avatar
Bruce Momjian committed
881
    
Bruce Momjian's avatar
Bruce Momjian committed
882
   PostgreSQL supports a SERIAL data type. It auto-creates a sequence and
Bruce Momjian's avatar
Bruce Momjian committed
883
   index on the column. For example, this:
Bruce Momjian's avatar
Bruce Momjian committed
884 885 886 887
    CREATE TABLE person (
        id   SERIAL,
        name TEXT
    );
Bruce Momjian's avatar
Bruce Momjian committed
888

Bruce Momjian's avatar
Bruce Momjian committed
889
   is automatically translated into this:
Bruce Momjian's avatar
Bruce Momjian committed
890 891 892 893 894 895
    CREATE SEQUENCE person_id_seq;
    CREATE TABLE person (
        id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),
        name TEXT
    );
    CREATE UNIQUE INDEX person_id_key ON person ( id );
Bruce Momjian's avatar
Bruce Momjian committed
896 897

   See the create_sequence manual page for more information about
Bruce Momjian's avatar
Bruce Momjian committed
898
   sequences. You can also use each row's OID field as a unique value.
Bruce Momjian's avatar
Bruce Momjian committed
899
   However, if you need to dump and reload the database, you need to use
Bruce Momjian's avatar
Bruce Momjian committed
900
   pg_dump's -o option or COPY WITH OIDS option to preserve the OIDs.
Bruce Momjian's avatar
Bruce Momjian committed
901
   
Bruce Momjian's avatar
Bruce Momjian committed
902
    4.15.2) How do I get the value of a SERIAL insert?
903
    
Bruce Momjian's avatar
Bruce Momjian committed
904
   One approach is to retrieve the next SERIAL value from the sequence
Bruce Momjian's avatar
Bruce Momjian committed
905
   object with the nextval() function before inserting and then insert it
906 907 908 909
   explicitly. Using the example table in 4.15.1, an example in a
   pseudo-language would look like this:
    new_id = execute("SELECT nextval('person_id_seq')");
    execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
Bruce Momjian's avatar
Bruce Momjian committed
910

Bruce Momjian's avatar
Bruce Momjian committed
911 912 913 914 915
   You would then also have the new value stored in new_id for use in
   other queries (e.g., as a foreign key to the person table). Note that
   the name of the automatically created SEQUENCE object will be named
   <table>_<serialcolumn>_seq, where table and serialcolumn are the names
   of your table and your SERIAL column, respectively.
Bruce Momjian's avatar
Bruce Momjian committed
916
   
Bruce Momjian's avatar
Bruce Momjian committed
917 918
   Alternatively, you could retrieve the assigned SERIAL value with the
   currval() function after it was inserted by default, e.g.,
919 920
    execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
    new_id = execute("SELECT currval('person_id_seq')");
Bruce Momjian's avatar
Bruce Momjian committed
921

Bruce Momjian's avatar
Bruce Momjian committed
922
   Finally, you could use the OID returned from the INSERT statement to
Bruce Momjian's avatar
Bruce Momjian committed
923
   look up the default value, though this is probably the least portable
924 925
   approach, and the oid value will wrap around when it reaches 4
   billion. In Perl, using DBI with Edmund Mergl's DBD::Pg module, the
Bruce Momjian's avatar
Bruce Momjian committed
926 927 928
   oid value is made available via $sth->{pg_oid_status} after
   $sth->execute().
   
Bruce Momjian's avatar
Bruce Momjian committed
929
    4.15.3) Don't currval() and nextval() lead to a race condition with other
Bruce Momjian's avatar
Bruce Momjian committed
930
    users?
Bruce Momjian's avatar
Bruce Momjian committed
931
    
932
   No. currval() returns the current value assigned by your backend, not
Bruce Momjian's avatar
Bruce Momjian committed
933
   by all users.
Bruce Momjian's avatar
Bruce Momjian committed
934
   
Bruce Momjian's avatar
Bruce Momjian committed
935
    4.15.4) Why aren't my sequence numbers reused on transaction abort? Why are
Bruce Momjian's avatar
Bruce Momjian committed
936
    there gaps in the numbering of my sequence/SERIAL column?
Bruce Momjian's avatar
Bruce Momjian committed
937 938
    
   To improve concurrency, sequence values are given out to running
Bruce Momjian's avatar
Bruce Momjian committed
939
   transactions as needed and are not locked until the transaction
Bruce Momjian's avatar
Bruce Momjian committed
940 941
   completes. This causes gaps in numbering from aborted transactions.
   
Bruce Momjian's avatar
Bruce Momjian committed
942
    4.16) What is an OID? What is a TID?
943
    
Bruce Momjian's avatar
Bruce Momjian committed
944
   OIDs are PostgreSQL's answer to unique row ids. Every row that is
Bruce Momjian's avatar
Bruce Momjian committed
945
   created in PostgreSQL gets a unique OID. All OIDs generated during
946
   initdb are less than 16384 (from include/access/transam.h). All
Bruce Momjian's avatar
Bruce Momjian committed
947 948
   user-created OIDs are equal to or greater than this. By default, all
   these OIDs are unique not only within a table or database, but unique
Bruce Momjian's avatar
Bruce Momjian committed
949
   within the entire PostgreSQL installation.
Bruce Momjian's avatar
Bruce Momjian committed
950
   
Bruce Momjian's avatar
Bruce Momjian committed
951 952 953 954
   PostgreSQL uses OIDs in its internal system tables to link rows
   between tables. These OIDs can be used to identify specific user rows
   and used in joins. It is recommended you use column type OID to store
   OID values. You can create an index on the OID field for faster
Bruce Momjian's avatar
Bruce Momjian committed
955
   access.
Bruce Momjian's avatar
Bruce Momjian committed
956
   
Bruce Momjian's avatar
Bruce Momjian committed
957
   OIDs are assigned to all new rows from a central area that is used by
Bruce Momjian's avatar
Bruce Momjian committed
958
   all databases. If you want to change the OID to something else, or if
Bruce Momjian's avatar
Bruce Momjian committed
959 960
   you want to make a copy of the table, with the original OIDs, there is
   no reason you can't do it:
Bruce Momjian's avatar
Bruce Momjian committed
961
        CREATE TABLE new_table(old_oid oid, mycol int);
Bruce Momjian's avatar
Bruce Momjian committed
962
        SELECT old_oid, mycol INTO new FROM old;
Bruce Momjian's avatar
Bruce Momjian committed
963 964 965
        COPY new TO '/tmp/pgtable';
        DELETE FROM new;
        COPY new WITH OIDS FROM '/tmp/pgtable';
Bruce Momjian's avatar
Bruce Momjian committed
966

Bruce Momjian's avatar
Bruce Momjian committed
967
   OIDs are stored as 4-byte integers, and will overflow at 4 billion. No
Bruce Momjian's avatar
Bruce Momjian committed
968
   one has reported this ever happening, and we plan to have the limit
Bruce Momjian's avatar
Bruce Momjian committed
969 970
   removed before anyone does.
   
Bruce Momjian's avatar
Bruce Momjian committed
971
   TIDs are used to identify specific physical rows with block and offset
Bruce Momjian's avatar
Bruce Momjian committed
972
   values. TIDs change after rows are modified or reloaded. They are used
Bruce Momjian's avatar
Bruce Momjian committed
973
   by index entries to point to physical rows.
Bruce Momjian's avatar
Bruce Momjian committed
974
   
Bruce Momjian's avatar
Bruce Momjian committed
975
    4.17) What is the meaning of some of the terms used in PostgreSQL?
976
    
Bruce Momjian's avatar
Bruce Momjian committed
977 978
   Some of the source code and older documentation use terms that have
   more common usage. Here are some:
Bruce Momjian's avatar
Bruce Momjian committed
979
     * table, relation, class
Bruce Momjian's avatar
Bruce Momjian committed
980
     * row, record, tuple
Bruce Momjian's avatar
Bruce Momjian committed
981
     * column, field, attribute
Bruce Momjian's avatar
Bruce Momjian committed
982 983 984
     * retrieve, select
     * replace, update
     * append, insert
Bruce Momjian's avatar
Bruce Momjian committed
985
     * OID, serial value
Bruce Momjian's avatar
Bruce Momjian committed
986 987 988
     * portal, cursor
     * range variable, table name, table alias
       
Bruce Momjian's avatar
Bruce Momjian committed
989
   A list of general database terms can be found at:
990 991
   http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary
   /glossary.html
Bruce Momjian's avatar
Bruce Momjian committed
992
   
Bruce Momjian's avatar
Bruce Momjian committed
993
    4.18) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
Bruce Momjian's avatar
Bruce Momjian committed
994
    
995 996 997
   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
   postmaster:
Bruce Momjian's avatar
Bruce Momjian committed
998 999 1000 1001 1002 1003 1004 1005 1006 1007
    ulimit -d 262144
    limit datasize 256m

   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 SQL client because the backend is returning too much
   data, try it before starting the client.
   
Bruce Momjian's avatar
Bruce Momjian committed
1008
    4.19) How do I tell what PostgreSQL version I am running?
1009
    
1010
   From psql, type SELECT version();
Bruce Momjian's avatar
Bruce Momjian committed
1011
   
Bruce Momjian's avatar
Bruce Momjian committed
1012 1013
    4.20) Why does my large-object operations get "invalid large obj
    descriptor"?
Bruce Momjian's avatar
Bruce Momjian committed
1014 1015 1016 1017
    
   You need to put BEGIN WORK and COMMIT around any use of a large object
   handle, that is, surrounding lo_open ... lo_close.
   
Bruce Momjian's avatar
Bruce Momjian committed
1018 1019 1020 1021 1022
   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 invalid large obj descriptor. 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.
Bruce Momjian's avatar
Bruce Momjian committed
1023 1024 1025 1026
   
   If you are using a client interface like ODBC you may need to set
   auto-commit off.
   
Bruce Momjian's avatar
Bruce Momjian committed
1027
    4.21) How do I create a column that will default to the current time?
Bruce Momjian's avatar
Bruce Momjian committed
1028
    
Bruce Momjian's avatar
Bruce Momjian committed
1029 1030
   Use CURRENT_TIMESTAMP:
CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
Bruce Momjian's avatar
Bruce Momjian committed
1031

Bruce Momjian's avatar
Bruce Momjian committed
1032
    4.22) Why are my subqueries using IN so slow?
Bruce Momjian's avatar
Bruce Momjian committed
1033
    
Bruce Momjian's avatar
Bruce Momjian committed
1034
   Currently, we join subqueries to outer queries by sequentially
Bruce Momjian's avatar
Bruce Momjian committed
1035 1036 1037 1038
   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, IN is fastest. To speed up other queries, replace IN with
   EXISTS:
1039
    SELECT *
Bruce Momjian's avatar
Bruce Momjian committed
1040
    FROM tab
1041
    WHERE col IN (SELECT subcol FROM subtab);
Bruce Momjian's avatar
Bruce Momjian committed
1042 1043

   to:
1044
    SELECT *
Bruce Momjian's avatar
Bruce Momjian committed
1045
    FROM tab
1046
    WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col);
Bruce Momjian's avatar
Bruce Momjian committed
1047

1048 1049
   For this to be fast, subcol should be an indexed column. This
   preformance problem will be fixed in 7.4.
Bruce Momjian's avatar
Bruce Momjian committed
1050
   
Bruce Momjian's avatar
Bruce Momjian committed
1051
    4.23) How do I perform an outer join?
Bruce Momjian's avatar
Bruce Momjian committed
1052
    
1053 1054
   PostgreSQL supports outer joins using the SQL standard syntax. Here
   are two examples:
Bruce Momjian's avatar
Bruce Momjian committed
1055 1056 1057 1058
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);

   or
Bruce Momjian's avatar
Bruce Momjian committed
1059 1060 1061
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 USING (col);

Bruce Momjian's avatar
Bruce Momjian committed
1062 1063 1064 1065 1066 1067
   These identical queries join t1.col to t2.col, and also return any
   unjoined rows in t1 (those with no match in t2). A RIGHT join would
   add unjoined rows of t2. A FULL join would return the matched rows
   plus all unjoined rows from t1 and t2. The word OUTER is optional and
   is assumed in LEFT, RIGHT, and FULL joins. Ordinary joins are called
   INNER joins.
Bruce Momjian's avatar
Bruce Momjian committed
1068
   
Bruce Momjian's avatar
Bruce Momjian committed
1069 1070 1071
   In previous releases, outer joins can be simulated using UNION and NOT
   IN. For example, when joining tab1 and tab2, the following query does
   an outer join of the two tables:
Bruce Momjian's avatar
Bruce Momjian committed
1072 1073 1074 1075 1076 1077 1078
    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
1079
    ORDER BY col1
Bruce Momjian's avatar
Bruce Momjian committed
1080

Bruce Momjian's avatar
Bruce Momjian committed
1081
    4.24) How do I perform queries using multiple databases?
Bruce Momjian's avatar
Bruce Momjian committed
1082
    
1083 1084 1085
   There is no way to query a database other than the current one.
   Because PostgreSQL loads database-specific system catalogs, it is
   uncertain how a cross-database query should even behave.
Bruce Momjian's avatar
Bruce Momjian committed
1086
   
Bruce Momjian's avatar
Bruce Momjian committed
1087
   contrib/dblink allows cross-database queries using function calls. Of
1088 1089
   course, a client can make simultaneous connections to different
   databases and merge the results on the client side.
Bruce Momjian's avatar
Bruce Momjian committed
1090
   
Bruce Momjian's avatar
Bruce Momjian committed
1091 1092
    4.25) How do I return multiple rows or columns from a function?
    
1093 1094
   In 7.3, you can easily return multiple rows or columns from a
   function, http://techdocs.postgresql.org/guides/SetReturningFunctions.
Bruce Momjian's avatar
Bruce Momjian committed
1095
   
1096 1097 1098 1099 1100 1101 1102 1103 1104 1105
    4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
    functions?
    
   PL/PgSQL caches function contents, and an unfortunate side effect is
   that if a PL/PgSQL function accesses a temporary table, and that table
   is later dropped and recreated, and the function called again, the
   function will fail because the cached function contents still point to
   the old temporary table. The solution is to use EXECUTE for temporary
   table access in PL/PgSQL. This will cause the query to be reparsed
   every time.
1106 1107 1108
   
    4.27) What replication options are available?
    
Bruce Momjian's avatar
Bruce Momjian committed
1109
   There are several master/slave replication options available. These
1110 1111
   allow only the master to make database changes and the slave can only
   do database reads. The bottom of
1112
   http://gborg.PostgreSQL.org/genpage?replication_research lists them. A
1113
   multi-master replication solution is being worked on at
1114
   http://gborg.PostgreSQL.org/project/pgreplication/projdisplay.php.
1115
   
1116
    4.28) What encryption options are available?
1117
    
Bruce Momjian's avatar
Bruce Momjian committed
1118 1119
     * contrib/pgcrypto contains many encryption functions for use in SQL
       queries.
1120 1121 1122 1123 1124 1125
     * To encrypt transmission from the client to the server, the server
       must have the ssl option set to true in postgresql.conf, and an
       applicable host or hostssl record must exist in pg_hba.conf, and
       the client sslmode must not be disable. (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.)
1126
     * Database user passwords are automatically encrypted when stored in
1127 1128
       version 7.3. In previous versions, you must enable the option
       PASSWORD_ENCRYPTION in postgresql.conf.
Bruce Momjian's avatar
Bruce Momjian committed
1129
     * The server can run using an encrypted file system.
1130
     _________________________________________________________________
1131
   
1132 1133 1134 1135 1136
                            Extending PostgreSQL
                                      
    5.1) I wrote a user-defined function. When I run it in psql, why does it
    dump core?
    
Bruce Momjian's avatar
Bruce Momjian committed
1137
   The problem could be a number of things. Try testing your user-defined
Bruce Momjian's avatar
Bruce Momjian committed
1138
   function in a stand-alone test program first.
Bruce Momjian's avatar
Bruce Momjian committed
1139
   
Bruce Momjian's avatar
Bruce Momjian committed
1140
    5.2) How can I contribute some nifty new types and functions to PostgreSQL?
1141 1142 1143
    
   Send your extensions to the pgsql-hackers mailing list, and they will
   eventually end up in the contrib/ subdirectory.
Bruce Momjian's avatar
Bruce Momjian committed
1144
   
Bruce Momjian's avatar
Bruce Momjian committed
1145
    5.3) How do I write a C function to return a tuple?
1146
    
1147 1148 1149 1150 1151
   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
   contrib/tablefunc.
Bruce Momjian's avatar
Bruce Momjian committed
1152
   
Bruce Momjian's avatar
Bruce Momjian committed
1153
    5.4) I have changed a source file. Why does the recompile not see the
1154 1155 1156
    change?
    
   The Makefiles do not have the proper dependencies for include files.
Bruce Momjian's avatar
Bruce Momjian committed
1157 1158 1159
   You have to do a make clean and then another make. If you are using
   GCC you can use the --enable-depend option of configure to have the
   compiler compute the dependencies automatically.