From 474a42473adf9b18417242f1fc0691a857ec578b Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Mon, 28 Feb 2011 18:41:10 +0200
Subject: [PATCH] PL/Python custom SPI exceptions
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

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
---
 doc/src/sgml/plpython.sgml                    |  49 ++++++-
 src/pl/plpython/.gitignore                    |   1 +
 src/pl/plpython/Makefile                      |  12 +-
 src/pl/plpython/expected/plpython_error.out   |  44 +++++-
 src/pl/plpython/expected/plpython_error_0.out |  44 +++++-
 .../expected/plpython_subtransaction.out      |  10 +-
 .../expected/plpython_subtransaction_0.out    |   2 +-
 src/pl/plpython/expected/plpython_test.out    |   6 +-
 src/pl/plpython/generate-spiexceptions.pl     |  44 ++++++
 src/pl/plpython/plpython.c                    | 129 ++++++++++++++++--
 src/pl/plpython/sql/plpython_error.sql        |  21 +++
 src/tools/msvc/Solution.pm                    |   6 +
 12 files changed, 342 insertions(+), 26 deletions(-)
 create mode 100644 src/pl/plpython/generate-spiexceptions.pl

diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml
index 73203e6251..a729fa3e17 100644
--- a/doc/src/sgml/plpython.sgml
+++ b/doc/src/sgml/plpython.sgml
@@ -962,7 +962,7 @@ $$ LANGUAGE plpythonu;
     Functions accessing the database might encounter errors, which
     will cause them to abort and raise an exception.  Both
     <function>plpy.execute</function> and
-    <function>plpy.prepare</function> can raise an instance of
+    <function>plpy.prepare</function> can raise an instance of a subclass of
     <literal>plpy.SPIError</literal>, which by default will terminate
     the function.  This error can be handled just like any other
     Python exception, by using the <literal>try/except</literal>
@@ -978,6 +978,53 @@ CREATE FUNCTION try_adding_joe() RETURNS text AS $$
 $$ LANGUAGE plpythonu;
 </programlisting>
    </para>
+
+   <para>
+    The actual class of the exception being raised corresponds to the
+    specific condition that caused the error.  Refer
+    to <xref linkend="errcodes-table"> for a list of possible
+    conditions.  The module
+    <literal>plpy.spiexceptions</literal> defines an exception class
+    for each <productname>PostgreSQL</productname> condition, deriving
+    their names from the condition name.  For
+    instance, <literal>division_by_zero</literal>
+    becomes <literal>DivisionByZero</literal>, <literal>unique_violation</literal>
+    becomes <literal>UniqueViolation</literal>, <literal>fdw_error</literal>
+    becomes <literal>FdwError</literal>, and so on.  Each of these
+    exception classes inherits from <literal>SPIError</literal>.  This
+    separation makes it easier to handle specific errors, for
+    instance:
+<programlisting>
+CREATE FUNCTION insert_fraction(numerator int, denominator int) RETURNS text AS $$
+from plpy import spiexceptions
+try:
+    plan = plpy.prepare("INSERT INTO fractions (frac) VALUES ($1 / $2)", ["int", "int"])
+    plpy.execute(plan, [numerator, denominator])
+except spiexceptions.DivisionByZero:
+    return "denominator cannot equal zero"
+except spiexceptions.UniqueViolation:
+    return "already have that fraction"
+except plpy.SPIError, e:
+    return "other error, SQLSTATE %s" % e.sqlstate
+else:
+    return "fraction inserted"
+$$ LANGUAGE plpythonu;
+</programlisting>
+    Note that because all exceptions from
+    the <literal>plpy.spiexceptions</literal> module inherit
+    from <literal>SPIError</literal>, an <literal>except</literal>
+    clause handling it will catch any database access error.
+   </para>
+
+   <para>
+    As an alternative way of handling different error conditions, you
+    can catch the <literal>SPIError</literal> exception and determine
+    the specific error condition inside the <literal>except</literal>
+    block by looking at the <literal>sqlstate</literal> attribute of
+    the exception object.  This attribute is a string value containing
+    the <quote>SQLSTATE</quote> error code.  This approach provides
+    approximately the same functionality
+   </para>
   </sect2>
  </sect1>
 
