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

Commitba2c6d6

Browse files
committed
Avoid misbehavior when persisting a non-stable cursor.
PersistHoldablePortal has long assumed that it should store theentire output of the query-to-be-persisted, which requires rewindingand re-reading the output. This is problematic if the query is notstable: we might get different row contents, or even a differentnumber of rows, which'd confuse the cursor state mightily.In the case where the cursor is NO SCROLL, this is very easy tosolve: just store the remaining query output, without any rewinding,and tweak the portal's cursor state to match. Aside from removingthe semantic problem, this could be significantly more efficientthan storing the whole output.If the cursor is scrollable, there's not much we can do, but itwas already the case that scrolling a volatile query's result waspretty unsafe. We can just document more clearly that gettingcorrect results from that is not guaranteed.There are already prohibitions in place on using SCROLL withFOR UPDATE/SHARE, which is one way for a SELECT query to havenon-stable results. We could imagine prohibiting SCROLL whenthe query contains volatile functions, but that would beexpensive to enforce. Moreover, it could break applicationsthat work just fine, if they have functions that are in factstable but the user neglected to mark them so. So settle fordocumenting the hazard.While this problem has existed in some guise for a long time,it got a lot worse in v11, which introduced the possibilityof persisting plpgsql cursors (perhaps implicit ones) evenwhen they violate the rules for what can be marked WITH HOLD.Hence, I've chosen to back-patch to v11 but not further.Per bug #17050 from Алексей Булгаков.Discussion:https://postgr.es/m/17050-f77aa827dc85247c@postgresql.org
1 parent444302e commitba2c6d6

File tree

5 files changed

+122
-6
lines changed

5 files changed

+122
-6
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3142,6 +3142,15 @@ DECLARE
31423142
is said to be <firstterm>unbound</firstterm> since it is not bound to
31433143
any particular query.
31443144
</para>
3145+
3146+
<para>
3147+
The <literal>SCROLL</literal> option cannot be used when the cursor's
3148+
query uses <literal>FOR UPDATE/SHARE</literal>. Also, it is
3149+
best to use <literal>NO SCROLL</literal> with a query that involves
3150+
volatile functions. The implementation of <literal>SCROLL</literal>
3151+
assumes that re-reading the query's output will give consistent
3152+
results, which a volatile function might not do.
3153+
</para>
31453154
</sect2>
31463155

31473156
<sect2 id="plpgsql-cursor-opening">

‎doc/src/sgml/ref/declare.sgml

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -239,12 +239,14 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIV
239239

240240
<caution>
241241
<para>
242-
Scrollableand <literal>WITH HOLD</literal>cursors may give unexpected
242+
Scrollable cursors may give unexpected
243243
results if they invoke any volatile functions (see <xref
244244
linkend="xfunc-volatility"/>). When a previously fetched row is
245245
re-fetched, the functions might be re-executed, perhaps leading to
246-
results different from the first time. One workaround for such cases
247-
is to declare the cursor <literal>WITH HOLD</literal> and commit the
246+
results different from the first time. It's best to
247+
specify <literal>NO SCROLL</literal> for a query involving volatile
248+
functions. If that is not practical, one workaround
249+
is to declare the cursor <literal>SCROLL WITH HOLD</literal> and commit the
248250
transaction before reading any rows from it. This will force the
249251
entire output of the cursor to be materialized in temporary storage,
250252
so that volatile functions are executed exactly once for each row.

‎src/backend/commands/portalcmds.c

Lines changed: 16 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -374,10 +374,23 @@ PersistHoldablePortal(Portal portal)
374374
PushActiveSnapshot(queryDesc->snapshot);
375375

376376
/*
377-
* Rewind the executor: we need to store the entire result set in the
378-
* tuplestore, so that subsequent backward FETCHs can be processed.
377+
* If the portal is marked scrollable, we need to store the entire
378+
* result set in the tuplestore, so that subsequent backward FETCHs
379+
* can be processed. Otherwise, store only the not-yet-fetched rows.
380+
* (The latter is not only more efficient, but avoids semantic
381+
* problems if the query's output isn't stable.)
379382
*/
380-
ExecutorRewind(queryDesc);
383+
if (portal->cursorOptions&CURSOR_OPT_SCROLL)
384+
{
385+
ExecutorRewind(queryDesc);
386+
}
387+
else
388+
{
389+
/* We must reset the cursor state as though at start of query */
390+
portal->atStart= true;
391+
portal->atEnd= false;
392+
portal->portalPos=0;
393+
}
381394

