Re: plpythonu strange syntax error

I try to create a trivial (for testing purpouse) function with the
plpythonu language in postgresql 8.0 but when I try to utilize it the
postgres refuse to compile it for a sintax error:
'ERROR: plpython: could not compile function ''myfunc''
DETAIL: exceptions.SyntaxError: invalid syntax (line 2)'

-- Function: myfunc(text)

-- DROP FUNCTION myfunc(text);

CREATE OR REPLACE FUNCTION myfunc(text)
RETURNS text AS
$BODY$c=1
c = 2$BODY$
LANGUAGE 'plpythonu' VOLATILE;
ALTER FUNCTION myfunc(text) OWNER TO postgres;

I.e. every time I try to add a second line to myfunc (I'm not able to
write useful function made by single line of code!:) postgres give me
the previous error. In fact if I delete the second line ('c=2', the
$BODYs text is automatically created by the pgadmin III):

CREATE OR REPLACE FUNCTION myfunc(text)
RETURNS text AS
'c=1'
LANGUAGE 'plpythonu' VOLATILE;
ALTER FUNCTION myfunc(text) OWNER TO postgres;

all works as expected.

Is it my fault or there is a bug that blocks the possibility to write
function with more than one line of code?

Thanks in advance for any advice.

ciao, Michele

On Fri, Feb 18, 2005 at 10:38:17AM +0100, Michele Bendazzoli wrote:

> I try to create a trivial (for testing purpouse) function with the
> plpythonu language in postgresql 8.0 but when I try to utilize it the
> postgres refuse to compile it for a sintax error:
> 'ERROR: plpython: could not compile function ''myfunc''
> DETAIL: exceptions.SyntaxError: invalid syntax (line 2)'

Does the string that contains the Python code have carriage returns?
Python is picky about that; there was a thread about it recently.

http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Il giorno ven, 18-02-2005 alle 02:56 -0700, Michael Fuhr ha scritto:
> On Fri, Feb 18, 2005 at 10:38:17AM +0100, Michele Bendazzoli wrote:
>
> > I try to create a trivial (for testing purpouse) function with the
> > plpythonu language in postgresql 8.0 but when I try to utilize it the
> > postgres refuse to compile it for a sintax error:
> > 'ERROR: plpython: could not compile function ''myfunc''
> > DETAIL: exceptions.SyntaxError: invalid syntax (line 2)'
>
> Does the string that contains the Python code have carriage returns?
> Python is picky about that; there was a thread about it recently.
>
> http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php

No, absolutely. I try an retry, but I'm not able to add a second line
without the previsuo error message appears.

Maybe it is related with a PGAdmin III bug?

Thank you,

ciao, Michele

> > Does the string that contains the Python code have carriage returns?
> > Python is picky about that; there was a thread about it recently.
> >
> > http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php

I read the thread but they seem to have miss the point: there is not a
platform issue here because postgresql, pgadmin and python are both
running on the same machine (a windows 2003 server)!

Have I to subscribe the pgsql-general list and report this?

Thank you in advance for any advice.

ciao, Michele

Michele Bendazzoli <mickymouse(at)mickymouse(dot)it> writes:
> Il giorno ven, 18-02-2005 alle 02:56 -0700, Michael Fuhr ha scritto:
>> Does the string that contains the Python code have carriage returns?
>> Python is picky about that; there was a thread about it recently.
>> http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php

> No, absolutely. I try an retry, but I'm not able to add a second line
> without the previsuo error message appears.

> Maybe it is related with a PGAdmin III bug?

Can you create a function that works if you *don't* use pgAdmin to do
it? Also try pg_dump'ing the function so you can see exactly what
pgAdmin put into it.

I seem to recall that pgAdmin is a bit cavalier about whitespace in
function bodies, which in sanely-defined languages is no big deal
but in Python there are multiple ways in which it will kill you.
This could be either a problem of inconsistent newlines or excess
leading whitespace ...

regards, tom lane

On Fri, Feb 18, 2005 at 12:44:47PM +0100, Michele Bendazzoli wrote:
> > > Does the string that contains the Python code have carriage returns?
> > > Python is picky about that; there was a thread about it recently.
> > >
> > > http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php
>
> I read the thread but they seem to have miss the point: there is not a
> platform issue here because postgresql, pgadmin and python are both
> running on the same machine (a windows 2003 server)!