diff --git a/src/pl/plpython/.gitignore b/src/pl/plpython/.gitignore
index 5dcb3ff972..07bee6a29c 100644
--- a/src/pl/plpython/.gitignore
+++ b/src/pl/plpython/.gitignore
@@ -1,3 +1,4 @@
+/spiexceptions.h
 # Generated subdirectories
 /log/
 /results/
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index c72e1e300c..8e3ebddf73 100644
--- a/src/pl/plpython/Makefile
+++ b/src/pl/plpython/Makefile
@@ -88,9 +88,16 @@ PSQLDIR = $(bindir)
 
 include $(top_srcdir)/src/Makefile.shlib
 
+# Force this dependency to be known even without dependency info built:
+plpython.o: spiexceptions.h
+
+spiexceptions.h: $(top_srcdir)/src/backend/utils/errcodes.txt generate-spiexceptions.pl
+	$(PERL) $(srcdir)/generate-spiexceptions.pl $< > $@
 
 all: all-lib
 
+distprep: spiexceptions.h
+
 install: all installdirs install-lib
 ifeq ($(python_majorversion),2)
 	cd '$(DESTDIR)$(pkglibdir)' && rm -f plpython$(DLSUFFIX) && $(LN_S) $(shlib) plpython$(DLSUFFIX)
@@ -142,13 +149,16 @@ endif
 submake:
 	$(MAKE) -C $(top_builddir)/src/test/regress pg_regress$(X)
 
-clean distclean maintainer-clean: clean-lib
+clean distclean: clean-lib
 	rm -f $(OBJS)
 	rm -rf $(pg_regress_clean_files)
 ifeq ($(PORTNAME), win32)
 	rm -f python${pytverstr}.def
 endif
 
+maintainer-clean: distclean
+	rm -f spiexceptions.h
+
 else # can't build
 
 all:
diff --git a/src/pl/plpython/expected/plpython_error.out b/src/pl/plpython/expected/plpython_error.out
index 7597ca73f1..e38ea60de1 100644
--- a/src/pl/plpython/expected/plpython_error.out
+++ b/src/pl/plpython/expected/plpython_error.out
@@ -32,7 +32,7 @@ CREATE FUNCTION sql_syntax_error() RETURNS text
 'plpy.execute("syntax error")'
         LANGUAGE plpythonu;
 SELECT sql_syntax_error();
-ERROR:  plpy.SPIError: syntax error at or near "syntax"
+ERROR:  spiexceptions.SyntaxError: syntax error at or near "syntax"
 LINE 1: syntax error
         ^
 QUERY:  syntax error
@@ -54,7 +54,7 @@ CREATE FUNCTION exception_index_invalid_nested() RETURNS text
 return rv[0]'
 	LANGUAGE plpythonu;
 SELECT exception_index_invalid_nested();
-ERROR:  plpy.SPIError: function test5(unknown) does not exist
+ERROR:  spiexceptions.UndefinedFunction: function test5(unknown) does not exist
 LINE 1: SELECT test5('foo')
                ^
 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
@@ -74,7 +74,7 @@ return None
 '
 	LANGUAGE plpythonu;
 SELECT invalid_type_uncaught('rick');
