英文:
Different results with 2 Postgres instances
问题
我在本地使用Postgres App运行一个Postgres 9.2.2服务器，ssmode=disable。所有的表都按照预期创建，并且SELECT查询也正常工作。
当我在Heroku上创建一个Postgres实例时（根据psql，服务器版本为9.1.6，ssmode=require），除了查询不返回结果外，其他都正常工作。
具体的查询是
<!-- language: sql -->
SELECT * FROM "captcha" WHERE "cid" = $1 LIMIT $2
cid/$1的类型是character varying(20)。
当通过psql连接到远程实例并手动执行查询时，行按预期返回。我只是不明白可能导致这种不同行为的原因。
主要嫌疑人应该是我使用的驱动程序bmizerany/pq，但还可能有其他导致这种行为的错误来源吗？
更新：
我尝试了一个简单的查询，结果相同：结果集中没有行
SELECT * FROM "captcha" WHERE "cid" = 'JQRPm6qRpYukXCiPUpHZ' LIMIT 1
更新2：
这与bytea字段有关，以下代码片段说明了在这两个PG版本上执行时的问题https://gist.github.com/eaigner/5004468
英文:
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
<!-- language: sql -->
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
答案1
得分: 2
问题是，Heroku使用了非默认的bytea_output。因此，解决这个问题的方法是在运行查询之前设置bytea_output为hex。
英文:
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
答案2
得分: 1
请尝试使用PREPARE/EXECUTE。pq使用的是“扩展”查询协议，它使用绑定而不是“简单”协议，后者只是文本，因此这样会更好地模拟交互。
请提交一个错误报告。
英文:
Try using PREPARE/EXECUTE. pq uses the 'extended' query protocol which uses bind rather than the 'simple' protocol that is just text, so that'll model the interaction better.
Please file a bug.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论