Different results with 2 Postgres instances

Question

I run a Postgres 9.2.2 server locally using the Postgres App with ssmode=disable. All the tables are created like they should be and SELECT queries work as expected.

When I create a Postgres instance on Heroku (the server is 9.1.6 according to psql, ssmode=require), everything works fine, except that a query returns no results, where the local postgres instance does.

The specific query is

SELECT * FROM "captcha" WHERE "cid" = $1 LIMIT $2

and cid/$1 is of type character varying(20).

When connecting to the remote instance via psql and executing the query manually, the rows were returned as expected. I just don't understand what could possibly cause that different behavior.

The primary suspect would be the driver that I use bmizerany/pq, but could there be other error sources for this behavior?

UPDATE:

I tried it with a plain query, same result: No rows in result set

SELECT * FROM "captcha" WHERE "cid" = 'JQRPm6qRpYukXCiPUpHZ' LIMIT 1

UPDATE 2:

It has something todo with the bytea field, the following snippet illustrates the problem when executing on those 2 PG versions https://gist.github.com/eaigner/5004468

Answer 1

The problem is, that Heroku uses a non-default bytea_output. So a workaround to deal with this was

SET bytea_output = 'hex';

before running the query