-ERROR:  plpy.SPIError: type "test" does not exist
+ERROR:  spiexceptions.UndefinedObject: type "test" does not exist
 CONTEXT:  PL/Python function "invalid_type_uncaught"
 /* for what it's worth catch the exception generated by
  * the typo, and return None
@@ -140,6 +140,44 @@ SELECT valid_type('rick');
  
 (1 row)
 
+/* check catching specific types of exceptions
+ */
+CREATE TABLE specific (
+    i integer PRIMARY KEY
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "specific_pkey" for table "specific"
+CREATE FUNCTION specific_exception(i integer) RETURNS void AS
+$$
+from plpy import spiexceptions
+try:
+    plpy.execute("insert into specific values (%s)" % (i or "NULL"));
+except spiexceptions.NotNullViolation, e:
+    plpy.notice("Violated the NOT NULL constraint, sqlstate %s" % e.sqlstate)
+except spiexceptions.UniqueViolation, e:
+    plpy.notice("Violated the UNIQUE constraint, sqlstate %s" % e.sqlstate)
+$$ LANGUAGE plpythonu;
+SELECT specific_exception(2);
+ specific_exception 
+--------------------
+ 
+(1 row)
+
+SELECT specific_exception(NULL);
+NOTICE:  Violated the NOT NULL constraint, sqlstate 23502
+CONTEXT:  PL/Python function "specific_exception"
+ specific_exception 
+--------------------
+ 
+(1 row)
+
+SELECT specific_exception(2);
+NOTICE:  Violated the UNIQUE constraint, sqlstate 23505
+CONTEXT:  PL/Python function "specific_exception"
+ specific_exception 
+--------------------
+ 
+(1 row)
+
 /* manually starting subtransactions - a bad idea
  */
 CREATE FUNCTION manual_subxact() RETURNS void AS $$
diff --git a/src/pl/plpython/expected/plpython_error_0.out b/src/pl/plpython/expected/plpython_error_0.out
index 42e4119630..1b65d35fc0 100644
--- a/src/pl/plpython/expected/plpython_error_0.out
+++ b/src/pl/plpython/expected/plpython_error_0.out
@@ -32,7 +32,7 @@ CREATE FUNCTION sql_syntax_error() RETURNS text
 'plpy.execute("syntax error")'
         LANGUAGE plpythonu;
 SELECT sql_syntax_error();
-ERROR:  plpy.SPIError: syntax error at or near "syntax"
+ERROR:  spiexceptions.SyntaxError: syntax error at or near "syntax"
 LINE 1: syntax error
         ^
 QUERY:  syntax error
@@ -54,7 +54,7 @@ CREATE FUNCTION exception_index_invalid_nested() RETURNS text
 return rv[0]'
 	LANGUAGE plpythonu;
 SELECT exception_index_invalid_nested();
-ERROR:  plpy.SPIError: function test5(unknown) does not exist
+ERROR:  spiexceptions.UndefinedFunction: function test5(unknown) does not exist
 LINE 1: SELECT test5('foo')
                ^
 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
@@ -74,7 +74,7 @@ return None
 '
 	LANGUAGE plpythonu;
 SELECT invalid_type_uncaught('rick');
-ERROR:  plpy.SPIError: type "test" does not exist
+ERROR:  spiexceptions.UndefinedObject: type "test" does not exist
 CONTEXT:  PL/Python function "invalid_type_uncaught"
 /* for what it's worth catch the exception generated by
  * the typo, and return None
@@ -140,6 +140,44 @@ SELECT valid_type('rick');
  
 (1 row)
 
+/* check catching specific types of exceptions
+ */
+CREATE TABLE specific (
+    i integer PRIMARY KEY
+);
+NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "specific_pkey" for table "specific"
+CREATE FUNCTION specific_exception(i integer) RETURNS void AS
+$$
+from plpy import spiexceptions
+try:
+    plpy.execute("insert into specific values (%s)" % (i or "NULL"));
+except spiexceptions.NotNullViolation, e:
+    plpy.notice("Violated the NOT NULL constraint, sqlstate %s" % e.sqlstate)
+except spiexceptions.UniqueViolation, e:
+    plpy.notice("Violated the UNIQUE constraint, sqlstate %s" % e.sqlstate)
+$$ LANGUAGE plpythonu;
+SELECT specific_exception(2);
+ specific_exception 
+--------------------
+ 
+(1 row)
+
+SELECT specific_exception(NULL);
+NOTICE:  Violated the NOT NULL constraint, sqlstate 23502
+CONTEXT:  PL/Python function "specific_exception"
+ specific_exception 
+--------------------
+ 
+(1 row)
+
+SELECT specific_exception(2);
+NOTICE:  Violated the UNIQUE constraint, sqlstate 23505
+CONTEXT:  PL/Python function "specific_exception"
+ specific_exception 
+--------------------
+ 
+(1 row)
+
 /* manually starting subtransactions - a bad idea
  */
 CREATE FUNCTION manual_subxact() RETURNS void AS $$
diff --git a/src/pl/plpython/expected/plpython_subtransaction.out b/src/pl/plpython/expected/plpython_subtransaction.out
index 01b40a09a7..50d97faa78 100644
--- a/src/pl/plpython/expected/plpython_subtransaction.out
+++ b/src/pl/plpython/expected/plpython_subtransaction.out
@@ -43,7 +43,7 @@ SELECT * FROM subtransaction_tbl;
 
 TRUNCATE subtransaction_tbl;
 SELECT subtransaction_test('SPI');
-ERROR:  plpy.SPIError: invalid input syntax for integer: "oops"
+ERROR:  spiexceptions.InvalidTextRepresentation: invalid input syntax for integer: "oops"
 LINE 1: INSERT INTO subtransaction_tbl VALUES ('oops')
                                                ^
 QUERY:  INSERT INTO subtransaction_tbl VALUES ('oops')
@@ -89,7 +89,7 @@ SELECT * FROM subtransaction_tbl;
 
 TRUNCATE subtransaction_tbl;
 SELECT subtransaction_ctx_test('SPI');
-ERROR:  plpy.SPIError: invalid input syntax for integer: "oops"
+ERROR:  spiexceptions.InvalidTextRepresentation: invalid input syntax for integer: "oops"
 LINE 1: INSERT INTO subtransaction_tbl VALUES ('oops')
                                                ^
 QUERY:  INSERT INTO subtransaction_tbl VALUES ('oops')
@@ -126,7 +126,7 @@ with plpy.subtransaction():
 return "ok"
 $$ LANGUAGE plpythonu;
 SELECT subtransaction_nested_test();
-ERROR:  plpy.SPIError: syntax error at or near "error"
+ERROR:  spiexceptions.SyntaxError: syntax error at or near "error"
 LINE 1: error
         ^
 QUERY:  error
@@ -138,7 +138,7 @@ SELECT * FROM subtransaction_tbl;
 
 TRUNCATE subtransaction_tbl;
 SELECT subtransaction_nested_test('t');
-NOTICE:  Swallowed SPIError('syntax error at or near "error"',)
+NOTICE:  Swallowed SyntaxError('syntax error at or near "error"',)
 CONTEXT:  PL/Python function "subtransaction_nested_test"
  subtransaction_nested_test 
 ----------------------------
@@ -164,7 +164,7 @@ with plpy.subtransaction():
 return "ok"
 $$ LANGUAGE plpythonu;
 SELECT subtransaction_deeply_nested_test();
-NOTICE:  Swallowed SPIError('syntax error at or near "error"',)
+NOTICE:  Swallowed SyntaxError('syntax error at or near "error"',)
 CONTEXT:  PL/Python function "subtransaction_nested_test"
 SQL statement "SELECT subtransaction_nested_test('t')"
 PL/Python function "subtransaction_nested_test"
diff --git a/src/pl/plpython/expected/plpython_subtransaction_0.out b/src/pl/plpython/expected/plpython_subtransaction_0.out
index c5575fd715..164e9878ad 100644
--- a/src/pl/plpython/expected/plpython_subtransaction_0.out
+++ b/src/pl/plpython/expected/plpython_subtransaction_0.out
@@ -43,7 +43,7 @@ SELECT * FROM subtransaction_tbl;
 
 TRUNCATE subtransaction_tbl;
 SELECT subtransaction_test('SPI');
-ERROR:  plpy.SPIError: invalid input syntax for integer: "oops"
+ERROR:  spiexceptions.InvalidTextRepresentation: invalid input syntax for integer: "oops"
 LINE 1: INSERT INTO subtransaction_tbl VALUES ('oops')
                                                ^
 QUERY:  INSERT INTO subtransaction_tbl VALUES ('oops')
diff --git a/src/pl/plpython/expected/plpython_test.out b/src/pl/plpython/expected/plpython_test.out
index 18eb6d1c28..7b2e1703f4 100644
--- a/src/pl/plpython/expected/plpython_test.out
+++ b/src/pl/plpython/expected/plpython_test.out
@@ -43,9 +43,9 @@ contents.sort()
 return ", ".join(contents)
 $$ LANGUAGE plpythonu;
 select module_contents();
-                                                                    module_contents                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------
- Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, quote_ident, quote_literal, quote_nullable, subtransaction, warning
+                                                                           module_contents                                                                            
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, quote_ident, quote_literal, quote_nullable, spiexceptions, subtransaction, warning
 (1 row)
 
 CREATE FUNCTION elog_test() RETURNS void
diff --git a/src/pl/plpython/generate-spiexceptions.pl b/src/pl/plpython/generate-spiexceptions.pl
new file mode 100644
index 0000000000..cf050d1db6
--- /dev/null
+++ b/src/pl/plpython/generate-spiexceptions.pl
@@ -0,0 +1,44 @@
+#!/usr/bin/perl
+#
+# Generate the spiexceptions.h header from errcodes.txt
+# Copyright (c) 2000-2011, PostgreSQL Global Development Group
+
+use warnings;
+use strict;
+
+print "/* autogenerated from src/backend/utils/errcodes.txt, do not edit */\n";
+print "/* there is deliberately not an #ifndef SPIEXCEPTIONS_H here */\n";
+
+open my $errcodes, $ARGV[0] or die;
+
+while (<$errcodes>) {
+    chomp;
+
+    # Skip comments
+    next if /^#/;
+    next if /^\s*$/;
+
+    # Skip section headers
+    next if /^Section:/;
+
+    die unless /^([^\s]{5})\s+([EWS])\s+([^\s]+)(?:\s+)?([^\s]+)?/;
+
+    (my $sqlstate,
+     my $type,
+     my $errcode_macro,
+     my $condition_name) = ($1, $2, $3, $4);
+
+    # Skip non-errors
+    next unless $type eq 'E';
+
+    # Skip lines without PL/pgSQL condition names
+    next unless defined($condition_name);
+
+    # Change some_error_condition to SomeErrorCondition
+    $condition_name =~ s/([a-z])([^_]*)(?:_|$)/\u$1$2/g;
+
+    print "{ \"spiexceptions.$condition_name\", " .
+	"\"$condition_name\", $errcode_macro },\n";
+}
+
+close $errcodes;
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 4744ee7beb..4cc0708dcb 100644
--- a/src/pl/plpython/plpython.c
+++ b/src/pl/plpython/plpython.c
@@ -268,6 +268,28 @@ typedef struct PLySubtransactionObject
 	bool		exited;
 } PLySubtransactionObject;
 
