Re: Bug in plpython's Python Generators

Hi there,

I can't make Python Generators to work reliably. According to the
documentation, this should work:

CREATE OR REPLACE FUNCTION foobar()
RETURNS SETOF text AS
$$
for s in ('Hello', 'World'):
plpy.execute('select 1')
yield s
$$
LANGUAGE 'plpythonu';

I get this error when calling the function:

test=# select foobar();
ERROR: error fetching next item from iterator
CONTEXT: PL/Python function "foobar"

When I remove the dummy plpy.execute() call, it works:

CREATE OR REPLACE FUNCTION foobar()
RETURNS SETOF text AS
$$
for s in ('Hello', 'World'):
yield s
$$
LANGUAGE 'plpythonu';

test=# select foobar();
foobar
--------
Hello
World
(2 rows)

Seems like calls to plpy.execute() conflict with generators. This is
the case both on versions 8.4.4 and 9.0.1.

All the best,
--
Jean-Baptiste Quenot

Excerpts from Jean-Baptiste Quenot's message of jue oct 21 09:20:16 -0300 2010:

> I get this error when calling the function:
>
> test=# select foobar();
> ERROR: error fetching next item from iterator

I can reproduce this here. The first bug to solve is, I think, getting
a more meaningful error report.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Excerpts from Alvaro Herrera's message of jue oct 21 15:32:53 -0300 2010:
> Excerpts from Jean-Baptiste Quenot's message of jue oct 21 09:20:16 -0300 2010:
>
> > I get this error when calling the function:
> >
> > test=# select foobar();
> > ERROR: error fetching next item from iterator
>
> I can reproduce this here. The first bug to solve is, I think, getting
> a more meaningful error report.

Something like this. Somebody that really knows their way around Python
has to clean this up.

alvherre=# select * from foobar();
ERROR: error extrayendo el próximo elemento del iterador
CONTEXTO: falló SPI_execute: SPI_ERROR_UNCONNECTED
función PL/Python «foobar»

I think all error cases in plpython need some improvement so that they
show the error message from Python. Right now they are ignored.

... and presumably somebody can fix the real bug that Jean-Baptiste hit,
too.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 21/10/10 20:48, Alvaro Herrera wrote:
> Excerpts from Alvaro Herrera's message of jue oct 21 15:32:53 -0300 2010:
>> Excerpts from Jean-Baptiste Quenot's message of jue oct 21 09:20:16 -0300 2010:
>>
>>> I get this error when calling the function:
>>>
>>> test=# select foobar();
>>> ERROR: error fetching next item from iterator

> I think all error cases in plpython need some improvement so that they
> show the error message from Python. Right now they are ignored.
>
> ... and presumably somebody can fix the real bug that Jean-Baptiste hit,
> too.

AFAICS the error comes from PLy_function_handler disconnecting from SPI
after calling into the Python code and then going ahead and reading the
result from the iterator. The problem is that after calling PyIter_Next
to resume the iterator and fetch the next value, it tries to do
plpy.execute("select 1"), which fails because SPI is already
disconnected. This probably means that a SRF should not disconnect from
SPI until it has finished reading the result.

The error handling in plpython is well-known to be a mess, hence the
confusing error message that OP got. Another annoying thing is that SPI
calls are not done in a subtransaction, which means you can't trap
errors with a try/catch Python construct. I'm currently trying to get
try/catch to work and might end up cleaning up error handling as well...
but this can take me some time, so maybe someone should plug this
particular hole right away.

Cheers,
Jan

On 24/10/10 00:32, Jan Urbański wrote:
> On 21/10/10 20:48, Alvaro Herrera wrote:
>> ... and presumably somebody can fix the real bug that Jean-Baptiste hit,
>> too.
>
> AFAICS the error comes from PLy_function_handler disconnecting from SPI
> after calling into the Python code and then going ahead and reading the
> result from the iterator.

Here's a patch with a fix for that bug.

Cheers,
Jan

On Sun, Oct 24, 2010 at 01:32, Jan Urbański <wulczer(at)wulczer(dot)org> wrote:
> The error handling in plpython is well-known to be a mess, hence the
> confusing error message that OP got. Another annoying thing is that SPI
> calls are not done in a subtransaction, which means you can't trap
> errors with a try/catch Python construct. I'm currently trying to get
> try/catch to work and might end up cleaning up error handling as well...
> but this can take me some time, so maybe someone should plug this
> particular hole right away.

While on this topic, maybe you can chime in on the pgsql-docs list for
my documentation patches to document the current (broken) behavior?

Regards,
Marti

Thanks, Jan.

I tested the patch and it looks fine.

Oleg
On Sun, 14 Nov 2010, Jan Urbaski wrote:

> On 24/10/10 00:32, Jan UrbaЪЪski wrote:
>> On 21/10/10 20:48, Alvaro Herrera wrote:
>>> ... and presumably somebody can fix the real bug that Jean-Baptiste hit,
>>> too.
>>
>> AFAICS the error comes from PLy_function_handler disconnecting from SPI
>> after calling into the Python code and then going ahead and reading the
>> result from the iterator.
>
> Here's a patch with a fix for that bug.
>
> Cheers,
> Jan
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= <wulczer(at)wulczer(dot)org> writes:
> On 24/10/10 00:32, Jan Urbaski wrote:
>> On 21/10/10 20:48, Alvaro Herrera wrote:
>>> ... and presumably somebody can fix the real bug that Jean-Baptiste hit,
>>> too.

>> AFAICS the error comes from PLy_function_handler disconnecting from SPI
>> after calling into the Python code and then going ahead and reading the
>> result from the iterator.

> Here's a patch with a fix for that bug.

Applied back to 8.2. Thanks for the patch.

regards, tom lane