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

Commitd61a6ca

Browse files
committed
Add support for DEFAULT in ALTER TABLE .. SET ACCESS METHOD
This option can be used to switch a relation to use the access methodset by default_table_access_method when running the command.This has come up when discussing the possibility to support settingpg_class.relam for partitioned tables (left out here as future work),while being useful on its own for relations with physical storage asthese must have an access method set.Per suggestion from Justin Pryzby.Author: Michael PaquierReviewed-by: Justin PryzbyDiscussion:https://postgr.es/m/ZeCZ89xAVFeOmrQC@pryzbyj2023
1 parent4f8c1e7 commitd61a6ca

File tree

6 files changed

+52
-6
lines changed

6 files changed

+52
-6
lines changed

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

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -76,7 +76,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
7676
CLUSTER ON <replaceable class="parameter">index_name</replaceable>
7777
SET WITHOUT CLUSTER
7878
SET WITHOUT OIDS
79-
SET ACCESS METHOD <replaceable class="parameter">new_access_method</replaceable>
79+
SET ACCESS METHOD{<replaceable class="parameter">new_access_method</replaceable> | DEFAULT }
8080
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
8181
SET { LOGGED | UNLOGGED }
8282
SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
@@ -733,7 +733,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
733733
<listitem>
734734
<para>
735735
This form changes the access method of the table by rewriting it. See
736-
<xref linkend="tableam"/> for more information.
736+
<xref linkend="tableam"/> for more information. Writing
737+
<literal>DEFAULT</literal> changes the access method of the table
738+
to <xref linkend="guc-default-table-access-method"/>.
737739
</para>
738740
</listitem>
739741
</varlistentry>

‎src/backend/commands/tablecmds.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15202,14 +15202,16 @@ ATExecDropCluster(Relation rel, LOCKMODE lockmode)
1520215202
*
1520315203
* Check that access method exists. If it is the same as the table's current
1520415204
* access method, it is a no-op. Otherwise, a table rewrite is necessary.
15205+
* If amname is NULL, select default_table_access_method as access method.
1520515206
*/
1520615207
static void
1520715208
ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)
1520815209
{
1520915210
Oidamoid;
1521015211

1521115212
/* Check that the table access method exists */
15212-
amoid = get_table_am_oid(amname, false);
15213+
amoid = get_table_am_oid(amname ? amname : default_table_access_method,
15214+
false);
1521315215

1521415216
if (rel->rd_rel->relam == amoid)
1521515217
return;

‎src/backend/parser/gram.y

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -338,6 +338,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
338338
%type<list>alter_identity_column_option_list
339339
%type<defelt>alter_identity_column_option
340340
%type<node>set_statistics_value
341+
%type<str>set_access_method_name
341342

342343
%type<list>createdb_opt_listcreatedb_opt_itemscopy_opt_list
343344
transaction_mode_list
@@ -2859,8 +2860,8 @@ alter_table_cmd:
28592860
n->newowner =$3;
28602861
$$ = (Node *) n;
28612862
}
2862-
/* ALTER TABLE <name> SET ACCESS METHOD <amname>*/
2863-
|SETACCESSMETHODname
2863+
/* ALTER TABLE <name> SET ACCESS METHOD{<amname> | DEFAULT }*/
2864+
|SETACCESSMETHODset_access_method_name
28642865
{
28652866
AlterTableCmd *n = makeNode(AlterTableCmd);
28662867

@@ -3076,6 +3077,11 @@ set_statistics_value:
30763077
|DEFAULT{$$ =NULL; }
30773078
;
30783079

3080+
set_access_method_name:
3081+
ColId{$$ =$1; }
3082+
|DEFAULT{$$ =NULL; }
3083+
;
3084+
30793085
PartitionBoundSpec:
30803086
/* a HASH partition*/
30813087
FORVALUESWITH'('hash_partbound')'

‎src/bin/psql/tab-complete.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2563,7 +2563,8 @@ psql_completion(const char *text, int start, int end)
25632563
* AMs.
25642564
*/
25652565
elseif (Matches("ALTER","TABLE",MatchAny,"SET","ACCESS","METHOD"))
2566-
COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
2566+
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_table_access_methods,
2567+
"DEFAULT");
25672568