+/* A list of all known exceptions, generated from backend/utils/errcodes.txt */
+typedef struct ExceptionMap
+{
+	char	   *name;
+	char	   *classname;
+	int			sqlstate;
+} ExceptionMap;
+
+static const ExceptionMap exception_map[] = {
+#include "spiexceptions.h"
+	{NULL, NULL, 0}
+};
+
+/* A hash table mapping sqlstates to exceptions, for speedy lookup */
+static HTAB *PLy_spi_exceptions;
+
+typedef struct PLyExceptionEntry
+{
+	int			sqlstate;	/* hash key, must be first */
+	PyObject   *exc;		/* corresponding exception */
+} PLyExceptionEntry;
+
 
 /* function declarations */
 
@@ -310,7 +332,7 @@ __attribute__((format(printf, 2, 5)))
 __attribute__((format(printf, 3, 5)));
 
 /* like PLy_exception_set, but conserve more fields from ErrorData */
-static void PLy_spi_exception_set(ErrorData *edata);
+static void PLy_spi_exception_set(PyObject *excclass, ErrorData *edata);
 
 /* Get the innermost python procedure called from the backend */
 static char *PLy_procedure_name(PLyProcedure *);
@@ -3013,6 +3035,10 @@ static PyMethodDef PLy_methods[] = {
 	{NULL, NULL, 0, NULL}
 };
 
