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
Appearance settings

Commit f41872d

Browse filesBrowse files
alvherrealvherre
committed
Implement ALTER TABLE .. SET LOGGED / UNLOGGED
This enables changing permanent (logged) tables to unlogged and vice-versa. (Docs for ALTER TABLE / SET TABLESPACE got shuffled in an order that hopefully makes more sense than the original.) Author: Fabrízio de Royes Mello Reviewed by: Christoph Berg, Andres Freund, Thom Brown Some tweaking by Álvaro Herrera
1 parent 01d15a2 commit f41872d
Copy full SHA for f41872d

File tree

Expand file treeCollapse file tree

11 files changed

+472
-55
lines changed
Filter options
Expand file treeCollapse file tree

11 files changed

+472
-55
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/alter_table.sgml
+38-26Lines changed: 38 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -63,14 +63,15 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
6363
SET WITHOUT CLUSTER
6464
SET WITH OIDS
6565
SET WITHOUT OIDS
66+
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
67+
SET {LOGGED | UNLOGGED}
6668
SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
6769
RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
6870
INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
6971
NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
7072
OF <replaceable class="PARAMETER">type_name</replaceable>
7173
NOT OF
7274
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
73-
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
7475
REPLICA IDENTITY {DEFAULT | USING INDEX <replaceable class="PARAMETER">index_name</replaceable> | FULL | NOTHING}
7576

7677
<phrase>and <replaceable class="PARAMETER">table_constraint_using_index</replaceable> is:</phrase>
@@ -478,6 +479,42 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
478479
</listitem>
479480
</varlistentry>
480481

482+
<varlistentry>
483+
<term><literal>SET TABLESPACE</literal></term>
484+
<listitem>
485+
<para>
486+
This form changes the table's tablespace to the specified tablespace and
487+
moves the data file(s) associated with the table to the new tablespace.
488+
Indexes on the table, if any, are not moved; but they can be moved
489+
separately with additional <literal>SET TABLESPACE</literal> commands.
490+
All tables in the current database in a tablespace can be moved by using
491+
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
492+
to be moved first and then move each one. This form also supports
493+
<literal>OWNED BY</literal>, which will only move tables owned by the
494+
roles specified. If the <literal>NOWAIT</literal> option is specified
495+
then the command will fail if it is unable to acquire all of the locks
496+
required immediately. Note that system catalogs are not moved by this
497+
command, use <command>ALTER DATABASE</command> or explicit
498+
<command>ALTER TABLE</command> invocations instead if desired. The
499+
<literal>information_schema</literal> relations are not considered part
500+
of the system catalogs and will be moved.
501+
See also
502+
<xref linkend="SQL-CREATETABLESPACE">.
503+
</para>
504+
</listitem>
505+
</varlistentry>
506+
507+
<varlistentry>
508+
<term><literal>SET {LOGGED | UNLOGGED}</literal></term>
509+
<listitem>
510+
<para>
511+
This form changes the table from unlogged to logged or vice-versa
512+
(see <xref linkend="SQL-CREATETABLE-UNLOGGED">). It cannot be applied
513+
to a temporary table.
514+
</para>
515+
</listitem>
516+
</varlistentry>
517+
481518
<varlistentry>
482519
<term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
483520
<listitem>
@@ -591,31 +628,6 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
591628
</listitem>
592629
</varlistentry>
593630

