Skip to content

Commit 15f55cc

Browse files
peterepetere
committed
Add validator to PL/Python
Jan Urbański, reviewed by Hitoshi Harada
1 parent 6c6e6f7 commit 15f55cc

File tree

10 files changed

+259
-7
lines changed

10 files changed

+259
-7
lines changed

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201101251
56+
#define CATALOG_VERSION_NO 201102011
5757

5858
#endif

‎src/include/catalog/pg_pltemplate.h

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -72,8 +72,8 @@ DATA(insert ( "pltcl" t t "pltcl_call_handler" _null_ _null_ "$libdir/pltcl" _n
7272
DATA(insert ( "pltclu" f f "pltclu_call_handler" _null_ _null_ "$libdir/pltcl" _null_ ));
7373
DATA(insert ( "plperl" t t "plperl_call_handler" "plperl_inline_handler" "plperl_validator" "$libdir/plperl" _null_ ));
7474
DATA(insert ( "plperlu" f f "plperl_call_handler" "plperl_inline_handler" "plperl_validator" "$libdir/plperl" _null_ ));
75-
DATA(insert ( "plpythonu" f f "plpython_call_handler" "plpython_inline_handler" _null_ "$libdir/plpython" _null_ ));
76-
DATA(insert ( "plpython2u" f f "plpython_call_handler" "plpython_inline_handler" _null_ "$libdir/plpython2" _null_ ));
77-
DATA(insert ( "plpython3u" f f "plpython3_call_handler" "plpython3_inline_handler" _null_ "$libdir/plpython3" _null_ ));
75+
DATA(insert ( "plpythonu" f f "plpython_call_handler" "plpython_inline_handler" "plpython_validator" "$libdir/plpython" _null_ ));
76+
DATA(insert ( "plpython2u" f f "plpython_call_handler" "plpython_inline_handler" "plpython_validator" "$libdir/plpython2" _null_ ));
77+
DATA(insert ( "plpython3u" f f "plpython3_call_handler" "plpython3_inline_handler" "plpython3_validator" "$libdir/plpython3" _null_ ));
7878

7979
#endif /* PG_PLTEMPLATE_H */

‎src/pl/plpython/expected/README

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,7 @@
11
Guide to alternative expected files:
22

3+
plpython_error_0.out Python 2.4 and older
4+
35
plpython_unicode.out any version, when server encoding != SQL_ASCII and client encoding = UTF8; else ...
46
plpython_unicode_0.out any version, when server encoding != SQL_ASCII and client encoding != UTF8; else ...
57
plpython_unicode_2.out Python 2.2

‎src/pl/plpython/expected/plpython_error.out

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,30 @@
11
-- test error handling, i forgot to restore Warn_restart in
22
-- the trigger handler once. the errors and subsequent core dump were
33
-- interesting.
4+
/* Flat out Python syntax error
5+
*/
6+
CREATE FUNCTION python_syntax_error() RETURNS text
7+
AS
8+
'.syntaxerror'
9+
LANGUAGE plpythonu;
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
14+
*/
15+
SET check_function_bodies = false;
16+
CREATE FUNCTION python_syntax_error() RETURNS text
17+
AS
18+
'.syntaxerror'
19+
LANGUAGE plpythonu;
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;
428
/* Flat out syntax error
529
*/
630
CREATE FUNCTION sql_syntax_error() RETURNS text

‎src/pl/plpython/expected/plpython_error_0.out

Lines changed: 152 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,152 @@
1+
-- test error handling, i forgot to restore Warn_restart in
2+
-- the trigger handler once. the errors and subsequent core dump were
3+
-- interesting.
4+
/* Flat out Python syntax error
5+
*/
6+
CREATE FUNCTION python_syntax_error() RETURNS text
7+
AS
8+
'.syntaxerror'
9+
LANGUAGE plpythonu;
10+
ERROR: could not compile PL/Python function "python_syntax_error"
11+
DETAIL: SyntaxError: invalid syntax (line 2)
12+
/* With check_function_bodies = false the function should get defined
13+
* and the error reported when called
14+
*/
15+
SET check_function_bodies = false;
16+
CREATE FUNCTION python_syntax_error() RETURNS text
17+
AS
18+
'.syntaxerror'
19+
LANGUAGE plpythonu;
20+
SELECT python_syntax_error();
21+
ERROR: could not compile PL/Python function "python_syntax_error"
22+
DETAIL: SyntaxError: invalid syntax (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 (line 2)
27+
RESET check_function_bodies;
28+
/* Flat out syntax error
29+
*/
30+
CREATE FUNCTION sql_syntax_error() RETURNS text
31+
AS
32+
'plpy.execute("syntax error")'
33+
LANGUAGE plpythonu;
34+
SELECT sql_syntax_error();
35+
WARNING: plpy.SPIError: unrecognized error in PLy_spi_execute_query
36+
CONTEXT: PL/Python function "sql_syntax_error"
37+
ERROR: plpy.SPIError: syntax error at or near "syntax"
38+
LINE 1: syntax error
39+
^
40+
QUERY: syntax error
41+
CONTEXT: PL/Python function "sql_syntax_error"
42+
/* check the handling of uncaught python exceptions
43+
*/
44+
CREATE FUNCTION exception_index_invalid(text) RETURNS text
45+
AS
46+
'return args[1]'
47+
LANGUAGE plpythonu;
48+
SELECT exception_index_invalid('test');
49+
ERROR: IndexError: list index out of range
50+
CONTEXT: PL/Python function "exception_index_invalid"
51+
/* check handling of nested exceptions
52+
*/
53+
CREATE FUNCTION exception_index_invalid_nested() RETURNS text
54+
AS
55+
'rv = plpy.execute("SELECT test5(''foo'')")
56+
return rv[0]'
57+
LANGUAGE plpythonu;
58+
SELECT exception_index_invalid_nested();
59+
WARNING: plpy.SPIError: unrecognized error in PLy_spi_execute_query
60+
CONTEXT: PL/Python function "exception_index_invalid_nested"
61+
ERROR: plpy.SPIError: function test5(unknown) does not exist
62+
LINE 1: SELECT test5('foo')
63+
^
64+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
65+
QUERY: SELECT test5('foo')
66+
CONTEXT: PL/Python function "exception_index_invalid_nested"
67+
/* a typo
68+
*/
69+
CREATE FUNCTION invalid_type_uncaught(a text) RETURNS text
70+
AS
71+
'if "plan" not in SD:
72+
q = "SELECT fname FROM users WHERE lname = $1"
73+
SD["plan"] = plpy.prepare(q, [ "test" ])
74+
rv = plpy.execute(SD["plan"], [ a ])
75+
if len(rv):
76+
return rv[0]["fname"]
77+
return None
78+
'
79+
LANGUAGE plpythonu;
80+
SELECT invalid_type_uncaught('rick');
81+
WARNING: plpy.SPIError: unrecognized error in PLy_spi_prepare
82+
CONTEXT: PL/Python function "invalid_type_uncaught"
83+
ERROR: plpy.SPIError: type "test" does not exist
84+
CONTEXT: PL/Python function "invalid_type_uncaught"
85+
/* for what it's worth catch the exception generated by
86+
* the typo, and return None
87+
*/
88+
CREATE FUNCTION invalid_type_caught(a text) RETURNS text
89+
AS
90+
'if "plan" not in SD:
91+
q = "SELECT fname FROM users WHERE lname = $1"
92+
try:
93+
SD["plan"] = plpy.prepare(q, [ "test" ])
94+
except plpy.SPIError, ex:
95+
plpy.notice(str(ex))
96+
return None
97+
rv = plpy.execute(SD["plan"], [ a ])
98+
if len(rv):
99+
return rv[0]["fname"]
100+
return None
101+
'
102+
LANGUAGE plpythonu;
103+
SELECT invalid_type_caught('rick');
104+
WARNING: plpy.SPIError: unrecognized error in PLy_spi_prepare
105+
CONTEXT: PL/Python function "invalid_type_caught"
106+
NOTICE: type "test" does not exist
107+
CONTEXT: PL/Python function "invalid_type_caught"
108+
invalid_type_caught
109+
---------------------
110+
111+
(1 row)
112+
113+
/* for what it's worth catch the exception generated by
114+
* the typo, and reraise it as a plain error
115+
*/
116+
CREATE FUNCTION invalid_type_reraised(a text) RETURNS text
117+
AS
118+
'if "plan" not in SD:
119+
q = "SELECT fname FROM users WHERE lname = $1"
120+
try:
121+
SD["plan"] = plpy.prepare(q, [ "test" ])
122+
except plpy.SPIError, ex:
123+
plpy.error(str(ex))
124+
rv = plpy.execute(SD["plan"], [ a ])
125+
if len(rv):
126+
return rv[0]["fname"]
127+
return None
128+
'
129+
LANGUAGE plpythonu;
130+
SELECT invalid_type_reraised('rick');
131+
WARNING: plpy.SPIError: unrecognized error in PLy_spi_prepare
132+
CONTEXT: PL/Python function "invalid_type_reraised"
133+
ERROR: plpy.Error: type "test" does not exist
134+
CONTEXT: PL/Python function "invalid_type_reraised"
135+
/* no typo no messing about
136+
*/
137+
CREATE FUNCTION valid_type(a text) RETURNS text
138+
AS
139+
'if "plan" not in SD:
140+
SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ])
141+
rv = plpy.execute(SD["plan"], [ a ])
142+
if len(rv):
143+
return rv[0]["fname"]
144+
return None
145+
'
146+
LANGUAGE plpythonu;
147+
SELECT valid_type('rick');
148+
valid_type
149+
------------
150+
151+
(1 row)
152+

‎src/pl/plpython/expected/plpython_record.out

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@ CREATE FUNCTION test_in_out_params_multi(first in text,
4747
second out text, third out text) AS $$
4848
return first + '_record_in_to_out';
4949
$$ LANGUAGE plpythonu;
50+
ERROR: PL/Python functions cannot return type record
5051
CREATE FUNCTION test_inout_params(first inout text) AS $$
5152
return first + '_inout';
5253
$$ LANGUAGE plpythonu;
@@ -299,7 +300,10 @@ SELECT * FROM test_in_out_params('test_in');
299300

300301
-- this doesn't work yet :-(
301302
SELECT * FROM test_in_out_params_multi('test_in');
302-
ERROR: PL/Python functions cannot return type record
303+
ERROR: function test_in_out_params_multi(unknown) does not exist
304+
LINE 1: SELECT * FROM test_in_out_params_multi('test_in');
305+
^
306+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
303307
SELECT * FROM test_inout_params('test_in');
304308
first
305309
---------------

‎src/pl/plpython/expected/plpython_types.out

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -571,9 +571,13 @@ PL/Python function "test_type_conversion_array_mixed2"
571571
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
572572
return [None]
573573
$$ LANGUAGE plpythonu;
574-
SELECT * FROM test_type_conversion_array_record();
575574
ERROR: PL/Python functions cannot return type type_record[]
576575
DETAIL: PL/Python does not support conversion to arrays of row types.
576+
SELECT * FROM test_type_conversion_array_record();
577+
ERROR: function test_type_conversion_array_record() does not exist
578+
LINE 1: SELECT * FROM test_type_conversion_array_record();
579+
^
580+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
577581
CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$
578582
return 'abc'
579583
$$ LANGUAGE plpythonu;

‎src/pl/plpython/expected/plpython_types_3.out

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -571,9 +571,13 @@ PL/Python function "test_type_conversion_array_mixed2"
571571
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
572572
return [None]
573573
$$ LANGUAGE plpython3u;
574-
SELECT * FROM test_type_conversion_array_record();
575574
ERROR: PL/Python functions cannot return type type_record[]
576575
DETAIL: PL/Python does not support conversion to arrays of row types.
576+
SELECT * FROM test_type_conversion_array_record();
577+
ERROR: function test_type_conversion_array_record() does not exist
578+
LINE 1: SELECT * FROM test_type_conversion_array_record();
579+
^
580+
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
577581
CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$
578582
return 'abc'
579583
$$ LANGUAGE plpython3u;

‎src/pl/plpython/plpython.c

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -251,15 +251,18 @@ typedef struct PLyResultObject
251251

252252
#if PY_MAJOR_VERSION >= 3
253253
/* Use separate names to avoid clash in pg_pltemplate */
254+
#define plpython_validator plpython3_validator
254255
#define plpython_call_handler plpython3_call_handler
255256
#define plpython_inline_handler plpython3_inline_handler
256257
#endif
257258

258259
/* exported functions */
260+
Datum plpython_validator(PG_FUNCTION_ARGS);
259261
Datum plpython_call_handler(PG_FUNCTION_ARGS);
260262
Datum plpython_inline_handler(PG_FUNCTION_ARGS);
261263
void _PG_init(void);
262264

265+
PG_FUNCTION_INFO_V1(plpython_validator);
263266
PG_FUNCTION_INFO_V1(plpython_call_handler);
264267
PG_FUNCTION_INFO_V1(plpython_inline_handler);
265268

@@ -437,6 +440,42 @@ plpython_return_error_callback(void *arg)
437440
errcontext("while creating return value");
438441
}
439442

