Skip to content

Commit fec4342

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 e0d8f49 commit fec4342

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
@@ -160,6 +160,53 @@ makeWholeRowVar(RangeTblEntry *rte,
160160
varlevelsup);
161161
break;
162162

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

165212
/*
@@ -216,8 +263,8 @@ makeWholeRowVar(RangeTblEntry *rte,
216263
default:
217264

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

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

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -764,8 +764,14 @@ preprocess_function_rtes(PlannerInfo *root)
764764
rte->rtekind = RTE_SUBQUERY;
765765
rte->subquery = funcquery;
766766
rte->security_barrier = false;
767-
/* Clear fields that should not be set in a subquery RTE */
768-
rte->functions = NIL;
767+
768+
/*
769+
* Clear fields that should not be set in a subquery RTE.
770+
* However, we leave rte->functions filled in for the moment,
771+
* in case makeWholeRowVar needs to consult it. We'll clear
772+
* it in setrefs.c (see add_rte_to_flat_rtable) so that this
773+
* abuse of the data structure doesn't escape the planner.
774+
*/
769775
rte->funcordinality = false;
770776
}
771777
}

‎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)