Re: postgresql_fdw doesn't handle defaults correctly

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.

Regards

Pavel

>
> Thanks,
> Amit
>
>