FAQ 51 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
                                       
Bruce Momjian's avatar
Bruce Momjian committed
4
   Last updated: Tue Aug 31 23:28:02 EDT 2004
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?
Bruce Momjian's avatar
Bruce Momjian committed
57
   3.11) What computer hardware should I use?
58 59
   
                           Operational Questions
Bruce Momjian's avatar
Bruce Momjian committed
60
                                      
Bruce Momjian's avatar
Bruce Momjian committed
61
   4.1) What is the difference between binary cursors and normal cursors?
62
   4.2) How do I SELECT only the first few rows of a query? A random row?
Bruce Momjian's avatar
Bruce Momjian committed
63
   4.3) How do I get a list of tables or other things I can see in psql?
64 65
   4.4) How do you remove a column from a table, or change it's data
   type?
Bruce Momjian's avatar
Bruce Momjian committed
66
   4.5) What is the maximum size for a row, a table, and a database?
Bruce Momjian's avatar
Bruce Momjian committed
67
   4.6) How much database disk space is required to store data from a
Bruce Momjian's avatar
Bruce Momjian committed
68
   typical text file?
69 70
   4.7) How do I find out what tables, indexes, databases, and users are
   defined?
Bruce Momjian's avatar
Bruce Momjian committed
71 72 73 74 75
   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
76 77
   case-insensitive regular expression searches? How do I use an index
   for case-insensitive searches?
Bruce Momjian's avatar
Bruce Momjian committed
78 79 80 81 82
   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
83
   other users?
Bruce Momjian's avatar
Bruce Momjian committed
84
   4.15.4) Why aren't my sequence numbers reused on transaction abort?
Bruce Momjian's avatar
Bruce Momjian committed
85
   Why are there gaps in the numbering of my sequence/SERIAL column?
Bruce Momjian's avatar
Bruce Momjian committed
86 87 88
   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
89
   AllocSetAlloc()"?
Bruce Momjian's avatar
Bruce Momjian committed
90
   4.19) How do I tell what PostgreSQL version I am running?
Bruce Momjian's avatar
Bruce Momjian committed
91 92
   4.20) Why does my large-object operations get "invalid large obj
   descriptor"?
Bruce Momjian's avatar
Bruce Momjian committed
93 94 95 96
   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
97
   4.25) How do I return multiple rows or columns from a function?
98 99
   4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
   functions?
100 101
   4.27) What replication options are available?
   4.28) What encryption options are available?
Bruce Momjian's avatar
Bruce Momjian committed
102
   
103
                            Extending PostgreSQL
Bruce Momjian's avatar
Bruce Momjian committed
104
                                      
105
   5.1) I wrote a user-defined function. When I run it in psql, why does
Bruce Momjian's avatar
Bruce Momjian committed
106
   it dump core?
Bruce Momjian's avatar
Bruce Momjian committed
107
   5.2) How can I contribute some nifty new types and functions to
108
   PostgreSQL?
Bruce Momjian's avatar
Bruce Momjian committed
109
   5.3) How do I write a C function to return a tuple?
Bruce Momjian's avatar
Bruce Momjian committed
110
   5.4) I have changed a source file. Why does the recompile not see the
Bruce Momjian's avatar
Bruce Momjian committed
111
   change?
Bruce Momjian's avatar
Bruce Momjian committed
112 113
     _________________________________________________________________
   
114 115
                             General Questions
                                      
116
    1.1) What is PostgreSQL? How is it pronounced?
117
    
Bruce Momjian's avatar
Bruce Momjian committed
118 119 120
   PostgreSQL is pronounced Post-Gres-Q-L. An audio file is available at
   http://www.postgresql.org/postgresql.mp3 for those would like to hear
   the pronunciation.
Bruce Momjian's avatar
Bruce Momjian committed
121
   
Bruce Momjian's avatar
Bruce Momjian committed
122
   PostgreSQL is an enhancement of the POSTGRES database management
Bruce Momjian's avatar
Bruce Momjian committed
123 124 125 126 127
   system (and is still sometimes reffered to as simply "Postgres"), 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.
Bruce Momjian's avatar
Bruce Momjian committed
128
   
129 130 131 132
   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
133
   of PostgreSQL. It is a community project and is not controlled by any
Bruce Momjian's avatar
Bruce Momjian committed
134 135
   company. To get involved, see the developer's FAQ at
   http://www.PostgreSQL.org/docs/faqs/FAQ_DEV.html
Bruce Momjian's avatar
Bruce Momjian committed
136 137
   
   The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. Many
Bruce Momjian's avatar
Bruce Momjian committed
138
   others have contributed to the porting, testing, debugging, and
Bruce Momjian's avatar
Bruce Momjian committed
139 140 141 142 143 144 145 146 147 148
   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
149
    1.2) What is the copyright on PostgreSQL?
150
    
Bruce Momjian's avatar
Bruce Momjian committed
151
   PostgreSQL is subject to the following COPYRIGHT:
Bruce Momjian's avatar
Bruce Momjian committed
152 153 154
   
   PostgreSQL Data Base Management System
   
Bruce Momjian's avatar
Bruce Momjian committed
155
   Portions copyright (c) 1996-2004, PostgreSQL Global Development Group
Bruce Momjian's avatar
Bruce Momjian committed
156
   Portions Copyright (c) 1994-6 Regents of the University of California
Bruce Momjian's avatar
Bruce Momjian committed
157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176
   
   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
177 178 179 180
   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.
   
181 182
    1.3) What Unix platforms does PostgreSQL run on?
    
Bruce Momjian's avatar
Bruce Momjian committed
183
   In general, any modern Unix-compatible platform should be able to run
Bruce Momjian's avatar
Bruce Momjian committed
184 185 186
   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
187
    1.4) What non-Unix ports are available?
188
    
