Skip to content

Commit acfce50

Browse files
tglsfdctglsfdc
committed
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.
1 parent 5d429f8 commit acfce50

26 files changed

+494
-173
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 63 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.124 2007/05/17 23:36:04 neilc Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.125 2007/06/03 17:05:29 tgl Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -281,7 +281,7 @@ SET ENABLE_SEQSCAN TO OFF;
281281
If you wish, you can specify the configuration file names and locations
282282
individually using the parameters <varname>config_file</>,
283283
<varname>hba_file</> and/or <varname>ident_file</>.
284-
<varname>config_file</> can only be specified on the
284+
<varname>config_file</> can only be specified on the
285285
<command>postgres</command> command line, but the others can be
286286
set within the main configuration file. If all three parameters plus
287287
<varname>data_directory</> are explicitly set, then it is not necessary
@@ -311,7 +311,7 @@ SET ENABLE_SEQSCAN TO OFF;
311311
<listitem>
312312
<para>
313313
Specifies the TCP/IP address(es) on which the server is
314-
to listen for connections from client applications.
314+
to listen for connections from client applications.
315315
The value takes the form of a comma-separated list of host names
316316
and/or numeric IP addresses. The special entry <literal>*</>
317317
corresponds to all available IP interfaces.
@@ -362,7 +362,7 @@ SET ENABLE_SEQSCAN TO OFF;
362362
</para>
363363
</listitem>
364364
</varlistentry>
365-
365+
366366
<varlistentry id="guc-superuser-reserved-connections"
367367
xreflabel="superuser_reserved_connections">
368368
<term><varname>superuser_reserved_connections</varname>
@@ -461,7 +461,7 @@ SET ENABLE_SEQSCAN TO OFF;
461461
</para>
462462
</listitem>
463463
</varlistentry>
464-
464+
465465
<varlistentry id="guc-bonjour-name" xreflabel="bonjour_name">
466466
<term><varname>bonjour_name</varname> (<type>string</type>)</term>
467467
<indexterm>
@@ -478,7 +478,7 @@ SET ENABLE_SEQSCAN TO OFF;
478478
</para>
479479
</listitem>
480480
</varlistentry>
481-
481+
482482
<varlistentry id="guc-tcp-keepalives-idle" xreflabel="tcp_keepalives_idle">
483483
<term><varname>tcp_keepalives_idle</varname> (<type>integer</type>)</term>
484484
<indexterm>
@@ -494,7 +494,7 @@ SET ENABLE_SEQSCAN TO OFF;
494494
</para>
495495
</listitem>
496496
</varlistentry>
497-
497+
498498
<varlistentry id="guc-tcp-keepalives-interval" xreflabel="tcp_keepalives_interval">
499499
<term><varname>tcp_keepalives_interval</varname> (<type>integer</type>)</term>
500500
<indexterm>
@@ -510,7 +510,7 @@ SET ENABLE_SEQSCAN TO OFF;
510510
</para>
511511
</listitem>
512512
</varlistentry>
513-
513+
514514
<varlistentry id="guc-tcp-keepalives-count" xreflabel="tcp_keepalives_count">
515515
<term><varname>tcp_keepalives_count</varname> (<type>integer</type>)</term>
516516
<indexterm>
@@ -519,19 +519,19 @@ SET ENABLE_SEQSCAN TO OFF;
519519
<listitem>
520520
<para>
521521
On systems that support the <symbol>TCP_KEEPCNT</symbol> socket option, specifies how
522-
many keepalives can be lost before the connection is considered dead.
522+
many keepalives can be lost before the connection is considered dead.
523523
A value of zero uses the system default. If <symbol>TCP_KEEPCNT</symbol> is not
524524
supported, this parameter must be zero. This parameter is ignored
525525
for connections made via a Unix-domain socket.
526526
</para>
527527
</listitem>
528528
</varlistentry>
529-
529+
530530
</variablelist>
531531
</sect2>
532532
<sect2 id="runtime-config-connection-security">
533533
<title>Security and Authentication</title>
534-
534+
535535
<variablelist>
536536
<varlistentry id="guc-authentication-timeout" xreflabel="authentication_timeout">
537537
<term><varname>authentication_timeout</varname> (<type>integer</type>)</term>
@@ -826,7 +826,7 @@ SET ENABLE_SEQSCAN TO OFF;
826826
</para>
827827
</listitem>
828828
</varlistentry>
829-
829+
830830
<varlistentry id="guc-maintenance-work-mem" xreflabel="maintenance_work_mem">
831831
<term><varname>maintenance_work_mem</varname> (<type>integer</type>)</term>
832832
<indexterm>
@@ -896,7 +896,7 @@ SET ENABLE_SEQSCAN TO OFF;
896896
is not in the map cannot be re-used; instead <productname>PostgreSQL</>
897897
will request more disk space from the operating system when it needs
898898
to store new data.
899-
The last few lines displayed by a database-wide <command>VACUUM VERBOSE</>
899+
The last few lines displayed by a database-wide <command>VACUUM VERBOSE</>
900900
command can help in determining if the current settings are adequate.
901901
A <literal>NOTICE</> message is also printed during such an operation
902902
if the current settings are too low.
@@ -944,7 +944,7 @@ SET ENABLE_SEQSCAN TO OFF;
944944
</para>
945945
</listitem>
946946
</varlistentry>
947-
947+
948948
</variablelist>
949949
</sect2>
950950
<sect2 id="runtime-config-resource-kernel">
@@ -3475,7 +3475,14 @@ SELECT * FROM parent WHERE key = 2400;
34753475
to specify using the default tablespace of the current database.
34763476
If the value does not match the name of any existing tablespace,
34773477
<productname>PostgreSQL</> will automatically use the default
3478-
tablespace of the current database.
3478+
tablespace of the current database. If a nondefault tablespace
3479+
is specified, the user must have <literal>CREATE</> privilege
3480+
for it, or creation attempts will fail.
3481+
</para>
3482+
3483+
<para>
3484+
This variable is not used for temporary tables; for them,
3485+
<xref linkend="guc-temp-tablespaces"> is consulted instead.
34793486
</para>
34803487

