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

Commit474a424

Browse files
committed
PL/Python custom SPI exceptions
This provides a separate exception class for each error code that thebackend defines, as well as the ability to get the SQLSTATE from theexception object.Jan Urbański, reviewed by Steve Singer
1 parent0ef0b30 commit474a424

File tree

12 files changed

+342
-26
lines changed

12 files changed

+342
-26
lines changed

‎doc/src/sgml/plpython.sgml

Lines changed: 48 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -962,7 +962,7 @@ $$ LANGUAGE plpythonu;
962962
Functions accessing the database might encounter errors, which
963963
will cause them to abort and raise an exception. Both
964964
<function>plpy.execute</function> and
965-
<function>plpy.prepare</function> can raise an instance of
965+
<function>plpy.prepare</function> can raise an instance of a subclass of
966966
<literal>plpy.SPIError</literal>, which by default will terminate
967967
the function. This error can be handled just like any other
968968
Python exception, by using the <literal>try/except</literal>
@@ -978,6 +978,53 @@ CREATE FUNCTION try_adding_joe() RETURNS text AS $$
978978
$$ LANGUAGE plpythonu;
979979
</programlisting>
980980
</para>
981+
982+
<para>
983+
The actual class of the exception being raised corresponds to the
984+
specific condition that caused the error. Refer
985+
to <xref linkend="errcodes-table"> for a list of possible
986+
conditions. The module
987+
<literal>plpy.spiexceptions</literal> defines an exception class
988+
for each <productname>PostgreSQL</productname> condition, deriving
989+
their names from the condition name. For
990+
instance, <literal>division_by_zero</literal>
991+
becomes <literal>DivisionByZero</literal>, <literal>unique_violation</literal>
992+
becomes <literal>UniqueViolation</literal>, <literal>fdw_error</literal>
993+
becomes <literal>FdwError</literal>, and so on. Each of these
994+
exception classes inherits from <literal>SPIError</literal>. This
995+
separation makes it easier to handle specific errors, for
996+
instance:
997+
<programlisting>
998+
CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
999+
from plpy import spiexceptions
1000+
try:
1001+
plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
1002+
plpy.execute(plan, [numerator, denominator])
1003+
except spiexceptions.DivisionByZero:
1004+
return "denominator cannot equal zero"
1005+
except spiexceptions.UniqueViolation:
1006+
return "already have that fraction"
1007+
except plpy.SPIError, e:
1008+
return "other error, SQLSTATE %s" % e.sqlstate
1009+
else:
1010+
return "fraction inserted"
1011+
$$ LANGUAGE plpythonu;
1012+
</programlisting>
1013+
Note that because all exceptions from
1014+
the <literal>plpy.spiexceptions</literal> module inherit
1015+
from <literal>SPIError</literal>, an <literal>except</literal>
1016+
clause handling it will catch any database access error.
1017+
</para>
1018+
1019+
<para>
1020+
As an alternative way of handling different error conditions, you
1021+
can catch the <literal>SPIError</literal> exception and determine
1022+
the specific error condition inside the <literal>except</literal>
1023+
block by looking at the <literal>sqlstate</literal> attribute of
1024+
the exception object. This attribute is a string value containing
1025+
the <quote>SQLSTATE</quote> error code. This approach provides
1026+
approximately the same functionality
1027+
</para>
9811028
</sect2>
9821029
</sect1>
9831030

‎src/pl/plpython/.gitignore

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
/spiexceptions.h
12
# Generated subdirectories
23
/log/
34
/results/

‎src/pl/plpython/Makefile

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -88,9 +88,16 @@ PSQLDIR = $(bindir)
8888

8989
include$(top_srcdir)/src/Makefile.shlib
9090

91+
# Force this dependency to be known even without dependency info built:
92+
plpython.o: spiexceptions.h
93+
94+
spiexceptions.h:$(top_srcdir)/src/backend/utils/errcodes.txt generate-spiexceptions.pl
95+
$(PERL)$(srcdir)/generate-spiexceptions.pl$<>$@
9196

9297
all: all-lib
9398

