Psycopg psycopg

Comments and changes to this ticket

• 

  Federico Di Gregorio September 8th, 2011 @ 01:12 PM

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

  • signature.asc 263 Bytes
• 

  Daniele Varrazzo September 8th, 2011 @ 01:46 PM

  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.

• You flagged this item as spam.
  

  Jonathan Slenders September 9th, 2011 @ 10:09 AM

  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!

• 

  Daniele Varrazzo September 9th, 2011 @ 10:31 AM

  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.

• You flagged this item as spam.
  

  Jonathan Slenders September 9th, 2011 @ 10:53 AM

  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.

• 

  Daniele Varrazzo September 9th, 2011 @ 10:54 AM

  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.

  • postgis-adapter.patch 1.4 KB
• 

  Daniele Varrazzo September 9th, 2011 @ 11:01 AM

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

• You flagged this item as spam.
  

  Jonathan Slenders September 9th, 2011 @ 11:08 AM

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

• You flagged this item as spam.
  

  Jonathan Slenders September 9th, 2011 @ 11:34 AM

  I can confirm this patach to be working.

• 

  Daniele Varrazzo September 9th, 2011 @ 11:51 AM

  • State changed from “new” to “resolved”

  Brilliant, thanks for testing.

  I've open ticket #16778 on Django: https://code.djangoproject.com/ticket/16778

  Closing this bug as psycopg is doing ok here. Bye!