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

Commitf4e7756

Browse files
committed
Build whole-row Vars the same way during parsing and planning.
makeWholeRowVar() has different rules for constructing awhole-row Var depending on the kind of RTE it's representing.This turns out to be problematic because the rewriter and plannercan convert view RTEs and set-returning-function RTEs intosubquery RTEs; so a whole-row Var made during planning mightlook different from one made by the parser. In isolation thisdoesn't cause any problem, but if a query contains Vars madeboth ways for the same varno, there are cross-checks in theexecutor that will complain. This manifests for UPDATE, DELETE,and MERGE queries that use whole-row table references.To fix, we need makeWholeRowVar() to produce the same resultfrom an inlined RTE as it would have for the original. Foran inlined view, we can use RangeTblEntry.relid to detectthat this had been a view RTE. For inlined SRFs, make adata structure definition change akin to commit47bb9db,and say that we won't clear RangeTblEntry.functions untilthe end of planning. That allows makeWholeRowVar() torepeat what it would have done with the unmodified RTE.Reported-by: Duncan Sands <duncan.sands@deepbluecap.com>Reported-by: Dean Rasheed <dean.a.rasheed@gmail.com>Diagnosed-by: Tender Wang <tndrwang@gmail.com>Author: Tom Lane <tgl@sss.pgh.pa.us>Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>Discussion:https://postgr.es/m/3518c50a-ab18-482f-b916-a37263622501@deepbluecap.comBackpatch-through: 13
1 parent18cd15e commitf4e7756

File tree

4 files changed

+140
-5
lines changed

4 files changed

+140
-5
lines changed

‎src/backend/nodes/makefuncs.c

Lines changed: 49 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -161,6 +161,53 @@ makeWholeRowVar(RangeTblEntry *rte,
161161
varlevelsup);
162162
break;
163163

164+
caseRTE_SUBQUERY:
165+
166+
/*
167+
* For a standard subquery, the Var should be of RECORD type.
168+
* However, if we're looking at a subquery that was expanded from
169+
* a view or SRF (only possible during planning), we must use the
170+
* appropriate rowtype, so that the resulting Var has the same
171+
* type that we would have produced from the original RTE.
172+
*/
173+
if (OidIsValid(rte->relid))
174+
{
175+
/* Subquery was expanded from a view */
176+
toid=get_rel_type_id(rte->relid);
177+
if (!OidIsValid(toid))
178+
ereport(ERROR,
179+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
180+
errmsg("relation \"%s\" does not have a composite type",
181+
get_rel_name(rte->relid))));
182+
}
183+
elseif (rte->functions)
184+
{
185+
/*
186+
* Subquery was expanded from a set-returning function. That
187+
* would not have happened if there's more than one function
188+
* or ordinality was requested. We also needn't worry about
189+
* the allowScalar case, since the planner doesn't use that.
190+
* Otherwise this must match the RTE_FUNCTION code below.
191+
*/
192+
Assert(!allowScalar);
193+
fexpr= ((RangeTblFunction*)linitial(rte->functions))->funcexpr;
194+
toid=exprType(fexpr);
195+
if (!type_is_rowtype(toid))
196+
toid=RECORDOID;
197+
}
198+
else
199+
{
200+
/* Normal subquery-in-FROM */
201+
toid=RECORDOID;
202+
}
203+
result=makeVar(varno,
204+
InvalidAttrNumber,
205+
toid,
206+
-1,
207+
InvalidOid,
208+
varlevelsup);
209+
break;
210+
164211
caseRTE_FUNCTION:
165212

166213
/*
@@ -217,8 +264,8 @@ makeWholeRowVar(RangeTblEntry *rte,
217264
default:
218265

219266
/*
220-
* RTE is a join,subselect, tablefunc, or VALUES. We represent
221-
*this as a whole-row Var of RECORD type. (Note that in most
267+
* RTE is a join,tablefunc, VALUES, CTE, etc. We represent these
268+
*cases as a whole-row Var of RECORD type. (Note that in most
222269
* cases the Var will be expanded to a RowExpr during planning,
223270
* but that is not our concern here.)
224271
*/

‎src/backend/optimizer/prep/prepjointree.c

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -914,8 +914,14 @@ preprocess_function_rtes(PlannerInfo *root)
914914
rte->rtekind=RTE_SUBQUERY;
915915
rte->subquery=funcquery;
916916
rte->security_barrier= false;
917-
/* Clear fields that should not be set in a subquery RTE */
918-
rte->functions=NIL;
917+
918+
/*
919+
* Clear fields that should not be set in a subquery RTE.
920+
* However, we leave rte->functions filled in for the moment,
921+
* in case makeWholeRowVar needs to consult it. We'll clear
922+
* it in setrefs.c (see add_rte_to_flat_rtable) so that this
923+
* abuse of the data structure doesn't escape the planner.
924+
*/
919925
rte->funcordinality= false;
920926
}
921927
}

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

Lines changed: 59 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -286,6 +286,63 @@ SELECT * FROM voo;
286286
16 | zoo2
287287
(2 rows)
288288

