Postgres与Heroku上的Sinatra / Haml / DataMapper错误 [英] Postgres error with Sinatra/Haml/DataMapper on Heroku

问题描述

我试图将简单的Sinatra应用移到Heroku。使用Taps迁移Ruby应用程序代码和现有MySQL数据库的过程非常顺利，但是我收到以下Postgres错误：

PostgresError - 错误：运算符不存在：text = integer
LINE 1：... d_at，post_idFROMcommentsWHERE（post_idIN（4，17，...
^
提示：没有操作符匹配给定的名称和参数类型。您可能需要添加显式类型转换。

很明显，这个问题与查询中的类型不匹配有关，但是这是由DataMapper ORM在Haml模板中以非常高的抽象级别发布的，所以我不知道如何去控制它。 ..

具体来说，这似乎是从我的Haml模板中调用 p.comments 其中 p 表示给定的帖子。

Datamapper模型与fo llows：

  class Post 
 property：id，Serial 
 ... 
 has n ，：注释
结束
 
类注释
属性：id，系列
 ... 
 belongs_to：后
结束
  

这对使用MySQL的本地和当前托管环境很有效，但Postgres显然更为严格。 b
$ b

必须有数百个Datamapper& Haml应用程序运行在Postgres DB上，这种模型关系是超常规的，所以希望有人看到（并确定如何解决）这一点。感谢！

更新：请参阅

Heroku：Postgres在从MySQL迁移数据库后输入运算符错误。 class =h2_lin>解决方案

它看起来像post_id是TEXT类型而不是INTEGER。要解决这个问题，你必须改变数据类型。这在8.3版本中已经改变，旧版本有一个隐含的强制转换。你可以告诉PostgreSQL的这样做的：

  CREATE FUNCTION pg_catalog.text（整数）传回文字严格IMMUTABLE语言SQL ASSELECT textin （int4out（$ 1））;'; 
 CREATE CAST（整数AS文本）WITH FUNCTION pg_catalog.text（integer）AS IMPLICIT; 
 
 CREATE FUNCTION pg_catalog.text（smallint）返回文本STRICT IMMUTABLE语言SQL AS'SELECT textin（int2out（$ 1））;'; 
 CREATE CAST（smallint AS文本）WITH FUNCTION pg_catalog.text（smallint）AS IMPLICIT; 
 
 CREATE FUNCTION pg_catalog.text（bigint）返回文本STRICT IMMUTABLE语言SQL AS'SELECT textin（int8out（$ 1））;'; 
 CREATE CAST（bigint AS文本）WITH FUNCTION pg_catalog.text（bigint）AS IMPLICIT; 
  

另见 http://wiki.postgresql.org/wiki/Image:Pg83-implicit-casts.sql

I'm trying to move a simple Sinatra app over to Heroku. Migration of the Ruby app code and existing MySQL database using Taps went smoothly, but I'm getting the following Postgres error:

PostgresError - ERROR: operator does not exist: text = integer LINE 1: ...d_at", "post_id" FROM "comments" WHERE ("post_id" IN (4, 17,... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

It's evident that the problem is related to a type mismatch in the query, but this is being issued from a Haml template by the DataMapper ORM at a very high level of abstraction, so I'm not sure how I'd go about controlling this...

Specifically, this seems to be throwing up on a call of p.comments from my Haml template, where p represents a given post.

The Datamapper models are related as follows:

class Post
    property :id, Serial
    ...
    has n, :comments
end

class Comment
    property :id, Serial
    ...
    belongs_to :post
end

This works fine on my local and current hosted environment using MySQL, but Postgres is clearly more strict.

There must be hundreds of Datamapper & Haml apps running on Postgres DBs, and this model relationship is super-conventional, so hopefully someone has seen (and determined how to fix) this. Thanks!

UPDATE: See Heroku: Postgres type operator error after migrating DB from MySQL for resolution.

解决方案

It looks like post_id is of type TEXT instead of INTEGER. To fix this, you have to change the datatype. This has been changed in version 8.3, older version have an implicit cast. You can tell PostgreSQL to do so:

CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;

CREATE FUNCTION pg_catalog.text(smallint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int2out($1));';
CREATE CAST (smallint AS text) WITH FUNCTION pg_catalog.text(smallint) AS IMPLICIT;

CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;

See also http://wiki.postgresql.org/wiki/Image:Pg83-implicit-casts.sql

这篇关于Postgres与Heroku上的Sinatra / Haml / DataMapper错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！