9.5 foreign schema

From: "Campbell, Lance" <lance(at)illinois(dot)edu>
To: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>
Subject: 9.5 foreign schema
Date: 2015-11-20 14:44:53
Message-ID: B75CD08C73BD3543B97E4EF3964B7D702014C829@CITESMBX1.ad.uillinois.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I am really excited about foreign schemas being available in 9.5 . I was testing this feature out and ran into a situation.

Issue:
I have a prod database called db1. I have two schemas, schema1 and schema2. I have two tables schema1.table1 and schema2.table2 . In my current production database a column within schema2.table2 has a foreign key constraint to schema1.table1 . If a record is deleted from schema1.table1 then the corresponding rows are deleted from schema2.table2 .

So I set up a new database on a different server called db2. I then set up a foreign schema on db2 that pointed to schema1 on db1. Then I loaded schema2 directly onto db2 in the hopes that all would work great.

Then I got an error message that said, 'ERROR: referenced relation "table1" is not a table'. I now am assuming I cannot have foreign keys pointing to tables within foreign schemas.

I guess that makes sense because if db1 was not available then db2 would start deleting data. Is that correct?

Is there a new way to handle foreign keys when using foreign tables?

Lance

Browse pgsql-admin by date

  From Date Subject
Next Message Williams, Alex 2015-11-20 19:50:41 Re: Question on Hot Standby in PostgreSQL
Previous Message John Scalia 2015-11-20 12:46:50 Re: Problem with pg_hba.conf