189 190 191 192
   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
   http://pgfoundry.org/projects/pginstaller.
193
   
194 195
   There is also a Novell Netware 6 port at http://forge.novell.com.
   
196 197 198
    1.5) Where can I get PostgreSQL?
    
   The primary anonymous ftp site for PostgreSQL is
Bruce Momjian's avatar
Bruce Momjian committed
199
   ftp://ftp.PostgreSQL.org/pub. For mirror sites, see our main web site.
200
   
Bruce Momjian's avatar
Bruce Momjian committed
201
    1.6) Where can I get support?
202
    
Bruce Momjian's avatar
Bruce Momjian committed
203
   The main mailing list is: pgsql-general@PostgreSQL.org. It is
Bruce Momjian's avatar
Bruce Momjian committed
204
   available for discussion of matters pertaining to PostgreSQL. To
Bruce Momjian's avatar
Bruce Momjian committed
205
   subscribe, send mail with the following lines in the body (not the
Bruce Momjian's avatar
Bruce Momjian committed
206
   subject line):
Bruce Momjian's avatar
Bruce Momjian committed
207 208
    subscribe
    end
Bruce Momjian's avatar
Bruce Momjian committed
209

Bruce Momjian's avatar
Bruce Momjian committed
210
   to pgsql-general-request@PostgreSQL.org.
Bruce Momjian's avatar
Bruce Momjian committed
211 212
   
   There is also a digest list available. To subscribe to this list, send
Bruce Momjian's avatar
Bruce Momjian committed
213
   email to: pgsql-general-digest-request@PostgreSQL.org with a body of:
Bruce Momjian's avatar
Bruce Momjian committed
214 215
    subscribe
    end
Bruce Momjian's avatar
Bruce Momjian committed
216 217 218 219

   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
220
   The bugs mailing list is available. To subscribe to this list, send
Bruce Momjian's avatar
Bruce Momjian committed
221
   email to pgsql-bugs-request@PostgreSQL.org with a body of:
Bruce Momjian's avatar
Bruce Momjian committed
222 223
    subscribe
    end
Bruce Momjian's avatar
Bruce Momjian committed
224

Bruce Momjian's avatar
Bruce Momjian committed
225
   There is also a developers discussion mailing list available. To
Bruce Momjian's avatar
Bruce Momjian committed
226 227
   subscribe to this list, send email to
   pgsql-hackers-request@PostgreSQL.org with a body of:
Bruce Momjian's avatar
Bruce Momjian committed
228 229
    subscribe
    end
Bruce Momjian's avatar
Bruce Momjian committed
230

Bruce Momjian's avatar
Bruce Momjian committed
231 232
   Additional mailing lists and information about PostgreSQL can be found
   via the PostgreSQL WWW home page at:
Bruce Momjian's avatar
Bruce Momjian committed
233
   
Bruce Momjian's avatar
Bruce Momjian committed
234
     http://www.PostgreSQL.org
Bruce Momjian's avatar
Bruce Momjian committed
235
     
236
   There is also an IRC channel on Freenode and EFNet, channel
Bruce Momjian's avatar
Bruce Momjian committed
237 238
   #PostgreSQL. You can use the Unix command irc -c '#PostgreSQL' "$USER"
   irc.phoenix.net. or irc -c '#PostgreSQL' "$USER" irc.freenode.net.
Bruce Momjian's avatar
Bruce Momjian committed
239
   
Bruce Momjian's avatar
Bruce Momjian committed
240
   A list of commercial support companies is available at
Bruce Momjian's avatar
Bruce Momjian committed
241
   http://techdocs.postgresql.org/companies.php.
242
   
Bruce Momjian's avatar
Bruce Momjian committed
243
    1.7) What is the latest release?
244
    
245
   The latest release of PostgreSQL is version 7.4.5.
Bruce Momjian's avatar
Bruce Momjian committed
246
   
Bruce Momjian's avatar
Bruce Momjian committed
247
   We plan to have major releases every six to eight months.
Bruce Momjian's avatar
Bruce Momjian committed
248
   
Bruce Momjian's avatar
Bruce Momjian committed
249
    1.8) What documentation is available?
250
    
Bruce Momjian's avatar
Bruce Momjian committed
251
   Several manuals, manual pages, and some small test examples are
Bruce Momjian's avatar
Bruce Momjian committed
252
   included in the distribution. See the /doc directory. You can also
253
   browse the manuals online at http://www.PostgreSQL.org/docs.
Bruce Momjian's avatar
Bruce Momjian committed
254
   
Bruce Momjian's avatar
Bruce Momjian committed
255 256 257
   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
258 259 260
   books available for purchase at
   http://techdocs.PostgreSQL.org/techdocs/bookreviews.php. There is also
   a collection of PostgreSQL technical articles at
261
   http://techdocs.PostgreSQL.org/.
Bruce Momjian's avatar
Bruce Momjian committed
262 263 264
   
   psql has some nice \d commands to show information about types,
   operators, functions, aggregates, etc.
Bruce Momjian's avatar
Bruce Momjian committed
265
   
Bruce Momjian's avatar
Bruce Momjian committed
266
   Our web site contains even more documentation.
267 268 269
   
    1.9) How do I find out about known bugs or missing features?
    
Bruce Momjian's avatar
Bruce Momjian committed
270 271
   PostgreSQL supports an extended subset of SQL-92. See our TODO list
   for known bugs, missing features, and future plans.
272 273 274
   
    1.10) How can I learn SQL?
    
Bruce Momjian's avatar
Bruce Momjian committed
275
   The PostgreSQL book at http://www.PostgreSQL.org/docs/awbook.html
Bruce Momjian's avatar
Bruce Momjian committed
276 277 278 279 280
   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
281 282
   
   Another one is "Teach Yourself SQL in 21 Days, Second Edition" at
