2018-06-27 8:28 GMT+02:00 Amit Langote <langote_amit...@lab.ntt.co.jp>:
> On 2018/06/27 2:47, Pavel Stehule wrote: > > 2018-06-25 4:30 GMT+02:00 Amit Langote <langote_amit...@lab.ntt.co.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 > >
