On Wed, Sep 25, 2013 at 2:47 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Lonni J Friedman <netllama@xxxxxxxxx> 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.
Is there some elegant mechanism for keeping the local & foreign
sequences in sync?
>
> 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.
Understood. Other than reading the code, is there somewhere that
these limitations are documented that I overlooked?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