Bruce Momjian's avatar
Bruce Momjian committed
283
   http://members.tripod.com/er4ebus/sql/index.htm
284
   
Bruce Momjian's avatar
Bruce Momjian committed
285
   Many of our users like The Practical SQL Handbook, Bowman, Judith S.,
Bruce Momjian's avatar
Bruce Momjian committed
286
   et al., Addison-Wesley. Others like The Complete Reference SQL, Groff
Bruce Momjian's avatar
Bruce Momjian committed
287
   et al., McGraw-Hill.
288 289 290
   
    1.11) Is PostgreSQL Y2K compliant?
    
Bruce Momjian's avatar
Bruce Momjian committed
291
   Yes, we easily handle dates past the year 2000 AD, and before 2000 BC.
Bruce Momjian's avatar
Bruce Momjian committed
292
   
293 294
    1.12) How do I join the development team?
    
Bruce Momjian's avatar
Bruce Momjian committed
295
   First, download the latest source and read the PostgreSQL Developers
Bruce Momjian's avatar
Bruce Momjian committed
296
   documentation on our web site, or in the distribution. Second,
297
   subscribe to the pgsql-hackers and pgsql-patches mailing lists. Third,
Bruce Momjian's avatar
Bruce Momjian committed
298
   submit high quality patches to pgsql-patches.
Bruce Momjian's avatar
Bruce Momjian committed
299
   
Bruce Momjian's avatar
Bruce Momjian committed
300
   There are about a dozen people who have commit privileges to the
Bruce Momjian's avatar
Bruce Momjian committed
301
   PostgreSQL CVS archive. They each have submitted so many high-quality
Bruce Momjian's avatar
Bruce Momjian committed
302 303 304
   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
305
   
306 307
    1.13) How do I submit a bug report?
    
308 309 310
   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
311
   
Bruce Momjian's avatar
Bruce Momjian committed
312
   Also check out our ftp site ftp://ftp.PostgreSQL.org/pub to see if
313 314
   there is a more recent PostgreSQL version or patches.
   
Bruce Momjian's avatar
Bruce Momjian committed
315
    1.14) How does PostgreSQL compare to other DBMSs?
316 317 318 319 320
    
   There are several ways of measuring software: features, performance,
   reliability, support, and price.
   
   Features
Bruce Momjian's avatar
Bruce Momjian committed
321 322 323 324
          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
325
          inheritance, rules, and multi-version concurrency control to
Bruce Momjian's avatar
Bruce Momjian committed
326
          reduce lock contention.
327 328
          
   Performance
Bruce Momjian's avatar
Bruce Momjian committed
329 330
          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
331
          others. In comparison to MySQL or leaner database systems, we
Bruce Momjian's avatar
Bruce Momjian committed
332
          are faster for multiple users, complex queries, and a
Bruce Momjian's avatar
Bruce Momjian committed
333
          read/write query load. MySQL is faster for simple SELECT
Bruce Momjian's avatar
Bruce Momjian committed
334 335 336
          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
337 338
          improve performance in every release. There is an interesting
          Web page comparing PostgreSQL to MySQL at
Bruce Momjian's avatar
Bruce Momjian committed
339
          http://openacs.org/philosophy/why-not-mysql.html Also, MySQL is
340 341 342
          is a company that distributes its products via open source, and
          requires a commercial license for close-source software, not an
          open source development community like PostgreSQL.
343 344 345 346 347 348 349 350 351 352
          
   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
353 354 355 356 357 358 359 360
          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.)
361 362 363 364 365
          
   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
366
          
Bruce Momjian's avatar
Bruce Momjian committed
367
    1.15) How can I financially assist PostgreSQL?
Bruce Momjian's avatar
Bruce Momjian committed
368
    
369
   PostgreSQL has had a first-class infrastructure since we started in
370
   1996. This is all thanks to Marc Fournier, who has created and managed
371
   this infrastructure over the years.
Bruce Momjian's avatar
Bruce Momjian committed
372 373 374 375 376 377 378 379
   
   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,
380
   please go to http://store.pgsql.com/shopping/ and make a donation.
Bruce Momjian's avatar
Bruce Momjian committed
381
   
Bruce Momjian's avatar
Bruce Momjian committed
382 383 384 385
   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.
386
     _________________________________________________________________
Bruce Momjian's avatar
Bruce Momjian committed
387
   
388 389 390
   Also, if you have a success story about PostgreSQL, please submit it
   to our advocacy site at http://advocacy.postgresql.org.
   
391 392 393 394
                           User Client Questions
                                      
    2.1) Are there ODBC drivers for PostgreSQL?
    
Bruce Momjian's avatar
Bruce Momjian committed
395
   There are two ODBC drivers available, PsqlODBC and OpenLink ODBC.
Bruce Momjian's avatar
Bruce Momjian committed
396
   
397 398
   You can download PsqlODBC from
   http://gborg.postgresql.org/project/psqlodbc/projdisplay.php.
Bruce Momjian's avatar
Bruce Momjian committed
399
   
Bruce Momjian's avatar
Bruce Momjian committed
400 401 402 403 404 405
   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
406
   commercial-quality support, but a freeware version will always be
Bruce Momjian's avatar
Bruce Momjian committed
407
   available. Please send questions to postgres95@openlink.co.uk.
Bruce Momjian's avatar
Bruce Momjian committed
408
   
Bruce Momjian's avatar
Bruce Momjian committed
409
    2.2) What tools are available for using PostgreSQL with Web pages?
410
    
Bruce Momjian's avatar
Bruce Momjian committed
411
   A nice introduction to Database-backed Web pages can be seen at:
Bruce Momjian's avatar
Bruce Momjian committed
412
   http://www.webreview.com
