🌐 AI搜索 & 代理 主页
Skip to content

Commit 5254958

Browse files
sfrostsfrost
committed
Add CREATE TABLESPACE ... WITH ... Options
Tablespaces have a few options which can be set on them to give PG hints as to how the tablespace behaves (perhaps it's faster for sequential scans, or better able to handle random access, etc). These options were only available through the ALTER TABLESPACE command. This adds the ability to set these options at CREATE TABLESPACE time, removing the need to do both a CREATE TABLESPACE and ALTER TABLESPACE to get the correct options set on the tablespace. Vik Fearing, reviewed by Michael Paquier.
1 parent 115f414 commit 5254958

File tree

8 files changed

+61
-3
lines changed

8 files changed

+61
-3
lines changed

‎doc/src/sgml/ref/create_tablespace.sgml‎

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,10 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> [ OWNER <replaceable class="parameter">user_name</replaceable> ] LOCATION '<replaceable class="parameter">directory</replaceable>'
24+
CREATE TABLESPACE <replaceable class="parameter">tablespace_name</replaceable>
25+
[ OWNER <replaceable class="parameter">user_name</replaceable> ]
26+
LOCATION '<replaceable class="parameter">directory</replaceable>'
27+
[ WITH ( <replaceable class="PARAMETER">tablespace_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) ]
2528
</synopsis>
2629
</refsynopsisdiv>
2730

@@ -87,6 +90,24 @@ CREATE TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> [
8790
</para>
8891
</listitem>
8992
</varlistentry>
93+
94+
<varlistentry>
95+
<term><replaceable class="parameter">tablespace_option</replaceable></term>
96+
<listitem>
97+
<para>
98+
A tablespace parameter to be set or reset. Currently, the only
99+
available parameters are <varname>seq_page_cost</> and
100+
<varname>random_page_cost</>. Setting either value for a particular
101+
tablespace will override the planner's usual estimate of the cost of
102+
reading pages from tables in that tablespace, as established by
103+
the configuration parameters of the same name (see
104+
<xref linkend="guc-seq-page-cost">,
105+
<xref linkend="guc-random-page-cost">). This may be useful if one
106+
tablespace is located on a disk which is faster or slower than the
107+
remainder of the I/O subsystem.
108+
</para>
109+
</listitem>
110+
</varlistentry>
90111
</variablelist>
91112
</refsect1>
92113

‎src/backend/commands/tablespace.c‎

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -239,6 +239,7 @@ CreateTableSpace(CreateTableSpaceStmt *stmt)
239239
Oid tablespaceoid;
240240
char *location;
241241
Oid ownerId;
242+
Datum newOptions;
242243

243244
/* Must be super user */
244245
if (!superuser())
@@ -322,7 +323,16 @@ CreateTableSpace(CreateTableSpaceStmt *stmt)
322323
values[Anum_pg_tablespace_spcowner - 1] =
323324
ObjectIdGetDatum(ownerId);
324325
nulls[Anum_pg_tablespace_spcacl - 1] = true;
325-
nulls[Anum_pg_tablespace_spcoptions - 1] = true;
326+
327+
/* Generate new proposed spcoptions (text array) */
328+
newOptions = transformRelOptions((Datum) 0,
329+
stmt->options,
330+
NULL, NULL, false, false);
331+
(void) tablespace_reloptions(newOptions, true);
332+
if (newOptions != (Datum) 0)
333+
values[Anum_pg_tablespace_spcoptions - 1] = newOptions;
334+
else
335+
nulls[Anum_pg_tablespace_spcoptions - 1] = true;
326336

327337
tuple = heap_form_tuple(rel->rd_att, values, nulls);
328338

‎src/backend/nodes/copyfuncs.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3370,6 +3370,7 @@ _copyCreateTableSpaceStmt(const CreateTableSpaceStmt *from)
33703370
COPY_STRING_FIELD(tablespacename);
33713371
COPY_STRING_FIELD(owner);
33723372
COPY_STRING_FIELD(location);
3373+
COPY_NODE_FIELD(options);
33733374

33743375
return newnode;
33753376
}

‎src/backend/nodes/equalfuncs.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1610,6 +1610,7 @@ _equalCreateTableSpaceStmt(const CreateTableSpaceStmt *a, const CreateTableSpace
16101610
COMPARE_STRING_FIELD(tablespacename);
16111611
COMPARE_STRING_FIELD(owner);
16121612
COMPARE_STRING_FIELD(location);
1613+
COMPARE_NODE_FIELD(options);
16131614

16141615
return true;
16151616
}

‎src/backend/parser/gram.y‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3588,12 +3588,13 @@ opt_procedural:
35883588
*
35893589
*****************************************************************************/
35903590

3591-
CreateTableSpaceStmt: CREATE TABLESPACE name OptTableSpaceOwner LOCATION Sconst
3591+
CreateTableSpaceStmt: CREATE TABLESPACE name OptTableSpaceOwner LOCATION Sconst opt_reloptions
35923592
{
35933593
CreateTableSpaceStmt *n = makeNode(CreateTableSpaceStmt);
35943594
n->tablespacename = $3;
35953595
n->owner = $4;
35963596
n->location = $6;
3597+
n->options = $7;
35973598
$$ = (Node *) n;
35983599
}
35993600
;

‎src/include/nodes/parsenodes.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1669,6 +1669,7 @@ typedef struct CreateTableSpaceStmt
16691669
char *tablespacename;
16701670
char *owner;
16711671
char *location;
1672+
List *options;
16721673
} CreateTableSpaceStmt;
16731674

16741675
typedef struct DropTableSpaceStmt

‎src/test/regress/input/tablespace.source‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,13 @@
1+
-- create a tablespace using WITH clause
2+
CREATE TABLESPACE testspacewith LOCATION '@testtablespace@' WITH (some_nonexistent_parameter = true); -- fail
3+
CREATE TABLESPACE testspacewith LOCATION '@testtablespace@' WITH (random_page_cost = 3.0); -- ok
4+
5+
-- check to see the parameter was used
6+
SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspacewith';
7+
8+
-- drop the tablespace so we can re-use the location
9+
DROP TABLESPACE testspacewith;
10+
111
-- create a tablespace we can use
212
CREATE TABLESPACE testspace LOCATION '@testtablespace@';
313

‎src/test/regress/output/tablespace.source‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,16 @@
1+
-- create a tablespace using WITH clause
2+
CREATE TABLESPACE testspacewith LOCATION '@testtablespace@' WITH (some_nonexistent_parameter = true); -- fail
3+
ERROR: unrecognized parameter "some_nonexistent_parameter"
4+
CREATE TABLESPACE testspacewith LOCATION '@testtablespace@' WITH (random_page_cost = 3.0); -- ok
5+
-- check to see the parameter was used
6+
SELECT spcoptions FROM pg_tablespace WHERE spcname = 'testspacewith';
7+
spcoptions
8+
------------------------
9+
{random_page_cost=3.0}
10+
(1 row)
11+
12+
-- drop the tablespace so we can re-use the location
13+
DROP TABLESPACE testspacewith;
114
-- create a tablespace we can use
215
CREATE TABLESPACE testspace LOCATION '@testtablespace@';
316
-- try setting and resetting some properties for the new tablespace

0 commit comments

Comments
 (0)