Re: postgres_fdw and defaults

On 11/15/2016 10:49 AM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> I know we've discussed this before, but I have just had the unpleasant
>> experience of trying to get around the difficulty of inserting into a
>> foreign table with a serial field, surely a common enough scenario that
>> we should try to deal with it better. The solution of using a local
>> sequence really doesn't work, as there might be multiple users of the
>> table, as there will be in my scenario. I opted instead to get a value
>> from the foreign sequence explicitly before inserting, but that's pretty
>> ugly. So I am wondering (without having looked at all closely at it) if
>> we could set an option to tell the FDW that we want the foreign default
>> to be used instead of a local one. Is the difficulty that we don't know
>> if a value has been explicitly supplied or not? Maybe we could have some
>> magic value that we could use instead ('foreign_default'?). I'm just
>> throwing out ideas here, but this is really a wart that could well do
>> with attention.
> I'm not awake enough to recall the previous discussions of remote
> default-value insertion in any detail, but they were extensive, and
> no one has proposed solutions to the problems we hit.  Please consult
> the archives.


I will look back further, But I see in 2013 Stephen said this:

> At first blush, with 'simple' writable views, perhaps that can just be a
> view definition on the remote side which doesn't include that column and
> therefore that column won't be sent to the remote side explicitly but,
> but the view, running on the remote, would turn around and pick up the
> default value for any fields which aren't in the view definition when
> inserting into the table underneath.


and you replied

> Yeah, I think the possibility of such a workaround was one of the
> reasons we decided it was okay to support only locally-computed
> defaults for now.


The trouble in my case is I actually need to know the serial column 
value, so using a view that hides it doesn't work.



cheers

andrew