+static PyMethodDef PLy_exc_methods[] = {
+	{NULL, NULL, 0, NULL}
+};
+
 #if PY_MAJOR_VERSION >= 3
 static PyModuleDef PLy_module = {
 	PyModuleDef_HEAD_INIT,		/* m_base */
@@ -3021,6 +3047,18 @@ static PyModuleDef PLy_module = {
 	-1,							/* m_size */
 	PLy_methods,				/* m_methods */
 };
+
+static PyModuleDef PLy_exc_module = {
+	PyModuleDef_HEAD_INIT,		/* m_base */
+	"spiexceptions",			/* m_name */
+	NULL,						/* m_doc */
+	-1,							/* m_size */
+	PLy_exc_methods,			/* m_methods */
+	NULL,						/* m_reload */
+	NULL,						/* m_traverse */
+	NULL,						/* m_clear */
+	NULL						/* m_free */
+};
 #endif
 
 /* plan object methods */
@@ -3318,6 +3356,8 @@ PLy_spi_prepare(PyObject *self, PyObject *args)
 	PG_CATCH();
 	{
 		ErrorData	*edata;
+		PLyExceptionEntry *entry;
+		PyObject	*exc;
 
 		/* Save error info */
 		MemoryContextSwitchTo(oldcontext);
@@ -3338,8 +3378,14 @@ PLy_spi_prepare(PyObject *self, PyObject *args)
 		 */
 		SPI_restore_connection();
 
+		/* Look up the correct exception */
+		entry = hash_search(PLy_spi_exceptions, &(edata->sqlerrcode),
+							HASH_FIND, NULL);
+		/* We really should find it, but just in case have a fallback */
+		Assert(entry != NULL);
+		exc = entry ? entry->exc : PLy_exc_spi_error;
 		/* Make Python raise the exception */
-		PLy_spi_exception_set(edata);
+		PLy_spi_exception_set(exc, edata);
 		return NULL;
 	}
 	PG_END_TRY();
@@ -3490,6 +3536,8 @@ PLy_spi_execute_plan(PyObject *ob, PyObject *list, long limit)
 	{
 		int			k;
 		ErrorData	*edata;
+		PLyExceptionEntry *entry;
+		PyObject	*exc;
 
 		/* Save error info */
 		MemoryContextSwitchTo(oldcontext);
@@ -3521,8 +3569,14 @@ PLy_spi_execute_plan(PyObject *ob, PyObject *list, long limit)
 		 */
 		SPI_restore_connection();
 
+		/* Look up the correct exception */
+		entry = hash_search(PLy_spi_exceptions, &(edata->sqlerrcode),
+							HASH_FIND, NULL);
+		/* We really should find it, but just in case have a fallback */
+		Assert(entry != NULL);
+		exc = entry ? entry->exc : PLy_exc_spi_error;
 		/* Make Python raise the exception */
-		PLy_spi_exception_set(edata);
+		PLy_spi_exception_set(exc, edata);
 		return NULL;
 	}
 	PG_END_TRY();
@@ -3582,7 +3636,9 @@ PLy_spi_execute_query(char *query, long limit)
 	}
 	PG_CATCH();
 	{
-		ErrorData	*edata;
+		ErrorData				*edata;
+		PLyExceptionEntry		*entry;
+		PyObject				*exc;
 
 		/* Save error info */
 		MemoryContextSwitchTo(oldcontext);
@@ -3601,8 +3657,14 @@ PLy_spi_execute_query(char *query, long limit)
 		 */
 		SPI_restore_connection();
 
+		/* Look up the correct exception */
+		entry = hash_search(PLy_spi_exceptions, &edata->sqlerrcode,
+							HASH_FIND, NULL);
+		/* We really should find it, but just in case have a fallback */
+		Assert(entry != NULL);
+		exc = entry ? entry->exc : PLy_exc_spi_error;
 		/* Make Python raise the exception */
-		PLy_spi_exception_set(edata);
+		PLy_spi_exception_set(exc, edata);
 		return NULL;
 	}
 	PG_END_TRY();
