代码之家 › 专栏 › 技术社区 › Tim Sullivan

ActiveRecord::StatementInvalid,PG::UndefinedTable错误,但生成的SQL有效

postgresql ruby-on-rails

Tim Sullivan · 技术社区 · 6 年前

整个设置的一个奇怪之处是salesforce模式中有两个表,而tablename和主键不是标准的。基本结构如下:

class Contact
  self.table_name =  'salesforce.contact'
  self.primary_key = 'sfid'

  has_many :content_accesses
  has_many :inventories, through: :content_accesses # I've tried inventory and inventorys, just to ensure it's not Rails magic
end


class ContentAccess
  belongs_to :inventory
  belongs_to :contact
end


class Inventory
  self.table_name =  'salesforce.inventory__c'
  self.primary_key = 'sfid'

  has_many :content_accesses, foreign_key: 'inventory_id'
end

c = Contact.first
c.content_accesses # works, gives the related items

c.content_accesses.first.inventory # works, gives the related Inventory item

错误:

c.inventories # Gives:

# ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  relation "content_accesses" does not exist)
# LINE 1: ..._c".* FROM "salesforce"."inventory__c" INNER JOIN "content_a...
#                                                          ^
# : SELECT  "salesforce"."inventory__c".* FROM "salesforce"."inventory__c" INNER JOIN "content_accesses" ON "salesforce"."inventory__c"."sfid" = "content_accesses"."inventory_id" WHERE "content_accesses"."contact_id" = $1 LIMIT $2

Postico 不过,效果不错。¤

编辑以添加:

