Thread: plpython function problem workaround

I worked around the plpython problem that doesn't allow scripts created on
Windows to be run on the *nix server with the following statement.
    update pg_proc set prosrc=replace(prosrc,chr(13),'') where prolang=87238
--plpythonu's oid in my setup is 87238. I don't know if that is a standard
or just on mine.

Is there a way to automate that script every time a plpythonu function is
created?
I tried writing a trigger on the pg_proc table but it wouldn't let me:
    ERROR:  permission denied: "pg_proc" is a system catalog
Is there a way to do this without playing with the source code?

CREATE FUNCTION fixpython() RETURNS trigger AS $$
BEGIN
        IF new.prolang=87238 THEN
            new.prosrc=replace(prosrc,chr(13),'');
        END IF;
end
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER fixpython BEFORE INSERT OR UPDATE ON pg_proc
    FOR EACH ROW EXECUTE PROCEDURE fixpython();

On Mon, Mar 14, 2005 at 02:37:00PM +0200, Sim Zacks wrote:

> I worked around the plpython problem that doesn't allow scripts created on
> Windows to be run on the *nix server with the following statement.
>     update pg_proc set prosrc=replace(prosrc,chr(13),'') where prolang=87238
> --plpythonu's oid in my setup is 87238. I don't know if that is a standard
> or just on mine.

The oid is arbitrary, so you should get it via a (sub)query instead
of hardcoding it.

> Is there a way to automate that script every time a plpythonu function is
> created?
> I tried writing a trigger on the pg_proc table but it wouldn't let me:

Hmmm...plpythonu doesn't install a VALIDATOR function.  I wonder
if you could exploit that?  This is just a brainstorm, but the
following worked for me in trivial tests:

CREATE FUNCTION fixpython(funcoid oid) RETURNS void AS $$
BEGIN
    UPDATE pg_proc SET prosrc = replace(prosrc, chr(13), '')
     WHERE oid = funcoid;

    RETURN;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

UPDATE pg_language SET lanvalidator = 'fixpython'::regproc
 WHERE lanname = 'plpythonu';

Are there any problems with doing this?  Is a VALIDATOR function
permitted to modify the function it's validating?  This wouldn't
work if plpythonu ever installs a VALIDATOR, but you might be able
to use it until such time (barring objections about why it's a Bad
Idea, that is).

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

Thank You Michael,

It worked when I tried it on the 3 functions that I have.
I checked the archives for any discussion on the Validator and there wasn't
anything specifically discussing it.
I really didn't find a lot of information about the Validators either, for
example where did you see that a validator function gets the language oid
passed in? The only place I could find it was after I looked at the other
languages installed on my system and saw that they all had the same argument
type passed in.

Sim

"Michael Fuhr" <mike@fuhr.org> wrote in message
news:20050314134405.GA20902@winnie.fuhr.org...
> On Mon, Mar 14, 2005 at 02:37:00PM +0200, Sim Zacks wrote:
>
> > I worked around the plpython problem that doesn't allow scripts created
on
> > Windows to be run on the *nix server with the following statement.
> >     update pg_proc set prosrc=replace(prosrc,chr(13),'') where
prolang=87238
> > --plpythonu's oid in my setup is 87238. I don't know if that is a
standard
> > or just on mine.
>
> The oid is arbitrary, so you should get it via a (sub)query instead
> of hardcoding it.
>
> > Is there a way to automate that script every time a plpythonu function
is
> > created?
> > I tried writing a trigger on the pg_proc table but it wouldn't let me:
>
> Hmmm...plpythonu doesn't install a VALIDATOR function.  I wonder
> if you could exploit that?  This is just a brainstorm, but the
> following worked for me in trivial tests:
>
> CREATE FUNCTION fixpython(funcoid oid) RETURNS void AS $$
> BEGIN
>     UPDATE pg_proc SET prosrc = replace(prosrc, chr(13), '')
>      WHERE oid = funcoid;
>
>     RETURN;
> END;
> $$ LANGUAGE plpgsql VOLATILE STRICT;
>
> UPDATE pg_language SET lanvalidator = 'fixpython'::regproc
>  WHERE lanname = 'plpythonu';
>
> Are there any problems with doing this?  Is a VALIDATOR function
> permitted to modify the function it's validating?  This wouldn't
> work if plpythonu ever installs a VALIDATOR, but you might be able
> to use it until such time (barring objections about why it's a Bad
> Idea, that is).
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

On Mon, Mar 14, 2005 at 04:08:09PM +0200, Sim Zacks wrote:

