This project is archived and is in readonly mode.

#48 ✓ resolved
Psycopg website

Interface error: decoding empty BYTEA

Reported by Psycopg website | March 16th, 2011 @ 11:22 AM

Submitted by: Adam Petrovic

Hi,

I'm getting InterfaceError when selecting BYTEA data from custom function. Whole error is:
InterfaceError: can't receive bytea data from server >= 9.0 with the current libpq client library: please update the libpq to at least 9.0 or set bytea_output to 'escape' in the server config or with a query

All rows from "SELECT decode_fn(col).bytea_col" return empty "\x" data, but I can't reproduce it on simple "SELECT bytea_col".
libpq-9 installed a psycopg2 correctly linked.

Error is thrown in psycopg/typecast_binary.c line 170. I'm not C programmer, but is that IF right for empty data?

Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo March 16th, 2011 @ 12:05 PM

    Hello,

    the check should be correct: an encoded empty string correctly decoded should have input s="\x" and output str="", so the check str[0] == 'x' should fail and the error shouldn't be raised.

    This check also proves an empty bytea only represented with \x is correctly handled:

    In [1]: import psycopg2
    
    In [2]: cnn = psycopg2.connect('')
    
    In [3]: cur = cnn.cursor()
    
    In [4]: cur.execute(r"select E'\\x'::bytea")
    
    In [5]: str(cur.fetchone()[0])
    Out[5]: ''
    

    Are you sure the libpq psycopg is linked to is the correct version? Please take a look using:

    ldd path/to/psycopg2/_psycopg.so | grep libpq
    

    What is decode_fn? Can you provide a self-contained failing test case?

    I think we really need to have our own hex format parser instead of relying on the libpq one anyway...

  • Adam Petrovic

    Adam Petrovic March 16th, 2011 @ 02:06 PM

    Yes, linked libpq is ok

    ldd /usr/lib/python2.4/site-packages/psycopg2/_psycopg.so
    libpq.so.5 => /usr/pgsql-9.0/lib/libpq.so.5 (0xb7824000)
    

    Unfortunately I can't provide any complete test case - decode_fn is custom function written in C that was used before with PostgreSQL 8.4 and psycopg2 2.0.

    However I found another test case when SELECT of BYTEA fails:

    In [1]: import psycopg2
    
    In [2]: conn = psycopg2.connect("")
    
    In [3]: cur = conn.cursor()
    
    In [4]: cur.execute("SELECT ''::BYTEA")
    
    In [5]: cur.fetchall()
    ---------------------------------------------------------------------------
    InterfaceError                            Traceback (most recent call last)
    
    /root/<ipython console> 
    
    InterfaceError: can't receive bytea data from server >= 9.0 with the current libpq client library: please update the libpq to at least 9.0 or set bytea_output to 'escape' in the server config or with a query
    
    In [6]: cur.execute(r"SELECT E'\\x'::bytea")
    
    In [7]: cur.fetchall()
    ---------------------------------------------------------------------------
    InterfaceError                            Traceback (most recent call last)
    
    /root/<ipython console> 
    
    InterfaceError: can't receive bytea data from server >= 9.0 with the current libpq client library: please update the libpq to at least 9.0 or set bytea_output to 'escape' in the server config or with a query
    

    After closing ipython and repeating those commands again, everything was OK.
    My colleague looked at PQunescapeBytea function and found out that when unescaping empty data, it allocates 1 byte and returns it unchanged - so there actually could be some bad data from last allocation. Could this be the problem?

  • Daniele Varrazzo

    Daniele Varrazzo March 16th, 2011 @ 03:04 PM

    • Tag changed from rel-2.4 to bytea, rel-2.4

    I don't think the libpq can retain data across different invocations of a python process: its state should be cleaned when the process terminates.

    It still smells like a different libpq version getting linked somehow (I see your libpq is in a non-standard path). Your test passes regularly here (using the system libpq from backported debian 9.0.3 package on ubuntu). Do you have a libpq in /usr/lib/ too?

    In [4]: cur.execute("SELECT ''::BYTEA")
    
    In [5]: cur.fetchall()
    Out[5]: [(<read-only buffer for 0x7fb21d54c390, size 0, offset 0 at 0x134bd30>,)]
    
    In [6]: cur.execute(r"SELECT E'\\x'::bytea")
    
    In [7]: cur.fetchall()
    Out[7]: [(<read-only buffer for 0x7fb21d54c330, size 0, offset 0 at 0x134bef0>,)]
    
  • Adam Petrovic

    Adam Petrovic March 16th, 2011 @ 04:28 PM

    We're using PGRPMS on CentOS - so wen can run pg 8.4 and 9.0. But on this server there's only v9.0.
    Like I said - once it raised those errors and for the second time, everything was ok. So we tried to apply this patch

    diff -Naur psycopg2-2.4/psycopg/typecast_binary.c psycopg2-2.4-fix/psycopg/typecast_binary.c
    --- psycopg2-2.4/psycopg/typecast_binary.c  2011-02-24 11:38:28.000000000 +0100
    +++ psycopg2-2.4-fix/psycopg/typecast_binary.c  2011-03-16 16:44:25.701055330 +0100
    @@ -168,7 +168,7 @@
    
    /* Check the escaping was successful */
    if (s[0] == '\\' && s[1] == 'x' /* input encoded in hex format */
    - && str[0] == 'x' /* output resulted in an 'x' */
    + && len > 0 && str[0] == 'x' /* output resulted in an 'x' */
    && s[2] != '7' && s[3] != '8') /* input wasn't really an x (0x78) */
    {
    PyErr_SetString(InterfaceError,
    

    and it seem ok, no errors - it skips escape check if PQunescapeBytea returns empty string (len == 0, but str is 1 byte long with uninitialized data).
    Please consider this as a patch and if it could cause any problems. Thanks

  • Daniele Varrazzo

    Daniele Varrazzo March 16th, 2011 @ 04:36 PM

    Thank you for the patch, but I think a safer approach would be to ditch entirely the libpq unescape and deal with the hex format ourselves - it is an easy to parse format after all.

    I should have done it for the 2.4 release: it is my only regret for an otherwise perfect release. Will fix it in 2.4.1: will try to write the patch this weekend.

  • Daniele Varrazzo

    Daniele Varrazzo March 26th, 2011 @ 01:22 PM

    • State changed from “new” to “resolved”

    Just pushed our own bytea hex parser, there is no more need of a specific libpq version on the client.

    I've added a specific test for '\x' to be parsed correctly. Feel free to make your own test: source available on my repos (https://github.com/dvarrazzo/psycopg - 'devel' branch).

  • Daniele Varrazzo

    Daniele Varrazzo March 28th, 2011 @ 01:13 PM

    By the way, your analysis was correct: the check str[0] is wrong when len == 0.

    The libpq allocates at least one byte to avoid a non portable malloc(0). I was in the mindset of a null-terminated string, but str[0] was reading an uninitialized block of memory.

    Your patch would have been to be applied, but now it's superseded by having our own parser. Thank you very much anyway.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

WARNING: the informations in this tracker are archived. Please submit new tickets or comments to the new tracker.


Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins

Tags

Pages