594-
<varlistentry>
595-
<term><literal>SET TABLESPACE</literal></term>
596-
<listitem>
597-
<para>
598-
This form changes the table's tablespace to the specified tablespace and
599-
moves the data file(s) associated with the table to the new tablespace.
600-
Indexes on the table, if any, are not moved; but they can be moved
601-
separately with additional <literal>SET TABLESPACE</literal> commands.
602-
All tables in the current database in a tablespace can be moved by using
603-
the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
604-
to be moved first and then move each one. This form also supports
605-
<literal>OWNED BY</literal>, which will only move tables owned by the
606-
roles specified. If the <literal>NOWAIT</literal> option is specified
607-
then the command will fail if it is unable to acquire all of the locks
608-
required immediately. Note that system catalogs are not moved by this
609-
command, use <command>ALTER DATABASE</command> or explicit
610-
<command>ALTER TABLE</command> invocations instead if desired. The
611-
<literal>information_schema</literal> relations are not considered part
612-
of the system catalogs and will be moved.
613-
See also
614-
<xref linkend="SQL-CREATETABLESPACE">.
615-
</para>
616-
</listitem>
617-
</varlistentry>
618-
619631
<varlistentry id="SQL-CREATETABLE-REPLICA-IDENTITY">
620632
<term><literal>REPLICA IDENTITY</literal></term>
621633
<listitem>

‎src/backend/commands/cluster.c

Copy file name to clipboardExpand all lines: src/backend/commands/cluster.c
+27-19Lines changed: 27 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -574,7 +574,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
574574
heap_close(OldHeap, NoLock);
575575

