Re: Column defaults for foreign tables (was Re: [v9.3] writable foreign tables)

Tom Lane wrote:
>> Thom Brown <thom(at)linux(dot)com> writes:
>>> Out of curiosity, is there any way to explicitly force a foreign
>>> DEFAULT with column-omission?

> 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 agree.

> 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?

Do you think that it is possible to insert remote defaults
by omitting columns like this:

INSERT INTO foreigntable (col1, col3) VALUES (a, c);

If that can be made to work, then my opinion is that throwing an
error on

INSERT INTO foreigntable (col1, col2, col3) VALUES (a, DEFAULT, c);

would be acceptable, because there is at least a workaround.

If the first variant also cannot be made to work with remote
defaults, then I'd say that the best is to use local
defaults throughout and accept the loss of usability.

Yours,
Laurenz Albe