Thread: 9.5 Import foreign schema error with user defined type in schema other than public.
9.5 Import foreign schema error with user defined type in schema other than public.
From
reiner peterke
Date:
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=
Re: 9.5 Import foreign schema error with user defined type in schema other than public.
From
Tom Lane
Date:
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
Re: 9.5 Import foreign schema error with user defined type in schema other than public.
From
reiner peterke
Date:
> 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=
Re: 9.5 Import foreign schema error with user defined type in schema other than public.
From
David Fetter
Date:
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