99+
distprep: spiexceptions.h
100+
94101
install: all installdirs install-lib
95102
ifeq ($(python_majorversion),2)
96103
cd '$(DESTDIR)$(pkglibdir)' && rm -f plpython$(DLSUFFIX) && $(LN_S) $(shlib) plpython$(DLSUFFIX)
@@ -142,13 +149,16 @@ endif
142149
submake:
143150
$(MAKE) -C$(top_builddir)/src/test/regress pg_regress$(X)
144151

145-
cleandistcleanmaintainer-clean: clean-lib
152+
cleandistclean: clean-lib
146153
rm -f$(OBJS)
147154
rm -rf$(pg_regress_clean_files)
148155
ifeq ($(PORTNAME), win32)
149156
rm -f python${pytverstr}.def
150157
endif
151158

159+
maintainer-clean: distclean
160+
rm -f spiexceptions.h
161+
152162
else # can't build
153163

154164
all:

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

Lines changed: 41 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,7 @@ CREATE FUNCTION sql_syntax_error() RETURNS text
3232
'plpy.execute("syntax error")'
3333
LANGUAGE plpythonu;
3434
SELECT sql_syntax_error();
35-
ERROR:plpy.SPIError: syntax error at or near "syntax"
35+
ERROR:spiexceptions.SyntaxError: syntax error at or near "syntax"
3636
LINE 1: syntax error
3737
^
3838
QUERY: syntax error
@@ -54,7 +54,7 @@ CREATE FUNCTION exception_index_invalid_nested() RETURNS text
5454
return rv[0]'
5555
LANGUAGE plpythonu;
5656
SELECT exception_index_invalid_nested();
57-
ERROR:plpy.SPIError: function test5(unknown) does not exist
57+
ERROR:spiexceptions.UndefinedFunction: function test5(unknown) does not exist
5858
LINE 1: SELECT test5('foo')
5959
^
6060
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
@@ -74,7 +74,7 @@ return None
7474
'
7575
LANGUAGE plpythonu;
7676
SELECT invalid_type_uncaught('rick');
77-
ERROR:plpy.SPIError: type "test" does not exist
77+
ERROR:spiexceptions.UndefinedObject: type "test" does not exist
7878
CONTEXT: PL/Python function "invalid_type_uncaught"
7979
/* for what it's worth catch the exception generated by
8080
* the typo, and return None
@@ -140,6 +140,44 @@ SELECT valid_type('rick');
140140

141141
(1 row)
142142

143+
/* check catching specific types of exceptions
144+
*/
145+
CREATE TABLE specific (
146+
i integer PRIMARY KEY
147+
);
148+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "specific_pkey" for table "specific"
149+
CREATE FUNCTION specific_exception(i integer) RETURNS void AS
150+
$$
151+
from plpy import spiexceptions
152+
try:
153+
plpy.execute("insert into specific values (%s)" % (i or "NULL"));
154+
except spiexceptions.NotNullViolation, e:
155+
plpy.notice("Violated the NOT NULL constraint, sqlstate %s" % e.sqlstate)
156+
except spiexceptions.UniqueViolation, e:
157+
plpy.notice("Violated the UNIQUE constraint, sqlstate %s" % e.sqlstate)
158+
$$ LANGUAGE plpythonu;
159+
SELECT specific_exception(2);
160+
specific_exception
161+
--------------------
162+
163+
(1 row)
164+
165+
SELECT specific_exception(NULL);
166+
NOTICE: Violated the NOT NULL constraint, sqlstate 23502
167+
CONTEXT: PL/Python function "specific_exception"
168+
specific_exception
169+
--------------------
170+
171+
(1 row)
172+
173+
SELECT specific_exception(2);
174+
NOTICE: Violated the UNIQUE constraint, sqlstate 23505
175+
CONTEXT: PL/Python function "specific_exception"
176+
specific_exception
177+
--------------------
178+
179+
(1 row)
180+
143181
/* manually starting subtransactions - a bad idea
144182
*/
145183
CREATE FUNCTION manual_subxact() RETURNS void AS $$

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

