Postgresql problems with postgres_fdw module

Question

I'm trying, using the PostgreSQL Maestro tool, to reference a foreign key coming from a "local" DB to an other primary key inside an other DB (actually, they're both on the same remote machine). I've heard about the postgres_fdw module to create a foreign table that act like a copy of the table inside the remote DB, but when I try to execute my query I have this error:

"SQL Error: ERROR: referenced relation "foreign_olo" is not a table".

This is my sql code:

CREATE TABLE edb.olo_config (  
primary_key       integer NOT NULL PRIMARY KEY,
puntamento        varchar,
mail_contatto_to  varchar,
mail_contatto_cc  varchar,
/* Foreign keys */
CONSTRAINT olo_code
  FOREIGN KEY (olo_code)
  REFERENCES edb.foreign_olo(codice_operatore)
) WITH (
  OIDS = FALSE
);

foreign_olo is my foreign table created with postgres_fdw. I have tried to commit an INSERT or a simple SELECT on the foreign_olo table, and all went well, so I can't understand why for the foreign key case it can't be recognized as a table. Thank you to everyone would give me an hand!

Answer 1

There are two parts to enforcing a foreign key constraint:

• An INSERT (or UPDATE of the FK field) on the child table must check that a parent record exists.
• A DELETE (or UPDATE of the PK field) on the parent table must check that no child record exists.

Foreign tables can't be referenced by FK constraints, because this second condition is impossible to enforce - there's no way for the remote server to automatically check for records in your local table.

You can implement the first check relatively easily with a trigger:

CREATE FUNCTION edb.olo_config_fk_trg() RETURNS TRIGGER AS
$$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM edb.foreign_olo
    WHERE codice_operatore = new.olo_code
    FOR KEY SHARE
  )
  THEN
    RAISE foreign_key_violation
      USING MESSAGE = 'edb.foreign_olo.codice_operatore="' || new.olo_code || '" not found';
  END IF;
  RETURN NULL;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER olo_config_fk_trg
AFTER INSERT OR UPDATE OF olo_code ON edb.olo_config
FOR EACH ROW EXECUTE PROCEDURE edb.olo_config_fk_trg();

You can create a similar trigger on the PK table to do the update/delete check, but it will require another foreign table in your other database which points back to your local olo_config.