Re: [COMMITTERS] pgsql: plpython: Add SPI cursor support

plpython: Add SPI cursor support

Add a function plpy.cursor that is similar to plpy.execute but uses an
SPI cursor to avoid fetching the entire result set into memory.

Jan Urbański, reviewed by Steve Singer

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/89e850e6fda9e4e441712012abe971fe938d595a

Modified Files
--------------
doc/src/sgml/plpython.sgml | 81 +++
src/pl/plpython/expected/plpython_spi.out | 151 +++++
.../plpython/expected/plpython_subtransaction.out | 66 ++
.../expected/plpython_subtransaction_0.out | 70 +++
.../expected/plpython_subtransaction_5.out | 70 +++
src/pl/plpython/expected/plpython_test.out | 6 +-
src/pl/plpython/plpython.c | 642 ++++++++++++++++++++
src/pl/plpython/sql/plpython_spi.sql | 116 ++++
src/pl/plpython/sql/plpython_subtransaction.sql | 52 ++
9 files changed, 1251 insertions(+), 3 deletions(-)

Peter Eisentraut wrote:
> plpython: Add SPI cursor support
>
> Add a function plpy.cursor that is similar to plpy.execute but uses an
> SPI cursor to avoid fetching the entire result set into memory.
>
> Jan Urba?ski, reviewed by Steve Singer

I assume this is _not_ related to this TODO item:

Add a DB-API compliant interface on top of the SPI interface

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> plpython: Add SPI cursor support

Buildfarm member narwhal does not like this patch. It looks like
"PyObject_SelfIter" is not a compile-time constant on its version
of python (2.5, apparently).

regards, tom lane

On 06/12/11 19:23, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> plpython: Add SPI cursor support
>
> Buildfarm member narwhal does not like this patch. It looks like
> "PyObject_SelfIter" is not a compile-time constant on its version
> of python (2.5, apparently).

Hm, I quickly tried with a self-compiled Python 2.5.6 from the upstream
tarball and it compiled on my Linux machine.

I see that PyObject_SelfIter is defined here:

http://hg.python.org/cpython/file/b48e1b48e670/Include/object.h#l407

I'll try to dig around to see what can be causing that...

Cheers,
Jan

On 06/12/11 19:33, Jan Urbański wrote:
> On 06/12/11 19:23, Tom Lane wrote:
>> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>>> plpython: Add SPI cursor support
>>
>> Buildfarm member narwhal does not like this patch. It looks like
>> "PyObject_SelfIter" is not a compile-time constant on its version
>> of python (2.5, apparently).
>
> I'll try to dig around to see what can be causing that...

Seems that PyObject_GenericGetIter has been renamed to PyObject_SelfIter
at some point:

http://hg.python.org/cpython/rev/fd5ef7003469

I'm trying to muster whatever mercurial-foo I have to check if there's
been a 2.5 version tagged without that patch...

Jan

On 07/12/11 11:16, Jan Urbański wrote:
> On 06/12/11 19:33, Jan Urbański wrote:
>> On 06/12/11 19:23, Tom Lane wrote:
>>> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>>>> plpython: Add SPI cursor support
>>>
>>> Buildfarm member narwhal does not like this patch. It looks like
>>> "PyObject_SelfIter" is not a compile-time constant on its version
>>> of python (2.5, apparently).
>>
>> I'll try to dig around to see what can be causing that...
>
> Seems that PyObject_GenericGetIter has been renamed to PyObject_SelfIter
> at some point:
>
> http://hg.python.org/cpython/rev/fd5ef7003469
>
> I'm trying to muster whatever mercurial-foo I have to check if there's
> been a 2.5 version tagged without that patch...

So no, the renaming happened on Mar 17 2003 and 2.5 was tagged on
Sep 18 2006. The source tarball for 2.5 contains PyObject_SelfIter.

Both fennec and mastodon build OK with Python 2.5. Is it possible that
narwhal's Python installation is in some way broken?

On 12/05/2011 12:56 PM, Peter Eisentraut wrote:
> plpython: Add SPI cursor support
>
> Add a function plpy.cursor that is similar to plpy.execute but uses an
> SPI cursor to avoid fetching the entire result set into memory.
>
> Jan Urbański, reviewed by Steve Singer
>
>

This commit apparently caused a problem on buildfarm narwhal, which has
been failing for 8 days without anyone paying attention. See
<http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=narwhal&dt=2011-12-14%2017%3A00%3A02>:

