Re: postgresql_fdw doesn't handle defaults correctly

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:
> >>> Unfortunately, when I use identity column
> >>>
> >>> create table foo(a int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, b
> >> date
> >>> default current_date, c int);
> >>>
> >>> then import doesn't fail, but still it doesn't work
> >>
> >> It seems that, unlike DEFAULT, the information about IDENTITY is not
> >> stored in pg_attrdef catalog. It's rather stored in
> >> pg_attribute.attidentity. Looking at postgres_fdw's IMPORT FOREIGN
> SCHEMA
> >> implementation, while it fetches the DEFAULT expression from pg_attrdef
> >> when asked, it seems that it does not fetch the value of attidentity.
> >>
> >> Not sure if we should consider that a bug or simply an unsupported case
> >> like a DEFAULT referring to a sequence. In any case, if it's an
> >> unsupported case, we should perhaps error out in a more user-friendly
> >> manner.
> >>
> >
> > 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.

Regards

Pavel

> Thanks,
> Amit
>
>