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. Thanks, Amit
