No way to join when postgresql_fdw is used

[odinho]

I need to run postgrest on another machine than where the real database is. Connecting postgrest directly gave abysmal performance, so I've used postgresql_fdw to make postgres handle the connection. I also as an added bonus created materialized views for the tables to make the readonly lookups even more performant.

I was originally thinking of replicating the database, but this frontend also use postgres for other things so didn't find a good, nice way of solving it.

Since postgresql_fdw don't allow you to set the REFERENCES annotations, there's now way for postgrest to figure out the references. Is there any way around this?

{
  "details": "no relation between checkins and account",
  "message": "could not find foreign keys between these entities"
}

[ruslantalpa]

No easy way, maybe in the relative near future there will be a way to manually specify the relations in a config, but not for now.
Thinking about it ... you somehow need to fake the results of this query https://github.com/begriffs/postgrest/blob/master/src/PostgREST/DbStructure.hs#L411
maybe you can somhow create views with the same name and data tha come from the original db, and play with the search path so that when the query is run, you get to the views and not to the local tables.
Don't know if you can "configure" postgres with a predefined searchpath for a particular user (authenticator) but if you can't, you can create a custom build that prior to "reading the structure", issues a "set search_path" so that the query gets the data from your views

[odinho]

What an idea :) Well, I tried this today, but I can't really get it to work. It seemed like it would, but then something else further down is probably making this not-work. The search_path can be set for the database for the user, and that worked really well.

ALTER ROLE postgrest IN DATABASE p2k SET search_path = public, remote, pg_catalog;

I tried to put the relations on the postgresql_fdw view first, and after that on the materialized view. I'm not comfortable with debugging Haskell since I have trouble even reading the code (sadly quite Haskell illiterate) - and I didn't see any debug mode you could put it in. I guess I could get postgresql to log the requests, but I should probably just do something simpler like copying the tables over SSH every night.

I guess this will still just be a use case which is not yet fully supported ;)

p2k=# SELECT * FROM pg_constraint;
        conname        | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | conkey | confkey | conpfeqop | conppeqop | conffeqop | conexclop | conbin | consrc | oid 
-----------------------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-----------+-------------+-------------+---------------+------------+-------------+--------------+--------+---------+-----------+-----------+-----------+-----------+--------+--------+-----
 accounts_pkey         |         2200 | p       | f             | f           | t            |    26433 |        0 |        0 |         0 |             |             |               | t          |           0 | t            | {1}    |         |           |           |           |           |        |        |   2
 checkins_account_fkey |         2200 | f       | f             | f           | t            |    26481 |        0 |        0 |     26433 | a           | a           | s             | t          |           0 | t            | {2}    | {1}     | {96}      | {96}      | {96}      |           |        |        |   1
(2 rows)

p2k=> SELECT ns1.nspname AS table_schema,
           tab.relname AS table_name,
           column_info.cols AS columns,
           ns2.nspname AS foreign_table_schema,
           other.relname AS foreign_table_name,
           column_info.refs AS foreign_columns           
    FROM pg_constraint,
       LATERAL (SELECT array_agg(cols.attname) AS cols,
                       array_agg(cols.attnum)  AS nums,
                       array_agg(refs.attname) AS refs
                  FROM ( SELECT unnest(conkey) AS col, unnest(confkey) AS ref) k,
                       LATERAL (SELECT * FROM pg_attribute
                                 WHERE attrelid = conrelid AND attnum = col)
                            AS cols,
                       LATERAL (SELECT * FROM pg_attribute
                                 WHERE attrelid = confrelid AND attnum = ref)
                            AS refs)
            AS column_info,
       LATERAL (SELECT * FROM pg_namespace WHERE pg_namespace.oid = connamespace) AS ns1,
       LATERAL (SELECT * FROM pg_class WHERE pg_class.oid = conrelid) AS tab,
       LATERAL (SELECT * FROM pg_class WHERE pg_class.oid = confrelid) AS other,
       LATERAL (SELECT * FROM pg_namespace WHERE pg_namespace.oid = other.relnamespace) AS ns2
    WHERE confrelid != 0
    ORDER BY (conrelid, column_info.nums);
 table_schema | table_name |  columns  | foreign_table_schema | foreign_table_name | foreign_columns 
--------------+------------+-----------+----------------------+--------------------+-----------------
 public       | checkins   | {account} | public               | accounts           | {id}

[ruslantalpa]

You don't have to debug haskell for this particular issue, i think this can work for you (it probably failed because you created the views only for that particular query i showed but you probably need to check all the queries in that file), try these steps

1. extract all the queries from the DbStructure.hs
2. run them on the source/remote db and write down the results.
3. create the materialised views on the db with postgrests
4. run the same queries and check that the results are the same with the remote db

[ruslantalpa]

i.e. you need to create materialised views for pg_namespace pg_class pg_constraint .... and so on, check all the tables that are used in those queries.
if you could somehow create a mirror of the pg_catalog schema from the remote machine in a local schema called pg_catalog_remote, and add that to the search path, i think it should work

[ruslantalpa]

Closing as a duplicate of (same core problem) #709

[Iced-Sun]

Closing as a duplicate of (same core problem) #709

It seems to me that #709 deals with foreign key detection in views. There is still no work-around to do the resource embedding for the foreign tables.

[steve-chavez]

Related SO question here https://dba.stackexchange.com/questions/138591/foreign-key-references-constraint-on-postgresql-foreign-data-wrapper.

Also https://stackoverflow.com/questions/41568251/postgresql-9-5-creating-foreign-key-across-database-using-foreign-data-wrapper.

For now it seems that the PostgreSQL way to solve this is to sync the remote db to guarantee the constraint works properly.

If we allow Resouce Embedding on foreign tables we wouldn't be talking about foreign keys anymore(even if manually indicated to PostgREST), so I agree this is a separate issue.

[steve-chavez]

Solved on #2144