Re: postgresql_fdw doesn't handle defaults correctly

On 2018/06/27 2:47, Pavel Stehule wrote:
> 2018-06-25 4:30 GMT+02:00 Amit Langote <Langote_Amit_f8@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