Re: postgres_fdw: Running default expressions on foreign server

On Fri, Aug 22, 2014 at 6:15 AM, Tommy Duek <taduek(at)gmail(dot)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.1380145647@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