Re: postgresql_fdw doesn't handle defaults correctly

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.

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.

Thanks,
Amit