From acfce502ba1f79ff48c9376a4c113ee06b2674b8 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 3 Jun 2007 17:08:34 +0000 Subject: Create a GUC parameter temp_tablespaces that allows selection of the tablespace(s) in which to store temp tables and temporary files. This is a list to allow spreading the load across multiple tablespaces (a random list element is chosen each time a temp object is to be created). Temp files are not stored in per-database pgsql_tmp/ directories anymore, but per-tablespace directories. Jaime Casanova and Albert Cervera, with review by Bernd Helmle and Tom Lane. --- doc/src/sgml/config.sgml | 83 ++++++++++++++++++++++++++--------- doc/src/sgml/manage-ag.sgml | 24 +++++++--- doc/src/sgml/ref/create_index.sgml | 8 ++-- doc/src/sgml/ref/create_table.sgml | 12 +++-- doc/src/sgml/ref/create_table_as.sgml | 7 ++- doc/src/sgml/ref/drop_tablespace.sgml | 7 ++- doc/src/sgml/ref/grant.sgml | 10 ++--- doc/src/sgml/storage.sgml | 13 +++++- 8 files changed, 114 insertions(+), 50 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index e614cc001f0..e1948b8bde2 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ - + Server Configuration @@ -281,7 +281,7 @@ SET ENABLE_SEQSCAN TO OFF; If you wish, you can specify the configuration file names and locations individually using the parameters config_file, hba_file and/or ident_file. - config_file can only be specified on the + config_file can only be specified on the postgres command line, but the others can be set within the main configuration file. If all three parameters plus data_directory are explicitly set, then it is not necessary @@ -311,7 +311,7 @@ SET ENABLE_SEQSCAN TO OFF; Specifies the TCP/IP address(es) on which the server is - to listen for connections from client applications. + to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. @@ -362,7 +362,7 @@ SET ENABLE_SEQSCAN TO OFF; - + superuser_reserved_connections @@ -461,7 +461,7 @@ SET ENABLE_SEQSCAN TO OFF; - + bonjour_name (string) @@ -478,7 +478,7 @@ SET ENABLE_SEQSCAN TO OFF; - + tcp_keepalives_idle (integer) @@ -494,7 +494,7 @@ SET ENABLE_SEQSCAN TO OFF; - + tcp_keepalives_interval (integer) @@ -510,7 +510,7 @@ SET ENABLE_SEQSCAN TO OFF; - + tcp_keepalives_count (integer) @@ -519,19 +519,19 @@ SET ENABLE_SEQSCAN TO OFF; On systems that support the TCP_KEEPCNT socket option, specifies how - many keepalives can be lost before the connection is considered dead. + many keepalives can be lost before the connection is considered dead. A value of zero uses the system default. If TCP_KEEPCNT is not supported, this parameter must be zero. This parameter is ignored for connections made via a Unix-domain socket. - + Security and Authentication - + authentication_timeout (integer) @@ -826,7 +826,7 @@ SET ENABLE_SEQSCAN TO OFF; - + maintenance_work_mem (integer) @@ -896,7 +896,7 @@ SET ENABLE_SEQSCAN TO OFF; is not in the map cannot be re-used; instead PostgreSQL will request more disk space from the operating system when it needs to store new data. - The last few lines displayed by a database-wide VACUUM VERBOSE + The last few lines displayed by a database-wide VACUUM VERBOSE command can help in determining if the current settings are adequate. A NOTICE message is also printed during such an operation if the current settings are too low. @@ -944,7 +944,7 @@ SET ENABLE_SEQSCAN TO OFF; - + @@ -3475,7 +3475,14 @@ SELECT * FROM parent WHERE key = 2400; to specify using the default tablespace of the current database. If the value does not match the name of any existing tablespace, PostgreSQL will automatically use the default - tablespace of the current database. + tablespace of the current database. If a nondefault tablespace + is specified, the user must have CREATE privilege + for it, or creation attempts will fail. + + + + This variable is not used for temporary tables; for them, + is consulted instead. @@ -3485,6 +3492,42 @@ SELECT * FROM parent WHERE key = 2400; + + temp_tablespaces (string) + + temp_tablespaces configuration parameter + + tablespacetemporary + + + This variable specifies tablespace(s) in which to create temporary + objects (temp tables and indexes on temp tables) when a + CREATE command does not explicitly specify a tablespace. + Temporary files for purposes such as sorting large data sets + are also created in these tablespace(s). + + + + The value is a list of names of tablespaces. When there is more than + one name in the list, PostgreSQL chooses a random + member of the list each time a temporary object is to be created. + + + + If any element of the list is an empty string or does not match the + name of any existing tablespace, PostgreSQL will + automatically use the default tablespace of the current database + instead. If a nondefault tablespace + is specified, the user must have CREATE privilege + for it, or creation attempts will fail. + + + + See also . + + + + check_function_bodies (boolean) @@ -3605,7 +3648,7 @@ SELECT * FROM parent WHERE key = 2400; - + xmlbinary (string) @@ -3632,7 +3675,7 @@ SELECT * FROM parent WHERE key = 2400; - + xmloption (string) @@ -3664,7 +3707,7 @@ SET XML OPTION { DOCUMENT | CONTENT }; - + @@ -3945,7 +3988,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' - + local_preload_libraries (string) @@ -3994,7 +4037,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' Every PostgreSQL-supported library has a magic block that is checked to guarantee compatibility. - For this reason, non-PostgreSQL libraries cannot be + For this reason, non-PostgreSQL libraries cannot be loaded in this way. diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index 5e0fed86373..23308f68867 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -1,4 +1,4 @@ - + Managing Databases @@ -423,13 +423,23 @@ CREATE TABLE foo(i int); do not have an explicit one. + + There is also a parameter, which + determines the placement of temporary tables and indexes, as well as + temporary files that are used for purposes such as sorting large data + sets. This can be a list of tablespace names, rather than only one, + so that the load associated with temporary objects can be spread over + multiple tablespaces. A random member of the list is picked each time + a temporary object is to be created. + + The tablespace associated with a database is used to store the system - catalogs of that database, as well as any temporary files created by - server processes using that database. Furthermore, it is the default - tablespace selected for tables and indexes created within the database, - if no TABLESPACE clause is given (either explicitly or via - default_tablespace) when the objects are created. + catalogs of that database. Furthermore, it is the default tablespace + used for tables, indexes, and temporary files created within the database, + if no TABLESPACE clause is given and no other selection is + specified by default_tablespace or + temp_tablespaces (as appropriate). If a database is created without specifying a tablespace for it, it uses the same tablespace as the template database it is copied from. @@ -468,7 +478,7 @@ SELECT spcname FROM pg_tablespace; - PostgreSQL makes extensive use of symbolic links + PostgreSQL makes use of symbolic links to simplify the implementation of tablespaces. This means that tablespaces can be used only on systems that support symbolic links. diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index feebc4c164a..ed2b8e22e70 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -1,5 +1,5 @@ @@ -240,9 +240,9 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name The tablespace in which to create the index. If not specified, - is used, or the database's - default tablespace if default_tablespace is an empty - string. + is consulted, or + for indexes on temporary + tables. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 718ed1cf53c..064769cee0a 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,5 +1,5 @@ @@ -645,9 +645,8 @@ and table_constraint is: The tablespace is the name of the tablespace in which the new table is to be created. If not specified, - is used, or the database's - default tablespace if default_tablespace is an empty - string. + is consulted, or + if the table is temporary. @@ -660,9 +659,8 @@ and table_constraint is: associated with a UNIQUE or PRIMARY KEY constraint will be created. If not specified, - is used, or the database's - default tablespace if default_tablespace is an empty - string. + is consulted, or + if the table is temporary. diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml index 36348c8ad49..edc9ce5e6c5 100644 --- a/doc/src/sgml/ref/create_table_as.sgml +++ b/doc/src/sgml/ref/create_table_as.sgml @@ -1,5 +1,5 @@ @@ -184,9 +184,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name The tablespace is the name of the tablespace in which the new table is to be created. If not specified, - is used, or the database's - default tablespace if default_tablespace is an empty - string. + is consulted, or + if the table is temporary. diff --git a/doc/src/sgml/ref/drop_tablespace.sgml b/doc/src/sgml/ref/drop_tablespace.sgml index bf570d851a0..31a4888141c 100644 --- a/doc/src/sgml/ref/drop_tablespace.sgml +++ b/doc/src/sgml/ref/drop_tablespace.sgml @@ -1,5 +1,5 @@ @@ -36,7 +36,10 @@ DROP TABLESPACE [ IF EXISTS ] tablespacename setting of any active session, the + DROP might fail due to temporary files residing in the + tablespace. diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index a4c3b0d4408..e8e93bf9ab3 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -1,5 +1,5 @@ @@ -211,10 +211,10 @@ GRANT role [, ...] TO - For tablespaces, allows tables and indexes to be created within the - tablespace, and allows databases to be created that have the tablespace - as their default tablespace. (Note that revoking this privilege - will not alter the placement of existing objects.) + For tablespaces, allows tables, indexes, and temporary files to be + created within the tablespace, and allows databases to be created that + have the tablespace as their default tablespace. (Note that revoking + this privilege will not alter the placement of existing objects.) diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml index 9c3cf7589da..a66aeb2584e 100644 --- a/doc/src/sgml/storage.sgml +++ b/doc/src/sgml/storage.sgml @@ -1,4 +1,4 @@ - + @@ -170,6 +170,17 @@ tablespace is not accessed through pg_tblspc, but corresponds to PGDATA/global. + +Temporary files (for operations such as sorting more data than can fit in +memory) are created within PGDATA/base/pgsql_tmp, +or within a pgsql_tmp subdirectory of a tablespace directory +if a tablespace other than pg_default is specified for them. +The name of a temporary file has the form +pgsql_tmpPPP.NNN, +where PPP is the PID of the owning backend and +NNN distinguishes different files of that backend. + + -- cgit v1.2.3