这只会让我觉得这个问题更奇怪。:(

表的DDL:

CREATE TABLE salesforce.inventory__c (
    createddate timestamp without time zone,
    isdeleted boolean,
    name character varying(80),
    systemmodstamp timestamp without time zone,
    inventory_unique_name__c character varying(255),
    sfid character varying(18),
    id integer DEFAULT nextval('salesforce.inventory__c_id_seq'::regclass) PRIMARY KEY,
    _hc_lastop character varying(32),
    _hc_err text
);

CREATE UNIQUE INDEX inventory__c_pkey ON salesforce.inventory__c(id int4_ops);
CREATE INDEX hc_idx_inventory__c_systemmodstamp ON salesforce.inventory__c(systemmodstamp timestamp_ops);
CREATE UNIQUE INDEX hcu_idx_inventory__c_sfid ON salesforce.inventory__c(sfid text_ops);


CREATE TABLE salesforce.contact (
    lastname character varying(80),
    mailingpostalcode character varying(20),
    accountid character varying(18),
    assistantname character varying(40),
    name character varying(121),
    mobilephone character varying(40),
    birthdate date,
    phone character varying(40),
    mailingstreet character varying(255),
    isdeleted boolean,
    assistantphone character varying(40),
    systemmodstamp timestamp without time zone,
    mailingstatecode character varying(10),
    createddate timestamp without time zone,
    mailingcity character varying(40),
    salutation character varying(40),
    title character varying(128),
    mailingcountrycode character varying(10),
    firstname character varying(40),
    email character varying(80),
    sfid character varying(18),
    id integer DEFAULT nextval('salesforce.contact_id_seq'::regclass) PRIMARY KEY,
    _hc_lastop character varying(32),
    _hc_err text
);

CREATE UNIQUE INDEX contact_pkey ON salesforce.contact(id int4_ops);
CREATE INDEX hc_idx_contact_systemmodstamp ON salesforce.contact(systemmodstamp timestamp_ops);
CREATE UNIQUE INDEX hcu_idx_contact_sfid ON salesforce.contact(sfid text_ops);

CREATE TABLE content_accesses (
    id BIGSERIAL PRIMARY KEY,
    inventory_id character varying(20),
    contact_id character varying(20),
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

CREATE UNIQUE INDEX content_accesses_pkey ON content_accesses(id int8_ops);

编辑2: 作为调试的一部分,我尝试在控制台中运行生成的查询:

如果我使用 ActiveRecord::Base.connection.execute 查询有效。
Contact.connection.execute 同样的错误。

感觉Rails没有搞清楚,但是我不知道在哪里,为什么,什么。

编辑3: 根据要求,框架跟踪:

activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:669:in `prepare'
activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:669:in `block in prepare_statement'
/Users/timsullivan/.rvm/rubies/ruby-2.5.1/lib/ruby/2.5.0/monitor.rb:226:in `mon_synchronize'
activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:664:in `prepare_statement'
activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:609:in `exec_cache'
activerecord (5.2.0) lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `execute_and_clear'
activerecord (5.2.0) lib/active_record/connection_adapters/postgresql/database_statements.rb:81:in `exec_query'
activerecord (5.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:469:in `select_prepared'
activerecord (5.2.0) lib/active_record/connection_adapters/abstract/database_statements.rb:55:in `select_all'
activerecord (5.2.0) lib/active_record/connection_adapters/abstract/query_cache.rb:101:in `select_all'
activerecord (5.2.0) lib/active_record/querying.rb:41:in `find_by_sql'
activerecord (5.2.0) lib/active_record/relation.rb:554:in `block in exec_queries'
activerecord (5.2.0) lib/active_record/relation.rb:578:in `skip_query_cache_if_necessary'
activerecord (5.2.0) lib/active_record/relation.rb:542:in `exec_queries'
activerecord (5.2.0) lib/active_record/association_relation.rb:34:in `exec_queries'
activerecord (5.2.0) lib/active_record/relation.rb:414:in `load'
activerecord (5.2.0) lib/active_record/relation.rb:200:in `records'
activerecord (5.2.0) lib/active_record/relation.rb:195:in `to_ary'
activerecord (5.2.0) lib/active_record/relation/finder_methods.rb:530:in `find_nth_with_limit'
activerecord (5.2.0) lib/active_record/associations/collection_proxy.rb:1136:in `find_nth_with_limit'
activerecord (5.2.0) lib/active_record/relation/finder_methods.rb:515:in `find_nth'
activerecord (5.2.0) lib/active_record/relation/finder_methods.rb:125:in `first'
actionview (5.2.0) lib/action_view/template.rb:159:in `block in render'
activesupport (5.2.0) lib/active_support/notifications.rb:170:in `instrument'
actionview (5.2.0) lib/action_view/template.rb:354:in `instrument_render_template'
actionview (5.2.0) lib/action_view/template.rb:157:in `render'
actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:54:in `block (2 levels) in render_template'
actionview (5.2.0) lib/action_view/renderer/abstract_renderer.rb:44:in `block in instrument'
activesupport (5.2.0) lib/active_support/notifications.rb:168:in `block in instrument'
activesupport (5.2.0) lib/active_support/notifications/instrumenter.rb:23:in `instrument'
activesupport (5.2.0) lib/active_support/notifications.rb:168:in `instrument'
actionview (5.2.0) lib/action_view/renderer/abstract_renderer.rb:43:in `instrument'
actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:53:in `block in render_template'
actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:61:in `render_with_layout'
actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:52:in `render_template'
actionview (5.2.0) lib/action_view/renderer/template_renderer.rb:16:in `render'
actionview (5.2.0) lib/action_view/renderer/renderer.rb:44:in `render_template'
actionview (5.2.0) lib/action_view/renderer/renderer.rb:25:in `render'
actionview (5.2.0) lib/action_view/rendering.rb:103:in `_render_template'
actionpack (5.2.0) lib/action_controller/metal/streaming.rb:219:in `_render_template'
actionview (5.2.0) lib/action_view/rendering.rb:84:in `render_to_body'
actionpack (5.2.0) lib/action_controller/metal/rendering.rb:52:in `render_to_body'
actionpack (5.2.0) lib/action_controller/metal/renderers.rb:142:in `render_to_body'
actionpack (5.2.0) lib/abstract_controller/rendering.rb:25:in `render'
actionpack (5.2.0) lib/action_controller/metal/rendering.rb:36:in `render'
actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:46:in `block (2 levels) in render'
activesupport (5.2.0) lib/active_support/core_ext/benchmark.rb:14:in `block in ms'
/Users/timsullivan/.rvm/rubies/ruby-2.5.1/lib/ruby/2.5.0/benchmark.rb:308:in `realtime'
activesupport (5.2.0) lib/active_support/core_ext/benchmark.rb:14:in `ms'
actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:46:in `block in render'
actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:87:in `cleanup_view_runtime'
activerecord (5.2.0) lib/active_record/railties/controller_runtime.rb:31:in `cleanup_view_runtime'
actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:45:in `render'
actionpack (5.2.0) lib/action_controller/metal/implicit_render.rb:35:in `default_render'
actionpack (5.2.0) lib/action_controller/metal/basic_implicit_render.rb:6:in `block in send_action'
actionpack (5.2.0) lib/action_controller/metal/basic_implicit_render.rb:6:in `tap'
actionpack (5.2.0) lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action'
actionpack (5.2.0) lib/abstract_controller/base.rb:194:in `process_action'
actionpack (5.2.0) lib/action_controller/metal/rendering.rb:30:in `process_action'
actionpack (5.2.0) lib/abstract_controller/callbacks.rb:42:in `block in process_action'
activesupport (5.2.0) lib/active_support/callbacks.rb:132:in `run_callbacks'
actionpack (5.2.0) lib/abstract_controller/callbacks.rb:41:in `process_action'
actionpack (5.2.0) lib/action_controller/metal/rescue.rb:22:in `process_action'
actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:34:in `block in process_action'
activesupport (5.2.0) lib/active_support/notifications.rb:168:in `block in instrument'
activesupport (5.2.0) lib/active_support/notifications/instrumenter.rb:23:in `instrument'
activesupport (5.2.0) lib/active_support/notifications.rb:168:in `instrument'
actionpack (5.2.0) lib/action_controller/metal/instrumentation.rb:32:in `process_action'
actionpack (5.2.0) lib/action_controller/metal/params_wrapper.rb:256:in `process_action'
activerecord (5.2.0) lib/active_record/railties/controller_runtime.rb:24:in `process_action'
actionpack (5.2.0) lib/abstract_controller/base.rb:134:in `process'
actionview (5.2.0) lib/action_view/rendering.rb:32:in `process'
actionpack (5.2.0) lib/action_controller/metal.rb:191:in `dispatch'
actionpack (5.2.0) lib/action_controller/metal.rb:252:in `dispatch'
actionpack (5.2.0) lib/action_dispatch/routing/route_set.rb:52:in `dispatch'
actionpack (5.2.0) lib/action_dispatch/routing/route_set.rb:34:in `serve'
actionpack (5.2.0) lib/action_dispatch/journey/router.rb:52:in `block in serve'
actionpack (5.2.0) lib/action_dispatch/journey/router.rb:35:in `each'
actionpack (5.2.0) lib/action_dispatch/journey/router.rb:35:in `serve'
actionpack (5.2.0) lib/action_dispatch/routing/route_set.rb:840:in `call'
warden (1.2.7) lib/warden/manager.rb:36:in `block in call'
warden (1.2.7) lib/warden/manager.rb:35:in `catch'
warden (1.2.7) lib/warden/manager.rb:35:in `call'
rack (2.0.5) lib/rack/tempfile_reaper.rb:15:in `call'
rack (2.0.5) lib/rack/etag.rb:25:in `call'
rack (2.0.5) lib/rack/conditional_get.rb:25:in `call'
rack (2.0.5) lib/rack/head.rb:12:in `call'
actionpack (5.2.0) lib/action_dispatch/http/content_security_policy.rb:18:in `call'
rack (2.0.5) lib/rack/session/abstract/id.rb:232:in `context'
rack (2.0.5) lib/rack/session/abstract/id.rb:226:in `call'
actionpack (5.2.0) lib/action_dispatch/middleware/cookies.rb:670:in `call'
activerecord (5.2.0) lib/active_record/migration.rb:559:in `call'
actionpack (5.2.0) lib/action_dispatch/middleware/callbacks.rb:28:in `block in call'
activesupport (5.2.0) lib/active_support/callbacks.rb:98:in `run_callbacks'
actionpack (5.2.0) lib/action_dispatch/middleware/callbacks.rb:26:in `call'
actionpack (5.2.0) lib/action_dispatch/middleware/executor.rb:14:in `call'
airbrake (7.2.1) lib/airbrake/rack/middleware.rb:52:in `call'
actionpack (5.2.0) lib/action_dispatch/middleware/debug_exceptions.rb:61:in `call'
web-console (3.6.1) lib/web_console/middleware.rb:135:in `call_app'
web-console (3.6.1) lib/web_console/middleware.rb:30:in `block in call'
web-console (3.6.1) lib/web_console/middleware.rb:20:in `catch'
web-console (3.6.1) lib/web_console/middleware.rb:20:in `call'
actionpack (5.2.0) lib/action_dispatch/middleware/show_exceptions.rb:33:in `call'
railties (5.2.0) lib/rails/rack/logger.rb:38:in `call_app'
railties (5.2.0) lib/rails/rack/logger.rb:26:in `block in call'
activesupport (5.2.0) lib/active_support/tagged_logging.rb:71:in `block in tagged'
activesupport (5.2.0) lib/active_support/tagged_logging.rb:28:in `tagged'
activesupport (5.2.0) lib/active_support/tagged_logging.rb:71:in `tagged'
railties (5.2.0) lib/rails/rack/logger.rb:26:in `call'
sprockets-rails (3.2.1) lib/sprockets/rails/quiet_assets.rb:13:in `call'
actionpack (5.2.0) lib/action_dispatch/middleware/remote_ip.rb:81:in `call'
actionpack (5.2.0) lib/action_dispatch/middleware/request_id.rb:27:in `call'
rack (2.0.5) lib/rack/method_override.rb:22:in `call'
rack (2.0.5) lib/rack/runtime.rb:22:in `call'
activesupport (5.2.0) lib/active_support/cache/strategy/local_cache_middleware.rb:29:in `call'
actionpack (5.2.0) lib/action_dispatch/middleware/executor.rb:14:in `call'
actionpack (5.2.0) lib/action_dispatch/middleware/static.rb:127:in `call'
rack (2.0.5) lib/rack/sendfile.rb:111:in `call'
webpacker (3.4.3) lib/webpacker/dev_server_proxy.rb:18:in `perform_request'
rack-proxy (0.6.4) lib/rack/proxy.rb:57:in `call'
railties (5.2.0) lib/rails/engine.rb:524:in `call'
puma (3.11.4) lib/puma/configuration.rb:225:in `call'
puma (3.11.4) lib/puma/server.rb:632:in `handle_request'
puma (3.11.4) lib/puma/server.rb:446:in `process_client'
puma (3.11.4) lib/puma/server.rb:306:in `block in run'
puma (3.11.4) lib/puma/thread_pool.rb:120:in `block in spawn_thread'

2 回复 | 直到 6 年前

smallbutton 6 年前

既然您说,生成的SQL在您直接调用它时工作,那么问题的根源就在将返回的数据映射回对象的过程中。尽管您的设置看起来很好,但它似乎非常不标准,所以我将尝试向rails提供更多关于这些关联如何属于一起的提示。

source 为了你的 through 关系 (docs) :

has_many :inventories, through: :content_accesses, source: :inventory

inverse_of , foreign_key , primary_key class_name 另一方面 belongs_to 和 has_many 关联,给rails必要的提示。很难说什么可能有用,但在非标准设置中,您有时会遇到自动推断名称的某些问题。

Tim Sullivan 6 年前

salesforce

将正确的herokukonnect_URL设置到本地Postgres服务器解决了这个问题,不过我可以通过移动 ContentAccess 的表,然后从服务器创建数据库的新副本以供本地使用。

check out the followup question 我有一个不同但相似的例子 PBKAC .