Skip to content

Commit 784cedd

Browse files
peterepetere
committed
Allow specifying STORAGE attribute for a new table
Previously, the STORAGE specification was only available in ALTER TABLE. This makes it available in CREATE TABLE as well. Also make the code and the documentation for STORAGE and COMPRESSION attributes consistent. Author: Teodor Sigaev <teodor@sigaev.ru> Author: Aleksander Alekseev <aleksander@timescale.com> Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com> Reviewed-by: wenjing zeng <wjzeng2012@gmail.com> Reviewed-by: Matthias van de Meent <boekewurm+postgres@gmail.com> Reviewed-by: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Discussion: https://postgr.es/m/de83407a-ae3d-a8e1-a788-920eb334f25b@sigaev.ru
1 parent 503e383 commit 784cedd

File tree

7 files changed

+107
-52
lines changed

7 files changed

+107
-52
lines changed

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -367,7 +367,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
367367

368368
<varlistentry>
369369
<term>
370-
<literal>SET STORAGE</literal>
370+
<literal>SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }</literal>
371371
<indexterm>
372372
<primary>TOAST</primary>
373373
<secondary>per-column storage settings</secondary>

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

Lines changed: 28 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
25-
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COMPRESSION <replaceable>compression_method</replaceable> ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
25+
{ <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ] [ COMPRESSION <replaceable>compression_method</replaceable> ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ]
2626
| <replaceable>table_constraint</replaceable>
2727
| LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
2828
[, ... ]
@@ -297,6 +297,33 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
297297
</listitem>
298298
</varlistentry>
299299

300+
<varlistentry>
301+
<term>
302+
<literal>STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }</literal>
303+
<indexterm>
304+
<primary>TOAST</primary>
305+
<secondary>per-column storage settings</secondary>
306+
</indexterm>
307+
</term>
308+
<listitem>
309+
<para>
310+
This form sets the storage mode for the column. This controls whether this
311+
column is held inline or in a secondary <acronym>TOAST</acronym> table,
312+
and whether the data should be compressed or not. <literal>PLAIN</literal>
313+
must be used for fixed-length values such as <type>integer</type> and is
314+
inline, uncompressed. <literal>MAIN</literal> is for inline, compressible
315+
data. <literal>EXTERNAL</literal> is for external, uncompressed data, and
316+
<literal>EXTENDED</literal> is for external, compressed data.
317+
<literal>EXTENDED</literal> is the default for most data types that
318+
support non-<literal>PLAIN</literal> storage. Use of
319+
<literal>EXTERNAL</literal> will make substring operations on very large
320+
<type>text</type> and <type>bytea</type> values run faster, at the penalty
321+
of increased storage space. See <xref linkend="storage-toast"/> for more
322+
information.
323+
</para>
324+
</listitem>
325+
</varlistentry>
326+
300327
<varlistentry>
301328
<term><literal>COMPRESSION <replaceable class="parameter">compression_method</replaceable></literal></term>
302329
<listitem>

‎src/backend/commands/tablecmds.c

