Skip to content

Commit d86d51a

Browse files
robertmhaasrobertmhaas
committed
Support ALTER TABLESPACE name SET/RESET ( tablespace_options ).
This patch only supports seq_page_cost and random_page_cost as parameters, but it provides the infrastructure to scalably support many more. In particular, we may want to add support for effective_io_concurrency, but I'm leaving that as future work for now. Thanks to Tom Lane for design help and Alvaro Herrera for the review.
1 parent 72559b4 commit d86d51a

File tree

28 files changed

+588
-101
lines changed

28 files changed

+588
-101
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.241 2009/12/25 01:09:31 rhaas Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.242 2010/01/05 21:53:58 rhaas Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -2000,6 +2000,9 @@ archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows
20002000
<para>
20012001
Sets the planner's estimate of the cost of a disk page fetch
20022002
that is part of a series of sequential fetches. The default is 1.0.
2003+
This value can be overriden for a particular tablespace by setting
2004+
the tablespace parameter of the same name
2005+
(see <xref linkend="sql-altertablespace">).
20032006
</para>
20042007
</listitem>
20052008
</varlistentry>
@@ -2013,6 +2016,12 @@ archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows
20132016
<para>
20142017
Sets the planner's estimate of the cost of a
20152018
non-sequentially-fetched disk page. The default is 4.0.
2019+
This value can be overriden for a particular tablespace by setting
2020+
the tablespace parameter of the same name
2021+
(see <xref linkend="sql-altertablespace">).
2022+
</para>
2023+
2024+
<para>
20162025
Reducing this value relative to <varname>seq_page_cost</>
20172026
will cause the system to prefer index scans; raising it will
20182027
make index scans look relatively more expensive. You can raise

‎doc/src/sgml/ref/alter_tablespace.sgml

Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_tablespace.sgml,v 1.5 2009/09/19 10:23:26 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_tablespace.sgml,v 1.6 2010/01/05 21:53:58 rhaas Exp $
33
PostgreSQL documentation
44
-->
55

@@ -23,6 +23,8 @@ PostgreSQL documentation
2323
<synopsis>
2424
ALTER TABLESPACE <replaceable>name</replaceable> RENAME TO <replaceable>new_name</replaceable>
2525
ALTER TABLESPACE <replaceable>name</replaceable> OWNER TO <replaceable>new_owner</replaceable>
26+
ALTER TABLESPACE <replaceable>name</replaceable> SET ( <replaceable class="PARAMETER">tablespace_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
27+
ALTER TABLESPACE <replaceable>name</replaceable> RESET ( <replaceable class="PARAMETER">tablespace_option</replaceable> [, ... ] )
2628
</synopsis>
2729
</refsynopsisdiv>
2830

@@ -74,6 +76,24 @@ ALTER TABLESPACE <replaceable>name</replaceable> OWNER TO <replaceable>new_owner
7476
</para>
7577
</listitem>
7678
</varlistentry>
79+
80+
<varlistentry>
81+
<term><replaceable class="parameter">tablespace_parameter</replaceable></term>
82+
<listitem>
83+
<para>
84+
A tablespace parameter to be set or reset. Currently, the only
85+
available parameters are <varname>seq_page_cost</> and
86+
<varname>random_page_cost</>. Setting either value for a particular
87+
tablespace will override the planner's usual estimate of the cost of
88+
reading pages from tables in that tablespace, as established by
89+
the configuration parameters of the same name (see
90+
<xref linkend="guc-seq-page-cost">,
91+
<xref linkend="guc-random-page-cost">). This may be useful if one
92+
tablespace is located on a disk which is faster or slower than the
93+
remainder of the I/O subsystem.
94+
</para>
95+
</listitem>
96+
</varlistentry>
7797
</variablelist>
7898
</refsect1>
7999

‎src/backend/access/common/reloptions.c

Lines changed: 49 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/common/reloptions.c,v 1.30 2010/01/02 16:57:33 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/common/reloptions.c,v 1.31 2010/01/05 21:53:58 rhaas Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -21,6 +21,7 @@
2121
#include "access/reloptions.h"
2222
#include "catalog/pg_type.h"
2323
#include "commands/defrem.h"
24+
#include "commands/tablespace.h"
2425
#include "nodes/makefuncs.h"
2526
#include "utils/array.h"
2627
#include "utils/builtins.h"
@@ -179,6 +180,22 @@ static relopt_real realRelOpts[] =
179180
},
180181
-1, 0.0, 100.0
181182
},
183+
{
184+
{
185+
"seq_page_cost",
186+
"Sets the planner's estimate of the cost of a sequentially fetched disk page.",
187+
RELOPT_KIND_TABLESPACE
188+
},
189+
-1, 0.0, DBL_MAX
190+
},
191+
{
192+
{
193+
"random_page_cost",
194+
"Sets the planner's estimate of the cost of a nonsequentially fetched disk page.",
195+
RELOPT_KIND_TABLESPACE
196+
},
197+
-1, 0.0, DBL_MAX
198+
},
182199
/* list terminator */
183200
{{NULL}}
184201
};
@@ -1168,3 +1185,34 @@ index_reloptions(RegProcedure amoptions, Datum reloptions, bool validate)
11681185

