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

Commit89e850e

Browse files
committed
plpython: Add SPI cursor support
Add a function plpy.cursor that is similar to plpy.execute but uses anSPI cursor to avoid fetching the entire result set into memory.Jan Urbański, reviewed by Steve Singer
1 parente6d9e21 commit89e850e

File tree

9 files changed

+1251
-3
lines changed

9 files changed

+1251
-3
lines changed

‎doc/src/sgml/plpython.sgml

Lines changed: 81 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -891,6 +891,15 @@ $$ LANGUAGE plpythonu;
891891
can be modified.
892892
</para>
893893

894+
<para>
895+
Note that calling <literal>plpy.execute</literal> will cause the entire
896+
result set to be read into memory. Only use that function when you are sure
897+
that the result set will be relatively small. If you don't want to risk
898+
excessive memory usage when fetching large results,
899+
use <literal>plpy.cursor</literal> rather
900+
than <literal>plpy.execute</literal>.
901+
</para>
902+
894903
<para>
895904
For example:
896905
<programlisting>
@@ -958,6 +967,78 @@ $$ LANGUAGE plpythonu;
958967

959968
</sect2>
960969

970+
<sect2>
971+
<title>Accessing Data with Cursors</title>
972+
973+
<para>
974+
The <literal>plpy.cursor</literal> function accepts the same arguments
975+
as <literal>plpy.execute</literal> (except for <literal>limit</literal>)
976+
and returns a cursor object, which allows you to process large result sets
977+
in smaller chunks. As with <literal>plpy.execute</literal>, either a query
978+
string or a plan object along with a list of arguments can be used. The
979+
cursor object provides a <literal>fetch</literal> method that accepts an
980+
integer parameter and returns a result object. Each time you
981+
call <literal>fetch</literal>, the returned object will contain the next
982+
batch of rows, never larger than the parameter value. Once all rows are
983+
exhausted, <literal>fetch</literal> starts returning an empty result
984+
object. Cursor objects also provide an
985+
<ulink url="http://docs.python.org/library/stdtypes.html#iterator-types">iterator
986+
interface</ulink>, yielding one row at a time until all rows are exhausted.
987+
Data fetched that way is not returned as result objects, but rather as
988+
dictionaries, each dictionary corresponding to a single result row.
989+
</para>
990+
991+
<para>
992+
Cursors are automatically disposed of. But if you want to explicitly
993+
release all resources held by a cursor, use the <literal>close</literal>
994+
method. Once closed, a cursor cannot be fetched from anymore.
995+
</para>
996+
997+
<tip>
998+
<para>
999+
Do not confuse objects created by <literal>plpy.cursor</literal> with
1000+
DB-API cursors as defined by
1001+
the <ulink url="http://www.python.org/dev/peps/pep-0249/">Python Database
1002+
API specification</ulink>. They don't have anything in common except for
1003+
the name.
1004+
</para>
1005+
</tip>
1006+
1007+
<para>
1008+
An example of two ways of processing data from a large table is:
1009+
<programlisting>
1010+
CREATE FUNCTION count_odd_iterator() RETURNS integer AS $$
1011+
odd = 0
1012+
for row in plpy.cursor("select num from largetable"):
1013+
if row['num'] % 2:
1014+
odd += 1
1015+
return odd
1016+
$$ LANGUAGE plpythonu;
1017+
1018+
CREATE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
1019+
odd = 0
1020+
cursor = plpy.cursor("select num from largetable")
1021+
while True:
1022+
rows = cursor.fetch(batch_size)
1023+
if not rows:
1024+
break
1025+
for row in rows:
1026+
if row['num'] % 2:
1027+
odd += 1
1028+
return odd
1029+
$$ LANGUAGE plpythonu;
1030+
1031+
CREATE FUNCTION count_odd_prepared() RETURNS integer AS $$
1032+
odd = 0
1033+
plan = plpy.prepare("select num from largetable where num % $1 &lt;&gt; 0", ["integer"])
1034+
rows = list(plpy.cursor(plan, [2]))
1035+
1036+
return len(rows)
1037+
$$ LANGUAGE plpythonu;
1038+
</programlisting>
1039+
</para>
1040+
</sect2>
1041+
9611042
<sect2 id="plpython-trapping">
9621043
<title>Trapping Errors</title>
9631044

‎src/pl/plpython/expected/plpython_spi.out

Lines changed: 151 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -133,3 +133,154 @@ CONTEXT: PL/Python function "result_nrows_test"
133133
2
134134
(1 row)
135135

136+
-- cursor objects
137+
CREATE FUNCTION simple_cursor_test() RETURNS int AS $$
138+
res = plpy.cursor("select fname, lname from users")
139+
does = 0
140+
for row in res:
141+
if row['lname'] == 'doe':
142+
does += 1
143+
return does
144+
$$ LANGUAGE plpythonu;
145+
CREATE FUNCTION double_cursor_close() RETURNS int AS $$
146+
res = plpy.cursor("select fname, lname from users")
147+
res.close()
148+
res.close()
149+
$$ LANGUAGE plpythonu;
150+
CREATE FUNCTION cursor_fetch() RETURNS int AS $$
151+
res = plpy.cursor("select fname, lname from users")
152+
assert len(res.fetch(3)) == 3
153+
assert len(res.fetch(3)) == 1
154+
assert len(res.fetch(3)) == 0
155+
assert len(res.fetch(3)) == 0
156+
try:
157+
# use next() or __next__(), the method name changed in
158+
# http://www.python.org/dev/peps/pep-3114/
159+
try:
160+
res.next()
161+
except AttributeError:
162+
res.__next__()
163+
except StopIteration:
164+
pass
165+
else:
166+
assert False, "StopIteration not raised"
167+
$$ LANGUAGE plpythonu;
168+
CREATE FUNCTION cursor_mix_next_and_fetch() RETURNS int AS $$
169+
res = plpy.cursor("select fname, lname from users order by fname")
170+
assert len(res.fetch(2)) == 2
171+
172+
item = None
173+
try:
174+
item = res.next()
175+
except AttributeError:
176+
item = res.__next__()
177+
assert item['fname'] == 'rick'
178+
179+
assert len(res.fetch(2)) == 1
180+
$$ LANGUAGE plpythonu;
181+
CREATE FUNCTION fetch_after_close() RETURNS int AS $$
182+
res = plpy.cursor("select fname, lname from users")
183+
res.close()
184+
try:
185+
res.fetch(1)
186+
except ValueError:
187+
pass
188+
else:
189+
assert False, "ValueError not raised"
190+
$$ LANGUAGE plpythonu;
191+
CREATE FUNCTION next_after_close() RETURNS int AS $$
192+
res = plpy.cursor("select fname, lname from users")
193+
res.close()
194+
try:
195+
try:
196+
res.next()
197+
except AttributeError:
198+
res.__next__()
199+
except ValueError:
200+
pass
201+
else:
202+
assert False, "ValueError not raised"
203+
$$ LANGUAGE plpythonu;
204+
CREATE FUNCTION cursor_fetch_next_empty() RETURNS int AS $$
205+
res = plpy.cursor("select fname, lname from users where false")
206+
assert len(res.fetch(1)) == 0
207+
try:
208+
try:
209+
res.next()
210+
except AttributeError:
211+
res.__next__()
212+
except StopIteration:
213+
pass
214+
else:
215+
assert False, "StopIteration not raised"
216+
$$ LANGUAGE plpythonu;
217+
CREATE FUNCTION cursor_plan() RETURNS SETOF text AS $$
218+
plan = plpy.prepare(
219+
"select fname, lname from users where fname like $1 || '%' order by fname",
220+
["text"])
221+
for row in plpy.cursor(plan, ["w"]):
222+
yield row['fname']
223+
for row in plpy.cursor(plan, ["j"]):
224+
yield row['fname']
225+
$$ LANGUAGE plpythonu;
226+
CREATE FUNCTION cursor_plan_wrong_args() RETURNS SETOF text AS $$
227+
plan = plpy.prepare("select fname, lname from users where fname like $1 || '%'",
228+
["text"])
229+
c = plpy.cursor(plan, ["a", "b"])
230+
$$ LANGUAGE plpythonu;
231+
SELECT simple_cursor_test();
232+
simple_cursor_test
233+
--------------------
234+
3
235+
(1 row)
236+
237+
SELECT double_cursor_close();
238+
double_cursor_close
239+
---------------------
240+
241+
(1 row)
242+
243+
SELECT cursor_fetch();
244+
cursor_fetch
245+
--------------
246+
247+
(1 row)
248+
249+
SELECT cursor_mix_next_and_fetch();
250+
cursor_mix_next_and_fetch
251+
---------------------------
252+
253+
(1 row)
254+
255+
SELECT fetch_after_close();
256+
fetch_after_close
257+
-------------------
258+
259+
(1 row)
260+
261+
SELECT next_after_close();
262+
next_after_close
263+
------------------
264+
265+
(1 row)
266+
267+
SELECT cursor_fetch_next_empty();
268+
cursor_fetch_next_empty
269+
-------------------------
270+
271+
(1 row)
272+
273+
SELECT cursor_plan();
274+
cursor_plan
275+
-------------
276+
willem
277+
jane
278+
john
279+
(3 rows)
280+
281+
SELECT cursor_plan_wrong_args();
282+
ERROR: TypeError: Expected sequence of 1 argument, got 2: ['a', 'b']
283+
CONTEXT: Traceback (most recent call last):
284+
PL/Python function "cursor_plan_wrong_args", line 4, in <module>
285+
c = plpy.cursor(plan, ["a", "b"])
286+
PL/Python function "cursor_plan_wrong_args"

‎src/pl/plpython/expected/plpython_subtransaction.out

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -409,3 +409,69 @@ SELECT * FROM subtransaction_tbl;
409409
(1 row)
410410

411411
DROP TABLE subtransaction_tbl;
412+
-- cursor/subtransactions interactions
413+
CREATE FUNCTION cursor_in_subxact() RETURNS int AS $$
414+
with plpy.subtransaction():
415+
cur = plpy.cursor("select * from generate_series(1, 20) as gen(i)")
416+
cur.fetch(10)
417+
fetched = cur.fetch(10);
418+
return int(fetched[5]["i"])
419+
$$ LANGUAGE plpythonu;
420+
CREATE FUNCTION cursor_aborted_subxact() RETURNS int AS $$
421+
try:
422+
with plpy.subtransaction():
423+
cur = plpy.cursor("select * from generate_series(1, 20) as gen(i)")
424+
cur.fetch(10);
425+
plpy.execute("select no_such_function()")
426+
except plpy.SPIError:
427+
fetched = cur.fetch(10)
428+
return int(fetched[5]["i"])
429+
return 0 # not reached
430+
$$ LANGUAGE plpythonu;
431+
CREATE FUNCTION cursor_plan_aborted_subxact() RETURNS int AS $$
432+
try:
433+
with plpy.subtransaction():
434+
plpy.execute('create temporary table tmp(i) '
435+
'as select generate_series(1, 10)')
436+
plan = plpy.prepare("select i from tmp")
437+
cur = plpy.cursor(plan)
438+
plpy.execute("select no_such_function()")
439+
except plpy.SPIError:
440+
fetched = cur.fetch(5)
441+
return fetched[2]["i"]
442+
return 0 # not reached
443+
$$ LANGUAGE plpythonu;
444+
CREATE FUNCTION cursor_close_aborted_subxact() RETURNS boolean AS $$
445+
try:
446+
with plpy.subtransaction():
447+
cur = plpy.cursor('select 1')
448+
plpy.execute("select no_such_function()")
449+
except plpy.SPIError:
450+
cur.close()
451+
return True
452+
return False # not reached
453+
$$ LANGUAGE plpythonu;
454+
SELECT cursor_in_subxact();
455+
cursor_in_subxact
456+
-------------------
457+
16
458+
(1 row)
459+
460+
SELECT cursor_aborted_subxact();
461+
ERROR: ValueError: iterating a cursor in an aborted subtransaction
462+
CONTEXT: Traceback (most recent call last):
463+
PL/Python function "cursor_aborted_subxact", line 8, in <module>
464+
fetched = cur.fetch(10)
465+
PL/Python function "cursor_aborted_subxact"
466+
SELECT cursor_plan_aborted_subxact();
467+
ERROR: ValueError: iterating a cursor in an aborted subtransaction
468+
CONTEXT: Traceback (most recent call last):
469+
PL/Python function "cursor_plan_aborted_subxact", line 10, in <module>
470+
fetched = cur.fetch(5)
471+
PL/Python function "cursor_plan_aborted_subxact"
472+
SELECT cursor_close_aborted_subxact();
473+
ERROR: ValueError: closing a cursor in an aborted subtransaction
474+
CONTEXT: Traceback (most recent call last):
475+
PL/Python function "cursor_close_aborted_subxact", line 7, in <module>
476+
cur.close()
477+
PL/Python function "cursor_close_aborted_subxact"

‎src/pl/plpython/expected/plpython_subtransaction_0.out

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -382,3 +382,73 @@ SELECT * FROM subtransaction_tbl;
382382
(0 rows)
383383

384384
DROP TABLE subtransaction_tbl;
385+
-- cursor/subtransactions interactions
386+
CREATE FUNCTION cursor_in_subxact() RETURNS int AS $$
387+
with plpy.subtransaction():
388+
cur = plpy.cursor("select * from generate_series(1, 20) as gen(i)")
389+
cur.fetch(10)
390+
fetched = cur.fetch(10);
391+
return int(fetched[5]["i"])
392+
$$ LANGUAGE plpythonu;
393+
ERROR: could not compile PL/Python function "cursor_in_subxact"
394+
DETAIL: SyntaxError: invalid syntax (line 3)
395+
CREATE FUNCTION cursor_aborted_subxact() RETURNS int AS $$
396+
try:
397+
with plpy.subtransaction():
398+
cur = plpy.cursor("select * from generate_series(1, 20) as gen(i)")
399+
cur.fetch(10);
400+
plpy.execute("select no_such_function()")
401+
except plpy.SPIError:
402+
fetched = cur.fetch(10)
403+
return int(fetched[5]["i"])
404+
return 0 # not reached
405+
$$ LANGUAGE plpythonu;
406+
ERROR: could not compile PL/Python function "cursor_aborted_subxact"
407+
DETAIL: SyntaxError: invalid syntax (line 4)
408+
CREATE FUNCTION cursor_plan_aborted_subxact() RETURNS int AS $$
409+
try:
410+
with plpy.subtransaction():
411+
plpy.execute('create temporary table tmp(i) '
412+
'as select generate_series(1, 10)')
413+
plan = plpy.prepare("select i from tmp")
414+
cur = plpy.cursor(plan)
415+
plpy.execute("select no_such_function()")
416+
except plpy.SPIError:
417+
fetched = cur.fetch(5)
418+
return fetched[2]["i"]
419+
return 0 # not reached
420+
$$ LANGUAGE plpythonu;
421+
ERROR: could not compile PL/Python function "cursor_plan_aborted_subxact"
422+
DETAIL: SyntaxError: invalid syntax (line 4)
423+
CREATE FUNCTION cursor_close_aborted_subxact() RETURNS boolean AS $$
424+
try:
425+
with plpy.subtransaction():
426+
cur = plpy.cursor('select 1')
427+
plpy.execute("select no_such_function()")
428+
except plpy.SPIError:
429+
cur.close()
430+
return True
431+
return False # not reached
432+
$$ LANGUAGE plpythonu;
433+
ERROR: could not compile PL/Python function "cursor_close_aborted_subxact"
434+
DETAIL: SyntaxError: invalid syntax (line 4)
435+
SELECT cursor_in_subxact();
436+
ERROR: function cursor_in_subxact() does not exist
437+
LINE 1: SELECT cursor_in_subxact();
438+
^
439+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
440+
SELECT cursor_aborted_subxact();
441+
ERROR: function cursor_aborted_subxact() does not exist
442+
LINE 1: SELECT cursor_aborted_subxact();
443+
^
444+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
445+
SELECT cursor_plan_aborted_subxact();
446+
ERROR: function cursor_plan_aborted_subxact() does not exist
447+
LINE 1: SELECT cursor_plan_aborted_subxact();
448+
^
449+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
450+
SELECT cursor_close_aborted_subxact();
451+
ERROR: function cursor_close_aborted_subxact() does not exist
452+
LINE 1: SELECT cursor_close_aborted_subxact();
453+
^
454+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

0 commit comments

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