> I checked the archives for any discussion on the Validator and there wasn't
> anything specifically discussing it.
> I really didn't find a lot of information about the Validators either, for
> example where did you see that a validator function gets the language oid
> passed in?

The validator is passed the function's OID, not the language's OID.
See the documentation for CREATE LANGUAGE, as well as the "Procedural
Languages" chapter:

http://www.postgresql.org/docs/8.0/interactive/sql-createlanguage.html
http://www.postgresql.org/docs/8.0/interactive/xplang.html

Note that my idea to use the validator function was just a brainstorm,
not thoroughly tested or thought out.  Maybe one of the developers
will comment about the wisdom of (ab)using it the way I suggested.

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

Michael Fuhr <mike@fuhr.org> writes:
> Are there any problems with doing this?  Is a VALIDATOR function
> permitted to modify the function it's validating?

Well, it's not *supposed* to, but the tuple is already stored so I
guess an UPDATE on it will work.  At the moment.

> This wouldn't work if plpythonu ever installs a VALIDATOR, but you
> might be able to use it until such time (barring objections about why
> it's a Bad Idea, that is).

The proposed hack seems far too simplistic to me ... what of CRs that
are deliberately included in string literals?

I don't know Python at all, so I don't know how complicated its lexical
structure is, but ISTM you'd at least need enough smarts to distinguish
literals from unquoted whitespace.

The other small fly in the ointment is that when the server is running
on Windows, I suppose we would have to *put in* rather than remove CRs.
Sim probably doesn't care about that case, but we couldn't accept an
official patch that doesn't handle it.

            regards, tom lane

On Mon, Mar 14, 2005 at 10:54:22AM -0500, Tom Lane wrote:

> The proposed hack seems far too simplistic to me ... what of CRs that
> are deliberately included in string literals?

Yeah, I meant to mention that; I think it's been brought up before
in related threads.  The programmer would need to be aware of that
issue and allow for it.  It doesn't seem to be a problem if you use
\r escape sequences in a dollar-quoted function body or if you use
\\r in single quotes -- is there any case where those sequences
might get converted to literal CRs?  Third-party software like user
interfaces might be problematic, but what about PostgreSQL itself
and its "official" tools (psql, pg_dump, etc.)?

> I don't know Python at all, so I don't know how complicated its lexical
> structure is, but ISTM you'd at least need enough smarts to distinguish
> literals from unquoted whitespace.

The example I posted was merely that: an example.  I was more
interested in whether abusing the validator mechanism would work
or if it might have subtle problems.  A function that understands
the Python grammar is left as an exercise for the reader.

> The other small fly in the ointment is that when the server is running
> on Windows, I suppose we would have to *put in* rather than remove CRs.

Would we?  My understanding is that code passed to PyRun_String()
and friends must be free of line-ending CRs on all platforms, and
that the code that reads a "normal" Python script takes care of
that (i.e., normalizes line endings to be LF only).  Can anybody
confirm or deny?

> Sim probably doesn't care about that case, but we couldn't accept an
> official patch that doesn't handle it.

I wasn't proposing a patch, at least not yet.  Just throwing out
an idea that somebody might be able to build on.

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

On Mon, 14 Mar 2005, Michael Fuhr wrote:

> Would we?  My understanding is that code passed to PyRun_String()
> and friends must be free of line-ending CRs on all platforms, and
> that the code that reads a "normal" Python script takes care of
> that (i.e., normalizes line endings to be LF only).  Can anybody
> confirm or deny?

I'm not sure of that. I suspect you'll need to pass CRs on windows.

If anyone manages to compile the following code on Windows...

#include "Python.h"

void
run_program(const char *program)
{
     PyObject *ret, *globals, *locals;

     printf("> running:\n%s\n", program);
     globals = PyDict_New();
     locals = PyDict_New();
     ret = PyRun_String(program, Py_file_input, globals, locals);
     if (ret) {
         Py_DECREF(ret);
         printf("\n");
     } else {
         PyErr_Print();
     }
     Py_DECREF(locals);
     Py_DECREF(globals);
     printf("> end\n\n");

}

int
main(int argc, char *argv[])
{
     const char *program1 = "print 1\nprint 2\n";
     const char *program2 = "print 1\r\nprint 2\r\n";

     Py_Initialize();
     printf("> Initialized.\n");
     printf("> Python %s\n", Py_GetVersion());
     run_program(program1);
     run_program(program2);
     Py_Finalize();
     printf("> Finalized.\n");
}

On my Fedora Core 2, I need to complile it with the following command:

gcc -I/usr/include/python2.3 -L/usr/lib/python2.3/config py-test.c -o py-test\
-lpython2.3 -ldl -lm -lpthread -lutil

This is my first attempt to embed python, so I may be missing something...

On Linux, you get:

$ ./py-test 2>&1 | cat -v
> Initialized.
> Python 2.3.3 (#1, May  7 2004, 10:31:40)
[GCC 3.3.3 20040412 (Red Hat Linux 3.3.3-7)]
> running:
print 1
print 2

1
2

> end

> running:
print 1^M
print 2^M

   File "<string>", line 1
     print 1^M
            ^
SyntaxError: invalid syntax
> end

> Finalized.


I bet on windows the first program fails and the second is ok.

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it

On Mon, Mar 14, 2005 at 08:14:42PM +0100, Marco Colombo wrote:
> On Mon, 14 Mar 2005, Michael Fuhr wrote:
>
> >Would we?  My understanding is that code passed to PyRun_String()
> >and friends must be free of line-ending CRs on all platforms, and
> >that the code that reads a "normal" Python script takes care of
> >that (i.e., normalizes line endings to be LF only).  Can anybody
> >confirm or deny?
>
> I'm not sure of that. I suspect you'll need to pass CRs on windows.

Hmmm...I think that would be inconsistent with previous reports.
For example, in the following message, the poster said that everything
(PostgreSQL, pgAdmin) was running on Windows 2003:

http://archives.postgresql.org/pgsql-interfaces/2005-02/msg00066.php

I suggested that he strip the CRs from pg_proc.prosrc and he said
it worked:

http://archives.postgresql.org/pgsql-interfaces/2005-03/msg00014.php

It's not clear that the test in the second message was run on a
Windows server (apparently pgAdmin was run on a Windows client),
but I think the beginning of the message is saying that he didn't
reply for so long because he didn't have access to a Windows server.
From that I infer that he tried my suggestion and posted the results
when he finally did get access to a customer's Windows server.  I
could be misreading that, however.

A couple of months ago Stuart Bishop brought this issue up in
python-dev.  Most of the thread is along the lines of "strip the
carriage returns":

http://mail.python.org/pipermail/python-dev/2005-January/051203.html

> If anyone manages to compile the following code on Windows...
...
> I bet on windows the first program fails and the second is ok.

Hopefully somebody will do a Windows test of the code you posted.
I'd find it bizarre that the Python code embedded in a C program
had to care whether it was running on *nix or Windows.

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

I don't think that this type of solution should be discussed as an official
patch.
If it was, I would recommend solving the problem in source code when the
function is passed to the translator. That way each platform could fix the
code to work with as is needed and the code would be portable.

I ran into this problem about half a year ago and it didn't go anywhere,
then I saw a long discussion about it that also didn't go anywhere. I had
given up on using plpython until now because I actually need it. So I
figured out how to make it work and thought that it would be helpful to
others, but I couldn't figure out how to automate the fix.

Hopefully, aside from the issue of having CRs inside the actual program,
using the validator function/updating pg_proc directly won't cause any
harmful ramifications. I don't have access to any other platforms right now,
aside from Windows client connected to Linux server, so I really couldn't
reliably test any other situation.

Sim

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:13832.1110815662@sss.pgh.pa.us...
> Michael Fuhr <mike@fuhr.org> writes:
> > Are there any problems with doing this?  Is a VALIDATOR function
> > permitted to modify the function it's validating?
>
> Well, it's not *supposed* to, but the tuple is already stored so I
> guess an UPDATE on it will work.  At the moment.
>
> > This wouldn't work if plpythonu ever installs a VALIDATOR, but you
> > might be able to use it until such time (barring objections about why
> > it's a Bad Idea, that is).
>
> The proposed hack seems far too simplistic to me ... what of CRs that
> are deliberately included in string literals?
>
> I don't know Python at all, so I don't know how complicated its lexical
> structure is, but ISTM you'd at least need enough smarts to distinguish
> literals from unquoted whitespace.
>
> The other small fly in the ointment is that when the server is running
> on Windows, I suppose we would have to *put in* rather than remove CRs.
> Sim probably doesn't care about that case, but we couldn't accept an
> official patch that doesn't handle it.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

"Sim Zacks" <sim@compulab.co.il> writes:
> I don't think that this type of solution should be discussed as an official
> patch.
> If it was, I would recommend solving the problem in source code when the
> function is passed to the translator.

Indeed, but first we need to work out what the necessary translation is.
A hack using a VALIDATOR function isn't an unreasonable way to prototype
the conversion logic.

            regards, tom lane

I've been looking at the possibility of having a planned CR in the source
code and I don't see a case where it would happen.
I tried this function, as an example:
    create or replace function crtest() returns int as
    $$
    x=plpy.execute("select * from pg_proc where prosrc like '%\r%'")
    return x.nrows()
    $$ language 'plpythonu'

and it considered the \r to be text and not a CR. Therefore the update
function removed the CR at the end of the line but ignored the \r that was
specifically mentioned.

Is there any case that someone can think of where an actual CR, not a
textual representation of it, would purposefully be put into the code?

Sim


"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:3279.1110868888@sss.pgh.pa.us...
> "Sim Zacks" <sim@compulab.co.il> writes:
> > I don't think that this type of solution should be discussed as an
official
> > patch.
> > If it was, I would recommend solving the problem in source code when the
> > function is passed to the translator.
>
> Indeed, but first we need to work out what the necessary translation is.
> A hack using a VALIDATOR function isn't an unreasonable way to prototype
> the conversion logic.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Now that I think about it, FTP programs currently do this when they transfer
ASCII files.
If you have a planned CR in your program and FTP from mac to windows the CR
becomes a CRLF and if you FTP from mac to unix the CR changes to an LF.


"Sim Zacks" <sim@compulab.co.il> wrote in message
news:d1674d$1l39$1@news.hub.org...
> I've been looking at the possibility of having a planned CR in the source
> code and I don't see a case where it would happen.
> I tried this function, as an example:
>     create or replace function crtest() returns int as
>     $$
>     x=plpy.execute("select * from pg_proc where prosrc like '%\r%'")
>     return x.nrows()
>     $$ language 'plpythonu'
>
> and it considered the \r to be text and not a CR. Therefore the update
> function removed the CR at the end of the line but ignored the \r that was
> specifically mentioned.
>
> Is there any case that someone can think of where an actual CR, not a
> textual representation of it, would purposefully be put into the code?
>
> Sim
>
>
> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
> news:3279.1110868888@sss.pgh.pa.us...
> > "Sim Zacks" <sim@compulab.co.il> writes:
> > > I don't think that this type of solution should be discussed as an
> official
> > > patch.
> > > If it was, I would recommend solving the problem in source code when
the
> > > function is passed to the translator.
> >
> > Indeed, but first we need to work out what the necessary translation is.
> > A hack using a VALIDATOR function isn't an unreasonable way to prototype
> > the conversion logic.
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>

The following is how I understand it, but please let's delay further
discussion until someone tests the program under Windows.

On Mon, 14 Mar 2005, Michael Fuhr wrote:

> Hmmm...I think that would be inconsistent with previous reports.
> For example, in the following message, the poster said that everything
> (PostgreSQL, pgAdmin) was running on Windows 2003:
>
> http://archives.postgresql.org/pgsql-interfaces/2005-02/msg00066.php

I'm sorry, he's wrong.
The initial report was by Hong Yuan:
http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php

later he clarified:
http://archives.postgresql.org/pgsql-general/2005-01/msg00858.php

"I am using pgAdmin III Version 1.2.0 under Chinese Windows XP, while
  the database is 7.4.6 under Linux."

BTW I just noticed someone else provided a simpler example:
http://archives.postgresql.org/pgsql-general/2005-01/msg00876.php

someone should try and complile those under windows.

> I suggested that he strip the CRs from pg_proc.prosrc and he said
> it worked:
>
> http://archives.postgresql.org/pgsql-interfaces/2005-03/msg00014.php
>
> It's not clear that the test in the second message was run on a
> Windows server (apparently pgAdmin was run on a Windows client),
> but I think the beginning of the message is saying that he didn't
> reply for so long because he didn't have access to a Windows server.
> From that I infer that he tried my suggestion and posted the results
> when he finally did get access to a customer's Windows server.  I
> could be misreading that, however.

I have no idea of where Michele Bendazzoli ran that code. He's not
the original poster, tho.

> A couple of months ago Stuart Bishop brought this issue up in
> python-dev.  Most of the thread is along the lines of "strip the
> carriage returns":
>
> http://mail.python.org/pipermail/python-dev/2005-January/051203.html

Sorry again, but he's assuming Unix on the server side, and
Windows or Mac on the client side.

>> If anyone manages to compile the following code on Windows...
> ...
>> I bet on windows the first program fails and the second is ok.
>
> Hopefully somebody will do a Windows test of the code you posted.
> I'd find it bizarre that the Python code embedded in a C program
> had to care whether it was running on *nix or Windows.

I find it perfectly consistent! Face it, _any_ C program that's handling
data of type _text_ has to know which platform it is running on. If you
don't like the behaviour of Python functions, think of printf().
C programmers under Windows are used to write:

     printf("Hello World!\r\n");

as much (old) Mac programmers write:

     printf("Hello World!\r");

and Unix programmers write:

     printf("Hello World!\n");

_ANY_ C program that processes multiline text input has to know which
platform it is running on, otherwise it reads or proceduces garbage.

Python just requires the input being text, which seems reasonable to me,
since, by design, a python program is more that just a sequence of ';'-
separated statements (ala C or Perl), with _optional_ intermixing
whitespaces. White spaces ('eol' included) do have a meaning in Python
syntax.

BTW, this attitude reminds me of PostgreSQL strict validation of input,
compared to more relaxed checking made by MySQL. I really don't feel
the need to enumerate the merits of input validation on this list.
Python functions want 'valid text' and the definition is platform
dependent, unfortunately. Why should it be relaxed, and accept invalid
text as input?

If you are to compile any unix text processing utility, such as grep,
on a mac, you'll have to change the source and have the program split
lines at \r (under windows, splitting lines at \n still works, but
leaves a spurious \r around). Python is the same, since it expects
programs as _text_.

The universal newline support is totally another matter. And it applies
to files only (it's a wrapper around file functions). It's a tool
for lazy programmers.

It is possible that in the feature the Python lexxer is changed to
recognize different line endings (since there's no valid case for a
\r at the end of a line that I can think of). But requiring the
input being text is not 'bizarre' at all.

The issue about text representation affects _any_ application.
Treating text as binary data is plain wrong, IMHO, and will always
lead to problems.

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it

On Tue, Mar 15, 2005 at 08:09:47AM +0200, Sim Zacks wrote:
> I don't think that this type of solution should be discussed as an official
> patch.
> If it was, I would recommend solving the problem in source code when the
> function is passed to the translator. That way each platform could fix the
> code to work with as is needed and the code would be portable.
>
> I ran into this problem about half a year ago and it didn't go anywhere,
> then I saw a long discussion about it that also didn't go anywhere. I had
> given up on using plpython until now because I actually need it.

I'd like to insert one note here.  While I'm not particularly familiar
with either perl or python, when I encountered the referred-to thread
regarding Windows/Unix newline incompatibilities in Python, I ran some
tests on my Linux system.  I wrote some very simple scripts in Perl and
Python and tested them.  They ran OK when I saved them in native Linux
newlines, they ran, but if I saved them in DOS mode (vim), neither the
Python nor the Perl scripts would run.  I noted that some of the writers
in that thread were critical of Python for not taking care of the
conversions, and it may be that the Perl interfaces in postgresql may
take care of this, I have not tried this, but it seems that my tests, if
valid, indicate that this problem is not restricted to Python alone as
far as the interpreters are concerned.

This observation may have nothing to do with the pl/Perl and pl/Python
interfaces, but I thought I'd pass this along FWIW.

> So I
> figured out how to make it work and thought that it would be helpful to
> others, but I couldn't figure out how to automate the fix.
>
> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message

> > I don't know Python at all, so I don't know how complicated its lexical
> > structure is, but ISTM you'd at least need enough smarts to distinguish
> > literals from unquoted whitespace.
> >
> > The other small fly in the ointment is that when the server is running
> > on Windows, I suppose we would have to *put in* rather than remove CRs.
> > Sim probably doesn't care about that case, but we couldn't accept an
> > official patch that doesn't handle it.

"Sim Zacks" <sim@compulab.co.il> writes:
> I've been looking at the possibility of having a planned CR in the source
> code and I don't see a case where it would happen.

Does python actually disallow newlines in string literals?  That is

    x = 'foo
bar'

Whether you think this is good style is not the question --- is it
allowed by the language?

            regards, tom lane

On Tue, Mar 15, 2005 at 09:46:54AM -0500, Tom Lane wrote:
> "Sim Zacks" <sim@compulab.co.il> writes:
> > I've been looking at the possibility of having a planned CR in the source
> > code and I don't see a case where it would happen.
>
> Does python actually disallow newlines in string literals?  That is
>
>     x = 'foo
> bar'
>
> Whether you think this is good style is not the question --- is it
> allowed by the language?

You can do

    x = """this has
newlines embedded"""

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)

On Tue, 2005-03-15 at 07:33 -0600, David wrote:

> [about the line-termination problem in plpython]

> I'd like to insert one note here.  While I'm not particularly familiar
> with either perl or python, when I encountered the referred-to thread
> regarding Windows/Unix newline incompatibilities in Python, I ran some
> tests on my Linux system.  I wrote some very simple scripts in Perl and
> Python and tested them.  They ran OK when I saved them in native Linux
> newlines, they ran, but if I saved them in DOS mode (vim), neither the
> Python nor the Perl scripts would run.

actually, perl scripts with \r\n line endings will run just fine in
unix/linux. what you might have been experiencing, is the fact that the
unix shell is expecting \n lineendings, and the #! line could have been
failing to run the perl executable.

gnari

On Tue, 15 Mar 2005, Tom Lane wrote:

> "Sim Zacks" <sim@compulab.co.il> writes:
>> I've been looking at the possibility of having a planned CR in the source
>> code and I don't see a case where it would happen.
>
> Does python actually disallow newlines in string literals?  That is
>
>     x = 'foo
> bar'
>
> Whether you think this is good style is not the question --- is it
> allowed by the language?

You can with triple-quoting and by escaping it with backslash.
The following code, admitedly ugly, is valid python:

a = 'a\
bc'
print a

b = '''a
bc'''
print b

and produces:
abc
a
bc

as output. \<newline> in any non raw literal is allowed and ignored,
while a bare <newline> in a triple-quoted string literal is allowed
and retained.

Moreover, this is not an execise of bad style only. It's customary to
write docstrings as multiline triple-quoted string literals:

def afunction(a, b, c):
     """This is a function.

     Its arguments are:
      a - first argument
      b - second argument
      c - third argument.
     It does ans returns nothing.
     """
     pass

It's more or less the recommended way to document a function (or class
or module or whatever). See PEP 257 for more examples:
http://www.python.org/peps/pep-0257.html

So, to answer to your question, newlines are more than allowed in
string literals.

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it

On Tue, Mar 15, 2005 at 01:40:23PM +0100, Marco Colombo wrote:
> On Mon, 14 Mar 2005, Michael Fuhr wrote:
>
> >Hmmm...I think that would be inconsistent with previous reports.
> >For example, in the following message, the poster said that everything
> >(PostgreSQL, pgAdmin) was running on Windows 2003:
> >
> >http://archives.postgresql.org/pgsql-interfaces/2005-02/msg00066.php
>
> I'm sorry, he's wrong.

Wrong about what?  He reported that he was having the same problem
and that both client and server were running on Windows 2003.  Here's
his first message:

http://archives.postgresql.org/pgsql-interfaces/2005-02/msg00063.php

> The initial report was by Hong Yuan:
> http://archives.postgresql.org/pgsql-general/2005-01/msg00792.php

That was a different thread.  Same problem, but an earlier thread
that Michele apparently didn't know about until I mentioned it.

> later he clarified:
> http://archives.postgresql.org/pgsql-general/2005-01/msg00858.php
>
> "I am using pgAdmin III Version 1.2.0 under Chinese Windows XP, while
>  the database is 7.4.6 under Linux."

A problem with Windows <=> Linux doesn't preclude the same problem
from happening with Windows <=> Windows.  At issue is that pgAdmin
on Windows apparently adds carriage returns, and whether Python on
any platform doesn't like that (that's what we're still trying to
determine).

> BTW I just noticed someone else provided a simpler example:
> http://archives.postgresql.org/pgsql-general/2005-01/msg00876.php

That somebody was me.

> I have no idea of where Michele Bendazzoli ran that code. He's not
> the original poster, tho.

He was the original poster of *his* thread, the one with a subject
of "plpythonu strange syntax error."  He wasn't the first person
to report the problem, but his first message didn't reference any
previous messages.

http://archives.postgresql.org/pgsql-interfaces/2005-02/msg00063.php

I'll postpone commenting on the rest until we find out how the
example programs run on Windows.  If nobody follows up here then
maybe I'll wander over to comp.lang.python.

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

On Tue, 15 Mar 2005, Michael Fuhr wrote:

[...]
> That somebody was me.
Ok, sorry.

> I'll postpone commenting on the rest until we find out how the
> example programs run on Windows.  If nobody follows up here then
> maybe I'll wander over to comp.lang.python.

Yeah, there's no point in discussing until we have some real world
data. I can't compile on windows, so I'll have to wait someone else
to do that. I'm basing my opinions on Python documentation only.

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it

On Tue, Mar 15, 2005 at 06:03:01PM +0100, Marco Colombo wrote:
> On Tue, 15 Mar 2005, Michael Fuhr wrote:
> >I'll postpone commenting on the rest until we find out how the
> >example programs run on Windows.  If nobody follows up here then
> >maybe I'll wander over to comp.lang.python.
>
> Yeah, there's no point in discussing until we have some real world
> data. I can't compile on windows, so I'll have to wait someone else
> to do that. I'm basing my opinions on Python documentation only.

I've been looking through the Python source code (2.4.1c1) and I've
found several places that use only \n in embedded code.  One is
Modules/main.c, which says it's the "Python interpreter main program."
The Py_Main() function is processing command-line options and does
the following:

    if (c == 'c') {
            /* -c is the last option; following arguments
               that look like options are left for the
               command to interpret. */
            command = malloc(strlen(_PyOS_optarg) + 2);
            if (command == NULL)
                    Py_FatalError(
                       "not enough memory to copy -c argument");
            strcpy(command, _PyOS_optarg);
            strcat(command, "\n");
            break;
    }

Later, without further changes to the command variable, it does this:

    if (command) {
        sts = PyRun_SimpleStringFlags(command, &cf) != 0;
        free(command);

Modules/cPickle.c has additional examples:

    if (!( r=PyRun_String(
                   "def __init__(self, *args): self.args=args\n\n"
                   "def __str__(self):\n"
                   "  return self.args and ('%s' % self.args[0]) or '(what)'\n",
                   Py_file_input,
                   module_dict, t)  ))  return -1;

and

    if (!( r=PyRun_String(
                   "def __init__(self, *args): self.args=args\n\n"
                   "def __str__(self):\n"
                   "  a=self.args\n"
                   "  a=a and type(a[0]) or '(what)'\n"
                   "  return 'Cannot pickle %s objects' % a\n"
                   , Py_file_input,
                   module_dict, t)  ))  return -1;

The code in Demo/embed/demo.c uses only \n to terminate its lines:

    PyRun_SimpleString("import sys\n");
    PyRun_SimpleString("print sys.builtin_module_names\n");
    PyRun_SimpleString("print sys.modules.keys()\n");
    PyRun_SimpleString("print sys.executable\n");
    PyRun_SimpleString("print sys.argv\n");

If these examples are intended to run on Windows, then presumably
using \n works on that platform.  That doesn't necessarily preclude
\r\n from working as well, but apparently a platform-specific
sequence isn't required.  I'd still be interested in having somebody
run the test programs we've both posted to find out for sure.

Is anybody who's following this thread running a PostgreSQL server
with PL/Python on Windows?  We could use a test platform to answer
some questions, since the PostgreSQL aspect of this discussion is
about problems with PL/Python.

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

On Tue, Mar 15, 2005 at 03:41:37PM +0000, Ragnar Hafstað wrote:

> actually, perl scripts with \r\n line endings will run just fine in
> unix/linux.

Indeed, and PL/Perl doesn't care.  I just tested several PLs with
PostgreSQL 8.0.1 on Solaris 9 and here are the results:

PL/pgSQL   CRLF ok
PL/Perl    CRLF ok
PL/Ruby    CRLF ok
PL/Tcl     CRLF ok
PL/Python  CRLF fails
PL/R       CRLF fails

Details:

CREATE FUNCTION test_pgsql() RETURNS integer AS
'DECLARE x integer;\r\nBEGIN\r\nx := 123;\r\nRETURN x;\r\nEND;\r\n'
LANGUAGE plpgsql;

CREATE FUNCTION test_perl() RETURNS integer AS
'$x = 123;\r\nreturn $x;\r\n'
LANGUAGE plperl;

CREATE FUNCTION test_ruby() RETURNS integer AS
'x = 123\r\nreturn x\r\n'
LANGUAGE plruby;

CREATE FUNCTION test_tcl() RETURNS integer AS
'set x 123\r\nreturn $x\r\n'
LANGUAGE pltcl;

CREATE FUNCTION test_python() RETURNS integer AS
'x = 123\r\nreturn x\r\n'
LANGUAGE plpythonu;

CREATE FUNCTION test_r() RETURNS integer AS
'x <- 123\r\nreturn(x)\r\n'
LANGUAGE plr;

SELECT test_pgsql();
 test_pgsql
------------
        123
(1 row)

SELECT test_perl();
 test_perl
-----------
       123
(1 row)

SELECT test_ruby();
 test_ruby
-----------
       123
(1 row)

SELECT test_tcl();
 test_tcl
----------
      123
(1 row)

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

SELECT test_r();
ERROR:  R interpreter parse error
DETAIL:  R parse error caught in "PLR36865 <- function() {x <- 123
return(x)
}".
CONTEXT:  In PL/R function test_r

If I remove the CRs from the Python and R functions then they work:

CREATE OR REPLACE FUNCTION test_python() RETURNS integer AS
'x = 123\nreturn x\n'
LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION test_r() RETURNS integer AS
'x <- 123\nreturn(x)\n'
LANGUAGE plr;

SELECT test_python();
 test_python
-------------
         123
(1 row)

SELECT test_r();
 test_r
--------
    123
(1 row)

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

On Tue, Mar 15, 2005 at 06:03:01PM +0100, Marco Colombo wrote:
> On Tue, 15 Mar 2005, Michael Fuhr wrote:
>
> >I'll postpone commenting on the rest until we find out how the
> >example programs run on Windows.  If nobody follows up here then
> >maybe I'll wander over to comp.lang.python.
>
> Yeah, there's no point in discussing until we have some real world
> data. I can't compile on windows, so I'll have to wait someone else
> to do that. I'm basing my opinions on Python documentation only.

Paul Moore has run some tests on a Windows server.  I don't see his
message in the archives yet, but when it shows up it should be a
followup to my request for his help (posted in another thread):

http://archives.postgresql.org/pgsql-general/2005-03/msg00717.php

Paul's tests on Windows show the same results as tests on *nix,
viz., that lines in embedded Python need to end in LF, not CRLF.

Thanks to Paul for running the tests.

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

On Tue, Mar 15, 2005 at 03:41:37PM +0000, Ragnar Hafstað wrote:
> On Tue, 2005-03-15 at 07:33 -0600, David wrote:
>
> > [about the line-termination problem in plpython]
>
> > I'd like to insert one note here.  While I'm not particularly familiar
> > with either perl or python, when I encountered the referred-to thread
> > regarding Windows/Unix newline incompatibilities in Python, I ran some
> > tests on my Linux system.  I wrote some very simple scripts in Perl and
> > Python and tested them.  They ran OK when I saved them in native Linux
> > newlines, they ran, but if I saved them in DOS mode (vim), neither the
> > Python nor the Perl scripts would run.
>
> actually, perl scripts with \r\n line endings will run just fine in
> unix/linux.

Yes, I believe Michael Fuhr demonstrated that quite admirably in another
reply to this message.

> what you might have been experiencing, is the fact that the
> unix shell is expecting \n lineendings, and the #! line could have been
> failing to run the perl executable.

Yes, I'm now certain you are correct.  On retesting, I caught something
I didn't when I first ran them - the error message was identical for
each language.  The message was
": bad interpreter: No such file or directory"

If the error had been internal to the language, I'm sure the messages
from each would have been different.

I guess this shows what happens when you shoot off your mouth when you
don't know what you're talking about.

On Tue, Mar 15, 2005 at 02:50:06PM -0700, Michael Fuhr wrote:
> On Tue, Mar 15, 2005 at 03:41:37PM +0000, Ragnar Hafstað wrote:
>
> > actually, perl scripts with \r\n line endings will run just fine in
> > unix/linux.
>
> Indeed, and PL/Perl doesn't care.  I just tested several PLs with
> PostgreSQL 8.0.1 on Solaris 9 and here are the results:
>
> PL/pgSQL   CRLF ok
> PL/Perl    CRLF ok
> PL/Ruby    CRLF ok
> PL/Tcl     CRLF ok
> PL/Python  CRLF fails
> PL/R       CRLF fails
>
> Details:

Thanks for the detailed test you ran.  This is something I should have
done before spouting off.

So, really, AIUI, one of the "selling points" for each of the
above-mentioned languages is their portability.  It would appear to me
that this newline incompatibility ought to be considered a major bug in
python.  (Maybe I'm spouting off half-cocked again, though).

Sorry it took me so long to respond. I've been out for a couple days.

While certain things may be permissible in a language, I think it is also
important to look at the context at which the language is applied and make a
determination if it will practically turn up in relevant code. If the answer
is no, then I think it would be acceptable to add a note that the postgresql
python implementation does not accept implicit newlines and any newlines
must be explicit (the text \r\n)

That being said, the only place I can see wanting to use a newline is for a
situation where you would want to either do a select statement with a
multiline where such as
   results = plpy.execute("""...where textfield= 'multi
                                                        line
                                                        string'...""")
or if you returned the results and wanted to do the same type of thing.
if results[0]["textfield"]=="""multi
                                            line
                                            string""":
...

However, this example assumes that the client writing the functions and the
client inserting the data are always using the same OS, because if a linux
client inserted the data and a windows client had written the function, it
wouldn't receive any results, because postgresql sees the newline as valid
characters. hat being the case I would say that while it is entirely
possible to put multiline quoted text in  python code, it would be
inappropriate in a postgresql environment.

Sim


"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:6000.1110898014@sss.pgh.pa.us...
> "Sim Zacks" <sim@compulab.co.il> writes:
> > I've been looking at the possibility of having a planned CR in the
source
> > code and I don't see a case where it would happen.
>
> Does python actually disallow newlines in string literals?  That is
>
> x = 'foo
> bar'
>
> Whether you think this is good style is not the question --- is it
> allowed by the language?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>