Re: plpython and bytea

Hi

It seems that plpython is unable to return bytea string when it contains
NUL bytes:

hannu=# CREATE OR REPLACE FUNCTION get_bytea_with_nul() RETURNS bytea AS
'
return ''aa\\0bb''
' LANGUAGE plpythonu SECURITY DEFINER;

hannu=# select get_bytea_with_nul();
get_bytea_with_nul
--------------------
aa
(1 row)

probably related to plpythons way of generating return value via
converting python objcet to its string representation and then letting
postgres's input func to convert it back.

Btw, does anyone know where Andrew Bosma (the original author of
plpython) is ?

I would probably have a paid job improving an opensource project for
him :)

--
Hannu Krosing <hannu(at)skype(dot)net>

Am Montag, den 21.11.2005, 02:11 +0200 schrieb Hannu Krosing:
> Hi
>
> It seems that plpython is unable to return bytea string when it contains
> NUL bytes:
>
> hannu=# CREATE OR REPLACE FUNCTION get_bytea_with_nul() RETURNS bytea AS
> '
> return ''aa\\0bb''
> ' LANGUAGE plpythonu SECURITY DEFINER;
>
> hannu=# select get_bytea_with_nul();
> get_bytea_with_nul
> --------------------
> aa
> (1 row)
>
>
> probably related to plpythons way of generating return value via
> converting python objcet to its string representation and then letting
> postgres's input func to convert it back.

Did you also try:

http://python.projects.postgresql.org/project/be.html

? Afaic it works a little different.

>
> Btw, does anyone know where Andrew Bosma (the original author of
> plpython) is ?
>
> I would probably have a paid job improving an opensource project for
> him :)
>
>

On Mon, 2005-11-21 at 08:37 +0100, Tino Wildenhain wrote:
> Am Montag, den 21.11.2005, 02:11 +0200 schrieb Hannu Krosing:
> > Hi
> >
> > It seems that plpython is unable to return bytea string when it contains
> > NUL bytes:
> >
...
> Did you also try:
>
> http://python.projects.postgresql.org/project/be.html
>
> ? Afaic it works a little different.

The project seems quite interesting, will surely take a deeper look