Bruce Momjian's avatar
Bruce Momjian committed
413
   
Bruce Momjian's avatar
Bruce Momjian committed
414
   For Web integration, PHP is an excellent interface. It is at
Bruce Momjian's avatar
Bruce Momjian committed
415
   http://www.php.net.
Bruce Momjian's avatar
Bruce Momjian committed
416
   
417
   For complex cases, many use the Perl interface and CGI.pm or mod_perl.
Bruce Momjian's avatar
Bruce Momjian committed
418
   
419
    2.3) Does PostgreSQL have a graphical user interface?
420
    
421
   Yes, there are several graphical interfaces to PostgreSQL available.
Bruce Momjian's avatar
Bruce Momjian committed
422
   These include PgAccess http://www.pgaccess.org), PgAdmin III
423 424
   (http://www.pgadmin.org, RHDB Admin (http://sources.redhat.com/rhdb/ )
   and Rekall ( http://www.thekompany.com/products/rekall/, proprietary).
Bruce Momjian's avatar
Bruce Momjian committed
425
   There is also PhpPgAdmin ( http://phppgadmin.sourceforge.net/ ), a
426
   web-based interface to PostgreSQL.
427
   
428 429 430
   See http://techdocs.postgresql.org/guides/GUITools for a more detailed
   list.
   
431
    2.4) What languages are able to communicate with PostgreSQL?
432
    
433 434 435 436
   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
437 438 439 440 441
     * C (libpq)
     * Embedded C (ecpg)
     * Java (jdbc)
     * Python (PyGreSQL)
     * TCL (libpgtcl)
Bruce Momjian's avatar
Bruce Momjian committed
442
       
443 444
   Additional interfaces are available at http://gborg.postgresql.org in
   the Drivers/Interfaces section.
Bruce Momjian's avatar
Bruce Momjian committed
445 446
     _________________________________________________________________
   
447 448
                          Administrative Questions
                                      
Bruce Momjian's avatar
Bruce Momjian committed
449
    3.1) How do I install PostgreSQL somewhere other than /usr/local/pgsql?
450
    
Bruce Momjian's avatar
Bruce Momjian committed
451
   Specify the --prefix option when running configure.
Bruce Momjian's avatar
Bruce Momjian committed
452
   
Bruce Momjian's avatar
Bruce Momjian committed
453
    3.2) When I start postmaster, I get a Bad System Call or core dumped
454 455
    message. Why?
    
Bruce Momjian's avatar
Bruce Momjian committed
456
   It could be a variety of problems, but first check to see that you
Bruce Momjian's avatar
Bruce Momjian committed
457
   have System V extensions installed in your kernel. PostgreSQL requires
Bruce Momjian's avatar
Bruce Momjian committed
458
   kernel support for shared memory and semaphores.
Bruce Momjian's avatar
Bruce Momjian committed
459
   
Bruce Momjian's avatar
Bruce Momjian committed
460
    3.3) When I try to start postmaster, I get IpcMemoryCreate errors. Why?
461
    
Bruce Momjian's avatar
Bruce Momjian committed
462 463 464
   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
465 466 467 468
   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
469
   
Bruce Momjian's avatar
Bruce Momjian committed
470
    3.4) When I try to start postmaster, I get IpcSemaphoreCreate errors. Why?
471 472 473 474
    
   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
475 476 477 478 479 480 481
   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.
482 483
   
   If the error message is something else, you might not have semaphore
Bruce Momjian's avatar
Bruce Momjian committed
484 485 486
   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
487
   
Bruce Momjian's avatar
Bruce Momjian committed
488
    3.5) How do I control connections from other hosts?
489
    
Bruce Momjian's avatar
Bruce Momjian committed
490
   By default, PostgreSQL only allows connections from the local machine
Bruce Momjian's avatar
Bruce Momjian committed
491 492 493 494
   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 and enable host-based authentication by modifying the
   file $PGDATA/pg_hba.conf accordingly.
Bruce Momjian's avatar
Bruce Momjian committed
495
   
Bruce Momjian's avatar
Bruce Momjian committed
496
    3.6) How do I tune the database engine for better performance?
497
    
Bruce Momjian's avatar
Bruce Momjian committed
498 499
   Certainly, indexes can speed up queries. The EXPLAIN ANALYZE command
   allows you to see how PostgreSQL is interpreting your query, and which
Bruce Momjian's avatar
Bruce Momjian committed
500
   indexes are being used.
Bruce Momjian's avatar
Bruce Momjian committed
501
   
Bruce Momjian's avatar
Bruce Momjian committed
502 503 504 505 506 507 508
   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
509
   
Bruce Momjian's avatar
Bruce Momjian committed
510
   There are several tuning options. You can disable fsync() by starting
Bruce Momjian's avatar
Bruce Momjian committed
511
   postmaster with a -o -F option. This will prevent fsync()s from
Bruce Momjian's avatar
Bruce Momjian committed
512
   flushing to disk after every transaction.
Bruce Momjian's avatar
Bruce Momjian committed
513 514
   
   You can also use the postmaster -B option to increase the number of
Bruce Momjian's avatar
Bruce Momjian committed
515
   shared memory buffers used by the backend processes. If you make this
Bruce Momjian's avatar
Bruce Momjian committed
516
   parameter too high, the postmaster may not start because you have
Bruce Momjian's avatar
Bruce Momjian committed
517 518
   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
519
   
Bruce Momjian's avatar
Bruce Momjian committed
520
   You can also use the backend -S option to increase the maximum amount
Bruce Momjian's avatar
Bruce Momjian committed
521
   of memory used by the backend process for temporary sorts. The -S
Bruce Momjian's avatar
Bruce Momjian committed
522
   value is measured in kilobytes, and the default is 512 (i.e. 512K).
Bruce Momjian's avatar
Bruce Momjian committed
523
   
