diff options
Diffstat (limited to 'doc/FAQ')
| -rw-r--r-- | doc/FAQ | 144 |
1 files changed, 98 insertions, 46 deletions
@@ -1,7 +1,7 @@ Frequently Asked Questions (FAQ) for PostgreSQL - Last updated: Mon Mar 18 14:34:57 EST 2002 + Last updated: Mon Sep 30 23:28:35 EDT 2002 Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us) @@ -53,6 +53,8 @@ 3.7) What debugging features are available? 3.8) Why do I get "Sorry, too many clients" when trying to connect? 3.9) What are the pg_sorttempNNN.NN files in my database directory? + 3.10) Why do I need to do a dump and restore to upgrade PostgreSQL + releases? Operational Questions @@ -63,8 +65,8 @@ 4.5) What is the maximum size for a row, a table, and a database? 4.6) How much database disk space is required to store data from a typical text file? - 4.7) How do I find out what tables or indexes are defined in the - database? + 4.7) How do I find out what tables, indexes, databases, and users are + defined? 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? @@ -91,6 +93,9 @@ 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? + 4.25) How do I return multiple rows or columns from a function? + 4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL + functions? Extending PostgreSQL @@ -237,7 +242,7 @@ 1.7) What is the latest release? - The latest release of PostgreSQL is version 7.2.1. + The latest release of PostgreSQL is version 7.2.3. We plan to have major releases every four months. @@ -320,29 +325,15 @@ reduce lock contention. Performance - PostgreSQL runs in two modes. Normal fsync mode flushes every - completed transaction to disk, guaranteeing that if the OS - crashes or loses power in the next few seconds, all your data - is safely stored on disk. In this mode, we are slower than most - commercial databases, partly because few of them do such - conservative flushing to disk in their default modes. In - no-fsync mode, we are usually faster than commercial databases, - though in this mode, an OS crash could cause data corruption. - We are working to provide an intermediate mode that suffers - less performance overhead than full fsync mode, and will allow - data integrity within 30 seconds of an OS crash. - In comparison to MySQL or leaner database systems, we are - slower on inserts/updates because we have transaction overhead. + PostgreSQL has performance similar to other commercial and open + source databases. it is faster for some things, slower for + others. In comparison to MySQL or leaner database systems, we + are slower on inserts/updates because of transaction overhead. Of course, MySQL does not have any of the features mentioned in - the Features section above. We are built for flexibility and - features, though we continue to improve performance through - profiling and source code analysis. There is an interesting Web - page comparing PostgreSQL to MySQL at - http://openacs.org/why-not-mysql.html - We handle each user connection by creating a Unix process. - Backend processes share data buffers and locking information. - With multiple CPUs, multiple backends can easily run on - different CPUs. + the Features section above. We are built for reliability and + features, though we continue to improve performance in every + release. There is an interesting Web page comparing PostgreSQL + to MySQL at http://openacs.org/why-not-mysql.html Reliability We realize that a DBMS must be reliable, or it is worthless. We @@ -380,7 +371,8 @@ 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, - please go to http://www.pgsql.com/pg_goodies and make a donation. + please go to https://store.pgsql.com/shopping/index.php?id=1 and make + a donation. Although the web page mentions PostgreSQL, Inc, the "contributions" item is solely to support the PostgreSQL project and does not fund any @@ -443,6 +435,9 @@ * TCL (libpgtcl) * C Easy API (libpgeasy) * Embedded HTML (PHP from http://www.php.net) + + Additional interfaces are available at + http://www.postgresql.org/interfaces.html. _________________________________________________________________ Administrative Questions @@ -570,7 +565,8 @@ 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 - in the client's current directory. + in the client's current directory. Linux requires a compile with + -DLINUX_PROFILE for proper profiling. 3.8) Why do I get "Sorry, too many clients" when trying to connect? @@ -606,6 +602,21 @@ The temporary files should be deleted automatically, but might not if a backend crashes during a sort. If you have no backends running at the time, it is safe to delete the pg_tempNNN.NN files. + + 3.10) Why do I need to do a dump and restore to upgrade between major + PostgreSQL releases? + + 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. + However, major releases 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. + + 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. _________________________________________________________________ Operational Questions @@ -635,19 +646,22 @@ 4.4) How do you remove a column from a table? We do not support ALTER TABLE DROP COLUMN, but do this: + BEGIN; + LOCK TABLE old_table; 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; + COMMIT; 4.5) What is the maximum size for a row, a table, and a database? These are the limits: - Maximum size for a database? unlimited (500 GB databases exist) + Maximum size for a database? unlimited (1 TB databases exist) Maximum size for a table? 16 TB - Maximum size for a row? unlimited in 7.1 and later - Maximum size for a field? 1 GB in 7.1 and later + Maximum size for a row? 1.6TB + Maximum size for a field? 1 GB 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 @@ -695,10 +709,14 @@ Indexes do not require as much overhead, but do contain the data that is being indexed, so they can be large also. - 4.7) How do I find out what tables or indexes are defined in the database? + NULLs are stored in bitmaps, so they use very little space. + + 4.7) How do I find out what tables, indexes, databases, and users are + defined? psql has a variety of backslash commands to show such information. Use - \? to see them. + \? to see them. There are also system tables beginning with pg_ that + describe these too. Also, psql -l will list all databases. Also try the file pgsql/src/tutorial/syscat.source. It illustrates many of the SELECTs needed to get information from the database system @@ -709,7 +727,7 @@ Indexes are not automatically used by every query. Indexes are only used if the table is larger than a minimum size, and the query selects only a small percentage of the rows in the table. This is because the - random disk access caused by an index scan is sometimes slower than a + random disk access caused by an index scan can be slower than a straight read through the table, or sequential scan. To determine if an index should be used, PostgreSQL must have @@ -724,12 +742,29 @@ 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 - only a small portion of the table is returned. - + 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 + ORDER BY col [ DESC ] + LIMIT 1 + When using wild-card operators such as LIKE or ~, indexes can only be - used if the beginning of the search is anchored to the start of the - string. Therefore, to use indexes, LIKE patterns must not start with - %, and ~(regular expression) patterns must start with ^. + used in certain circumstances: + * The beginning of the search string must be anchored to the start + 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 like ILIKE and ~* can not be used. + Instead, use functional indexes, which are described later in this + FAQ. + + The default C local must be used during initdb. 4.9) How do I see how the query optimizer is evaluating my query? @@ -770,7 +805,7 @@ The ~ operator does regular expression matching, and ~* does case-insensitive regular expression matching. The case-insensitive - variant of LIKE is called ILIKE in PostgreSQL 7.1 and later. + variant of LIKE is called ILIKE. Case-insensitive equality comparisons are normally expressed as: SELECT * @@ -923,10 +958,9 @@ BYTEA bytea variable-length byte array (null-byte safe) 4.18) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"? - If you are running a version older than 7.1, an upgrade may fix the - problem. Also it is possible you have run out of virtual memory on - your system, or your kernel has a low limit for certain resources. Try - this before starting postmaster: + 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: ulimit -d 262144 limit datasize 256m @@ -979,8 +1013,8 @@ SELECT * 4.23) How do I perform an outer join? - PostgreSQL 7.1 and later supports outer joins using the SQL standard - syntax. Here are two examples: + PostgreSQL supports outer joins using the SQL standard syntax. Here + are two examples: SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col); @@ -1015,6 +1049,24 @@ SELECT * Of course, a client can make simultaneous connections to different databases and merge the information that way. + + 4.25) How do I return multiple rows or columns from a function? + + You can return result sets from PL/pgSQL functions using refcursors. + See + http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html, + section 23.7.3.3. + + 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. _________________________________________________________________ Extending PostgreSQL |