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

Commit4b74ebf

Browse files
committed
When creating materialized views, use REFRESH to load data.
Previously, CREATE MATERIALIZED VIEW ... WITH DATA populated the MVthe same way as CREATE TABLE ... AS.Instead, reuse the REFRESH logic, which locks down security-restrictedoperations and restricts the search_path. This reduces the chance thata subsequent refresh will fail.Reported-by: Noah MischBackpatch-through: 17Discussion:https://postgr.es/m/20240630222344.db.nmisch@google.com
1 parent0a8ca12 commit4b74ebf

File tree

4 files changed

+60
-43
lines changed

4 files changed

+60
-43
lines changed

‎src/backend/commands/createas.c

Lines changed: 15 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -225,10 +225,8 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
225225
Query*query=castNode(Query,stmt->query);
226226
IntoClause*into=stmt->into;
227227
boolis_matview= (into->viewQuery!=NULL);
228+
booldo_refresh= false;
228229
DestReceiver*dest;
229-
Oidsave_userid=InvalidOid;
230-
intsave_sec_context=0;
231-
intsave_nestlevel=0;
232230
ObjectAddressaddress;
233231
List*rewritten;
234232
PlannedStmt*plan;
@@ -263,18 +261,13 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
263261
Assert(query->commandType==CMD_SELECT);
264262

265263
/*
266-
* For materialized views, lock down security-restricted operations and
267-
* arrange to make GUC variable changes local to this command. This is
268-
* not necessary for security, but this keeps the behavior similar to
269-
* REFRESH MATERIALIZED VIEW. Otherwise, one could create a materialized
270-
* view not possible to refresh.
264+
* For materialized views, always skip data during table creation, and use
265+
* REFRESH instead (see below).
271266
*/
272267
if (is_matview)
273268
{
274-
GetUserIdAndSecContext(&save_userid,&save_sec_context);
275-
SetUserIdAndSecContext(save_userid,
276-
save_sec_context |SECURITY_RESTRICTED_OPERATION);
277-
save_nestlevel=NewGUCNestLevel();
269+
do_refresh= !into->skipData;
270+
into->skipData= true;
278271
}
279272

280273
if (into->skipData)
@@ -346,13 +339,18 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
346339
PopActiveSnapshot();
347340
}
348341

349-
if (is_matview)
342+
/*
343+
* For materialized views, reuse the REFRESH logic, which locks down
344+
* security-restricted operations and restricts the search_path. This
345+
* reduces the chance that a subsequent refresh will fail.
346+
*/
347+
if (do_refresh)
350348
{
351-
/* Roll back any GUC changes */
352-
AtEOXact_GUC(false,save_nestlevel);
349+
RefreshMatViewByOid(address.objectId, false, false,
350+
pstate->p_sourcetext,NULL,qc);
353351

354-
/* Restore userid and security context */
355-
SetUserIdAndSecContext(save_userid,save_sec_context);
352+
if (qc)
353+
qc->commandTag=CMDTAG_SELECT;
356354
}
357355

358356
returnaddress;

‎src/backend/commands/matview.c

