SSW logo Blog - Michael's Musings

Wed, 27 Jan 2016

PostgresQL Foreign data wrappers and rails

Rails' discovers available tables and the attributes of those tables when it starts. It has no problems with views, but it turns out that foreign data wrappers do not turn up in that list, and so if one of your tables is really a foreign data wrapper (fdw), rails just doesn't believe it exists.

I was using the mysql_fdw to merge the customer lists of a CRM, a ticket system and the SG1 management application. The CRM uses MySQL (only!) while our preferred database is PostgreSQL; mysql_fdw makes them all see a few tables in common, but the the auto-discovery did not like it in staging.

Solution: add a layer of indirection! Move the original fdw into a schema, and then:

CREATE OR REPLACE VIEW public.crm_accounts AS (SELECT * FROM suitecrm.crm_accounts);

and lo-and-behold! it all works as expected.