On 23/12/10 15:40, Jan Urbański wrote:
> 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.
I changed that patch to use Perl instead of sed to generate the
exceptions, which should be a more portable. It's still not nice, and I
think the way forward is to have a common format for SQLSTATE
conditions, as proposed in
http://archives.postgresql.org/message-id/4d19c93c.5000...@wulczer.org.
I failed to follow on with that patch because I couldn't figure out how
to persuade the buildsystem to generate errcodes.h early enough for the
rest of the system to compile, not to mention doing it for the MSVC
build system.
Cheers,
Jan
diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile
index 33dddc6..0d7ddee 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,102 ----
include $(top_srcdir)/src/Makefile.shlib
+ .PHONY: gen-spiexceptions
! # Generate spiexceptions.h from utils/errcodes.h
! spiexceptions.h: $(top_srcdir)/src/include/utils/errcodes.h
! $(PERL) $(srcdir)/generate-spiexceptions.pl $^ > $(SPIEXCEPTIONS)
!
! gen-spiexceptions: $(SPIEXCEPTIONS)
!
! all: gen-spiexceptions all-lib
!
! distprep: gen-spiexceptions
install: all installdirs install-lib
ifeq ($(python_majorversion),2)
*************** clean distclean maintainer-clean: clean-
*** 138,143 ****
--- 147,153 ----
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/generate-spiexceptions.pl b/src/pl/plpython/generate-spiexceptions.pl
index ...d70f439 .
*** a/src/pl/plpython/generate-spiexceptions.pl
--- b/src/pl/plpython/generate-spiexceptions.pl
***************
*** 0 ****
--- 1,37 ----
+ #!/usr/bin/perl
+ #
+ # Generate the spiexceptions.h file from src/include/utils/errcodes.h
+ # The generated header file is used by PL/Python to create a Python
+ # exception object per SPI error code
+
+ use warnings;
+ use strict;
+
+ print "/* autogenerated from utils/errcodes.h, do not edit */\n";
+
+ # Are we processing errors, or still looking at success and warning codes
+ my $in_errors = 0;
+
+ open my $errcodes, $ARGV[0] or die;
+
+ while (<$errcodes>) {
+ chomp;
+
+ # Class 03 is the first one that includes errors
+ /Class 03/ && ($in_errors = 1);
+
+ next unless $in_errors;
+ next unless /^#define ERRCODE_.*MAKE_SQLSTATE/;
+
+ # Get the exception name
+ (my $exception) = /^#define ERRCODE_(\S*)/;
+
+ # Change SOME_ERROR_CONDITION to SomeErrorCondition
+ my $pyexception = $exception;
+ $pyexception =~ s/([A-Z])([^_]*)(?:_|$)/$1\L$2/g;
+
+ print "{ \"spiexceptions.$pyexception\", " .
+ "\"$pyexception\", ERRCODE_$exception },\n";
+ }
+
+ close $errcodes;
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 7506b54..468aedd 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
