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

From: reiner peterke <zedaardv(at)drizzle(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: 9.5 Import foreign schema error with user defined type in schema other than public.
Date: 2015-06-09 08:56:26
Message-ID: 46A815E3-DE1A-4A70-AAED-F395C24DE19C@drizzle.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message krzysztof.czajka 2015-06-09 11:29:46 BUG #13420: Array function contains with null
Previous Message nnd 2015-06-09 07:11:00 BUG #13419: could not adopt C locale