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

Commit0c5933d

Browse files
committed
Wrap PL/Python SPI calls into subtransactions
This allows the language-specific try/catch construct to catch andhandle exceptions arising from SPI calls, matching the behavior ofother PLs.As an additional bonus you no longer get all the ugly "unrecognizederror in PLy_spi_execute_query" errors.Jan Urbański, reviewed by Steve Singer
1 parentc73fe72 commit0c5933d

File tree

5 files changed

+202
-32
lines changed

5 files changed

+202
-32
lines changed

‎doc/src/sgml/plpython.sgml‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -858,6 +858,9 @@ $$ LANGUAGE plpythonu;
858858
<literal>plpy.<replaceable>foo</replaceable></literal>.
859859
</para>
860860

861+
<sect2>
862+
<title>Database Access Functions</title>
863+
861864
<para>
862865
The <literal>plpy</literal> module provides two
863866
functions called <function>execute</function> and
@@ -937,6 +940,33 @@ CREATE FUNCTION usesavedplan() RETURNS trigger AS $$
937940
$$ LANGUAGE plpythonu;
938941
</programlisting>
939942
</para>
943+
944+
</sect2>
945+
946+
<sect2>
947+
<title>Trapping Errors</title>
948+
949+
<para>
950+
Functions accessing the database might encounter errors, which
951+
will cause them to abort and raise an exception. Both
952+
<function>plpy.execute</function> and
953+
<function>plpy.prepare</function> can raise an instance of
954+
<literal>plpy.SPIError</literal>, which by default will terminate
955+
the function. This error can be handled just like any other
956+
Python exception, by using the <literal>try/except</literal>
957+
construct. For example:
958+
<programlisting>
959+
CREATE FUNCTION try_adding_joe() RETURNS text AS $$
960+
try:
961+
plpy.execute("INSERT INTO users(username) VALUES ('joe')")
962+
except plpy.SPIError:
963+
return "something went wrong"
964+
else:
965+
return "Joe added"
966+
$$ LANGUAGE plpythonu;
967+
</programlisting>
968+
</para>
969+
</sect2>
940970
</sect1>
941971

942972
<sect1 id="plpython-util">

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

Lines changed: 22 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -32,8 +32,6 @@ CREATE FUNCTION sql_syntax_error() RETURNS text
3232
'plpy.execute("syntax error")'
3333
LANGUAGE plpythonu;
3434
SELECT sql_syntax_error();
35-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_execute_query
36-
CONTEXT: PL/Python function "sql_syntax_error"
3735
ERROR: plpy.SPIError: syntax error at or near "syntax"
3836
LINE 1: syntax error
3937
^
@@ -56,8 +54,6 @@ CREATE FUNCTION exception_index_invalid_nested() RETURNS text
5654
return rv[0]'
5755
LANGUAGE plpythonu;
5856
SELECT exception_index_invalid_nested();
59-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_execute_query
60-
CONTEXT: PL/Python function "exception_index_invalid_nested"
6157
ERROR: plpy.SPIError: function test5(unknown) does not exist
6258
LINE 1: SELECT test5('foo')
6359
^
@@ -78,8 +74,6 @@ return None
7874
'
7975
LANGUAGE plpythonu;
8076
SELECT invalid_type_uncaught('rick');
81-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_prepare
82-
CONTEXT: PL/Python function "invalid_type_uncaught"
8377
ERROR: plpy.SPIError: type "test" does not exist
8478
CONTEXT: PL/Python function "invalid_type_uncaught"
8579
/* for what it's worth catch the exception generated by
@@ -101,8 +95,6 @@ return None
10195
'
10296
LANGUAGE plpythonu;
10397
SELECT invalid_type_caught('rick');
104-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_prepare
105-
CONTEXT: PL/Python function "invalid_type_caught"
10698
NOTICE: type "test" does not exist
10799
CONTEXT: PL/Python function "invalid_type_caught"
108100
invalid_type_caught
@@ -128,8 +120,6 @@ return None
128120
'
129121
LANGUAGE plpythonu;
130122
SELECT invalid_type_reraised('rick');
131-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_prepare
132-
CONTEXT: PL/Python function "invalid_type_reraised"
133123
ERROR: plpy.Error: type "test" does not exist
134124
CONTEXT: PL/Python function "invalid_type_reraised"
135125
/* no typo no messing about
@@ -150,3 +140,25 @@ SELECT valid_type('rick');
150140

151141
(1 row)
152142

143+
/* manually starting subtransactions - a bad idea
144+
*/
145+
CREATE FUNCTION manual_subxact() RETURNS void AS $$
146+
plpy.execute("savepoint save")
147+
plpy.execute("create table foo(x integer)")
148+
plpy.execute("rollback to save")
149+
$$ LANGUAGE plpythonu;
150+
SELECT manual_subxact();
151+
ERROR: plpy.SPIError: SPI_execute failed: SPI_ERROR_TRANSACTION
152+
CONTEXT: PL/Python function "manual_subxact"
153+
/* same for prepared plans
154+
*/
155+
CREATE FUNCTION manual_subxact_prepared() RETURNS void AS $$
156+
save = plpy.prepare("savepoint save")
157+
rollback = plpy.prepare("rollback to save")
158+
plpy.execute(save)
159+
plpy.execute("create table foo(x integer)")
160+
plpy.execute(rollback)
161+
$$ LANGUAGE plpythonu;
162+
SELECT manual_subxact_prepared();
163+
ERROR: plpy.SPIError: SPI_execute_plan failed: SPI_ERROR_TRANSACTION
164+
CONTEXT: PL/Python function "manual_subxact_prepared"

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

Lines changed: 22 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -32,8 +32,6 @@ CREATE FUNCTION sql_syntax_error() RETURNS text
3232
'plpy.execute("syntax error")'
3333
LANGUAGE plpythonu;
3434
SELECT sql_syntax_error();
35-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_execute_query
36-
CONTEXT: PL/Python function "sql_syntax_error"
3735
ERROR: plpy.SPIError: syntax error at or near "syntax"
3836
LINE 1: syntax error
3937
^
@@ -56,8 +54,6 @@ CREATE FUNCTION exception_index_invalid_nested() RETURNS text
5654
return rv[0]'
5755
LANGUAGE plpythonu;
5856
SELECT exception_index_invalid_nested();
59-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_execute_query
60-
CONTEXT: PL/Python function "exception_index_invalid_nested"
6157
ERROR: plpy.SPIError: function test5(unknown) does not exist
6258
LINE 1: SELECT test5('foo')
6359
^
@@ -78,8 +74,6 @@ return None
7874
'
7975
LANGUAGE plpythonu;
8076
SELECT invalid_type_uncaught('rick');
81-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_prepare
82-
CONTEXT: PL/Python function "invalid_type_uncaught"
8377
ERROR: plpy.SPIError: type "test" does not exist
8478
CONTEXT: PL/Python function "invalid_type_uncaught"
8579
/* for what it's worth catch the exception generated by
@@ -101,8 +95,6 @@ return None
10195
'
10296
LANGUAGE plpythonu;
10397
SELECT invalid_type_caught('rick');
104-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_prepare
105-
CONTEXT: PL/Python function "invalid_type_caught"
10698
NOTICE: type "test" does not exist
10799
CONTEXT: PL/Python function "invalid_type_caught"
108100
invalid_type_caught
@@ -128,8 +120,6 @@ return None
128120
'
129121
LANGUAGE plpythonu;
130122
SELECT invalid_type_reraised('rick');
131-
WARNING: plpy.SPIError: unrecognized error in PLy_spi_prepare
132-
CONTEXT: PL/Python function "invalid_type_reraised"
133123
ERROR: plpy.Error: type "test" does not exist
134124
CONTEXT: PL/Python function "invalid_type_reraised"
135125
/* no typo no messing about
@@ -150,3 +140,25 @@ SELECT valid_type('rick');
150140

151141
(1 row)
152142

143+
/* manually starting subtransactions - a bad idea
144+
*/
145+
CREATE FUNCTION manual_subxact() RETURNS void AS $$
146+
plpy.execute("savepoint save")
147+
plpy.execute("create table foo(x integer)")
148+
plpy.execute("rollback to save")
149+
$$ LANGUAGE plpythonu;
150+
SELECT manual_subxact();
151+
ERROR: plpy.SPIError: SPI_execute failed: SPI_ERROR_TRANSACTION
152+
CONTEXT: PL/Python function "manual_subxact"
153+
/* same for prepared plans
154+
*/
155+
CREATE FUNCTION manual_subxact_prepared() RETURNS void AS $$
156+
save = plpy.prepare("savepoint save")
157+
rollback = plpy.prepare("rollback to save")
158+
plpy.execute(save)
159+
plpy.execute("create table foo(x integer)")
160+
plpy.execute(rollback)
161+
$$ LANGUAGE plpythonu;
162+
SELECT manual_subxact_prepared();
163+
ERROR: plpy.SPIError: SPI_execute_plan failed: SPI_ERROR_TRANSACTION
164+
CONTEXT: PL/Python function "manual_subxact_prepared"

0 commit comments

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