Here's a patch implementing custom Python exceptions for SPI errors
mentioned in
http://archives.postgresql.org/pgsql-hackers/2010-12/msg01991.php. It's
an incremental patch on top of the explicit-subxacts patch sent eariler.
Git branch for this patch:
https://github.com/wulczer/postgres/tree/custom-spi-exceptions.
What the patch does is provide a Python exception per each error defined
in utils/errcodes.h, and then raise the corresponding exception when a
SPI call fails. The parsing of errcodes.h in the Makefile is a little
grotty and would probably have to be ported to the Windows build system,
which I have no idea about.
With this patch you can do:
from plpy import spiexceptions
try:
plpy.execute("insert into t values (4)")
catch spiexceptions.UniqueViolation:
plpy.notice("unique constraint violation")
catch spiexceptions.NotNullViolation:
plpy.notice("not null constraint violation")
All exceptions inherint from plpy.SPIError, so code thta just catches a
blanket SPIErorr will continue to work.
The patch lacks user-facing docs, if it gets accepted I'll write some.
Not sure if we should provide a table similar to
http://www.postgresql.org/docs/current/static/errcodes-appendix.html, or
just refer to that table and state that the rule is changing underscores
to camel case...
Also, I just realised that this patch does not really depend on the
explicit-subxacts change, but rather only on the spi-in-subxacts, so if
need be I can generate it as an incremental changeset ofer the latter
and not the former.
Cheers,
Jan
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 33dddc6..dd5b445 100644
*** a/src/pl/plpython/Makefile
--- b/src/pl/plpython/Makefile
*************** rpathdir = $(python_libdir)
*** 38,44 ****
NAME = plpython$(python_majorversion)
OBJS = plpython.o
!
# Python on win32 ships with import libraries only for Microsoft Visual C++,
# which are not compatible with mingw gcc. Therefore we need to build a
--- 38,44 ----
NAME = plpython$(python_majorversion)
OBJS = plpython.o
! SPIEXCEPTIONS = spiexceptions.h
# Python on win32 ships with import libraries only for Microsoft Visual C++,
# which are not compatible with mingw gcc. Therefore we need to build a
*************** PSQLDIR = $(bindir)
*** 86,93 ****
include $(top_srcdir)/src/Makefile.shlib
! all: all-lib
install: all installdirs install-lib
ifeq ($(python_majorversion),2)
--- 86,113 ----
include $(top_srcdir)/src/Makefile.shlib
+ # A quite horrendous sed, but does the job. The steps are, in order:
+ # 1. Remove everything up to the line with Class 03. We only generate
+ # exceptions for errors, not for warnings or notices
+ # 2. Remove lines that don't define an error code
+ # 3. Change ERRCODE_XXX into { "spiexceptions.ERRCODE_XY_Z, "XY_Z", ERRCODE_XY_Z },
+ # 4. Leave an uppercase letter after a dot or a quote, change the rest
+ # into lowercase thus giving us
+ # { "spiexceptions.Errcode_xy_z, "Xy_z", ERRCODE_XY_Z },
+ # 5. change lowercase letters after an underscore into uppercase, giving us
+ # { "spiexceptions.ErrcodeXyZ, "XyZ", ERRCODE_XY_Z },
+ gen-spiexceptions:
+ echo "/* autogenerated from utils/errcodes.h, do not edit */" > $(SPIEXCEPTIONS)
+ sed -e '1,/Class 03/ d' \
+ -e '/^#define ERRCODE_.*MAKE_SQLSTATE/! d' \
+ -e 's|#define ERRCODE_\([^\t ]*\).*|{ "spiexceptions.\1", "\1", ERRCODE_\1 },|' \
+ -e 's|\(["\.]\)\([A-Z]\)\([^"]*\)|\1\2\L\3|g' \
+ -e 's|_\([a-z]\)|\u\1|g' \
+ $(top_srcdir)/src/include/utils/errcodes.h >> $(SPIEXCEPTIONS)
! .PHONY: gen-spiexceptions
!
! all: gen-spiexceptions all-lib
install: all installdirs install-lib
ifeq ($(python_majorversion),2)
*************** clean distclean maintainer-clean: clean-
*** 138,143 ****
--- 158,164 ----
rm -f $(OBJS)
rm -rf results
rm -f regression.diffs regression.out
+ rm -f $(SPIEXCEPTIONS)
ifeq ($(PORTNAME), win32)
rm -f python${pytverstr}.def
endif
diff --git a/src/pl/plpython/expected/plpython_error.out b/src/pl/plpython/expected/plpython_error.out
index 7fc8337..718ebce 100644
*** a/src/pl/plpython/expected/plpython_error.out
--- b/src/pl/plpython/expected/plpython_error.out
*************** CREATE FUNCTION sql_syntax_error() RETUR
*** 8,14 ****
'plpy.execute("syntax error")'
LANGUAGE plpythonu;
SELECT sql_syntax_error();
! ERROR: plpy.SPIError: syntax error at or near "syntax"
CONTEXT: PL/Python function "sql_syntax_error"
/* check the handling of uncaught python exceptions
*/
--- 8,14 ----
'plpy.execute("syntax error")'
LANGUAGE plpythonu;
SELECT sql_syntax_error();
! ERROR: spiexceptions.SyntaxError: syntax error at or near "syntax"
CONTEXT: PL/Python function "sql_syntax_error"
/* check the handling of uncaught python exceptions
*/
*************** CREATE FUNCTION exception_index_invalid_
*** 27,33 ****
return rv[0]'
LANGUAGE plpythonu;
SELECT exception_index_invalid_nested();
! ERROR: plpy.SPIError: function test5(unknown) does not exist
CONTEXT: PL/Python function "exception_index_invalid_nested"
/* a typo
*/
--- 27,33 ----
return rv[0]'
LANGUAGE plpythonu;
SELECT exception_index_invalid_nested();
! ERROR: spiexceptions.UndefinedFunction: function test5(unknown) does not exist
CONTEXT: PL/Python function "exception_index_invalid_nested"
/* a typo
*/
*************** return None
*** 43,49 ****
'
LANGUAGE plpythonu;
SELECT invalid_type_uncaught('rick');
! ERROR: plpy.SPIError: 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
--- 43,49 ----
'
LANGUAGE plpythonu;
SELECT invalid_type_uncaught('rick');
! 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
*************** SELECT valid_type('rick');
*** 109,111 ****
--- 109,149 ----
(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)
+
diff --git a/src/pl/plpython/expected/plpython_subxact.out b/src/pl/plpython/expected/plpython_subxact.out
index 25a5a4b..fd7e9d9 100644
*** a/src/pl/plpython/expected/plpython_subxact.out
--- b/src/pl/plpython/expected/plpython_subxact.out
*************** SELECT * FROM subxact_tbl;
*** 43,49 ****
TRUNCATE subxact_tbl;
SELECT subxact_test('SPI');
! ERROR: plpy.SPIError: invalid input syntax for integer: "oops"
CONTEXT: PL/Python function "subxact_test"
SELECT * FROM subxact_tbl;
i
--- 43,49 ----
TRUNCATE subxact_tbl;
SELECT subxact_test('SPI');
! ERROR: spiexceptions.InvalidTextRepresentation: invalid input syntax for integer: "oops"
CONTEXT: PL/Python function "subxact_test"
SELECT * FROM subxact_tbl;
i
*************** SELECT * FROM subxact_tbl;
*** 87,93 ****
TRUNCATE subxact_tbl;
SELECT subxact_ctx_test('SPI');
! ERROR: plpy.SPIError: invalid input syntax for integer: "oops"
CONTEXT: PL/Python function "subxact_ctx_test"
SELECT * FROM subxact_tbl;
i
--- 87,93 ----
TRUNCATE subxact_tbl;
SELECT subxact_ctx_test('SPI');
! ERROR: spiexceptions.InvalidTextRepresentation: invalid input syntax for integer: "oops"
CONTEXT: PL/Python function "subxact_ctx_test"
SELECT * FROM subxact_tbl;
i
*************** with plpy.subxact():
*** 122,128 ****
return "ok"
$$ LANGUAGE plpythonu;
SELECT subxact_nested_test();
! ERROR: plpy.SPIError: syntax error at or near "error"
CONTEXT: PL/Python function "subxact_nested_test"
SELECT * FROM subxact_tbl;
i
--- 122,128 ----
return "ok"
$$ LANGUAGE plpythonu;
SELECT subxact_nested_test();
! ERROR: spiexceptions.SyntaxError: syntax error at or near "error"
CONTEXT: PL/Python function "subxact_nested_test"
SELECT * FROM subxact_tbl;
i
*************** SELECT * FROM subxact_tbl;
*** 131,137 ****
TRUNCATE subxact_tbl;
SELECT subxact_nested_test('t');
! NOTICE: Swallowed SPIError('syntax error at or near "error"',)
CONTEXT: PL/Python function "subxact_nested_test"
subxact_nested_test
---------------------
--- 131,137 ----
TRUNCATE subxact_tbl;
SELECT subxact_nested_test('t');
! NOTICE: Swallowed SyntaxError('syntax error at or near "error"',)
CONTEXT: PL/Python function "subxact_nested_test"
subxact_nested_test
---------------------
*************** with plpy.subxact():
*** 157,163 ****
return "ok"
$$ LANGUAGE plpythonu;
SELECT subxact_deeply_nested_test();
! NOTICE: Swallowed SPIError('syntax error at or near "error"',)
CONTEXT: PL/Python function "subxact_nested_test"
SQL statement "select subxact_nested_test('t')"
PL/Python function "subxact_nested_test"
--- 157,163 ----
return "ok"
$$ LANGUAGE plpythonu;
SELECT subxact_deeply_nested_test();
! NOTICE: Swallowed SyntaxError('syntax error at or near "error"',)
CONTEXT: PL/Python function "subxact_nested_test"
SQL statement "select subxact_nested_test('t')"
PL/Python function "subxact_nested_test"
diff --git a/src/pl/plpython/expected/plpython_test.out b/src/pl/plpython/expected/plpython_test.out
index 674c739..ef2b284 100644
*** a/src/pl/plpython/expected/plpython_test.out
--- b/src/pl/plpython/expected/plpython_test.out
*************** contents.sort()
*** 43,51 ****
return ", ".join(contents)
$$ LANGUAGE plpythonu;
select module_contents();
! module_contents
! ----------------------------------------------------------------------------------------------------
! Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, subxact, warning
(1 row)
CREATE FUNCTION elog_test() RETURNS void
--- 43,51 ----
return ", ".join(contents)
$$ LANGUAGE plpythonu;
select module_contents();
! module_contents
! -------------------------------------------------------------------------------------------------------------------
! Error, Fatal, SPIError, debug, error, execute, fatal, info, log, notice, prepare, spiexceptions, subxact, warning
(1 row)
CREATE FUNCTION elog_test() RETURNS void
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index bd5dc65..69d8d2c 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*************** typedef struct PLySubxactObject
*** 258,263 ****
--- 258,285 ----
bool exited;
} PLySubxactObject;
+ /* A list of all known exceptions, generated from utils/errcodes.h */
+ typedef struct ExceptionMap
+ {
+ char *name;
+ char *classname;
+ int sqlstate;
+ } ExceptionMap;
+
+ static const ExceptionMap exception_map[] = {
+ #include "spiexceptions.h"
+ {NULL, NULL, 0}
+ };
+
+ /* A hashtable 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 */
#if PY_MAJOR_VERSION >= 3
*************** static PyMethodDef PLy_methods[] = {
*** 2765,2770 ****
--- 2787,2796 ----
{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 */
*************** static PyModuleDef PLy_module = {
*** 2777,2782 ****
--- 2803,2820 ----
NULL, /* m_clear */
NULL /* m_free */
};
+
+ 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 */
*************** PLy_spi_prepare(PyObject *self, PyObject
*** 3080,3086 ****
}
PG_CATCH();
{
! ErrorData *edata;
Py_DECREF(plan);
Py_XDECREF(optr);
--- 3118,3126 ----
}
PG_CATCH();
{
! ErrorData *edata;
! PLyExceptionEntry *entry;
! PyObject *exc;
Py_DECREF(plan);
Py_XDECREF(optr);
*************** PLy_spi_prepare(PyObject *self, PyObject
*** 3105,3112 ****
SPI_restore_connection();
}
/* Make Python raise the exception */
! PLy_exception_set(PLy_exc_spi_error, edata->message);
return NULL;
}
PG_END_TRY();
--- 3145,3158 ----
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_exception_set(exc, edata->message);
return NULL;
}
PG_END_TRY();
*************** PLy_spi_execute_plan(PyObject *ob, PyObj
*** 3257,3263 ****
}
PG_CATCH();
{
! ErrorData *edata;
/* Save error info */
MemoryContextSwitchTo(oldcontext);
--- 3303,3311 ----
}
PG_CATCH();
{
! ErrorData *edata;
! PLyExceptionEntry *entry;
! PyObject *exc;
/* Save error info */
MemoryContextSwitchTo(oldcontext);
*************** PLy_spi_execute_plan(PyObject *ob, PyObj
*** 3292,3299 ****
SPI_restore_connection();
}
/* Make Python raise the exception */
! PLy_exception_set(PLy_exc_spi_error, edata->message);
return NULL;
}
PG_END_TRY();
--- 3340,3353 ----
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_exception_set(exc, edata->message);
return NULL;
}
PG_END_TRY();
*************** PLy_spi_execute_query(char *query, long
*** 3352,3358 ****
}
PG_CATCH();
{
! ErrorData *edata;
/* Save error info */
MemoryContextSwitchTo(oldcontext);
--- 3406,3414 ----
}
PG_CATCH();
{
! ErrorData *edata;
! PLyExceptionEntry *entry;
! PyObject *exc;
/* Save error info */
MemoryContextSwitchTo(oldcontext);
*************** PLy_spi_execute_query(char *query, long
*** 3374,3381 ****
SPI_restore_connection();
}
/* Make Python raise the exception */
! PLy_exception_set(PLy_exc_spi_error, edata->message);
return NULL;
}
PG_END_TRY();
--- 3430,3443 ----
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_exception_set(exc, edata->message);
return NULL;
}
PG_END_TRY();
*************** static void PLy_add_exception(PyObject *
*** 3606,3625 ****
--- 3668,3724 ----
#endif
}
+ /* 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);
+ PLy_add_exception(mod, exception_map[i].classname, exc);
+ entry = hash_search(PLy_spi_exceptions, &exception_map[i].sqlstate,
+ HASH_ENTER, &found);
+ entry->exc = exc;
+ Assert(!found);
+ }
+ }
+
/* Add exceptions to the plpy module */
static void
PLy_initialize_exceptions(PyObject *plpy)
{
PyObject *mod;
+ PyObject *exc;
+ PyObject *excmod;
+ HASHCTL hash_ctl;
#if PY_MAJOR_VERSION < 3
/* For Python <3 we add the exceptions to the module dictionary */
mod = PyModule_GetDict(plpy);
+ excmod = Py_InitModule("spiexceptions", PLy_exc_methods);
+ exc = PyModule_GetDict(excmod);
#else
/* In Python 3 you add them directly into the module */
mod = plpy;
+ excmod = PyModule_Create(&PLy_exc_module);
+ exc = excmod;
#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);
*************** PLy_initialize_exceptions(PyObject *plpy
*** 3627,3632 ****
--- 3726,3740 ----
PLy_add_exception(mod, "Error", PLy_exc_error);
PLy_add_exception(mod, "Fatal", PLy_exc_fatal);
PLy_add_exception(mod, "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(exc, PLy_exc_spi_error);
}
/*
diff --git a/src/pl/plpython/sql/plpython_error.sql b/src/pl/plpython/sql/plpython_error.sql
index 5ca6849..2306c1d 100644
*** a/src/pl/plpython/sql/plpython_error.sql
--- b/src/pl/plpython/sql/plpython_error.sql
*************** return None
*** 107,109 ****
--- 107,130 ----
LANGUAGE plpythonu;
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);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