@@ -3832,9 +3894,49 @@ PLy_subtransaction_exit(PyObject *self, PyObject *args)
 /*
  * Add exceptions to the plpy module
  */
+
+/*
+ * Add all the autogenerated exceptions as subclasses of SPIError
+ */
+static void
+PLy_generate_spi_exceptions(PyObject *mod, PyObject *base)
+{
+	int			i;
+
+	for (i = 0; exception_map[i].name != NULL; i++)
+	{
+		bool		found;
+		PyObject   *exc;
+		PLyExceptionEntry *entry;
+		PyObject   *sqlstate;
+		PyObject   *dict = PyDict_New();
+
+		sqlstate = PyString_FromString(unpack_sql_state(exception_map[i].sqlstate));
+		PyDict_SetItemString(dict, "sqlstate", sqlstate);
+		Py_DECREF(sqlstate);
+		exc = PyErr_NewException(exception_map[i].name, base, dict);
+		PyModule_AddObject(mod, exception_map[i].classname, exc);
+		entry = hash_search(PLy_spi_exceptions, &exception_map[i].sqlstate,
+							HASH_ENTER, &found);
+		entry->exc = exc;
+		Assert(!found);
+	}
+}
+
 static void
 PLy_add_exceptions(PyObject *plpy)
 {
+	PyObject   *excmod;
+	HASHCTL		hash_ctl;
+
+#if PY_MAJOR_VERSION < 3
+	excmod = Py_InitModule("spiexceptions", PLy_exc_methods);
+#else
+	excmod = PyModule_Create(&PLy_exc_module);
+#endif
+	if (PyModule_AddObject(plpy, "spiexceptions", excmod) < 0)
+		PLy_elog(ERROR, "failed to add the spiexceptions module");
+
 	PLy_exc_error = PyErr_NewException("plpy.Error", NULL, NULL);
 	PLy_exc_fatal = PyErr_NewException("plpy.Fatal", NULL, NULL);
 	PLy_exc_spi_error = PyErr_NewException("plpy.SPIError", NULL, NULL);
@@ -3845,6 +3947,15 @@ PLy_add_exceptions(PyObject *plpy)
 	PyModule_AddObject(plpy, "Fatal", PLy_exc_fatal);
 	Py_INCREF(PLy_exc_spi_error);
 	PyModule_AddObject(plpy, "SPIError", PLy_exc_spi_error);
+
+	memset(&hash_ctl, 0, sizeof(hash_ctl));
+	hash_ctl.keysize = sizeof(int);
+	hash_ctl.entrysize = sizeof(PLyExceptionEntry);
+	hash_ctl.hash = tag_hash;
+	PLy_spi_exceptions = hash_create("SPI exceptions", 256,
+									 &hash_ctl, HASH_ELEM | HASH_FUNCTION);
+
+	PLy_generate_spi_exceptions(excmod, PLy_exc_spi_error);
 }
 
 #if PY_MAJOR_VERSION >= 3