Lines changed: 51 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -593,7 +593,7 @@ static void ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKM
593593
static void ATExecGenericOptions(Relation rel, List *options);
594594
static void ATExecSetRowSecurity(Relation rel, bool rls);
595595
static void ATExecForceNoForceRowSecurity(Relation rel, bool force_rls);
596-
static ObjectAddress ATExecSetCompression(AlteredTableInfo *tab, Relation rel,
596+
static ObjectAddress ATExecSetCompression(Relation rel,
597597
const char *column, Node *newValue, LOCKMODE lockmode);
598598

599599
static void index_copy_data(Relation rel, RelFileLocator newrlocator);
@@ -633,6 +633,7 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx,
633633
static List *GetParentedForeignKeyRefs(Relation partition);
634634
static void ATDetachCheckNoForeignKeyRefs(Relation partition);
635635
static char GetAttributeCompression(Oid atttypid, char *compression);
636+
static char GetAttributeStorage(Oid atttypid, const char *storagemode);
636637

637638

638639
/* ----------------------------------------------------------------
@@ -931,6 +932,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
931932
if (colDef->compression)
932933
attr->attcompression = GetAttributeCompression(attr->atttypid,
933934
colDef->compression);
935+
936+
if (colDef->storage_name)
937+
attr->attstorage = GetAttributeStorage(attr->atttypid, colDef->storage_name);
934938
}
935939

936940
/*
@@ -4963,8 +4967,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
49634967
case AT_SetStorage: /* ALTER COLUMN SET STORAGE */
49644968
address = ATExecSetStorage(rel, cmd->name, cmd->def, lockmode);
49654969
break;
4966-
case AT_SetCompression:
4967-
address = ATExecSetCompression(tab, rel, cmd->name, cmd->def,
4970+
case AT_SetCompression: /* ALTER COLUMN SET COMPRESSION */
4971+
address = ATExecSetCompression(rel, cmd->name, cmd->def,
49684972
lockmode);
49694973
break;
49704974
case AT_DropColumn: /* DROP COLUMN */
@@ -6820,7 +6824,10 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
68206824
attribute.atttypmod = typmod;
68216825
attribute.attbyval = tform->typbyval;
68226826
attribute.attalign = tform->typalign;
6823-
attribute.attstorage = tform->typstorage;
6827+
if (colDef->storage_name)
6828+
attribute.attstorage = GetAttributeStorage(typeOid, colDef->storage_name);
6829+
else
6830+
attribute.attstorage = tform->typstorage;
68246831
attribute.attcompression = GetAttributeCompression(typeOid,
68256832
colDef->compression);
68266833
attribute.attnotnull = colDef->is_not_null;
@@ -8263,33 +8270,12 @@ SetIndexStorageProperties(Relation rel, Relation attrelation,
82638270
static ObjectAddress
82648271
ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE lockmode)
82658272
{
8266-
char *storagemode;
8267-
char newstorage;
82688273
Relation attrelation;
82698274
HeapTuple tuple;
82708275
Form_pg_attribute attrtuple;
82718276
AttrNumber attnum;
82728277
ObjectAddress address;
82738278

8274-
storagemode = strVal(newValue);
8275-
8276-
if (pg_strcasecmp(storagemode, "plain") == 0)
8277-
newstorage = TYPSTORAGE_PLAIN;
8278-
else if (pg_strcasecmp(storagemode, "external") == 0)
8279-
newstorage = TYPSTORAGE_EXTERNAL;
8280-
else if (pg_strcasecmp(storagemode, "extended") == 0)
8281-
newstorage = TYPSTORAGE_EXTENDED;
8282-
else if (pg_strcasecmp(storagemode, "main") == 0)
8283-
newstorage = TYPSTORAGE_MAIN;
8284-
else
8285-
{
8286-
ereport(ERROR,
8287-
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
8288-
errmsg("invalid storage type \"%s\"",
8289-
storagemode)));
8290-
newstorage = 0; /* keep compiler quiet */
8291-
}
8292-
82938279
attrelation = table_open(AttributeRelationId, RowExclusiveLock);
82948280

82958281
tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);
@@ -8308,35 +8294,25 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
83088294
errmsg("cannot alter system column \"%s\"",
83098295
colName)));
83108296

8311-
/*
8312-
* safety check: do not allow toasted storage modes unless column datatype
8313-
* is TOAST-aware.
8314-
*/
8315-
if (newstorage == TYPSTORAGE_PLAIN || TypeIsToastable(attrtuple->atttypid))
8316-
attrtuple->attstorage = newstorage;
8317-
else
8318-
ereport(ERROR,
8319-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
8320-
errmsg("column data type %s can only have storage PLAIN",
8321-
format_type_be(attrtuple->atttypid))));
8297+
attrtuple->attstorage = GetAttributeStorage(attrtuple->atttypid, strVal(newValue));
83228298

83238299
CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
83248300

83258301
InvokeObjectPostAlterHook(RelationRelationId,
83268302
RelationGetRelid(rel),
83278303
attrtuple->attnum);
83288304

8329-
heap_freetuple(tuple);
8330-
83318305
/*
83328306
* Apply the change to indexes as well (only for simple index columns,
83338307
* matching behavior of index.c ConstructTupleDescriptor()).
83348308
*/
83358309
SetIndexStorageProperties(rel, attrelation, attnum,
8336-
true, newstorage,
8310+
true, attrtuple->attstorage,
83378311
false, 0,
83388312
lockmode);
83398313

