11
22 Frequently Asked Questions (FAQ) for PostgreSQL
33
4- Last updated:Sat Jul 10 00:37:57 EDT 1999
4+ Last updated:Tue Sep 28 01:06:15 EDT 1999
55
66 Current maintainer: Bruce Momjian (maillist@candle.pha.pa.us)
77
4949 3.2) How do I install PostgreSQL somewhere other than
5050 /usr/local/pgsql?
5151 3.3) When I start the postmaster, I get a Bad System Call or core
52- dumpedmessage3 . Why?
52+ dumpedmessage . Why?
5353 3.4) When I try to start the postmaster, I get IpcMemoryCreate
5454 errors3. Why?
5555 3.5) When I try to start the postmaster, I get IpcSemaphoreCreate
9393 4.19) Why do I get the error "FATAL: palloc failure: memory
9494 exhausted?"
9595 4.20) How do I tell what PostgreSQL version I am running?
96+ 4.21) My large-object operations get invalid large obj descriptor.
97+ Why?
98+ 4.22) How do I create a column that will default to the current time?
9699
97100 Extending PostgreSQL
98101
194197 The database server is now working on Windows NT using the Cygnus
195198 Unix/NT porting library. See pgsql/doc/README.NT in the distribution.
196199
197- There is another port using U/Win at
198- http://surya.wipro.com/uwin/ported.html.
200+ There is also a web page at
201+ http://members.tripod.com/~kevlo/postgres/portNT.html. There is
202+ another port using U/Win at http://surya.wipro.com/uwin/ported.html.
199203
200204 1.5) Where can I get PostgreSQL?
201205
213217 available for discussion of matters pertaining to PostgreSQL. To
214218 subscribe, send a mail with the lines in the body (not the subject
215219 line)
216-
217220 subscribe
218221 end
219222
220223 to pgsql-general-request@postgreSQL.org.
221224
222225 There is also a digest list available. To subscribe to this list, send
223226 email to: pgsql-general-digest-request@postgreSQL.org with a BODY of:
224-
225227 subscribe
226228 end
227229
231233 The bugs mailing list is available. To subscribe to this list, send
232234 email to bugs-request@postgreSQL.org with a BODY of:
233235
234-
235236 subscribe
236237 end
237238
238239 There is also a developers discussion mailing list available. To
239240 subscribe to this list, send email to hackers-request@postgreSQL.org
240241 with a BODY of:
241242
242-
243243 subscribe
244244 end
245245
256256
257257 1.7) What is the latest release of PostgreSQL?
258258
259- The latest release of PostgreSQL is version 6.5.
259+ The latest release of PostgreSQL is version 6.5.2.
260260
261261 We plan to have major releases every four months.
262262
315315
316316 Features
317317 PostgreSQL has most features present in large commercial
318- DBMS's, like transactions, subselects,and sophisticated
319- locking. We have some features they don't have, like
320- user-defined types, inheritance, rules, and multi-version
318+ DBMS's, like transactions, subselects,triggers, views, and
319+ sophisticated locking. We have some features they don't have,
320+ like user-defined types, inheritance, rules, and multi-version
321321 concurrency control to reduce lock contention. We don't have
322322 foreign key referential integrity or outer joins, but are
323323 working on them for our next release.
324324
325325 Performance
326326 PostgreSQL runs in two modes. Normal fsync mode flushes every
327327 completed transaction to disk, guaranteeing that if the OS
328- crashes orlooses power in the next few seconds, all your data
328+ crashes orloses power in the next few seconds, all your data
329329 is safely stored on disk. In this mode, we are slower than most
330330 commercial databases, partly because few of them do such
331331 conservative flushing to disk in their default modes. In
332332 no-fsync mode, we are usually faster than commercial databases,
333333 though in this mode, an OS crash could cause data corruption.
334334 We are working to provide an intermediate mode that suffers
335- from less performance overhead than full fsync mode, and will
336- allow data integrity within 30 seconds of an OS crash. The mode
337- is select-able by the database administrator.
338-
335+ less performance overhead than full fsync mode, and will allow
336+ data integrity within 30 seconds of an OS crash. The mode is
337+ select-able by the database administrator.
339338 In comparison to MySQL or leaner database systems, we are
340- slower because we have transaction overhead. We are built for
341- flexibility and features, not speed, though we continue to
342- improve performance through profiling and source code analysis.
339+ slower on inserts/updates because we have transaction overhead.
340+ Of course, MySQL doesn't have any of the features mentioned in
341+ the Features section above. We are built for flexibility and
342+ features, though we continue to improve performance through
343+ profiling and source code analysis.
344+ We handle each user connection by creating a Unix process.
345+ Backend processes share data buffers and locking information.
346+ With multiple CPU's, multiple backends can easily run on
347+ different CPU's.
343348
344349 Reliability
345350 We realize that a DBMS must be reliable, or it is worthless. We
544549 Both postmaster and postgres have several debug options available.
545550 First, whenever you start the postmaster, make sure you send the
546551 standard output and error to a log file, like:
547-
548552 cd /usr/local/pgsql
549553 ./bin/postmaster >server.log 2>&1 &
550554
578582 You need to increase the postmaster's limit on how many concurrent
579583 backend processes it can start.
580584
581- In Postgres 6.5, the default limit is 32 processes. You can increase
585+ In Postgres 6.5.* , the default limit is 32 processes. You can increase
582586 it by restarting the postmaster with a suitable -N value. With the
583587 default configuration you can set -N as large as 1024; if you need
584588 more, increase MAXBACKENDS in include/config.h and rebuild. You can
615619
616620 Currently, there is no easy interface to set up user groups. You have
617621 to explicitly insert/update the pg_group table. For example:
618-
619622 jolly=> insert into pg_group (groname, grosysid, grolist)
620623 jolly=> values ('posthackers', '1234', '{5443, 8261}');
621624 INSERT 548224
667670 4.5) How do you remove a column from a table?
668671
669672 We do not support alter table drop column, but do this:
670-
671673 SELECT ... -- select all columns but the one you want to remove
672674 INTO TABLE new_table
673675 FROM old_table;
@@ -871,7 +873,6 @@ BYTEA bytea variable-length array of bytes
871873 It is possible you have run out of virtual memory on your system, or
872874 your kernel has a low limit for certain resources. Try this before
873875 starting the postmaster:
874-
875876 ulimit -d 65536
876877 limit datasize 64m
877878
@@ -885,6 +886,37 @@ BYTEA bytea variable-length array of bytes
885886 4.20) How do I tell what PostgreSQL version I am running?
886887
887888 From psql, type select version();
889+
890+ 4.21) My large-object operations get invalid large obj descriptor. Why?
891+
892+ You need to put BEGIN WORK and COMMIT around any use of a large object
893+ handle, that is, surrounding lo_open ... lo_close.
894+
895+ The documentation has always stated that lo_open must be wrapped in a
896+ transaction, but PostgreSQL versions prior to 6.5 didn't enforce that
897+ rule. Instead, they'd just fail occasionally if you broke it.
898+
899+ Current PostgreSQL enforces the rule by closing large object handles
900+ at transaction commit, which will be instantly upon completion of the
901+ lo_open command if you are not inside a transaction. So the first
902+ attempt to do anything with the handle will draw invalid large obj
903+ descriptor. So code that used to work (at least most of the time) will
904+ now generate that error message if you fail to use a transaction.
905+
906+ If you are using a client interface like ODBC you may need to set
907+ auto-commit off.
908+
909+ 4.22) How do I create a column that will default to the current time?
910+
911+ The tempation is to do:
912+ create table test (x int, modtime timestamp default 'now');
913+
914+ but this makes the column default to the time of table creation, not
915+ the time of row insertion. Instead do:
916+ create table test (x int, modtime timestamp default text 'now');
917+
918+ The casting of the value to text prevents the default value from being
919+ computed at table creation time, and delays it until insertion time.
888920 _________________________________________________________________
889921
890922 Extending PostgreSQL