aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ref/alter_table.sgml2
-rw-r--r--doc/src/sgml/ref/create_table.sgml29
-rw-r--r--src/backend/commands/tablecmds.c92
-rw-r--r--src/backend/parser/gram.y24
-rw-r--r--src/include/nodes/parsenodes.h1
-rw-r--r--src/test/regress/expected/alter_table.out6
-rw-r--r--src/test/regress/sql/alter_table.sql5
7 files changed, 107 insertions, 52 deletions
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index a3c62bf056e..f0f912a56c5 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -367,7 +367,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<varlistentry>
<term>
- <literal>SET STORAGE</literal>
+ <literal>SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }</literal>
<indexterm>
<primary>TOAST</primary>
<secondary>per-column storage settings</secondary>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 6c9918b0a1e..6bbf15ed1a4 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name</replaceable> ( [
- { <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> [ ... ] ]
+ { <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> [ ... ] ]
| <replaceable>table_constraint</replaceable>
| LIKE <replaceable>source_table</replaceable> [ <replaceable>like_option</replaceable> ... ] }
[, ... ]
@@ -298,6 +298,33 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry>
+ <term>
+ <literal>STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }</literal>
+ <indexterm>
+ <primary>TOAST</primary>
+ <secondary>per-column storage settings</secondary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ This form sets the storage mode for the column. This controls whether this
+ column is held inline or in a secondary <acronym>TOAST</acronym> table,
+ and whether the data should be compressed or not. <literal>PLAIN</literal>
+ must be used for fixed-length values such as <type>integer</type> and is
+ inline, uncompressed. <literal>MAIN</literal> is for inline, compressible
+ data. <literal>EXTERNAL</literal> is for external, uncompressed data, and
+ <literal>EXTENDED</literal> is for external, compressed data.
+ <literal>EXTENDED</literal> is the default for most data types that
+ support non-<literal>PLAIN</literal> storage. Use of
+ <literal>EXTERNAL</literal> will make substring operations on very large
+ <type>text</type> and <type>bytea</type> values run faster, at the penalty
+ of increased storage space. See <xref linkend="storage-toast"/> for more
+ information.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>COMPRESSION <replaceable class="parameter">compression_method</replaceable></literal></term>
<listitem>
<para>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 701bd73f5ee..f2947ea9b49 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -593,7 +593,7 @@ static void ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKM
static void ATExecGenericOptions(Relation rel, List *options);
static void ATExecSetRowSecurity(Relation rel, bool rls);
static void ATExecForceNoForceRowSecurity(Relation rel, bool force_rls);
-static ObjectAddress ATExecSetCompression(AlteredTableInfo *tab, Relation rel,
+static ObjectAddress ATExecSetCompression(Relation rel,
const char *column, Node *newValue, LOCKMODE lockmode);
static void index_copy_data(Relation rel, RelFileLocator newrlocator);
@@ -633,6 +633,7 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx,
static List *GetParentedForeignKeyRefs(Relation partition);
static void ATDetachCheckNoForeignKeyRefs(Relation partition);
static char GetAttributeCompression(Oid atttypid, char *compression);
+static char GetAttributeStorage(Oid atttypid, const char *storagemode);
/* ----------------------------------------------------------------
@@ -931,6 +932,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (colDef->compression)
attr->attcompression = GetAttributeCompression(attr->atttypid,
colDef->compression);
+
+ if (colDef->storage_name)
+ attr->attstorage = GetAttributeStorage(attr->atttypid, colDef->storage_name);
}
/*
@@ -4963,8 +4967,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
case AT_SetStorage: /* ALTER COLUMN SET STORAGE */
address = ATExecSetStorage(rel, cmd->name, cmd->def, lockmode);
break;
- case AT_SetCompression:
- address = ATExecSetCompression(tab, rel, cmd->name, cmd->def,
+ case AT_SetCompression: /* ALTER COLUMN SET COMPRESSION */
+ address = ATExecSetCompression(rel, cmd->name, cmd->def,
lockmode);
break;
case AT_DropColumn: /* DROP COLUMN */
@@ -6820,7 +6824,10 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
attribute.atttypmod = typmod;
attribute.attbyval = tform->typbyval;
attribute.attalign = tform->typalign;
- attribute.attstorage = tform->typstorage;
+ if (colDef->storage_name)
+ attribute.attstorage = GetAttributeStorage(typeOid, colDef->storage_name);
+ else
+ attribute.attstorage = tform->typstorage;
attribute.attcompression = GetAttributeCompression(typeOid,
colDef->compression);
attribute.attnotnull = colDef->is_not_null;
@@ -8263,33 +8270,12 @@ SetIndexStorageProperties(Relation rel, Relation attrelation,
static ObjectAddress
ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE lockmode)
{
- char *storagemode;
- char newstorage;
Relation attrelation;
HeapTuple tuple;
Form_pg_attribute attrtuple;
AttrNumber attnum;
ObjectAddress address;
- storagemode = strVal(newValue);
-
- if (pg_strcasecmp(storagemode, "plain") == 0)
- newstorage = TYPSTORAGE_PLAIN;
- else if (pg_strcasecmp(storagemode, "external") == 0)
- newstorage = TYPSTORAGE_EXTERNAL;
- else if (pg_strcasecmp(storagemode, "extended") == 0)
- newstorage = TYPSTORAGE_EXTENDED;
- else if (pg_strcasecmp(storagemode, "main") == 0)
- newstorage = TYPSTORAGE_MAIN;
- else
- {
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("invalid storage type \"%s\"",
- storagemode)));
- newstorage = 0; /* keep compiler quiet */
- }
-
attrelation = table_open(AttributeRelationId, RowExclusiveLock);
tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);
@@ -8308,17 +8294,7 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
errmsg("cannot alter system column \"%s\"",
colName)));
- /*
- * safety check: do not allow toasted storage modes unless column datatype
- * is TOAST-aware.
- */
- if (newstorage == TYPSTORAGE_PLAIN || TypeIsToastable(attrtuple->atttypid))
- attrtuple->attstorage = newstorage;
- else
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("column data type %s can only have storage PLAIN",
- format_type_be(attrtuple->atttypid))));
+ attrtuple->attstorage = GetAttributeStorage(attrtuple->atttypid, strVal(newValue));
CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
@@ -8326,17 +8302,17 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE loc
RelationGetRelid(rel),
attrtuple->attnum);
- heap_freetuple(tuple);
-
/*
* Apply the change to indexes as well (only for simple index columns,
* matching behavior of index.c ConstructTupleDescriptor()).
*/
SetIndexStorageProperties(rel, attrelation, attnum,
- true, newstorage,
+ true, attrtuple->attstorage,
false, 0,
lockmode);
+ heap_freetuple(tuple);
+
table_close(attrelation, RowExclusiveLock);
ObjectAddressSubSet(address, RelationRelationId,
@@ -16156,8 +16132,7 @@ ATExecGenericOptions(Relation rel, List *options)
* Return value is the address of the modified column
*/
static ObjectAddress
-ATExecSetCompression(AlteredTableInfo *tab,
- Relation rel,
+ATExecSetCompression(Relation rel,
const char *column,
Node *newValue,
LOCKMODE lockmode)
@@ -19287,3 +19262,38 @@ GetAttributeCompression(Oid atttypid, char *compression)
return cmethod;
}
+
+/*
+ * resolve column storage specification
+ */
+static char
+GetAttributeStorage(Oid atttypid, const char *storagemode)
+{
+ char cstorage = 0;
+
+ if (pg_strcasecmp(storagemode, "plain") == 0)
+ cstorage = TYPSTORAGE_PLAIN;
+ else if (pg_strcasecmp(storagemode, "external") == 0)
+ cstorage = TYPSTORAGE_EXTERNAL;
+ else if (pg_strcasecmp(storagemode, "extended") == 0)
+ cstorage = TYPSTORAGE_EXTENDED;
+ else if (pg_strcasecmp(storagemode, "main") == 0)
+ cstorage = TYPSTORAGE_MAIN;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid storage type \"%s\"",
+ storagemode)));
+
+ /*
+ * safety check: do not allow toasted storage modes unless column datatype
+ * is TOAST-aware.
+ */
+ if (!(cstorage == TYPSTORAGE_PLAIN || TypeIsToastable(atttypid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column data type %s can only have storage PLAIN",
+ format_type_be(atttypid))));
+
+ return cstorage;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0523013f537..c018140afe4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -595,7 +595,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> TableConstraint TableLikeClause
%type <ival> TableLikeOptionList TableLikeOption
-%type <str> column_compression opt_column_compression
+%type <str> column_compression opt_column_compression column_storage opt_column_storage
%type <list> ColQualList
%type <node> ColConstraint ColConstraintElem ConstraintAttr
%type <ival> key_match
@@ -2537,13 +2537,13 @@ alter_table_cmd:
$$ = (Node *) n;
}
/* ALTER TABLE <name> ALTER [COLUMN] <colname> SET STORAGE <storagemode> */
- | ALTER opt_column ColId SET STORAGE ColId
+ | ALTER opt_column ColId SET column_storage
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_SetStorage;
n->name = $3;
- n->def = (Node *) makeString($6);
+ n->def = (Node *) makeString($5);
$$ = (Node *) n;
}
/* ALTER TABLE <name> ALTER [COLUMN] <colname> SET COMPRESSION <cm> */
@@ -3778,13 +3778,14 @@ TypedTableElement:
| TableConstraint { $$ = $1; }
;
-columnDef: ColId Typename opt_column_compression create_generic_options ColQualList
+columnDef: ColId Typename opt_column_storage opt_column_compression create_generic_options ColQualList
{
ColumnDef *n = makeNode(ColumnDef);
n->colname = $1;
n->typeName = $2;
- n->compression = $3;
+ n->storage_name = $3;
+ n->compression = $4;
n->inhcount = 0;
n->is_local = true;
n->is_not_null = false;
@@ -3793,8 +3794,8 @@ columnDef: ColId Typename opt_column_compression create_generic_options ColQualL
n->raw_default = NULL;
n->cooked_default = NULL;
n->collOid = InvalidOid;
- n->fdwoptions = $4;
- SplitColQualList($5, &n->constraints, &n->collClause,
+ n->fdwoptions = $5;
+ SplitColQualList($6, &n->constraints, &n->collClause,
yyscanner);
n->location = @1;
$$ = (Node *) n;
@@ -3851,6 +3852,15 @@ opt_column_compression:
| /*EMPTY*/ { $$ = NULL; }
;
+column_storage:
+ STORAGE ColId { $$ = $2; }
+ ;
+
+opt_column_storage:
+ column_storage { $$ = $1; }
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
+
ColQualList:
ColQualList ColConstraint { $$ = lappend($1, $2); }
| /*EMPTY*/ { $$ = NIL; }
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e2ad7617685..3605cea4c72 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -695,6 +695,7 @@ typedef struct ColumnDef
bool is_not_null; /* NOT NULL constraint specified? */
bool is_from_type; /* column definition came from table type */
char storage; /* attstorage setting, or 0 for default */
+ char *storage_name; /* attstorage setting name or NULL for default */
Node *raw_default; /* default value (untransformed parse tree) */
Node *cooked_default; /* default value (transformed expr tree) */
char identity; /* attidentity setting */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 5ede56d9b55..e3dac1699cf 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2244,7 +2244,7 @@ alter table recur1 add column f2 int;
alter table recur1 alter column f2 type recur2; -- fails
ERROR: composite type recur1 cannot be made a member of itself
-- SET STORAGE may need to add a TOAST table
-create table test_storage (a text);
+create table test_storage (a text, c text storage plain);
alter table test_storage alter a set storage plain;
alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
alter table test_storage alter a set storage extended; -- re-add TOAST table
@@ -2256,6 +2256,9 @@ where oid = 'test_storage'::regclass;
t
(1 row)
+-- check STORAGE correctness
+create table test_storage_failed (a text, b int storage extended);
+ERROR: column data type integer can only have storage PLAIN
-- test that SET STORAGE propagates to index correctly
create index test_storage_idx on test_storage (b, a);
alter table test_storage alter column a set storage external;
@@ -2264,6 +2267,7 @@ alter table test_storage alter column a set storage external;
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | text | | | | external | |
+ c | text | | | | plain | |
b | integer | | | 0 | plain | |
Indexes:
"test_storage_idx" btree (b, a)
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 52001e3135d..e7013f5e154 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1527,7 +1527,7 @@ alter table recur1 add column f2 int;
alter table recur1 alter column f2 type recur2; -- fails
-- SET STORAGE may need to add a TOAST table
-create table test_storage (a text);
+create table test_storage (a text, c text storage plain);
alter table test_storage alter a set storage plain;
alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
alter table test_storage alter a set storage extended; -- re-add TOAST table
@@ -1536,6 +1536,9 @@ select reltoastrelid <> 0 as has_toast_table
from pg_class
where oid = 'test_storage'::regclass;
+-- check STORAGE correctness
+create table test_storage_failed (a text, b int storage extended);
+
-- test that SET STORAGE propagates to index correctly
create index test_storage_idx on test_storage (b, a);
alter table test_storage alter column a set storage external;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-30 05:50:04 +0000