Bruce Momjian's avatar
Bruce Momjian committed
524
   You can also use the CLUSTER command to group data in tables to match
Bruce Momjian's avatar
Bruce Momjian committed
525
   an index. See the CLUSTER manual page for more details.
Bruce Momjian's avatar
Bruce Momjian committed
526
   
Bruce Momjian's avatar
Bruce Momjian committed
527
    3.7) What debugging features are available?
528
    
Bruce Momjian's avatar
Bruce Momjian committed
529 530 531
   PostgreSQL has several features that report status information that
   can be valuable for debugging purposes.
   
Bruce Momjian's avatar
Bruce Momjian committed
532
   First, by running configure with the --enable-cassert option, many
Bruce Momjian's avatar
Bruce Momjian committed
533
   assert()s monitor the progress of the backend and halt the program
Bruce Momjian's avatar
Bruce Momjian committed
534 535 536
   when something unexpected occurs.
   
   Both postmaster and postgres have several debug options available.
Bruce Momjian's avatar
Bruce Momjian committed
537 538
   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
539 540
    cd /usr/local/pgsql
    ./bin/postmaster >server.log 2>&1 &
Bruce Momjian's avatar
Bruce Momjian committed
541 542

   This will put a server.log file in the top-level PostgreSQL directory.
Bruce Momjian's avatar
Bruce Momjian committed
543
   This file contains useful information about problems or errors
Bruce Momjian's avatar
Bruce Momjian committed
544 545
   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
546
   that specifies the debug level. Be warned that high debug level values
Bruce Momjian's avatar
Bruce Momjian committed
547
   generate large log files.
Bruce Momjian's avatar
Bruce Momjian committed
548
   
Bruce Momjian's avatar
Bruce Momjian committed
549
   If postmaster is not running, you can actually run the postgres
Bruce Momjian's avatar
Bruce Momjian committed
550 551 552 553
   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
554 555 556
   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
557
   
Bruce Momjian's avatar
Bruce Momjian committed
558 559 560
   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
561 562
   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
563 564
   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
565
   
Bruce Momjian's avatar
Bruce Momjian committed
566 567
   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
568 569 570 571
   
   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
572 573
   in the client's current directory. Linux requires a compile with
   -DLINUX_PROFILE for proper profiling.
Bruce Momjian's avatar
Bruce Momjian committed
574
   
Bruce Momjian's avatar
Bruce Momjian committed
575
    3.8) Why do I get "Sorry, too many clients" when trying to connect?
576
    
Bruce Momjian's avatar
Bruce Momjian committed
577 578
   You need to increase postmaster's limit on how many concurrent backend
   processes it can start.
Bruce Momjian's avatar
Bruce Momjian committed
579
   
580
   The default limit is 32 processes. You can increase it by restarting
Bruce Momjian's avatar
Bruce Momjian committed
581
   postmaster with a suitable -N value or modifying postgresql.conf.
Bruce Momjian's avatar
Bruce Momjian committed
582 583 584 585 586 587
   
   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
588 589 590
   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
591
   and the maximum number of open files, NFILE and NINODE. The reason
Bruce Momjian's avatar
Bruce Momjian committed
592
   that PostgreSQL has a limit on the number of allowed backend processes
Bruce Momjian's avatar
Bruce Momjian committed
593
   is so your system won't run out of resources.
Bruce Momjian's avatar
Bruce Momjian committed
594
   
Bruce Momjian's avatar
Bruce Momjian committed
595
    3.9) What is in the pgsql_tmp directory?
596
    
Bruce Momjian's avatar
Bruce Momjian committed
597 598 599 600
   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
601
   
602 603 604
   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
605
   
606 607
    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
608
    
609 610
   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.
611 612 613 614 615
   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.
616 617 618 619
   
   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.
620 621 622 623 624 625 626
   
    3.11) What computer hardware should I use?
    
   Because PC hardware is mostly compatible, people tend to believe that
   all PC hardware is of equal quality. It is not. ECC RAM, SCSI, and
   quality motherboards are more reliable and have better performance
   than less expensive hardware. PostgreSQL will run on almost any
Bruce Momjian's avatar
Bruce Momjian committed
627 628 629
   hardware, but if reliability and performance are important it is wise
   to research your hardware options thoroughly. Our email lists can be
   used to discuss hardware options and tradeoffs.
630
     _________________________________________________________________
Bruce Momjian's avatar
Bruce Momjian committed
631
   
632 633
                           Operational Questions
                                      
Bruce Momjian's avatar
Bruce Momjian committed
634
    4.1) What is the difference between binary cursors and normal cursors?
635
    
Bruce Momjian's avatar
Bruce Momjian committed
636
   See the DECLARE manual page for a description.
Bruce Momjian's avatar
Bruce Momjian committed
637
   
638
    4.2) How do I SELECT only the first few rows of a query? A random row?
639
    
Bruce Momjian's avatar
Bruce Momjian committed
640
   See the FETCH manual page, or use SELECT ... LIMIT....
Bruce Momjian's avatar
Bruce Momjian committed
641
   
Bruce Momjian's avatar
Bruce Momjian committed
642
   The entire query may have to be evaluated, even if you only want the
643 644 645 646
   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
647
   
648 649 650 651 652 653
   To SELECT a random row, use:
    SELECT col
    FROM tab
    ORDER BY random()
    LIMIT 1;

Bruce Momjian's avatar
Bruce Momjian committed
654
    4.3) How do I get a list of tables or other things I can see in psql?
655
    
Bruce Momjian's avatar
Bruce Momjian committed
656 657 658 659 660 661 662 663
   Use the \dt command to see tables in psql. For a complete list of
   commands inside psql you can use \?. Alternatively you can read the
   source code for psql in file 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 commands you give. PostgreSQL
   also provides an SQLi compliant INFORMATION SCHEMA interface you can
   query to get information about the database.
