On 3/25/16 3:11 PM, Tom Lane wrote:
Jim Nasby <jim.na...@bluetreble.com> writes:
the data, we're making it unnecessarily hard. All we need is one more
field in there, and you can simplify that to
Ahh, nice.
I think actually it's a simple point: there won't ever be a case where
cursorpos is set here, because that's only used for top-level SQL syntax
errors. Anything we are catching would be an internal-query error, so
we might as well not confuse PL/Tcl users with the distinction but just
report internalquery/internalpos as the statement and cursor position.
PLy_spi_exception_set simply exposes the raw internalquery and internalpos.
Right, because that's all that could be useful.
Ahh, ok, finally I get it.
It would be nice if the comments for ErrorData were clearer...
it strikes me that this is not coding style we want to encourage.
We should borrow the infrastructure plpgsql has for converting
SQLSTATEs into condition names, so that that can be more like
Yeah, Karl and I were just talking about that as we were finishing up
the docs changes (ironically, as you were commiting this...).
I ended up with a more realistic example that also demonstrates that you
can refer to errorCode in a separate function if desired. That patch
attached for posterity.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml
index d2175d5..4cf32df 100644
--- a/doc/src/sgml/pltcl.sgml
+++ b/doc/src/sgml/pltcl.sgml
@@ -775,6 +775,169 @@ CREATE EVENT TRIGGER tcl_a_snitch ON ddl_command_start
EXECUTE PROCEDURE tclsnit
</para>
</sect1>
+ <sect1 id="pltcl-error-handling">
+ <title>Error Handling in PL/Tcl</title>
+
+ <indexterm>
+ <primary>error handling</primary>
+ <secondary>in PL/Tcl</secondary>
+ </indexterm>
+
+ <para>
+ All Tcl errors that occur within a stored procedure and are not caught
+ using Tcl's <function>catch</function> or <function>try</function>
+ functions will raise a database error.
+ </para>
+ <para>
+ Tcl code can raise a database error by invoking the
+ <function>elog</function> command provided by PL/Tcl or by generating an
+ error using the Tcl <function>error</function> command and not catching it
+ with Tcl's <function>catch</function> command.
+ </para>
+ <para>
+ Database errors that occur from the PL/Tcl stored procedure's
+ use of <function>spi_exec</function>, <function>spi_prepare</function>,
+ and <function>spi_execp</function> are also catchable by Tcl's
+ <function>catch</function> command.
+ </para>
+ <para>
+ Tcl provides an <varname>errorCode</varname> variable that can represent
+ additional information about the error in a form that is easy for programs
+ to interpret. The contents are a Tcl list format. The first word
+ identifies the subsystem or library responsible for the error. The
+ remaining contents are up to the individual code or library. For example
+ if Tcl's <function>open</function> command is asked to open a file that
+ doesn't exist, <varname>errorCode</varname> might contain <literal>POSIX
+ ENOENT {no such file or directory}</literal> where the third element may
+ vary by locale but the first and second will not.
+ </para>
+ <para>
+ When <function>spi_exec</function>, <function>spi_prepare</function>
+ or <function>spi_execp</function> cause a database error to be raised,
+ that database eror propagates back to Tcl as a Tcl error. In this case
+ <varname>errorCode</varname> is set to a list where the first element is
+ <literal>POSTGRES</literal> followed by details of the Postgres error.
+ Since fields in the structure may or may not be present depending on the
+ nature of the error, how the function was invoked, etc, PL/Tcl has adopted
+ the convention that subsequent elements of the
+ <varname>errorCode</varname> list are key-value pairs where the first
+ value is the name of the field and the second is its value.
+ </para>
+ <para>
+ Fields that may be present include <varname>SQLSTATE</varname>,
+ <varname>message</varname>,
+ <varname>detail</varname>,
+ <varname>hint</varname>,
+ <varname>context</varname>,
+ <varname>schema</varname>,
+ <varname>table</varname>,
+ <varname>column</varname>,
+ <varname>datatype</varname>,
+ <varname>constraint</varname>,
+ <varname>cursor_position</varname>,
+ <varname>internalquery</varname>,
+ <varname>internal_position</varname>,
+ <varname>filename</varname>,
+ <varname>lineno</varname> and
+ <varname>funcname</varname>.
+ </para>
+ <para>
+ You might find it useful to load the results into an array. Code
+ for doing that might look like
+<programlisting>
+if {[lindex $errorCode 0] == "POSTGRES"} {
+ array set errorRow [lrange $errorCode 1 end]
+}
+</programlisting>
+ </para>
+ <para>
+ This example shows how to trap a specific SQL error.
+<programlisting>
+CREATE TABLE account(user_name varchar(1) NOT NULL PRIMARY KEY);
+CREATE OR REPLACE FUNCTION public.create_user(user_name text)
+ RETURNS void LANGUAGE pltcl AS $function$
+set prep [ spi_prepare "INSERT INTO account(user_name) VALUES(\$1)" [ list
text ] ]
+if [ catch {
+ spi_execp $prep [ list $1 ]
+ } msg ] {
+ if {[lindex $::errorCode 0] == "POSTGRES"} {
+ array set errorData [lrange $::errorCode 1 end]
+ if { $errorData(SQLSTATE) == "23505" && $errorData(constraint) ==
"account_pkey" } {
+ return -code error "user '$1' already exists"
+ }
+ }
+ throw $::errorCode $msg
+}
+$function$;
+</programlisting>
+<screen>
+<computeroutput>
+SELECT create_user('a');
+ create_user
+-------------
+
+(1 row)
+
+SELECT create_user('a');
+ERROR: user 'a' already exists
+CONTEXT: user 'a' already exists
+ while executing
+"__PLTcl_proc_16388 a"
+in PL/Tcl function "create_user"
+</computeroutput>
+</screen>
+ </para>
+ <para>
+ This is an example of inspecting the raw errorCode data. (The
+ double-colons explicitly reference <varname>errorCode</varname> as a
+ global variable.)
+<programlisting>
+CREATE FUNCTION get_error_code() RETURNS text LANGUAGE pltcl AS $$
+join $::errorCode "\n"
+$$;
+</programlisting>
+<screen>
+<computeroutput>
+SELECT create_user('aa');
+ERROR: value too long for type character varying(1)
+CONTEXT: value too long for type character varying(1)
+ while executing
+"throw $::errorCode $msg"
+ invoked from within
+"if [ catch {
+ spi_execp $prep [ list $1 ]
+ } msg ] {
+ if {[lindex $::errorCode 0] == "POSTGRES"} {
+ array set errorData [lrange $:..."
+ (procedure "__PLTcl_proc_16388" line 4)
+ invoked from within
+"__PLTcl_proc_16388 aa"
+in PL/Tcl function "create_user"
+
+SELECT get_error_code();
+ get_error_code
+-----------------------------------------------------------
+ POSTGRES +
+ SQLSTATE +
+ 22001 +
+ message +
+ value too long for type character varying(1) +
+ context +
+ SQL statement "INSERT INTO account(user_name) VALUES($1)"+
+ cursor_position +
+ 0 +
+ filename +
+ varchar.c +
+ lineno +
+ 624 +
+ funcname +
+ varchar
+(1 row)
+</computeroutput>
+</screen>
+ </para>
+ </sect1>
+
<sect1 id="pltcl-unknown">
<title>Modules and the <function>unknown</> Command</title>
<para>
diff --git a/src/pl/tcl/expected/pltcl_setup.out
b/src/pl/tcl/expected/pltcl_setup.out
index e11718c..9a9d7d1 100644
--- a/src/pl/tcl/expected/pltcl_setup.out
+++ b/src/pl/tcl/expected/pltcl_setup.out
@@ -555,3 +555,47 @@ NOTICE: tclsnitch: ddl_command_start DROP TABLE
NOTICE: tclsnitch: ddl_command_end DROP TABLE
drop event trigger tcl_a_snitch;
drop event trigger tcl_b_snitch;
+-- test error handling
+/*
+ * The ugly hack of messsing with the verbosity is because the error context is
+ * set to the TCL variable errorInfo, which contains some unstable data (namely
+ * the full name of the TCL function created by the handler, which includes the
+ * Postgres backend PID).
+ */
+\set VERBOSITY terse
+CREATE OR REPLACE FUNCTION pg_temp.tcl_eval (varchar) RETURNS varchar AS $$
+eval $1
+$$ LANGUAGE pltcl;
+select pg_temp.tcl_eval('spi_exec "select * from foo;"');
+ERROR: relation "foo" does not exist
+select pg_temp.tcl_eval($$
+set list [lindex $::errorCode 0];
+foreach "key value" [lrange $::errorCode 1 end] {
+ if {$key == "domain" || $key == "context_domain" || $key == "lineno"} {
+ regsub -all {[0-9]} $value "" value
+ }
+ lappend list $key $value
+};
+return [join $list "\n"]
+$$);
+ tcl_eval
+-------------------------------
+ POSTGRES +
+ SQLSTATE +
+ 42P01 +
+ message +
+ relation "foo" does not exist+
+ cursor_position +
+ 0 +
+ internalquery +
+ select * from foo; +
+ internal_position +
+ 15 +
+ filename +
+ parse_relation.c +
+ lineno +
+ +
+ funcname +
+ parserOpenTable
+(1 row)
+
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
index 5b27c73..1c7678f 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -1576,6 +1576,85 @@ compile_pltcl_function(Oid fn_oid, Oid tgreloid,
return prodesc;
}
+/**********************************************************************
+ * pltcl_construct_errorCode() - construct a Tcl errorCode
+ * list with detailed information from the PostgreSQL server
+ **********************************************************************/
+static void
+pltcl_construct_errorCode(Tcl_Interp *interp, ErrorData *edata)
+{
+ Tcl_Obj *obj = Tcl_NewObj();
+
+ UTF_BEGIN;
+ Tcl_ListObjAppendElement(interp, obj, Tcl_NewStringObj("POSTGRES", -1));
+ Tcl_ListObjAppendElement(interp, obj, Tcl_NewStringObj("SQLSTATE", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(unpack_sql_state(edata->sqlerrcode), -1));
+ Tcl_ListObjAppendElement(interp, obj, Tcl_NewStringObj("message", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->message), -1));
+
+ if (edata->detail)
+ {
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("detail", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->detail), -1));
+ }
+ if (edata->hint)
+ {
+ Tcl_ListObjAppendElement(interp, obj, Tcl_NewStringObj("hint",
-1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->hint), -1));
+ }
+ if (edata->context)
+ {
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("context", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->context), -1));
+ }
+ if (edata->schema_name)
+ {
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("schema", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->schema_name), -1));
+ }
+ if (edata->table_name)
+ {
+ Tcl_ListObjAppendElement(interp, obj, Tcl_NewStringObj("table",
-1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->table_name), -1));
+ }
+ if (edata->column_name)
+ {
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("column", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->column_name), -1));
+ }
+ if (edata->datatype_name)
+ {
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("datatype", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->datatype_name), -1));
+ }
+ if (edata->constraint_name)
+ {
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("constraint", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->constraint_name), -1));
+ }
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("cursor_position", -1));
+ Tcl_ListObjAppendElement(interp, obj, Tcl_NewIntObj(edata->cursorpos));
+ if (edata->internalquery)
+ {
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("internalquery", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->internalquery), -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("internal_position", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewIntObj(edata->internalpos));
+ }
+ if (edata->filename)
+ {
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("filename", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->filename), -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("lineno", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewIntObj(edata->lineno));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj("funcname", -1));
+ Tcl_ListObjAppendElement(interp, obj,
Tcl_NewStringObj(UTF_E2U(edata->funcname), -1));
+ }
+ UTF_END;
+
+ Tcl_SetObjErrorCode(interp, obj);
+}
+
/**********************************************************************
* pltcl_elog() - elog() support for PLTcl
@@ -1652,6 +1731,7 @@ pltcl_elog(ClientData cdata, Tcl_Interp *interp,
UTF_BEGIN;
Tcl_SetObjResult(interp,
Tcl_NewStringObj(UTF_E2U(edata->message), -1));
UTF_END;
+ pltcl_construct_errorCode(interp, edata);
FreeErrorData(edata);
return TCL_ERROR;
@@ -1884,6 +1964,7 @@ pltcl_subtrans_abort(Tcl_Interp *interp,
UTF_BEGIN;
Tcl_SetResult(interp, UTF_E2U(edata->message), TCL_VOLATILE);
UTF_END;
+ pltcl_construct_errorCode(interp, edata);
FreeErrorData(edata);
}
diff --git a/src/pl/tcl/sql/pltcl_setup.sql b/src/pl/tcl/sql/pltcl_setup.sql
index 53358ea..3ee8583 100644
--- a/src/pl/tcl/sql/pltcl_setup.sql
+++ b/src/pl/tcl/sql/pltcl_setup.sql
@@ -595,3 +595,29 @@ drop table foo;
drop event trigger tcl_a_snitch;
drop event trigger tcl_b_snitch;
+
+
+-- test error handling
+
+/*
+ * The ugly hack of messsing with the verbosity is because the error context is
+ * set to the TCL variable errorInfo, which contains some unstable data (namely
+ * the full name of the TCL function created by the handler, which includes the
+ * Postgres backend PID).
+ */
+\set VERBOSITY terse
+CREATE OR REPLACE FUNCTION pg_temp.tcl_eval (varchar) RETURNS varchar AS $$
+eval $1
+$$ LANGUAGE pltcl;
+
+select pg_temp.tcl_eval('spi_exec "select * from foo;"');
+select pg_temp.tcl_eval($$
+set list [lindex $::errorCode 0];
+foreach "key value" [lrange $::errorCode 1 end] {
+ if {$key == "domain" || $key == "context_domain" || $key == "lineno"} {
+ regsub -all {[0-9]} $value "" value
+ }
+ lappend list $key $value
+};
+return [join $list "\n"]
+$$);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