It looks quite alpha, seems to mess to deeply with backend, and the cvs
checkout of module be does not build, so I will probably not be able to
use it in production for quite a while :(

[hannu(at)localhost be]$ python setup.py build
running build
running config
Traceback (most recent call last):
File "setup.py", line 256, in ?
setup(defaults)
File "setup.py", line 249, in setup
d = distutils.core.setup(**kw)
File "/usr/lib/python2.4/distutils/core.py", line 149, in setup
dist.run_commands()
File "/usr/lib/python2.4/distutils/dist.py", line 946, in run_commands
self.run_command(cmd)
File "/usr/lib/python2.4/distutils/dist.py", line 966, in run_command
cmd_obj.run()
File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 193, in
run
self.run_command('config')
File "/usr/lib/python2.4/distutils/cmd.py", line 333, in run_command
self.distribution.run_command(command)
File "/usr/lib/python2.4/distutils/dist.py", line 966, in run_command
cmd_obj.run()
File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 166, in
run
self.distribution.config = cfg = self.load()
File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 129, in
load
cfgsrc(self)
File "setup.py", line 19, in configure
import postgresql.utility.config
ImportError: No module named postgresql.utility.config

-----------------
Hannu Krosing

Hannu Krosing schrieb:
> On Mon, 2005-11-21 at 08:37 +0100, Tino Wildenhain wrote:
>
>>Am Montag, den 21.11.2005, 02:11 +0200 schrieb Hannu Krosing:
>>
>>>Hi
>>>
>>>It seems that plpython is unable to return bytea string when it contains
>>>NUL bytes:
>>>
>
> ...
>
>>Did you also try:
>>
>>http://python.projects.postgresql.org/project/be.html
>>
>>? Afaic it works a little different.
>
>
>
> The project seems quite interesting, will surely take a deeper look
>
> It looks quite alpha, seems to mess to deeply with backend, and the cvs
> checkout of module be does not build, so I will probably not be able to
> use it in production for quite a while :(

Well I had it running and it was very impressive. However
it seems no easy install yet - tried a cvs head with
similar problems. I hope the author reads this here.
At least I met him on freenode #postgresql

On Mon, 2005-11-21 at 15:18 +0200, Hannu Krosing wrote:
> The project seems quite interesting, will surely take a deeper look
>
> It looks quite alpha, seems to mess to deeply with backend, and the cvs
> checkout of module be does not build, so I will probably not be able to
> use it in production for quite a while :(
>
> [hannu(at)localhost be]$ python setup.py build
> running build
> running config
> Traceback (most recent call last):
> File "setup.py", line 256, in ?
> setup(defaults)
> File "setup.py", line 249, in setup
> d = distutils.core.setup(**kw)
> File "/usr/lib/python2.4/distutils/core.py", line 149, in setup
> dist.run_commands()
> File "/usr/lib/python2.4/distutils/dist.py", line 946, in run_commands
> self.run_command(cmd)
> File "/usr/lib/python2.4/distutils/dist.py", line 966, in run_command
> cmd_obj.run()
> File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 193, in
> run
> self.run_command('config')
> File "/usr/lib/python2.4/distutils/cmd.py", line 333, in run_command
> self.distribution.run_command(command)
> File "/usr/lib/python2.4/distutils/dist.py", line 966, in run_command
> cmd_obj.run()
> File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 166, in
> run
> self.distribution.config = cfg = self.load()
> File "/home/hannu/work/postgresPy/be/lib/ldistutils.py", line 129, in
> load
> cfgsrc(self)
> File "setup.py", line 19, in configure
> import postgresql.utility.config
> ImportError: No module named postgresql.utility.config

The 'layout' package needs to be installed first.

See this quick start section:
http://python.projects.postgresql.org/quick.html#Fetch+and+Install+the
+Backend
('be' depends on 'lo' and 'ex')

--
Regards, James William Pye

Am Montag, den 21.11.2005, 09:08 -0700 schrieb James William Pye:
> On Mon, 2005-11-21 at 15:18 +0200, Hannu Krosing wrote:
> > The project seems quite interesting, will surely take a deeper look
> >
...
>
> The 'layout' package needs to be installed first.
>
> See this quick start section:
> http://python.projects.postgresql.org/quick.html#Fetch+and+Install+the
> +Backend
> ('be' depends on 'lo' and 'ex')

There is:

cvs -d :pserver:anonymous:@cvs.pgfoundry.org co lo ex be

which should be:

cvs -d :pserver:anonymous:@cvs.pgfoundry.org:/cvsroot/python co lo ex be

to work.

On Mon, 2005-11-21 at 02:11 +0200, Hannu Krosing wrote:
> Hi
>
> It seems that plpython is unable to return bytea string when it contains
> NUL bytes:
>
> hannu=# CREATE OR REPLACE FUNCTION get_bytea_with_nul() RETURNS bytea AS
> '
> return ''aa\\0bb''
> ' LANGUAGE plpythonu SECURITY DEFINER;
>
> hannu=# select get_bytea_with_nul();
> get_bytea_with_nul
> --------------------
> aa
> (1 row)
>
>
> probably related to plpythons way of generating return value via
> converting python objcet to its string representation and then letting
> postgres's input func to convert it back.

Ok, I was able to successfuly return all bytea values from plpython by
creating a bytea class that oveloads strings __str__ method to generate
something that postgresql's bytea type input method understands:

create or replace function get_bytea256() returns bytea as $$

class bytea(str):
def __str__(self):
res = []
for c in self:
if (c in ("\000","'","\\")):
res.append(r"\%03o" % ord(c))
else:
res.append(c)
return ''.join(res)

return bytea("".join([chr(i) for i in range(256)]))
$$ language plpythonu;

please note that this is a quick proof-of-concept implementation which
contains several gross inefficiencies :p

-----------------------
Hannu Krosing