Skip to content

Commit d3a29ae

Browse files
tglsfdctglsfdc
committed
Build whole-row Vars the same way during parsing and planning.
makeWholeRowVar() has different rules for constructing a whole-row Var depending on the kind of RTE it's representing. This turns out to be problematic because the rewriter and planner can convert view RTEs and set-returning-function RTEs into subquery RTEs; so a whole-row Var made during planning might look different from one made by the parser. In isolation this doesn't cause any problem, but if a query contains Vars made both ways for the same varno, there are cross-checks in the executor 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 result from an inlined RTE as it would have for the original. For an inlined view, we can use RangeTblEntry.relid to detect that this had been a view RTE. For inlined SRFs, make a data structure definition change akin to commit 47bb9db, and say that we won't clear RangeTblEntry.functions until the end of planning. That allows makeWholeRowVar() to repeat 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.com Backpatch-through: 13
1 parent 584f17a commit d3a29ae

File tree

4 files changed

+138
-4
lines changed

4 files changed

+138
-4
lines changed

‎src/backend/nodes/makefuncs.c

Lines changed: 49 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -157,6 +157,53 @@ makeWholeRowVar(RangeTblEntry *rte,
157157
varlevelsup);
158158
break;
159159

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

162209
/*
@@ -213,8 +260,8 @@ makeWholeRowVar(RangeTblEntry *rte,
213260
default:
214261

215262
/*
216-
* RTE is a join, subselect, tablefunc, or VALUES. We represent
217-
* this as a whole-row Var of RECORD type. (Note that in most
263+
* RTE is a join, tablefunc, VALUES, CTE, etc. We represent these
264+
* cases as a whole-row Var of RECORD type. (Note that in most
218265
* cases the Var will be expanded to a RowExpr during planning,
219266
* but that is not our concern here.)
220267
*/

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

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -664,8 +664,14 @@ preprocess_function_rtes(PlannerInfo *root)
664664
rte->rtekind = RTE_SUBQUERY;
665665
rte->subquery = funcquery;
666666
rte->security_barrier = false;
667-
/* Clear fields that should not be set in a subquery RTE */
668-
rte->functions = NIL;
667+
668+
/*
669+
* Clear fields that should not be set in a subquery RTE.
670+
* However, we leave rte->functions filled in for the moment,
671+
* in case makeWholeRowVar needs to consult it. We'll clear
672+
* it in setrefs.c (see add_rte_to_flat_rtable) so that this
673+
* abuse of the data structure doesn't escape the planner.
674+
*/
669675
rte->funcordinality = false;
670676
}
671677
}

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

Lines changed: 57 additions & 0 deletions
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);

‎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_v AS SELECT * FROM foo OFFSET 0;
137+
UPDATE foo SET f2 = foo_v.f2 FROM foo_v WHERE foo_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+
CREATE FUNCTION foo_f() RETURNS SETOF foo AS
143+
$$ SELECT * FROM foo OFFSET 0 $$ LANGUAGE sql STABLE;
144+
UPDATE foo SET f2 = foo_f.f2 FROM foo_f() WHERE foo_f.f1 = foo.f1
145+
RETURNING foo_f;
146+
SELECT * FROM foo;
147+
DROP FUNCTION foo_f();
148+
149+
-- As above, but SRF is defined to return a composite type
150+
CREATE TYPE foo_t AS (f1 int, f2 text, f3 int, f4 int8);
151+
CREATE FUNCTION foo_f() RETURNS SETOF foo_t AS
152+
$$ SELECT * FROM foo OFFSET 0 $$ LANGUAGE sql STABLE;
153+
UPDATE foo SET f2 = foo_f.f2 FROM foo_f() WHERE foo_f.f1 = foo.f1
154+
RETURNING foo_f;
155+
SELECT * FROM foo;
156+
DROP FUNCTION foo_f();
157+
DROP TYPE foo_t;
158+
135159
-- Try a join case
136160

137161
CREATE TEMP TABLE joinme (f2j text, other int);

0 commit comments

Comments
 (0)