Re: postgresql_fdw doesn't handle defaults correctly
От | Pavel Stehule |
---|---|
Тема | Re: postgresql_fdw doesn't handle defaults correctly |
Дата | |
Msg-id | CAFj8pRAW3WxV4Ca-z+x8LHpq45xB0g=Bt9xgGWyM__-s61X6Zw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: postgresql_fdw doesn't handle defaults correctly (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
Ответы |
Re: postgresql_fdw doesn't handle defaults correctly
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
Список | pgsql-hackers |
2018-06-27 8:28 GMT+02:00 Amit Langote <Langote_Amit_f8@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_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...Hmm, yes. In the example in my previous reply, I used the same database,
> 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;
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
В списке pgsql-hackers по дате отправления: