Thread: postgres_fdw: Running default expressions on foreign server
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.
Thanks,
Tommy Duek
On Fri, Aug 22, 2014 at 6:15 AM, Tommy Duek <taduek@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.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:
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');
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
Tommy Duek <taduek@gmail.com> writes:
> 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
figureit�s worth checking since 9.4 is scheduled to be released any day now, before I start rewriting the whole
project.
No, there's no change in this area in 9.4.
regards, tom lane