diff options
Diffstat (limited to 'doc/FAQ')
| -rw-r--r-- | doc/FAQ | 160 |
1 files changed, 117 insertions, 43 deletions
@@ -1,9 +1,9 @@ Frequently Asked Questions (FAQ) for PostgreSQL - Last updated: Tue Sep 28 01:06:15 EDT 1999 + Last updated: Tue Mar 21 16:09:11 EST 2000 - Current maintainer: Bruce Momjian (maillist@candle.pha.pa.us) + Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us) The most recent version of this document can be viewed at the postgreSQL Web site, http://www.PostgreSQL.org. @@ -87,7 +87,10 @@ regexp searching? 4.14) In a query, how do I detect if a field is NULL? 4.15) What is the difference between the various character types? - 4.16) How do I create a serial/auto-incrementing field? + 4.16.1) How do I create a serial/auto-incrementing field? + 4.16.2) How do I get the value of a serial insert? + 4.16.3) Wouldn't use of currval() and nextval() lead to a race + condition with other concurrent backend processes? 4.17) What is an oid? What is a tid? 4.18) What is the meaning of some of the terms used in PostgreSQL? 4.19) Why do I get the error "FATAL: palloc failure: memory @@ -96,6 +99,7 @@ 4.21) My large-object operations get invalid large obj descriptor. Why? 4.22) How do I create a column that will default to the current time? + 4.23) Why are my subqueries using IN so slow? Extending PostgreSQL @@ -144,7 +148,8 @@ PostgreSQL Data Base Management System - Copyright (c) 1994-6 Regents of the University of California + Portions copyright (c) 1996-2000, PostgreSQL, Inc Portions Copyright + (c) 1994-6 Regents of the University of California Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written @@ -198,8 +203,8 @@ Unix/NT porting library. See pgsql/doc/README.NT in the distribution. There is also a web page at - http://members.tripod.com/~kevlo/postgres/portNT.html. There is - another port using U/Win at http://surya.wipro.com/uwin/ported.html. + http://www.freebsd.org/~kevlo/postgres/portNT.html. There is another + port using U/Win at http://surya.wipro.com/uwin/ported.html. 1.5) Where can I get PostgreSQL? @@ -279,10 +284,14 @@ There is a nice tutorial at http://w3.one.net/~jhoffman/sqltut.htm and at - http://ourworld.compuserve.com/homepages/Graeme_Birchall/DB2_COOK.HTM. + http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM. + + Another one is "Teach Yourself SQL in 21 Days, Second Edition" at + http://members.tripod.com/er4ebus/sql/index.htm Many of our users like The Practical SQL Handbook, Bowman et al., - Addison Wesley. + Addison Wesley. Others like Lan Times Guide to SQL, Groff et al., + Osborne McGraw-Hill. 1.11) Is PostgreSQL Y2K compliant? @@ -295,7 +304,7 @@ subscribe to the pgsql-hackers and pgsql-patches mailing lists. Third, submit high-quality patches to pgsql-patches. - There are about a dozen people who have commit privileges to the + There are about a dozen people who have COMMIT privileges to the PostgreSQL CVS archive. All of them have submitted so many high-quality patches that it was a pain for the existing committers to keep up, and we had confidence that patches they committed were likely @@ -506,13 +515,13 @@ 3.10) How do I tune the database engine for better performance? - Certainly, indices can speed up queries. The explain command allows + Certainly, indices can speed up queries. The EXPLAIN command allows you to see how PostgreSQL is interpreting your query, and which indices are being used. - If you are doing a lot of inserts, consider doing them in a large - batch using the copy command. This is much faster than single - individual inserts. Second, statements not in a begin work/commit + If you are doing a lot of INSERTs, consider doing them in a large + batch using the COPY command. This is much faster than single + individual INSERTS. Second, statements not in a BEGIN WORK/COMMIT transaction block are considered to be in their own transaction. Consider performing several statements in a single transaction block. This reduces the transaction overhead. Also consider dropping and @@ -534,7 +543,7 @@ is unwise to make this value too large, or you may run out of memory when a query invokes several concurrent sorts. - You can also use the cluster command to group data in base tables to + You can also use the CLUSTER command to group data in base tables to match an index. See the cluster(l) manual page for more details. 3.11) What debugging features are available in PostgreSQL? @@ -607,7 +616,7 @@ 3.13) What are the pg_tempNNN.NN files in my database directory? They are temporary files generated by the query executor. For example, - if a sort needs to be done to satisfy an order by, and the sort + if a sort needs to be done to satisfy an ORDER BY, and the sort requires more space than the backend's -S parameter allows, then temp files are created to hold the extra data. @@ -648,16 +657,17 @@ 4.2) What is the exact difference between binary cursors and normal cursors? - See the declare manual page for a description. + See the DECLARE manual page for a description. - 4.3) How do I select only the first few rows of a query? + 4.3) How do I SELECT only the first few rows of a query? - See the fetch manual page, or use SELECT ... LIMIT.... + See the FETCH manual page, or use SELECT ... LIMIT.... - This only prevents all row results from being transferred to the - client. The entire query must be evaluated, even if you only want just - the first few rows. Consider a query that has an order by. There is no - way to return any rows until the entire query is evaluated and sorted. + The entire query may have to be evaluated, even if you only want the + first few rows. Consider a query that has an ORDER BY. If there is an + index that matches the ORDER BY, PostgreSQL may be able to evaluate + only the first few records requested, or the entire query may have to + be evaluated until the desired rows have been generated. 4.4) How do I get a list of tables, or other information I see in psql? @@ -669,7 +679,7 @@ 4.5) How do you remove a column from a table? - We do not support alter table drop column, but do this: + We do not support ALTER TABLE DROP COLUMN, but do this: SELECT ... -- select all columns but the one you want to remove INTO TABLE new_table FROM old_table; @@ -725,35 +735,36 @@ \? to see them. Also try the file pgsql/src/tutorial/syscat.source. It illustrates - many of the selects needed to get information from the database system + many of the SELECTs needed to get information from the database system tables. 4.9) My queries are slow or don't make use of the indexes. Why? PostgreSQL does not automatically maintain statistics. One has to make - an explicit vacuum call to update the statistics. After statistics are + an explicit VACUUM call to update the statistics. After statistics are updated, the optimizer knows how many rows in the table, and can better decide if it should use indices. Note that the optimizer does not use indices in cases when the table is small because a sequential scan would be faster. - For column-specific optimization statistics, use vacuum analyze. - Vacuum analyze is important for complex multi-join queries, so the + For column-specific optimization statistics, use VACUUM ANALYZE. + VACUUM ANALYZE is important for complex multi-join queries, so the optimizer can estimate the number of rows returned from each table, and choose the proper join order. The backend does not keep track of - column statistics on its own, so vacuum analyze must be run to collect + column statistics on its own, so VACUUM ANALYZE must be run to collect them periodically. - Indexes are not used for order by operations. + Indexes are not used for ORDER BY operations. When using wild-card operators such as LIKE or ~, indices can only be used if the beginning of the search is anchored to the start of the string. So, to use indices, LIKE searches can should not begin with %, - and ~(regular expression searches) should start with ^. + and ~(regular expression searches) should start with ^. If you have + locale enabled, indexes can't be used for wild-card searches. 4.10) How do I see how the query optimizer is evaluating my query? - See the explain manual page. + See the EXPLAIN manual page. 4.11) What is an R-tree index? @@ -816,14 +827,60 @@ BYTEA bytea variable-length array of bytes using them. Specifically, the penalty is for access to all columns after the first column of this type. - 4.16) How do I create a serial/auto-incrementing field? + 4.16.1) How do I create a serial/auto-incrementing field? - PostgreSQL supports a serial data type. It auto-creates a sequence and - index on the column. 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. + PostgreSQL supports SERIAL data type. It auto-creates a sequence and + index on the column. For example, this... + CREATE TABLE person ( + id SERIAL, + name TEXT + ); + + ...is automatically translated into this... + CREATE SEQUENCE person_id_seq; + CREATE TABLE person ( + id INT4 NOT NULL DEFAULT nextval('person_id_seq'), + name TEXT + ); + CREATE UNIQUE INDEX person_id_key ON person ( id ); + + 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. + + For more details, see Bruce Momjian's chapter on Numbering Rows. + + 4.16.2) How do I get the back the generated SERIAL value after an insert? + + Probably the simplest approach is to 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.16.1, that + might look like this: + $newSerialID = nextval('person_id_seq'); + INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal'); + + You would then also have the new value stored in $newSerialID for use + in other queries (e.g., as a foreign key to the person table). Note + that the name of the automatically-created SEQUENCE object will be + named <table>_<serialcolumn>_seq, where table and serialcolumn are the + names of your table and your SERIAL column, respectively. + + Similarly, you could retrieve the just-assigned SERIAL value with the + currval() function after it was inserted by default, e.g., + INSERT INTO person (name) VALUES ('Blaise Pascal'); + $newID = currval('person_id_seq'); + + Finally, you could use the oid returned from the INSERT statement to + lookup the default value, though this is probably the least portable + approach. In perl, using DBI with Edmund Mergl's DBD::Pg module, the + oid value is made available via $sth->{pg_oid_status} after + $sth->execute(). + + 4.16.3) Wouldn't use of currval() and nextval() lead to a race condition + with other concurrent backend processes? + + No. That has been handled by the backends. 4.17) What is an oid? What is a tid? @@ -858,9 +915,9 @@ BYTEA bytea variable-length array of bytes Some of the source code and older documentation use terms that have more common usage. Here are some: + * table, relation, class * row, record, tuple - * attribute, field, column - * table, class + * column, field, attribute * retrieve, select * replace, update * append, insert @@ -913,10 +970,27 @@ BYTEA bytea variable-length array of bytes but this makes the column default to the time of table creation, not the time of row insertion. Instead do: - create table test (x int, modtime timestamp default text 'now'); + CREATE TABLE test (x int, modtime timestamp default now() ); + + The calling of the function now() prevents the default value from + being computed at table creation time, and delays it until insertion + time. We believe this will not be a problem in post-6.5.* releases. + + 4.23) Why are my subqueries using IN so slow? + + Currently, we join subqueries to outer queries by sequential scanning + the result of the subquery for each row of the outer query. A + workaround is to replace IN with EXISTS. For example, change: + SELECT * + FROM tab + WHERE col1 IN (SELECT col2 FROM TAB2) + + to: + SELECT * + FROM tab + WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = col2) - The casting of the value to text prevents the default value from being - computed at table creation time, and delays it until insertion time. + We hope to fix this limitation in a future release. _________________________________________________________________ Extending PostgreSQL |