11
22 Frequently Asked Questions (FAQ) for PostgreSQL
33
4- Last updated:Sat Oct 24 00:12:23 EDT1998
4+ Last updated:Fri Jun 4 23:30:19 EDT1999
55
66 Current maintainer: Bruce Momjian (maillist@candle.pha.pa.us)
77
88 The most recent version of this document can be viewed at the
99 postgreSQL Web site, http://postgreSQL.org.
1010
1111 Linux-specific questions are answered in
12- http://postgreSQL.org/docs/faq-linux.shtml .
12+ http://postgreSQL.org/docs/faq-linux.html .
1313
1414 Irix-specific questions are answered in
15- http://postgreSQL.org/docs/faq-irix.shtml.
16-
17- HPUX-specific questions are answered in
18- http://postgreSQL.org/docs/faq-hpux.shtml.
15+ http://postgreSQL.org/docs/faq-irix.html.
1916 _________________________________________________________________
2017
2118 General questions
5956 2.10) All my servers crash under concurrent table access. Why?
6057 2.11) How do I tune the database engine for better performance?
6158 2.12) What debugging features are available in PostgreSQL?
62- 2.13) How do I enable more than 64 concurrent backends?
63- 2.14) What non-unix ports are available?
59+ 2.13) When I try to start the postmaster, I get IpcSemaphoreCreate
60+ errors. Why?
61+ 2.14) I get 'Sorry, too many clients' when trying to connect. Why?
62+ 2.15) What non-unix ports are available?
6463
6564 Operational questions
6665
7069 3.4) What is the exact difference between binary cursors and normal
7170 cursors?
7271 3.5) What is an R-tree index and what is it used for?
73- 3.6) What is the maximum size for a tuple?
74- 3.7) I defined indices but my queries don't seem to make use of them.
75- Why?
72+ 3.6) What is the maximum size for a row, table, database?
73+ 3.7) My queries are slow or don't make use of the indexes. Why?
7674 3.8) How do I do regular expression searches? case-insensitive regexp
7775 searching?
7876 3.9) I experienced a server crash during a vacuum. How do I remove the
@@ -240,11 +238,11 @@ Section 1: General Questions
240238 http://postgreSQL.org
241239
242240 There also an IRC channel on EFNet, channel #PostgreSQL. I use the
243- unix command irc -c '#PostgreSQL' "$USER" irc.ais .net
241+ unix command irc -c '#PostgreSQL' "$USER" irc.phoenix .net
244242
245243 1.6) Latest release of PostgreSQL
246244
247- The latest release of PostgreSQL is version 6.4 .
245+ The latest release of PostgreSQL is version 6.5 .
248246
249247 We plan to have major releases every four months.
250248
@@ -274,13 +272,12 @@ Section 1: General Questions
274272 1.10) Does PostgreSQL work with databases from earlier versions of
275273 PostgreSQL?
276274
277- Upgrading to 6.4 from release 6.3.* can be accomplished using the new
278- pg_upgrade utility. Those upgrading from earlier releases require a
279- dump and restore.
275+ Upgrading to 6.5 can not use the pg_upgrade utility. Those upgrading
276+ from earlier releases require a dump and restore.
280277
281- Thoseugrading from versions earlier than 1.09 must upgrade to 1.09
278+ Thoseupgrading from versions earlier than 1.09 must upgrade to 1.09
282279 first without a dump/reload, then dump the data from 1.09, and then
283- load it into 6.4 .
280+ load it into 6.5 .
284281
285282 1.11) Are there ODBC drivers for PostgreSQL?
286283
@@ -309,14 +306,14 @@ Section 1: General Questions
309306 PHP is great for simple stuff, but for more complex stuff, some still
310307 use the perl interface and CGI.pm.
311308
312- An WWWgatway based on WDB using perl can be downloaded from
309+ An WWWgateway based on WDB using perl can be downloaded from
313310 http://www.eol.ists.ca/~dunlop/wdb-p95
314311
315312 1.13) Does PostgreSQL have a graphical user interface? A report generator? A
316313 embedded query language interface?
317314
318315 We have a nice graphical user interface called pgaccess, which is
319- shipped as part of thedistribtion . Pgaccess also has a report
316+ shipped as part of thedistribution . Pgaccess also has a report
320317 generator.
321318
322319 The web page is http://www.flex.ro/pgaccess We also include ecpg,
@@ -365,8 +362,9 @@ Section 2: Installation Questions
365362 formats.
366363
367364 Check your locale configuration. PostgreSQL uses the locale settings
368- of the user that ran the postmaster process. Set those accordingly for
369- your operating environment.
365+ of the user that ran the postmaster process. There are postgres and
366+ psql SET commands to control the date format. Set those accordingly
367+ for your operating environment.
370368
371369 2.4) How do I install PostgreSQL somewhere other than /usr/local/pgsql?
372370
@@ -384,8 +382,9 @@ Section 2: Installation Questions
384382 You either do not have shared memory configured properly in kernel or
385383 you need to enlarge the shared memory available in the kernel. The
386384 exact amount you need depends on your architecture and how many
387- buffers you configure postmaster to run with. For most systems, with
388- default buffer sizes, you need a minimum of ~760K.
385+ buffers and backend processes you configure postmaster to run with.
386+ For most systems, with default numbers of buffers and processes, you
387+ need a minimum of ~1MB.
389388
390389 2.7) I have changed a source file, but a recompile does not see the change?
391390
@@ -420,7 +419,7 @@ Section 2: Installation Questions
420419 If you are doing a lot of inserts, consider doing them in a large
421420 batch using the copy command. This is much faster than single
422421 individual inserts. Second, statements not in a begin work/commit
423- transaction block are considered to betheir in their own transaction.
422+ transaction block are considered to be in their own transaction.
424423 Consider performing several statements in a single transaction block.
425424 This reduces the transaction overhead. Also consider dropping and
426425 recreating indices when making large data changes.
@@ -436,7 +435,7 @@ Section 2: Installation Questions
436435
437436 You can also use the postgres -S option to increase the maximum amount
438437 of memory used by each backend process for temporary sorts. Each
439- buffer is 1K and thedefualt is 512 buffers.
438+ buffer is 1K and thedefault is 512 buffers.
440439
441440 You can also use the cluster command to group data in base tables to
442441 match an index. See the cluster(l) manual page for more details.
@@ -474,33 +473,72 @@ Section 2: Installation Questions
474473 operating system can attach to a running backend directly to diagnose
475474 problems.
476475
477- The postgres program has a -s, -A, -t options that can be veryusefull
476+ The postgres program has a -s, -A, -t options that can be veryuseful
478477 for debugging and performance measurements.
479478
480479 You can also compile with profiling to see what functions are taking
481480 execution time. The backend profile files will be deposited in the
482481 pgsql/data/base/dbname directory. The client profile file will be put
483482 in the current directory.
484483
485- 2.13) How do I enable more than 64 concurrent backends?
486-
487- Edit include/storage/sinvaladt.h, and change the value of
488- MaxBackendId. In the future, we plan to make this a configurable
489- prameter.
490-
491- 2.14) What non-unix ports are available?
484+ 2.13) When I try to start the postmaster, I get IpcSemaphoreCreate errors.
485+ Why?
486+
487+ If the error message is IpcSemaphoreCreate: semget failed (No space
488+ left on device) then your kernel is not configured with enough
489+ semaphores. Postgres needs one semaphore per potential backend
490+ process. A temporary solution is to start the postmaster with a
491+ smaller limit on the number of backend processes. Use -N with a
492+ parameter less than the default of 32. A more permanent solution is to
493+ increase your kernel's SEMMNS and SEMMNI parameters.
494+
495+ If the error message is something else, you might not have semaphore
496+ support configured in your kernel at all.
497+
498+ 2.14) I get 'Sorry, too many clients' when trying to connect. Why?
499+
500+ You need to increase the postmaster's limit on how many concurrent
501+ backend processes it can start.
502+
503+ In Postgres 6.5, the default limit is 32 processes. You can increase
504+ it by restarting the postmaster with a suitable -N value. With the
505+ default configuration you can set -N as large as 1024; if you need
506+ more, increase MAXBACKENDS in include/config.h and rebuild. You can
507+ set the default value of -N at configuration time, if you like, using
508+ configure's --with-maxbackends switch.
509+
510+ Note that if you make -N larger than 32, you should consider
511+ increasing -B beyond its default of 64. For large numbers of backend
512+ processes, you are also likely to find that you need to increase
513+ various Unix kernel configuration parameters. Things to check include
514+ the maximum size of shared memory blocks, SHMMAX, the maximum number
515+ of semaphores, SEMMNS and SEMMNI, the maximum number of processes,
516+ NPROC, the maximum number of processes per user, MAXUPRC, and the
517+ maximum number of open files, NFILE and NINODE. The reason that
518+ Postgres has a limit on the number of allowed backend processes is so
519+ that you can ensure that your system won't run out of resources.
520+
521+ In Postgres versions prior to 6.5, the maximum number of backends was
522+ 64, and changing it required a rebuild after altering the MaxBackendId
523+ constant in include/storage/sinvaladt.h.
524+
525+ 2.15) What non-unix ports are available?
492526
493527 It is possible to compile the libpq C library, psql, and other
494528 interfaces and binaries to run on MS Windows platforms. In this case,
495529 the client is running on MS Windows, and communicates via TCP/IP to a
496530 server running on one of our supported Unix platforms.
497531
498- A file win32.mak is included in thedistributiion for making a Win32
532+ A file win32.mak is included in thedistribution for making a Win32
499533 libpq library and psql.
500534
501- Someone is attempting to port our PostgreSQL database server to
502- Windows NT using the Cygnus Unix/NT porting library. He has gotten it
503- compiled, but initdb is currently failing.
535+ The database server is now working on Windows NT using the Cygnus
536+ Unix/NT porting library. The only feature missing is dynamic loading
537+ of user-defined functions/types. See
538+ http://www.askesis.nl/AskesisPostgresIndex.html for more information.
539+
540+ There is another port using U/Win at
541+ http://surya.wipro.com/uwin/ported.html.
504542 _________________________________________________________________
505543
506544Section 3: PostgreSQL Features
@@ -562,32 +600,42 @@ Section 3: PostgreSQL Features
562600 extending R-trees require a bit of work and we don't currently have
563601 any documentation on how to do it.
564602
565- 3.6) What is the maximum size for atuple ?
603+ 3.6) What is the maximum size for arow, table, database ?
566604
567- Tuples are limited to 8K bytes. Taking into account system attributes
605+ Rows are limited to 8K bytes. Taking into account system attributes
568606 and other overhead, one should stay well shy of 8,000 bytes to be on
569607 the safe side. To use attributes larger than 8K, try using the large
570608 objects interface.
571609
572- Tuples do not cross 8k boundaries so a 5ktuple will require 8k of
610+ Rows do not cross 8k boundaries so a 5krow will require 8k of
573611 storage.
574612
575- 3.7) I defined indices but my queries don't seem to make use of them. Why?
613+ Table and database sizes are unlimited. There are many databases that
614+ are tens of gigabytes, and probably some that are hundreds of
615+ gigabytes.
616+
617+ 3.7) My queries are slow or don't make use of the indexes. Why?
576618
577619 PostgreSQL does not automatically maintain statistics. One has to make
578620 an explicit vacuum call to update the statistics. After statistics are
579621 updated, the optimizer knows how many rows in the table, and can
580622 better decide if it should use indices. Note that the optimizer does
581- not use indices in cases when the table is small because a sequentail
582- scan would be faster. For column-specific optimization statistics, use
583- vacuum analyze.
623+ not use indices in cases when the table is small because a sequential
624+ scan would be faster.
625+
626+ For column-specific optimization statistics, use vacuum analyze.
627+ Vacuum analyze is important for complex multi-join queries, so the
628+ optimizer can estimate the number of rows returned from each table,
629+ and choose the proper join order. The backend does not keep track of
630+ column statistics on its own, and vacuum analyze must be run to
631+ collect them periodically.
584632
585633 Indexes are not used for order by operations.
586634
587- When usingwildcard operators like LIKE or ~, indices can only be used
588- if the beginning of the search is anchored to the start of the string.
589- So, to use indices, LIKE searches can should not begin with %, and
590- ~(regular expression searches) should start with ^.
635+ When usingwild-card operators like LIKE or ~, indices can only be
636+ used if the beginning of the search is anchored to the start of the
637+ string. So, to use indices, LIKE searches can should not begin with %,
638+ and ~(regular expression searches) should start with ^.
591639
592640 3.8) How do I do regular expression searches? case-insensitive regexp
593641 searching?
@@ -606,7 +654,7 @@ Type Internal Name Notes
606654CHAR char 1 character
607655CHAR(#) bpchar blank padded to the specified fixed length
608656VARCHAR(#) varchar size specifies maximum length, no padding
609- TEXT text length limited only by maximumtuple length
657+ TEXT text length limited only by maximumrow length
610658BYTEA bytea variable-length array of bytes
611659
612660 You need to use the internal name when doing internal operations.
@@ -681,12 +729,15 @@ BYTEA bytea variable-length array of bytes
681729 all databases. If you want to change the oid to something else, or if
682730 you want to make a copy of the table, with the original oid's, there
683731 is no reason you can't do it:
684- CREATE TABLE new_table (mycol int);
685- INSERT INTO new_table SELECT oid, mycol FROM old_table;
732+ CREATE TABLE new_table(old_oid oid, mycol int);
733+ SELECT INTO new SELECT old_oid, mycol FROM old;
734+ COPY new TO '/tmp/pgtable';
735+ DELETE FROM new;
736+ COPY new WITH OIDS FROM '/tmp/pgtable';
686737
687- Tids are used toindentify specific physical rows with block and
688- offset values. Tids change after rows are modified or reloaded. They
689- are used by index entries to point to physical rows.
738+ Tids are used toidentify specific physical rows with block and offset
739+ values. Tids change after rows are modified or reloaded. They are used
740+ by index entries to point to physical rows.
690741
691742 3.18) What is the meaning of some of the terms used in PostgreSQL?
692743
@@ -797,10 +848,8 @@ Section 4: Extending PostgreSQL
797848 4.2) I get messages of the type NOTICE:PortalHeapMemoryFree: 0x402251d0 not
798849 in alloc set!
799850
800- You are pfree'ing something that was not palloc'ed. When writing
801- user-defined functions, do not include the file "libpq-fe.h". Doing so
802- will cause your palloc to be a malloc instead of a free. Then, when
803- the backend pfrees the storage, you get the notice message.
851+ You are pfree'ing something that was not palloc'ed. Beware of mixing
852+ malloc/free and palloc/pfree.
804853
805854 4.3) I've written some nifty new types and functions for PostgreSQL.
806855