Brand 7.2.3.REL7_2_3

1 files changed, 98 insertions, 46 deletions

diff --git a/doc/FAQ b/doc/FAQ
index 0adb4c7a80f..b7ccc62b069 100644
--- a/doc/FAQ
+++ b/doc/FAQ
@@ -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