576576
/* Create the transient table that will receive the re-ordered data */
577-
OIDNewHeap = make_new_heap(tableOid, tableSpace, false,
577+
OIDNewHeap = make_new_heap(tableOid, tableSpace,
578+
OldHeap->rd_rel->relpersistence,
578579
AccessExclusiveLock);
579580

580581
/* Copy the heap data into the new table in the desired order */
@@ -595,13 +596,14 @@ rebuild_relation(Relation OldHeap, Oid indexOid, bool verbose)
595596
* Create the transient table that will be filled with new data during
596597
* CLUSTER, ALTER TABLE, and similar operations. The transient table
597598
* duplicates the logical structure of the OldHeap, but is placed in
598-
* NewTableSpace which might be different from OldHeap's.
599+
* NewTableSpace which might be different from OldHeap's. Also, it's built
600+
* with the specified persistence, which might differ from the original's.
599601
*
600602
* After this, the caller should load the new heap with transferred/modified
601603
* data, then call finish_heap_swap to complete the operation.
602604
*/
603605
Oid
604-
make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
606+
make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, char relpersistence,
605607
LOCKMODE lockmode)
606608
{
607609
TupleDesc OldHeapDesc;
@@ -613,7 +615,6 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
613615
Datum reloptions;
614616
bool isNull;
615617
Oid namespaceid;
616-
char relpersistence;
617618

618619
OldHeap = heap_open(OIDOldHeap, lockmode);
619620
OldHeapDesc = RelationGetDescr(OldHeap);
@@ -636,16 +637,10 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, bool forcetemp,
636637
if (isNull)
637638
reloptions = (Datum) 0;
638639

639-
if (forcetemp)
640-
{
640+
if (relpersistence == RELPERSISTENCE_TEMP)
641641
namespaceid = LookupCreationNamespace("pg_temp");
642-
relpersistence = RELPERSISTENCE_TEMP;
643-
}
644642
else
645-
{
646643
namespaceid = RelationGetNamespace(OldHeap);
647-
relpersistence = OldHeap->rd_rel->relpersistence;
648-
}
649644

650645
/*
651646
* Create the new heap, using a temporary name in the same namespace as
@@ -1109,8 +1104,10 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose,
11091104
/*
11101105
* Swap the physical files of two given relations.
11111106
*
1112-
* We swap the physical identity (reltablespace and relfilenode) while
1113-
* keeping the same logical identities of the two relations.
1107+
* We swap the physical identity (reltablespace, relfilenode) while keeping the
1108+
* same logical identities of the two relations. relpersistence is also
1109+
* swapped, which is critical since it determines where buffers live for each
1110+
* relation.
11141111
*
11151112
* We can swap associated TOAST data in either of two ways: recursively swap
11161113
* the physical content of the toast tables (and their indexes), or swap the
@@ -1146,6 +1143,7 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
11461143
Oid relfilenode1,
11471144
relfilenode2;
11481145
Oid swaptemp;
1146+
char swptmpchr;
11491147
CatalogIndexState indstate;
11501148

11511149
/* We need writable copies of both pg_class tuples. */
@@ -1166,7 +1164,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
11661164

11671165
if (OidIsValid(relfilenode1) && OidIsValid(relfilenode2))
11681166
{
1169-
/* Normal non-mapped relations: swap relfilenodes and reltablespaces */
1167+
/*
1168+
* Normal non-mapped relations: swap relfilenodes, reltablespaces,
1169+
* relpersistence
1170+
*/
11701171
Assert(!target_is_pg_class);
11711172

11721173
swaptemp = relform1->relfilenode;
@@ -1177,6 +1178,10 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
11771178
relform1->reltablespace = relform2->reltablespace;
11781179
relform2->reltablespace = swaptemp;
11791180

1181+
swptmpchr = relform1->relpersistence;
1182+
relform1->relpersistence = relform2->relpersistence;
1183+
relform2->relpersistence = swptmpchr;
1184+
11801185
/* Also swap toast links, if we're swapping by links */
11811186
if (!swap_toast_by_content)
11821187
{
@@ -1196,15 +1201,18 @@ swap_relation_files(Oid r1, Oid r2, bool target_is_pg_class,
11961201
NameStr(relform1->relname));
11971202

11981203
/*
1199-
* We can't change the tablespace of a mapped rel, and we can't handle
1200-
* toast link swapping for one either, because we must not apply any
1201-
* critical changes to its pg_class row. These cases should be
1202-
* prevented by upstream permissions tests, so this check is a
1203-
* non-user-facing emergency backstop.
1204+
* We can't change the tablespace nor persistence of a mapped rel, and
1205+
* we can't handle toast link swapping for one either, because we must
1206+
* not apply any critical changes to its pg_class row. These cases
1207+
* should be prevented by upstream permissions tests, so these checks
1208+
* are non-user-facing emergency backstop.
12041209
*/
12051210
if (relform1->reltablespace != relform2->reltablespace)
12061211
elog(ERROR, "cannot change tablespace of mapped relation \"%s\"",
12071212
NameStr(relform1->relname));
1213+
if (relform1->relpersistence != relform2->relpersistence)
1214+
elog(ERROR, "cannot change persistence of mapped relation \"%s\"",
1215+
NameStr(relform1->relname));
12081216
if (!swap_toast_by_content &&
12091217
(relform1->reltoastrelid || relform2->reltoastrelid))
12101218
elog(ERROR, "cannot swap toast by links for mapped relation \"%s\"",

‎src/backend/commands/matview.c

Copy file name to clipboardExpand all lines: src/backend/commands/matview.c
+8-1Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -147,6 +147,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
147147
DestReceiver *dest;
148148
bool concurrent;
149149
LOCKMODE lockmode;
150+
char relpersistence;
150151

151152
/* Determine strength of lock needed. */
152153
concurrent = stmt->concurrent;
@@ -233,9 +234,15 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
233234

234235
/* Concurrent refresh builds new data in temp tablespace, and does diff. */
235236
if (concurrent)
237+
{
236238
tableSpace = GetDefaultTablespace(RELPERSISTENCE_TEMP);
239+
relpersistence = RELPERSISTENCE_TEMP;
240+
}
237241
else
242+
{
238243
tableSpace = matviewRel->rd_rel->reltablespace;
244+
relpersistence = matviewRel->rd_rel->relpersistence;
245+
}
239246

240247
owner = matviewRel->rd_rel->relowner;
241248

@@ -244,7 +251,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
244251
* it against access by any other process until commit (by which time it
245252
* will be gone).
246253
*/
247-
OIDNewHeap = make_new_heap(matviewOid, tableSpace, concurrent,
254+
OIDNewHeap = make_new_heap(matviewOid, tableSpace, relpersistence,
248255
ExclusiveLock);
249256
LockRelationOid(OIDNewHeap, AccessExclusiveLock);
250257
dest = CreateTransientRelDestReceiver(OIDNewHeap);

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.