From 97052bc9c9cff07ece76c73e825b3eea1570e2c2 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 30 Jan 2005 04:03:02 +0000 Subject: Remove FAQ item about binary cursors. --- doc/src/FAQ/FAQ.html | 128 ++++++++++++++++++++++++--------------------------- 1 file changed, 60 insertions(+), 68 deletions(-) (limited to 'doc/src') diff --git a/doc/src/FAQ/FAQ.html b/doc/src/FAQ/FAQ.html index b7e62d9f1fe..f877d412e08 100644 --- a/doc/src/FAQ/FAQ.html +++ b/doc/src/FAQ/FAQ.html @@ -10,7 +10,7 @@ alink="#0000ff">

Frequently Asked Questions (FAQ) for PostgreSQL

-

Last updated: Sat Jan 29 22:59:12 EST 2005

+

Last updated: Sat Jan 29 23:02:37 EST 2005

Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
@@ -71,63 +71,61 @@

Operational Questions

- 4.1) What is the difference between binary - cursors and normal cursors?
- 4.2) How do I SELECT only the + 4.1) How do I SELECT only the first few rows of a query? A random row?
- 4.3) How do I find out what tables, indexes, + 4.2) How do I find out what tables, indexes, databases, and users are defined? How do I see the queries used by psql to display them?
- 4.4) How do you remove a column from a + 4.3) How do you remove a column from a table, or change its data type?
- 4.5) What is the maximum size for a row, a + 4.4) What is the maximum size for a row, a table, and a database?
- 4.6) How much database disk space is required + 4.5) How much database disk space is required to store data from a typical text file?
- 4.7) My queries are slow or don't make use of + 4.6) My queries are slow or don't make use of the indexes. Why?
- 4.8) How do I see how the query optimizer is + 4.7) How do I see how the query optimizer is evaluating my query?
- 4.9) What is an R-tree index?
- 4.10) What is the Genetic Query Optimizer?
- 4.11) How do I perform regular expression + 4.8) What is an R-tree index?
+ 4.9) What is the Genetic Query Optimizer?
+ 4.10) How do I perform regular expression searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?
- 4.12) In a query, how do I detect if a field + 4.11) In a query, how do I detect if a field is NULL?
- 4.13) What is the difference between the + 4.12) What is the difference between the various character types?
- 4.14.0) How do I create a + 4.13.0) How do I create a serial/auto-incrementing field?
- 4.14.1) How do I get the value of a + 4.13.1) How do I get the value of a SERIAL insert?
- 4.14.2) Doesn't currval() + 4.13.2) Doesn't currval() lead to a race condition with other users?
- 4.14.3) Why aren't my sequence numbers + 4.13.3) Why aren't my sequence numbers reused on transaction abort? Why are there gaps in the numbering of my sequence/SERIAL column?
- 4.15) What is an OID? What is a + 4.14) What is an OID? What is a TID?
- 4.16) What is the meaning of some of the terms + 4.15) What is the meaning of some of the terms used in PostgreSQL?
- 4.17) Why do I get the error "ERROR: Memory + 4.16) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
- 4.18) How do I tell what PostgreSQL version I + 4.17) How do I tell what PostgreSQL version I am running?
- 4.19) Why does my large-object operations get + 4.18) Why does my large-object operations get "invalid large obj descriptor"?
- 4.20) How do I create a column that will + 4.19) How do I create a column that will default to the current time?
- 4.21) Why are my subqueries using + 4.20) Why are my subqueries using IN so slow?
- 4.22) How do I perform an outer join?
- 4.23) How do I perform queries using multiple + 4.21) How do I perform an outer join?
+ 4.22) How do I perform queries using multiple databases?
- 4.24) How do I return multiple rows or columns + 4.23) How do I return multiple rows or columns from a function?
- 4.25) Why can't I reliably create/drop + 4.24) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?
- 4.26) What encryption options are available?
+ 4.25) What encryption options are available?

Extending PostgreSQL

@@ -712,13 +710,7 @@

Operational Questions

-

4.1) What is the difference between binary - cursors and normal cursors?

- -

See the DECLARE manual page for a - description.

- -

4.2) How do I SELECT only the +