Lines changed: 40 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -112,15 +112,44 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
112112
/*
113113
* ExecRefreshMatView -- execute a REFRESH MATERIALIZED VIEW command
114114
*
115+
* If WITH NO DATA was specified, this is effectively like a TRUNCATE;
116+
* otherwise it is like a TRUNCATE followed by an INSERT using the SELECT
117+
* statement associated with the materialized view. The statement node's
118+
* skipData field shows whether the clause was used.
119+
*/
120+
ObjectAddress
121+
ExecRefreshMatView(RefreshMatViewStmt*stmt,constchar*queryString,
122+
ParamListInfoparams,QueryCompletion*qc)
123+
{
124+
OidmatviewOid;
125+
LOCKMODElockmode;
126+
127+
/* Determine strength of lock needed. */
128+
lockmode=stmt->concurrent ?ExclusiveLock :AccessExclusiveLock;
129+
130+
/*
131+
* Get a lock until end of transaction.
132+
*/
133+
matviewOid=RangeVarGetRelidExtended(stmt->relation,
134+
lockmode,0,
135+
RangeVarCallbackMaintainsTable,
136+
NULL);
137+
138+
returnRefreshMatViewByOid(matviewOid,stmt->skipData,stmt->concurrent,
139+
queryString,params,qc);
140+
}
141+
142+
/*
143+
* RefreshMatViewByOid -- refresh materialized view by OID
144+
*
115145
* This refreshes the materialized view by creating a new table and swapping
116146
* the relfilenumbers of the new table and the old materialized view, so the OID
117147
* of the original materialized view is preserved. Thus we do not lose GRANT
118148
* nor references to this materialized view.
119149
*
120-
* If WITH NO DATA was specified, this is effectively like a TRUNCATE;
121-
* otherwise it is like a TRUNCATE followed by an INSERT using the SELECT
122-
* statement associated with the materialized view. The statement node's
123-
* skipData field shows whether the clause was used.
150+
* If skipData is true, this is effectively like a TRUNCATE; otherwise it is
151+
* like a TRUNCATE followed by an INSERT using the SELECT statement associated
152+
* with the materialized view.
124153
*
125154
* Indexes are rebuilt too, via REINDEX. Since we are effectively bulk-loading
126155
* the new heap, it's better to create the indexes afterwards than to fill them
@@ -130,10 +159,10 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
130159
* reflect the result set of the materialized view's query.
131160
*/
132161
ObjectAddress
133-
ExecRefreshMatView(RefreshMatViewStmt*stmt,constchar*queryString,
134-
ParamListInfoparams,QueryCompletion*qc)
162+
RefreshMatViewByOid(OidmatviewOid,boolskipData,boolconcurrent,
163+
constchar*queryString,ParamListInfoparams,
164+
QueryCompletion*qc)
135165
{
136-
OidmatviewOid;
137166
RelationmatviewRel;
138167
RewriteRule*rule;
139168
List*actions;
@@ -143,25 +172,12 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
143172
OidOIDNewHeap;
144173
DestReceiver*dest;
145174
uint64processed=0;
146-
boolconcurrent;
147-
LOCKMODElockmode;
148175
charrelpersistence;
149176
Oidsave_userid;
150177
intsave_sec_context;
151178
intsave_nestlevel;
152179
ObjectAddressaddress;
153180

154-
/* Determine strength of lock needed. */
155-
concurrent=stmt->concurrent;
156-
lockmode=concurrent ?ExclusiveLock :AccessExclusiveLock;
157-
158-
/*
159-
* Get a lock until end of transaction.
160-
*/
161-
matviewOid=RangeVarGetRelidExtended(stmt->relation,
162-
lockmode,0,
163-
RangeVarCallbackMaintainsTable,
164-
NULL);
165181
matviewRel=table_open(matviewOid,NoLock);
166182
relowner=matviewRel->rd_rel->relowner;
167183

@@ -190,7 +206,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
190206
errmsg("CONCURRENTLY cannot be used when the materialized view is not populated")));
191207

192208
/* Check that conflicting options have not been specified. */
193-
if (concurrent&&stmt->skipData)
209+
if (concurrent&&skipData)
194210
ereport(ERROR,
195211
(errcode(ERRCODE_SYNTAX_ERROR),
196212
errmsg("%s and %s options cannot be used together",
@@ -275,7 +291,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
275291
* Tentatively mark the matview as populated or not (this will roll back
276292
* if we fail later).
277293
*/
278-
SetMatViewPopulatedState(matviewRel, !stmt->skipData);
294+
SetMatViewPopulatedState(matviewRel, !skipData);
279295

280296
/* Concurrent refresh builds new data in temp tablespace, and does diff. */
281297
if (concurrent)
@@ -301,7 +317,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
301317
dest=CreateTransientRelDestReceiver(OIDNewHeap);
302318

303319
/* Generate the data, if wanted. */
304-
if (!stmt->skipData)
320+
if (!skipData)
305321
processed=refresh_matview_datafill(dest,dataQuery,queryString);
306322

307323
/* Make the matview match the newly generated data. */
@@ -333,7 +349,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
333349
* inserts and deletes it issues get counted by lower-level code.)
334350
*/
335351
pgstat_count_truncate(matviewRel);
336-
if (!stmt->skipData)
352+
if (!skipData)
337353
pgstat_count_heap_insert(matviewRel,processed);
338354
}
339355

‎src/include/commands/matview.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,9 @@ extern void SetMatViewPopulatedState(Relation relation, bool newstate);
2525

2626
externObjectAddressExecRefreshMatView(RefreshMatViewStmt*stmt,constchar*queryString,
2727
ParamListInfoparams,QueryCompletion*qc);
28+
externObjectAddressRefreshMatViewByOid(OidmatviewOid,boolskipData,boolconcurrent,
29+
constchar*queryString,ParamListInfoparams,
30+
QueryCompletion*qc);
2831

2932
externDestReceiver*CreateTransientRelDestReceiver(Oidtransientoid);
3033

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -129,8 +129,8 @@ $$;
129129
CREATE TABLE test_maint(i INT);
130130
INSERT INTO test_maint VALUES (1), (2);
131131
CREATE MATERIALIZED VIEW test_maint_mv AS SELECT fn(i) FROM test_maint;
132-
NOTICE: current search_path:test_maint_search_path
133-
NOTICE: current search_path:test_maint_search_path
132+
NOTICE: current search_path:pg_catalog, pg_temp
133+
NOTICE: current search_path:pg_catalog, pg_temp
134134
-- the following commands should see search_path as pg_catalog, pg_temp
135135
CREATE INDEX test_maint_idx ON test_maint_search_path.test_maint (fn(i));
136136
NOTICE: current search_path: pg_catalog, pg_temp

0 commit comments

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