8314+
heap_freetuple(tuple);
8315+
83408316
table_close(attrelation, RowExclusiveLock);
83418317

83428318
ObjectAddressSubSet(address, RelationRelationId,
@@ -16156,8 +16132,7 @@ ATExecGenericOptions(Relation rel, List *options)
1615616132
* Return value is the address of the modified column
1615716133
*/
1615816134
static ObjectAddress
16159-
ATExecSetCompression(AlteredTableInfo *tab,
16160-
Relation rel,
16135+
ATExecSetCompression(Relation rel,
1616116136
const char *column,
1616216137
Node *newValue,
1616316138
LOCKMODE lockmode)
@@ -19287,3 +19262,38 @@ GetAttributeCompression(Oid atttypid, char *compression)
1928719262

1928819263
return cmethod;
1928919264
}
19265+
19266+
/*
19267+
* resolve column storage specification
19268+
*/
19269+
static char
19270+
GetAttributeStorage(Oid atttypid, const char *storagemode)
19271+
{
19272+
char cstorage = 0;
19273+
19274+
if (pg_strcasecmp(storagemode, "plain") == 0)
19275+
cstorage = TYPSTORAGE_PLAIN;
19276+
else if (pg_strcasecmp(storagemode, "external") == 0)
19277+
cstorage = TYPSTORAGE_EXTERNAL;
19278+
else if (pg_strcasecmp(storagemode, "extended") == 0)
19279+
cstorage = TYPSTORAGE_EXTENDED;
19280+
else if (pg_strcasecmp(storagemode, "main") == 0)
19281+
cstorage = TYPSTORAGE_MAIN;
19282+
else
19283+
ereport(ERROR,
19284+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
19285+
errmsg("invalid storage type \"%s\"",
19286+
storagemode)));
19287+
19288+
/*
19289+
* safety check: do not allow toasted storage modes unless column datatype
19290+
* is TOAST-aware.
19291+
*/
19292+
if (!(cstorage == TYPSTORAGE_PLAIN || TypeIsToastable(atttypid)))
19293+
ereport(ERROR,
19294+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
19295+
errmsg("column data type %s can only have storage PLAIN",
19296+
format_type_be(atttypid))));
19297+
19298+
return cstorage;
19299+
}

‎src/backend/parser/gram.y

Lines changed: 17 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -595,7 +595,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
595595

596596
%type <node> TableConstraint TableLikeClause
597597
%type <ival> TableLikeOptionList TableLikeOption
598-
%type <str> column_compression opt_column_compression
598+
%type <str> column_compression opt_column_compression column_storage opt_column_storage
599599
%type <list> ColQualList
600600
%type <node> ColConstraint ColConstraintElem ConstraintAttr
601601
%type <ival> key_match
@@ -2537,13 +2537,13 @@ alter_table_cmd:
25372537
$$ = (Node *) n;
25382538
}
25392539
/* ALTER TABLE <name> ALTER [COLUMN] <colname> SET STORAGE <storagemode> */
2540-
| ALTER opt_column ColId SET STORAGE ColId
2540+
| ALTER opt_column ColId SET column_storage
25412541
{
25422542
AlterTableCmd *n = makeNode(AlterTableCmd);
25432543

25442544
n->subtype = AT_SetStorage;
25452545
n->name = $3;
2546-
n->def = (Node *) makeString($6);
2546+
n->def = (Node *) makeString($5);
25472547
$$ = (Node *) n;
25482548
}
25492549
/* ALTER TABLE <name> ALTER [COLUMN] <colname> SET COMPRESSION <cm> */
@@ -3778,13 +3778,14 @@ TypedTableElement:
37783778
| TableConstraint { $$ = $1; }
37793779
;
37803780

3781-
columnDef: ColId Typename opt_column_compression create_generic_options ColQualList
3781+
columnDef: ColId Typename opt_column_storage opt_column_compression create_generic_options ColQualList
37823782
{
37833783
ColumnDef *n = makeNode(ColumnDef);
37843784

37853785
n->colname = $1;
37863786
n->typeName = $2;
3787-
n->compression = $3;
3787+
n->storage_name = $3;
3788+
n->compression = $4;
37883789
n->inhcount = 0;
37893790
n->is_local = true;
37903791
n->is_not_null = false;
@@ -3793,8 +3794,8 @@ columnDef: ColId Typename opt_column_compression create_generic_options ColQualL
37933794
n->raw_default = NULL;
37943795
n->cooked_default = NULL;
37953796
n->collOid = InvalidOid;
3796-
n->fdwoptions = $4;
3797-
SplitColQualList($5, &n->constraints, &n->collClause,
3797+
n->fdwoptions = $5;
3798+
SplitColQualList($6, &n->constraints, &n->collClause,
37983799
yyscanner);
37993800
n->location = @1;
38003801
$$ = (Node *) n;
@@ -3851,6 +3852,15 @@ opt_column_compression:
38513852
| /*EMPTY*/ { $$ = NULL; }
38523853
;
38533854

3855+
column_storage:
3856+
STORAGE ColId { $$ = $2; }
3857+
;
3858+
3859+
opt_column_storage:
3860+
column_storage { $$ = $1; }
3861+
| /*EMPTY*/ { $$ = NULL; }
3862+
;
3863+
38543864
ColQualList:
38553865
ColQualList ColConstraint { $$ = lappend($1, $2); }
38563866
| /*EMPTY*/ { $$ = NIL; }

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -695,6 +695,7 @@ typedef struct ColumnDef
695695
bool is_not_null; /* NOT NULL constraint specified? */
696696
bool is_from_type; /* column definition came from table type */
697697
char storage; /* attstorage setting, or 0 for default */
698+
char *storage_name; /* attstorage setting name or NULL for default */
698699
Node *raw_default; /* default value (untransformed parse tree) */
699700
Node *cooked_default; /* default value (transformed expr tree) */
700701
char identity; /* attidentity setting */

‎src/test/regress/expected/alter_table.out

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2244,7 +2244,7 @@ alter table recur1 add column f2 int;
22442244
alter table recur1 alter column f2 type recur2; -- fails
22452245
ERROR: composite type recur1 cannot be made a member of itself
22462246
-- SET STORAGE may need to add a TOAST table
2247-
create table test_storage (a text);
2247+
create table test_storage (a text, c text storage plain);
22482248
alter table test_storage alter a set storage plain;
22492249
alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
22502250
alter table test_storage alter a set storage extended; -- re-add TOAST table
@@ -2256,6 +2256,9 @@ where oid = 'test_storage'::regclass;
22562256
t
22572257
(1 row)
22582258

2259+
-- check STORAGE correctness
2260+
create table test_storage_failed (a text, b int storage extended);
2261+
ERROR: column data type integer can only have storage PLAIN
22592262
-- test that SET STORAGE propagates to index correctly
22602263
create index test_storage_idx on test_storage (b, a);
22612264
alter table test_storage alter column a set storage external;
@@ -2264,6 +2267,7 @@ alter table test_storage alter column a set storage external;
22642267
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
22652268
--------+---------+-----------+----------+---------+----------+--------------+-------------
22662269
a | text | | | | external | |
2270+
c | text | | | | plain | |
22672271
b | integer | | | 0 | plain | |
22682272
Indexes:
22692273
"test_storage_idx" btree (b, a)

‎src/test/regress/sql/alter_table.sql

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1527,7 +1527,7 @@ alter table recur1 add column f2 int;
15271527
alter table recur1 alter column f2 type recur2; -- fails
15281528

15291529
-- SET STORAGE may need to add a TOAST table
1530-
create table test_storage (a text);
1530+
create table test_storage (a text, c text storage plain);
15311531
alter table test_storage alter a set storage plain;
15321532
alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
15331533
alter table test_storage alter a set storage extended; -- re-add TOAST table
@@ -1536,6 +1536,9 @@ select reltoastrelid <> 0 as has_toast_table
15361536
from pg_class
15371537
where oid = 'test_storage'::regclass;
15381538

1539+
-- check STORAGE correctness
1540+
create table test_storage_failed (a text, b int storage extended);
1541+
15391542
-- test that SET STORAGE propagates to index correctly
15401543
create index test_storage_idx on test_storage (b, a);
15411544
alter table test_storage alter column a set storage external;

0 commit comments

Comments
 (0)