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
