Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commit5254958

Browse files
committed
Add CREATE TABLESPACE ... WITH ... Options
Tablespaces have a few options which can be set on them to give PG hintsas to how the tablespace behaves (perhaps it's faster for sequentialscans, or better able to handle random access, etc). These options wereonly 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 toget the correct options set on the tablespace.Vik Fearing, reviewed by Michael Paquier.
1 parent115f414 commit5254958

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
Oidtablespaceoid;
240240
char*location;
241241
OidownerId;
242+
DatumnewOptions;
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
returnnewnode;
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:CREATETABLESPACEnameOptTableSpaceOwnerLOCATIONSconst
3591+
CreateTableSpaceStmt:CREATETABLESPACEnameOptTableSpaceOwnerLOCATIONSconstopt_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
typedefstructDropTableSpaceStmt

‎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)

[8]ページ先頭

©2009-2025 Movatter.jp