Re: postgres FDW doesn't support sequences?

On Wed, Sep 25, 2013 at 4:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Lonni J Friedman <netllama(at)gmail(dot)com> writes:
>> If I INSERT a new row into the local table (not the foreign table
>> version), without specifying the 'id' column explicitly, it
>> automatically is assigned the nextval in the sequence counter.
>> However, if I attempt to run the same INSERT using the foreign table,
>> it always fails complaining that null value in column "id" violates
>> not-null constraint. It seems like the FDW is somehow ignoring the
>> existence of the sequence default value, and rewriting the SQL query
>> to explicitly attempt to insert a NULL value.
>
> Yeah, there was quite a bit of discussion about that back in February or
> so. The short of it is that column default values that are defined on the
> foreign server are not respected by operations on a foreign table; rather,
> you have to attach a DEFAULT specification to the foreign table definition
> if you want inserts into the foreign table to use that default.
>
> The default expression is executed locally, too, which means that if you'd
> like it to read like "nextval('some_seq')" then some_seq has to be a local
> sequence, not one on the foreign server.
>
> I realize that this isn't ideal for serial-like columns, but honoring
> default expressions that would execute on the foreign server turned out
> to be a huge can of worms. We might figure out how to fix that some day;
> but if we'd insisted on a solution now, there wouldn't be writable foreign
> tables at all in 9.3.

This situation as well as a lot of other things be worked around if
there was a mechanic to pass SQL (or whatever language the foreign
server accepts) directly through to the foreign server, especially if
it could be optionally parameterized. Until that happens for most
real world work you're better off using dblink for postgres to
postgres communication.

merlin