Re: postgresql_fdw doesn't handle defaults correctly

Hi

2018-06-25 4:30 GMT+02:00 Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)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)

\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;

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
>
>