Bruce Momjian's avatar
Bruce Momjian committed
664
   
665
    4.4) How do you remove a column from a table, or change its data type?
666
    
667 668
   DROP COLUMN functionality was added in release 7.3 with ALTER TABLE
   DROP COLUMN. In earlier versions, you can do this:
669 670
    BEGIN;
    LOCK TABLE old_table;
Bruce Momjian's avatar
Bruce Momjian committed
671 672 673 674 675
    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;
676
    COMMIT;
677

678 679 680 681
   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
682
    ALTER TABLE tab DROP COLUMN old_col;
683 684 685 686 687
    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
688
    4.5) What is the maximum size for a row, a table, and a database?
689
    
Bruce Momjian's avatar
Bruce Momjian committed
690
   These are the limits:
691
    Maximum size for a database?             unlimited (32 TB databases exist)
692
    Maximum size for a table?                32 TB
Bruce Momjian's avatar
Bruce Momjian committed
693 694
    Maximum size for a row?                  1.6TB
    Maximum size for a field?                1 GB
Bruce Momjian's avatar
Bruce Momjian committed
695 696 697
    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
698 699

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

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

   8192 bytes per page
Bruce Momjian's avatar
Bruce Momjian committed
730 731
   -------------------   =  136 rows per database page (rounded down)
     60 bytes per row
732

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

Bruce Momjian's avatar
Bruce Momjian committed
737
735 database pages * 8192 bytes per page  =  6,021,120 bytes (6 MB)
738

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

783 784 785 786
   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
787
   When using wild-card operators such as LIKE or ~, indexes can only be
788 789
   used in certain circumstances:
     * The beginning of the search string must be anchored to the start
Bruce Momjian's avatar
Bruce Momjian committed
790 791 792 793 794 795 796 797 798
       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.
799
       
Bruce Momjian's avatar
Bruce Momjian committed
800 801 802 803
   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.
   
Bruce Momjian's avatar
Bruce Momjian committed
804
    4.9) How do I see how the query optimizer is evaluating my query?
805
    
Bruce Momjian's avatar
Bruce Momjian committed
806
   See the EXPLAIN manual page.
807
   
Bruce Momjian's avatar
Bruce Momjian committed
808
    4.10) What is an R-tree index?
809
    
Bruce Momjian's avatar
Bruce Momjian committed
810
   An R-tree index is used for indexing spatial data. A hash index can't
811
   handle range searches. A B-tree index only handles range searches in a
Bruce Momjian's avatar
Bruce Momjian committed
812
   single dimension. R-trees can handle multi-dimensional data. For
813
   example, if an R-tree index can be built on an attribute of type
Bruce Momjian's avatar
Bruce Momjian committed
814 815
   point, the system can more efficiently answer queries such as "select
   all points within a bounding rectangle."
816
   
Bruce Momjian's avatar
Bruce Momjian committed
817
   The canonical paper that describes the original R-tree design is:
818
   
Bruce Momjian's avatar
Bruce Momjian committed
819
   Guttman, A. "R-trees: A Dynamic Index Structure for Spatial
Bruce Momjian's avatar
Bruce Momjian committed
820 821
   Searching." Proceedings of the 1984 ACM SIGMOD Int'l Conf on Mgmt of
   Data, 45-57.
822 823
   
   You can also find this paper in Stonebraker's "Readings in Database
Bruce Momjian's avatar
Bruce Momjian committed
824
   Systems".
825
   
Bruce Momjian's avatar
Bruce Momjian committed
826
   Built-in R-trees can handle polygons and boxes. In theory, R-trees can
827
   be extended to handle higher number of dimensions. In practice,
Bruce Momjian's avatar
Bruce Momjian committed
828
   extending R-trees requires a bit of work and we don't currently have
829 830
   any documentation on how to do it.
   
Bruce Momjian's avatar
Bruce Momjian committed
831
    4.11) What is the Genetic Query Optimizer?
832
    
Bruce Momjian's avatar
Bruce Momjian committed
833 834
   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
835
   join queries through nonexhaustive search.
Bruce Momjian's avatar
Bruce Momjian committed
836
   
Bruce Momjian's avatar
Bruce Momjian committed
837
    4.12) How do I perform regular expression searches and case-insensitive
Bruce Momjian's avatar
Bruce Momjian committed
838 839
    regular expression searches? How do I use an index for case-insensitive
    searches?
840
    
Bruce Momjian's avatar
Bruce Momjian committed
841
   The ~ operator does regular expression matching, and ~* does
Bruce Momjian's avatar
Bruce Momjian committed
842
   case-insensitive regular expression matching. The case-insensitive
843
   variant of LIKE is called ILIKE.
Bruce Momjian's avatar
Bruce Momjian committed
844
   
Bruce Momjian's avatar
Bruce Momjian committed
845 846 847
   Case-insensitive equality comparisons are normally expressed as:
    SELECT *
    FROM tab
848
    WHERE lower(col) = 'abc';
Bruce Momjian's avatar
Bruce Momjian committed
849 850 851

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

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

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

Bruce Momjian's avatar
Bruce Momjian committed
895
   is automatically translated into this:
Bruce Momjian's avatar
Bruce Momjian committed
896 897 898 899 900
    CREATE SEQUENCE person_id_seq;
    CREATE TABLE person (
        id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),
        name TEXT
    );
Bruce Momjian's avatar
Bruce Momjian committed
901 902

   See the create_sequence manual page for more information about
Bruce Momjian's avatar
Bruce Momjian committed
903
   sequences. You can also use each row's OID field as a unique value.
Bruce Momjian's avatar
Bruce Momjian committed
904
   However, if you need to dump and reload the database, you need to use