289+
-- Check use of a whole-row variable for an un-flattenable view
290+
CREATE TEMP VIEW foo_v AS SELECT * FROM foo OFFSET 0;
291+
UPDATE foo SET f2 = foo_v.f2 FROM foo_v WHERE foo_v.f1 = foo.f1
292+
RETURNING foo_v;
293+
foo_v
294+
-----------------
295+
(2,more,42,141)
296+
(16,zoo2,57,99)
297+
(2 rows)
298+
299+
SELECT * FROM foo;
300+
f1 | f2 | f3 | f4
301+
----+------+----+-----
302+
2 | more | 42 | 141
303+
16 | zoo2 | 57 | 99
304+
(2 rows)
305+
306+
-- Check use of a whole-row variable for an inlined set-returning function
307+
CREATE FUNCTION foo_f() RETURNS SETOF foo AS
308+
$$ SELECT * FROM foo OFFSET 0 $$ LANGUAGE sql STABLE;
309+
UPDATE foo SET f2 = foo_f.f2 FROM foo_f() WHERE foo_f.f1 = foo.f1
310+
RETURNING foo_f;
311+
foo_f
312+
-----------------
313+
(2,more,42,141)
314+
(16,zoo2,57,99)
315+
(2 rows)
316+
317+
SELECT * FROM foo;
318+
f1 | f2 | f3 | f4
319+
----+------+----+-----
320+
2 | more | 42 | 141
321+
16 | zoo2 | 57 | 99
322+
(2 rows)
323+
324+
DROP FUNCTION foo_f();
325+
-- As above, but SRF is defined to return a composite type
326+
CREATE TYPE foo_t AS (f1 int, f2 text, f3 int, f4 int8);
327+
CREATE FUNCTION foo_f() RETURNS SETOF foo_t AS
328+
$$ SELECT * FROM foo OFFSET 0 $$ LANGUAGE sql STABLE;
329+
UPDATE foo SET f2 = foo_f.f2 FROM foo_f() WHERE foo_f.f1 = foo.f1
330+
RETURNING foo_f;
331+
foo_f
332+
-----------------
333+
(2,more,42,141)
334+
(16,zoo2,57,99)
335+
(2 rows)
336+
337+
SELECT * FROM foo;
338+
f1 | f2 | f3 | f4
339+
----+------+----+-----
340+
2 | more | 42 | 141
341+
16 | zoo2 | 57 | 99
342+
(2 rows)
343+
344+
DROP FUNCTION foo_f();
345+
DROP TYPE foo_t;
289346
-- Try a join case
290347
CREATE TEMP TABLE joinme (f2j text, other int);
291348
INSERT INTO joinme VALUES('more', 12345);
@@ -726,8 +783,9 @@ NOTICE: UPDATE: (3,zoo2,58,99,54321) -> (3,zoo2,59,7,54321)
726783

727784
-- Test wholerow & dropped column handling
728785
ALTER TABLE foo DROP COLUMN f3 CASCADE;
729-
NOTICE: drop cascades to3 other objects
786+
NOTICE: drop cascades to4 other objects
730787
DETAIL: drop cascades to rule voo_i on view voo
788+
drop cascades to view foo_v
731789
drop cascades to view joinview
732790
drop cascades to rule foo_del_rule on table foo
733791
UPDATE foo SET f4 = f4 + 1 RETURNING old.f3; -- should fail

‎src/test/regress/sql/returning.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,30 @@ DELETE FROM foo WHERE f2 = 'zit' RETURNING *;
132132
SELECT*FROM foo;
133133
SELECT*FROM voo;
134134

135+
-- Check use of a whole-row variable for an un-flattenable view
136+
CREATE TEMP VIEW foo_vASSELECT*FROM foo OFFSET0;
137+
UPDATE fooSET f2=foo_v.f2FROM foo_vWHEREfoo_v.f1=foo.f1
138+
RETURNING foo_v;
139+
SELECT*FROM foo;
140+
141+
-- Check use of a whole-row variable for an inlined set-returning function
142+
CREATEFUNCTIONfoo_f() RETURNS SETOF fooAS
143+
$$SELECT*FROM foo OFFSET0 $$ LANGUAGE sql STABLE;
144+
UPDATE fooSET f2=foo_f.f2FROM foo_f()WHEREfoo_f.f1=foo.f1
145+
RETURNING foo_f;
146+
SELECT*FROM foo;
147+
DROPFUNCTION foo_f();
148+
149+
-- As above, but SRF is defined to return a composite type
150+
CREATETYPEfoo_tAS (f1int, f2text, f3int, f4 int8);
151+
CREATEFUNCTIONfoo_f() RETURNS SETOF foo_tAS
152+
$$SELECT*FROM foo OFFSET0 $$ LANGUAGE sql STABLE;
153+
UPDATE fooSET f2=foo_f.f2FROM foo_f()WHEREfoo_f.f1=foo.f1
154+
RETURNING foo_f;
155+
SELECT*FROM foo;
156+
DROPFUNCTION foo_f();
157+
DROPTYPE foo_t;
158+
135159
-- Try a join case
136160

137161
CREATE TEMP TABLE joinme (f2jtext, otherint);

0 commit comments

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