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

Commiteb8399c

Browse files
committed
Improve handling of SET ACCESS METHOD for ALTER MATERIALIZED VIEW
b048326 has added support for SET ACCESS METHOD in ALTER TABLE, but ithas missed a few things for materialized views:- No documentation for this clause on the ALTER MATERIALIZED VIEW page.- psql tab completion missing.- No regression tests.This commit closes the gap on all the points listed above.Author: Yugo NagataDiscussion:https://postgr.es/m/20220316133337.5dc9740abfa24c25ec9f67f5@sraoss.co.jp
1 parentade2159 commiteb8399c

File tree

4 files changed

+42
-1
lines changed

4 files changed

+42
-1
lines changed

‎doc/src/sgml/ref/alter_materialized_view.sgml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,7 @@ ALTER MATERIALIZED VIEW ALL IN TABLESPACE <replaceable class="parameter">name</r
4343
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET COMPRESSION <replaceable class="parameter">compression_method</replaceable>
4444
CLUSTER ON <replaceable class="parameter">index_name</replaceable>
4545
SET WITHOUT CLUSTER
46+
SET ACCESS METHOD <replaceable class="parameter">new_access_method</replaceable>
4647
SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
4748
SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
4849
RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )

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

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2124,7 +2124,11 @@ psql_completion(const char *text, int start, int end)
21242124
COMPLETE_WITH("TO");
21252125
/* ALTER MATERIALIZED VIEW xxx SET */
21262126
elseif (Matches("ALTER","MATERIALIZED","VIEW",MatchAny,"SET"))
2127-
COMPLETE_WITH("(","SCHEMA","TABLESPACE","WITHOUT CLUSTER");
2127+
COMPLETE_WITH("(","ACCESS METHOD","SCHEMA","TABLESPACE","WITHOUT CLUSTER");
2128+
/* ALTER MATERIALIZED VIEW xxx SET ACCESS METHOD */
2129+
elseif (Matches("ALTER","MATERIALIZED","VIEW",MatchAny,"SET","ACCESS","METHOD"))
2130+
COMPLETE_WITH_QUERY(Query_for_list_of_table_access_methods);
2131+
21282132
/* ALTER POLICY <name> */
21292133
elseif (Matches("ALTER","POLICY"))
21302134
COMPLETE_WITH_QUERY(Query_for_list_of_policies);

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

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -254,9 +254,35 @@ SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
254254
9 | 1
255255
(1 row)
256256

257+
-- ALTER MATERIALIZED VIEW SET ACCESS METHOD
258+
CREATE MATERIALIZED VIEW heapmv USING heap AS SELECT * FROM heaptable;
259+
SELECT amname FROM pg_class c, pg_am am
260+
WHERE c.relam = am.oid AND c.oid = 'heapmv'::regclass;
261+
amname
262+
--------
263+
heap
264+
(1 row)
265+
266+
ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap2;
267+
SELECT amname FROM pg_class c, pg_am am
268+
WHERE c.relam = am.oid AND c.oid = 'heapmv'::regclass;
269+
amname
270+
--------
271+
heap2
272+
(1 row)
273+
274+
SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heapmv;
275+
count | count
276+
-------+-------
277+
9 | 1
278+
(1 row)
279+
257280
-- No support for multiple subcommands
258281
ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
259282
ERROR: cannot have multiple SET ACCESS METHOD subcommands
283+
ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
284+
ERROR: cannot have multiple SET ACCESS METHOD subcommands
285+
DROP MATERIALIZED VIEW heapmv;
260286
DROP TABLE heaptable;
261287
-- No support for partitioned tables.
262288
CREATE TABLE am_partitioned(x INT, y INT)

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -170,8 +170,18 @@ ALTER TABLE heaptable SET ACCESS METHOD heap2;
170170
SELECT amnameFROM pg_class c, pg_am am
171171
WHEREc.relam=am.oidANDc.oid='heaptable'::regclass;
172172
SELECTCOUNT(a),COUNT(1) FILTER(WHERE a=1)FROM heaptable;
173+
-- ALTER MATERIALIZED VIEW SET ACCESS METHOD
174+
CREATE MATERIALIZED VIEW heapmv USING heapASSELECT*FROM heaptable;
175+
SELECT amnameFROM pg_class c, pg_am am
176+
WHEREc.relam=am.oidANDc.oid='heapmv'::regclass;
177+
ALTER MATERIALIZED VIEW heapmvSET ACCESS METHOD heap2;
178+
SELECT amnameFROM pg_class c, pg_am am
179+
WHEREc.relam=am.oidANDc.oid='heapmv'::regclass;
180+
SELECTCOUNT(a),COUNT(1) FILTER(WHERE a=1)FROM heapmv;
173181
-- No support for multiple subcommands
174182
ALTERTABLE heaptableSET ACCESS METHOD heap,SET ACCESS METHOD heap2;
183+
ALTER MATERIALIZED VIEW heapmvSET ACCESS METHOD heap,SET ACCESS METHOD heap2;
184+
DROP MATERIALIZED VIEW heapmv;
175185
DROPTABLE heaptable;
176186
-- No support for partitioned tables.
177187
CREATETABLEam_partitioned(xINT, yINT)

0 commit comments

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