25682569
/*
25692570
* If we have ALTER TABLE <sth> SET TABLESPACE provide a list of

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -283,6 +283,27 @@ SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
283283
9 | 1
284284
(1 row)
285285

286+
-- DEFAULT access method
287+
BEGIN;
288+
SET LOCAL default_table_access_method TO heap2;
289+
ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
290+
SELECT amname FROM pg_class c, pg_am am
291+
WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
292+
amname
293+
--------
294+
heap2
295+
(1 row)
296+
297+
SET LOCAL default_table_access_method TO heap;
298+
ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
299+
SELECT amname FROM pg_class c, pg_am am
300+
WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
301+
amname
302+
--------
303+
heap
304+
(1 row)
305+
306+
ROLLBACK;
286307
-- ALTER MATERIALIZED VIEW SET ACCESS METHOD
287308
CREATE MATERIALIZED VIEW heapmv USING heap AS SELECT * FROM heaptable;
288309
SELECT amname FROM pg_class c, pg_am am
@@ -309,6 +330,8 @@ SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heapmv;
309330
-- No support for multiple subcommands
310331
ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
311332
ERROR: cannot have multiple SET ACCESS METHOD subcommands
333+
ALTER TABLE heaptable SET ACCESS METHOD DEFAULT, SET ACCESS METHOD heap2;
334+
ERROR: cannot have multiple SET ACCESS METHOD subcommands
312335
ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
313336
ERROR: cannot have multiple SET ACCESS METHOD subcommands
314337
DROP MATERIALIZED VIEW heapmv;

‎src/test/regress/sql/create_am.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -188,6 +188,17 @@ ALTER TABLE heaptable SET ACCESS METHOD heap2;
188188
SELECT amnameFROM pg_class c, pg_am am
189189
WHEREc.relam=am.oidANDc.oid='heaptable'::regclass;
190190
SELECTCOUNT(a),COUNT(1) FILTER(WHERE a=1)FROM heaptable;
191+
-- DEFAULT access method
192+
BEGIN;
193+
SET LOCAL default_table_access_method TO heap2;
194+
ALTERTABLE heaptableSET ACCESS METHOD DEFAULT;
195+
SELECT amnameFROM pg_class c, pg_am am
196+
WHEREc.relam=am.oidANDc.oid='heaptable'::regclass;
197+
SET LOCAL default_table_access_method TO heap;
198+
ALTERTABLE heaptableSET ACCESS METHOD DEFAULT;
199+
SELECT amnameFROM pg_class c, pg_am am
200+
WHEREc.relam=am.oidANDc.oid='heaptable'::regclass;
201+
ROLLBACK;
191202
-- ALTER MATERIALIZED VIEW SET ACCESS METHOD
192203
CREATE MATERIALIZED VIEW heapmv USING heapASSELECT*FROM heaptable;
193204
SELECT amnameFROM pg_class c, pg_am am
@@ -198,6 +209,7 @@ SELECT amname FROM pg_class c, pg_am am
198209
SELECTCOUNT(a),COUNT(1) FILTER(WHERE a=1)FROM heapmv;
199210
-- No support for multiple subcommands
200211
ALTERTABLE heaptableSET ACCESS METHOD heap,SET ACCESS METHOD heap2;
212+
ALTERTABLE heaptableSET ACCESS METHOD DEFAULT,SET ACCESS METHOD heap2;
201213
ALTER MATERIALIZED VIEW heapmvSET ACCESS METHOD heap,SET ACCESS METHOD heap2;
202214
DROP MATERIALIZED VIEW heapmv;
203215
DROPTABLE heaptable;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp