From 2de7ebe91028194d91b9ef69632e094710433323 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 1 Feb 2005 02:31:59 +0000 Subject: Remove FAQ about database terms. --- doc/src/FAQ/FAQ.html | 152 ++++++++++++++++++++------------------------------- 1 file changed, 60 insertions(+), 92 deletions(-) (limited to 'doc/src/FAQ/FAQ.html') diff --git a/doc/src/FAQ/FAQ.html b/doc/src/FAQ/FAQ.html index 71fd8577107..d5e816926cc 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: Mon Jan 31 20:41:21 EST 2005

+

Last updated: Mon Jan 31 21:31:39 EST 2005

Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us) @@ -101,24 +101,22 @@ my sequence/SERIAL column?
4.12) What is an OID? What is a TID?
- 4.13) What is the meaning of some of the terms - used in PostgreSQL?
- 4.14) Why do I get the error "ERROR: Memory + 4.13) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?
- 4.15) How do I tell what PostgreSQL version I + 4.14) How do I tell what PostgreSQL version I am running?
- 4.16) Why does my large-object operations get + 4.15) Why does my large-object operations get "invalid large obj descriptor"?
- 4.17) How do I create a column that will + 4.16) How do I create a column that will default to the current time?
- 4.18) How do I perform an outer join?
- 4.19) How do I perform queries using multiple + 4.17) How do I perform an outer join?
+ 4.18) How do I perform queries using multiple databases?
- 4.20) How do I return multiple rows or columns + 4.19) How do I return multiple rows or columns from a function?
- 4.21) Why can't I reliably create/drop + 4.20) Why can't I reliably create/drop temporary tables in PL/PgSQL functions?
- 4.22) What encryption options are available?
+ 4.21) What encryption options are available?

Extending PostgreSQL

@@ -531,7 +529,7 @@ option, many assert()s monitor the progress of the backend and halt the program when something unexpected occurs.

- The postmaster has a -d option that allows even more +

The postmaster has a -d option that allows even more detailed information to be reported. The -d option takes a number that specifies the debug level. Be warned that high debug level values generate large log files.

@@ -567,7 +565,7 @@

3.5) Why do I get "Sorry, too many clients" when trying to connect?

- You have reached the default limit is 100 database sessions. You +

You have reached the default limit is 100 database sessions. You need to increase the postmaster's limit on how many concurrent backend processes it can start by changing the max_connections value in postgresql.conf and @@ -672,19 +670,25 @@ table, and a database?

These are the limits:

-
-    Maximum size for a database?             unlimited (32 TB databases exist)
-    Maximum size for a table?                32 TB
-    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
-
- - Of course, these are not actually unlimited, but limited to +
+ + + + + + + + +
Maximum size for a database?unlimited (32 TB databases +exist)
Maximum size for a table?32 TB
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
+
+
+ +

Of course, these are not actually unlimited, but limited to available disk space and memory/swap space. Performance may suffer - when these values get unusually large. + when these values get unusually large.

The maximum table size of 32 TB does not require large file support from the operating system. Large tables are stored as @@ -781,10 +785,10 @@

  • The search string can not start with a character class, 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.10.
    • + ~* do not utilize indexes. Instead, use expression + indexes, which are described in section 4.8.
  • The default C locale must be used during - initdb because it is not possible to know the next-greater + initdb because it is not possible to know the next-greatest character in a non-C locale. You can create a special text_pattern_ops index for such cases that work only for LIKE indexing. @@ -792,7 +796,7 @@

    In pre-8.0 releases, indexes often can not be used unless the data - types exactly match the index's column types. This is particularly + types exactly match the index's column types. This was particularly true of int2, int8, and numeric column indexes.

    4.7) How do I see how the query optimizer is @@ -818,7 +822,7 @@ This will not use an standard index. However, if you create a - functional index, it will be used: + expresssion index, it will be used: 
         CREATE INDEX tabindex ON tab (lower(col));
    @@ -831,16 +835,20 @@

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

    -
    -Type            Internal Name   Notes
---------------------------------------------------
-VARCHAR(n)      varchar         size specifies maximum length, no padding
-CHAR(n)         bpchar          blank padded to the specified fixed length
-TEXT            text            no specific upper limit on length
-BYTEA           bytea           variable-length byte array (null-byte safe)
-"char"          char            one character
-
    - +
    + + + + + + + +
    TypeInternal NameNotes
    VARCHAR(n)varcharsize specifies maximum +length, no padding
    CHAR(n)bpcharblank padded to the specified +fixed length
    TEXTtextno specific upper limit on +length
    BYTEAbyteavariable-length byte array +(null-byte safe)
    "char"charone character
    +

    You will see the internal name when examining system catalogs and in some error messages.

    @@ -884,10 +892,7 @@ BYTEA bytea variable-length byte array (null-byte safe) See the create_sequence manual page for more information - about sequences. You can also use each row's OID field as a - unique value. However, if you need to dump and reload the database, - you need to use pg_dump's -o option or COPY - WITH OIDS option to preserve the OIDs. + about sequences.

    4.11.2) How do I get the value of a SERIAL insert?

    @@ -918,19 +923,11 @@ BYTEA bytea variable-length byte array (null-byte safe) new_id = execute("SELECT currval('person_id_seq')"); -

    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. - In Perl, using DBI with the DBD::Pg module, the oid value is made - available via $sth->{pg_oid_status} after - $sth->execute().

    -

    4.11.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.

    + session, not by all sessions.

    4.11.4) Why aren't my sequence numbers reused on transaction abort? Why are there gaps in the numbering of @@ -964,36 +961,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.13) What is the meaning of some of the - terms used in PostgreSQL?

    - -

    Some of the source code and older documentation use terms that - have more common usage. Here are some:

    - -
      -
    • table, relation, class
      • - -
    • row, record, tuple
      • - -
    • column, field, attribute
      • - -
    • retrieve, select
      • - -
    • replace, update
      • - -
    • append, insert
      • - -
    • OID, serial value
      • - -
    • portal, cursor
      • - -
    • range variable, table name, table alias
      • -
    - -

    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.14) Why do I get the error "ERROR: +

    4.13) Why do I get the error "ERROR: Memory exhausted in AllocSetAlloc()"?

    You probably have run out of virtual memory on your system, @@ -1012,12 +980,12 @@ BYTEA bytea variable-length byte array (null-byte safe) backend is returning too much data, try it before starting the client. -

    4.15) How do I tell what PostgreSQL version +

    4.14) How do I tell what PostgreSQL version I am running?

    From psql, type SELECT version();

    -

    4.16) Why does my large-object operations +

    4.15) Why does my large-object operations get "invalid large obj descriptor"?

    You need to put BEGIN WORK and COMMIT @@ -1033,7 +1001,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.17) How do I create a column that will +

    4.16) How do I create a column that will default to the current time?

    Use CURRENT_TIMESTAMP:

    @@ -1041,7 +1009,7 @@ BYTEA bytea variable-length byte array (null-byte safe) CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP ); -

    4.18) How do I perform an outer join?

    +

    4.17) How do I perform an outer join?

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

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

    4.19) How do I perform queries using +

    4.18) How do I perform queries using multiple databases?

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

    -

    4.20) How do I return multiple rows or +

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

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

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

    4.20) 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 @@ -1111,7 +1079,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.22) What encryption options are available? +

    4.21) What encryption options are available?

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