Skip to content

Commit 4fa520f

Browse files
peterepetere
committed
PL/Python: Accept strings in functions returning composite types
Before 9.1, PL/Python functions returning composite types could return a string and it would be parsed using record_in. The 9.1 changes made PL/Python only expect dictionaries, tuples, or objects supporting getattr as output of composite functions, resulting in a regression and a confusing error message, as the strings were interpreted as sequences and the code for transforming lists to database tuples was used. Fix this by treating strings separately as before, before checking for the other types. The reason why it's important to support string to database tuple conversion is that trigger functions on tables with composite columns get the composite row passed in as a string (from record_out). Without supporting converting this back using record_in, this makes it impossible to implement pass-through behavior for these columns, as PL/Python no longer accepts strings for composite values. A better solution would be to fix the code that transforms composite inputs into Python objects to produce dictionaries that would then be correctly interpreted by the Python->PostgreSQL counterpart code. But that would be too invasive to backpatch to 9.1, and it is too late in the 9.2 cycle to attempt it. It should be revisited in the future, though. Reported as bug #6559 by Kirill Simonov. Jan Urbański
1 parent dc7521d commit 4fa520f

File tree

5 files changed

+149
-44
lines changed

5 files changed

+149
-44
lines changed

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,8 @@ elif typ == 'obj':
3838
type_record.first = first
3939
type_record.second = second
4040
return type_record
41+
elif typ == 'str':
42+
return "('%s',%r)" % (first, second)
4143
$$ LANGUAGE plpythonu;
4244
CREATE FUNCTION test_in_out_params(first in text, second out text) AS $$
4345
return first + '_in_to_out';
@@ -290,6 +292,12 @@ SELECT * FROM test_type_record_as('obj', null, null, true);
290292
|
291293
(1 row)
292294

295+
SELECT * FROM test_type_record_as('str', 'one', 1, false);
296+
first | second
297+
-------+--------
298+
'one' | 1
299+
(1 row)
300+
293301
SELECT * FROM test_in_out_params('test_in');
294302
second
295303
-------------------
@@ -355,3 +363,11 @@ ERROR: attribute "second" does not exist in Python object
355363
HINT: To return null in a column, let the returned object have an attribute named after column with value None.
356364
CONTEXT: while creating return value
357365
PL/Python function "test_type_record_error3"
366+
CREATE FUNCTION test_type_record_error4() RETURNS type_record AS $$
367+
return 'foo'
368+
$$ LANGUAGE plpythonu;
369+
SELECT * FROM test_type_record_error4();
370+
ERROR: malformed record literal: "foo"
371+
DETAIL: Missing left parenthesis.
372+
CONTEXT: while creating return value
373+
PL/Python function "test_type_record_error4"

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

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -567,3 +567,40 @@ SELECT * FROM composite_trigger_test;
567567
(3,f) | (7,t)
568568
(1 row)
569569

570+
-- triggers with composite type columns (bug #6559)
571+
CREATE TABLE composite_trigger_noop_test (f1 comp1, f2 comp2);
572+
CREATE FUNCTION composite_trigger_noop_f() RETURNS trigger AS $$
573+
return 'MODIFY'
574+
$$ LANGUAGE plpythonu;
575+
CREATE TRIGGER composite_trigger_noop BEFORE INSERT ON composite_trigger_noop_test
576+
FOR EACH ROW EXECUTE PROCEDURE composite_trigger_noop_f();
577+
INSERT INTO composite_trigger_noop_test VALUES (NULL, NULL);
578+
INSERT INTO composite_trigger_noop_test VALUES (ROW(1, 'f'), NULL);
579+
INSERT INTO composite_trigger_noop_test VALUES (ROW(NULL, 't'), ROW(1, 'f'));
580+
SELECT * FROM composite_trigger_noop_test;
581+
f1 | f2
582+
-------+-------
583+
|
584+
(1,f) |
585+
(,t) | (1,f)
586+
(3 rows)
587+
588+
-- nested composite types
589+
CREATE TYPE comp3 AS (c1 comp1, c2 comp2, m integer);
590+
CREATE TABLE composite_trigger_nested_test(c comp3);
591+
CREATE FUNCTION composite_trigger_nested_f() RETURNS trigger AS $$
592+
return 'MODIFY'
593+
$$ LANGUAGE plpythonu;
594+
CREATE TRIGGER composite_trigger_nested BEFORE INSERT ON composite_trigger_nested_test
595+
FOR EACH ROW EXECUTE PROCEDURE composite_trigger_nested_f();
596+
INSERT INTO composite_trigger_nested_test VALUES (NULL);
597+
INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(1, 'f'), NULL, 3));
598+
INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(NULL, 't'), ROW(1, 'f'), NULL));
599+
SELECT * FROM composite_trigger_nested_test;
600+
c
601+
-------------------
602+
603+
("(1,f)",,3)
604+
("(,t)","(1,f)",)
605+
(3 rows)
606+