4.1) How do I SELECT only the first few rows of a query? A random row?

See the FETCH manual page, or use @@ -739,7 +731,7 @@ LIMIT 1; -

4.3) How do I find out what tables, indexes, +

4.2) How do I find out what tables, indexes, databases, and users are defined? How do I see the queries used by psql to display them?

@@ -760,7 +752,7 @@ illustrates many of the SELECTs needed to get information from the database system tables.

-

4.4) How do you remove a column from a +

4.3) How do you remove a column from a table, or change its data type?

DROP COLUMN functionality was added in release 7.3 @@ -791,7 +783,7 @@

You might then want to do VACUUM FULL tab to reclaim the disk space used by the expired rows.

-

4.5) What is the maximum size for a row, a +

4.4) What is the maximum size for a row, a table, and a database?

These are the limits:

@@ -817,7 +809,7 @@

The maximum table size and maximum number of columns can be quadrupled by increasing the default block size to 32k.

-

4.6) How much database disk space is required +

4.5) How much database disk space is required to store data from a typical text file?

A PostgreSQL database may require up to five times the disk @@ -854,7 +846,7 @@

NULLs are stored as bitmaps, so they use very little space.

-

4.7) My queries are slow or don't make use of +

4.6) My queries are slow or don't make use of the indexes. Why?

Indexes are not automatically used by every query. Indexes are only @@ -905,7 +897,7 @@ e.g. [a-e].

  • Case-insensitive searches such as ILIKE and ~* do not utilize indexes. Instead, use functional - indexes, which are described in section 4.11.
    • + indexes, which are described in section 4.10.
  • The default C locale must be used during initdb because it is not possible to know the next-greater character in a non-C locale. You can create a special @@ -918,12 +910,12 @@ types exactly match the index's column types. This is particularly true of int2, int8, and numeric column indexes.

    -

    4.8) How do I see how the query optimizer is +

    4.7) How do I see how the query optimizer is evaluating my query?

    See the EXPLAIN manual page.

    -

    4.9) What is an R-tree index?

    +

    4.8) What is an R-tree index?

    An R-tree index is used for indexing spatial data. A hash index can't handle range searches. A B-tree index only handles range @@ -948,7 +940,7 @@ practice, extending R-trees requires a bit of work and we don't currently have any documentation on how to do it.

    -

    4.10) What is the Genetic Query +

    4.9) What is the Genetic Query Optimizer?

    The GEQO module speeds query optimization when @@ -956,7 +948,7 @@ the handling of large join queries through nonexhaustive search.

    -

    4.11) How do I perform regular expression +

    4.10) How do I perform regular expression searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?

    @@ -979,13 +971,13 @@ CREATE INDEX tabindex ON tab (lower(col)); -

    4.12) In a query, how do I detect if a field +

    4.11) In a query, how do I detect if a field is NULL?

    You test the column with IS NULL and IS NOT NULL.

    -

    4.13) What is the difference between the +

    4.12) What is the difference between the various character types?

     Type            Internal Name   Notes
@@ -1017,7 +1009,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     particularly values that include NULL bytes. All the
     types described here have similar performance characteristics.
    
 
-    
    4.14.1) How do I create a
+    
    4.13.1) How do I create a
     serial/auto-incrementing field?
    
 
     
    PostgreSQL supports a SERIAL data type. It
@@ -1045,13 +1037,13 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     you need to use pg_dump's -o option or COPY
     WITH OIDS option to preserve the OIDs. 
 
-    
    4.14.2) How do I get the value of a
+    
    4.13.2) How do I get the value of a
     SERIAL insert?
    
 
     
    One approach is to retrieve the next SERIAL value
     from the sequence object with the nextval() function
     before inserting and then insert it explicitly. Using the
-    example table in 4.14.1, an example in a
+    example table in 4.13.1, an example in a
     pseudo-language would look like this:
    
 
         new_id = execute("SELECT nextval('person_id_seq')");
@@ -1074,7 +1066,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     new_id = execute("SELECT currval('person_id_seq')");
 
    
     
-    
    Finally, you could use the OID
+    
    Finally, you could use the OID
     returned from the INSERT statement to look up the
     default value, though this is probably the least portable approach,
     and the oid value will wrap around when it reaches 4 billion.
@@ -1082,13 +1074,13 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     available via $sth->{pg_oid_status} after
     $sth->execute().
    
 
-    
    4.14.3) Doesn't currval()
+    
    4.13.3) Doesn't currval()
     lead to a race condition with other users?
    
 
     
    No. currval() returns the current value assigned by your
     backend, not by all users.
    
 
-    
    4.14.4) Why aren't my sequence numbers
+    
    4.13.4) Why aren't my sequence numbers
     reused on transaction abort? Why are there gaps in the numbering of
     my sequence/SERIAL column?
    
 
@@ -1097,7 +1089,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     completes. This causes gaps in numbering from aborted
     transactions.
    
 
-    
    4.15) What is an OID? What is
+    
    4.14) What is an OID? What is
     a TID?
    
 
     
    Every row that is created in PostgreSQL gets a unique
@@ -1120,7 +1112,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     are modified or reloaded. They are used by index entries to point
     to physical rows.
    
 
-    
    4.16) What is the meaning of some of the
+    
    4.15) What is the meaning of some of the
     terms used in PostgreSQL?
    
 
     
    Some of the source code and older documentation use terms that
@@ -1149,7 +1141,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     
    A list of general database terms can be found at: http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html
    
 
-    
    4.17) Why do I get the error "ERROR:
+    
    4.16) Why do I get the error "ERROR:
     Memory exhausted in AllocSetAlloc()"?
    
 
     
    You probably have run out of virtual memory on your system,
@@ -1168,12 +1160,12 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     backend is returning too much data, try it before starting the
     client.
 
-    
    4.18) How do I tell what PostgreSQL version
+    
    4.17) How do I tell what PostgreSQL version
     I am running?
    
 
     
    From psql, type SELECT version();
    
 
-    
    4.19) Why does my large-object operations
+    
    4.18) Why does my large-object operations
     get "invalid large obj descriptor"?
    
 
     
    You need to put BEGIN WORK and COMMIT
@@ -1189,7 +1181,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
     
    If you are using a client interface like ODBC you
     may need to set auto-commit off.
    
 
-    
    4.20) How do I create a column that will
+    
    4.19) How do I create a column that will
     default to the current time?
    
 
     
    Use CURRENT_TIMESTAMP:
    
@@ -1198,7 +1190,7 @@ BYTEA           bytea           variable-length byte array (null-byte safe)
    -

    4.21) Why are my subqueries using +

    4.20) Why are my subqueries using IN so slow?

    In versions prior to 7.4, subqueries were joined to outer queries @@ -1222,7 +1214,7 @@ BYTEA bytea variable-length byte array (null-byte safe) sophisticated join techniques as normal queries, and is prefered to using EXISTS. -

    4.22) How do I perform an outer join?

    +

    4.21) How do I perform an outer join?

    PostgreSQL supports outer joins using the SQL standard syntax. Here are two examples:

    @@ -1262,7 +1254,7 @@ BYTEA bytea variable-length byte array (null-byte safe) ORDER BY col1 -

    4.23) How do I perform queries using +

    4.22) How do I perform queries using multiple databases?

    There is no way to query a database other than the current one. @@ -1274,7 +1266,7 @@ BYTEA bytea variable-length byte array (null-byte safe) connections to different databases and merge the results on the client side.

    -

    4.24) How do I return multiple rows or +

    4.23) How do I return multiple rows or columns from a function?

    In 7.3, you can easily return multiple rows or columns from a @@ -1282,7 +1274,7 @@ BYTEA bytea variable-length byte array (null-byte safe) http://techdocs.postgresql.org/guides/SetReturningFunctions. -

    4.25) Why can't I reliably create/drop +

    4.24) 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 @@ -1292,7 +1284,7 @@ BYTEA bytea variable-length byte array (null-byte safe) EXECUTE for temporary table access in PL/PgSQL. This will cause the query to be reparsed every time.

    -

    4.26) What encryption options are available? +

    4.25) What encryption options are available?

    • contrib/pgcrypto contains many encryption functions for -- cgit v1.2.3