Bruce Momjian's avatar
Bruce Momjian committed
905
   pg_dump's -o option or COPY WITH OIDS option to preserve the OIDs.
Bruce Momjian's avatar
Bruce Momjian committed
906
   
Bruce Momjian's avatar
Bruce Momjian committed
907
    4.15.2) How do I get the value of a SERIAL insert?
908
    
Bruce Momjian's avatar
Bruce Momjian committed
909
   One approach is to retrieve the next SERIAL value from the sequence
Bruce Momjian's avatar
Bruce Momjian committed
910
   object with the nextval() function before inserting and then insert it
911 912 913 914
   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
915

Bruce Momjian's avatar
Bruce Momjian committed
916 917 918 919 920
   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
921
   
Bruce Momjian's avatar
Bruce Momjian committed
922 923
   Alternatively, you could retrieve the assigned SERIAL value with the
   currval() function after it was inserted by default, e.g.,
924 925
    execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
    new_id = execute("SELECT currval('person_id_seq')");
Bruce Momjian's avatar
Bruce Momjian committed
926

Bruce Momjian's avatar
Bruce Momjian committed
927
   Finally, you could use the OID returned from the INSERT statement to
Bruce Momjian's avatar
Bruce Momjian committed
928
   look up the default value, though this is probably the least portable
929 930
   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
931 932 933
   oid value is made available via $sth->{pg_oid_status} after
   $sth->execute().
   
Bruce Momjian's avatar
Bruce Momjian committed
934
    4.15.3) Don't currval() and nextval() lead to a race condition with other
Bruce Momjian's avatar
Bruce Momjian committed
935
    users?
Bruce Momjian's avatar
Bruce Momjian committed
936
    
937
   No. currval() returns the current value assigned by your backend, not
Bruce Momjian's avatar
Bruce Momjian committed
938
   by all users.
Bruce Momjian's avatar
Bruce Momjian committed
939
   
Bruce Momjian's avatar
Bruce Momjian committed
940
    4.15.4) Why aren't my sequence numbers reused on transaction abort? Why are
Bruce Momjian's avatar
Bruce Momjian committed
941
    there gaps in the numbering of my sequence/SERIAL column?
Bruce Momjian's avatar
Bruce Momjian committed
942 943
    
   To improve concurrency, sequence values are given out to running
Bruce Momjian's avatar
Bruce Momjian committed
944
   transactions as needed and are not locked until the transaction
Bruce Momjian's avatar
Bruce Momjian committed
945 946
   completes. This causes gaps in numbering from aborted transactions.
   
Bruce Momjian's avatar
Bruce Momjian committed
947
    4.16) What is an OID? What is a TID?
948
    
Bruce Momjian's avatar
Bruce Momjian committed
949
   OIDs are PostgreSQL's answer to unique row ids. Every row that is
Bruce Momjian's avatar
Bruce Momjian committed
950
   created in PostgreSQL gets a unique OID. All OIDs generated during
951
   initdb are less than 16384 (from include/access/transam.h). All
Bruce Momjian's avatar
Bruce Momjian committed
952 953
   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
954
   within the entire PostgreSQL installation.
Bruce Momjian's avatar
Bruce Momjian committed
955
   
Bruce Momjian's avatar
Bruce Momjian committed
956 957 958 959
   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
960
   access.
Bruce Momjian's avatar
Bruce Momjian committed
961
   
Bruce Momjian's avatar
Bruce Momjian committed
962
   OIDs are assigned to all new rows from a central area that is used by
Bruce Momjian's avatar
Bruce Momjian committed
963
   all databases. If you want to change the OID to something else, or if
Bruce Momjian's avatar
Bruce Momjian committed
964 965
   you want to make a copy of the table, with the original OIDs, there is
   no reason you can't do it:
966 967 968 969 970
        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;
Bruce Momjian's avatar
Bruce Momjian committed
971

Bruce Momjian's avatar
Bruce Momjian committed
972
   OIDs are stored as 4-byte integers, and will overflow at 4 billion. No
Bruce Momjian's avatar
Bruce Momjian committed
973
   one has reported this ever happening, and we plan to have the limit
Bruce Momjian's avatar
Bruce Momjian committed
974 975
   removed before anyone does.
   
Bruce Momjian's avatar
Bruce Momjian committed
976
   TIDs are used to identify specific physical rows with block and offset
Bruce Momjian's avatar
Bruce Momjian committed
977
   values. TIDs change after rows are modified or reloaded. They are used
Bruce Momjian's avatar
Bruce Momjian committed
978
   by index entries to point to physical rows.
Bruce Momjian's avatar
Bruce Momjian committed
979
   
Bruce Momjian's avatar
Bruce Momjian committed
980
    4.17) What is the meaning of some of the terms used in PostgreSQL?
981
    
Bruce Momjian's avatar
Bruce Momjian committed
982 983
   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
984
     * table, relation, class
Bruce Momjian's avatar
Bruce Momjian committed
985
     * row, record, tuple
Bruce Momjian's avatar
Bruce Momjian committed
986
     * column, field, attribute
Bruce Momjian's avatar
Bruce Momjian committed
987 988 989
     * retrieve, select
     * replace, update
     * append, insert
Bruce Momjian's avatar
Bruce Momjian committed
990
     * OID, serial value
Bruce Momjian's avatar
Bruce Momjian committed
991 992 993
     * portal, cursor
     * range variable, table name, table alias
       
Bruce Momjian's avatar
Bruce Momjian committed
994
   A list of general database terms can be found at:
995 996
   http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary
   /glossary.html
Bruce Momjian's avatar
Bruce Momjian committed
997
   
Bruce Momjian's avatar
Bruce Momjian committed
998
    4.18) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
Bruce Momjian's avatar
Bruce Momjian committed
999
    
