Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper?

Question

It seems to be disallowed to add a Foreign Key constraint to a foreign table. Is there any other way to do this? My two tables have these constrains on the remote server.

---

More specific details:

I only really need it as an annotation because some tools look that up for JOINs.

I have two tables on a remote server that I've added to my local using postgresql_fdw's CREATE FOREIGN TABLE. These I'm using in PostgREST, an automatic API server uses the REFERENCES information in order to make joins.

Actually, I'm further making two additional CREATE MATERIALIZED VIEW to make the lookups fast - but sadly, they too don't have any way I know of to add REFERENCES information?

I'm open to other ways of making (read-only) lookups fast for these tables on the foreign API server and where Foreign Key constraints will work.

Answer 1

The PostgREST team gave a potential solution: faking the foreign key constraints.

They read the pg_catalog.pg_constraint table to get this information. The idea was that I could manipulate the search_path and create my own pg_constraint table where I could make the fake foreign keys:

CREATE TABLE pg_constraint (LIKE pg_catalog.pg_constraint);

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

I was able to make such a table, and the query they use gave something reasonable back. However it seems to fail for some other reason.

It's also hackish and quite a bother to create the information in pg_constraint correctly. So I probably won't accept this answer, just wanted to put it here because it might work for someone else / some similar use case.

Answer 2

You could create local copies of the remote tables, and run periodic processes that would add new records, remove deleted records, and update changed records. How large are the remote tables? I guess not too big since you were planning on creating local materialized views from them. Normal tables that you keep synchronised would have a similar impact.

Once you have local copies of your remote tables, then you can implement foreign keys to them on the tables you need foreign keys.