34813488
<para>
@@ -3485,6 +3492,42 @@ SELECT * FROM parent WHERE key = 2400;
34853492
</listitem>
34863493
</varlistentry>
34873494

3495+
<varlistentry id="guc-temp-tablespaces" xreflabel="temp_tablespaces">
3496+
<term><varname>temp_tablespaces</varname> (<type>string</type>)</term>
3497+
<indexterm>
3498+
<primary><varname>temp_tablespaces</> configuration parameter</primary>
3499+
</indexterm>
3500+
<indexterm><primary>tablespace</><secondary>temporary</></>
3501+
<listitem>
3502+
<para>
3503+
This variable specifies tablespace(s) in which to create temporary
3504+
objects (temp tables and indexes on temp tables) when a
3505+
<command>CREATE</> command does not explicitly specify a tablespace.
3506+
Temporary files for purposes such as sorting large data sets
3507+
are also created in these tablespace(s).
3508+
</para>
3509+
3510+
<para>
3511+
The value is a list of names of tablespaces. When there is more than
3512+
one name in the list, <productname>PostgreSQL</> chooses a random
3513+
member of the list each time a temporary object is to be created.
3514+
</para>
3515+
3516+
<para>
3517+
If any element of the list is an empty string or does not match the
3518+
name of any existing tablespace, <productname>PostgreSQL</> will
3519+
automatically use the default tablespace of the current database
3520+
instead. If a nondefault tablespace
3521+
is specified, the user must have <literal>CREATE</> privilege
3522+
for it, or creation attempts will fail.
3523+
</para>
3524+
3525+
<para>
3526+
See also <xref linkend="guc-default-tablespace">.
3527+
</para>
3528+
</listitem>
3529+
</varlistentry>
3530+
34883531
<varlistentry id="guc-check-function-bodies" xreflabel="check_function_bodies">
34893532
<term><varname>check_function_bodies</varname> (<type>boolean</type>)</term>
34903533
<indexterm>
@@ -3605,7 +3648,7 @@ SELECT * FROM parent WHERE key = 2400;
36053648
</para>
36063649
</listitem>
36073650
</varlistentry>
3608-
3651+
36093652
<varlistentry id="guc-xmlbinary" xreflabel="xmlbinary">
36103653
<term><varname>xmlbinary</varname> (<type>string</type>)</term>
36113654
<indexterm>
@@ -3632,7 +3675,7 @@ SELECT * FROM parent WHERE key = 2400;
36323675
</para>
36333676
</listitem>
36343677
</varlistentry>
3635-
3678+
36363679
<varlistentry id="guc-xmloption" xreflabel="xmloption">
36373680
<term><varname>xmloption</varname> (<type>string</type>)</term>
36383681
<indexterm>
@@ -3664,7 +3707,7 @@ SET XML OPTION { DOCUMENT | CONTENT };
36643707
</para>
36653708
</listitem>
36663709
</varlistentry>
3667-
3710+
36683711
</variablelist>
36693712
</sect2>
36703713
<sect2 id="runtime-config-client-format">
@@ -3945,7 +3988,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
39453988
</para>
39463989
</listitem>
39473990
</varlistentry>
3948-
3991+
39493992
<varlistentry id="guc-local-preload-libraries" xreflabel="local_preload_libraries">
39503993
<term><varname>local_preload_libraries</varname> (<type>string</type>)</term>
39513994
<indexterm>
@@ -3994,7 +4037,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
39944037
<para>
39954038
Every PostgreSQL-supported library has a <quote>magic
39964039
block</> that is checked to guarantee compatibility.
3997-
For this reason, non-PostgreSQL libraries cannot be
4040+
For this reason, non-PostgreSQL libraries cannot be
39984041
loaded in this way.
39994042
</para>
40004043
</listitem>