‎src/pl/plpython/plpython.c

Lines changed: 50 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -410,10 +410,11 @@ static Datum PLyObject_ToComposite(PLyObToDatum *, int32, PyObject *);
410410
static Datum PLyObject_ToDatum(PLyObToDatum *, int32, PyObject *);
411411
static Datum PLySequence_ToArray(PLyObToDatum *, int32, PyObject *);
412412

413-
static HeapTuple PLyObject_ToTuple(PLyTypeInfo *, TupleDesc, PyObject *);
414-
static HeapTuple PLyMapping_ToTuple(PLyTypeInfo *, TupleDesc, PyObject *);
415-
static HeapTuple PLySequence_ToTuple(PLyTypeInfo *, TupleDesc, PyObject *);
416-
static HeapTuple PLyGenericObject_ToTuple(PLyTypeInfo *, TupleDesc, PyObject *);
413+
static Datum PLyObject_ToCompositeDatum(PLyTypeInfo *, TupleDesc, PyObject *);
414+
static Datum PLyString_ToComposite(PLyTypeInfo *, TupleDesc, PyObject *);
415+
static Datum PLyMapping_ToComposite(PLyTypeInfo *, TupleDesc, PyObject *);
416+
static Datum PLySequence_ToComposite(PLyTypeInfo *, TupleDesc, PyObject *);
417+
static Datum PLyGenericObject_ToComposite(PLyTypeInfo *, TupleDesc, PyObject *);
417418

418419
/*
419420
* Currently active plpython function
@@ -1213,7 +1214,6 @@ PLy_function_handler(FunctionCallInfo fcinfo, PLyProcedure *proc)
12131214
else if (proc->result.is_rowtype >= 1)
12141215
{
12151216
TupleDesc desc;
1216-
HeapTuple tuple = NULL;
12171217

12181218
/* make sure it's not an unnamed record */
12191219
Assert((proc->result.out.d.typoid == RECORDOID &&
@@ -1224,18 +1224,8 @@ PLy_function_handler(FunctionCallInfo fcinfo, PLyProcedure *proc)
12241224
desc = lookup_rowtype_tupdesc(proc->result.out.d.typoid,
12251225
proc->result.out.d.typmod);
12261226

1227-
tuple = PLyObject_ToTuple(&proc->result, desc, plrv);
1228-
1229-
if (tuple != NULL)
1230-
{
1231-
fcinfo->isnull = false;
1232-
rv = HeapTupleGetDatum(tuple);
1233-
}
1234-
else
1235-
{
1236-
fcinfo->isnull = true;
1237-
rv = (Datum) NULL;
1238-
}
1227+
rv = PLyObject_ToCompositeDatum(&proc->result, desc, plrv);
1228+
fcinfo->isnull = (rv == (Datum) NULL);
12391229
}
12401230
else
12411231
{
@@ -2419,26 +2409,28 @@ PLyDict_FromTuple(PLyTypeInfo *info, HeapTuple tuple, TupleDesc desc)
24192409
}
24202410

24212411
/*
2422-
* Convert a Python object to a PostgreSQL tuple, using all supported
2423-
* conversion methods: tuple as a sequence, as a mapping or as an object that
2424-
* has __getattr__ support.
2412+
* Convert a Python object to a composite Datum, using all supported
2413+
* conversion methods: composite as a string, as a sequence, as a mapping or
2414+
* as an object that has __getattr__ support.
24252415
*/
2426-
static HeapTuple
2427-
PLyObject_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *plrv)
2416+
static Datum
2417+
PLyObject_ToCompositeDatum(PLyTypeInfo *info, TupleDesc desc, PyObject *plrv)
24282418
{
2429-
HeapTuple tuple;
2419+
Datum datum;
24302420

2431-
if (PySequence_Check(plrv))
2421+
if (PyString_Check(plrv) || PyUnicode_Check(plrv))
2422+
datum = PLyString_ToComposite(info, desc, plrv);
2423+
else if (PySequence_Check(plrv))
24322424
/* composite type as sequence (tuple, list etc) */
2433-
tuple = PLySequence_ToTuple(info, desc, plrv);
2425+
datum = PLySequence_ToComposite(info, desc, plrv);
24342426
else if (PyMapping_Check(plrv))
24352427
/* composite type as mapping (currently only dict) */
2436-
tuple = PLyMapping_ToTuple(info, desc, plrv);
2428+
datum = PLyMapping_ToComposite(info, desc, plrv);
24372429
else
24382430
/* returned as smth, must provide method __getattr__(name) */
2439-
tuple = PLyGenericObject_ToTuple(info, desc, plrv);
2431+
datum = PLyGenericObject_ToComposite(info, desc, plrv);
24402432

2441-
return tuple;
2433+
return datum;
24422434
}
24432435