Lines changed: 41 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,7 @@ CREATE FUNCTION sql_syntax_error() RETURNS text
3232
'plpy.execute("syntax error")'
3333
LANGUAGE plpythonu;
3434
SELECT sql_syntax_error();
35-
ERROR:plpy.SPIError: syntax error at or near "syntax"
35+
ERROR:spiexceptions.SyntaxError: syntax error at or near "syntax"
3636
LINE 1: syntax error
3737
^
3838
QUERY: syntax error
@@ -54,7 +54,7 @@ CREATE FUNCTION exception_index_invalid_nested() RETURNS text
5454
return rv[0]'
5555
LANGUAGE plpythonu;
5656
SELECT exception_index_invalid_nested();
57-
ERROR:plpy.SPIError: function test5(unknown) does not exist
57+
ERROR:spiexceptions.UndefinedFunction: function test5(unknown) does not exist
5858
LINE 1: SELECT test5('foo')
5959
^
6060
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
@@ -74,7 +74,7 @@ return None
7474
'
7575
LANGUAGE plpythonu;
7676
SELECT invalid_type_uncaught('rick');
77-
ERROR:plpy.SPIError: type "test" does not exist
77+
ERROR:spiexceptions.UndefinedObject: type "test" does not exist
7878
CONTEXT: PL/Python function "invalid_type_uncaught"
7979
/* for what it's worth catch the exception generated by
8080
* the typo, and return None
@@ -140,6 +140,44 @@ SELECT valid_type('rick');
140140

141141
(1 row)
142142

143+
/* check catching specific types of exceptions
144+
*/
145+
CREATE TABLE specific (
146+
i integer PRIMARY KEY
147+
);
148+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "specific_pkey" for table "specific"
149+
CREATE FUNCTION specific_exception(i integer) RETURNS void AS
150+
$$
151+
from plpy import spiexceptions
152+
try:
153+
plpy.execute("insert into specific values (%s)" % (i or "NULL"));
154+
except spiexceptions.NotNullViolation, e:
155+
plpy.notice("Violated the NOT NULL constraint, sqlstate %s" % e.sqlstate)
156+
except spiexceptions.UniqueViolation, e:
157+
plpy.notice("Violated the UNIQUE constraint, sqlstate %s" % e.sqlstate)
158+
$$ LANGUAGE plpythonu;
159+
SELECT specific_exception(2);
160+
specific_exception
161+
--------------------
162+
163+
(1 row)
164+
165+
SELECT specific_exception(NULL);
166+
NOTICE: Violated the NOT NULL constraint, sqlstate 23502
167+
CONTEXT: PL/Python function "specific_exception"
168+
specific_exception
169+
--------------------
170+
171+
(1 row)
172+
173+
SELECT specific_exception(2);
174+
NOTICE: Violated the UNIQUE constraint, sqlstate 23505
175+
CONTEXT: PL/Python function "specific_exception"
176+
specific_exception
177+
--------------------
178+
179+
(1 row)
180+
143181
/* manually starting subtransactions - a bad idea
144182
*/
145183
CREATE FUNCTION manual_subxact() RETURNS void AS $$

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

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -43,7 +43,7 @@ SELECT * FROM subtransaction_tbl;
4343

4444
TRUNCATE subtransaction_tbl;
4545
SELECT subtransaction_test('SPI');
46-
ERROR:plpy.SPIError: invalid input syntax for integer: "oops"
46+
ERROR:spiexceptions.InvalidTextRepresentation: invalid input syntax for integer: "oops"
4747
LINE 1: INSERT INTO subtransaction_tbl VALUES ('oops')
4848
^
4949
QUERY: INSERT INTO subtransaction_tbl VALUES ('oops')
@@ -89,7 +89,7 @@ SELECT * FROM subtransaction_tbl;
8989

9090
TRUNCATE subtransaction_tbl;
9191
SELECT subtransaction_ctx_test('SPI');
92-
ERROR:plpy.SPIError: invalid input syntax for integer: "oops"
92+
ERROR:spiexceptions.InvalidTextRepresentation: invalid input syntax for integer: "oops"
9393
LINE 1: INSERT INTO subtransaction_tbl VALUES ('oops')
9494
^
9595
QUERY: INSERT INTO subtransaction_tbl VALUES ('oops')
@@ -126,7 +126,7 @@ with plpy.subtransaction():
126126
return "ok"
127127
$$ LANGUAGE plpythonu;
128128
SELECT subtransaction_nested_test();
129-
ERROR:plpy.SPIError: syntax error at or near "error"
129+
ERROR:spiexceptions.SyntaxError: syntax error at or near "error"
130130
LINE 1: error
131131
^
132132
QUERY: error
@@ -138,7 +138,7 @@ SELECT * FROM subtransaction_tbl;
138138

