#StackBounty: #ruby-on-rails #postgresql ActiveRecord::StatementInvalid, PG::UndefinedTable error, but generated SQL works

Bounty: 200

This has been tremendously frustrating. I’m trying to get a has_many through working, and I think I’m just too close to this to see something super obvious. Each step works correctly, and the SQL that Rails is generating works, but together in the console it’s not.

The one weird thing about this whole setup is that there are a couple of tables in a salesforce schema, and the tablename and primary key aren’t standard. Here’s the basic structure:

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

Works:

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

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

Error:

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

When I run that query through Postico, though, it works fine. 🤬

Edited to add:

  • I moved content_accesses into the salesforce schema, and set self.table_name on the model correctly, but the problem still happens. As such, I don’t think this is related to being cross-schema.
  • That only makes this problem weirder to me. 🙁

DDL for the tables:

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);


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.