Re: postgresql_fdw doesn't handle defaults correctly

2018-06-27 11:19 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2018-06-27 11:12 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>
>>
>>
>> 2018-06-27 9:30 GMT+02:00 Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>:
>>
>>> On 2018/06/27 15:59, Pavel Stehule wrote:
>>> > 2018-06-27 8:45 GMT+02:00 Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
>>> >:
>>> >> On 2018/06/27 15:33, Pavel Stehule wrote:
>>> >>> I don't understand, why is necessary to replace missing values by
>>> NULLs?
>>> >>>
>>> >>> I didn't expect so insert into foo(c) values(10)
>>> >>>
>>> >>> will be translated to
>>> >>>
>>> >>> insert into foo(a,b,c) values(NULL, NULL, 10)
>>> >>
>>> >> That's what we do if there is no default value to fill in if the
>>> INSERT
>>> >> command didn't specify the value. In this case, even if the table on
>>> the
>>> >> remote side may be define with column as IDENTITY, the IMPORT FOREIGN
>>> >> SCHEMA command does not fetch that information and creates the foreign
>>> >> table locally without any default value. So, that's a missing piece
>>> of
>>> >> functionality in postgres_fdw's implementation of the command.
>>> >>
>>> >> As a workaround for that missing functionality, one can always create
>>> the
>>> >> foreign table by hand and specify DEFAULT and IDENTITY explicitly as
>>> >> necessary.
>>> >>
>>> >
>>> > It is works but I afraid so this design is pretty unhappy.
>>> >
>>> > It created implicit local sequence .. for remote object. Maybe I use
>>> strong
>>> > worlds, but is not good design. In this case, when identity column is
>>> > defined, then driver should not to generate this value.
>>> >
>>> > creating any local object for remote object should not to work - it is
>>> like
>>> > indexes or some else. I don't understand to motivation for this design.
>>>
>>> Hmm, maybe you're right that foreign table creation shouldn't create a
>>> sequence locally. But it should remember that there is a default on the
>>> remote side somehow and therefore not put NULLs in place of columns whose
>>> values are not specified in the original INSERT command.
>>>
>>
>> I can understand so there can be use cases, where sequences are created
>> locally with different start. But usually, it is not possible when the
>> IMPORT SCHEMA command is used, and probably, this case will be a exception.
>>
>>
>>> To be clear, I didn't design this. Perhaps, someone who did or someone
>>> who knows this better can comment with regard to design issues and
>>> whether/how to fix it.
>>>
>>
>> ook :). It can be interesting to know more about this design.
>>
>
> looks like old issue
>
> https://www.postgresql.org/message-id/26654.1380145647%40sss.pgh.pa.us
>

and there is a ugly workaround - use before insert trigger on target table

>
>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>>
>>>
>>> Thanks,
>>> Amit
>>>
>>>
>>
>