1000 1001 1002
   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
1003 1004 1005 1006 1007 1008 1009 1010 1011 1012
    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
1013
    4.19) How do I tell what PostgreSQL version I am running?
1014
    
1015
   From psql, type SELECT version();
Bruce Momjian's avatar
Bruce Momjian committed
1016
   
Bruce Momjian's avatar
Bruce Momjian committed
1017 1018
    4.20) Why does my large-object operations get "invalid large obj
    descriptor"?
Bruce Momjian's avatar
Bruce Momjian committed
1019 1020 1021 1022
    
   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
1023 1024 1025 1026 1027
   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
1028 1029 1030 1031
   
   If you are using a client interface like ODBC you may need to set
   auto-commit off.
   
Bruce Momjian's avatar
Bruce Momjian committed
1032
    4.21) How do I create a column that will default to the current time?
Bruce Momjian's avatar
Bruce Momjian committed
1033
    
Bruce Momjian's avatar
Bruce Momjian committed
1034 1035
   Use CURRENT_TIMESTAMP:
CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
Bruce Momjian's avatar
Bruce Momjian committed
1036

Bruce Momjian's avatar
Bruce Momjian committed
1037
    4.22) Why are my subqueries using IN so slow?
Bruce Momjian's avatar
Bruce Momjian committed
1038
    
1039 1040 1041 1042 1043
   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, IN is fastest. To speed up other queries,
   replace IN with EXISTS:
1044
    SELECT *
Bruce Momjian's avatar
Bruce Momjian committed
1045
    FROM tab
1046
    WHERE col IN (SELECT subcol FROM subtab);
Bruce Momjian's avatar
Bruce Momjian committed
1047 1048

   to:
1049
    SELECT *
Bruce Momjian's avatar
Bruce Momjian committed
1050
    FROM tab
1051
    WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col);
Bruce Momjian's avatar
Bruce Momjian committed
1052

1053 1054 1055 1056
   For this to be fast, subcol should be an indexed column.
   
   In version 7.4 and later, IN actually uses the same sophisticated join
   techniques as normal queries, and is prefered to using EXISTS.
Bruce Momjian's avatar
Bruce Momjian committed
1057
   
Bruce Momjian's avatar
Bruce Momjian committed
1058
    4.23) How do I perform an outer join?
Bruce Momjian's avatar
Bruce Momjian committed
1059
    
1060 1061
   PostgreSQL supports outer joins using the SQL standard syntax. Here
   are two examples:
Bruce Momjian's avatar
Bruce Momjian committed
1062 1063 1064 1065
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);

   or
Bruce Momjian's avatar
Bruce Momjian committed
1066 1067 1068
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 USING (col);

Bruce Momjian's avatar
Bruce Momjian committed
1069 1070 1071 1072 1073 1074
   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
1075
   
Bruce Momjian's avatar
Bruce Momjian committed
1076 1077 1078
   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
1079 1080 1081 1082 1083 1084 1085
    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
1086
    ORDER BY col1
Bruce Momjian's avatar
Bruce Momjian committed
1087

Bruce Momjian's avatar
Bruce Momjian committed
1088
    4.24) How do I perform queries using multiple databases?
Bruce Momjian's avatar
Bruce Momjian committed
1089
    
1090 1091 1092
   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
1093
   
Bruce Momjian's avatar
Bruce Momjian committed
1094
   contrib/dblink allows cross-database queries using function calls. Of
1095 1096
   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
1097
   
Bruce Momjian's avatar
Bruce Momjian committed
1098 1099
    4.25) How do I return multiple rows or columns from a function?
    
1100 1101
   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
1102
   
1103 1104 1105 1106 1107 1108 1109 1110 1111 1112
    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.
1113 1114 1115
   
    4.27) What replication options are available?
    
Bruce Momjian's avatar
Bruce Momjian committed
1116
   There are several master/slave replication options available. These
1117 1118
   allow only the master to make database changes and the slave can only
   do database reads. The bottom of
1119
   http://gborg.PostgreSQL.org/genpage?replication_research lists them. A
1120
   multi-master replication solution is being worked on at
1121
   http://gborg.PostgreSQL.org/project/pgreplication/projdisplay.php.
1122
   
1123
    4.28) What encryption options are available?
1124
    
Bruce Momjian's avatar
Bruce Momjian committed
1125 1126
     * contrib/pgcrypto contains many encryption functions for use in SQL
       queries.
1127 1128 1129 1130 1131 1132
     * 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.)
1133
     * Database user passwords are automatically encrypted when stored in
1134 1135
       version 7.3. In previous versions, you must enable the option
       PASSWORD_ENCRYPTION in postgresql.conf.
Bruce Momjian's avatar
Bruce Momjian committed
1136
     * The server can run using an encrypted file system.
1137
     _________________________________________________________________
1138
   
1139 1140 1141 1142 1143
                            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
1144
   The problem could be a number of things. Try testing your user-defined
Bruce Momjian's avatar
Bruce Momjian committed
1145
   function in a stand-alone test program first.
Bruce Momjian's avatar
Bruce Momjian committed
1146
   
Bruce Momjian's avatar
Bruce Momjian committed
1147
    5.2) How can I contribute some nifty new types and functions to PostgreSQL?
1148 1149 1150
    
   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
1151
   
Bruce Momjian's avatar
Bruce Momjian committed
1152
    5.3) How do I write a C function to return a tuple?
1153
    
1154 1155 1156 1157 1158
   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
1159
   
Bruce Momjian's avatar
Bruce Momjian committed
1160
    5.4) I have changed a source file. Why does the recompile not see the
1161 1162 1163
    change?
    
   The Makefiles do not have the proper dependencies for include files.
Bruce Momjian's avatar
Bruce Momjian committed
1164 1165 1166
   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.