382395
/*
383396
* Change the destination to output to the tuplestore. Note we tell

‎src/pl/plpgsql/src/expected/plpgsql_transaction.out

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -335,6 +335,57 @@ SELECT * FROM pg_cursors;
335335
------+-----------+-------------+-----------+---------------+---------------
336336
(0 rows)
337337

338+
-- interaction of FOR UPDATE cursor with subsequent updates (bug #17050)
339+
TRUNCATE test1;
340+
INSERT INTO test1 VALUES (1,'one'), (2,'two'), (3,'three');
341+
DO LANGUAGE plpgsql $$
342+
DECLARE
343+
l_cur CURSOR FOR SELECT a FROM test1 ORDER BY 1 FOR UPDATE;
344+
BEGIN
345+
FOR r IN l_cur LOOP
346+
UPDATE test1 SET b = b || ' ' || b WHERE a = r.a;
347+
COMMIT;
348+
END LOOP;
349+
END;
350+
$$;
351+
SELECT * FROM test1;
352+
a | b
353+
---+-------------
354+
1 | one one
355+
2 | two two
356+
3 | three three
357+
(3 rows)
358+
359+
SELECT * FROM pg_cursors;
360+
name | statement | is_holdable | is_binary | is_scrollable | creation_time
361+
------+-----------+-------------+-----------+---------------+---------------
362+
(0 rows)
363+
364+
-- like bug #17050, but with implicit cursor
365+
TRUNCATE test1;
366+
INSERT INTO test1 VALUES (1,'one'), (2,'two'), (3,'three');
367+
DO LANGUAGE plpgsql $$
368+
DECLARE r RECORD;
369+
BEGIN
370+
FOR r IN SELECT a FROM test1 FOR UPDATE LOOP
371+
UPDATE test1 SET b = b || ' ' || b WHERE a = r.a;
372+
COMMIT;
373+
END LOOP;
374+
END;
375+
$$;
376+
SELECT * FROM test1;
377+
a | b
378+
---+-------------
379+
1 | one one
380+
2 | two two
381+
3 | three three
382+
(3 rows)
383+
384+
SELECT * FROM pg_cursors;
385+
name | statement | is_holdable | is_binary | is_scrollable | creation_time
386+
------+-----------+-------------+-----------+---------------+---------------
387+
(0 rows)
388+
338389
-- commit inside block with exception handler
339390
TRUNCATE test1;
340391
DO LANGUAGE plpgsql $$

‎src/pl/plpgsql/src/sql/plpgsql_transaction.sql

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -273,6 +273,47 @@ SELECT * FROM test2;
273273
SELECT*FROM pg_cursors;
274274

275275

276+
-- interaction of FOR UPDATE cursor with subsequent updates (bug #17050)
277+
TRUNCATE test1;
278+
279+
INSERT INTO test1VALUES (1,'one'), (2,'two'), (3,'three');
280+
281+
DO LANGUAGE plpgsql $$
282+
DECLARE
283+
l_cur CURSOR FORSELECT aFROM test1ORDER BY1 FORUPDATE;
284+
BEGIN
285+
FOR rIN l_cur LOOP
286+
UPDATE test1SET b= b||''|| bWHERE a=r.a;
287+
COMMIT;
288+
END LOOP;
289+
END;
290+
$$;
291+
292+
SELECT*FROM test1;
293+
294+
SELECT*FROM pg_cursors;
295+
296+
297+
-- like bug #17050, but with implicit cursor
298+
TRUNCATE test1;
299+
300+
INSERT INTO test1VALUES (1,'one'), (2,'two'), (3,'three');
301+
302+
DO LANGUAGE plpgsql $$
303+
DECLARE r RECORD;
304+
BEGIN
305+
FOR rINSELECT aFROM test1 FORUPDATE LOOP
306+
UPDATE test1SET b= b||''|| bWHERE a=r.a;
307+
COMMIT;
308+
END LOOP;
309+
END;
310+
$$;
311+
312+
SELECT*FROM test1;
313+
314+
SELECT*FROM pg_cursors;
315+
316+
276317
-- commit inside block with exception handler
277318
TRUNCATE test1;
278319

0 commit comments

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