24442436
/*
@@ -2513,7 +2505,6 @@ PLyObject_ToBytea(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
25132505
static Datum
25142506
PLyObject_ToComposite(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
25152507
{
2516-
HeapTuple tuple = NULL;
25172508
Datum rv;
25182509
PLyTypeInfo info;
25192510
TupleDesc desc;
@@ -2535,15 +2526,10 @@ PLyObject_ToComposite(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
25352526
* that info instead of looking it up every time a tuple is returned from
25362527
* the function.
25372528
*/
2538-
tuple = PLyObject_ToTuple(&info, desc, plrv);
2529+
rv = PLyObject_ToCompositeDatum(&info, desc, plrv);
25392530

25402531
PLy_typeinfo_dealloc(&info);
25412532

2542-
if (tuple != NULL)
2543-
rv = HeapTupleGetDatum(tuple);
2544-
else
2545-
rv = (Datum) NULL;
2546-
25472533
return rv;
25482534
}
25492535

@@ -2650,8 +2636,28 @@ PLySequence_ToArray(PLyObToDatum *arg, int32 typmod, PyObject *plrv)
26502636
return PointerGetDatum(array);
26512637
}
26522638

2653-
static HeapTuple
2654-
PLyMapping_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping)
2639+
2640+
static Datum
2641+
PLyString_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *string)
2642+
{
2643+
HeapTuple typeTup;
2644+
2645+
typeTup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(desc->tdtypeid));
2646+
if (!HeapTupleIsValid(typeTup))
2647+
elog(ERROR, "cache lookup failed for type %u", desc->tdtypeid);
2648+
2649+
PLy_output_datum_func2(&info->out.d, typeTup);
2650+
2651+
ReleaseSysCache(typeTup);
2652+
ReleaseTupleDesc(desc);
2653+
2654+
return PLyObject_ToDatum(&info->out.d, info->out.d.typmod, string);
2655+
}
2656+
2657+
2658+
2659+
static Datum
2660+
PLyMapping_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping)
26552661
{
26562662
HeapTuple tuple;
26572663
Datum *values;
@@ -2719,12 +2725,12 @@ PLyMapping_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *mapping)
27192725
pfree(values);
27202726
pfree(nulls);
27212727

2722-
return tuple;
2728+
return HeapTupleGetDatum(tuple);
27232729
}
27242730

27252731

2726-
static HeapTuple
2727-
PLySequence_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence)
2732+
static Datum
2733+
PLySequence_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence)
27282734
{
27292735
HeapTuple tuple;
27302736
Datum *values;
@@ -2805,12 +2811,12 @@ PLySequence_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *sequence)
28052811
pfree(values);
28062812
pfree(nulls);
28072813

2808-
return tuple;
2814+
return HeapTupleGetDatum(tuple);
28092815
}
28102816

28112817

2812-
static HeapTuple
2813-
PLyGenericObject_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *object)
2818+
static Datum
2819+
PLyGenericObject_ToComposite(PLyTypeInfo *info, TupleDesc desc, PyObject *object)
28142820
{
28152821
HeapTuple tuple;
28162822
Datum *values;
@@ -2877,7 +2883,7 @@ PLyGenericObject_ToTuple(PLyTypeInfo *info, TupleDesc desc, PyObject *object)
28772883
pfree(values);
28782884
pfree(nulls);
28792885

2880-
return tuple;
2886+
return HeapTupleGetDatum(tuple);
28812887
}
28822888

28832889

‎src/pl/plpython/sql/plpython_record.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,8 @@ elif typ == 'obj':
4343
type_record.first = first
4444
type_record.second = second
4545
return type_record
46+
elif typ == 'str':
47+
return "('%s',%r)" % (first, second)
4648
$$ LANGUAGE plpythonu;
4749

4850
CREATE FUNCTION test_in_out_params(first in text, second out text) AS $$
@@ -108,6 +110,8 @@ SELECT * FROM test_type_record_as('obj', null, 2, false);
108110
SELECT * FROM test_type_record_as('obj', 'three', 3, false);
109111
SELECT * FROM test_type_record_as('obj', null, null, true);
110112

113+
SELECT * FROM test_type_record_as('str', 'one', 1, false);
114+
111115
SELECT * FROM test_in_out_params('test_in');
112116
SELECT * FROM test_in_out_params_multi('test_in');
113117
SELECT * FROM test_inout_params('test_in');
@@ -151,3 +155,9 @@ CREATE FUNCTION test_type_record_error3() RETURNS type_record AS $$
151155
$$ LANGUAGE plpythonu;
152156

153157
SELECT * FROM test_type_record_error3();
158+
159+
CREATE FUNCTION test_type_record_error4() RETURNS type_record AS $$
160+
return 'foo'
161+
$$ LANGUAGE plpythonu;
162+
163+
SELECT * FROM test_type_record_error4();

‎src/pl/plpython/sql/plpython_trigger.sql

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -346,3 +346,39 @@ CREATE TRIGGER composite_trigger BEFORE INSERT ON composite_trigger_test
346346

347347
INSERT INTO composite_trigger_test VALUES (NULL, NULL);
348348
SELECT * FROM composite_trigger_test;
349+
350+
351+
-- triggers with composite type columns (bug #6559)
352+
353+
CREATE TABLE composite_trigger_noop_test (f1 comp1, f2 comp2);
354+
355+
CREATE FUNCTION composite_trigger_noop_f() RETURNS trigger AS $$
356+
return 'MODIFY'
357+
$$ LANGUAGE plpythonu;
358+
359+
CREATE TRIGGER composite_trigger_noop BEFORE INSERT ON composite_trigger_noop_test
360+
FOR EACH ROW EXECUTE PROCEDURE composite_trigger_noop_f();
361+
362+
INSERT INTO composite_trigger_noop_test VALUES (NULL, NULL);
363+
INSERT INTO composite_trigger_noop_test VALUES (ROW(1, 'f'), NULL);
364+
INSERT INTO composite_trigger_noop_test VALUES (ROW(NULL, 't'), ROW(1, 'f'));
365+
SELECT * FROM composite_trigger_noop_test;
366+
367+
368+
-- nested composite types
369+
370+
CREATE TYPE comp3 AS (c1 comp1, c2 comp2, m integer);
371+
372+
CREATE TABLE composite_trigger_nested_test(c comp3);
373+
374+
CREATE FUNCTION composite_trigger_nested_f() RETURNS trigger AS $$
375+
return 'MODIFY'
376+
$$ LANGUAGE plpythonu;
377+
378+
CREATE TRIGGER composite_trigger_nested BEFORE INSERT ON composite_trigger_nested_test
379+
FOR EACH ROW EXECUTE PROCEDURE composite_trigger_nested_f();
380+
381+
INSERT INTO composite_trigger_nested_test VALUES (NULL);
382+
INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(1, 'f'), NULL, 3));
383+
INSERT INTO composite_trigger_nested_test VALUES (ROW(ROW(NULL, 't'), ROW(1, 'f'), NULL));
384+
SELECT * FROM composite_trigger_nested_test;

0 commit comments

Comments
 (0)