@@ -4205,7 +4316,7 @@ PLy_exception_set_plural(PyObject *exc,
  * internal query and error position.
  */
 static void
-PLy_spi_exception_set(ErrorData *edata)
+PLy_spi_exception_set(PyObject *excclass, ErrorData *edata)
 {
 	PyObject	*args = NULL;
 	PyObject	*spierror = NULL;
@@ -4215,8 +4326,8 @@ PLy_spi_exception_set(ErrorData *edata)
 	if (!args)
 		goto failure;
 
-	/* create a new SPIError with the error message as the parameter */
-	spierror = PyObject_CallObject(PLy_exc_spi_error, args);
+	/* create a new SPI exception with the error message as the parameter */
+	spierror = PyObject_CallObject(excclass, args);
 	if (!spierror)
 		goto failure;
 
@@ -4228,7 +4339,7 @@ PLy_spi_exception_set(ErrorData *edata)
 	if (PyObject_SetAttrString(spierror, "spidata", spidata) == -1)
 		goto failure;
 
-	PyErr_SetObject(PLy_exc_spi_error, spierror);
+	PyErr_SetObject(excclass, spierror);
 
 	Py_DECREF(args);
 	Py_DECREF(spierror);
diff --git a/src/pl/plpython/sql/plpython_error.sql b/src/pl/plpython/sql/plpython_error.sql
index 7861cd61a2..0f456f4bc3 100644
--- a/src/pl/plpython/sql/plpython_error.sql
+++ b/src/pl/plpython/sql/plpython_error.sql
@@ -131,6 +131,27 @@ return None
 
 SELECT valid_type('rick');
 
+/* check catching specific types of exceptions
+ */
+CREATE TABLE specific (
+    i integer PRIMARY KEY
+);
+
+CREATE FUNCTION specific_exception(i integer) RETURNS void AS
+$$
+from plpy import spiexceptions
+try:
+    plpy.execute("insert into specific values (%s)" % (i or "NULL"));
+except spiexceptions.NotNullViolation, e:
+    plpy.notice("Violated the NOT NULL constraint, sqlstate %s" % e.sqlstate)
+except spiexceptions.UniqueViolation, e:
+    plpy.notice("Violated the UNIQUE constraint, sqlstate %s" % e.sqlstate)
+$$ LANGUAGE plpythonu;
+
+SELECT specific_exception(2);
+SELECT specific_exception(NULL);
+SELECT specific_exception(2);
+
 /* manually starting subtransactions - a bad idea
  */
 CREATE FUNCTION manual_subxact() RETURNS void AS $$
diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm
index 49504d7c59..e1fe4e9e00 100644
--- a/src/tools/msvc/Solution.pm
+++ b/src/tools/msvc/Solution.pm
@@ -273,6 +273,12 @@ s{PG_VERSION_STR "[^"]+"}{__STRINGIFY(x) #x\n#define __STRINGIFY2(z) __STRINGIFY
         );
     }
 
+    if ($self->{options}->{python} && IsNewer('src\pl\plpython\spiexceptions.h','src\include\backend\errcodes.txt'))
+    {
+        print "Generating spiexceptions.h...\n";
+        system('perl src\pl\plpython\generate-spiexceptions.pl src\backend\utils\errcodes.txt > src\pl\plpython\spiexceptions.h');
+    }
+
     if (IsNewer('src\include\utils\errcodes.h','src\backend\utils\errcodes.txt'))
     {
         print "Generating errcodes.h...\n";
-- 
2.40.0