On Fri, Aug 22, 2014 at 6:15 AM, Tommy Duek <tad...@gmail.com> wrote:
> Hi Tom, > > I realize that postgres_fdw on 9.3 doesn't support default expressions > that run on the foreign server. In my case, I have a unique, > auto-incrementing ID column that the remote server keeps track of in a > sequence. The local foreign table doesn't have access to this and tries to > INSERT with IDs that have already been taken in the original table on the > remote server. > > After seeing this post: > http://www.postgresql.org/message-id/26654.1380145...@sss.pgh.pa.us, I'm > hopeful honoring these default expressions in the foreign server will be > supported at some point. > > I'm working on a project now that uses the postgres_fdw extensively. Do > you know if this will be fixed in 9.4? I figure it's worth checking since > 9.4 is scheduled to be released any day now, before I start rewriting the > whole project. > Don't count on that for 9.4, that's too late for it (and that's not a straight-forward problem). But, you can actually use a trick here to support global sequence IDs: 1) define a view wrapping nextval for this sequence on the foreign server: create sequence seq; create view seq_view as select nextval('seq') as a; 2) On the local server, create a foreign table that scans the view already defined in foreign server: create foreign server foreign_seq_table (a bigint) server postgres_server options (table_name 'seq_view'); 3) Create on local server a function querying foreign_seq_table: create function foreign_seq_nextval() returns bigint as 'select a from foreign_seq_table;' language sql; And now use each functions in local and foreign servers and you are fine for the ID uniqueness. Note that you could also use an approach with uuid-based methods to limit network delay across nodes as well. -- Michael
