You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I ran into an Unhandled exception: invalid byte sequence for encoding "UTF8": 0x00 (PQ::PQError) when trying to do a query that involves the postgres BYTEA binary type. Any idea why it is trying to encode this as UTF8?
Simplified example code:
array_of_binaries =Array(Bytes?).new
array_of_binaries <<"Hello".to_slice
array_of_binaries <<nil
array_of_binaries <<"world".to_slice
array_of_binaries <<Bytes[0, 255] # <=== This line breaks the query.
my_db.query("SELECT * FROM UNNEST($1::bytea[])", args: [array_of_binaries]) do |rs|
rs.each doputs rs.read(Bytes?)
endend
As shown, I get this backtrace:
Unhandled exception: invalid byte sequence for encoding "UTF8": 0x00 (PQ::PQError)
from lib/pg/src/pq/connection.cr:215:7 in 'handle_error'
from lib/pg/src/pq/connection.cr:198:7 in 'handle_async_frames'
from lib/pg/src/pq/connection.cr:174:7 in 'read'
from lib/pg/src/pq/connection.cr:169:7 in 'read'
from lib/pg/src/pq/connection.cr:447:31 in 'expect_frame'
from lib/pg/src/pq/connection.cr:446:5 in 'expect_frame'
from lib/pg/src/pg/statement.cr:19:5 in 'perform_query'
from lib/db/src/db/statement.cr:93:9 in 'perform_query_with_rescue'
from lib/db/src/db/statement.cr:80:7 in 'query:args'
from lib/db/src/db/pool_statement.cr:29:30 in 'query:args'
from lib/db/src/db/query_methods.cr:46:7 in 'query:args'
from lib/db/src/db/query_methods.cr:61:7 in '__crystal_main'
If I comment out the line with the Bytes[0, 255], then it works fine:
Any ideas on where I can look to fix this? Postgres bytea docs suggest it can handle arbitrary binary data, so I suspect an issue on the crystal-pg side, but am not sure where to look. Thank you for any pointers.
The text was updated successfully, but these errors were encountered:
So I think the problem is that in this example it's not just bytea types but arrays of bytea, and before arrays get sent up to postgres they get encoded as strings
defself.encode_array(array)
String.build(array.size +2) do |io|
encode_array(io, array)
endend
and postgres doesn't like 0x00 in a string. It might be possible (but I haven't thought it through at all yet, so I don't know) to special case arrays of bytea and send them with the binary protocol instead of text.
Thank you so much @will! That was a very helpful pointer.
As a result, I found the following workaround by assuming that crystal-pg sends a string-encoded binary, and so I can pre-encode my data using the bytea hex format.
This worked for me:
array_of_binaries =Array(Bytes?).new
array_of_binaries <<"Hello".to_slice
array_of_binaries <<nil
array_of_binaries <<"world".to_slice
array_of_binaries <<Bytes[0, 255] # <=== This line breaks the query.
array_of_pg_hex_strings : Array(String?) = array_of_binaries.map do |bytes|
nextnilif bytes.nil?
String.build do |str|
str <<"\\x"
bytes.each do |byte|
str << sprintf("%02x", byte)
endendend
my_db.query("SELECT * FROM UNNEST($1::bytea[])", args: [array_of_pg_hex_strings]) do |rs|
rs.each doputs rs.read(Bytes?)
endend
I ran into an
Unhandled exception: invalid byte sequence for encoding "UTF8": 0x00 (PQ::PQError)
when trying to do a query that involves the postgresBYTEA
binary type. Any idea why it is trying to encode this as UTF8?Simplified example code:
As shown, I get this backtrace:
If I comment out the line with the
Bytes[0, 255]
, then it works fine:Any ideas on where I can look to fix this? Postgres bytea docs suggest it can handle arbitrary binary data, so I suspect an issue on the crystal-pg side, but am not sure where to look. Thank you for any pointers.
The text was updated successfully, but these errors were encountered: