Re: postgres FDW doesn't support sequences?

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.

regards, tom lane