139139
TRUNCATE subtransaction_tbl;
140140
SELECT subtransaction_nested_test('t');
141-
NOTICE: SwallowedSPIError('syntax error at or near "error"',)
141+
NOTICE: SwallowedSyntaxError('syntax error at or near "error"',)
142142
CONTEXT: PL/Python function "subtransaction_nested_test"
143143
subtransaction_nested_test
144144
----------------------------
@@ -164,7 +164,7 @@ with plpy.subtransaction():
164164
return "ok"
165165
$$ LANGUAGE plpythonu;
166166
SELECT subtransaction_deeply_nested_test();
167-
NOTICE: SwallowedSPIError('syntax error at or near "error"',)
167+
NOTICE: SwallowedSyntaxError('syntax error at or near "error"',)
168168
CONTEXT: PL/Python function "subtransaction_nested_test"
169169
SQL statement "SELECT subtransaction_nested_test('t')"
170170
PL/Python function "subtransaction_nested_test"

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -43,7 +43,7 @@ SELECT * FROM subtransaction_tbl;
4343

4444
TRUNCATE subtransaction_tbl;
4545
SELECT subtransaction_test('SPI');
46-
ERROR:plpy.SPIError: invalid input syntax for integer: "oops"
46+
ERROR:spiexceptions.InvalidTextRepresentation: invalid input syntax for integer: "oops"
4747
LINE 1: INSERT INTO subtransaction_tbl VALUES ('oops')
4848
^
4949
QUERY: INSERT INTO subtransaction_tbl VALUES ('oops')

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -43,9 +43,9 @@ contents.sort()
4343
return ", ".join(contents)
4444
$$ LANGUAGE plpythonu;
4545
select module_contents();
46-
module_contents
47-
-------------------------------------------------------------------------------------------------------------------------------------------------------
48-
Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, quote_ident, quote_literal, quote_nullable, subtransaction, warning
46+
module_contents
47+
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
48+
Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, quote_ident, quote_literal, quote_nullable,spiexceptions,subtransaction, warning
4949
(1 row)
5050

5151
CREATE FUNCTION elog_test() RETURNS void
Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
#!/usr/bin/perl
2+
#
3+
# Generate the spiexceptions.h header from errcodes.txt
4+
# Copyright (c) 2000-2011, PostgreSQL Global Development Group
5+
6+
use warnings;
7+
use strict;
8+
9+
print"/* autogenerated from src/backend/utils/errcodes.txt, do not edit */\n";
10+
print"/* there is deliberately not an #ifndef SPIEXCEPTIONS_H here */\n";
11+
12+
openmy$errcodes,$ARGV[0]ordie;
13+
14+
while (<$errcodes>) {
15+
chomp;
16+
17+
# Skip comments
18+
nextif/^#/;
19+
nextif/^\s*$/;
20+
21+
# Skip section headers
22+
nextif/^Section:/;
23+
24+
dieunless/^([^\s]{5})\s+([EWS])\s+([^\s]+)(?:\s+)?([^\s]+)?/;
25+
26+
(my$sqlstate,
27+
my$type,
28+
my$errcode_macro,
29+
my$condition_name) = ($1,$2,$3,$4);
30+
31+
# Skip non-errors
32+
nextunless$typeeq'E';
33+
34+
# Skip lines without PL/pgSQL condition names
35+
nextunlessdefined($condition_name);
36+
37+
# Change some_error_condition to SomeErrorCondition
38+
$condition_name =~s/([a-z])([^_]*)(?:_|$)/\u$1$2/g;
39+
40+
print"{\"spiexceptions.$condition_name\"," .
41+
"\"$condition_name\",$errcode_macro },\n";
42+
}
43+
44+
close$errcodes;

0 commit comments

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