9.5 Import foreign schema error with user defined type in schema other than public.

Started by reiner peterkealmost 11 years ago4 messagesbugs
Jump to latest
#1reiner peterke
zedaardv@drizzle.com

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 …

server information

local server:
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.5devel on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

CONFIGURE = '--prefix=/usr/local/postgres/9.5' '--with-perl' '--with-python' '--with-openssl' 'CFLAGS=-DLINUX_OOM_SCORE_ADJ=0'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -DLINUX_OOM_SCORE_ADJ=0

remote server

version
--------------------------------------------------------------------------------------------------------------
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 = '--with-tclconfig=/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=/usr/local/postgres/9.4' 'CFLAGS=-DLINUX_OOM_SCORE_ADJ=0' '--with-libs=/usr/lib' '--with-includes=/usr/include' '--with-uuid=/usr/lib'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -DLINUX_OOM_SCORE_ADJ=0

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 = time);
create table timerange_test(tid int, data text, t timerange, ta timerange[]);

in schema test
create type timerange as range (subtype = 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
describe objects.
\d
List of relations
Schema | Name | Type | Owner
----------+----------------+---------------+----------
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
--------+--------------------+-----------+--------------------
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’)

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: reiner peterke (#1)
Re: 9.5 Import foreign schema error with user defined type in schema other than public.

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.

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#3reiner peterke
zedaardv@drizzle.com
In reply to: Tom Lane (#2)
Re: 9.5 Import foreign schema error with user defined type in schema other than public.

On Jun 9, 2015, at 3:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

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

Ok i missed that

Thanks,

reiner

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

#4David Fetter
david@fetter.org
In reply to: reiner peterke (#1)
Re: 9.5 Import foreign schema error with user defined type in schema other than public.

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs