From d72168d6e09017a9c6b1678285051f4fe58502a7 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Wed, 14 Jul 1999 20:32:59 +0000 Subject: [PATCH] Complete first pass at markup adjustments for docbook2man man pages. Add some internal cross references to select.sgml. --- doc/src/sgml/ref/select.sgml | 316 +++++++++++++------------- doc/src/sgml/ref/select_into.sgml | 24 +- doc/src/sgml/ref/set.sgml | 4 +- doc/src/sgml/ref/show.sgml | 53 ++--- doc/src/sgml/ref/unlisten.sgml | 351 +++++++++++++++-------------- doc/src/sgml/ref/update.sgml | 392 +++++++++++++++++---------------- doc/src/sgml/ref/vacuum.sgml | 453 +++++++++++++++++++------------------- doc/src/sgml/ref/vacuumdb.sgml | 106 ++++----- 8 files changed, 865 insertions(+), 834 deletions(-) diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 4e98311873..56c3d55405 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1,13 +1,13 @@ -SELECT + SELECT SQL - Language Statements -SELECT + SELECT Retrieve rows from a table or view. @@ -17,20 +17,17 @@ SELECT 1998-09-24 -SELECT [ALL|DISTINCT [ON column] ] - expression [ AS - name ] [, ...] - [ INTO [TEMP] [TABLE] new_table ] - [ FROM table - [alias ] [, ...] ] +SELECT [ ALL | DISTINCT [ ON column ] ] + expression [ AS name ] [, ...] + [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ] + [ FROM table [ alias ] [, ...] ] [ WHERE condition ] [ GROUP BY column [, ...] ] [ HAVING condition [, ...] ] - [ { UNION [ALL] | INTERSECT | EXCEPT } select ] + [ { UNION [ ALL ] | INTERSECT | EXCEPT } select ] [ ORDER BY column [ ASC | DESC ] [, ...] ] - [ FOR UPDATE [OF class_name...]] - [ LIMIT count [OFFSET|, count]] + [ FOR UPDATE [ OF class_name... ] ] + [ LIMIT { count | ALL } [ { OFFSET | , } count ] ] @@ -40,13 +37,11 @@ SELECT [ALL|DISTINCT [ON column] ] Inputs - + - - expression - + expression The name of a table's column or an expression. @@ -55,9 +50,7 @@ SELECT [ALL|DISTINCT [ON column] ] - - name - + name Specifies another name for a column or an expression using @@ -70,9 +63,8 @@ SELECT [ALL|DISTINCT [ON column] ] - - TEMP - + TEMPORARY + TEMP The table is created unique to this session, and is @@ -82,9 +74,7 @@ SELECT [ALL|DISTINCT [ON column] ] - - new_table - + new_table If the INTO TABLE clause is specified, the result of the @@ -105,9 +95,7 @@ SELECT [ALL|DISTINCT [ON column] ] - - table - + table The name of an existing table referenced by the FROM clause. @@ -116,9 +104,7 @@ SELECT [ALL|DISTINCT [ON column] ] - - alias - + alias An alternate name for the preceding @@ -130,9 +116,7 @@ SELECT [ALL|DISTINCT [ON column] ] - - condition - + condition A boolean expression giving a result of true or false. @@ -142,9 +126,7 @@ SELECT [ALL|DISTINCT [ON column] ] - - column - + column The name of a table's column. @@ -153,9 +135,7 @@ SELECT [ALL|DISTINCT [ON column] ] - - select - + select A select statement with all features except the ORDER BY clause. @@ -175,34 +155,32 @@ SELECT [ALL|DISTINCT [ON column] ] Outputs - - - - - Rows - - - - The complete set of rows resulting from the query specification. - - - - - - - count - - - - The count of rows returned by the query. - - - - + + + + Rows + + + The complete set of rows resulting from the query specification. + + + + + + + count + + + + The count of rows returned by the query. + + + + - + 1998-09-24 @@ -213,69 +191,84 @@ SELECT [ALL|DISTINCT [ON column] ] SELECT will return rows from one or more tables. Candidates for selection are rows which satisfy the WHERE condition; - if WHERE is omitted, all rows are candidates. + if WHERE is omitted, all rows are candidates. + (See .) + + DISTINCT will eliminate all duplicate rows from the selection. - DISTINCT ON column will eliminate all duplicates in the specified column; this is -equivalent to using GROUP BY column. ALL will return all candidate rows, -including duplicates. + DISTINCT ON column + will eliminate all duplicates in the specified column; this is + equivalent to using + GROUP BY column. + ALL will return all candidate rows, + including duplicates. + The GROUP BY clause allows a user to divide a table - conceptually into groups. (See GROUP BY clause). - + conceptually into groups. + (See .) + + The HAVING clause specifies a grouped table derived by the elimination of groups from the result of the previously - specified clause. (See HAVING clause). + specified clause. + (See .) + The ORDER BY clause allows a user to specify that he/she wishes the rows sorted according to the ASCending or - DESCending mode operator. (See ORDER BY clause) + DESCending mode operator. + (See .) + The UNION clause allows the result to be the collection of rows - returned by the queries involved. (See UNION clause). + returned by the queries involved. + (See .) + The INTERSECT give you the rows that are common to both queries. - (See INTERSECT clause). + (See .) + The EXCEPT give you the rows in the upper query not in the lower query. - (See EXCEPT clause). + (See .) + The FOR UPDATE clause allows the SELECT statement to perform exclusive locking of selected rows. - (See EXCEPT clause). + The LIMIT...OFFSET clause allows control over which rows are - returned by the query. + returned by the query. + You must have SELECT privilege to a table to read its values - (See GRANT/REVOKE statements). - + (See the GRANT/REVOKE statements). + 1998-09-24 - + <title id="where-title"> WHERE Clause The optional WHERE condition has the general form: -WHERE expr ETER">ce"PARreplaceable> [ log_op ... ] +WHERE expr ETER">ce"PARreplaceable> [ log_op ... ] where cond_op can be @@ -294,7 +287,7 @@ class="PARAMETER">log_op ... ] 1998-09-24 - + <title id="group-by-title"> GROUP BY Clause @@ -302,19 +295,24 @@ class="PARAMETER">log_op ... ] of this clause: GROUP BY column [, ...] - - - GROUP BY will condense into a single row all rows that share the same values for the - grouped columns; aggregates return values derived from all rows that make up the group. The value returned for an ungrouped - and unaggregated column is dependent on the order in which rows happen to be read from the database. - - - + + + + + GROUP BY will condense into a single row all rows that share the + same values for the + grouped columns; aggregates return values derived from all rows + that make up the group. The value returned for an ungrouped + and unaggregated column is dependent on the order in which rows + happen to be read from the database. + + + 1998-09-24 - + <title id="having-title"> HAVING Clause @@ -325,16 +323,17 @@ HAVING cond_expr where cond_expr is the same - as specified for the WHERE clause. + as specified for the WHERE clause. + HAVING specifies a grouped table derived by the elimination of groups from the result of the previously specified clause that do not meet the cond_expr. - + Each column referenced in -cond_expr shall unambiguously + cond_expr shall unambiguously reference a grouping column. @@ -343,7 +342,7 @@ HAVING cond_expr 1998-09-24 - + <title id="order-by-title"> ORDER BY Clause @@ -353,7 +352,8 @@ ORDER BY column [ ASC | DESC ] [, . column can be either a column - name or an ordinal number. + name or an ordinal number. + The ordinal numbers refers to the ordinal (left-to-right) position of the column. This feature makes it possible to define an ordering @@ -366,130 +366,136 @@ SELECT title, date_prod + 1 AS newlen FROM films ORDER BY newlen; - From release 6.4 of PostgreSQL, the columns in the ORDER BY clause do not need to appear in the SELECT clause. + From release 6.4 of PostgreSQL, the columns in the ORDER BY clause + do not need to appear in the SELECT clause. Thus the following statement is now legal: SELECT name FROM distributors ORDER BY code; - + + Optionally one may add the keyword DESC (descending) or ASC (ascending) after each column name in the ORDER BY clause. - If not specified, ASC is assumed by default. + If not specified, ASC is assumed by default. + 1998-09-24 - + <title id="union-title"> UNION Clause -table_query UNION [ ALL ] -table_query - [ ORDER BY column [ ASC | DESC ] [, ...] ] +table_query UNION [ ALL ] table_query + [ ORDER BY column [ ASC | DESC ] [, ...] ] - + where table_query - specifies any select expression without an ORDER BY clause. + specifies any select expression without an ORDER BY clause. + The UNION clause allows the result to be the collection of rows returned by the queries involved. (See UNION clause). The two tables that represent the direct operands of the UNION must have the same number of columns, and corresponding columns must be - of compatible data types. + of compatible data types. + By default, the result of UNION does not contain any duplicate rows - unless the ALL clause is specified. + unless the ALL clause is specified. + Multiple UNION operators in the same SELECT statement are evaluated left to right. Note that the ALL keyword is not global in nature, being - applied only for the current pair of table results. - + applied only for the current pair of table results. + + 1998-09-24 - + <title id="intersect-title"> INTERSECT Clause -table_query INTERSECT -table_query - [ ORDER BY column [ ASC | DESC ] [, ...] ] +table_query INTERSECT table_query + [ ORDER BY column [ ASC | DESC ] [, ...] ] where table_query - specifies any select expression without an ORDER BY clause. - + specifies any select expression without an ORDER BY clause. + + The INTERSECT clause allows the result to be all rows that are - common to the involved queries. (See INTERSECT clause). + common to the involved queries. The two tables that represent the direct operands of the INTERSECT must have the same number of columns, and corresponding columns must be - of compatible data types. + of compatible data types. + Multiple INTERSECT operators in the same SELECT statement are evaluated left to right. - - + 1998-09-24 - + <title id="except-title"> EXCEPT Clause -table_query EXCEPT - table_query +table_query EXCEPT table_query [ ORDER BY column [ ASC | DESC ] [, ...] ] where table_query - specifies any select expression without an ORDER BY clause. - + specifies any select expression without an ORDER BY clause. + + The EXCEPT clause allows the result to be rows from the upper query that are not in the lower query. (See EXCEPT clause). The two tables that represent the direct operands of the EXCEPT must have the same number of columns, and corresponding columns must be - of compatible data types. - + of compatible data types. + + Multiple EXCEPT operators in the same SELECT statement are evaluated left to right. - - + + - - Usage + To join the table films with the table distributors: - - + + SELECT f.title, f.did, d.name, f.date_prod, f.kind FROM distributors d, films f WHERE f.did = d.did @@ -513,12 +519,14 @@ Storia di una donna |108|Westward |1970-08-15|Romantic The King and I |109|20th Century Fox|1956-08-11|Musical Das Boot |110|Bavaria Atelier |1981-11-11|Drama Bed Knobs and Broomsticks|111|Walt Disney | |Musical - + + + To sum the column len of all films and group the results by kind: - - + + SELECT kind, SUM(len) AS total FROM films GROUP BY kind; kind |total @@ -528,14 +536,15 @@ SELECT kind, SUM(len) AS total FROM films GROUP BY kind; Drama | 14:28 Musical | 06:42 Romantic | 04:38 - + + To sum the column len of all films, group the results by kind and show those group totals that are less than 5 hours: - - + + SELECT kind, SUM(len) AS total FROM films GROUP BY kind @@ -545,13 +554,15 @@ SELECT kind, SUM(len) AS total ----------+------ Comedy | 02:58 Romantic | 04:38 - + + + - The following two examples are identical ways of sorting the individual + The following two examples are identical ways of sorting the individual results according to the contents of the second column (name): - - + + SELECT * FROM distributors ORDER BY name; SELECT * FROM distributors ORDER BY 2; @@ -570,7 +581,8 @@ SELECT * FROM distributors ORDER BY 2; 111|Walt Disney 112|Warner Bros. 108|Westward - + + This example shows how to obtain the union of the tables @@ -578,8 +590,8 @@ SELECT * FROM distributors ORDER BY 2; actors, restricting the results to those that begin with letter W in each table. Only distinct rows are to be used, so the ALL keyword is omitted: - - + + -- distributors: actors: -- did|name id|name -- ---+------------ --+-------------- @@ -604,8 +616,8 @@ Warner Bros. Warren Beatty Westward Woody Allen - - + + @@ -705,7 +717,7 @@ SELECT distributors.* WHERE name = 'Westwood'; diff --git a/doc/src/sgml/ref/unlisten.sgml b/doc/src/sgml/ref/unlisten.sgml index 5f37388d66..51e5c0c93a 100644 --- a/doc/src/sgml/ref/unlisten.sgml +++ b/doc/src/sgml/ref/unlisten.sgml @@ -1,178 +1,197 @@ - - - -UNLISTEN - -SQL - Language Statements - - - -UNLISTEN - - -Stop listening for notification - - - - -1998-10-19 - - -UNLISTEN { notifyname | * } - - - - -1998-10-19 - - -Inputs - - - - - - -notifyname - - - -Name of previously registered notify condition. - - - - - -* - - - -All current listen registrations for this backend are cleared. - - - - - - - - - - -1998-10-19 - - -Outputs - - - - - - + + + + UNLISTEN + + SQL - Language Statements + + + + UNLISTEN + + + Stop listening for notification + + + + + 1998-10-19 + + +UNLISTEN { notifyname | * } + + + + + 1998-10-19 + + + Inputs + + + + + + notifyname + + + Name of previously registered notify condition. + + + + + * + + + All current listen registrations for this backend are cleared. + + + + + + + + + + 1998-10-19 + + + Outputs + + + + + + UNLISTEN - - - -Acknowledgement that statement has executed. - - - - - - - - - - - -1998-10-19 - - -Description - - -UNLISTEN -is used to remove an existing NOTIFY registration. -UNLISTEN cancels any existing registration of the current -Postgres session as a listener on the notify -condition notifyname. -The special condition wildcard "*" cancels all listener registrations -for the current session. - - - -contains a more extensive -discussion of the use of LISTEN and -NOTIFY. - - - - -1998-10-19 - - -Notes - - -classname -needs not to be a valid class name but can be any string valid -as a name up to 32 characters long. - - -The backend does not complain if you UNLISTEN something you were not -listening for. -Each backend will automatically execute UNLISTEN * when -exiting. - - -A restriction in some previous releases of - Postgres that a -classname -which does not correspond to an actual table must be enclosed in double-quotes -is no longer present. - - - - - - -Usage - - - - + + + + Acknowledgement that statement has executed. + + + + + + + + + + + 1998-10-19 + + + Description + + + + UNLISTEN + is used to remove an existing NOTIFY registration. + UNLISTEN cancels any existing registration of the current + Postgres session as a listener on the notify + condition notifyname. + The special condition wildcard "*" cancels all listener registrations + for the current session. + + + + + contains a more extensive + discussion of the use of LISTEN and + NOTIFY. + + + + + 1998-10-19 + + + Notes + + + classname + needs not to be a valid class name but can be any string valid + as a name up to 32 characters long. + + + The backend does not complain if you UNLISTEN something you were not + listening for. + Each backend will automatically execute UNLISTEN * when + exiting. + + + A restriction in some previous releases of + Postgres that a + classname + which does not correspond to an actual table must be enclosed in double-quotes + is no longer present. + + + + + + + Usage + + + + To subscribe to an existing registration: + + postgres=> LISTEN virtual; LISTEN postgres=> NOTIFY virtual; NOTIFY ASYNC NOTIFY of 'virtual' from backend pid '12317' received - + + - + + Once UNLISTEN has been executed, further NOTIFY commands will be + ignored: + + postgres=> UNLISTEN virtual; UNLISTEN postgres=> NOTIFY virtual; NOTIFY -- notice no NOTIFY event is received -postgres=> - - - - - - -Compatibility - - - - -1998-10-19 - - -SQL92 - - - There is no UNLISTEN in SQL92. - - - - + + + + + + + Compatibility + + + + + 1998-10-19 + + + SQL92 + + + There is no UNLISTEN in SQL92. + + + + + + diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 6861c983f7..7383e7de68 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -1,163 +1,154 @@ - - - -UPDATE - -SQL - Language Statements - - - -UPDATE - - -Replaces values of columns in a table - - - - -1998-09-24 - - -UPDATE table SET column = expression [, ...] - [ FROM fromlist ] - [ WHERE condition ] - - - - - 1998-09-24 - - - Inputs - - - - - - - table - - - - The name of an existing table. - - - - - - column - - - - The name of a column in table. - - - - - - expression - - - - A valid expression or value to assign to column. - - - - - - fromlist - - - - A Postgres - non-standard extension to allow columns - from other tables to appear in the WHERE condition. - - - - - - condition - - - - Refer to the SELECT statement for a further description - of the WHERE clause. - - - - - - - - - - 1998-09-24 - - - Outputs - - - - - - - UPDATE # - - - - Message returned if successful. - The # - means the number of rows updated. - If # - is equal 0 no rows are updated. - - - - - - - - - - - 1998-09-24 - - - Description - - - UPDATE changes the values of the columns specified for - all rows which satisfy condition. Only the columns - to be modified need appear as column. - - - Array references use the same syntax found in SELECT. - That is, either single array elements, a range of array - elements or the entire array may be replaced with a single - query. - - - You must have write access to the table in order to modify - it, as well as read access to any table whose values are - mentioned in the WHERE condition. - - - - - - Usage - - - - - --Change word "Drama" with "Dramatic" on column kind: - -- - UPDATE films - SET kind = 'Dramatic' - WHERE kind = 'Drama'; - - SELECT * FROM films WHERE kind = 'Dramatic' OR kind = 'Drama'; + + + + UPDATE + + SQL - Language Statements + + + + UPDATE + + + Replaces values of columns in a table + + + + + 1998-09-24 + + +UPDATE table SET R">colle> = expression [, ...] + [ FROM fromlist ] + [ WHERE condition ] + + + + + 1998-09-24 + + + Inputs + + + + + + table + + + The name of an existing table. + + + + + column + + + The name of a column in table. + + + + + expression + + + A valid expression or value to assign to column. + + + + + fromlist + + + A Postgres + non-standard extension to allow columns + from other tables to appear in the WHERE condition. + + + + + condition + + + Refer to the SELECT statement for a further description + of the WHERE clause. + + + + + + + + + + 1998-09-24 + + + Outputs + + + + + + +UPDATE # + + + + Message returned if successful. + The # + means the number of rows updated. + If # + is equal 0 no rows are updated. + + + + + + + + + + + 1998-09-24 + + + Description + + + UPDATE changes the values of the columns specified for + all rows which satisfy condition. Only the columns + to be modified need appear as column. + + + + Array references use the same syntax found in SELECT. + That is, either single array elements, a range of array + elements or the entire array may be replaced with a single + query. + + + + You must have write access to the table in order to modify + it, as well as read access to any table whose values are + mentioned in the WHERE condition. + + + + + + Usage + + + + Change word "Drama" with "Dramatic" on column kind: + + +UPDATE films + SET kind = 'Dramatic' + WHERE kind = 'Drama'; +SELECT * FROM films WHERE kind = 'Dramatic' OR kind = 'Drama'; code |title |did| date_prod|kind |len -----+-------------+---+----------+----------+------ @@ -166,35 +157,52 @@ UPDATE table SET - - - - - - Compatibility - - - - - - - 1998-09-24 - - - SQL92 - - - SQL92 defines a different syntax for positioned UPDATE statement: - - - UPDATE table SET column = expression [, ...] - WHERE CURRENT OF cursor - - - where cursor - identifies an open cursor. - - - - + + + + + + + Compatibility + + + + + + + 1998-09-24 + + + SQL92 + + + SQL92 defines a different syntax for positioned UPDATE statement: + + +UPDATE table SET column = expression [, ...] + WHERE CURRENT OF cursor + + + where cursor + identifies an open cursor. + + + + + + diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 98758c773e..2e9a3fe653 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -1,212 +1,206 @@ - - - -VACUUM - -SQL - Language Statements - - - -VACUUM - - -Clean and analyze a Postgres database - - - - -1998-10-04 - - -VACUUM [ VERBOSE ] [ ANALYZE ] [ table ] -VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] - + + + + VACUUM + + SQL - Language Statements + + + + VACUUM + + + Clean and analyze a Postgres database + + + + + 1998-10-04 + + +VACUUM [ VERBOSE ] [ ANALYZE ] [ table ] +VACUUM [ VERBOSE ] ANALYZE [ ER">tBLE> [ (column [, ...] ) ] ] + + + + + 1998-10-04 + + + Inputs + + + + + + VERBOSE + + + Prints a detailed vacuum activity report for each table. + + + + + ANALYZE + + + Updates column statistics used by the optimizer to + determine the most efficient way to execute a query. + The statistics represent the disbursion of the data in each column. + This information is valuable when several execution paths are possible. + + + + + table + + + The name of a specific table to vacuum. Defaults to all tables. + + + + + column + + + The name of a specific column to analyze. Defaults to all columns. + + + + + + + + + + 1998-10-04 + + + Outputs + + + + + + +VACUUM + + + + The command has been accepted and the database is being cleaned. + + + - - - 1998-10-04 - - - Inputs - - - - - - - VERBOSE - - - - Prints a detailed vacuum activity report for each table. - - - - - - ANALYZE - - - - Updates column statistics used by the optimizer to - determine the most efficient way to execute a query. - The statistics represent the disbursion of the data in each column. - This information is valuable when several execution paths are possible. - - - - - - table - - - - The name of a specific table to vacuum. Defaults to all tables. - - - - - - column - - - - The name of a specific column to analyze. Defaults to all columns. - - - - - - + + +NOTICE: --Relation table-- + + + + The report header for table. + + + - - - 1998-10-04 - - - Outputs - - + + +NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0; + Tup 1000: Vac 3000, Crash 0, UnUsed 0, MinLen 188, MaxLen 188; + Re-using: Free/Avail. Space 586952/586952; EndEmpty/Avail. Pages 0/74. + Elapsed 0/0 sec. + + + + The analysis for table itself. + + + + + + +NOTICE: Index index: Pages 28; + Tuples 1000: Deleted 3000. Elapsed 0/0 sec. + + + + The analysis for an index on the target table. + + + + + + + - - - - VACUUM - - - - The command has been accepted and the database is being cleaned. - - - - - - - NOTICE: --Relation table-- - - - - The report header for table. - - - + + + 1998-10-04 + + + Description + + + VACUUM serves two purposes in + Postgres as both a means to reclaim storage and + also a means to collect information for the optimizer. + - - - NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0; - Tup 1000: Vac 3000, Crash 0, UnUsed 0, MinLen 188, MaxLen 188; - Re-using: Free/Avail. Space 586952/586952; EndEmpty/Avail. Pages 0/74. - Elapsed 0/0 sec. - - - - The analysis for table itself. - - - - - - - NOTICE: Index index: Pages 28; - Tuples 1000: Deleted 3000. Elapsed 0/0 sec. - - - - The analysis for an index on the target table. - - - - - - - + + VACUUM opens every class in the database, + cleans out records from rolled back transactions, and updates statistics in the + system catalogs. The statistics maintained include the number of + tuples and number of pages stored in all classes. + - - - 1998-10-04 - - - Description - - - VACUUM serves two purposes in - Postgres as both a means to reclaim storage and - also a means to collect information for the optimizer. - - - VACUUM opens every class in the database, - cleans out records from rolled back transactions, and updates statistics in the - system catalogs. The statistics maintained include the number of - tuples and number of pages stored in all classes. + + Running VACUUM + periodically will increase the speed of the database in processing user queries. + - Running VACUUM - periodically will increase the speed of the database in processing user queries. - + + + 1998-10-04 + + + Notes + + + The open database is the target for VACUUM. + + + We recommend that active production databases be cleaned nightly, in order + to keep statistics relatively current. The VACUUM + query may be executed at any time, however. In particular, after + copying a large class into Postgres + or after deleting a large number of + records, it may be a good idea to issue a VACUUM + query. This will update the system catalogs with the results of all + recent changes, and allow the Postgres + query optimizer to make better choices in planning user queries. + + + If the server crashes during a VACUUM command, + chances are it will leave a lock file hanging around. + Attempts to re-run the VACUUM command + result in an error message about the creation of a lock file. If you + are sure VACUUM is not running, + remove the pg_vlock file in your + database directory + (i.e. PGDATA/base/dbname/pg_vlock). + + + - - - 1998-10-04 - - - Notes - - - The open database is target for VACUUM. - - - We recommend that active production databases be cleaned nightly, in order - to keep statistics relatively current. The VACUUM - query may be executed at any time, however. In particular, after - copying a large class into Postgres - or after deleting a large number of - records, it may be a good idea to issue a VACUUM - query. This will update the system catalogs with the results of all - recent changes, and allow the Postgres - query optimizer to make better choices in planning user queries. - - - If the server crashes during a VACUUM command, - chances are it will leave a lock file hanging around. - Attempts to re-run the VACUUM command - result in an error message about the creation of a lock file. If you - are sure VACUUM is not running, - remove the pg_vlock file in your - database directory - (i.e. PGDATA/base/dbname/pg_vlock). - - - - + + + Usage + + + The following is an example from running VACUUM on a table + in the regression database: - - - Usage - - - The following is an example from running VACUUM on a table - in the regression database: - - + regression=> vacuum verbose analyze onek; NOTICE: --Relation onek-- NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0; @@ -223,24 +217,41 @@ NOTICE: Index onek_hundred: Pages 12; Tuples 1000: Deleted 1000. Elapsed 0/0 se NOTICE: Index onek_unique2: Pages 19; Tuples 1000: Deleted 1000. Elapsed 0/0 sec. NOTICE: Index onek_unique1: Pages 17; Tuples 1000: Deleted 1000. Elapsed 0/0 sec. VACUUM - - - + + + + + + + Compatibility + + + + 1998-10-04 + + + SQL92 + + + There is no VACUUM statement in SQL92. + + + + - - - Compatibility - - - - 1998-10-04 - - - SQL92 - - - There is no VACUUM statement in SQL92. - - - - + diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index 8dad71bbdc..55774395e3 100644 --- a/doc/src/sgml/ref/vacuumdb.sgml +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -18,12 +18,9 @@ 1998-10-04 -vacuumdb [ --analyze | -z ] [ --verbose | -v ] [ dbname ] -vacuumdb [ -h host ] [ -p - port ] - [ --table 'table [ ( - column [,...] ) ]' ] +vacuumdb [ --analyze | -z ] [ --verbose | -v ] [ dbname ] +vacuumdb [ -h host ] [ -p port ] + [ --table 'table [ ( column [,...] ) ]' ] [ dbname ] @@ -39,9 +36,7 @@ vacuumdb [ -h host ] [ -p - - dbname - + dbname Specifies the name of the database to be cleaned or analyzed. @@ -52,44 +47,30 @@ vacuumdb [ -h host ] [ -p - + - - --analyze - - - -z - + --analyze + -z Calculate statistics on the database for use by the optimizer. - + - - --verbose - - - -v - + --verbose + -v Print detailed information during processing. - + - - --table table [ - (column [,...]) ] - - - -t table [ - (column [,...]) ] - + --table table [ (column [,...]) ] + -t table [ (column [,...]) ] Clean or analyze table only. @@ -98,18 +79,17 @@ vacuumdb [ -h host ] [ -p - + + vacuumdb also accepts the following command line arguments for connection parameters: - - -h host - + -h host Specifies the hostname of the machine on which the @@ -119,11 +99,9 @@ vacuumdb [ -h host ] [ -p - + - - -p port - + -p port Specifies the Internet TCP/IP port or local Unix domain socket file @@ -134,11 +112,9 @@ vacuumdb [ -h host ] [ -p - + - - -u - + -u Use password authentication. @@ -148,7 +124,6 @@ vacuumdb [ -h host ] [ -p - @@ -166,11 +141,10 @@ vacuumdb [ -h host ] [ -p - - ERROR: Can't vacuum columns, only tables. You can 'vacuum analyze' columns. - vacuumdb: database vacuum failed on dbname. - + +ERROR: Can't vacuum columns, only tables. You can 'vacuum analyze' columns. +vacuumdb: database vacuum failed on dbname. + The non-analyze mode requires cleaning full tables or databases. @@ -180,11 +154,11 @@ vacuumdb [ -h host ] [ -p - - Connection to database 'template1' failed. - connectDB() failed: Is the postmaster running and accepting connections - at 'UNIX Socket' on port 'port'? - + +Connection to database 'template1' failed. +connectDB() failed: Is the postmaster running and accepting connections +at 'UNIX Socket' on port 'port'? + vacuumdb could not attach to the @@ -199,10 +173,10 @@ vacuumdb [ -h host ] [ -p - - Connection to database 'dbname' failed. - FATAL 1: SetUserId: user 'username' is not in 'pg_shadow' - + +Connection to database 'dbname' failed. +FATAL 1: SetUserId: user 'username' is not in 'pg_shadow' + You do not have a valid entry in the relation pg_shadow @@ -211,7 +185,6 @@ vacuumdb [ -h host ] [ -p - @@ -234,7 +207,7 @@ vacuumdb [ -h host ] [ -p Description - + vacuumdb is a utility for cleaning a Postgres database. @@ -250,7 +223,7 @@ vacuumdb [ -h host ] [ -p Notes - + See for more details. @@ -267,22 +240,23 @@ vacuumdb [ -h host ] [ -p To clean a database of the same name as the user: - % vacuumdb +% vacuumdb + To analyze a database named bigdb for the optimizer: - % vacuumdb --analyze bigdb +% vacuumdb --analyze bigdb To analyze a single column bar in table foo in a database named xyzzy for the optimizer: - + - % vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy +% vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy -- 2.11.0