Thread: postgresql_fdw doesn't handle defaults correctly
Hi
I have a table boo
create table boo(id serial primary key, inserted date default current_date, v varchar);
I imported this table via simple
IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO public;
The command insert into boo(v) values('ahoj'); is working in original database, but in second database with foreign table this fails
postgres=# insert into boo(v) values('ahoj');
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, null, ahoj).
CONTEXT: remote SQL command: INSERT INTO public.boo(id, inserted, v) VALUES ($1, $2, $3)
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, null, ahoj).
CONTEXT: remote SQL command: INSERT INTO public.boo(id, inserted, v) VALUES ($1, $2, $3)
It does unwanted transformation to insert of all columns.
Is it expected behave?
Regards
Pavel
Hi. On 2018/06/24 2:23, Pavel Stehule wrote: > Hi > > I have a table boo > > create table boo(id serial primary key, inserted date default current_date, > v varchar); > > I imported this table via simple > > IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO public; 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); select * from public.foo; a | b | c ---+------------+--- 1 | 2018-06-25 | 1 2 | 2018-06-25 | 2 (2 rows) Thanks, Amit
Hi
2018-06-25 4:30 GMT+02:00 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp >:
Hi.
On 2018/06/24 2:23, Pavel Stehule wrote:
> Hi
>
> I have a table boo
>
> create table boo(id serial primary key, inserted date default current_date,
> v varchar);
>
> I imported this table via simple
>
> IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO public;
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.
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)
+---+------------+----+
| 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"
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;
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
Regards
Pavel
Thanks,
Amit
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
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
On 2018/06/27 15:33, Pavel Stehule wrote: >>> 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) That's what we do if there is no default value to fill in if the INSERT command didn't specify the value. In this case, even if the table on the remote side may be define with column as IDENTITY, the IMPORT FOREIGN SCHEMA command does not fetch that information and creates the foreign table locally without any default value. So, that's a missing piece of functionality in postgres_fdw's implementation of the command. As a workaround for that missing functionality, one can always create the foreign table by hand and specify DEFAULT and IDENTITY explicitly as necessary. Thanks, Amit
2018-06-27 8:45 GMT+02:00 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>:
On 2018/06/27 15:33, Pavel Stehule wrote:
>>> 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)
That's what we do if there is no default value to fill in if the INSERT
command didn't specify the value. In this case, even if the table on the
remote side may be define with column as IDENTITY, the IMPORT FOREIGN
SCHEMA command does not fetch that information and creates the foreign
table locally without any default value. So, that's a missing piece of
functionality in postgres_fdw's implementation of the command.
As a workaround for that missing functionality, one can always create the
foreign table by hand and specify DEFAULT and IDENTITY explicitly as
necessary.
It is works but I afraid so this design is pretty unhappy.
It created implicit local sequence .. for remote object. Maybe I use strong worlds, but is not good design. In this case, when identity column is defined, then driver should not to generate this value.
creating any local object for remote object should not to work - it is like indexes or some else. I don't understand to motivation for this design.
Regards
Pavel
Thanks,
Amit
On 2018/06/27 15:59, Pavel Stehule wrote: > 2018-06-27 8:45 GMT+02:00 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>: >> On 2018/06/27 15:33, Pavel Stehule wrote: >>> 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) >> >> That's what we do if there is no default value to fill in if the INSERT >> command didn't specify the value. In this case, even if the table on the >> remote side may be define with column as IDENTITY, the IMPORT FOREIGN >> SCHEMA command does not fetch that information and creates the foreign >> table locally without any default value. So, that's a missing piece of >> functionality in postgres_fdw's implementation of the command. >> >> As a workaround for that missing functionality, one can always create the >> foreign table by hand and specify DEFAULT and IDENTITY explicitly as >> necessary. >> > > It is works but I afraid so this design is pretty unhappy. > > It created implicit local sequence .. for remote object. Maybe I use strong > worlds, but is not good design. In this case, when identity column is > defined, then driver should not to generate this value. > > creating any local object for remote object should not to work - it is like > indexes or some else. I don't understand to motivation for this design. Hmm, maybe you're right that foreign table creation shouldn't create a sequence locally. But it should remember that there is a default on the remote side somehow and therefore not put NULLs in place of columns whose values are not specified in the original INSERT command. To be clear, I didn't design this. Perhaps, someone who did or someone who knows this better can comment with regard to design issues and whether/how to fix it. Thanks, Amit
2018-06-27 9:30 GMT+02:00 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>:
On 2018/06/27 15:59, Pavel Stehule wrote:
> 2018-06-27 8:45 GMT+02:00 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>:
>> On 2018/06/27 15:33, Pavel Stehule wrote:
>>> 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)
>>
>> That's what we do if there is no default value to fill in if the INSERT
>> command didn't specify the value. In this case, even if the table on the
>> remote side may be define with column as IDENTITY, the IMPORT FOREIGN
>> SCHEMA command does not fetch that information and creates the foreign
>> table locally without any default value. So, that's a missing piece of
>> functionality in postgres_fdw's implementation of the command.
>>
>> As a workaround for that missing functionality, one can always create the
>> foreign table by hand and specify DEFAULT and IDENTITY explicitly as
>> necessary.
>>
>
> It is works but I afraid so this design is pretty unhappy.
>
> It created implicit local sequence .. for remote object. Maybe I use strong
> worlds, but is not good design. In this case, when identity column is
> defined, then driver should not to generate this value.
>
> creating any local object for remote object should not to work - it is like
> indexes or some else. I don't understand to motivation for this design.
Hmm, maybe you're right that foreign table creation shouldn't create a
sequence locally. But it should remember that there is a default on the
remote side somehow and therefore not put NULLs in place of columns whose
values are not specified in the original INSERT command.
I can understand so there can be use cases, where sequences are created locally with different start. But usually, it is not possible when the IMPORT SCHEMA command is used, and probably, this case will be a exception.
To be clear, I didn't design this. Perhaps, someone who did or someone
who knows this better can comment with regard to design issues and
whether/how to fix it.
ook :). It can be interesting to know more about this design.
Regards
Pavel
Thanks,
Amit
2018-06-27 11:12 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:
2018-06-27 9:30 GMT+02:00 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>: On 2018/06/27 15:59, Pavel Stehule wrote:
> 2018-06-27 8:45 GMT+02:00 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>:
>> On 2018/06/27 15:33, Pavel Stehule wrote:
>>> 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)
>>
>> That's what we do if there is no default value to fill in if the INSERT
>> command didn't specify the value. In this case, even if the table on the
>> remote side may be define with column as IDENTITY, the IMPORT FOREIGN
>> SCHEMA command does not fetch that information and creates the foreign
>> table locally without any default value. So, that's a missing piece of
>> functionality in postgres_fdw's implementation of the command.
>>
>> As a workaround for that missing functionality, one can always create the
>> foreign table by hand and specify DEFAULT and IDENTITY explicitly as
>> necessary.
>>
>
> It is works but I afraid so this design is pretty unhappy.
>
> It created implicit local sequence .. for remote object. Maybe I use strong
> worlds, but is not good design. In this case, when identity column is
> defined, then driver should not to generate this value.
>
> creating any local object for remote object should not to work - it is like
> indexes or some else. I don't understand to motivation for this design.
Hmm, maybe you're right that foreign table creation shouldn't create a
sequence locally. But it should remember that there is a default on the
remote side somehow and therefore not put NULLs in place of columns whose
values are not specified in the original INSERT command.I can understand so there can be use cases, where sequences are created locally with different start. But usually, it is not possible when the IMPORT SCHEMA command is used, and probably, this case will be a exception.
To be clear, I didn't design this. Perhaps, someone who did or someone
who knows this better can comment with regard to design issues and
whether/how to fix it.ook :). It can be interesting to know more about this design.
looks like old issue
RegardsPavel
Thanks,
Amit
2018-06-27 11:19 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:
2018-06-27 11:12 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:2018-06-27 9:30 GMT+02:00 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>: On 2018/06/27 15:59, Pavel Stehule wrote:
> 2018-06-27 8:45 GMT+02:00 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>:
>> On 2018/06/27 15:33, Pavel Stehule wrote:
>>> 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)
>>
>> That's what we do if there is no default value to fill in if the INSERT
>> command didn't specify the value. In this case, even if the table on the
>> remote side may be define with column as IDENTITY, the IMPORT FOREIGN
>> SCHEMA command does not fetch that information and creates the foreign
>> table locally without any default value. So, that's a missing piece of
>> functionality in postgres_fdw's implementation of the command.
>>
>> As a workaround for that missing functionality, one can always create the
>> foreign table by hand and specify DEFAULT and IDENTITY explicitly as
>> necessary.
>>
>
> It is works but I afraid so this design is pretty unhappy.
>
> It created implicit local sequence .. for remote object. Maybe I use strong
> worlds, but is not good design. In this case, when identity column is
> defined, then driver should not to generate this value.
>
> creating any local object for remote object should not to work - it is like
> indexes or some else. I don't understand to motivation for this design.
Hmm, maybe you're right that foreign table creation shouldn't create a
sequence locally. But it should remember that there is a default on the
remote side somehow and therefore not put NULLs in place of columns whose
values are not specified in the original INSERT command.I can understand so there can be use cases, where sequences are created locally with different start. But usually, it is not possible when the IMPORT SCHEMA command is used, and probably, this case will be a exception.
To be clear, I didn't design this. Perhaps, someone who did or someone
who knows this better can comment with regard to design issues and
whether/how to fix it.ook :). It can be interesting to know more about this design.looks like old issue
and there is a ugly workaround - use before insert trigger on target table
RegardsPavel
Thanks,
Amit