bytea escape type 'hex' not being recognized

[ged]

Original report by Don Park (Bitbucket: donpdonp, GitHub: donpdonp).

---

#!ruby
puts conn.exec("select * from open_id_associations limit 1")[0]["secret"]
\x65958bdb611a6bce10efbd6ddddd8c1c82e8ff36

Then from psql:

select secret from open_id_associations limit 1;
                   secret                   
--------------------------------------------
 \x65958bdb611a6bce10efbd6ddddd8c1c82e8ff36
(1 row)

The output of psql I would expect to show the escaped version, but I expect the ruby call to return a binary string. Is that a correct assumption?

[ged]

Original comment by Michael Granger (Bitbucket: ged, GitHub: ged).

---

No, the driver doesn't presume to know how to convert anything for you -- that's left to higher-level abstractions like Sequel, ActiveRecord, etc. The library returns pretty much exactly what the libpq library returns, with the notable exception of associating the Encoding (where possible) under Ruby 1.9.

However, there are two methods on the PGconn object to help you with doing the conversion yourself: [[http://deveiate.org/code/pg/PGconn.html#escape_bytea-instance_method|PGconn#escape_bytea]] and [[http://deveiate.org/code/pg/PGconn.html#unescape_bytea-instance_method|PGconn#unescape_bytea]].

There's more details about the design of the client library, including the topic of data type conversion, on [[http://wiki.postgresql.org/wiki/Driver_development|the PostreSQL wiki]].

Does that answer your questions?

[ged]

Original comment by Don Park (Bitbucket: donpdonp, GitHub: donpdonp).

---

The first guideline in the wiki is "Avoid Magic" or the principle of the least surprise. I believe that if I have a ruby string and store it with pg, the retrieved string should be identical. Do you agree?

if so, thats not happening. A rails app is storing an authentication string and getting a different string back so the app is breaking when postgres is using the 'new' binary escape mode (introduced in version 9), but works fine when the postgres 9 daemon is using the old escape mode.

[ged]

Original comment by Michael Granger (Bitbucket: ged, GitHub: ged).

---

My point was that the pg' library returns only what libpq' returns without modifications other than associating the encoding. This, to me, is avoiding "magic". The driver returns exactly what the client library returns from the database, without assuming that you'd want it converted to something else.

As with many cases when people cite the Principle of Least Surprise, it's dependent on whose surprise you're trying to avoid. In this case, I prefer not to surprise people who have come to expect that `pg' will behave identically to libpq, and who have possibly built libraries around these behaviors. It's the job of higher-level abstractions to convert the notion of a "binary string" into whatever Ruby construct corresponds to its representation by PostgreSQL.

I apologize if this makes what you're trying to do difficult. As I mentioned above, there are methods to help with escaping and unescaping bytea column values, but I'm unwilling to introduce magical conversions of any column values from a PGresult.