443+
static bool
444+
PLy_procedure_is_trigger(Form_pg_proc procStruct)
445+
{
446+
return (procStruct->prorettype == TRIGGEROID ||
447+
(procStruct->prorettype == OPAQUEOID &&
448+
procStruct->pronargs == 0));
449+
}
450+
451+
Datum
452+
plpython_validator(PG_FUNCTION_ARGS)
453+
{
454+
Oid funcoid = PG_GETARG_OID(0);
455+
HeapTuple tuple;
456+
Form_pg_proc procStruct;
457+
bool is_trigger;
458+
459+
if (!check_function_bodies)
460+
{
461+
PG_RETURN_VOID();
462+
}
463+
464+
/* Get the new function's pg_proc entry */
465+
tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcoid));
466+
if (!HeapTupleIsValid(tuple))
467+
elog(ERROR, "cache lookup failed for function %u", funcoid);
468+
procStruct = (Form_pg_proc) GETSTRUCT(tuple);
469+
470+
is_trigger = PLy_procedure_is_trigger(procStruct);
471+
472+
ReleaseSysCache(tuple);
473+
474+
PLy_procedure_get(funcoid, is_trigger);
475+
476+
PG_RETURN_VOID();
477+
}
478+
440479
Datum
441480
plpython_call_handler(PG_FUNCTION_ARGS)
442481
{

‎src/pl/plpython/sql/plpython_error.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,29 @@
22
-- the trigger handler once. the errors and subsequent core dump were
33
-- interesting.
44

5+
/* Flat out Python syntax error
6+
*/
7+
CREATE FUNCTION python_syntax_error() RETURNS text
8+
AS
9+
'.syntaxerror'
10+
LANGUAGE plpythonu;
11+
12+
/* With check_function_bodies = false the function should get defined
13+
* and the error reported when called
14+
*/
15+
SET check_function_bodies = false;
16+
17+
CREATE FUNCTION python_syntax_error() RETURNS text
18+
AS
19+
'.syntaxerror'
20+
LANGUAGE plpythonu;
21+
22+
SELECT python_syntax_error();
23+
/* Run the function twice to check if the hashtable entry gets cleaned up */
24+
SELECT python_syntax_error();
25+
26+
RESET check_function_bodies;
27+
528
/* Flat out syntax error
629
*/
730
CREATE FUNCTION sql_syntax_error() RETURNS text

0 commit comments

Comments
 (0)