Retrieving binary data yields invalid values for non-binary fields

Issue #22 invalid
Nicolas Martyanoff created an issue

I don't know if it's normal, but here's a test case.

I get the following results:

{{{ a = * b = [1, 2, 3] }}}

It seems the 'a' integer isn't decoded.

Comments (13)

  1. Michael Granger repo owner
    • changed status to open

    Thanks for the report! Just to be clear, you're testing this using the 0.9.0 prerelease version, correct? And judging by your named-param-style call to .connect, using Ruby 1.9.1?

  2. Nicolas Martyanoff reporter

    I'm using the version provided by gem install :) If you think it has been fixed recently, I can try with the last mercurial version. And I'm indeed using Ruby 1.9.1.

  3. Michael Granger repo owner

    I've now replicated this myself with a slightly modified version of your test case; I changed the last bit to:

    db.exec "SELECT a, b FROM test LIMIT 1", [], 1 do |res|
      res.each do |row|
        puts "a = #{row['a'].inspect}"
        puts "a (unpacked) = #{row['a'].unpack('N*').inspect}"
        puts "b = #{row['b'].unpack('N*').inspect}"

    which outputs:

    a = "\x00\x00\x00*"
    a (unpacked) = [42]
    b = [1, 2, 3]

    Interesting. The values in the database are stored in the correct form, too, so it's almost surely something the result object is doing. I've added a spec for this based on your test code, and I'll track down what's going on.

  4. Michael Granger repo owner

    Oh, and I've replicated it with the very latest code under both 1.9.1p243 (2009-07-16 revision 24175) and 1.8.7 (2009-06-12 patchlevel 174) and PostgreSQL 8.4.2.

  5. Nicolas Martyanoff reporter

    I discovered I could fix it by retrieving all data as text, and then using PGconn#unescape_bytea on binary field. It feels a bit hacky, but it works. So at least that's a temporary fix :)

  6. Michael Granger repo owner

    After reading the PostgreSQL docs a bit and looking at the test case some more, I think the pg library is actually doing what it's supposed to. The documentation for PQexecParams says about the resultFormat parameter ()which is what the third argument to PGconn#exec becomes):

    "Specify zero to obtain results in text format, or one to obtain results in binary format. (There is not currently a provision to obtain different result columns in different formats, although that is possible in the underlying protocol.)"

    The workaround you found seems to be the way to fetch a mix of the two formats. I agree that it feels a bit hacky, but it's probably better than any of the alternatives.

  7. Log in to comment