PQescapeByteaConn does not escape backslashes on postgres 9.1 server

Can you provide us with a minimalist python program that expose the problem?

The difference in the output of PQescapeByteaConn is due to the standard_conforming_string setting: it is on by default in PG 9.1.

The difference is taken into account by binary_escape, which adds an E when the "double backslash" are used. The result should be thus:

# on pg 9.0 with SCS = off
print cur.mogrify("select %s", [psycopg2.Binary(chr(0))])
select E'\\x00'::bytea

# on pg 9.0 with SCS = on
print cur.mogrify("select %s", [psycopg2.Binary(chr(0))])
select '\x00'::bytea

by default, pg 9.1 should send '\x00'::bytea or maybe '\000'::bytea (probably depending on the version on the client libpq). The E'\000' (the E with single backslash) is dodgy.

Can you send the version of the libpq you are using and the output for

show standard_conforming_strings;
show bytea_output;

in the 9.1 server? Also the output of cur.mogrify("select %s", [psycopg2.Binary(chr(0))]) could be useful to see if I am on the right track. Thanks!

Meanwhile I will try to reproduce the bug with the latest 9.1 and a libpq 8.4 on the client.

Indeed, somehow, I got the E with single backslashes...

. >>> import psycopg2

. >>> conn_string = "local_postgres8.4 database... ..."

. >>> conn = psycopg2.connect(conn_string)

. >>> cursor=conn.cursor()

. >>> cursor.mogrify("select %s", [psycopg2.Binary(chr(0))])

"select E'\\000'::bytea"

. >>> conn_string = "remote postgres 9.1 database... ..."

. >>> conn = psycopg2.connect(conn_string)

. >>> cursor=conn.cursor()

. >>> cursor.mogrify("select %s", [psycopg2.Binary(chr(0))])

"select '\x00'::bytea"

For the 9.1 database, I have:
standard_conforming_stings: on
bytea_output: hex

For the 8.4 server, I have:
standard_conforming_stings: off
bytea_output: (does not exist???)

Thanks!

From the samples you posted, it looks psycopg is behaving as expected. What is in between your django program and psycopg?

Are you using django's PostGIS adapter? This looks a bug to me: https://code.djangoproject.com/browser/django/trunk/django/contrib/...

That is not the way to write the adapter: I shall provide them a patch.

Thanks a lot, Daniele! I am indeed using the PostGIS adaptor, so that explains the issue.
Hopefully, they will merge your patch asap into their trunk.

Try with this patch. Please try with postgres servers using both parameters for standard_conforming_strings. If we are happy with it we can pass it to the django guys. Thanks.

(please see for update to the above comment: I've submitted it before finishing)

Thanks, that was quick. I'll test it right now.

I can confirm this patach to be working.