Skip to content

Commit 474a424

Browse files
peterepetere
committed
PL/Python custom SPI exceptions
This provides a separate exception class for each error code that the backend defines, as well as the ability to get the SQLSTATE from the exception object. Jan Urbański, reviewed by Steve Singer
1 parent 0ef0b30 commit 474a424

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-
clean distclean maintainer-clean: clean-lib
152+
clean distclean: 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: Swallowed SPIError('syntax error at or near "error"',)
141+
NOTICE: Swallowed SyntaxError('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: Swallowed SPIError('syntax error at or near "error"',)
167+
NOTICE: Swallowed SyntaxError('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

‎src/pl/plpython/generate-spiexceptions.pl

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+
open my $errcodes, $ARGV[0] or die;
13+
14+
while (<$errcodes>) {
15+
chomp;
16+
17+
# Skip comments
18+
next if /^#/;
19+
next if /^\s*$/;
20+
21+
# Skip section headers
22+
next if /^Section:/;
23+
24+
die unless /^([^\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+
next unless $type eq 'E';
33+
34+
# Skip lines without PL/pgSQL condition names
35+
next unless defined($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)