Re: postgresql_fdw doesn't handle defaults correctly

2018-06-27 8:28 GMT+02:00 Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>:

> On 2018/06/27 2:47, Pavel Stehule wrote:
> > 2018-06-25 4:30 GMT+02:00 Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>:
> >> It seems you missed using OPTIONS (import_default 'true') here.
> >>
> >> create schema foo;
> >> create table foo.foo (a serial primary key, b date default current_date
> >> not null, c int);
> >>
> >> import foreign schema foo from server loopback into public options
> >> (import_default 'true');
> >>
> >> insert into public.foo (c) values (1);
> >> select * from public.foo;
> >> a | b | c
> >> ---+------------+---
> >> 1 | 2018-06-25 | 1
> >> (1 row)
> >>
> >> insert into foo.foo (c) values (2);
> >
> > This insert doesn't use foreign table. So it is different case.
>
> The first one (insert into public.foo ...) does, but...
>
> > select * from public.foo;
> >> a | b | c
> >> ---+------------+---
> >> 1 | 2018-06-25 | 1
> >> 2 | 2018-06-25 | 2
> >> (2 rows)
> >>
> > It looks like more different than I expected.
> >
> > create database t1;
> > \c t1
> > create table foo(a serial primary key, b date default current_date, c
> int);
> > insert into foo(c) values(10),(20);
> > select * from foo;
> >
> > t1=# select * from foo;
> > +---+------------+----+
> > | a | b | c |
> > +---+------------+----+
> > | 1 | 2018-06-26 | 10 |
> > | 2 | 2018-06-26 | 20 |
> > +---+------------+----+
> > (2 rows)
> >
> > \c postgres
> > create server t1 foreign data wrapper postgres_fdw options (dbname 't1');
> > create user mapping for pavel server t1;
> >
> > postgres=# import foreign schema public from server t1 into public
> options
> > (import_default 'true');
> > ERROR: relation "public.foo_a_seq" does not exist
> > CONTEXT: importing foreign table "foo"
> >
> > So it fails as probably expected - we doesn't support foreign sequences
> -
> > so we cannot to import schema with table with sequence with option
> > import_default = true;
> >
> > Looks like unsupported case - is not possible to insert to table with
> > serial column;
>
> Hmm, yes. In the example in my previous reply, I used the same database,
> so foo_a_seq would exist when importing foo. I now tried with the foreign
> server pointing to a different database, and can see the problem.
>
> So, that's indeed an unsupported case.
>
> > 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)

why? For situation, when target is a SQL database, it is contraproductive.

Regards

Pavel

> Thanks,
> Amit
>
>