plpython.c:3163: error: initializer element is not constant
plpython.c:3163: error: (near initialization for `PLy_CursorType.tp_iter')
make[3]: *** [plpython.o] Error 1

Can someone please investigate?

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 12/05/2011 12:56 PM, Peter Eisentraut wrote:
>> plpython: Add SPI cursor support

> This commit apparently caused a problem on buildfarm narwhal, which has
> been failing for 8 days without anyone paying attention.

Um, yes, some of us noticed:
http://archives.postgresql.org/pgsql-hackers/2011-12/msg00264.php

Apparently, nobody has any idea what's wrong or how to fix it, because
it works fine with other copies of python 2.5. What we need to know is
what's different about narwhal's version. Dave, any ideas?

regards, tom lane

On 12/14/2011 06:43 PM, Jan Urbański wrote:
> ----- Original message -----
>> On 12/05/2011 12:56 PM, Peter Eisentraut wrote:
>>> plpython: Add SPI cursor support
>>>
>>> Add a function plpy.cursor that is similar to plpy.execute but uses an
>>> SPI cursor to avoid fetching the entire result set into memory.
>>>
>>> Jan Urbański, reviewed by Steve Singer
>>>
>>>
>> This commit apparently caused a problem on buildfarm narwhal, which has
>> been failing for 8 days without anyone paying attention. See
>> <http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=narwhal&dt=2011-12-14%2017%3A00%3A02>:
> I did some investigation after this has been committed and couldn't come up with a reasonable explanation (not at my PC at the moment, can't find the relevant thread).
>
> Can we check if PyObject_SelfIter is defined in Include/object.h in the Python install dir on that machine? If yes, then this looks like a MinGW quirk, which I'll do my best to investigate, but I don't have easy access to a Windows machine with a mingw toolchain...

Dave,

this is your machine. It's been failing for 34 days now. Could you
please follow up?

cheers

andrew

On Mon, Jan 9, 2012 at 3:35 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
>
> On 12/14/2011 06:43 PM, Jan Urbański wrote:
>>
>> ----- Original message -----
>>>
>>> On 12/05/2011 12:56 PM, Peter Eisentraut wrote:
>>>>
>>>> plpython: Add SPI cursor support
>>>>
>>>> Add a function plpy.cursor that is similar to plpy.execute but uses an
>>>> SPI cursor to avoid fetching the entire result set into memory.
>>>>
>>>> Jan Urbański, reviewed by Steve Singer
>>>>
>>>>
>>> This commit apparently caused a problem on buildfarm narwhal, which has
>>> been failing for 8 days without anyone paying attention. See
>>> <http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=narwhal&dt=2011-12-14%2017%3A00%3A02>:
>>
>> I did some investigation after this has been committed and couldn't come up with a reasonable explanation (not at my PC at the moment, can't find the relevant thread).
>>
>> Can we check if PyObject_SelfIter is defined in Include/object.h in the Python install dir on that machine? If yes, then this looks like a MinGW quirk, which I'll do my best to investigate, but I don't have easy access to a Windows machine with a mingw toolchain...
>
>
> Dave,
>
> this is your machine. It's been failing for 34 days now. Could you please follow up?

Yes, it's defined as such:

PyAPI_FUNC(PyObject *) PyObject_SelfIter(PyObject *);

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/09/2012 10:52 AM, Dave Page wrote:
> On Mon, Jan 9, 2012 at 3:35 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>>
>>
>> On 12/14/2011 06:43 PM, Jan Urbański wrote:
>>> ----- Original message -----
>>>> On 12/05/2011 12:56 PM, Peter Eisentraut wrote:
>>>>> plpython: Add SPI cursor support
>>>>>
>>>>> Add a function plpy.cursor that is similar to plpy.execute but uses an
>>>>> SPI cursor to avoid fetching the entire result set into memory.
>>>>>
>>>>> Jan Urbański, reviewed by Steve Singer
>>>>>
>>>>>
>>>> This commit apparently caused a problem on buildfarm narwhal, which has
>>>> been failing for 8 days without anyone paying attention. See
>>>> <http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=narwhal&dt=2011-12-14%2017%3A00%3A02>:
>>> I did some investigation after this has been committed and couldn't come up with a reasonable explanation (not at my PC at the moment, can't find the relevant thread).
>>>
>>> Can we check if PyObject_SelfIter is defined in Include/object.h in the Python install dir on that machine? If yes, then this looks like a MinGW quirk, which I'll do my best to investigate, but I don't have easy access to a Windows machine with a mingw toolchain...
>>
>> Dave,
>>
>> this is your machine. It's been failing for 34 days now. Could you please follow up?
> Yes, it's defined as such:
>
> PyAPI_FUNC(PyObject *) PyObject_SelfIter(PyObject *);
>
>
>

Hmm. Well, at this stage I'm slightly inclined to say it's a compiler
problem. That's a pretty old compiler. If you can't upgrade it maybe you
should just take --with-python out of the buildfarm settings.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 01/09/2012 10:52 AM, Dave Page wrote:
>> Yes, it's defined as such:
>> PyAPI_FUNC(PyObject *) PyObject_SelfIter(PyObject *);

> Hmm. Well, at this stage I'm slightly inclined to say it's a compiler
> problem. That's a pretty old compiler. If you can't upgrade it maybe you
> should just take --with-python out of the buildfarm settings.

I'm wondering what PyAPI_FUNC() expands to on Dave's machine. In my
copy of pyport.h, there's a rat's nest of cygwin-related tests that may
or may not lead to including "extern" and/or "__declspec" in that macro.
I think possibly the wrong combination might lead to failures of this
ilk?

regards, tom lane

On Mon, Jan 9, 2012 at 4:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> On 01/09/2012 10:52 AM, Dave Page wrote:
>>> Yes, it's defined as such:
>>> PyAPI_FUNC(PyObject *) PyObject_SelfIter(PyObject *);
>
>> Hmm. Well, at this stage I'm slightly inclined to say it's a compiler
>> problem. That's a pretty old compiler. If you can't upgrade it maybe you
>> should just take --with-python out of the buildfarm settings.
>
> I'm wondering what PyAPI_FUNC() expands to on Dave's machine.  In my
> copy of pyport.h, there's a rat's nest of cygwin-related tests that may
> or may not lead to including "extern" and/or "__declspec" in that macro.
> I think possibly the wrong combination might lead to failures of this
> ilk?

Is there a way I can get gcc to spit out the expanded definition in a
readable format that you know of?

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 9 January 2012 17:06, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> Is there a way I can get gcc to spit out the expanded definition in a
> readable format that you know of?

Yes. Figure out what flags gcc is given when building the TU. Then,
add the -E flag and see what is generated:

http://www.network-theory.co.uk/docs/gccintro/gccintro_36.html

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On 01/09/2012 12:13 PM, Peter Geoghegan wrote:
> On 9 January 2012 17:06, Dave Page<dpage(at)pgadmin(dot)org> wrote:
>> Is there a way I can get gcc to spit out the expanded definition in a
>> readable format that you know of?
> Yes. Figure out what flags gcc is given when building the TU. Then,
> add the -E flag and see what is generated:
>
> http://www.network-theory.co.uk/docs/gccintro/gccintro_36.html

Yeah, something like

gcc -E -I/usr/include/python2.7/ -include Python.h
/usr/include/python2.7/object.h

adjusted for your paths should do it.

cheers

andrew

On mån, 2012-01-09 at 17:06 +0000, Dave Page wrote:
> Is there a way I can get gcc to spit out the expanded definition in a
> readable format that you know of?

make plpy_cursorobject.i

On Mon, Jan 9, 2012 at 5:32 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> On 01/09/2012 12:13 PM, Peter Geoghegan wrote:
>>
>> On 9 January 2012 17:06, Dave Page<dpage(at)pgadmin(dot)org>  wrote:
>>>
>>> Is there a way I can get gcc to spit out the expanded definition in a
>>> readable format that you know of?
>>
>> Yes. Figure out what flags gcc is given when building the TU. Then,
>> add the -E flag and see what is generated:
>>
>> http://www.network-theory.co.uk/docs/gccintro/gccintro_36.html
>
>
>
> Yeah, something like
>
>    gcc -E -I/usr/include/python2.7/ -include Python.h
> /usr/include/python2.7/object.h
>
> adjusted for your paths should do it.

Thanks - pfa.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 01/10/2012 04:56 AM, Dave Page wrote:
> On Mon, Jan 9, 2012 at 5:32 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>>
>> On 01/09/2012 12:13 PM, Peter Geoghegan wrote:
>>> On 9 January 2012 17:06, Dave Page<dpage(at)pgadmin(dot)org> wrote:
>>>> Is there a way I can get gcc to spit out the expanded definition in a
>>>> readable format that you know of?
>>> Yes. Figure out what flags gcc is given when building the TU. Then,
>>> add the -E flag and see what is generated:
>>>
>>> http://www.network-theory.co.uk/docs/gccintro/gccintro_36.html
>>
>>
>> Yeah, something like
>>
>> gcc -E -I/usr/include/python2.7/ -include Python.h
>> /usr/include/python2.7/object.h
>>
>> adjusted for your paths should do it.
> Thanks - pfa.

which contains:

__attribute__((dllimport)) PyObject * PyObject_SelfIter(PyObject *);

The result is identical on frogmouth, which builds happily using gcc
4.5.0. So this does look like a compiler quirk.

cheers

andrew

On Tue, Jan 10, 2012 at 2:28 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> On 01/10/2012 04:56 AM, Dave Page wrote:
>>
>> On Mon, Jan 9, 2012 at 5:32 PM, Andrew Dunstan<andrew(at)dunslane(dot)net>
>>  wrote:
>>>
>>>
>>> On 01/09/2012 12:13 PM, Peter Geoghegan wrote:
>>>>
>>>> On 9 January 2012 17:06, Dave Page<dpage(at)pgadmin(dot)org>    wrote:
>>>>>
>>>>> Is there a way I can get gcc to spit out the expanded definition in a
>>>>> readable format that you know of?
>>>>
>>>> Yes. Figure out what flags gcc is given when building the TU. Then,
>>>> add the -E flag and see what is generated:
>>>>
>>>> http://www.network-theory.co.uk/docs/gccintro/gccintro_36.html
>>>
>>>
>>>
>>> Yeah, something like
>>>
>>>    gcc -E -I/usr/include/python2.7/ -include Python.h
>>> /usr/include/python2.7/object.h
>>>
>>> adjusted for your paths should do it.
>>
>> Thanks - pfa.
>
>
>
> which contains:
>
>   __attribute__((dllimport)) PyObject * PyObject_SelfIter(PyObject *);
>
>
>
> The result is identical on frogmouth, which builds happily using gcc 4.5.0.
> So this does look like a compiler quirk.

OK, I'll disable Python on Narwhal.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company