Re: plpython function problem workaround - Mailing list pgsql-general

On Tue, 29 Mar 2005, Sim Zacks wrote:

> The only ?issue? that I have found with it is similar to an issue I
> posted about multiline in general, which does not seem to be
> considered a bug.

I've posted similar concerns in the past. The whole point is that
there are two possible approaches:
1) treat text as binary - as we do now;
2) do on the wire conversion - like FTP ASCII mode.

Both have disadvantages, and both lead to unexpected results.

As I wrote before, 2) is more problematic. You'll have to reject
any file with a bare \n from a Windows, otherwise you won't be able
to process it correclty.

I think if you do:

insert into test (sometext) values ('Line one\nLine two\r\n');
-- with the literal chars, not the escape sequences

you're expecting exaclty the same on output. If the server
converts it in the Unix form:

'Line one\nLine two\n'

for storing and the converts back to the Windows form, when you do:

select sometext from test; -- from a Windows client

you get:

Line one\r\nLine two\r\n

which is not the same you entered.


I doubt FTP ASCII mode handles this correctly.


As for the examples you made (the python functions), it's a problem
with python string literals (just don't use them). Let's try this:

---------- CUT HERE 8< ------------
#!/usr/bin/env python

import pgdb

db = pgdb.connect()
curs = db.cursor()

# this is only to emulate PGAdmin under Windows (I don't have it)
# (I ran the script with these uncommented on Linux)
#q = "create temp table test1(f1 varchar(50));"
#curs.execute(q)
#q = "insert into test1 values('this is a multi line string\r\nline2\r\nline3\r\n');"
#curs.execute(q)

#### real test

# embedded in string literal
q1 = """select count(f1) from test1 where f1 = 'this is a multi line string
line2
line3
'"""

# escapes (expanded by python)
q2 = "select count(f1) from test1 where f1 = 'this is a multi line string\r\nline2\r\nline3\r\n'"

# escapes (expanded by PostgreSQL)
q3 = r"select count(f1) from test1 where f1 = 'this is a multi line string\r\nline2\r\nline3\r\n'"
curs.execute(q3)

# stating the obvious
print "Comparisons:"
print "%-10s%-10s%-10s" % ("q1 == q2", "q1 == q3", "q2 == q3")
print "%-10s%-10s%-10s" % (q1 == q2,    q1 == q3,   q2 == q3)

print "\nRunning tests..."

curs.execute(q1)
print "Test 1 (string literal):", curs.fetchone()[0]

curs.execute(q2)
print "Test 2 (Python escapes):", curs.fetchone()[0]

curs.execute(q3)
print "Test 3 (PG escapes):", curs.fetchone()[0]

# in case someone wonders, let's try using query parameters
astring = """this is a multi line string
line2
line3
"""

q = "select count(f1) from test1 where f1 = %(mystr)s"
curs.execute(q, { "mystr": astring })
print "Test 4 (parameters):", curs.fetchone()[0]
---------- >8 CUT HERE ------------

This is the output (on Linux):

Comparisons:
q1 == q2  q1 == q3  q2 == q3
False     False     False

Running tests...
Test 1 (string literal): 0
Test 2 (Python escapes): 1
Test 3 (PG escapes): 1
Test 4 (parameters): 0

Which is consistent with your examples, that is, it works _only_
with explicit escapes (either at Python level or at PostgreSQL level).

If client-side python works this way, why are you expecting server-side
python to work differently?

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