Column defaults for foreign tables (was Re: [v9.3] writable foreign tables) - Mailing list pgsql-hackers

I wrote:
> Thom Brown <thom@linux.com> writes:
>> Out of curiosity, is there any way to explicitly force a foreign
>> DEFAULT with column-omission?

> That's one of the things that would have to be worked out before
> we could implement anything here.  The easy answer would be that DEFAULT
> specifies the local default, and only if you omit the column entirely
> from the local command (including not having a local default) does the
> remote default take effect.  But whether that would be convenient to
> use is hard to tell.

> Another thing that would be easy to implement is to say that the new row
> value is fully determined locally (including defaults if any) and remote
> defaults have nothing to do with it.  But I think that's almost
> certainly a usability fail --- imagine that the remote has a
> sequence-generated primary key, for instance.  I think it's probably
> necessary to permit remote insertion of defaults for that sort of table
> definition to work conveniently.

I looked into this a bit, and realize that the code-as-committed is
already not self-consistent, because these give different results:
insert into foreigntable default values;
insert into foreigntable values(default, default, ...);

The former case inserts whatever the remote-side default values are.
The latter case inserts NULLs, regardless of the remote defaults,
because that's what the query is expanded to by the rewriter.  So it
seems like this is something we must fix for 9.3, because otherwise
we're going to have backwards-compatibility issues if we try to change
the behavior later.

I've concluded that the "ideal behavior" probably is that if you have
declared a DEFAULT expression for a foreign table's column, then that's
what the default is for the purpose of inserts or updates through the
foreign table; but if you haven't, then (at least for postgres_fdw)
the effective default is whatever the remote table has.

I thought at first that we could fix this, and the related case
update foreigntable set somecolumn = default

with some relatively localized hacking in the rewriter.  However, that
idea fell down when I looked at multi-row inserts:
insert into foreigntable  values (x, y, z), (a, default, b), (c, d, default), ...

The current implementation of this requires substituting the appropriate
column default expressions into the VALUES lists at rewrite time.
That's okay for a default expression that is known locally and should be
evaluated locally; but I see absolutely no practical way to make it work
if we'd like to have the defaults inserted remotely.  We'd need to have
some out-of-band indication that a row being returned by the ValuesScan
node had had "default" placeholders in particular columns --- and I just
can't see us doing the amount of violence that would need to be done to
the executor to make that happen.  Especially not in the 9.3 timeframe.

So one possible answer is to adopt the ignore-remote-defaults semantics
I suggested above, but I don't much like that from a usability standpoint.

Another idea is to throw a "not implemented" error on the specific case
of a multi-row VALUES with DEFAULT placeholders when the target is a
foreign table.  That's pretty grotty, not least because it would have to
reject the case for all foreign tables not just postgres_fdw ones.  But
it would give us some wiggle room to implement more desirable semantics
in the cases that we can handle reasonably.

Thoughts?
        regards, tom lane