Re: postgresql_fdw doesn't handle defaults correctly

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

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