‎doc/src/sgml/manage-ag.sgml

Lines changed: 17 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.53 2007/02/01 00:28:17 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.54 2007/06/03 17:05:52 tgl Exp $ -->
22

33
<chapter id="managing-databases">
44
<title>Managing Databases</title>
@@ -423,13 +423,23 @@ CREATE TABLE foo(i int);
423423
do not have an explicit one.
424424
</para>
425425

426+
<para>
427+
There is also a <xref linkend="guc-temp-tablespaces"> parameter, which
428+
determines the placement of temporary tables and indexes, as well as
429+
temporary files that are used for purposes such as sorting large data
430+
sets. This can be a list of tablespace names, rather than only one,
431+
so that the load associated with temporary objects can be spread over
432+
multiple tablespaces. A random member of the list is picked each time
433+
a temporary object is to be created.
434+
</para>
435+
426436
<para>
427437
The tablespace associated with a database is used to store the system
428-
catalogs of that database, as well as any temporary files created by
429-
server processes using that database. Furthermore, it is the default
430-
tablespace selected for tables and indexes created within the database,
431-
if no <literal>TABLESPACE</> clause is given (either explicitly or via
432-
<varname>default_tablespace</>) when the objects are created.
438+
catalogs of that database. Furthermore, it is the default tablespace
439+
used for tables, indexes, and temporary files created within the database,
440+
if no <literal>TABLESPACE</> clause is given and no other selection is
441+
specified by <varname>default_tablespace</> or
442+
<varname>temp_tablespaces</> (as appropriate).
433443
If a database is created without specifying a tablespace for it,
434444
it uses the same tablespace as the template database it is copied from.
435445
</para>
@@ -468,7 +478,7 @@ SELECT spcname FROM pg_tablespace;
468478
</para>
469479

470480
<para>
471-
<productname>PostgreSQL</> makes extensive use of symbolic links
481+
<productname>PostgreSQL</> makes use of symbolic links
472482
to simplify the implementation of tablespaces. This
473483
means that tablespaces can be used <emphasis>only</> on systems
474484
that support symbolic links.

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.62 2007/04/06 22:33:41 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.63 2007/06/03 17:05:53 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -240,9 +240,9 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] <replaceable class="parameter">name</re
240240
<listitem>
241241
<para>
242242
The tablespace in which to create the index. If not specified,
243-
<xref linkend="guc-default-tablespace"> is used, or the database's
244-
default tablespace if <varname>default_tablespace</> is an empty
245-
string.
243+
<xref linkend="guc-default-tablespace"> is consulted, or
244+
<xref linkend="guc-temp-tablespaces"> for indexes on temporary
245+
tables.
246246
</para>
247247
</listitem>
248248
</varlistentry>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.107 2007/02/01 00:28:18 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.108 2007/06/03 17:06:03 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -645,9 +645,8 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
645645
The <replaceable class="PARAMETER">tablespace</replaceable> is the name
646646
of the tablespace in which the new table is to be created.
647647
If not specified,
648-
<xref linkend="guc-default-tablespace"> is used, or the database's
649-
default tablespace if <varname>default_tablespace</> is an empty
650-
string.
648+
<xref linkend="guc-default-tablespace"> is consulted, or
649+
<xref linkend="guc-temp-tablespaces"> if the table is temporary.
651650
</para>
652651
</listitem>
653652
</varlistentry>
@@ -660,9 +659,8 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
660659
associated with a <literal>UNIQUE</literal> or <literal>PRIMARY
661660
KEY</literal> constraint will be created.
662661
If not specified,
663-
<xref linkend="guc-default-tablespace"> is used, or the database's
664-
default tablespace if <varname>default_tablespace</> is an empty
665-
string.
662+
<xref linkend="guc-default-tablespace"> is consulted, or
663+
<xref linkend="guc-temp-tablespaces"> if the table is temporary.
666664
</para>
667665
</listitem>
668666
</varlistentry>

‎doc/src/sgml/ref/create_table_as.sgml

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.36 2006/09/18 19:54:01 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.37 2007/06/03 17:06:12 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -184,9 +184,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
184184
The <replaceable class="PARAMETER">tablespace</replaceable> is the name
185185
of the tablespace in which the new table is to be created.
186186
If not specified,
187-
<xref linkend="guc-default-tablespace"> is used, or the database's
188-
default tablespace if <varname>default_tablespace</> is an empty
189-
string.
187+
<xref linkend="guc-default-tablespace"> is consulted, or
188+
<xref linkend="guc-temp-tablespaces"> if the table is temporary.
190189
</para>
191190
</listitem>
192191
</varlistentry>

‎doc/src/sgml/ref/drop_tablespace.sgml

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_tablespace.sgml,v 1.6 2007/01/31 23:26:03 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_tablespace.sgml,v 1.7 2007/06/03 17:06:13 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -36,7 +36,10 @@ DROP TABLESPACE [ IF EXISTS ] <replaceable class="PARAMETER">tablespacename</rep
3636
The tablespace must be empty of all database objects before it can be
3737
dropped. It is possible that objects in other databases might still reside
3838
in the tablespace even if no objects in the current database are using
39-
the tablespace.
39+
the tablespace. Also, if the tablespace is listed in the <xref
40+
linkend="guc-temp-tablespaces"> setting of any active session, the
41+
<command>DROP</> might fail due to temporary files residing in the
42+
tablespace.
4043
</para>
4144
</refsect1>
4245

‎doc/src/sgml/ref/grant.sgml

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.65 2007/04/07 03:48:25 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/grant.sgml,v 1.66 2007/06/03 17:06:13 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -211,10 +211,10 @@ GRANT <replaceable class="PARAMETER">role</replaceable> [, ...] TO <replaceable
211211
have this privilege for the containing schema.
212212
</para>
213213
<para>
214-
For tablespaces, allows tables and indexes to be created within the
215-
tablespace, and allows databases to be created that have the tablespace
216-
as their default tablespace. (Note that revoking this privilege
217-
will not alter the placement of existing objects.)
214+
For tablespaces, allows tables, indexes, and temporary files to be
215+
created within the tablespace, and allows databases to be created that
216+
have the tablespace as their default tablespace. (Note that revoking
217+
this privilege will not alter the placement of existing objects.)
218218
</para>
219219
</listitem>
220220
</varlistentry>

0 commit comments

Comments
 (0)