1
-- test error handling, i forgot to restore Warn_restart in
2
-- the trigger handler once. the errors and subsequent core dump were
4
/* Flat out Python syntax error
6
CREATE FUNCTION python_syntax_error() RETURNS text
10
ERROR: could not compile PL/Python function "python_syntax_error"
11
DETAIL: SyntaxError: invalid syntax (<string>, line 2)
12
/* With check_function_bodies = false the function should get defined
13
* and the error reported when called
15
SET check_function_bodies = false;
16
CREATE FUNCTION python_syntax_error() RETURNS text
20
SELECT python_syntax_error();
21
ERROR: could not compile PL/Python function "python_syntax_error"
22
DETAIL: SyntaxError: invalid syntax (<string>, line 2)
23
/* Run the function twice to check if the hashtable entry gets cleaned up */
24
SELECT python_syntax_error();
25
ERROR: could not compile PL/Python function "python_syntax_error"
26
DETAIL: SyntaxError: invalid syntax (<string>, line 2)
27
RESET check_function_bodies;
28
/* Flat out syntax error
30
CREATE FUNCTION sql_syntax_error() RETURNS text
32
'plpy.execute("syntax error")'
34
SELECT sql_syntax_error();
35
ERROR: spiexceptions.SyntaxError: syntax error at or near "syntax"
39
CONTEXT: Traceback (most recent call last):
40
PL/Python function "sql_syntax_error", line 1, in <module>
41
plpy.execute("syntax error")
42
PL/Python function "sql_syntax_error"
43
/* check the handling of uncaught python exceptions
45
CREATE FUNCTION exception_index_invalid(text) RETURNS text
49
SELECT exception_index_invalid('test');
50
ERROR: IndexError: list index out of range
51
CONTEXT: Traceback (most recent call last):
52
PL/Python function "exception_index_invalid", line 1, in <module>
54
PL/Python function "exception_index_invalid"
55
/* check handling of nested exceptions
57
CREATE FUNCTION exception_index_invalid_nested() RETURNS text
59
'rv = plpy.execute("SELECT test5(''foo'')")
62
SELECT exception_index_invalid_nested();
63
ERROR: spiexceptions.UndefinedFunction: function test5(unknown) does not exist
64
LINE 1: SELECT test5('foo')
66
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
67
QUERY: SELECT test5('foo')
68
CONTEXT: Traceback (most recent call last):
69
PL/Python function "exception_index_invalid_nested", line 1, in <module>
70
rv = plpy.execute("SELECT test5('foo')")
71
PL/Python function "exception_index_invalid_nested"
74
CREATE FUNCTION invalid_type_uncaught(a text) RETURNS text
77
q = "SELECT fname FROM users WHERE lname = $1"
78
SD["plan"] = plpy.prepare(q, [ "test" ])
79
rv = plpy.execute(SD["plan"], [ a ])
85
SELECT invalid_type_uncaught('rick');
86
ERROR: spiexceptions.UndefinedObject: type "test" does not exist
87
CONTEXT: Traceback (most recent call last):
88
PL/Python function "invalid_type_uncaught", line 3, in <module>
89
SD["plan"] = plpy.prepare(q, [ "test" ])
90
PL/Python function "invalid_type_uncaught"
91
/* for what it's worth catch the exception generated by
92
* the typo, and return None
94
CREATE FUNCTION invalid_type_caught(a text) RETURNS text
97
q = "SELECT fname FROM users WHERE lname = $1"
99
SD["plan"] = plpy.prepare(q, [ "test" ])
100
except plpy.SPIError as ex:
103
rv = plpy.execute(SD["plan"], [ a ])
105
return rv[0]["fname"]
109
SELECT invalid_type_caught('rick');
110
NOTICE: type "test" does not exist
111
CONTEXT: PL/Python function "invalid_type_caught"
113
---------------------
117
/* for what it's worth catch the exception generated by
118
* the typo, and reraise it as a plain error
120
CREATE FUNCTION invalid_type_reraised(a text) RETURNS text
122
'if "plan" not in SD:
123
q = "SELECT fname FROM users WHERE lname = $1"
125
SD["plan"] = plpy.prepare(q, [ "test" ])
126
except plpy.SPIError as ex:
128
rv = plpy.execute(SD["plan"], [ a ])
130
return rv[0]["fname"]
134
SELECT invalid_type_reraised('rick');
135
ERROR: plpy.Error: type "test" does not exist
136
CONTEXT: Traceback (most recent call last):
137
PL/Python function "invalid_type_reraised", line 6, in <module>
139
PL/Python function "invalid_type_reraised"
140
/* no typo no messing about
142
CREATE FUNCTION valid_type(a text) RETURNS text
144
'if "plan" not in SD:
145
SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ])
146
rv = plpy.execute(SD["plan"], [ a ])
148
return rv[0]["fname"]
152
SELECT valid_type('rick');
158
/* error in nested functions to get a traceback
160
CREATE FUNCTION nested_error() RETURNS text
175
SELECT nested_error();
176
ERROR: plpy.Error: boom
177
CONTEXT: Traceback (most recent call last):
178
PL/Python function "nested_error", line 10, in <module>
180
PL/Python function "nested_error", line 8, in fun3
182
PL/Python function "nested_error", line 5, in fun2
184
PL/Python function "nested_error", line 2, in fun1
186
PL/Python function "nested_error"
187
/* raising plpy.Error is just like calling plpy.error
189
CREATE FUNCTION nested_error_raise() RETURNS text
192
raise plpy.Error("boom")
204
SELECT nested_error_raise();
205
ERROR: plpy.Error: boom
206
CONTEXT: Traceback (most recent call last):
207
PL/Python function "nested_error_raise", line 10, in <module>
209
PL/Python function "nested_error_raise", line 8, in fun3
211
PL/Python function "nested_error_raise", line 5, in fun2
213
PL/Python function "nested_error_raise", line 2, in fun1
214
raise plpy.Error("boom")
215
PL/Python function "nested_error_raise"
216
/* using plpy.warning should not produce a traceback
218
CREATE FUNCTION nested_warning() RETURNS text
230
return "you''ve been warned"
233
SELECT nested_warning();
235
CONTEXT: PL/Python function "nested_warning"
241
/* AttributeError at toplevel used to give segfaults with the traceback
243
CREATE FUNCTION toplevel_attribute_error() RETURNS void AS
246
$$ LANGUAGE plpython3u;
247
SELECT toplevel_attribute_error();
248
ERROR: AttributeError: module 'plpy' has no attribute 'nonexistent'
249
CONTEXT: Traceback (most recent call last):
250
PL/Python function "toplevel_attribute_error", line 2, in <module>
252
PL/Python function "toplevel_attribute_error"
253
/* Calling PL/Python functions from SQL and vice versa should not lose context.
255
CREATE OR REPLACE FUNCTION python_traceback() RETURNS void AS $$
263
plpy.execute("select sql_error()")
266
$$ LANGUAGE plpython3u;
267
CREATE OR REPLACE FUNCTION sql_error() RETURNS void AS $$
272
CREATE OR REPLACE FUNCTION python_from_sql_error() RETURNS void AS $$
274
select python_traceback();
277
CREATE OR REPLACE FUNCTION sql_from_python_error() RETURNS void AS $$
278
plpy.execute("select sql_error()")
279
$$ LANGUAGE plpython3u;
280
SELECT python_traceback();
281
ERROR: spiexceptions.DivisionByZero: division by zero
282
CONTEXT: Traceback (most recent call last):
283
PL/Python function "python_traceback", line 11, in <module>
285
PL/Python function "python_traceback", line 3, in first
287
PL/Python function "python_traceback", line 6, in second
289
PL/Python function "python_traceback", line 9, in third
290
plpy.execute("select sql_error()")
291
PL/Python function "python_traceback"
293
ERROR: division by zero
294
CONTEXT: SQL statement "select 1/0"
295
PL/pgSQL function sql_error() line 3 at SQL statement
296
SELECT python_from_sql_error();
297
ERROR: spiexceptions.DivisionByZero: division by zero
298
CONTEXT: Traceback (most recent call last):
299
PL/Python function "python_traceback", line 11, in <module>
301
PL/Python function "python_traceback", line 3, in first
303
PL/Python function "python_traceback", line 6, in second
305
PL/Python function "python_traceback", line 9, in third
306
plpy.execute("select sql_error()")
307
PL/Python function "python_traceback"
308
SQL statement "select python_traceback()"
309
PL/pgSQL function python_from_sql_error() line 3 at SQL statement
310
SELECT sql_from_python_error();
311
ERROR: spiexceptions.DivisionByZero: division by zero
312
CONTEXT: Traceback (most recent call last):
313
PL/Python function "sql_from_python_error", line 2, in <module>
314
plpy.execute("select sql_error()")
315
PL/Python function "sql_from_python_error"
316
/* check catching specific types of exceptions
318
CREATE TABLE specific (
319
i integer PRIMARY KEY
321
CREATE FUNCTION specific_exception(i integer) RETURNS void AS
323
from plpy import spiexceptions
325
plpy.execute("insert into specific values (%s)" % (i or "NULL"));
326
except spiexceptions.NotNullViolation as e:
327
plpy.notice("Violated the NOT NULL constraint, sqlstate %s" % e.sqlstate)
328
except spiexceptions.UniqueViolation as e:
329
plpy.notice("Violated the UNIQUE constraint, sqlstate %s" % e.sqlstate)
330
$$ LANGUAGE plpython3u;
331
SELECT specific_exception(2);
337
SELECT specific_exception(NULL);
338
NOTICE: Violated the NOT NULL constraint, sqlstate 23502
339
CONTEXT: PL/Python function "specific_exception"
345
SELECT specific_exception(2);
346
NOTICE: Violated the UNIQUE constraint, sqlstate 23505
347
CONTEXT: PL/Python function "specific_exception"
353
/* SPI errors in PL/Python functions should preserve the SQLSTATE value
355
CREATE FUNCTION python_unique_violation() RETURNS void AS $$
356
plpy.execute("insert into specific values (1)")
357
plpy.execute("insert into specific values (1)")
358
$$ LANGUAGE plpython3u;
359
CREATE FUNCTION catch_python_unique_violation() RETURNS text AS $$
362
perform python_unique_violation();
363
exception when unique_violation then
366
return 'not reached';
369
SELECT catch_python_unique_violation();
370
catch_python_unique_violation
371
-------------------------------
375
/* manually starting subtransactions - a bad idea
377
CREATE FUNCTION manual_subxact() RETURNS void AS $$
378
plpy.execute("savepoint save")
379
plpy.execute("create table foo(x integer)")
380
plpy.execute("rollback to save")
381
$$ LANGUAGE plpython3u;
382
SELECT manual_subxact();
383
ERROR: plpy.SPIError: SPI_execute failed: SPI_ERROR_TRANSACTION
384
CONTEXT: Traceback (most recent call last):
385
PL/Python function "manual_subxact", line 2, in <module>
386
plpy.execute("savepoint save")
387
PL/Python function "manual_subxact"
388
/* same for prepared plans
390
CREATE FUNCTION manual_subxact_prepared() RETURNS void AS $$
391
save = plpy.prepare("savepoint save")
392
rollback = plpy.prepare("rollback to save")
394
plpy.execute("create table foo(x integer)")
395
plpy.execute(rollback)
396
$$ LANGUAGE plpython3u;
397
SELECT manual_subxact_prepared();
398
ERROR: plpy.SPIError: SPI_execute_plan failed: SPI_ERROR_TRANSACTION
399
CONTEXT: Traceback (most recent call last):
400
PL/Python function "manual_subxact_prepared", line 4, in <module>
402
PL/Python function "manual_subxact_prepared"
403
/* raising plpy.spiexception.* from python code should preserve sqlstate
405
CREATE FUNCTION plpy_raise_spiexception() RETURNS void AS $$
406
raise plpy.spiexceptions.DivisionByZero()
407
$$ LANGUAGE plpython3u;
410
SELECT plpy_raise_spiexception();
411
EXCEPTION WHEN division_by_zero THEN
415
/* setting a custom sqlstate should be handled
417
CREATE FUNCTION plpy_raise_spiexception_override() RETURNS void AS $$
418
exc = plpy.spiexceptions.DivisionByZero()
419
exc.sqlstate = 'SILLY'
421
$$ LANGUAGE plpython3u;
424
SELECT plpy_raise_spiexception_override();
425
EXCEPTION WHEN SQLSTATE 'SILLY' THEN