11691186
return DatumGetByteaP(result);
11701187
}
1188+
1189+
/*
1190+
* Option parser for tablespace reloptions
1191+
*/
1192+
bytea *
1193+
tablespace_reloptions(Datum reloptions, bool validate)
1194+
{
1195+
relopt_value *options;
1196+
TableSpaceOpts *tsopts;
1197+
int numoptions;
1198+
static const relopt_parse_elt tab[] = {
1199+
{"random_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, random_page_cost)},
1200+
{"seq_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, seq_page_cost)}
1201+
};
1202+
1203+
options = parseRelOptions(reloptions, validate, RELOPT_KIND_TABLESPACE,
1204+
&numoptions);
1205+
1206+
/* if none set, we're done */
1207+
if (numoptions == 0)
1208+
return NULL;
1209+
1210+
tsopts = allocateReloptStruct(sizeof(TableSpaceOpts), options, numoptions);
1211+
1212+
fillRelOptions((void *) tsopts, sizeof(TableSpaceOpts), options, numoptions,
1213+
validate, tab, lengthof(tab));
1214+
1215+
pfree(options);
1216+
1217+
return (bytea *) tsopts;
1218+
}

‎src/backend/catalog/aclchk.c

Lines changed: 15 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.159 2010/01/02 16:57:36 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.160 2010/01/05 21:53:58 rhaas Exp $
1212
*
1313
* NOTES
1414
* See acl.h.
@@ -2783,18 +2783,11 @@ ExecGrant_Tablespace(InternalGrant *istmt)
27832783
int nnewmembers;
27842784
Oid *oldmembers;
27852785
Oid *newmembers;
2786-
ScanKeyData entry[1];
2787-
SysScanDesc scan;
27882786
HeapTuple tuple;
27892787

2790-
/* There's no syscache for pg_tablespace, so must look the hard way */
2791-
ScanKeyInit(&entry[0],
2792-
ObjectIdAttributeNumber,
2793-
BTEqualStrategyNumber, F_OIDEQ,
2794-
ObjectIdGetDatum(tblId));
2795-
scan = systable_beginscan(relation, TablespaceOidIndexId, true,
2796-
SnapshotNow, 1, entry);
2797-
tuple = systable_getnext(scan);
2788+
/* Search syscache for pg_tablespace */
2789+
tuple = SearchSysCache(TABLESPACEOID, ObjectIdGetDatum(tblId),
2790+
0, 0, 0);
27982791
if (!HeapTupleIsValid(tuple))
27992792
elog(ERROR, "cache lookup failed for tablespace %u", tblId);
28002793

@@ -2865,8 +2858,7 @@ ExecGrant_Tablespace(InternalGrant *istmt)
28652858
noldmembers, oldmembers,
28662859
nnewmembers, newmembers);
28672860

2868-
systable_endscan(scan);
2869-
2861+
ReleaseSysCache(tuple);
28702862
pfree(new_acl);
28712863

28722864
/* prevent error when processing duplicate objects */
@@ -3696,9 +3688,6 @@ pg_tablespace_aclmask(Oid spc_oid, Oid roleid,
36963688
AclMode mask, AclMaskHow how)
36973689
{
36983690
AclMode result;
3699-
Relation pg_tablespace;
3700-
ScanKeyData entry[1];
3701-
SysScanDesc scan;
37023691
HeapTuple tuple;
37033692
Datum aclDatum;
37043693
bool isNull;
@@ -3711,26 +3700,19 @@ pg_tablespace_aclmask(Oid spc_oid, Oid roleid,
37113700

37123701
/*
37133702
* Get the tablespace's ACL from pg_tablespace
3714-
*
3715-
* There's no syscache for pg_tablespace, so must look the hard way
37163703
*/
3717-
pg_tablespace = heap_open(TableSpaceRelationId, AccessShareLock);
3718-
ScanKeyInit(&entry[0],
3719-
ObjectIdAttributeNumber,
3720-
BTEqualStrategyNumber, F_OIDEQ,
3721-
ObjectIdGetDatum(spc_oid));
3722-
scan = systable_beginscan(pg_tablespace, TablespaceOidIndexId, true,
3723-
SnapshotNow, 1, entry);
3724-
tuple = systable_getnext(scan);
3704+
tuple = SearchSysCache(TABLESPACEOID, ObjectIdGetDatum(spc_oid),
3705+
0, 0, 0);
37253706
if (!HeapTupleIsValid(tuple))
37263707
ereport(ERROR,
37273708
(errcode(ERRCODE_UNDEFINED_OBJECT),
37283709
errmsg("tablespace with OID %u does not exist", spc_oid)));
37293710

37303711
ownerId = ((Form_pg_tablespace) GETSTRUCT(tuple))->spcowner;
37313712

3732-
aclDatum = heap_getattr(tuple, Anum_pg_tablespace_spcacl,
3733-
RelationGetDescr(pg_tablespace), &isNull);
3713+
aclDatum = SysCacheGetAttr(TABLESPACEOID, tuple,
3714+
Anum_pg_tablespace_spcacl,
3715+
&isNull);
37343716

37353717
if (isNull)
37363718
{
@@ -3750,8 +3732,7 @@ pg_tablespace_aclmask(Oid spc_oid, Oid roleid,
37503732
if (acl && (Pointer) acl != DatumGetPointer(aclDatum))
37513733
pfree(acl);
37523734

3753-
systable_endscan(scan);
3754-
heap_close(pg_tablespace, AccessShareLock);
3735+
ReleaseSysCache(tuple);
37553736

37563737
return result;
37573738
}
@@ -4338,36 +4319,24 @@ pg_namespace_ownercheck(Oid nsp_oid, Oid roleid)
43384319
bool
43394320
pg_tablespace_ownercheck(Oid spc_oid, Oid roleid)
43404321
{
4341-
Relation pg_tablespace;
4342-
ScanKeyData entry[1];
4343-
SysScanDesc scan;
43444322
HeapTuple spctuple;
43454323
Oid spcowner;
43464324

43474325
/* Superusers bypass all permission checking. */
43484326
if (superuser_arg(roleid))
43494327
return true;
43504328

4351-
/* There's no syscache for pg_tablespace, so must look the hard way */
4352-
pg_tablespace = heap_open(TableSpaceRelationId, AccessShareLock);
4353-
ScanKeyInit(&entry[0],
4354-
ObjectIdAttributeNumber,
4355-
BTEqualStrategyNumber, F_OIDEQ,
4356-
ObjectIdGetDatum(spc_oid));
4357-
scan = systable_beginscan(pg_tablespace, TablespaceOidIndexId, true,
4358-
SnapshotNow, 1, entry);
4359-
4360-
spctuple = systable_getnext(scan);
4361-
4329+
/* Search syscache for pg_tablespace */
4330+
spctuple = SearchSysCache(TABLESPACEOID, ObjectIdGetDatum(spc_oid),
4331+
0, 0, 0);
43624332
if (!HeapTupleIsValid(spctuple))
43634333
ereport(ERROR,
43644334
(errcode(ERRCODE_UNDEFINED_OBJECT),
43654335
errmsg("tablespace with OID %u does not exist", spc_oid)));
43664336

43674337
spcowner = ((Form_pg_tablespace) GETSTRUCT(spctuple))->spcowner;
43684338

4369-
systable_endscan(scan);
4370-
heap_close(pg_tablespace, AccessShareLock);
4339+
ReleaseSysCache(spctuple);
43714340

43724341
return has_privs_of_role(roleid, spcowner);
43734342
}

‎src/backend/commands/tablespace.c

Lines changed: 72 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
*
3838
*
3939
* IDENTIFICATION
40-
* $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.65 2010/01/02 16:57:37 momjian Exp $
40+
* $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.66 2010/01/05 21:53:58 rhaas Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -49,6 +49,7 @@
4949
#include <sys/stat.h>
5050

5151
#include "access/heapam.h"
52+
#include "access/reloptions.h"
5253
#include "access/sysattr.h"
5354
#include "access/transam.h"
5455
#include "access/xact.h"
@@ -57,6 +58,7 @@
5758
#include "catalog/indexing.h"
5859
#include "catalog/pg_tablespace.h"
5960
#include "commands/comment.h"
61+
#include "commands/defrem.h"
6062
#include "commands/tablespace.h"
6163
#include "miscadmin.h"
6264
#include "postmaster/bgwriter.h"
@@ -70,6 +72,7 @@
7072
#include "utils/lsyscache.h"
7173
#include "utils/memutils.h"
7274
#include "utils/rel.h"
75+
#include "utils/syscache.h"
7376
#include "utils/tqual.h"
7477

7578

@@ -290,6 +293,7 @@ CreateTableSpace(CreateTableSpaceStmt *stmt)
290293
values[Anum_pg_tablespace_spclocation - 1] =
291294
CStringGetTextDatum(location);
292295
nulls[Anum_pg_tablespace_spcacl - 1] = true;
296+
nulls[Anum_pg_tablespace_spcoptions - 1] = true;
293297

294298
tuple = heap_form_tuple(rel->rd_att, values, nulls);
295299

@@ -912,6 +916,73 @@ AlterTableSpaceOwner(const char *name, Oid newOwnerId)
912916
}
913917

914918

919+
/*
920+
* Alter table space options
921+
*/
922+
void
923+
AlterTableSpaceOptions(AlterTableSpaceOptionsStmt *stmt)
924+
{
925+
Relation rel;
926+
ScanKeyData entry[1];
927+
HeapScanDesc scandesc;
928+
HeapTuple tup;
929+
Datum datum;
930+
Datum newOptions;
931+
Datum repl_val[Natts_pg_tablespace];
932+
bool isnull;
933+
bool repl_null[Natts_pg_tablespace];
934+
bool repl_repl[Natts_pg_tablespace];
935+
HeapTuple newtuple;
936+
937+
/* Search pg_tablespace */
938+
rel = heap_open(TableSpaceRelationId, RowExclusiveLock);
939+
940+
ScanKeyInit(&entry[0],
941+
Anum_pg_tablespace_spcname,
942+
BTEqualStrategyNumber, F_NAMEEQ,
943+
CStringGetDatum(stmt->tablespacename));
944+
scandesc = heap_beginscan(rel, SnapshotNow, 1, entry);
945+
tup = heap_getnext(scandesc, ForwardScanDirection);
946+
if (!HeapTupleIsValid(tup))
947+
ereport(ERROR,
948+
(errcode(ERRCODE_UNDEFINED_OBJECT),
949+
errmsg("tablespace \"%s\" does not exist",
950+
stmt->tablespacename)));
951+
952+
/* Must be owner of the existing object */
953+
if (!pg_tablespace_ownercheck(HeapTupleGetOid(tup), GetUserId()))
954+
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TABLESPACE,
955+
stmt->tablespacename);
956+
957+
/* Generate new proposed spcoptions (text array) */
958+
datum = heap_getattr(tup, Anum_pg_tablespace_spcoptions,
959+
RelationGetDescr(rel), &isnull);
960+
newOptions = transformRelOptions(isnull ? (Datum) 0 : datum,
961+
stmt->options, NULL, NULL, false,
962+
stmt->isReset);
963+
(void) tablespace_reloptions(newOptions, true);
964+
965+
/* Build new tuple. */
966+
memset(repl_null, false, sizeof(repl_null));
967+
memset(repl_repl, false, sizeof(repl_repl));
968+
if (newOptions != (Datum) 0)
969+
repl_val[Anum_pg_tablespace_spcoptions - 1] = newOptions;
970+
else
971+
repl_null[Anum_pg_tablespace_spcoptions - 1] = true;
972+
repl_repl[Anum_pg_tablespace_spcoptions - 1] = true;
973+
newtuple = heap_modify_tuple(tup, RelationGetDescr(rel), repl_val,
974+
repl_null, repl_repl);
975+
976+
/* Update system catalog. */
977+
simple_heap_update(rel, &newtuple->t_self, newtuple);
978+
CatalogUpdateIndexes(rel, newtuple);
979+
heap_freetuple(newtuple);
980+
981+
/* Conclude heap scan. */
982+
heap_endscan(scandesc);
983+
heap_close(rel, NoLock);
984+
}
985+
915986
/*
916987
* Routines for handling the GUC variable 'default_tablespace'.
917988
*/

0 commit comments

Comments
 (0)