From 2467394ee1566e82d0314d12a0d1c0a5670a28c9 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 18 Jun 2004 06:14:31 +0000 Subject: Tablespaces. Alternate database locations are dead, long live tablespaces. There are various things left to do: contrib dbsize and oid2name modules need work, and so does the documentation. Also someone should think about COMMENT ON TABLESPACE and maybe RENAME TABLESPACE. Also initlocation is dead, it just doesn't know it yet. Gavin Sherry and Tom Lane. --- doc/src/sgml/catalogs.sgml | 110 ++- doc/src/sgml/manage-ag.sgml | 22 +- doc/src/sgml/ref/allfiles.sgml | 4 +- doc/src/sgml/ref/create_database.sgml | 104 +-- doc/src/sgml/ref/create_index.sgml | 50 +- doc/src/sgml/ref/create_schema.sgml | 38 +- doc/src/sgml/ref/create_sequence.sgml | 20 +- doc/src/sgml/ref/create_table.sgml | 63 +- doc/src/sgml/ref/create_tablespace.sgml | 156 ++++ doc/src/sgml/ref/drop_tablespace.sgml | 103 +++ doc/src/sgml/ref/grant.sgml | 20 +- doc/src/sgml/ref/psql-ref.sgml | 1417 ++++++++++++++++--------------- doc/src/sgml/ref/revoke.sgml | 8 +- doc/src/sgml/reference.sgml | 4 +- 14 files changed, 1253 insertions(+), 866 deletions(-) create mode 100644 doc/src/sgml/ref/create_tablespace.sgml create mode 100644 doc/src/sgml/ref/drop_tablespace.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 84ae3cb205f..36695e6811f 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,6 +1,6 @@ @@ -178,6 +178,11 @@ planner statistics + + pg_tablespace + tablespaces within this database cluster + + pg_trigger triggers @@ -1058,6 +1063,17 @@ Name of the on-disk file of this relation; 0 if none + + reltablespace + oid + pg_tablespace.oid + + The tablespace in which this relation is stored. If zero, + the database's default tablespace is implied. (Not meaningful + if the relation has no on-disk file.) + + + relpages int4 @@ -1602,13 +1618,15 @@ - datpath - text - + dattablespace + oid + pg_tablespace.oid - If the database is stored at an alternative location then this - records the location. It's either an environment variable name - or an absolute path, depending how it was entered. + The default tablespace for the database. + Within this database, all tables for which + pg_class.reltablespace is zero + will be stored in this tablespace; in particular, all the non-shared + system catalogs will be there. @@ -2386,6 +2404,17 @@ Owner of the namespace + + nsptablespace + oid + pg_tablespace.oid + + The default tablespace in which to place relations created in this + namespace. If zero, the database's default tablespace is implied. + (Changing this does not affect pre-existing relations.) + + + nspacl aclitem[] @@ -3232,6 +3261,73 @@ + + <structname>pg_tablespace</structname> + + + pg_tablespace + + + + The catalog pg_tablespace stores information + about the available tablespaces. Tables can be placed in particular + tablespaces to aid administration of disk layout. + + + + Unlike most system catalogs, pg_tablespace + is shared across all databases of a cluster: there is only one + copy of pg_tablespace per cluster, not + one per database. + + + + <structname>pg_tablespace</> Columns + + + + + Name + Type + References + Description + + + + + + spcname + name + + Tablespace name + + + + spcowner + int4 + pg_shadow.usesysid + Owner of the tablespace, usually the user who created it + + + + spclocation + text + + Location (directory path) of the tablespace + + + + spcacl + aclitem[] + + Access privileges + + + +
+ + + <structname>pg_trigger</structname> diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index df6d85afab1..34737ffd4fe 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -1,5 +1,5 @@ @@ -298,6 +298,11 @@ ALTER DATABASE mydb SET geqo TO off; Alternative Locations + + XXX this is entirely dead now, and needs to be replaced by a DBA-level + description of tablespaces. + + It is possible to create a database in a location other than the default location for the installation. But remember that all database access @@ -368,21 +373,6 @@ CREATE DATABASE name WITH LOCATION 'location'; Databases created in alternative locations can be accessed and dropped like any other database. - - - - It can also be possible to specify absolute paths directly to the - CREATE DATABASE command without defining environment - variables. This is disallowed by default because it is a security - risk. To allow it, you must compile PostgreSQL with - the C preprocessor macro ALLOW_ABSOLUTE_DBPATHS - defined. One way to do this is to run the compilation step like - this: - -gmake CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS all - - - diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index a44e21e69ec..352a21c292d 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -44,6 +44,7 @@ Complete list of usable sgml source files in this directory. + @@ -66,6 +67,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index 065ee479366..deb76d30f50 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -1,5 +1,5 @@ @@ -22,9 +22,9 @@ PostgreSQL documentation CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner ] - [ LOCATION [=] 'dbpath' ] [ TEMPLATE [=] template ] - [ ENCODING [=] encoding ] ] + [ ENCODING [=] encoding ] + [ TABLESPACE [=] tablespace ] ] @@ -50,29 +50,6 @@ CREATE DATABASE name privilege can only create databases owned by themselves. - - An alternative location can be specified in order to, - for example, store the database on a different disk. - The path must have been prepared with the - - command. - - - - If the path name does not contain a slash, it is interpreted - as an environment variable name, which must be known to the - server process. This way the database administrator can - exercise control over locations in which databases can be created. - (A customary choice is, e.g., PGDATA2.) - If the server is compiled with ALLOW_ABSOLUTE_DBPATHS - (not so by default), absolute path names, as identified by - a leading slash - (e.g., /usr/local/pgsql/data), - are allowed as well. - In either case, the final path name must be absolute and must not - contain any single quotes. - - By default, the new database will be created by cloning the standard system database template1. A different template can be @@ -83,13 +60,7 @@ CREATE DATABASE name version of PostgreSQL. This is useful if you wish to avoid copying any installation-local objects that may have been added to - template1. - - - - The optional encoding parameter allows selection of the database - encoding. When not specified, it defaults to the encoding used by - the selected template database. + template1. @@ -101,7 +72,7 @@ CREATE DATABASE name name - The name of a database to create. + The name of a database to create. @@ -110,41 +81,43 @@ CREATE DATABASE name The name of the database user who will own the new database, - or DEFAULT to use the default (namely, the - user executing the command). + or DEFAULT to use the default (namely, the + user executing the command). - dbpath + template - An alternate file-system location in which to store the new database, - specified as a string literal; - or DEFAULT to use the default location. + The name of the template from which to create the new database, + or DEFAULT to use the default template + (template1). - template + encoding - The name of the template from which to create the new database, - or DEFAULT to use the default template - (template1). + Character set encoding to use in the new database. Specify + a string constant (e.g., 'SQL_ASCII'), + or an integer encoding number, or DEFAULT + to use the default encoding. The character sets supported by the + PostgreSQL server are described in + . - encoding + tablespace - Character set encoding to use in the new database. Specify - a string constant (e.g., 'SQL_ASCII'), - or an integer encoding number, or DEFAULT - to use the default encoding. The character sets supported by the - PostgreSQL server are described in - . + Specifies the default tablespace for the new database. + If not specified, the same tablespace that is default for + the template database is used. See + + for more information. @@ -167,9 +140,7 @@ CREATE DATABASE name Errors along the line of could not initialize database directory are most likely related to insufficient permissions on the data - directory, a full disk, or other file system problems. When using an - alternate location, the user under - which the database server is running must have access to the location. + directory, a full disk, or other file system problems. @@ -181,13 +152,6 @@ CREATE DATABASE name wrapper program around this command, provided for convenience. - - There are security issues involved with using alternate database - locations specified with absolute path names; this is why the feature - is not enabled by default. See for more information. - - Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as @@ -205,24 +169,6 @@ CREATE DATABASE name CREATE DATABASE lusiadas; - - - - - To create a new database in an alternate area - ~/private_db, execute the following from the - shell: - - -mkdir private_db -initlocation ~/private_db - - - Then execute the following from within a - psql session: - - -CREATE DATABASE elsewhere WITH LOCATION '/home/olly/private_db'; diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index bfde61ee6c7..93ae0c1c816 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -1,5 +1,5 @@ @@ -22,6 +22,7 @@ PostgreSQL documentation CREATE [ UNIQUE ] INDEX name ON table [ USING method ] ( { column | ( expression ) } [ opclass ] [, ...] ) + [ TABLESPACE tablespace ] [ WHERE predicate ] @@ -78,7 +79,7 @@ CREATE [ UNIQUE ] INDEX name ON Indexes are not used for IS NULL clauses by default. - The best way to use indexes in such cases is to create a partial index + The best way to use indexes in such cases is to create a partial index using an IS NULL comparison. @@ -109,11 +110,11 @@ CREATE [ UNIQUE ] INDEX name ON UNIQUE - Causes the system to check for - duplicate values in the table when the index is created (if data - already exist) and each time data is added. Attempts to - insert or update data which would result in duplicate entries - will generate an error. + Causes the system to check for + duplicate values in the table when the index is created (if data + already exist) and each time data is added. Attempts to + insert or update data which would result in duplicate entries + will generate an error. @@ -122,9 +123,9 @@ CREATE [ UNIQUE ] INDEX name ON name - The name of the index to be created. No schema name can be included - here; the index is always created in the same schema as its parent - table. + The name of the index to be created. No schema name can be included + here; the index is always created in the same schema as its parent + table. @@ -133,7 +134,7 @@ CREATE [ UNIQUE ] INDEX name ON table - The name (possibly schema-qualified) of the table to be indexed. + The name (possibly schema-qualified) of the table to be indexed. @@ -154,7 +155,7 @@ CREATE [ UNIQUE ] INDEX name ON column - The name of a column of the table. + The name of a column of the table. @@ -163,10 +164,10 @@ CREATE [ UNIQUE ] INDEX name ON expression - An expression based on one or more columns of the table. The - expression usually must be written with surrounding parentheses, - as shown in the syntax. However, the parentheses may be omitted - if the expression has the form of a function call. + An expression based on one or more columns of the table. The + expression usually must be written with surrounding parentheses, + as shown in the syntax. However, the parentheses may be omitted + if the expression has the form of a function call. @@ -175,7 +176,17 @@ CREATE [ UNIQUE ] INDEX name ON opclass - The name of an operator class. See below for details. + The name of an operator class. See below for details. + + + + + + tablespace + + + The tablespace in which to create the index. If not specified, + the tablespace of the parent table is used. @@ -184,10 +195,11 @@ CREATE [ UNIQUE ] INDEX name ON predicate - The constraint expression for a partial index. + The constraint expression for a partial index. + @@ -260,7 +272,7 @@ SELECT * FROM points --> - + Compatibility diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index 807f358d1f9..8668612cc6a 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -1,5 +1,5 @@ @@ -12,7 +12,7 @@ PostgreSQL documentation CREATE SCHEMA define a new schema - + CREATE SCHEMA @@ -20,8 +20,8 @@ PostgreSQL documentation -CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ] -CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ] +CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ TABLESPACE tablespace ] [ schema_element [ ... ] ] +CREATE SCHEMA AUTHORIZATION username [ TABLESPACE tablespace ] [ schema_element [ ... ] ] @@ -54,7 +54,7 @@ CREATE SCHEMA AUTHORIZATION username - + Parameters @@ -63,8 +63,8 @@ CREATE SCHEMA AUTHORIZATION usernameschemaname - The name of a schema to be created. If this is omitted, the user name - is used as the schema name. + The name of a schema to be created. If this is omitted, the user name + is used as the schema name. @@ -74,8 +74,19 @@ CREATE SCHEMA AUTHORIZATION username The name of the user who will own the schema. If omitted, - defaults to the user executing the command. Only superusers - may create schemas owned by users other than themselves. + defaults to the user executing the command. Only superusers + may create schemas owned by users other than themselves. + + + + + + tablespace + + + The name of the tablespace that is to be the default tablespace + for all new objects created in the schema. If not supplied, the schema + will inherit the default tablespace of the database. @@ -102,8 +113,10 @@ CREATE SCHEMA AUTHORIZATION username To create a schema, the invoking user must have the - CREATE privilege for the current database. (Of course, - superusers bypass this check.) + CREATE privilege for the current database. + Also, the TABLESPACE option requires having + CREATE privilege for the specified tablespace. + (Of course, superusers bypass these checks.) @@ -181,7 +194,8 @@ CREATE VIEW hollywood.winners AS - + + diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index de5fd180f76..de9fa57ee14 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -1,5 +1,5 @@ @@ -12,7 +12,7 @@ PostgreSQL documentation CREATE SEQUENCE define a new sequence generator - + CREATE SEQUENCE @@ -23,6 +23,7 @@ PostgreSQL documentation CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] + [ TABLESPACE tablespace ] @@ -193,6 +194,19 @@ SELECT * FROM name; + + + tablespace + + + The optional clause TABLESPACE tablespace specifies + the tablespace in which to create the sequence. If this clause + is not supplied, the tablespace of the sequence's schema will be used. + + + + @@ -268,7 +282,7 @@ CREATE SEQUENCE serial START 101; Select the next number from this sequence: SELECT nextval('serial'); - + nextval --------- 114 diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 511aacc73f3..40462752f11 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,5 +1,5 @@ @@ -28,6 +28,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE tablespace ] where column_constraint is: @@ -48,7 +49,7 @@ and table_constraint is: [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] - + @@ -321,7 +322,7 @@ and table_constraint is: - + UNIQUE (column constraint) UNIQUE ( column_name [, ... ] ) (table constraint) @@ -405,9 +406,9 @@ and table_constraint is: REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint) FOREIGN KEY ( column [, ... ] ) - REFERENCES reftable [ ( refcolumn [, ... ] ) ] + REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] - [ ON DELETE action ] + [ ON DELETE action ] [ ON UPDATE action ] (table constraint) @@ -514,7 +515,7 @@ and table_constraint is: - + DEFERRABLE NOT DEFERRABLE @@ -553,7 +554,7 @@ and table_constraint is: The behavior of temporary tables at the end of a transaction - block can be controlled using ON COMMIT. + block can be controlled using ON COMMIT. The three options are: @@ -561,19 +562,19 @@ and table_constraint is: PRESERVE ROWS - No special action is taken at the ends of transactions. - This is the default behavior. + No special action is taken at the ends of transactions. + This is the default behavior. - + - + DELETE ROWS All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic - is done at each commit. + is done at each commit. @@ -583,7 +584,7 @@ and table_constraint is: The temporary table will be dropped at the end of the current - transaction block. + transaction block. @@ -591,8 +592,20 @@ and table_constraint is: + + + TABLESPACE tablespace + + + The tablespace is the name + of the tablespace in which the new table is to be created. If not + supplied, the default tablespace of the table's schema will be used. + + + + - + Notes @@ -696,7 +709,7 @@ CREATE TABLE films ( ); - + Define a check column constraint: @@ -719,7 +732,7 @@ CREATE TABLE distributors ( ); - + Define a primary key table constraint for the table films. Primary key table constraints can be defined @@ -749,7 +762,7 @@ CREATE TABLE distributors ( did integer, name varchar(40), PRIMARY KEY(did) -); +); @@ -812,7 +825,7 @@ CREATE TABLE distributors ( - + Compatibility @@ -827,7 +840,7 @@ CREATE TABLE distributors ( Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect is not the same. In the - standard, + standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead @@ -889,7 +902,7 @@ CREATE TABLE distributors ( column, its presence is simply noise. - + Inheritance @@ -923,6 +936,15 @@ CREATE TABLE distributors ( DROP COLUMN, so it seems cleaner to ignore this spec restriction. + + + TABLESPACE + + + The PostgreSQL concept of tablespaces is not + standard. + + @@ -932,6 +954,7 @@ CREATE TABLE distributors ( + diff --git a/doc/src/sgml/ref/create_tablespace.sgml b/doc/src/sgml/ref/create_tablespace.sgml new file mode 100644 index 00000000000..9f670a817d6 --- /dev/null +++ b/doc/src/sgml/ref/create_tablespace.sgml @@ -0,0 +1,156 @@ + + + + + CREATE TABLESPACE + SQL - Language Statements + + + + CREATE TABLESPACE + define a new tablespace + + + + CREATE TABLESPACE + + + + +CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory' + + + + + Description + + + CREATE TABLESPACE registers a new cluster-wide + tablespace. The tablespace name must be distinct from the name of any + existing tablespace in the database cluster. + + + + A tablespace allows superusers to define an alternative location on the + file system where the data files representing database objects + (such as tables and indexes) may reside. + + + + A user with appropriate privileges can pass + tablespacename to CREATE + DATABASE, CREATE SCHEMA, CREATE TABLE, + CREATE INDEX or CREATE SEQUENCE to have the data + files for these objects stored within the specified tablespace. + + + + + Parameters + + + + tablespacename + + + The name of a tablespace to be created. + + + + + + username + + + The name of the user who will own the tablespace. If omitted, + defaults to the user executing the command. Only superusers + may create tablespaces, but they can assign ownership of tablespaces + to non-superusers. + + + + + + directory + + + The directory that will be used for the tablespace. The directory + must be empty and must be owned by the + PostgreSQL system user. The directory must be + specified by an absolute path name. + + + + + + + + Notes + + + Tablespaces are only supported on systems that support symbolic links. + + + + + Examples + + + Create a tablespace dbspace at /data/dbs: + +CREATE TABLESPACE dbspace LOCATION '/data/dbs'; + + + + + Create a tablespace indexspace at /data/indexes + owned by user genevieve: + +CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes'; + + + + + + Compatibility + + + CREATE TABLESPACE is a PostgreSQL + extension. + + + + + See Also + + + + + + + + + + + + + + diff --git a/doc/src/sgml/ref/drop_tablespace.sgml b/doc/src/sgml/ref/drop_tablespace.sgml new file mode 100644 index 00000000000..ba8415208e3 --- /dev/null +++ b/doc/src/sgml/ref/drop_tablespace.sgml @@ -0,0 +1,103 @@ + + + + + DROP TABLESPACE + SQL - Language Statements + + + + DROP TABLESPACE + remove a tablespace + + + + DROP TABLESPACE + + + + +DROP TABLESPACE tablespacename + + + + + Description + + + DROP TABLESPACE removes a tablespace from the system. + + + + A tablespace can only be dropped by its owner or a superuser. + The tablespace must be empty of all database objects before it can be + dropped. It is possible that objects in other databases may still reside + in the tablespace even if no objects in the current database are using + the tablespace. + + + + + Parameters + + + + tablespacename + + + The name of a tablespace. + + + + + + + + Examples + + + To remove tablespace mystuff from the system: + +DROP TABLESPACE mystuff; + + + + + + Compatibility + + + DROP TABLESPACE is a PostgreSQL + extension. + + + + + See Also + + + + + + + + + diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 25ba6e29bd6..a6a2d4d994e 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -1,5 +1,5 @@ @@ -29,6 +29,10 @@ GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] +GRANT { CREATE | ALL [ PRIVILEGES ] } + ON TABLESPACE tablespacename [, ...] + TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] + GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ([type, ...]) [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] @@ -87,7 +91,7 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } Depending on the type of object, the initial default privileges may include granting some privileges to PUBLIC. - The default is no public access for tables and schemas; + The default is no public access for tables, schemas, and tablespaces; TEMP table creation privilege for databases; EXECUTE privilege for functions; and USAGE privilege for languages. @@ -184,6 +188,12 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } For databases, allows new schemas to be created within the database. + + For tablespaces, allows tables to be created within the tablespace, + and allows databases and schemas to be created that have the tablespace + as their default tablespace. (Note that revoking this privilege + will not alter the behavior of existing databases and schemas.) + For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and @@ -223,7 +233,7 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } of privilege that is applicable to procedural languages. - For schemas, allows access to objects contained in the specified + For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to look up objects within the schema. @@ -385,7 +395,7 @@ GRANT ALL PRIVILEGES ON kinds TO manuel; Compatibility - + According to the SQL standard, the PRIVILEGES key word in ALL PRIVILEGES is required. The @@ -412,7 +422,7 @@ GRANT privileges The RULE privilege, and privileges on - databases, schemas, languages, and sequences are + databases, tablespaces, schemas, languages, and sequences are PostgreSQL extensions. diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index dee5a60e68c..17ce7a2f391 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1,5 +1,5 @@ @@ -71,7 +71,7 @@ PostgreSQL documentation - + @@ -208,7 +208,7 @@ PostgreSQL documentation - + @@ -287,7 +287,7 @@ PostgreSQL documentation - + @@ -343,7 +343,7 @@ PostgreSQL documentation - + @@ -370,7 +370,7 @@ PostgreSQL documentation Connect to the database as the user username instead of the default. (You must have permission to do so, of course.) - + @@ -491,7 +491,7 @@ PostgreSQL documentation not belong to any option it will be interpreted as the database name (or the user name, if the database name is also given). Not all these options are required, defaults do apply. If you omit the host - name, psql will connect via a Unix domain socket + name, psql will connect via a Unix domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have unix domain sockets. The default port number is compile-time determined. Since the database server uses the same default, you will not have @@ -565,9 +565,9 @@ testdb=> - The format of a psql command is the backslash, + The format of a psql command is the backslash, followed immediately by a command verb, then any arguments. The arguments - are separated from the command verb and each other by any number of + are separated from the command verb and each other by any number of whitespace characters. @@ -629,10 +629,10 @@ testdb=> \a - If the current table output format is unaligned, it is switched to aligned. - If it is not unaligned, it is set to unaligned. This command is - kept for backwards compatibility. See \pset for a - general solution. + If the current table output format is unaligned, it is switched to aligned. + If it is not unaligned, it is set to unaligned. This command is + kept for backwards compatibility. See \pset for a + general solution. @@ -641,16 +641,16 @@ testdb=> \cd [ directory ] - Changes the current working directory to - directory. Without argument, changes - to the current user's home directory. + Changes the current working directory to + directory. Without argument, changes + to the current user's home directory. - - - To print your current working directory, use \!pwd. - - + + + To print your current working directory, use \!pwd. + + @@ -673,43 +673,43 @@ testdb=> \connect (or \c) [ dbname [ username ] ] - Establishes a connection to a new database and/or under a user - name. The previous connection is closed. If dbname is - - the current database name is assumed. - + Establishes a connection to a new database and/or under a user + name. The previous connection is closed. If dbname is - + the current database name is assumed. + - - If username is - omitted the current user name is assumed. + + If username is + omitted the current user name is assumed. - - As a special rule, \connect without any - arguments will connect to the default database as the default - user (as you would have gotten by starting - psql without any arguments). - + + As a special rule, \connect without any + arguments will connect to the default database as the default + user (as you would have gotten by starting + psql without any arguments). + - - If the connection attempt failed (wrong user name, access - denied, etc.), the previous connection will be kept if and only - if psql is in interactive mode. When - executing a non-interactive script, processing will immediately - stop with an error. This distinction was chosen as a user - convenience against typos on the one hand, and a safety - mechanism that scripts are not accidentally acting on the wrong - database on the other hand. - + + If the connection attempt failed (wrong user name, access + denied, etc.), the previous connection will be kept if and only + if psql is in interactive mode. When + executing a non-interactive script, processing will immediately + stop with an error. This distinction was chosen as a user + convenience against typos on the one hand, and a safety + mechanism that scripts are not accidentally acting on the wrong + database on the other hand. + \copy table - [ ( column_list ) ] + [ ( column_list ) ] { from | to } - { filename | stdin | stdout | pstdin | pstdout } - [ with ] - [ oids ] + { filename | stdin | stdout | pstdin | pstdout } + [ with ] + [ oids ] [ delimiter [ as ] 'character' ] [ null [ as ] 'string' ] [ csv [ quote [ as ] 'character' ] @@ -729,37 +729,37 @@ testdb=> This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required. - - - - The syntax of the command is similar to that of the - SQL command. Note that, because of this, - special parsing rules apply to the \copy - command. In particular, the variable substitution rules and - backslash escapes do not apply. - - - - \copy table from stdin | stdout - reads/writes based on the command input and output respectively. - All rows are read from the same source that issued the command, - continuing until \. is read or the stream - reaches EOF. Output is sent to the same place as - command output. To read/write from - psql's standard input or output, use - pstdin or pstdout. This option is useful - for populating tables in-line within a SQL script file. - + + + + The syntax of the command is similar to that of the + SQL command. Note that, because of this, + special parsing rules apply to the \copy + command. In particular, the variable substitution rules and + backslash escapes do not apply. + + + + \copy table from stdin | stdout + reads/writes based on the command input and output respectively. + All rows are read from the same source that issued the command, + continuing until \. is read or the stream + reaches EOF. Output is sent to the same place as + command output. To read/write from + psql's standard input or output, use + pstdin or pstdout. This option is useful + for populating tables in-line within a SQL script file. + - This operation is not as efficient as the SQL - COPY command because all data must pass - through the client/server connection. For large - amounts of data the SQL command may be preferable. + This operation is not as efficient as the SQL + COPY command because all data must pass + through the client/server connection. For large + amounts of data the SQL command may be preferable. @@ -782,32 +782,32 @@ testdb=> - For each relation (table, view, index, or sequence) matching the - pattern, show all - columns, their types, and any special - attributes such as NOT NULL or defaults, if - any. Associated indexes, constraints, rules, and triggers are - also shown, as is the view definition if the relation is a view. - (Matching the pattern is defined below.) - + For each relation (table, view, index, or sequence) matching the + pattern, show all + columns, their types, and any special + attributes such as NOT NULL or defaults, if + any. Associated indexes, constraints, rules, and triggers are + also shown, as is the view definition if the relation is a view. + (Matching the pattern is defined below.) + - - The command form \d+ is identical, except that - more information is displayed: any comments associated with the - columns of the table are shown, as is the presence of OIDs in the - table. - + + The command form \d+ is identical, except that + more information is displayed: any comments associated with the + columns of the table are shown, as is the presence of OIDs in the + table. + - - - If \d is used without a - pattern argument, it is - equivalent to \dtvs which will show a list of - all tables, views, and sequences. This is purely a convenience - measure. - - - + + + If \d is used without a + pattern argument, it is + equivalent to \dtvs which will show a list of + all tables, views, and sequences. This is purely a convenience + measure. + + + @@ -824,14 +824,27 @@ testdb=> + + \db [ pattern ] + + + + Lists all available tablespaces. If pattern + is specified, only tablespaces whose names match the pattern are shown. + + + + + \dc [ pattern ] Lists all available conversions between character-set encodings. - If pattern + If pattern is specified, only conversions whose names match the pattern are - listed. + listed. @@ -853,8 +866,8 @@ testdb=> Shows the descriptions of objects matching the pattern, or of all visible objects if - no argument is given. But in either case, only objects that have - a description are listed. + no argument is given. But in either case, only objects that have + a description are listed. (Object covers aggregates, functions, operators, types, relations (tables, views, indexes, sequences, large objects), rules, and triggers.) For example: @@ -899,7 +912,7 @@ testdb=> return types. If pattern is specified, only functions whose names match the pattern are shown. - If the form \df+ is used, additional information about + If the form \df+ is used, additional information about each function, including language and description, is shown. @@ -912,8 +925,8 @@ testdb=> To reduce clutter, \df does not show data type I/O - functions. This is implemented by ignoring functions that accept - or return type cstring. + functions. This is implemented by ignoring functions that accept + or return type cstring. @@ -936,36 +949,36 @@ testdb=> \distvS [ pattern ] - - - This is not the actual command name: the letters - i, s, t, - v, S stand for index, - sequence, table, view, and system table, respectively. You can - specify any or all of these letters, in any order, to obtain a - listing of all the matching objects. The letter S restricts the - listing to system objects; without S, only - non-system objects are shown. If + is appended - to the command name, each object is listed with its associated - description, if any. - + + + This is not the actual command name: the letters + i, s, t, + v, S stand for index, + sequence, table, view, and system table, respectively. You can + specify any or all of these letters, in any order, to obtain a + listing of all the matching objects. The letter S restricts the + listing to system objects; without S, only + non-system objects are shown. If + is appended + to the command name, each object is listed with its associated + description, if any. + - - If pattern is - specified, only objects whose names match the pattern are listed. - - + + If pattern is + specified, only objects whose names match the pattern are listed. + + \dl - - - This is an alias for \lo_list, which shows a - list of large objects. - - + + + This is an alias for \lo_list, which shows a + list of large objects. + + @@ -988,8 +1001,8 @@ testdb=> Lists available operators with their operand and return types. - If pattern is - specified, only operators whose names match the pattern are listed. + If pattern is + specified, only operators whose names match the pattern are listed. @@ -997,21 +1010,21 @@ testdb=> \dp [ pattern ] - - + + Produces a list of all available tables, views and sequences with their associated access privileges. - If pattern is - specified, only tables, views and sequences whose names match the pattern are listed. - + If pattern is + specified, only tables, views and sequences whose names match the pattern are listed. + - - The commands and - - are used to set access privileges. See - for more information. - - + + The commands and + + are used to set access privileges. See + for more information. + + @@ -1076,7 +1089,7 @@ testdb=> \echo text [ ... ] - + Prints the arguments to the standard output, separated by one space and followed by a newline. This can be useful to intersperse information in the output of scripts. For example: @@ -1086,16 +1099,16 @@ Tue Oct 26 21:40:57 CEST 1999 If the first argument is an unquoted -n the the trailing newline is not written. - + - - - If you use the \o command to redirect your - query output you may wish to use \qecho - instead of this command. - - - + + + If you use the \o command to redirect your + query output you may wish to use \qecho + instead of this command. + + + @@ -1155,13 +1168,13 @@ Tue Oct 26 21:40:57 CEST 1999 SQL commands is shown. - - - To simplify typing, commands that consists of several words do - not have to be quoted. Thus it is fine to type \help - alter table. - - + + + To simplify typing, commands that consists of several words do + not have to be quoted. Thus it is fine to type \help + alter table. + + @@ -1170,11 +1183,11 @@ Tue Oct 26 21:40:57 CEST 1999 \H - Turns on HTML query output format. If the - HTML format is already on, it is switched - back to the default aligned text format. This command is for - compatibility and convenience, but see \pset - about setting other output options. + Turns on HTML query output format. If the + HTML format is already on, it is switched + back to the default aligned text format. This command is for + compatibility and convenience, but see \pset + about setting other output options. @@ -1188,13 +1201,13 @@ Tue Oct 26 21:40:57 CEST 1999 class="parameter">filename and executes it as though it had been typed on the keyboard. - - - If you want to see the lines on the screen as they are read you - must set the variable ECHO to - all. - - + + + If you want to see the lines on the screen as they are read you + must set the variable ECHO to + all. + + @@ -1215,83 +1228,83 @@ Tue Oct 26 21:40:57 CEST 1999 \lo_export loid filename - - - Reads the large object with OID loid from the database and - writes it to filename. Note that this is - subtly different from the server function - lo_export, which acts with the permissions - of the user that the database server runs as and on the server's - file system. - - - - Use \lo_list to find out the large object's - OID. - - - + + + Reads the large object with OID loid from the database and + writes it to filename. Note that this is + subtly different from the server function + lo_export, which acts with the permissions + of the user that the database server runs as and on the server's + file system. + + + + Use \lo_list to find out the large object's + OID. + + + \lo_import filename [ comment ] - - - Stores the file into a PostgreSQL - large object. Optionally, it associates the given - comment with the object. Example: + + + Stores the file into a PostgreSQL + large object. Optionally, it associates the given + comment with the object. Example: foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me' lo_import 152801 - The response indicates that the large object received object ID - 152801 which one ought to remember if one wants to access the - object ever again. For that reason it is recommended to always - associate a human-readable comment with every object. Those can - then be seen with the \lo_list command. - + The response indicates that the large object received object ID + 152801 which one ought to remember if one wants to access the + object ever again. For that reason it is recommended to always + associate a human-readable comment with every object. Those can + then be seen with the \lo_list command. + - - Note that this command is subtly different from the server-side - lo_import because it acts as the local user - on the local file system, rather than the server's user and file - system. - - + + Note that this command is subtly different from the server-side + lo_import because it acts as the local user + on the local file system, rather than the server's user and file + system. + + \lo_list - - - Shows a list of all PostgreSQL - large objects currently stored in the database, - along with any comments provided for them. - - + + + Shows a list of all PostgreSQL + large objects currently stored in the database, + along with any comments provided for them. + + \lo_unlink loid - - - Deletes the large object with OID - loid from the - database. - + + + Deletes the large object with OID + loid from the + database. + - - - Use \lo_list to find out the large object's - OID. - - - + + + Use \lo_list to find out the large object's + OID. + + + @@ -1307,20 +1320,20 @@ lo_import 152801 specified, the query output will be reset to the standard output. - - Query results includes all tables, command - responses, and notices obtained from the database server, as - well as output of various backslash commands that query the - database (such as \d), but not error - messages. - + + Query results includes all tables, command + responses, and notices obtained from the database server, as + well as output of various backslash commands that query the + database (such as \d), but not error + messages. + - - - To intersperse text output in between query results, use - \qecho. - - + + + To intersperse text output in between query results, use + \qecho. + + @@ -1338,210 +1351,210 @@ lo_import 152801 \pset parameter [ value ] - - - This command sets options affecting the output of query result - tables. parameter - describes which option is to be set. The semantics of - value depend - thereon. - - - - Adjustable printing options are: - - - format - - - Sets the output format to one of unaligned, - aligned, html, or - latex. Unique abbreviations are allowed. - (That would mean one letter is enough.) - - - - Unaligned writes all columns of a row on a - line, separated by the currently active field separator. This - is intended to create output that might be intended to be read - in by other programs (tab-separated, comma-separated). - Aligned mode is the standard, human-readable, - nicely formatted text output that is default. The - HTML and - LaTeX modes put out tables that are intended to - be included in documents using the respective mark-up - language. They are not complete documents! (This might not be - so dramatic in HTML, but in LaTeX you must - have a complete document wrapper.) - - + + + This command sets options affecting the output of query result + tables. parameter + describes which option is to be set. The semantics of + value depend + thereon. + + + + Adjustable printing options are: + + + format + + + Sets the output format to one of unaligned, + aligned, html, or + latex. Unique abbreviations are allowed. + (That would mean one letter is enough.) + + + + Unaligned writes all columns of a row on a + line, separated by the currently active field separator. This + is intended to create output that might be intended to be read + in by other programs (tab-separated, comma-separated). + Aligned mode is the standard, human-readable, + nicely formatted text output that is default. The + HTML and + LaTeX modes put out tables that are intended to + be included in documents using the respective mark-up + language. They are not complete documents! (This might not be + so dramatic in HTML, but in LaTeX you must + have a complete document wrapper.) + + - - border - - - The second argument must be a number. In general, the higher - the number the more borders and lines the tables will have, - but this depends on the particular format. In - HTML mode, this will translate directly - into the border=... attribute, in the - others only values 0 (no border), 1 (internal dividing lines), - and 2 (table frame) make sense. - - - - - - expanded (or x) - - - Toggles between regular and expanded format. When expanded - format is enabled, all output has two columns with the column - name on the left and the data on the right. This mode is - useful if the data wouldn't fit on the screen in the normal - horizontal mode. - - - - Expanded mode is supported by all four output formats. - - - - - - null - - - The second argument is a string that should be printed - whenever a column is null. The default is not to print - anything, which can easily be mistaken for, say, an empty - string. Thus, one might choose to write \pset null - '(null)'. - - - - - - fieldsep - - - Specifies the field separator to be used in unaligned output - mode. That way one can create, for example, tab- or - comma-separated output, which other programs might prefer. To - set a tab as field separator, type \pset fieldsep - '\t'. The default field separator is - '|' (a vertical bar). - - - - - - footer - - - Toggles the display of the default footer (x - rows). - - - - - - recordsep - - + + border + + + The second argument must be a number. In general, the higher + the number the more borders and lines the tables will have, + but this depends on the particular format. In + HTML mode, this will translate directly + into the border=... attribute, in the + others only values 0 (no border), 1 (internal dividing lines), + and 2 (table frame) make sense. + + + + + + expanded (or x) + + + Toggles between regular and expanded format. When expanded + format is enabled, all output has two columns with the column + name on the left and the data on the right. This mode is + useful if the data wouldn't fit on the screen in the normal + horizontal mode. + + + + Expanded mode is supported by all four output formats. + + + + + + null + + + The second argument is a string that should be printed + whenever a column is null. The default is not to print + anything, which can easily be mistaken for, say, an empty + string. Thus, one might choose to write \pset null + '(null)'. + + + + + + fieldsep + + + Specifies the field separator to be used in unaligned output + mode. That way one can create, for example, tab- or + comma-separated output, which other programs might prefer. To + set a tab as field separator, type \pset fieldsep + '\t'. The default field separator is + '|' (a vertical bar). + + + + + + footer + + + Toggles the display of the default footer (x + rows). + + + + + + recordsep + + Specifies the record (line) separator to use in unaligned output mode. The default is a newline character. - - - - - - tuples_only (or t) - - - Toggles between tuples only and full display. Full display may - show extra information such as column headers, titles, and - various footers. In tuples only mode, only actual table data - is shown. - - - - - - title [ text ] - - - Sets the table title for any subsequently printed tables. This - can be used to give your output descriptive tags. If no - argument is given, the title is unset. - - - - - - tableattr (or T) [ text ] - - - Allows you to specify any attributes to be placed inside the - HTML table tag. This - could for example be cellpadding or - bgcolor. Note that you probably don't want - to specify border here, as that is already - taken care of by \pset border. - - - - - - - pager - - - Controls use of a pager for query and psql - help output. If the environment variable PAGER - is set, the output is piped to the specified program. - Otherwise a platform-dependent default (such as - more) is used. - - - - When the pager is off, the pager is not used. When the pager - is on, the pager is used only when appropriate, i.e. the - output is to a terminal and will not fit on the screen. - (psql does not do a perfect job of estimating - when to use the pager.) \pset pager turns the - pager on and off. Pager can also be set to always, - which causes the pager to be always used. - - - - - - - - Illustrations on how these different formats look can be seen in - the section. - - - - - There are various shortcut commands for \pset. See - \a, \C, \H, - \t, \T, and \x. - - - - - - It is an error to call \pset without - arguments. In the future this call might show the current status - of all printing options. - - - - + + + + + + tuples_only (or t) + + + Toggles between tuples only and full display. Full display may + show extra information such as column headers, titles, and + various footers. In tuples only mode, only actual table data + is shown. + + + + + + title [ text ] + + + Sets the table title for any subsequently printed tables. This + can be used to give your output descriptive tags. If no + argument is given, the title is unset. + + + + + + tableattr (or T) [ text ] + + + Allows you to specify any attributes to be placed inside the + HTML table tag. This + could for example be cellpadding or + bgcolor. Note that you probably don't want + to specify border here, as that is already + taken care of by \pset border. + + + + + + + pager + + + Controls use of a pager for query and psql + help output. If the environment variable PAGER + is set, the output is piped to the specified program. + Otherwise a platform-dependent default (such as + more) is used. + + + + When the pager is off, the pager is not used. When the pager + is on, the pager is used only when appropriate, i.e. the + output is to a terminal and will not fit on the screen. + (psql does not do a perfect job of estimating + when to use the pager.) \pset pager turns the + pager on and off. Pager can also be set to always, + which causes the pager to be always used. + + + + + + + + Illustrations on how these different formats look can be seen in + the section. + + + + + There are various shortcut commands for \pset. See + \a, \C, \H, + \t, \T, and \x. + + + + + + It is an error to call \pset without + arguments. In the future this call might show the current status + of all printing options. + + + + @@ -1559,9 +1572,9 @@ lo_import 152801 \qecho text [ ... ] - This command is identical to \echo except - that all output will be written to the query output channel, as - set by \o. + This command is identical to \echo except + that all output will be written to the query output channel, as + set by \o. @@ -1589,14 +1602,14 @@ lo_import 152801 GNU history library. - - - In the current version, it is no longer necessary to save the - command history, since that will be done automatically on - program termination. The history is also loaded automatically - every time psql starts up. - - + + + In the current version, it is no longer necessary to save the + command history, since that will be done automatically on + program termination. The history is also loaded automatically + every time psql starts up. + + @@ -1604,37 +1617,37 @@ lo_import 152801 \set [ name [ value [ ... ] ] ] - - - Sets the internal variable name to value or, if more than one value - is given, to the concatenation of all of them. If no second - argument is given, the variable is just set with no value. To - unset a variable, use the \unset command. - - - - Valid variable names can contain characters, digits, and - underscores. See the section below for details. - Variable names are case-sensitive. - - - - Although you are welcome to set any variable to anything you - want, psql treats several variables - as special. They are documented in the section about variables. - - - - - This command is totally separate from the SQL - command . - - - + + + Sets the internal variable name to value or, if more than one value + is given, to the concatenation of all of them. If no second + argument is given, the variable is just set with no value. To + unset a variable, use the \unset command. + + + + Valid variable names can contain characters, digits, and + underscores. See the section below for details. + Variable names are case-sensitive. + + + + Although you are welcome to set any variable to anything you + want, psql treats several variables + as special. They are documented in the section about variables. + + + + + This command is totally separate from the SQL + command . + + + @@ -1691,7 +1704,7 @@ lo_import 152801 Toggles extended table formatting mode. As such it is equivalent to - \pset expanded. + \pset expanded. @@ -1703,20 +1716,20 @@ lo_import 152801 Produces a list of all available tables, views and sequences with their associated access privileges. - If a pattern is - specified, only tables,views and sequences whose names match the pattern are listed. - + If a pattern is + specified, only tables,views and sequences whose names match the pattern are listed. + - - The commands and - - are used to set access privileges. See - for more information. - + + The commands and + + are used to set access privileges. See + for more information. + - - This is an alias for \dp (display - privileges). + + This is an alias for \dp (display + privileges). @@ -1764,7 +1777,7 @@ lo_import 152801 A pattern that contains an (unquoted) dot is interpreted as a schema name pattern followed by an object name pattern. For example, \dt foo*.bar* displays all tables in schemas whose name - starts with foo and whose table name + starts with foo and whose table name starts with bar. If no dot appears, then the pattern matches only objects that are visible in the current schema search path. @@ -1847,96 +1860,96 @@ bar psql AUTOCOMMIT - - - When on (the default), each SQL command is automatically - committed upon successful completion. To postpone commit in this - mode, you must enter a BEGIN or START - TRANSACTION SQL command. When off or unset, SQL - commands are not committed until you explicitly issue - COMMIT or END. The autocommit-off - mode works by issuing an implicit BEGIN for you, just - before any command that is not already in a transaction block and - is not itself a BEGIN or other transaction-control - command. - - - - - In autocommit-off mode, you must explicitly abandon any failed - transaction by entering ABORT or ROLLBACK. - Also keep in mind that if you exit the session - without committing, your work will be lost. - - - - - - The autocommit-on mode is PostgreSQL's traditional - behavior, but autocommit-off is closer to the SQL spec. If you - prefer autocommit-off, you may wish to set it in the system-wide - psqlrc or your + + + When on (the default), each SQL command is automatically + committed upon successful completion. To postpone commit in this + mode, you must enter a BEGIN or START + TRANSACTION SQL command. When off or unset, SQL + commands are not committed until you explicitly issue + COMMIT or END. The autocommit-off + mode works by issuing an implicit BEGIN for you, just + before any command that is not already in a transaction block and + is not itself a BEGIN or other transaction-control + command. + + + + + In autocommit-off mode, you must explicitly abandon any failed + transaction by entering ABORT or ROLLBACK. + Also keep in mind that if you exit the session + without committing, your work will be lost. + + + + + + The autocommit-on mode is PostgreSQL's traditional + behavior, but autocommit-off is closer to the SQL spec. If you + prefer autocommit-off, you may wish to set it in the system-wide + psqlrc or your .psqlrc file. - - - + + + DBNAME - - + + The name of the database you are currently connected to. This is set every time you connect to a database (including program start-up), but can be unset. - - + + ECHO - - - If set to all, all lines - entered or from a script are written to the standard output - before they are parsed or executed. To select this behavior on program - start-up, use the switch . If set to - queries, - psql merely prints all queries as - they are sent to the server. The switch for this is - . - - + + + If set to all, all lines + entered or from a script are written to the standard output + before they are parsed or executed. To select this behavior on program + start-up, use the switch . If set to + queries, + psql merely prints all queries as + they are sent to the server. The switch for this is + . + + ECHO_HIDDEN - - - When this variable is set and a backslash command queries the - database, the query is first shown. This way you can study the - PostgreSQL internals and provide - similar functionality in your own programs. (To select this behavior - on program start-up, use the switch .) If you set - the variable to the value noexec, the queries are - just shown but are not actually sent to the server and executed. - - + + + When this variable is set and a backslash command queries the + database, the query is first shown. This way you can study the + PostgreSQL internals and provide + similar functionality in your own programs. (To select this behavior + on program start-up, use the switch .) If you set + the variable to the value noexec, the queries are + just shown but are not actually sent to the server and executed. + + ENCODING - - + + The current client character set encoding. - - + + HISTCONTROL - - + + If this variable is set to ignorespace, lines which begin with a space are not entered into the history list. If set to a value of ignoredups, lines @@ -1944,47 +1957,47 @@ bar ignoreboth combines the two options. If unset, or if set to any other value than those above, all lines read in interactive mode are saved on the history list. - + This feature was shamelessly plagiarized from Bash. - + HISTSIZE - - + + The number of commands to store in the command history. The default value is 500. - + This feature was shamelessly plagiarized from Bash. - + HOST - - + + The database server host you are currently connected to. This is set every time you connect to a database (including program start-up), but can be unset. - - + + IGNOREEOF - - + + If unset, sending an EOF character (usually ControlD) to an interactive session of psql @@ -1992,125 +2005,125 @@ bar that many EOF characters are ignored before the application terminates. If the variable is set but has no numeric value, the default is 10. - + This feature was shamelessly plagiarized from Bash. - + LASTOID - - + + The value of the last affected OID, as returned from an INSERT or lo_insert command. This variable is only guaranteed to be valid until after the result of the next SQL command has been displayed. - - + + ON_ERROR_STOP - - - By default, if non-interactive scripts encounter an error, such - as a malformed SQL command or internal - meta-command, processing continues. This has been the - traditional behavior of psql but it - is sometimes not desirable. If this variable is set, script - processing will immediately terminate. If the script was called - from another script it will terminate in the same fashion. If - the outermost script was not called from an interactive - psql session but rather using the - option, psql will - return error code 3, to distinguish this case from fatal error - conditions (error code 1). - - + + + By default, if non-interactive scripts encounter an error, such + as a malformed SQL command or internal + meta-command, processing continues. This has been the + traditional behavior of psql but it + is sometimes not desirable. If this variable is set, script + processing will immediately terminate. If the script was called + from another script it will terminate in the same fashion. If + the outermost script was not called from an interactive + psql session but rather using the + option, psql will + return error code 3, to distinguish this case from fatal error + conditions (error code 1). + + PORT - - + + The database server port to which you are currently connected. This is set every time you connect to a database (including program start-up), but can be unset. - - + + PROMPT1 PROMPT2 PROMPT3 - - - These specify what the prompts psql - issues should look like. See below. - - + + + These specify what the prompts psql + issues should look like. See below. + + QUIET - - - This variable is equivalent to the command line option - . It is probably not too useful in - interactive mode. - - + + + This variable is equivalent to the command line option + . It is probably not too useful in + interactive mode. + + SINGLELINE - - - This variable is equivalent to the command line option - . - - + + + This variable is equivalent to the command line option + . + + SINGLESTEP - - - This variable is equivalent to the command line option - . - - + + + This variable is equivalent to the command line option + . + + USER - - + + The database user you are currently connected as. This is set every time you connect to a database (including program start-up), but can be unset. - - + + VERBOSITY - - - This variable can be set to the values default, - verbose, or terse to control the verbosity - of error reports. - - + + + This variable can be set to the values default, + verbose, or terse to control the verbosity + of error reports. + + @@ -2211,7 +2224,7 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' %M - + The full host name (with domain name) of the database server, or [local] if the connection is over a Unix @@ -2225,7 +2238,7 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' %m - + The host name of the database server, truncated at the first dot, or [local] if the connection is @@ -2236,12 +2249,12 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' %> - The port number at which the database server is listening. + The port number at which the database server is listening. %n - + The database session user name. (The expansion of this value might change during a database session as the result @@ -2253,18 +2266,18 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' %/ - The name of the current database. + The name of the current database. %~ - Like %/, but the output is ~ + Like %/, but the output is ~ (tilde) if the database is your default database. %# - + If the session user is a database superuser, then a #, otherwise a >. @@ -2277,75 +2290,75 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' %R - - - In prompt 1 normally =, but ^ if - in single-line mode, and ! if the session is - disconnected from the database (which can happen if - \connect fails). In prompt 2 the sequence is - replaced by -, *, a single quote, - or a double quote, depending on whether - psql expects more input because the - command wasn't terminated yet, because you are inside a - /* ... */ comment, or because you are inside - a quote. In prompt 3 the sequence doesn't produce anything. - - + + + In prompt 1 normally =, but ^ if + in single-line mode, and ! if the session is + disconnected from the database (which can happen if + \connect fails). In prompt 2 the sequence is + replaced by -, *, a single quote, + or a double quote, depending on whether + psql expects more input because the + command wasn't terminated yet, because you are inside a + /* ... */ comment, or because you are inside + a quote. In prompt 3 the sequence doesn't produce anything. + + %x - - - Transaction status: an empty string when not in a transaction - block, or * when in a transaction block, or - ! when in a failed transaction block, or ? - when the transaction state is indeterminate (for example, because - there is no connection). - - + + + Transaction status: an empty string when not in a transaction + block, or * when in a transaction block, or + ! when in a failed transaction block, or ? + when the transaction state is indeterminate (for example, because + there is no connection). + + %digits - - - The character with the indicated numeric code is substituted. - If digits starts - with 0x the rest of the characters are - interpreted as hexadecimal; otherwise if the first digit is - 0 the digits are interpreted as octal; - otherwise the digits are read as a decimal number. - - + + + The character with the indicated numeric code is substituted. + If digits starts + with 0x the rest of the characters are + interpreted as hexadecimal; otherwise if the first digit is + 0 the digits are interpreted as octal; + otherwise the digits are read as a decimal number. + + %:name: - - - The value of the psql variable - name. See the - section for details. - - + + + The value of the psql variable + name. See the + section for details. + + %`command` - - - The output of command, similar to ordinary - back-tick substitution. - - + + + The output of command, similar to ordinary + back-tick substitution. + + %[ ... %] - + Prompts may contain terminal control characters which, for example, change the color, background, or style of the prompt @@ -2360,8 +2373,8 @@ testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%#%] ' results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable - terminals. - + terminals. + @@ -2505,10 +2518,10 @@ $endif Before starting up, psql attempts to read and execute commands from the the system-wide - psqlrc file and the + psqlrc file and the $HOME/.psqlrc file in the user's home - directory. See PREFIX/share/psqlrc.sample - for information on setting up the system-wide file. It could be used + directory. See PREFIX/share/psqlrc.sample + for information on setting up the system-wide file. It could be used to set up the client or the server to taste (using the \set and SET commands). diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index 096a813b7f0..c6cd587ade7 100644 --- a/doc/src/sgml/ref/revoke.sgml +++ b/doc/src/sgml/ref/revoke.sgml @@ -1,5 +1,5 @@ @@ -33,6 +33,12 @@ REVOKE [ GRANT OPTION FOR ] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] +REVOKE [ GRANT OPTION FOR ] + { CREATE | ALL [ PRIVILEGES ] } + ON TABLESPACE tablespacename [, ...] + FROM { username | GROUP groupname | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] + REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ([type, ...]) [, ...] diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 03df5920566..100c69bde93 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,5 +1,5 @@ @@ -76,6 +76,7 @@ PostgreSQL Reference Manual &createSequence; &createTable; &createTableAs; + &createTableSpace; &createTrigger; &createType; &createUser; @@ -98,6 +99,7 @@ PostgreSQL Reference Manual &dropSchema; &dropSequence; &dropTable; + &dropTableSpace; &dropTrigger; &dropType; &dropUser; -- cgit v1.2.3