PEP 278 (http://www.python.org/peps/pep-0278.txt) says this:

There is no support for universal newlines in strings passed
to eval() or exec. It is envisioned that such strings always
have the standard \n line feed, if the strings come from a file
that file can be read with universal newlines.

If I'm interpreting that correctly, then regardless of platform, a
string containing code to be executed must end its lines with LF
(\n), not CRLF (\r\n). When running an ordinary Python script, a
CRLF => LF translation presumably happens as the script is read,
before the code is executed. You might not be getting that translation
when using pgAdmin. As I recall from the thread, there was also
concern about restoring a function that had been dumped with pg_dump.

Have you checked whether the plpythonu code contains carriage
returns? I forget if there's an easier way, but you could do
this:

SELECT replace(prosrc, '\r', 'CR')
FROM pg_proc
WHERE proname = 'foo';

I just created a plpythonu function with a carriage return and the
above command shows this:

replace
------------------
x = 1CR
return x
(1 row)

Trying to run the function fails:

SELECT foo();
ERROR: plpython: could not compile function "foo"
DETAIL: exceptions.SyntaxError: invalid syntax (line 2)

If I strip the carriage return from the function's prosrc column
then it works:

UPDATE pg_proc SET prosrc = replace(prosrc, '\r', '')
WHERE proname = 'foo';

SELECT foo();
foo
-----
1
(1 row)

You might try that to see if it works (be careful modifying system
tables -- I'd suggest using a transaction so you can ROLLBACK if
the update affected too many rows).

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Il giorno ven, 18-02-2005 alle 09:24 -0500, Tom Lane ha scritto:
cut
> I seem to recall that pgAdmin is a bit cavalier about whitespace in
> function bodies, which in sanely-defined languages is no big deal
> but in Python there are multiple ways in which it will kill you.
> This could be either a problem of inconsistent newlines or excess
> leading whitespace ...

Inconsistent newlines. I try to resolve the problem using psql instead
of PgAdmin to create the functions.

Thank you

ciao, Michele

Excuse me if i don't replay before, but i haven't the possibility to try
pgadmin in a windows system (it is located in the Customer's Firm).

Il giorno ven, 18-02-2005 alle 10:40 -0700, Michael Fuhr ha scritto:
cut

> If I'm interpreting that correctly, then regardless of platform, a
> string containing code to be executed must end its lines with LF
> (\n), not CRLF (\r\n). When running an ordinary Python script, a
> CRLF => LF translation presumably happens as the script is read,
> before the code is executed. You might not be getting that translation
> when using pgAdmin. As I recall from the thread, there was also
> concern about restoring a function that had been dumped with pg_dump.
>
> Have you checked whether the plpythonu code contains carriage
> returns? I forget if there's an easier way, but you could do
> this:
>
> SELECT replace(prosrc, '\r', 'CR')
> FROM pg_proc
> WHERE proname = 'foo';
>
> I just created a plpythonu function with a carriage return and the
> above command shows this:
>
> replace
> ------------------
> x = 1CR
> return x
> (1 row)

> Trying to run the function fails:
>
> SELECT foo();
> ERROR: plpython: could not compile function "foo"
> DETAIL: exceptions.SyntaxError: invalid syntax (line 2)
>
> If I strip the carriage return from the function's prosrc column
> then it works:
>
> UPDATE pg_proc SET prosrc = replace(prosrc, '\r', '')
> WHERE proname = 'foo';
>
> SELECT foo();
> foo
> -----
> 1
> (1 row)
>
> You might try that to see if it works (be careful modifying system
> tables -- I'd suggest using a transaction so you can ROLLBACK if
> the update affected too many rows).

Yes it works. So the problem is that in Windows system PGAdmin seem to
create the function with CRLF characters.

I don't know if it is a python fault or a pgadmin fault but I think that
someone should fix it, because it is impossibile to use PGAdmin to
create plpython function in windows ...

Thank you for the the quick and precise response.

ciao, Michele

On Sat, Mar 05, 2005 at 04:46:02PM +0100, Michele Bendazzoli wrote:

> Yes it works. So the problem is that in Windows system PGAdmin seem to
> create the function with CRLF characters.
>
> I don't know if it is a python fault or a pgadmin fault but I think that
> someone should fix it, because it is impossibile to use PGAdmin to
> create plpython function in windows ...

Opinion differs on whose fault it is. My understanding is that
Python's behavior is intentional, so one could argue that pgAdmin
should take care of the problem. On the other hand, one could argue
that Python's behavior is silly, and that Python should be fixed.

Since pgAdmin is a more specific tool than Python is, I expect it
would be easier to address the problem in pgAdmin. But I don't
know enough about pgAdmin to say whether such a "fix" would cause
other problems.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/