3 <TITLE>PostgreSQL FAQ</TITLE>
5 <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#FF0000" VLINK="#A00000" ALINK="#0000FF">
7 Frequently Asked Questions (FAQ) for PostgreSQL
10 Last updated: Fri Sep 29 23:04:02 EDT 2000
12 Current maintainer: Bruce Momjian (<A
13 HREF="mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR><P>
15 The most recent version of this document can be viewed at
16 <A HREF="http://www.Postgresql.org/docs/faq-english.html">
17 http://www.PostgreSQL.org/docs/faq-english.html</A>.<P>
19 Platform-specific questions are answered at <A
20 HREF="http://www.PostgreSQL.org/docs/">http://www.PostgreSQL.org/docs/</A>.<P>
24 <H2><CENTER>General Questions</CENTER></H2>
26 <A HREF="#1.1">1.1</A>) What is PostgreSQL?<BR>
27 <A HREF="#1.2">1.2</A>) What's the copyright on PostgreSQL?<BR>
28 <A HREF="#1.3">1.3</A>) What Unix platforms does PostgreSQL run on?<BR>
29 <A HREF="#1.4">1.4</A>) What non-unix ports are available?<BR>
30 <A HREF="#1.5">1.5</A>) Where can I get PostgreSQL?<BR>
31 <A HREF="#1.6">1.6</A>) Where can I get support?<BR>
32 <A HREF="#1.7">1.7</A>) What is the latest release?<BR>
33 <A HREF="#1.8">1.8</A>) What documentation is available?<BR>
34 <A HREF="#1.9">1.9</A>) How do I find out about known bugs or missing features?<BR>
35 <A HREF="#1.10">1.10</A>) How can I learn SQL?<BR>
36 <A HREF="#1.11">1.11</A>) Is PostgreSQL Y2K compliant?<BR>
37 <A HREF="#1.12">1.12</A>) How do I join the development team?<BR>
38 <A HREF="#1.13">1.13</A>) How do I submit a bug report?<BR>
39 <A HREF="#1.14">1.14</A>) How does PostgreSQL compare to other DBMS's?<BR>
42 <H2><CENTER>User Client Questions</CENTER></H2>
44 <A HREF="#2.1">2.1</A>) Are there ODBC drivers for
46 <A HREF="#2.2">2.2</A>) What tools are available for hooking
47 PostgreSQL to Web pages?<BR>
48 <A HREF="#2.3">2.3</A>) Does PostgreSQL have a graphical user interface?
49 A report generator? An embedded query language interface?<BR>
50 <A HREF="#2.4">2.4</A>) What languages are available to communicate
54 <H2><CENTER>Administrative Questions</CENTER></H2>
56 <A HREF="#3.1">3.1</A>) Why does <I>initdb</I> fail?<BR>
57 <A HREF="#3.2">3.2</A>) How do I install PostgreSQL somewhere other than
58 <I>/usr/local/pgsql?</I><BR>
59 <A HREF="#3.3">3.3</A>) When I start the <i>postmaster</i>, I get a
60 <I>Bad System Call</I> or core dumped message. Why?<BR>
61 <A HREF="#3.4">3.4</A>) When I try to start the <i>postmaster</i>, I get
62 <I>IpcMemoryCreate</I> errors. Why?<BR>
63 <A HREF="#3.5">3.5</A>) When I try to start the <i>postmaster</i>, I get
64 <I>IpcSemaphoreCreate</I> errors. Why?<BR>
65 <A HREF="#3.6">3.6</A>) How do I prevent other hosts from accessing my
66 PostgreSQL database?<BR>
67 <A HREF="#3.7">3.7</A>) Why can't I connect to my database from
69 <A HREF="#3.8">3.8</A>) Why can't I access the database as the
71 <A HREF="#3.9">3.9</A>) All my servers crash under concurrent
72 table access. Why?<BR>
73 <A HREF="#3.10">3.10</A>) How do I tune the database engine for
74 better performance?<BR>
75 <A HREF="#3.11">3.11</A>) What debugging features are available?<BR>
76 <A HREF="#3.12">3.12</A>) I get <I>"Sorry, too many clients"</I> when trying to
78 <A HREF="#3.13">3.13</A>) What are the <I>pg_sorttempNNN.NN</I> files in my
79 database directory?<BR>
81 <H2><CENTER>Operational Questions</CENTER></H2>
83 <A HREF="#4.1">4.1</A>) Why is the system confused about commas,
84 decimal points, and date formats.<BR>
85 <A HREF="#4.2">4.2</A>) What is the exact difference between
86 binary cursors and normal cursors?<BR>
87 <A HREF="#4.3">4.3</A>) How do I <small>SELECT</small> only the first few rows of
90 <A HREF="#4.4">4.4</A>) How do I get a list of tables or other
91 things I can see in <I>psql?</I><BR>
92 <A HREF="#4.5">4.5</A>) How do you remove a column from a table?<BR>
94 <A HREF="#4.6">4.6</A>) What is the maximum size for a
95 row, table, database?<BR>
96 <A HREF="#4.7">4.7</A>) How much database disk space is required
97 to store data from a typical text file?<BR>
99 <A HREF="#4.8">4.8</A>) How do I find out what indices or
100 operations are defined in the database?<BR>
101 <A HREF="#4.9">4.9</A>) My queries are slow or don't make use of the
103 <A HREF="#4.10">4.10</A>) How do I see how the query optimizer is
104 evaluating my query?<BR>
105 <A HREF="#4.11">4.11</A>) What is an R-tree index?<BR>
106 <A HREF="#4.12">4.12</A>) What is Genetic Query Optimization?<BR>
108 <A HREF="#4.13">4.13</A>) How do I do regular expression searches
109 and case-insensitive regular expression searches?<BR>
110 <A HREF="#4.14">4.14</A>) In a query, how do I detect if a field
112 <A HREF="#4.15">4.15</A>) What is the difference between the
113 various character types?<BR>
114 <A HREF="#4.16.1">4.16.1</A>) How do I create a serial/auto-incrementing field?<BR>
115 <A HREF="#4.16.2">4.16.2</A>) How do I get the value of a
116 <small>SERIAL</small> insert?<BR>
117 <A HREF="#4.16.3">4.16.3</A>) Don't <I>currval()</I> and <I>nextval()</I> lead to a
118 race condition with other users?<BR>
120 <A HREF="#4.17">4.17</A>) What is an <small>OID</small>? What is a
121 <small>TID</small>?<BR>
122 <A HREF="#4.18">4.18</A>) What is the meaning of some of the terms
123 used in PostgreSQL?<BR>
125 <A HREF="#4.19">4.19</A>) Why do I get the error <I>"FATAL: palloc
126 failure: memory exhausted?"</I><BR>
127 <A HREF="#4.20">4.20</A>) How do I tell what PostgreSQL version I
129 <A HREF="#4.21">4.21</A>) My large-object operations get <I>invalid
130 large obj descriptor.</I> Why?<BR>
131 <A HREF="#4.22">4.22</A>) How do I create a column that will default to the
133 <A HREF="#4.23">4.23</A>) Why are my subqueries using
134 <CODE><small>IN</small></CODE> so slow?<BR>
135 <A HREF="#4.24">4.24</A>) How do I do an <i>outer</i> join?<BR>
137 <H2><CENTER>Extending PostgreSQL</CENTER></H2>
139 <A HREF="#5.1">5.1</A>) I wrote a user-defined function. When I run
140 it in <I>psql,</I> why does it dump core?<BR>
141 <A HREF="#5.2">5.2</A>) What does the message
142 <I>"NOTICE:PortalHeapMemoryFree: 0x402251d0 not in alloc set!"</I> mean?<BR>
143 <A HREF="#5.3">5.3</A>) How can I contribute some nifty new types and functions
145 <A HREF="#5.4">5.4</A>) How do I write a C function to return a
147 <A HREF="#5.5">5.5</A>) I have changed a source file. Why does the
148 recompile not see the change?<BR>
153 <H2><CENTER>General Questions</CENTER></H2>
155 NAME="1.1">1.1</A>) What is PostgreSQL?</H4><P>
157 PostgreSQL is an enhancement of the POSTGRES database management system,
158 a next-generation DBMS research prototype. While PostgreSQL retains the
159 powerful data model and rich data types of POSTGRES, it replaces the
160 PostQuel query language with an extended subset of SQL. PostgreSQL is
161 free and the complete source is available.<P>
163 PostgreSQL development is performed by a team of Internet
164 developers who all subscribe to the PostgreSQL development mailing list.
165 The current coordinator is Marc G. Fournier (<A
166 HREF="mailto:scrappy@PostgreSQL.org">scrappy@PostgreSQL.org</A>). (See
167 below on how to join). This team is now responsible for all development
170 The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. Many
171 others have contributed to the porting, testing, debugging and
172 enhancement of the code. The original Postgres code, from which
173 PostgreSQL is derived, was the effort of many graduate students,
174 undergraduate students, and staff programmers working under the
175 direction of Professor Michael Stonebraker at the University of
176 California, Berkeley.<P>
178 The original name of the software at Berkeley was Postgres. When SQL
179 functionality was added in 1995, its name was changed to Postgres95. The
180 name was changed at the end of 1996 to PostgreSQL.<P>
182 It is pronounced <I>Post-Gres-Q-L.</I>
184 <H4><A NAME="1.2">1.2</A>) What's the copyright on
187 PostgreSQL is subject to the following COPYRIGHT.<P>
189 PostgreSQL Data Base Management System<P>
191 Portions copyright (c) 1996-2000, PostgreSQL, Inc
193 Portions Copyright (c) 1994-6 Regents of the University of California<P>
195 Permission to use, copy, modify, and distribute this software and its
196 documentation for any purpose, without fee, and without a written
197 agreement is hereby granted, provided that the above copyright notice
198 and this paragraph and the following two paragraphs appear in all
201 IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
202 FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,
203 INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
204 DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF
205 THE POSSIBILITY OF SUCH DAMAGE.<P>
207 THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
208 INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
209 AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER
210 IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO
211 OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR
216 <H4><A NAME="1.3">1.3</A>) What Unix platforms does PostgreSQL run
219 The authors have compiled and tested PostgreSQL on the following
220 platforms (some of these compiles require gcc):
222 <LI> aix - IBM on AIX 3.2.5 or 4.x
223 <LI> alpha - DEC Alpha AXP on Digital Unix 2.0, 3.2, 4.0
224 <LI> BSD44_derived - OSs derived from 4.4-lite BSD (NetBSD, FreeBSD)
225 <LI> bsdi - BSD/OS 2.x, 3.x, 4.x
226 <LI> dgux - DG/UX 5.4R4.11
227 <LI> hpux - HP PA-RISC on HP-UX 9.*, 10.*
228 <LI> i386_solaris - i386 Solaris
229 <LI> irix5 - SGI MIPS on IRIX 5.3
230 <LI> linux - Intel i86
237 <LI> sparc_solaris - SUN SPARC on Solaris 2.4, 2.5, 2.5.1
238 <LI> sunos4 - SUN SPARC on SunOS 4.1.3
239 <LI> svr4 - Intel x86 on Intel SVR4 and MIPS
240 <LI> ultrix4 - DEC MIPS on Ultrix 4.4
244 <H4><A NAME="1.4">1.4</A>) What non-unix ports are available?</H4><P>
246 It is possible to compile the <I>libpq</I> C library, psql, and other
247 interfaces and binaries to run on MS Windows platforms. In this case,
248 the client is running on MS Windows, and communicates via TCP/IP to a
249 server running on one of our supported Unix platforms.<P>
251 A file <I>win31.mak</I> is included in the distribution for making a
252 Win32 <I>libpq</I> library and psql.<P>
254 The database server is now working on Windows NT using the Cygnus
255 Unix/NT porting library. See <I>pgsql/doc/FAQ_NT</I> in the distribution.<P>
258 <H4><A NAME="1.5">1.5</A>) Where can I get PostgreSQL?</H4><P>
259 The primary anonymous ftp site for PostgreSQL is
261 HREF="ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub</A>.
262 For mirror sites, see our main web site.
264 <H4><A NAME="1.6">1.6</A>) Where can I get support?</H4><P>
266 There is no support for PostgreSQL from the University of
267 California, Berkeley. It is maintained through volunteer effort.<P>
269 The main mailing list is: <A
270 HREF="mailto:pgsql-general@PostgreSQL.org">pgsql-general@PostgreSQL.org</A>.
271 It is available for discussion of matters pertaining to PostgreSQL.
272 To subscribe, send mail with the following lines in the body (not
281 HREF="mailto:pgsql-general-request@PostgreSQL.org">pgsql-general-request@PostgreSQL.org</A>.<P>
283 There is also a digest list available. To subscribe to this list, send
284 email to: <A HREF="mailto:pgsql-general-digest-request@PostgreSQL.org">
285 pgsql-general-digest-request@PostgreSQL.org</A> with a body of:
292 Digests are sent out to members of this list whenever the main list has
293 received around 30k of messages.<P>
295 The bugs mailing list is available. To subscribe to this list, send email
297 HREF="mailto:pgsql-bugs-request@PostgreSQL.org">pgsql-bugs-request@PostgreSQL.org</A>
305 There is also a developers discussion mailing list available. To
306 subscribe to this list, send email to <A
307 HREF="mailto:pgsql-hackers-request@PostgreSQL.org">pgsql-hackers-request@PostgreSQL.org</A>
315 Additional mailing lists and information about PostgreSQL can be found
316 via the PostgreSQL WWW home page at:
319 <A HREF="http://www.PostgreSQL.org">http://www.PostgreSQL.org</A>
322 There is also an IRC channel on EFNet, channel <I>#PostgreSQL.</I>
323 I use the unix command <CODE>irc -c '#PostgreSQL' "$USER"
324 irc.phoenix.net.</CODE><P>
326 Commercial support for PostgreSQL is available at <A
327 HREF="http://www.pgsql.com">http://www.pgsql.com/</A>.<P>
330 <H4><A NAME="1.7">1.7</A>) What is the latest release?</H4><P>
332 The latest release of PostgreSQL is version 7.0.2.<P>
334 We plan to have major releases every four months.<P>
337 <H4><A NAME="1.8">1.8</A>) What documentation is available?</H4><P>
339 Several manuals, manual pages, and some small test examples are
340 included in the distribution. See the <I>/doc</I> directory. You can also
341 browse the manual on-line at <A
342 HREF="http://www.PostgreSQL.org/docs/postgres">
343 http://www.PostgreSQL.org/docs/postgres</A>.
346 There is a PostgreSQL book available at <A
347 HREF="http://www.PostgreSQL.org/docs/awbook.html">
348 http://www.PostgreSQL.org/docs/awbook.html</A>.<P>
350 <I>psql</I> has some nice \d commands to show information about types,
351 operators, functions, aggregates, etc.<P>
353 Our web site contains even more documentation.<P>
355 <H4><A NAME="1.9">1.9</A>) How do I find out about known bugs or missing features?
358 PostgreSQL supports an extended subset of SQL-92. See our
359 <A HREF="http://www.PostgreSQL.org/docs/todo.html">
360 TODO</A> list for known bugs, missing features, and future plans.<P>
362 <H4><A NAME="1.10">1.10</A>) How can I learn SQL?</H4><P>
364 The PostgreSQL book at <A
365 HREF="http://www.PostgreSQL.org/docs/awbook.html">
366 http://www.PostgreSQL.org/docs/awbook.html</A> teaches SQL.
368 There is a nice tutorial at <A
369 HREF="http://w3.one.net/~jhoffman/sqltut.htm">
370 http://w3.one.net/~jhoffman/sqltut.htm</A> and at <A
371 HREF="http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM">
372 http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM.</A><P>
374 Another one is "Teach Yourself SQL in 21 Days, Second Edition" at <A
375 HREF="http://members.tripod.com/er4ebus/sql/index.htm">
376 http://members.tripod.com/er4ebus/sql/index.htm </A><P>
378 Many of our users like <I>The Practical SQL Handbook</I>, Bowman, Judith
379 S., et al., Addison Wesley. Others like <I>The Complete Reference
380 SQL</I>, Groff et al., McGraw-Hill.<P>
383 <H4><A NAME="1.11">1.11</A>) Is PostgreSQL Y2K compliant?</H4><P>
385 Yes, we easily handle dates past the year 2000AD, and before 2000BC.<P>
388 <H4><A NAME="1.12">1.12</A>) How do I join the development team?</H4><P>
390 First, download the latest source and read the PostgreSQL Developers
391 documentation on our web site, or in the distribution.
392 Second, subscribe to the <I>pgsql-hackers</I> and <I>pgsql-patches</I> mailing lists.
393 Third, submit high-quality patches to pgsql-patches.<P>
395 There are about a dozen people who have commit privileges to
396 the PostgreSQL CVS archive. They each have submitted so many
397 high-quality patches that it was impossible for the existing
398 committers to keep up, and we had confidence that patches they
399 committed were of high quality.
401 <H4><A NAME="1.13">1.13</A>) How do I submit a bug report?</H4><P>
403 Fill out the "bug-template" file and send it to: <A
404 HREF="mailto:pgsql-bugs@PostgreSQL.org">pgsql-bugs@PostgreSQL.org</A><P>
406 Also check out our ftp site <A
407 HREF="ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub</A> to
408 see if there is a more recent PostgreSQL version or patches.<P>
411 <H4><A NAME="1.14">1.14</A>) How does PostgreSQL compare to other
414 There are several ways of measuring software: features, performance,
415 reliability, support, and price.<P>
421 PostgreSQL has most features present in large commercial DBMS's, like
422 transactions, subselects, triggers, views, foreign key referential
423 integrity, and sophisticated locking. We have some features they don't
424 have, like user-defined types, inheritance, rules, and multi-version
425 concurrency control to reduce lock contention. We don't have outer
426 joins, but are working on them.<BR><BR>
428 <DT> <B>Performance</B>
431 PostgreSQL runs in two modes. Normal <I>fsync</I> mode flushes every
432 completed transaction to disk, guaranteeing that if the OS crashes or
433 loses power in the next few seconds, all your data is safely stored on
434 disk. In this mode, we are slower than most commercial databases, partly
435 because few of them do such conservative flushing to disk in their
436 default modes. In <I>no-fsync</I> mode, we are usually faster than
437 commercial databases, though in this mode, an OS crash could cause data
438 corruption. We are working to provide an intermediate mode that suffers
439 less performance overhead than full fsync mode, and will allow data
440 integrity within 30 seconds of an OS crash.<BR><BR>
442 In comparison to MySQL or leaner database systems, we are slower on
443 inserts/updates because we have transaction overhead. Of course, MySQL
444 doesn't have any of the features mentioned in the <I>Features</I>
445 section above. We are built for flexibility and features, though we
446 continue to improve performance through profiling and source code
447 analysis. There is an interesting web page comparing PostgreSQL to MySQL
448 at <a href="http://openacs.org/why-not-mysql.html">
449 http://openacs.org/why-not-mysql.html</a><BR><BR>
451 We handle each user connection by creating a Unix process. Backend
452 processes share data buffers and locking information. With multiple
453 CPU's, multiple backends can easily run on different CPU's.<BR><BR>
455 <DT> <B>Reliability</B>
458 We realize that a DBMS must be reliable, or it is worthless. We strive
459 to release well-tested, stable code that has a minimum of bugs. Each
460 release has at least one month of beta testing, and our release history
461 shows that we can provide stable, solid releases that are ready for
462 production use. We believe we compare favorably to other database
463 software in this area.<BR><BR>
468 Our mailing list provides a large group of developers and users to help
469 resolve any problems encountered. While we can not guarantee a fix,
470 commercial DBMS's don't always supply a fix either. Direct access to
471 developers, the user community, manuals, and the source code often make
472 PostgreSQL support superior to other DBMS's.
473 There is commercial per-incident support available for those who need
474 it. (See support FAQ item.)<BR><BR>
479 We are free for all use, both commercial and non-commercial. You can
480 add our code to your product with no limitations, except those outlined
481 in our BSD-style license stated above.<BR><BR>
486 <H2><CENTER>User Client Questions</CENTER></H2>
491 <H4><A NAME="2.1">2.1</A>) Are there ODBC drivers for PostgreSQL?</H4><P>
493 There are two ODBC drivers available, PsqlODBC and OpenLink ODBC.<P>
495 PsqlODBC is included in the distribution. More information about it can
496 be gotten from <A HREF="ftp://ftp.PostgreSQL.org/pub/odbc/">
497 ftp://ftp.PostgreSQL.org/pub/odbc/</A>.<P>
499 OpenLink ODBC can be gotten from <A HREF="http://www.openlinksw.com/">
500 http://www.openlinksw.com</A>. It works with their standard ODBC client
501 software so you'll have PostgreSQL ODBC available on every client
502 platform they support (Win, Mac, Unix, VMS).<P>
504 They will probably be selling this product to people who need
505 commercial-quality support, but a freeware version will always be
506 available. Questions to <A
507 HREF="mailto:postgres95@openlink.co.uk">postgres95@openlink.co.uk</A>.<P>
509 See also the <A HREF="http://www.PostgreSQL.org/docs/programmer/odbc.htm">
510 ODBC chapter of the Programmer's Guide</A>.<P>
513 <H4><A NAME="2.2">2.2</A>) What tools are available for hooking
514 PostgreSQL to Web pages?</H4><P>
516 A nice introduction to Database-backed Web pages can be seen at: <A
517 HREF="http://www.webtools.com">http://www.webtools.com</A><P>
519 There is also one at <A HREF="http://www.phone.net/home/mwm/hotlist/">
520 http://www.phone.net/home/mwm/hotlist/.</A><P>
522 For web integration, PHP is an excellent interface. It is at
523 <A HREF="http://www.php.net">http://www.php.net</A><P>
525 For complex cases, many use the Perl interface and CGI.pm.<P>
527 A WWW gateway based on WDB using Perl can be downloaded from <A
528 HREF="http://www.eol.ists.ca/~dunlop/wdb-p95">http://www.eol.ists.ca/~dunlop/wdb-p95</A>
530 <H4><A NAME="2.3">2.3</A>) Does PostgreSQL have a graphical user interface?
531 A report generator? An embedded query language interface?</H4><P>
533 We have a nice graphical user interface called <I>pgaccess,</I> which is
534 shipped as part of the distribution. <I>Pgaccess</I> also has a report
535 generator. The web page is <A HREF=
536 "http://www.flex.ro/pgaccess">http://www.flex.ro/pgaccess</A><P>
538 We also include <I>ecpg,</I> which is an embedded SQL query language interface for
541 <H4><A NAME="2.4">2.4</A>) What languages are available to
542 communicate with PostgreSQL?</H4><P>
548 <LI>Embedded C (ecpg)
552 <LI>Python (PyGreSQL)
554 <LI>C Easy API (libpgeasy)
555 <LI>Embedded HTML (<A HREF="http://www.php.net">PHP from http://www.php.net</A>)
560 <H2><CENTER>Administrative Questions</CENTER></H2><P>
563 <H4><A NAME="3.1">3.1</A>) Why does <I>initdb</I> fail?</H4><P>
567 <LI> check that you don't have any of the previous version's binaries in
569 <LI> check to see that you have the proper paths set
570 <LI> check that the <I>postgres</I> user owns the proper files
574 <H4><A NAME="3.2">3.2</A>) How do I install PostgreSQL somewhere
575 other than <I>/usr/local/pgsql?</I></H4><P>
577 The simplest way is to specify the --prefix option when running <I>configure.</I>
578 If you forgot to do that, you can edit <I>Makefile.global</I> and change POSTGRESDIR
579 accordingly, or create a <I>Makefile.custom</I> and define POSTGRESDIR there.<P>
582 <H4><A NAME="3.3">3.3</A>) When I start the <i>postmaster</i>, I get a <I>Bad
583 System Call</I> or core dumped message. Why?</H4><P>
585 It could be a variety of problems, but first check to see that you
586 have System V extensions installed in your kernel. PostgreSQL requires
587 kernel support for shared memory and semaphores.<P>
590 <H4><A NAME="3.4">3.4</A>) When I try to start the <i>postmaster,</i> I
591 get <I>IpcMemoryCreate</I> errors. Why?</H4><P>
593 You either do not have shared memory configured properly in your kernel or
594 you need to enlarge the shared memory available in the kernel. The
595 exact amount you need depends on your architecture and how many buffers
596 and backend processes you configure for the <i>postmaster.</i>
597 For most systems, with default numbers of buffers and processes, you
598 need a minimum of ~1MB.<P>
600 <H4><A NAME="3.5">3.5</A>) When I try to start the <i>postmaster,</i> I
601 get <I>IpcSemaphoreCreate</I> errors. Why?</H4><P>
603 If the error message is <I>IpcSemaphoreCreate: semget failed (No space
604 left on device)</I> then your kernel is not configured with enough
605 semaphores. Postgres needs one semaphore per potential backend process.
606 A temporary solution is to start the <i>postmaster</i> with a smaller limit on
607 the number of backend processes. Use <I>-N</I> with a parameter less
608 than the default of 32. A more permanent solution is to increase your
609 kernel's <SMALL>SEMMNS</SMALL> and <SMALL>SEMMNI</SMALL> parameters.<P>
611 If the error message is something else, you might not have semaphore
612 support configured in your kernel at all.<P>
615 <H4><A NAME="3.6">3.6</A>) How do I prevent other hosts from
616 accessing my PostgreSQL database?</H4><P>
618 By default, PostgreSQL only allows connections from the local machine
619 using Unix domain sockets. Other machines will not be able to connect
620 unless you add the <I>-i</I> flag to the <I>postmaster,</I>
621 <B>and</B> enable host-based authentication by modifying the file
622 <I>$PGDATA/pg_hba.conf</I> accordingly. This will allow TCP/IP connections.
625 <H4><A NAME="3.7">3.7</A>) Why can't I connect to my database from
626 another machine?</H4><P>
628 The default configuration allows only unix domain socket connections
629 from the local machine. To enable TCP/IP connections, make sure the
630 <i>postmaster</i> has been started with the <I>-i</I> option, and add an
631 appropriate host entry to the file
632 <I>pgsql/data/pg_hba.conf</I>.
634 <H4><A NAME="3.8">3.8</A>) Why can't I access the database as the <I>root</I>
637 You should not create database users with user id 0 (root). They will be
638 unable to access the database. This is a security precaution because
639 of the ability of users to dynamically link object modules into the
643 <H4><A NAME="3.9">3.9</A>) All my servers crash under concurrent
644 table access. Why?</H4><P>
646 This problem can be caused by a kernel that is not configured to support
650 <H4><A NAME="3.10">3.10</A>) How do I tune the database engine for
651 better performance?</H4><P>
653 Certainly, indices can speed up queries. The <SMALL>EXPLAIN</SMALL> command
654 allows you to see how PostgreSQL is interpreting your query, and which
655 indices are being used.<P>
657 If you are doing a lot of <SMALL>INSERTs</SMALL>, consider doing them in a large
658 batch using the <SMALL>COPY</SMALL> command. This is much faster than
659 individual <SMALL>INSERTS.</SMALL> Second, statements not in a <SMALL>BEGIN
660 WORK/COMMIT</SMALL> transaction block are considered to be in their
661 own transaction. Consider performing several statements in a single
662 transaction block. This reduces the transaction overhead. Also
663 consider dropping and recreating indices when making large data
666 There are several tuning options. You can disable
667 <I>fsync()</I> by starting the <I>postmaster</I> with a <I>-o -F</I>
668 option. This will prevent <I>fsync()'s</I> from flushing to disk after
669 every transaction.<P>
671 You can also use the <I>postmaster</I> <I>-B</I> option to increase the number of
672 shared memory buffers used by the backend processes. If you make this
673 parameter too high, the <I>postmaster</I> may not start because you've exceeded
674 your kernel's limit on shared memory space.
675 Each buffer is 8K and the default is 64 buffers.<P>
677 You can also use the backend <I>-S</I> option to increase the maximum amount
678 of memory used by the backend process for temporary sorts. The <I>-S</I> value
679 is measured in kilobytes, and the default is 512 (ie, 512K).<P>
681 You can also use the <SMALL>CLUSTER</SMALL> command to group data in tables to
682 match an index. See the <small>CLUSTER</small> manual page for more details.<P>
685 <H4><A NAME="3.11">3.11</A>) What debugging features are available?</H4><P>
687 PostgreSQL has several features that report status information that can
688 be valuable for debugging purposes.<P>
690 First, by running <I>configure</I> with the --enable-cassert option, many
691 <I>assert()'s</I> monitor the progress of the backend and halt the program when
692 something unexpected occurs.<P>
694 Both <I>postmaster</I> and <I>postgres</I> have several debug options available.
695 First, whenever you start the <I>postmaster,</I> make sure you send the
696 standard output and error to a log file, like:
699 ./bin/postmaster >server.log 2>&1 &
702 This will put a server.log file in the top-level PostgreSQL directory.
703 This file contains useful information about problems or errors
704 encountered by the server. <I>Postmaster</I> has a <I>-d</I> option that allows even
705 more detailed information to be reported. The <I>-d</I> option takes a number
706 that specifies the debug level. Be warned that high debug level values
707 generate large log files.<P>
709 If the <i>postmaster</i> is not running, you can actually run the
710 <I>postgres</I> backend from the command line, and type your SQL statement
711 directly. This is recommended <B>only</B> for debugging purposes. Note
712 that a newline terminates the query, not a semicolon. If you have
713 compiled with debugging symbols, you can use a debugger to see what is
714 happening. Because the backend was not started from the <I>postmaster,</I> it
715 is not running in an identical environment and locking/backend
716 interaction problems may not be duplicated.<P>
718 If the <i>postmaster</i> is running, start <I>psql</I> in one window,
719 then find the <small>PID</small> of the <i>postgres</i> process used by
720 <i>psql.</i> Use a debugger to attach to the <i>postgres</i>
721 <small>PID.</small> You can set breakpoints in the debugger and issue
722 queries from <i>psql.</i> If you are debugging <i>postgres</i> startup,
723 you can set PGOPTIONS="-W n", then start <i>psql.</i> This will cause
724 startup to delay for <i>n</i> seconds so you can attach with the
725 debugger and trace through the startup sequence.<P>
727 The <I>postgres</I> program has <I>-s, -A,</I> and <I>-t</I> options that can be very useful
728 for debugging and performance measurements.<P>
730 You can also compile with profiling to see what functions are taking
731 execution time. The backend profile files will be deposited in the
732 <I>pgsql/data/base/dbname</I> directory. The client profile file will be put
733 in the client's current directory.<P>
736 <H4><A NAME="3.12">3.12</A>) I get 'Sorry, too many clients' when trying
737 to connect. Why?</H4><P>
739 You need to increase the <i>postmaster's</i> limit on how many concurrent backend
740 processes it can start.<P>
742 In PostgreSQL 6.5 and up, the default limit is 32 processes. You can
743 increase it by restarting the <i>postmaster</i> with a suitable <I>-N</I>
744 value. With the default configuration you can set <I>-N</I> as large as
745 1024. If you need more, increase <SMALL>MAXBACKENDS</SMALL> in
746 <I>include/config.h</I> and rebuild. You can set the default value of
747 <I>-N</I> at configuration time, if you like, using <I>configure's</I>
748 <I>--with-maxbackends</I> switch.<P>
750 Note that if you make <I>-N</I> larger than 32, you must also increase
751 <I>-B</I> beyond its default of 64; <I>-B</I> must be at least twice <I>-N,</I> and
752 probably should be more than that for best performance. For large
753 numbers of backend processes, you are also likely to find that you need
754 to increase various Unix kernel configuration parameters. Things to
755 check include the maximum size of shared memory blocks,
756 <SMALL>SHMMAX,</SMALL> the maximum number of semaphores,
757 <SMALL>SEMMNS</SMALL> and <SMALL>SEMMNI,</SMALL> the maximum number of
758 processes, <SMALL>NPROC,</SMALL> the maximum number of processes per
759 user, <SMALL>MAXUPRC,</SMALL> and the maximum number of open files,
760 <SMALL>NFILE</SMALL> and <SMALL>NINODE.</SMALL> The reason that PostgreSQL
761 has a limit on the number of allowed backend processes is so
762 your system won't run out of resources.<P>
764 In PostgreSQL versions prior to 6.5, the maximum number of backends was
765 64, and changing it required a rebuild after altering the MaxBackendId
766 constant in <I>include/storage/sinvaladt.h.</I><P>
768 <H4><A NAME="3.13">3.13</A>) What are the <I>pg_sorttempNNN.NN</I> files in my
769 database directory?</H4><P>
771 They are temporary files generated by the query executor. For
772 example, if a sort needs to be done to satisfy an <SMALL>ORDER BY,</SMALL> and
773 the sort requires more space than the backend's <I>-S</I> parameter allows,
774 then temporary files are created to hold the extra data.<P>
776 The temporary files should be deleted automatically, but might not if a backend
777 crashes during a sort. If you have no backends running at the time,
778 it is safe to delete the pg_tempNNN.NN files.<P>
782 <H2><CENTER>Operational Questions</CENTER></H2><P>
785 <H4><A NAME="4.1">4.1</A>) Why is system confused about
786 commas, decimal points, and date formats.</H4><P>
788 Check your locale configuration. PostgreSQL uses the locale setting of
789 the user that ran the <i>postmaster</i> process. There are postgres and psql
790 SET commands to control the date format. Set those accordingly for
791 your operating environment.<P>
794 <H4><A NAME="4.2">4.2</A>) What is the exact difference between
795 binary cursors and normal cursors?</H4><P>
797 See the <SMALL>DECLARE</SMALL> manual page for a description.<P>
799 <H4><A NAME="4.3">4.3</A>) How do I <SMALL>SELECT</SMALL> only the first few
800 rows of a query?</H4><P>
802 See the <SMALL>FETCH</SMALL> manual page, or use SELECT ... LIMIT....<P>
804 The entire query may have to be evaluated, even if you only want the
805 first few rows. Consider a query that has an <SMALL>ORDER BY.</SMALL>
806 If there is an index that matches the <SMALL>ORDER BY</SMALL>,
807 PostgreSQL may be able to evaluate only the first few records requested,
808 or the entire query may have to be evaluated until the desired rows have
811 <H4><A NAME="4.4">4.4</A>) How do I get a list of tables or other
812 things I can see in <I>psql?</I><BR></H4><P>
814 You can read the source code for <I>psql</I> in file
815 <I>pgsql/src/bin/psql/psql.c.</I> It contains SQL commands that generate the
816 output for psql's backslash commands. You can also start <I>psql</I>
817 with the <I>-E</I> option so it will print out the queries it uses
818 to execute the commands you give.<P>
821 <H4><A NAME="4.5">4.5</A>) How do you remove a column from a
824 We do not support <SMALL>ALTER TABLE DROP COLUMN,</SMALL> but do
827 SELECT ... -- select all columns but the one you want to remove
830 DROP TABLE old_table;
831 ALTER TABLE new_table RENAME TO old_table;
837 <H4><A NAME="4.6">4.6</A>) What is the maximum size for a
838 row, table, database?</H4><P>
840 These are the limits:
843 Maximum size for a database? unlimited (60GB databases exist)
844 Maximum size for a table? unlimited on all operating systems
845 Maximum size for a row? 8k, configurable to 32k
846 Maximum number of rows in a table? unlimited
847 Maximum number of columns table? unlimited
848 Maximum number of indexes on a table? unlimited
851 Of course, these are not actually unlimited, but limited to available
854 To change the maximum row size, edit <I>include/config.h</I> and change
855 <SMALL>BLCKSZ.</SMALL> To use attributes larger than 8K, you can also
856 use the large object interface.<P>
858 The row length limit will be removed in 7.1.<P>
861 <H4><A NAME="4.7">4.7</A>)How much database disk space is required to
862 store data from a typical text file?<BR></H4><P>
864 A PostgreSQL database may need six and a half times the disk space
865 required to store the data in a flat file.<P>
867 Consider a file of 300,000 lines with two integers on each line. The
868 flat file is 2.4MB. The size of the PostgreSQL database file containing
869 this data can be estimated at 14MB:
872 36 bytes: each row header (approximate)
873 + 8 bytes: two int fields @ 4 bytes each
874 + 4 bytes: pointer on page to tuple
875 ----------------------------------------
878 The data page size in PostgreSQL is 8192 bytes (8 KB), so:
881 ------------------- = 171 rows per database page (rounded up)
885 -------------------- = 1755 database pages
888 1755 database pages * 8192 bytes per page = 14,376,960 bytes (14MB)
891 Indexes do not require as much overhead, but do contain the data that is
892 being indexed, so they can be large also.<P>
894 <H4><A NAME="4.8">4.8</A>) How do I find out what indices or
895 operations are defined in the database?</H4><P>
897 <I>psql</I> has a variety of backslash commands to show such information. Use
900 Also try the file <I>pgsql/src/tutorial/syscat.source.</I> It
901 illustrates many of the <SMALL>SELECT</SMALL>s needed to get information from
902 the database system tables.<P>
905 <H4><A NAME="4.9">4.9</A>) My queries are slow or don't make
906 use of the indexes. Why?</H4><P>
908 PostgreSQL does not automatically maintain statistics. V<SMALL>ACUUM</SMALL>
909 must be run to update the statistics. After
910 statistics are updated, the optimizer knows how many rows in the table,
911 and can better decide if it should use indices. Note that the optimizer
912 does not use indices in cases when the table is small because a
913 sequential scan would be faster.<P>
915 For column-specific optimization statistics, use <SMALL>VACUUM
916 ANALYZE.</SMALL> <SMALL>VACUUM ANALYZE</SMALL> is important for complex
917 multi-join queries, so the optimizer can estimate the number of rows
918 returned from each table, and choose the proper join order. The backend
919 does not keep track of column statistics on its own, so <SMALL>VACUUM
920 ANALYZE</SMALL> must be run to collect them periodically.<P>
922 Indexes are usually not used for <SMALL>ORDER BY</SMALL> operations: a
923 sequential scan followed by an explicit sort is faster than an indexscan
924 of all tuples of a large table, because it takes fewer disk accesses.
927 When using wild-card operators such as <SMALL>LIKE</SMALL> or <I>~,</I> indices can
928 only be used if the beginning of the search is anchored to the start of
929 the string. So, to use indices, <SMALL>LIKE</SMALL> searches should not
930 begin with <I>%,</I> and <I>~</I>(regular expression searches) should
933 <H4><A NAME="4.10">4.10</A>) How do I see how the query optimizer is
934 evaluating my query?</H4><P>
936 See the <SMALL>EXPLAIN</SMALL> manual page.<P>
938 <H4><A NAME="4.11">4.11</A>) What is an R-tree index?</H4><P>
940 An R-tree index is used for indexing spatial data. A hash index can't
941 handle range searches. A B-tree index only handles range searches in a
942 single dimension. R-tree's can handle multi-dimensional data. For
943 example, if an R-tree index can be built on an attribute of type <I>point,</I>
944 the system can more efficient answer queries like select all points
945 within a bounding rectangle.<P>
947 The canonical paper that describes the original R-Tree design is:<P>
949 Guttman, A. "R-Trees: A Dynamic Index Structure for Spatial Searching."
950 Proc of the 1984 ACM SIGMOD Int'l Conf on Mgmt of Data, 45-57.<P>
952 You can also find this paper in Stonebraker's "Readings in Database
955 Builtin R-Trees can handle polygons and boxes. In theory, R-trees can
956 be extended to handle higher number of dimensions. In practice,
957 extending R-trees require a bit of work and we don't currently have any
958 documentation on how to do it.<P>
961 <H4><A NAME="4.12">4.12</A>) What is Genetic Query
962 Optimization?</H4><P>
964 The GEQO module speeds query
965 optimization when joining many tables by means of a Genetic
966 Algorithm (GA). It allows the handling of large join queries through
967 non-exhaustive search.<P>
969 <H4><A NAME="4.13">4.13</A>) How do I do regular expression searches and
970 case-insensitive regular expression searches?</H4><P>
972 The <I>~</I> operator does regular-expression matching, and <I>~*</I>
973 does case-insensitive regular-expression matching. There is no
974 case-insensitive variant of the LIKE operator, but you can get the
975 effect of case-insensitive <SMALL>LIKE</SMALL> with this:
977 WHERE lower(textfield) LIKE lower(pattern)
980 <H4><A NAME="4.14">4.14</A>) In a query, how do I detect if a field
983 You test the column with IS NULL and IS NOT NULL.<P>
986 <H4><A NAME="4.15">4.15</A>) What is the difference between the
987 various character types?</H4>
990 Type Internal Name Notes
991 --------------------------------------------------
992 "char" char 1 character
993 CHAR(#) bpchar blank padded to the specified fixed length
994 VARCHAR(#) varchar size specifies maximum length, no padding
995 TEXT text length limited only by maximum row length
996 BYTEA bytea variable-length array of bytes
999 You will see the internal name when examining system catalogs
1000 and in some error messages.<P>
1002 The last four types above are "varlena" types (i.e. the first four bytes
1003 are the length, followed by the data). <I>char(#)</I> allocates the
1004 maximum number of bytes no matter how much data is stored in the field.
1005 <I>text, varchar(#),</I> and <I>bytea</I> all have variable length on the disk,
1006 and because of this, there is a small performance penalty for using
1007 them. Specifically, the penalty is for access to all columns after the
1008 first column of this type.<P>
1011 <H4><A NAME="4.16.1">4.16.1</A>) How do I create a
1012 serial/auto-incrementing field?</H4><P>
1014 PostgreSQL supports a <SMALL>SERIAL</SMALL> data type. It auto-creates a
1015 sequence and index on the column. For example, this:
1017 CREATE TABLE person (
1022 is automatically translated into this:
1024 CREATE SEQUENCE person_id_seq;
1025 CREATE TABLE person (
1026 id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
1029 CREATE UNIQUE INDEX person_id_key ON person ( id );
1031 See the <I>create_sequence</I> manual page for more information about sequences.
1033 You can also use each row's <I>OID</I> field as a unique value. However, if
1034 you need to dump and reload the database, you need to use <I>pg_dump's -o</I>
1035 option or <SMALL>COPY WITH OIDS</SMALL> option to preserve the <small>OID</small>s.<P>
1037 <A HREF="http://www.PostgreSQL.org/docs/aw_pgsql_book">Numbering Rows.</A>
1039 <H4><A NAME="4.16.2">4.16.2</A>) How do I get the value of a
1040 <small>SERIAL</small> insert?</H4><P>
1041 One approach is to to retrieve the next SERIAL value from the sequence object with the <I>nextval()</I> function <I>before</I> inserting and then insert it explicitly. Using the example table in <A HREF="#4.16.1">4.16.1</A>, that might look like this:
1043 $newSerialID = nextval('person_id_seq');
1044 INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
1046 You would then also have the new value stored in <CODE>$newSerialID</CODE> for use in other queries (e.g., as a foreign key to the <CODE>person</CODE> table). Note that the name of the automatically-created SEQUENCE object will be named <<I>table</I>>_<<I>serialcolumn</I>>_<I>seq</I>, where <I>table</I> and <I>serialcolumn</I> are the names of your table and your SERIAL column, respectively.
1048 Alternatively, you could retrieve the just-assigned SERIAL value with the <I>currval</I>() function <I>after</I> it was inserted by default, e.g.,
1050 INSERT INTO person (name) VALUES ('Blaise Pascal');
1051 $newID = currval('person_id_seq');
1053 Finally, you could use the <A HREF="#4.17"><small>OID</small></A> returned from the
1054 INSERT statement to lookup the default value, though this is probably
1055 the least portable approach. In Perl, using DBI with Edmund Mergl's
1056 DBD::Pg module, the oid value is made available via
1057 <I>$sth->{pg_oid_status} after $sth->execute().</I>
1059 <H4><A NAME="4.16.3">4.16.3</A>) Don't <I>currval()</I> and <I>nextval()</I> lead to
1060 a race condition with other users?</H4><P>
1062 No. This is handled by the backends.
1065 <H4><A NAME="4.17">4.17</A>) What is an <small>OID</small>? What is a
1066 <small>TID</small>?</H4><P>
1068 <small>OID</small>s are PostgreSQL's answer to unique row ids. Every row that is
1069 created in PostgreSQL gets a unique <small>OID</small>. All <small>OID</small>s generated during
1070 <I>initdb</I> are less than 16384 (from <I>backend/access/transam.h</I>). All
1071 user-created <small>OID</small>s are equal or greater that this. By default, all these
1072 <small>OID</small>s are unique not only within a table, or database, but unique within
1073 the entire PostgreSQL installation.<P>
1075 PostgreSQL uses <small>OID</small>s in its internal system tables to link rows between
1076 tables. These <small>OID</small>s can be used to identify specific user rows and used
1077 in joins. It is recommended you use column type <small>OID</small> to
1078 store <small>OID</small>
1079 values. You can create an index on the <small>OID</small> field for faster access.<P>
1081 O<small>id</small>s are assigned to all new rows from a central area that is used by
1082 all databases. If you want to change the <small>OID</small> to something else, or if
1083 you want to make a copy of the table, with the original <small>OID</small>'s, there is
1084 no reason you can't do it:
1087 CREATE TABLE new_table(old_oid oid, mycol int);
1088 SELECT old_oid, mycol INTO new FROM old;
1089 COPY new TO '/tmp/pgtable';
1091 COPY new WITH OIDS FROM '/tmp/pgtable';
1093 CREATE TABLE new_table (mycol int);
1094 INSERT INTO new_table (oid, mycol) SELECT oid, mycol FROM old_table;
1098 O<small>ID</small>s are stored as 4-byte integers, and will overflow
1099 at 4 billion. No one has reported this ever happening, and we plan to
1100 have the limit removed before anyone does.<P>
1102 T<small>ID</small>s are used to identify specific physical rows with block and offset
1103 values. Tids change after rows are modified or reloaded. They are used
1104 by index entries to point to physical rows.<P>
1107 <H4><A NAME="4.18">4.18</A>) What is the meaning of some of the terms
1108 used in PostgreSQL?</H4><P>
1110 Some of the source code and older documentation use terms that have more
1111 common usage. Here are some:
1114 <LI> table, relation, class
1115 <LI> row, record, tuple
1116 <LI> column, field, attribute
1117 <LI> retrieve, select
1118 <LI> replace, update
1120 <LI> <small>OID</small>, serial value
1122 <LI> range variable, table name, table alias
1125 A list of general database terms can be found at: <a
1126 href="http://www.comptechnews.com/~reaster/dbdesign.html">
1127 http://www.comptechnews.com/~reaster/dbdesign.html</a><P>
1129 <H4><A NAME="4.19">4.19</A>) Why do I get the error <I>"FATAL: palloc
1130 failure: memory exhausted?"</I><BR></H4><P>
1132 It is possible you have run out of virtual memory on your system, or
1133 your kernel has a low limit for certain resources. Try this before
1134 starting the <i>postmaster:</i>
1141 Depending on your shell, only one of these may succeed, but it will set
1142 your process data segment limit much higher and perhaps allow the query
1143 to complete. This command applies to the current process, and all
1144 subprocesses created after the command is run. If you are having a problem
1145 with the SQL client because the backend is returning too much data, try
1146 it before starting the client.<P>
1148 <H4><A NAME="4.20">4.20</A>) How do I tell what PostgreSQL version I
1149 am running? <BR></H4><P>
1151 From <I>psql,</I> type <CODE>select version();</CODE><P>
1153 <H4><A NAME="4.21">4.21</A>) My large-object operations get <I>invalid
1154 large obj descriptor.</I> Why? <BR></H4><P>
1156 You need to put <CODE>BEGIN WORK</CODE> and <CODE>COMMIT
1157 </CODE> around any use of a large object handle, that is,
1158 surrounding <CODE>lo_open</CODE> ... <CODE>lo_close.</CODE><P>
1160 Currently PostgreSQL enforces the rule by closing large object handles
1161 at transaction commit. So the first attempt to do anything with the
1162 handle will draw <I>invalid large obj descriptor.</I> So code that used
1163 to work (at least most of the time) will now generate that error message
1164 if you fail to use a transaction.<P>
1166 If you are using a client interface like ODBC you may need to set
1167 <CODE>auto-commit off.</CODE><P>
1169 <H4><A NAME="4.22">4.22</A>) How do I create a column that will default to the
1170 current time?<BR></H4><P>
1173 CREATE TABLE test (x int, modtime timestamp DEFAULT now() );
1176 <H4><A NAME="4.23">4.23</A>) Why are my subqueries using
1177 <CODE><small>IN</small></CODE> so slow?<BR></H4><P>
1178 Currently, we join subqueries to outer queries by sequential scanning
1179 the result of the subquery for each row of the outer query. A workaround
1180 is to replace <CODE>IN</CODE> with <CODE>EXISTS</CODE>:
1184 WHERE col1 IN (SELECT col2 FROM TAB2)
1190 WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2)
1192 We hope to fix this limitation in a future release.
1194 <H4><A NAME="4.24">4.24</A>) How do I do an <i>outer</i> join?<BR></H4><P>
1195 PostgreSQL does not support outer joins in the current release. They can
1196 be simulated using <small>UNION</small> and <small>NOT IN</small>. For
1197 example, when joining <i>tab1</i> and <i>tab2,</i> the following query
1198 does an <i>outer</i> join of the two tables:
1200 SELECT tab1.col1, tab2.col2
1202 WHERE tab1.col1 = tab2.col1
1204 SELECT tab1.col1, NULL
1206 WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
1212 <H2><CENTER>Extending PostgreSQL</CENTER></H2><P>
1215 <H4><A NAME="5.1">5.1</A>) I wrote a user-defined function. When
1216 I run it in <I>psql,</I> why does it dump core?</H4><P>
1218 The problem could be a number of things. Try testing your user-defined
1219 function in a stand alone test program first.
1221 <H4><A NAME="5.2">5.2</A>) What does the message
1222 <I>"NOTICE:PortalHeapMemoryFree: 0x402251d0 not in alloc set!"</I> mean?</H4><P>
1224 You are <I>pfree'ing</I> something that was not <I>palloc'ed.</I>
1225 Beware of mixing <I>malloc/free</I> and <I>palloc/pfree.</I>
1228 <H4><A NAME="5.3">5.3</A>) How can I contribute some nifty new types and
1229 functions to PostgreSQL?</H4><P>
1232 Send your extensions to the <I>pgsql-hackers</I> mailing list, and they will
1233 eventually end up in the <I>contrib/</I> subdirectory.<P>
1236 <H4><A NAME="5.4">5.4</A>) How do I write a C function to return a
1239 This requires wizardry so extreme that the authors have never
1240 tried it, though in principle it can be done.<P>
1242 <H4><A NAME="5.5">5.5</A>) I have changed a source file. Why does the
1243 recompile not see the change?</H4><P>
1245 The <I>Makefiles</I> do not have the proper dependencies for include files. You
1246 have to do a <I>make clean</I> and then another <I>make</I>.<P>