Обсуждение: 9.5 Import foreign schema error with user defined type in schema other than public.

Hi,

I was testing the import foreign schema feature in 9.5.  i have a schema =
with a defined type timerange.  When i tried to import the the foreign =
schema i got the following error.


import foreign schema dba from server pg_test_94 into f_system_dba;
ERROR:  type "dba.timerange[]" does not exist
LINE 8:   period dba.timerange[] OPTIONS (column_name 'period') NOT =E2=80=
=A6

server information=20

local server:
                                                version                  =
                              =20
=
--------------------------------------------------------------------------=
-------------------------------
 PostgreSQL 9.5devel on x86_64-unknown-linux-gnu, compiled by gcc =
(Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
=20
CONFIGURE =3D '--prefix=3D/usr/local/postgres/9.5' '--with-perl' =
'--with-python' '--with-openssl' 'CFLAGS=3D-DLINUX_OOM_SCORE_ADJ=3D0'
CC =3D gcc
CPPFLAGS =3D -D_GNU_SOURCE
CFLAGS =3D -Wall -Wmissing-prototypes -Wpointer-arith =
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute =
-Wformat-security -fno-strict-aliasing -fwrapv =
-fexcess-precision=3Dstandard -DLINUX_OOM_SCORE_ADJ=3D0

remote server

                                                  version                =
                                   =20
=
--------------------------------------------------------------------------=
------------------------------------
 PostgreSQL 9.4.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) =
4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

CONFIGURE =3D '--with-tclconfig=3D/usr/lib64' '--with-perl' =
'--with-python' '--with-tcl' '--with-openssl' '--with-pam' '--with-krb5' =
'--with-gssapi' '--enable-nls' '--with-ossp-uuid' '--with-libxml' =
'--with-libxslt' '--with-ldap' '--prefix=3D/usr/local/postgres/9.4' =
'CFLAGS=3D-DLINUX_OOM_SCORE_ADJ=3D0' '--with-libs=3D/usr/lib' =
'--with-includes=3D/usr/include' '--with-uuid=3D/usr/lib'
CC =3D gcc
CPPFLAGS =3D -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include
CFLAGS =3D -Wall -Wmissing-prototypes -Wpointer-arith =
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute =
-Wformat-security -fno-strict-aliasing -fwrapv =
-fexcess-precision=3Dstandard -DLINUX_OOM_SCORE_ADJ=3D0


Getting the error.

LOCAL SERVER:
create server pg_fschema_test foreign data wrapper postgres_fdw options =
(host '192.168.6.200', dbname 'foreign_schema_test');
create user mapping for superbee server pg_fschema_test options (user =
'superbee');
create schema f_public;
create schema f_test;


REMOTE SERVER:
in schema public
create type timerange as range (subtype =3D time);
create table timerange_test(tid int, data text, t timerange, ta =
timerange[]);

in schema test
create type timerange as range (subtype =3D time);
create table timerange_test(tid int, data text, t timerange, ta =
timerange[]);

IMPORT FROM SCHEMA PUBLIC

import foreign schema public from server pg_fschema_test into f_public;
IMPORT FOREIGN SCHEMA
Time: 57,575 ms

works fine=20
describe objects.
 \d
                  List of relations
  Schema  |      Name      |     Type      |  Owner  =20
----------+----------------+---------------+----------
 f_public | pdb_object     | foreign table | superbee
 f_public | timerange_test | foreign table | superbee
(2 rows)

 \d timerange_test
           Foreign table "f_public.timerange_test"
 Column |        Type        | Modifiers |    FDW Options    =20
--------+--------------------+-----------+--------------------
 t      | public.timerange   |           | (column_name 't')
 ta     | public.timerange[] |           | (column_name 'ta')
Server: pg_fschema_test
FDW Options: (schema_name 'public', table_name 'timerange_test=E2=80=99)

IMPORT FROM SCHEMA TEST

import foreign schema test from server pg_fschema_test into f_test;
ERROR:  schema "test" does not exist
LINE 2:   t test.timerange OPTIONS (column_name 't'),
            ^
QUERY:  CREATE FOREIGN TABLE timerange_test (
  t test.timerange OPTIONS (column_name 't'),
  ta test.timerange[] OPTIONS (column_name 'ta')
) SERVER pg_fschema_test
OPTIONS (schema_name 'test', table_name 'timerange_test');
CONTEXT:  importing foreign table "timerange_test"
Time: 17,550 ms

I haven not been able to get the same error message on my test case as =
my initial try. But the behavior is the same.
I can provide more information as needed.

Reiner=

reiner peterke <zedaardv@drizzle.com> writes:
> I was testing the import foreign schema feature in 9.5.  i have a schema with a defined type timerange.  When i tried
toimport the the foreign schema i got the following error. 

I don't see any reason to think this isn't operating as designed.
postgres_fdw does not attempt to import user-defined data types from the
remote server.  Per the manual:

    If the remote tables to be imported have columns of user-defined
    data types, the local server must have compatible types of the
    same names.

("Same names" includes the schema name, btw.)

            regards, tom lane

> On Jun 9, 2015, at 3:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> reiner peterke <zedaardv@drizzle.com> writes:
>> I was testing the import foreign schema feature in 9.5.  i have a =
schema with a defined type timerange.  When i tried to import the the =
foreign schema i got the following error.
>=20
> I don't see any reason to think this isn't operating as designed.
> postgres_fdw does not attempt to import user-defined data types from =
the
> remote server.  Per the manual:
>=20
>     If the remote tables to be imported have columns of user-defined
>     data types, the local server must have compatible types of the
>     same names.
>=20
> ("Same names" includes the schema name, btw.)
>=20
>             regards, tom lane

Ok i missed that

Thanks,

reiner=

On Tue, Jun 09, 2015 at 10:56:26AM +0200, reiner peterke wrote:
> Hi,
>
> I was testing the import foreign schema feature in 9.5.  i have a
> schema with a defined type timerange.  When i tried to import the
> the foreign schema i got the following error.

I ran into this with PostGIS, and what I did to solve it is install
PostGIS on the local node.

Perhaps starting with importing your dba schema, or recursively down
to whichever one is "root" in the sense of the dependency graph could
help.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate