Thread: IMPORT FOREIGN SCHEMA statement

IMPORT FOREIGN SCHEMA statement

From

Ronan Dunklau

Date:

23 May 2014, 23:08:19

Hello,

Since my last proposal didn't get any strong rebuttal, please find attached a
more complete version of the IMPORT FOREIGN SCHEMA statement.

I tried to follow the SQL-MED specification as closely as possible.

This adds discoverability to foreign servers. The structure of the statement
as I understand it is simple enough:

IMPORT FOREIGN SCHEMA remote_schema FROM SERVER some_server [ (LIMIT TO |
EXCEPT) table_list ] INTO local_schema.

The import_foreign_schema patch adds the infrastructure, and a new FDW
routine:

typedef List *(*ImportForeignSchema_function) (ForeignServer *server,
ImportForeignSchemaStmt * parsetree);

This routine must return a list of CreateForeignTableStmt mirroring whatever
tables were found on the remote side, which will then be executed.

The import_foreign_schema_postgres_fdw patch proposes an implementation of
this API for postgres_fdw. It will import a foreign schema using the right
types as well as nullable information.

Regarding documentation, I don't really know where it should have been put. If
I missed something, let me know and I'll try to correct it.


--
Ronan Dunklau
http://dalibo.com - http://dalibo.org

On Mon, Jun 16, 2014 at 11:28 AM, Michael Paquier <michael.paquier@gmail.com> wrote:

On Mon, May 26, 2014 at 6:23 AM, Ronan Dunklau <ronan.dunklau@dalibo.com> wrote:
> Le dimanche 25 mai 2014 12:41:18 David Fetter a écrit :
>> On Fri, May 23, 2014 at 10:08:06PM +0200, Ronan Dunklau wrote:
>> > Hello,
>> >
>> > Since my last proposal didn't get any strong rebuttal, please find
>> > attached a more complete version of the IMPORT FOREIGN SCHEMA statement.
>>
>> Thanks!
>>
>> Please to send future patches to this thread so people can track them
>> in their mail.
>
> I'll do.
>
> I didn't for the previous one because it was a few months ago, and no patch
> had been added to the commit fest.
>
>>
>> > I tried to follow the SQL-MED specification as closely as possible.
>> >
>> > This adds discoverability to foreign servers. The structure of the
>> > statement as I understand it is simple enough:
>> >
>> > IMPORT FOREIGN SCHEMA remote_schema FROM SERVER some_server [ (LIMIT TO |
>> > EXCEPT) table_list ] INTO local_schema.
>> >
>> > The import_foreign_schema patch adds the infrastructure, and a new FDW
>> > routine:
>> >
>> > typedef List *(*ImportForeignSchema_function) (ForeignServer *server,
>> > ImportForeignSchemaStmt * parsetree);
>> >
>> > This routine must return a list of CreateForeignTableStmt mirroring
>> > whatever tables were found on the remote side, which will then be
>> > executed.
>> >
>> > The import_foreign_schema_postgres_fdw patch proposes an implementation of
>> > this API for postgres_fdw. It will import a foreign schema using the right
>> > types as well as nullable information.
>>
>> In the case of PostgreSQL, "the right types" are obvious until there's
>> a user-defined one. What do you plan to do in that case ?
>>
>
> The current implementation fetches the types as regtype, and when receiving a
> custom type, two things can happen:
>
> - the type is defined locally: everything will work as expected
> - the type is not defined locally: the conversion function will fail, and
> raise an error of the form: ERROR: type "schema.typname" does not exist

Just wondering: what about the case where the same data type is
defined on both local and remote, but with *different* definitions? Is
it the responsibility of the fdw to check for type incompatibilities?

Logically, should be.

Just wondering, cant we extend the above proposed function typedef List *(*ImportForeignSchema_function) (ForeignServer *server, ImportForeignSchemaStmt * parsetree); be changed a bit to give exact type definitions from the remote side as well?

Regards,

Atri

Regards,

Atri

l'apprenant

Re: IMPORT FOREIGN SCHEMA statement

From

Ronan Dunklau

Date:

16 June 2014, 09:41:56

Le lundi 16 juin 2014 11:32:38 Atri Sharma a écrit :
> On Mon, Jun 16, 2014 at 11:28 AM, Michael Paquier <michael.paquier@gmail.com
> > wrote:
> > Just wondering: what about the case where the same data type is
> > defined on both local and remote, but with *different* definitions? Is
> > it the responsibility of the fdw to check for type incompatibilities?
>
> Logically, should be.
>

This is akin to what Stephen proposed, to allow IMPORT FOREIGN SCHEMA to also
import types.

The problem with checking if the type is the same is deciding where to stop.
For composite types, sure it should be easy. But what about built-in types ?
Or types provided by an extension / a native library ? These could theorically
change from one release to another.

> Just wondering, cant we extend the above proposed function  typedef List
> *(*ImportForeignSchema_function) (ForeignServer *server,
> ImportForeignSchemaStmt * parsetree); be changed a bit to give exact type
> definitions from the remote side as well?

I toyed with this idea, but the more I think about it the less I'm sure what
the API should look like, should we ever decide to go beyond the standard and
import more than tables. Should the proposed function return value be changed
to void, letting the FDW execute any DDL statement ? The advantage of
returning a list of statement was to make it clear that tables should be
imported, and letting the core enforce "INTO local_schema" part of the clause.

I would prefer if the API is limited by design to import tables. This
limitation can always be bypassed by executing arbitrary statements before
returning the list of ImportForeignSchemaStmt*.

For the postgres_fdw specific case, we could add two IMPORT options (since it
looked like we had a consensus on this):
- import_types- check_types

Import types would import simple, composite types, issuing the corresponding
statements before returning to core.

Check types would compare the local and remote definition for composite types.
For types installed by an extension, it would check that the local type has
also been created by an extension of the same name, installed in the same
schema, raising a warning if the local and remote version differ.
For built-in types, a warning would be raised if the local and remote versions
of PostgreSQL differ.

However, I don't know what we should be doing about types located in a
different schema. For example, if the remote table s1.t1 has a column of
composite type s2.typ1, should we import typ1 in s1 ? In S2, optionnaly
creating the non-existing schema ? Raise an error ?

Regards,

--
Ronan Dunklau
http://dalibo.com - http://dalibo.org

Re: IMPORT FOREIGN SCHEMA statement

From

Michael Paquier

Date:

16 June 2014, 10:08:03

On Sat, May 24, 2014 at 5:08 AM, Ronan Dunklau <ronan.dunklau@dalibo.com> wrote:
> Hello,
>
> Since my last proposal didn't get any strong rebuttal, please find attached a
> more complete version of the IMPORT FOREIGN SCHEMA statement.
>
> I tried to follow the SQL-MED specification as closely as possible.
>
> This adds discoverability to foreign servers. The structure of the statement
> as I understand it is simple enough:
>
> IMPORT FOREIGN SCHEMA remote_schema FROM SERVER some_server [ (LIMIT TO |
> EXCEPT) table_list ] INTO local_schema.
>
> The import_foreign_schema patch adds the infrastructure, and a new FDW
> routine:
>
> typedef List *(*ImportForeignSchema_function) (ForeignServer *server,
> ImportForeignSchemaStmt * parsetree);
>
> This routine must return a list of CreateForeignTableStmt mirroring whatever
> tables were found on the remote side, which will then be executed.
>
> The import_foreign_schema_postgres_fdw patch proposes an implementation of
> this API for postgres_fdw. It will import a foreign schema using the right
> types as well as nullable information.
>
> Regarding documentation, I don't really know where it should have been put. If
> I missed something, let me know and I'll try to correct it.

I have been playing a bit with this patch... And got a couple of comments.

1) Perhaps somebody could guide me to a better spec but by looking at
the spec here => http://farrago.sourceforge.net/design/sqlmed.html, it
seems that the FROM SERVER clause is misplaced, it should be placed
after the table list.
2) The query I am seeing on this spec offers the possiblitily to query
TABLE_NAME LIKE 'pattern'. Is this part of the spec as well? If yes,
are you planning to add it as well. I imagine that this could be quite
handy for users importing from a foreign schema tables that have the
same prefix name for example. ImportForeignSchemaRestrictionType could
be extended with an IMPORT_LIKE type. Extending a bit the spec...
There could be a list of LIKE patterns, this matches more your code by
adding all of them in table_names. I am not voting to add TABLE_NAME
in the list of reserved keywords though, so something like "TABLE LIKE
'pattern'" would be nice.
3) This has been already raised in this thread, but something missing
with this patch is the possiblity to pass options when executing an
import. This could allow a FDW to do more fine-grained operations on
the underlying objects of the table. There would be two level of
options: table level and schema level. For example, with the current
feature it is not possible to rename imported tables on-the-fly. I
imagine that this would be useful.
4) Something not mandatory with the core patch but always nice to
have: tab completion for this command in psql.
5) The error message returned to user when import fails because of a
missing type is rather unfriendly. Let's imagine with two nodes and
postgres_fdw... Node 1:
create type toto as (a int, b int);
create table toto_tab (a toto);
Node 2:
=# import foreign schema public from server postgres_server into public;
ERROR:  42704: type "public.toto" does not exist
LOCATION:  parseTypeString, parse_type.c:797
I would rather imagine something like "IMPORT failed because of
missing type defined on remote but not locally".
6) Import does not fail if a table specified in LIMIT TO is not
defined on remote-side:
=# import foreign schema public from server postgres_server limit to
(tab_not_there) into public;
IMPORT FOREIGN SCHEMA
=# \d
No relations found.
7) A small thing: code comments do not respect the project format:
http://www.postgresql.org/docs/9.1/interactive/source-format.html
8) In fetch_remote_tables@postgres_fdw.c, you are missing materialized views:
WHERE relkind IN ('r', 'v', 'f')
9) The changes in pg_type.h adding new OID defines should be a
separate patch IMO
10) Code has many whitespaces.
11) Sometimes the import goes strangely:
11-1) After some testing I noticed that tables with incorrect names
were imported when using LIMIT TO. For example on remote a table
called "ab" is present, IMPORT SCHEMA LIMIT TO 'other_ab' created a
local entry called "other_ab" while it should create nothing.
11-2) Tables with empty names can be created locally. On foreign server:
=# \d        List of relationsSchema |   Name   | Type  | Owner
--------+----------+-------+--------public | aa       | table | ioltaspublic | toto_tab | table | ioltas
(2 rows)
On local node:
=# import foreign schema public from server postgres_server limit to
(toto_tab, "NULL", aa) into public;
-- (Forget about NULL here, I could reproduce it without as well)
IMPORT FOREIGN SCHEMA
Time: 13.589 ms
=# \d            List of relationsSchema |   Name   |     Type      | Owner
--------+----------+---------------+--------public |          | foreign table | ioltaspublic | toto_tab | foreign table
|ioltas 
(2 rows)
Some more testing showed me that as well:
=# \d
                                                               List
of relationsSchema |                                                              Name
                                                 |     Type      |
Owner

--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+--------public
|

                                                 | foreign table |
ioltaspublic | toto_tab

                                                 | foreign table |
ioltaspublic |
\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F
| foreign table | ioltas

That's all I have for now.
Regards,
--
Michael

Re: IMPORT FOREIGN SCHEMA statement

From

Stephen Frost

Date:

16 June 2014, 22:13:53

* Ronan Dunklau (ronan.dunklau@dalibo.com) wrote:
> The problem with checking if the type is the same is deciding where to stop.
> For composite types, sure it should be easy. But what about built-in types ?

Haven't we already decided to trust these based on server version
information?  Look at what quals we find acceptable to push down to the
remote side...  Certainly, composites built off of built-in types should
work.

> Or types provided by an extension / a native library ? These could theorically
> change from one release to another.

This is definitely an issue which we really need to figure out how to
address.  I don't have any great ideas off-hand, but it feels like we'll
need a catalog and appropriate SQL-fu to allow users and extensions to
add other types and functions which can be pushed down..  For example,
it'd be great if PostGIS queries could be pushed down to the remote
server- and have that set up by the postgis extension on installation
(perhaps via a call-back hook which gives the extension a handle to the
remote server where it could interrogate the server and then a way to
store the information about what is allowed to be pushed to the remote
side, and how?).

That's certainly a rather complicated bit to address and I don't think
we should hold this up for that- but let's definitely be thinking about
how to add these things later and try to avoid putting anything in place
which would cause problems for us later...

Will try to come back to the rest of your questions later.. :)
Thanks,
    Stephen

Re: IMPORT FOREIGN SCHEMA statement

From

Ronan Dunklau

Date:

16 June 2014, 22:36:13

Le lundi 16 juin 2014 16:07:51 Michael Paquier a écrit :
> On Sat, May 24, 2014 at 5:08 AM, Ronan Dunklau <ronan.dunklau@dalibo.com>
wrote:
> > Hello,
> >
> > Since my last proposal didn't get any strong rebuttal, please find
> > attached a more complete version of the IMPORT FOREIGN SCHEMA statement.
> >
> > I tried to follow the SQL-MED specification as closely as possible.
> >
> > This adds discoverability to foreign servers. The structure of the
> > statement as I understand it is simple enough:
> >
> > IMPORT FOREIGN SCHEMA remote_schema FROM SERVER some_server [ (LIMIT TO |
> > EXCEPT) table_list ] INTO local_schema.
> >
> > The import_foreign_schema patch adds the infrastructure, and a new FDW
> > routine:
> >
> > typedef List *(*ImportForeignSchema_function) (ForeignServer *server,
> > ImportForeignSchemaStmt * parsetree);
> >
> > This routine must return a list of CreateForeignTableStmt mirroring
> > whatever tables were found on the remote side, which will then be
> > executed.
> >
> > The import_foreign_schema_postgres_fdw patch proposes an implementation of
> > this API for postgres_fdw. It will import a foreign schema using the right
> > types as well as nullable information.
> >
> > Regarding documentation, I don't really know where it should have been
> > put. If I missed something, let me know and I'll try to correct it.
>
> I have been playing a bit with this patch... And got a couple of comments.
>
> 1) Perhaps somebody could guide me to a better spec but by looking at
> the spec here => http://farrago.sourceforge.net/design/sqlmed.html, it
> seems that the FROM SERVER clause is misplaced, it should be placed
> after the table list.

You're right, I misplaced it. Corrected in the attached patch.
The spec I'm using is the SQL:201X linked from this page of the wiki:
http://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F

> 2) The query I am seeing on this spec offers the possiblitily to query
> TABLE_NAME LIKE 'pattern'. Is this part of the spec as well? If yes,
> are you planning to add it as well. I imagine that this could be quite
> handy for users importing from a foreign schema tables that have the
> same prefix name for example. ImportForeignSchemaRestrictionType could
> be extended with an IMPORT_LIKE type. Extending a bit the spec...
> There could be a list of LIKE patterns, this matches more your code by
> adding all of them in table_names. I am not voting to add TABLE_NAME
> in the list of reserved keywords though, so something like "TABLE LIKE
> 'pattern'" would be nice.

>From the spec I have been reading, the import qualification is defined as :

    <table name list> ::= <table name> [ { <comma> <table name> }... ]

where table name is defined as a simple table name, without any qualifier.

It seems to me the example you linked extended the standard with this
particular implementation. I don't think it would be a problem to add such a
syntax later. In the meantime, FDW implementors can abuse the specification by
implementing a DSL in the table name, for example:  LIMIT TO ("LIKE
'pattern*'");

> 3) This has been already raised in this thread, but something missing
> with this patch is the possiblity to pass options when executing an
> import. This could allow a FDW to do more fine-grained operations on
> the underlying objects of the table. There would be two level of
> options: table level and schema level. For example, with the current
> feature it is not possible to rename imported tables on-the-fly. I
> imagine that this would be useful.

The attached patch implements options. Now, we should think about what options
may be desirable for postgres_fdw :)

I don't understand your point about table-level and schema-level options,
though. There is no concept of "foreign schema" allowing to set options on it,
AFAIK.

Renaming imported tables on the fly should, IMO, be done after the import
statement. Since it is transactional, nothing prevents you from running the
ALTER TABLE statements after that.

> 4) Something not mandatory with the core patch but always nice to
> have: tab completion for this command in psql.

I will look into it.

> 5) The error message returned to user when import fails because of a
> missing type is rather unfriendly. Let's imagine with two nodes and
> postgres_fdw... Node 1:
> create type toto as (a int, b int);
> create table toto_tab (a toto);
> Node 2:
> =# import foreign schema public from server postgres_server into public;
> ERROR:  42704: type "public.toto" does not exist
> LOCATION:  parseTypeString, parse_type.c:797
> I would rather imagine something like "IMPORT failed because of
> missing type defined on remote but not locally".

I simply prepended the suggested  error message to the previous one:

# IMPORT FOREIGN SCHEMA import_source FROM SERVER remote1 INTO
import_destination;
ERROR:  IMPORT of table t1 failed because of missing type defined on remote but
not locally: type "public.typ1" does not exist
Time: 11,305 ms

> 6) Import does not fail if a table specified in LIMIT TO is not
> defined on remote-side:
> =# import foreign schema public from server postgres_server limit to
> (tab_not_there) into public;
> IMPORT FOREIGN SCHEMA
> =# \d
> No relations found.

Thanks, this is corrected in the attached patch. The error detection code is
O(n²), but I assumed LIMIT TO clauses should be small enough for it not to be
a problem.

> 7) A small thing: code comments do not respect the project format:
> http://www.postgresql.org/docs/9.1/interactive/source-format.html

This should be fixed too, sorry for the inconvenience.


> 8) In fetch_remote_tables@postgres_fdw.c, you are missing materialized
> views: WHERE relkind IN ('r', 'v', 'f')

Fixed in the attached patch.


> 9) The changes in pg_type.h adding new OID defines should be a
> separate patch IMO

It has been broken into its separate patch.

> 10) Code has many whitespaces.

I hope this is corrected in the attached patch, if not, could you please point
me at specific examples ?

> 11) Sometimes the import goes strangely:
> 11-1) After some testing I noticed that tables with incorrect names
> were imported when using LIMIT TO. For example on remote a table
> called "ab" is present, IMPORT SCHEMA LIMIT TO 'other_ab' created a
> local entry called "other_ab" while it should create nothing
> 11-2) Tables with empty names can be created locally. On foreign server:
> =# \d
>          List of relations
>  Schema |   Name   | Type  | Owner
> --------+----------+-------+--------
>  public | aa       | table | ioltas
>  public | toto_tab | table | ioltas
> (2 rows)
> On local node:
> =# import foreign schema public from server postgres_server limit to
> (toto_tab, "NULL", aa) into public;
> -- (Forget about NULL here, I could reproduce it without as well)
> IMPORT FOREIGN SCHEMA
> Time: 13.589 ms
> =# \d
>              List of relations
>  Schema |   Name   |     Type      | Owner
> --------+----------+---------------+--------
>  public |          | foreign table | ioltas
>  public | toto_tab | foreign table | ioltas
> (2 rows)
> Some more testing showed me that as well:
> =# \d
>
>                                                                 List
> of relations
>  Schema |
>                                                                Name
>
>                                                   |     Type      |
>
> Owner
> --------+-------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> -----------------------------------+---------------+-------- public |
>
>                                                   | foreign table |
>
> ioltas
>  public | toto_tab
>
>                                                   | foreign table |
>
> ioltas
>  public |F\x7F\x7
> F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7
> F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7
> F\x7F\x7F\x7F\x7F\x7F\x7F
> | foreign table | ioltas
> \x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7

This seems to be related to re-using the table-name between invocations. The
attached patch should fix point 2. As for point 1, I don't know the cause for
it. Do you have a reproducible test case ?


> That's all I have for now.

Thank you very much for this review.

> Regards,

--
Ronan Dunklau
http://dalibo.com - http://dalibo.org

On Thu, Jun 19, 2014 at 11:00 PM, Michael Paquier <michael.paquier@gmail.com> wrote:

>> This seems to be related to re-using the table-name between invocations. The
>> attached patch should fix point 2. As for point 1, I don't know the cause for
>> it. Do you have a reproducible test case ?
> Sure. I'll try harder when looking in more details at the patch for
> postgres_fdw :)
With v2, I have tried randomly some of the scenarios of v1 plus some
extras, but was not able to reproduce it.

> I'll look into the patch for postgres_fdw later.
And here are some comments about it, when applied on top of the other 2 patches.
1) Code compiles without warning, regression tests pass.
2) In fetch_remote_tables, the palloc for the parameters should be
done after the number of parameters is determined. In the case of
IMPORT_ALL, some memory is wasted for nothing.
3) Current code is not able to import default expressions for a table.
A little bit of processing with pg_get_expr would be necessary:
select pg_catalog.pg_get_expr(adbin, adrelid) from pg_attrdef;
There are of course bonus cases like SERIAL columns coming immediately
to my mind but it would be possible to determine if a given column is
serial with pg_get_serial_sequence.
This would be a good addition for the FDW IMO.
4) The same applies of course to the constraint name: CREATE FOREIGN
TABLE foobar (a int CONSTRAINT toto NOT NULL) for example.
5) A bonus idea: enable default expression obtention and null/not null
switch by default but add options to disable their respective
obtention.
6) Defining once PGFDW_IMPORTRESULT_NUMCOLS at the top of
postgres_fdw.c without undefining would be perfectly fine.
7) In postgresImportForeignSchema, the palloc calls and the
definitions of the variables used to save the results should be done
within the for loop.
8) At quick glance, the logic of postgresImportForeignSchema looks
awkward... I'll have a second look with a fresher mind later on this
one.

While having a second look at the core patch, I have found myself re-hacking it, fixing a couple of bugs and adding things that have been missing in the former implementation:

- Deletions of unnecessary structures to simplify code and make it cleaner

- Fixed a bug related to the management of local schema name. A FDW was free to set the schema name where local tables are created, this should not be the case.

- Improved documentation, examples and other things, fixed doc padding for example

- Added some missing stuff in equalfuncs.c and copyfuncs.c

- Some other things.

With that, core patch looks pretty nice actually, and I think that we should let a committer have a look at this part at least for this CF.

Also, the postgres_fdw portion has been updated based on the previous core patch modified, using a version that Ronan sent me, which has addressed the remarks I sent before. This patch is still lacking documentation, some cleanup, and regression tests are broken, but it can be used to test the core feature. I unfortunately don't have much time today but I am sending this patch either way to let people play with IMPORT SCHEMA if I don't come back to it before.

Regards,

--
Michael

Attachment

Re: IMPORT FOREIGN SCHEMA statement

From

Ronan Dunklau

Date:

30 June 2014, 12:01:58

Le lundi 30 juin 2014 16:43:38 Michael Paquier a écrit :
> On Thu, Jun 19, 2014 at 11:00 PM, Michael Paquier <michael.paquier@gmail.com
> > wrote:
> > >> This seems to be related to re-using the table-name between
> >
> > invocations. The
> >
> > >> attached patch should fix point 2. As for point 1, I don't know the
> >
> > cause for
> >
> > >> it. Do you have a reproducible test case ?
> > >
> > > Sure. I'll try harder when looking in more details at the patch for
> > > postgres_fdw :)
> >
> > With v2, I have tried randomly some of the scenarios of v1 plus some
> > extras, but was not able to reproduce it.
> >
> > > I'll look into the patch for postgres_fdw later.
> >
> > And here are some comments about it, when applied on top of the other 2
> > patches.
> > 1) Code compiles without warning, regression tests pass.
> > 2) In fetch_remote_tables, the palloc for the parameters should be
> > done after the number of parameters is determined. In the case of
> > IMPORT_ALL, some memory is wasted for nothing.
> > 3) Current code is not able to import default expressions for a table.
> > A little bit of processing with pg_get_expr would be necessary:
> > select pg_catalog.pg_get_expr(adbin, adrelid) from pg_attrdef;
> > There are of course bonus cases like SERIAL columns coming immediately
> > to my mind but it would be possible to determine if a given column is
> > serial with pg_get_serial_sequence.
> > This would be a good addition for the FDW IMO.
> > 4) The same applies of course to the constraint name: CREATE FOREIGN
> > TABLE foobar (a int CONSTRAINT toto NOT NULL) for example.
> > 5) A bonus idea: enable default expression obtention and null/not null
> > switch by default but add options to disable their respective
> > obtention.
> > 6) Defining once PGFDW_IMPORTRESULT_NUMCOLS at the top of
> > postgres_fdw.c without undefining would be perfectly fine.
> > 7) In postgresImportForeignSchema, the palloc calls and the
> > definitions of the variables used to save the results should be done
> > within the for loop.
> > 8) At quick glance, the logic of postgresImportForeignSchema looks
> > awkward... I'll have a second look with a fresher mind later on this
> > one.
>
> While having a second look at the core patch, I have found myself
> re-hacking it, fixing a couple of bugs and adding things that have been
> missing in the former implementation:
> - Deletions of unnecessary structures to simplify code and make it cleaner
> - Fixed a bug related to the management of local schema name. A FDW was
> free to set the schema name where local tables are created, this should not
> be the case.
> - Improved documentation, examples and other things, fixed doc padding for
> example
> - Added some missing stuff in equalfuncs.c and copyfuncs.c
> - Some other things.
> With that, core patch looks pretty nice actually, and I think that we
> should let a committer have a look at this part at least for this CF.
>
> Also, the postgres_fdw portion has been updated based on the previous core
> patch modified, using a version that Ronan sent me, which has addressed the
> remarks I sent before. This patch is still lacking documentation, some
> cleanup, and regression tests are broken, but it can be used to test the
> core feature. I unfortunately don't have much time today but I am sending
> this patch either way to let people play with IMPORT SCHEMA if I don't come
> back to it before.

The regression tests fail because of a typo in pg_type.h: BOOLARRAYOID should
be defined to 1000, not 1003 (which clashes against NAMEARRAYOID).

What do you think should be documented, and where ?


> Regards,

--
Ronan Dunklau
http://dalibo.com - http://dalibo.org

Attachment

Re: IMPORT FOREIGN SCHEMA statement

From

Michael Paquier

Date:

30 June 2014, 15:54:33

On Mon, Jun 30, 2014 at 6:01 PM, Ronan Dunklau <ronan.dunklau@dalibo.com> wrote:

BOOLARRAYOID should be defined to 1000, not 1003 (which clashes against NAMEARRAYOID).

Oops, a bad copy-paste. Thanks for catching that.

I had a more detailed look at the postgres_fdw patch:

1) Having an example of options usable with postgres_fdw would be a good thing to test the FDW API more extensively. What about simply disable_nulls to disable NULL/NOT NULL creation on the tables imported.

2) (Should have noticed that earlier, sorry) I don't see any advantages but only complications in using PQexecParams to launch the queries checking schema existence remotely and fetching back table definitions as we use them only once per import. Why not removing the parameter part and build only plain query strings using StringInfo? This would have the merit to really simplify fetch_remote_tables.

3) If you add an option, let's get rid of PGFDW_IMPORTRESULT_NUMCOLS as the number of result columns would change.

4) Instead of using a double-layer of arrays when processing results, I think that it would make the whole process more readable to have one array for each result column (attname, atttype, atttypmod, attnotnull) initialized each time a new row is processed and then process through them to get the array items. I imagine that a small function doing the array parsing called on each array result would bring more readability as well to the process flow.

5) This could be costly with large sets of tables in LIMIT TO... I imagine that we can live with this O(n log(n)) process as LIMIT TO should not get big..

        foreach(lc1, table_names)
        {
            found = false;
            looked_up = ((RangeVar *) lfirst(lc1))->relname;
            foreach(lc2, tables)

What do you think should be documented, and where ?

Two things coming to my mind:

- If postgres_fdw can use options with IMPORT, they should be explicitly listed in the section "FDW Options of postgres_fdw".

- Documenting that postgres_fdw support IMPORT FOREIGN SCHEMA. A simple paragraph in the main section of postgres_fdw containing descriptions would be enough.

Once we get that done, I'll do another round of review on this patch and I think that we will be able to mark it as ready for committer.

Regards,

--
Michael

Re: IMPORT FOREIGN SCHEMA statement

From

Michael Paquier

Date:

01 July 2014, 09:36:33

On Mon, Jun 30, 2014 at 9:54 PM, Michael Paquier <michael.paquier@gmail.com> wrote:

Once we get that done, I'll do another round of review on this patch and I think that we will be able to mark it as ready for committer.

After sleeping on it, I have put my hands on the postgres_fdw portion and came up with a largely simplified flow, resulting in the patch attached. The following things are done:

- Removal of all the array stuff, resulting in a more simplified, and readable code, without performance impact.

- Removal of the parameter stuff to simplify code

- Addition of an option in postgres_fdw to ignore NULL/NOT NULL

- Addition of docs

- Fixed a bug related to the use of ::regtype, let's use directly typmod and typname in pg_type instead.

- The addition of new OID defines is now unnecessary

Ronan, what do you think of those patches? I have nothing more to add, and I think that they should be looked by a committer. Particularly the FDW API that is perhaps not the best fit, but let's see some extra opinions about that.

--
Michael

Attachment

Re: IMPORT FOREIGN SCHEMA statement

From

Albe Laurenz

Date:

01 July 2014, 09:59:57

Michael Paquier wrote:
> After sleeping on it, I have put my hands on the postgres_fdw portion and came up with a largely
> simplified flow, resulting in the patch attached.

[...]

> Ronan, what do you think of those patches? I have nothing more to add, and I think that they should be
> looked by a committer. Particularly the FDW API that is perhaps not the best fit, but let's see some
> extra opinions about that.

I looked the the API and ist documentation, and while I saw no problem with the API,
I think that the documentation still needs some attention:

It mentions a "local_schema", which doesn't exist (any more?).
It should be mentioned that the CreateForeignTableStmt's
base.relation->schemaname should be set to NULL.
Also, it would be nice to find a few words for "options",
maybe explaining a potential application.

Yours,
Laurenz Albe

Re: IMPORT FOREIGN SCHEMA statement

From

Ronan Dunklau

Date:

01 July 2014, 10:24:32

Le mardi 1 juillet 2014 06:59:49 Albe Laurenz a écrit :
> Michael Paquier wrote:
>
> > After sleeping on it, I have put my hands on the postgres_fdw portion and
> > came up with a largelysimplified flow, resulting in the patch attached.
>
>
> [...]
>
>
> > Ronan, what do you think of those patches? I have nothing more to add, and
> > I think that they should belooked by a committer. Particularly the FDW
> > API that is perhaps not the best fit, but let's see some extra opinions
> > about that.

The remote_schema parameter can be used for SQL injection. Either we should go
back to using parameters, or be extra careful. Since the remote schema is
parsed as a name, it is limited to 64 characters which is not that useful for
an SQL injection, but still.

The new query as you wrote it returns the typname (was cast to regtype before)
This is not schema qualified, and will fail when importing tables with columns
from a type not in search_path.

The regression tests don't pass: a user name is hard-coded in the result of
DROP USER MAPPING. Should we expect the tests to be run as postgres ?

>
>
> I looked the the API and ist documentation, and while I saw no problem with
> the API,
> I think that the documentation still needs some attention:
>
> It mentions a "local_schema", which doesn't exist (any more?).
> It should be mentioned that the CreateForeignTableStmt's
> base.relation->schemaname should be set to NULL.
> Also, it would be nice to find a few words for "options",
> maybe explaining a potential application.
>
> Yours,
> Laurenz Albe

--
Ronan Dunklau
http://dalibo.com - http://dalibo.org

Re: IMPORT FOREIGN SCHEMA statement

From

Michael Paquier

Date:

01 July 2014, 15:10:00

On Tue, Jul 1, 2014 at 4:23 PM, Ronan Dunklau <ronan.dunklau@dalibo.com> wrote:

The remote_schema parameter can be used for SQL injection. Either we should go
back to using parameters, or be extra careful. Since the remote schema is
parsed as a name, it is limited to 64 characters which is not that useful for
an SQL injection, but still.

Yes, right, I completely forgot that. IMPORT SCHEMA could be used by a non-superuser so controlling only the size of relation name is not enough.

The new query as you wrote it returns the typname (was cast to regtype before)
This is not schema qualified, and will fail when importing tables with columns
from a type not in search_path.

Hm. The current solution with simply LookupTypeNameOid is more elegant than relying on InputFunctionCall to fetch a Datum, that is then converted back into TypeName... In all cases I am wondering about the use of regtype where the same type name is used across multiple schemas. We should perhaps document correctly that search_path influences the custom type chosen when rebuilding foreign tables and that postgres_fdw does its best but that it may not be compatible with type on foreign server.

The regression tests don't pass: a user name is hard-coded in the result of
DROP USER MAPPING. Should we expect the tests to be run as postgres?

I think that we need a cleaner solution for this test case, I've let it for the time being but a make installcheck would let an extra database as it cannot be removed in postgres_fdw.sql (an extra test case just to clean up would work but I don't think that it is worth the complication). We could abuse of search_path not tracking types located on certain schemas to trigger this error :)

Want to give a shot on the following things? I wouldn't mind changing back the query construction part :)

--
Michael

Re: IMPORT FOREIGN SCHEMA statement

From

Ronan Dunklau

Date:

03 July 2014, 09:37:36

Le mardi 1 juillet 2014 21:09:55 Michael Paquier a écrit :
> On Tue, Jul 1, 2014 at 4:23 PM, Ronan Dunklau <ronan.dunklau@dalibo.com>
>
> wrote:
> > The remote_schema parameter can be used for SQL injection. Either we
> > should go
> > back to using parameters, or be extra careful. Since the remote schema is
> > parsed as a name, it is limited to 64 characters which is not that useful
> > for
> > an SQL injection, but still.
>
> Yes, right, I completely forgot that. IMPORT SCHEMA could be used by a
> non-superuser so controlling only the size of relation name is not enough.
>

I reintroduced PQExecParams, trying to keep it simple enough.

> The new query as you wrote it returns the typname (was cast to regtype
>
> > before)
> > This is not schema qualified, and will fail when importing tables with
> > columns
> > from a type not in search_path.
>
> Hm. The current solution with simply LookupTypeNameOid is more elegant than
> relying on InputFunctionCall to fetch a Datum, that is then converted back
> into TypeName... In all cases I am wondering about the use of regtype where
> the same type name is used across multiple schemas. We should perhaps
> document correctly that search_path influences the custom type chosen when
> rebuilding foreign tables and that postgres_fdw does its best but that it
> may not be compatible with type on foreign server.

I think that it would be better to qualify the type name. The attached patch
does that by fetching the type schema name in another column, and using
LookupTypeNameOid.

>
> > The regression tests don't pass: a user name is hard-coded in the result
> > of
> > DROP USER MAPPING. Should we expect the tests to be run as postgres?
>
> I think that we need a cleaner solution for this test case, I've let it for
> the time being but a make installcheck would let an extra database as it
> cannot be removed in postgres_fdw.sql (an extra test case just to clean up
> would work but I don't think that it is worth the complication). We could
> abuse of search_path not tracking types located on certain schemas to
> trigger this error :)
>
> Want to give a shot on the following things? I wouldn't mind changing back
> the query construction part :)

Also attached in this patch:
 - more robust way for cleaning things up in regression tests
 - small documentation change for options in the fdwhandler API



> --
> Michael

--
Ronan Dunklau
http://dalibo.com - http://dalibo.org

Attachment

Re: IMPORT FOREIGN SCHEMA statement

From

Michael Paquier

Date:

03 July 2014, 10:21:28

On Thu, Jul 3, 2014 at 3:37 PM, Ronan Dunklau <ronan.dunklau@dalibo.com> wrote:
> Also attached in this patch:
>  - more robust way for cleaning things up in regression tests
>  - small documentation change for options in the fdwhandler API
Those changes look good to me. I have found some minor issues, nothing
really huge so I am updating the patches myself:
- patch 0001 contains the documentation for postgres_fdw: it should be
in 0002, which is missing its docs.
- Some word-smithing as mentioned by Albe previously.
- PQclear before throwing the error "IMPORT of table \"%s\" failed
because of missing type blablah"
With that, I am marking this patch as ready for committer.
--
Michael

On Thu, Jul 10, 2014 at 2:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Michael Paquier <michael.paquier@gmail.com> writes:
> With that, I am marking this patch as ready for committer.

I've started looking at this patch. I wonder whether it's really such
a great idea to expect the FDW to return a list of parsetrees for
CREATE FOREIGN TABLE commands; that seems like a recipe for breakage
anytime we change the parsetree representation, say add a field to
ColumnDef. The alternative I'm thinking about is to have the FDW pass
back a list of strings, which would be textual CREATE FOREIGN TABLE
commands. This seems like it'd be more robust and in most cases not
any harder for the FDW to generate; moreover, it would greatly improve
the quality of error reporting anytime there was anything wrong with
what the FDW did.

Agreed. Modifying postgres_fdw portion to do so would not take long.

As against that, you could point out that we make FDWs deal with
parsetrees when doing planning. But the important difference there
is that they're mostly *reading* the parsetrees, not building new
ones from scratch, so there's much less opportunity for errors of
omission.

Comments?

The SQL-MED spec talks only about foreign tables when importing, It would be good to keep the checks on CreateTableForeignStmt after parsing the strings, which is what I imagine server would do after taking back the list of strings from FDW before creating the objects.

Regards,

--
Michael

Re: IMPORT FOREIGN SCHEMA statement

From

Tom Lane

Date:

10 July 2014, 02:37:27

Another thing ...

The SQL-MED standard says that "IMPORT FOREIGN SCHEMA ... LIMIT TO (a,b,c)"
should throw an error if not all of a,b,c exist as tables in the remote
server.  It's rather sloppily worded, though, such that it's not clear
whether an error is also expected for "EXCEPT (a,b,c)" when not all of
those names exist.  It seems to me that the whole thing is badly thought
out and it would be better not to complain for nonexistent names in either
type of list.  The use of "LIMIT" seems to me to imply that the list is
a filter, not an exact set of names that must be present.

Comments?
        regards, tom lane

Re: IMPORT FOREIGN SCHEMA statement

From

Tom Lane

Date:

10 July 2014, 03:52:57

I wrote:
> I've started looking at this patch.  I wonder whether it's really such
> a great idea to expect the FDW to return a list of parsetrees for
> CREATE FOREIGN TABLE commands; that seems like a recipe for breakage
> anytime we change the parsetree representation, say add a field to
> ColumnDef.  The alternative I'm thinking about is to have the FDW pass
> back a list of strings, which would be textual CREATE FOREIGN TABLE
> commands.

Here's a rather-heavily-editorialized draft patch that does it like that.

This patch takes the viewpoint I espoused nearby of allowing names in
the LIMIT TO clause that aren't present on the remote server.  If we
decide we want to hew to the letter of the standard on that, I'd be
inclined to enforce this in the core code, not in individual FDWs as
the submitted patch did.  (I didn't much like that implementation
anyway, since it didn't tell you which name it was unhappy about.)

            regards, tom lane

diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 322138d..2045774 100644
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
*************** static void deparseReturningList(StringI
*** 116,122 ****
  static void deparseColumnRef(StringInfo buf, int varno, int varattno,
                   PlannerInfo *root);
  static void deparseRelation(StringInfo buf, Relation rel);
- static void deparseStringLiteral(StringInfo buf, const char *val);
  static void deparseExpr(Expr *expr, deparse_expr_cxt *context);
  static void deparseVar(Var *node, deparse_expr_cxt *context);
  static void deparseConst(Const *node, deparse_expr_cxt *context);
--- 116,121 ----
*************** deparseRelation(StringInfo buf, Relation
*** 1160,1166 ****
  /*
   * Append a SQL string literal representing "val" to buf.
   */
! static void
  deparseStringLiteral(StringInfo buf, const char *val)
  {
      const char *valptr;
--- 1159,1165 ----
  /*
   * Append a SQL string literal representing "val" to buf.
   */
! void
  deparseStringLiteral(StringInfo buf, const char *val)
  {
      const char *valptr;
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2e49ee3..093bd65 100644
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
*************** NOTICE:  NEW: (13,"test triggered !")
*** 2834,2836 ****
--- 2834,3010 ----
   (0,27)
  (1 row)

+ -- ===================================================================
+ -- test IMPORT FOREIGN SCHEMA
+ -- ===================================================================
+ CREATE SCHEMA import_source;
+ CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
+ CREATE TABLE import_source.t2 (c1 int, c2 varchar NULL, c3 text);
+ CREATE TYPE typ1 AS (m1 int, m2 varchar);
+ CREATE TABLE import_source.t3 (c1 int, c2 typ1);
+ CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+ CREATE TABLE import_source."x 5" (c1 float8);
+ ALTER TABLE import_source."x 5" DROP COLUMN c1;
+ CREATE SCHEMA import_dest1;
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
+ \det+ import_dest1
+                                      List of foreign tables
+     Schema    | Table |  Server  |                   FDW Options                   | Description
+ --------------+-------+----------+-------------------------------------------------+-------------
+  import_dest1 | t1    | loopback | (schema_name 'import_source', table_name 't1')  |
+  import_dest1 | t2    | loopback | (schema_name 'import_source', table_name 't2')  |
+  import_dest1 | t3    | loopback | (schema_name 'import_source', table_name 't3')  |
+  import_dest1 | x 4   | loopback | (schema_name 'import_source', table_name 'x 4') |
+  import_dest1 | x 5   | loopback | (schema_name 'import_source', table_name 'x 5') |
+ (5 rows)
+
+ \d import_dest1.*
+            Foreign table "import_dest1.t1"
+  Column |       Type        | Modifiers | FDW Options
+ --------+-------------------+-----------+-------------
+  c1     | integer           |           |
+  c2     | character varying | not null  |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't1')
+
+            Foreign table "import_dest1.t2"
+  Column |       Type        | Modifiers | FDW Options
+ --------+-------------------+-----------+-------------
+  c1     | integer           |           |
+  c2     | character varying |           |
+  c3     | text              |           |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't2')
+
+       Foreign table "import_dest1.t3"
+  Column |  Type   | Modifiers | FDW Options
+ --------+---------+-----------+-------------
+  c1     | integer |           |
+  c2     | typ1    |           |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't3')
+
+              Foreign table "import_dest1.x 4"
+  Column |         Type          | Modifiers | FDW Options
+ --------+-----------------------+-----------+-------------
+  c1     | double precision      |           |
+  C 2    | text                  |           |
+  c3     | character varying(42) |           |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 'x 4')
+
+     Foreign table "import_dest1.x 5"
+  Column | Type | Modifiers | FDW Options
+ --------+------+-----------+-------------
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 'x 5')
+
+ -- Options
+ CREATE SCHEMA import_dest2;
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
+   OPTIONS (import_not_null 'false');
+ \det+ import_dest2
+                                      List of foreign tables
+     Schema    | Table |  Server  |                   FDW Options                   | Description
+ --------------+-------+----------+-------------------------------------------------+-------------
+  import_dest2 | t1    | loopback | (schema_name 'import_source', table_name 't1')  |
+  import_dest2 | t2    | loopback | (schema_name 'import_source', table_name 't2')  |
+  import_dest2 | t3    | loopback | (schema_name 'import_source', table_name 't3')  |
+  import_dest2 | x 4   | loopback | (schema_name 'import_source', table_name 'x 4') |
+  import_dest2 | x 5   | loopback | (schema_name 'import_source', table_name 'x 5') |
+ (5 rows)
+
+ \d import_dest2.*
+            Foreign table "import_dest2.t1"
+  Column |       Type        | Modifiers | FDW Options
+ --------+-------------------+-----------+-------------
+  c1     | integer           |           |
+  c2     | character varying |           |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't1')
+
+            Foreign table "import_dest2.t2"
+  Column |       Type        | Modifiers | FDW Options
+ --------+-------------------+-----------+-------------
+  c1     | integer           |           |
+  c2     | character varying |           |
+  c3     | text              |           |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't2')
+
+       Foreign table "import_dest2.t3"
+  Column |  Type   | Modifiers | FDW Options
+ --------+---------+-----------+-------------
+  c1     | integer |           |
+  c2     | typ1    |           |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 't3')
+
+              Foreign table "import_dest2.x 4"
+  Column |         Type          | Modifiers | FDW Options
+ --------+-----------------------+-----------+-------------
+  c1     | double precision      |           |
+  C 2    | text                  |           |
+  c3     | character varying(42) |           |
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 'x 4')
+
+     Foreign table "import_dest2.x 5"
+  Column | Type | Modifiers | FDW Options
+ --------+------+-----------+-------------
+ Server: loopback
+ FDW Options: (schema_name 'import_source', table_name 'x 5')
+
+ -- Check LIMIT TO and EXCEPT
+ CREATE SCHEMA import_dest3;
+ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
+   FROM SERVER loopback INTO import_dest3;
+ \det+ import_dest3
+                                      List of foreign tables
+     Schema    | Table |  Server  |                  FDW Options                   | Description
+ --------------+-------+----------+------------------------------------------------+-------------
+  import_dest3 | t1    | loopback | (schema_name 'import_source', table_name 't1') |
+ (1 row)
+
+ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
+   FROM SERVER loopback INTO import_dest3;
+ \det+ import_dest3
+                                      List of foreign tables
+     Schema    | Table |  Server  |                   FDW Options                   | Description
+ --------------+-------+----------+-------------------------------------------------+-------------
+  import_dest3 | t1    | loopback | (schema_name 'import_source', table_name 't1')  |
+  import_dest3 | t2    | loopback | (schema_name 'import_source', table_name 't2')  |
+  import_dest3 | t3    | loopback | (schema_name 'import_source', table_name 't3')  |
+  import_dest3 | x 5   | loopback | (schema_name 'import_source', table_name 'x 5') |
+ (4 rows)
+
+ -- Assorted error cases
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3;
+ ERROR:  relation "t1" already exists
+ CONTEXT:  importing foreign table "t1"
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest3;
+ ERROR:  schema "nonesuch" is not present on foreign server "loopback"
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere;
+ ERROR:  schema "notthere" does not exist
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
+ ERROR:  server "nowhere" does not exist
+ -- Check case of a type present only on the remote server.
+ -- We can fake this by dropping the type locally in our transaction.
+ CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
+ CREATE TABLE import_source.t5 (c1 int, "Col" "Colors");
+ CREATE SCHEMA import_dest4;
+ BEGIN;
+ DROP TYPE "Colors" CASCADE;
+ NOTICE:  drop cascades to table import_source.t5 column Col
+ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
+   FROM SERVER loopback INTO import_dest4;  -- ERROR
+ ERROR:  type "public.Colors" does not exist
+ LINE 3:   "Col" public."Colors"
+                 ^
+ QUERY:  CREATE FOREIGN TABLE t5 (
+   c1 integer,
+   "Col" public."Colors"
+ ) SERVER loopback
+ OPTIONS (schema_name 'import_source', table_name 't5');
+ CONTEXT:  importing foreign table "t5"
+ ROLLBACK;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 7dd43a9..d74fe64 100644
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
*************** static void postgresExplainForeignModify
*** 286,291 ****
--- 286,293 ----
  static bool postgresAnalyzeForeignTable(Relation relation,
                              AcquireSampleRowsFunc *func,
                              BlockNumber *totalpages);
+ static List *postgresImportForeignSchema(ImportForeignSchemaStmt *stmt,
+                             Oid serverOid);

  /*
   * Helper functions
*************** postgres_fdw_handler(PG_FUNCTION_ARGS)
*** 363,368 ****
--- 365,373 ----
      /* Support functions for ANALYZE */
      routine->AnalyzeForeignTable = postgresAnalyzeForeignTable;

+     /* Support functions for IMPORT FOREIGN SCHEMA */
+     routine->ImportForeignSchema = postgresImportForeignSchema;
+
      PG_RETURN_POINTER(routine);
  }

*************** analyze_row_processor(PGresult *res, int
*** 2565,2570 ****
--- 2570,2773 ----
  }

  /*
+  * Map a remote schema to a local one.
+  */
+ static List *
+ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
+ {
+     List       *commands = NIL;
+     bool        import_not_null = true;
+     ForeignServer *server;
+     UserMapping *mapping;
+     PGconn       *conn;
+     StringInfoData buf;
+     PGresult   *volatile res = NULL;
+     int            numrows,
+                 i;
+     ListCell   *lc;
+
+     /* Parse statement options */
+     foreach(lc, stmt->options)
+     {
+         DefElem    *def = (DefElem *) lfirst(lc);
+
+         if (strcmp(def->defname, "import_not_null") == 0)
+             import_not_null = defGetBoolean(def);
+         else
+             ereport(ERROR,
+                     (errcode(ERRCODE_FDW_INVALID_OPTION_NAME),
+                      errmsg("invalid option \"%s\"", def->defname)));
+     }
+
+     /*
+      * Get connection to the foreign server.  Connection manager will
+      * establish new connection if necessary.
+      */
+     server = GetForeignServer(serverOid);
+     mapping = GetUserMapping(GetUserId(), server->serverid);
+     conn = GetConnection(server, mapping, false);
+
+     /* Create workspace for strings */
+     initStringInfo(&buf);
+
+     /* In what follows, do not risk leaking any PGresults. */
+     PG_TRY();
+     {
+         /* Check that the schema really exists */
+         appendStringInfoString(&buf, "SELECT 1 FROM pg_catalog.pg_namespace WHERE nspname = ");
+         deparseStringLiteral(&buf, stmt->remote_schema);
+
+         res = PQexec(conn, buf.data);
+         if (PQresultStatus(res) != PGRES_TUPLES_OK)
+             pgfdw_report_error(ERROR, res, conn, false, buf.data);
+
+         if (PQntuples(res) != 1)
+             ereport(ERROR,
+                     (errcode(ERRCODE_FDW_SCHEMA_NOT_FOUND),
+               errmsg("schema \"%s\" is not present on foreign server \"%s\"",
+                      stmt->remote_schema, server->servername)));
+
+         PQclear(res);
+         res = NULL;
+         resetStringInfo(&buf);
+
+         /*
+          * Fetch all table data from this schema, possibly restricted by
+          * EXCEPT or LIMIT TO.
+          *
+          * Note: because we run the connection with search_path restricted to
+          * pg_catalog, the format_type() output will always include a schema
+          * name for types in other schemas, which is what we want.
+          *
+          * Note: in principle we'd like to include a COLLATE clause where
+          * appropriate, but because the remote server might have a different
+          * set of collations than we do, it's not easy to do that.
+          */
+         appendStringInfoString(&buf,
+                                "SELECT relname, "
+                                "  attname, "
+                                "  format_type(atttypid, atttypmod), "
+                                "  attnotnull "
+                                "FROM pg_catalog.pg_class c "
+                                "  JOIN pg_catalog.pg_namespace n ON "
+                                "    c.relnamespace = n.oid "
+                                "  LEFT JOIN pg_catalog.pg_attribute a ON "
+                                "    a.attrelid = c.oid AND a.attnum > 0 "
+                                "      AND NOT a.attisdropped "
+                                "WHERE c.relkind IN ('r', 'v', 'f', 'm') "
+                                "  AND n.nspname = ");
+         deparseStringLiteral(&buf, stmt->remote_schema);
+
+         /* Apply restrictions for LIMIT TO and EXCEPT */
+         if (stmt->list_type == FDW_IMPORT_SCHEMA_LIMIT_TO ||
+             stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+         {
+             bool        first_item = true;
+
+             appendStringInfoString(&buf, " AND c.relname ");
+             if (stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+                 appendStringInfoString(&buf, "NOT ");
+             appendStringInfoString(&buf, "IN (");
+
+             /* Append list of table names within IN clause */
+             foreach(lc, stmt->table_list)
+             {
+                 RangeVar   *rv = (RangeVar *) lfirst(lc);
+
+                 if (first_item)
+                     first_item = false;
+                 else
+                     appendStringInfoString(&buf, ", ");
+                 deparseStringLiteral(&buf, rv->relname);
+             }
+             appendStringInfoString(&buf, ")");
+         }
+
+         /* Append ORDER BY at the end of query to ensure output ordering */
+         appendStringInfo(&buf, " ORDER BY c.relname, a.attnum");
+
+         /* Fetch the data */
+         res = PQexec(conn, buf.data);
+         if (PQresultStatus(res) != PGRES_TUPLES_OK)
+             pgfdw_report_error(ERROR, res, conn, false, buf.data);
+
+         /* Process results */
+         numrows = PQntuples(res);
+         for (i = 0; i < numrows; i++)
+         {
+             char       *tablename = PQgetvalue(res, i, 0);
+             bool        first_item = true;
+
+             resetStringInfo(&buf);
+             appendStringInfo(&buf, "CREATE FOREIGN TABLE %s (\n",
+                              quote_identifier(tablename));
+
+             /* Scan all rows for this table. */
+             do
+             {
+                 char       *attname = PQgetvalue(res, i, 1);
+                 char       *typename = PQgetvalue(res, i, 2);
+                 char       *attnotnull = PQgetvalue(res, i, 3);
+
+                 /* If table has no columns, we'll see nulls here */
+                 if (PQgetisnull(res, i, 1))
+                     continue;
+
+                 if (first_item)
+                     first_item = false;
+                 else
+                     appendStringInfoString(&buf, ",\n");
+
+                 appendStringInfo(&buf, "  %s %s",
+                                  quote_identifier(attname),
+                                  typename);
+
+                 /* Mark NOT NULL if necessary */
+                 if (import_not_null && attnotnull[0] == 't')
+                     appendStringInfoString(&buf, " NOT NULL");
+             }
+             while (++i < numrows &&
+                    strcmp(PQgetvalue(res, i, 0), tablename) == 0);
+
+             /* Back up, we went one row too far */
+             i--;
+
+             /*
+              * Add server name and options.  We specify remote schema and
+              * table name as options (the latter to ensure that renaming the
+              * foreign table doesn't break the association).
+              */
+             appendStringInfo(&buf, "\n) SERVER %s\nOPTIONS (",
+                              quote_identifier(server->servername));
+
+             appendStringInfoString(&buf, "schema_name ");
+             deparseStringLiteral(&buf, stmt->remote_schema);
+             appendStringInfoString(&buf, ", table_name ");
+             deparseStringLiteral(&buf, tablename);
+
+             appendStringInfoString(&buf, ");");
+
+             commands = lappend(commands, pstrdup(buf.data));
+         }
+
+         /* Clean up */
+         PQclear(res);
+         res = NULL;
+     }
+     PG_CATCH();
+     {
+         if (res)
+             PQclear(res);
+         PG_RE_THROW();
+     }
+     PG_END_TRY();
+
+     ReleaseConnection(conn);
+
+     return commands;
+ }
+
+ /*
   * Create a tuple from the specified row of the PGresult.
   *
   * rel is the local representation of the foreign table, attinmeta is
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 8aa8f1a..94eadae 100644
*** a/contrib/postgres_fdw/postgres_fdw.h
--- b/contrib/postgres_fdw/postgres_fdw.h
*************** extern void deparseDeleteSql(StringInfo
*** 73,77 ****
--- 73,78 ----
  extern void deparseAnalyzeSizeSql(StringInfo buf, Relation rel);
  extern void deparseAnalyzeSql(StringInfo buf, Relation rel,
                    List **retrieved_attrs);
+ extern void deparseStringLiteral(StringInfo buf, const char *val);

  #endif   /* POSTGRES_FDW_H */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 6187839..a6df288 100644
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
*************** UPDATE rem1 SET f2 = 'testo';
*** 609,611 ****
--- 609,663 ----

  -- Test returning a system attribute
  INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
+
+ -- ===================================================================
+ -- test IMPORT FOREIGN SCHEMA
+ -- ===================================================================
+
+ CREATE SCHEMA import_source;
+ CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
+ CREATE TABLE import_source.t2 (c1 int, c2 varchar NULL, c3 text);
+ CREATE TYPE typ1 AS (m1 int, m2 varchar);
+ CREATE TABLE import_source.t3 (c1 int, c2 typ1);
+ CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+ CREATE TABLE import_source."x 5" (c1 float8);
+ ALTER TABLE import_source."x 5" DROP COLUMN c1;
+
+ CREATE SCHEMA import_dest1;
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
+ \det+ import_dest1
+ \d import_dest1.*
+
+ -- Options
+ CREATE SCHEMA import_dest2;
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
+   OPTIONS (import_not_null 'false');
+ \det+ import_dest2
+ \d import_dest2.*
+
+ -- Check LIMIT TO and EXCEPT
+ CREATE SCHEMA import_dest3;
+ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
+   FROM SERVER loopback INTO import_dest3;
+ \det+ import_dest3
+ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
+   FROM SERVER loopback INTO import_dest3;
+ \det+ import_dest3
+
+ -- Assorted error cases
+ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3;
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest3;
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere;
+ IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
+
+ -- Check case of a type present only on the remote server.
+ -- We can fake this by dropping the type locally in our transaction.
+ CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
+ CREATE TABLE import_source.t5 (c1 int, "Col" "Colors");
+
+ CREATE SCHEMA import_dest4;
+ BEGIN;
+ DROP TYPE "Colors" CASCADE;
+ IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
+   FROM SERVER loopback INTO import_dest4;  -- ERROR
+ ROLLBACK;
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 8ace8bd..3b7fff4 100644
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
*************** ANALYZE measurement;
*** 3069,3076 ****
      For additional information, see
      <xref linkend="sql-createforeigndatawrapper">,
      <xref linkend="sql-createserver">,
!     <xref linkend="sql-createusermapping">, and
!     <xref linkend="sql-createforeigntable">.
     </para>
   </sect1>

--- 3069,3077 ----
      For additional information, see
      <xref linkend="sql-createforeigndatawrapper">,
      <xref linkend="sql-createserver">,
!     <xref linkend="sql-createusermapping">,
!     <xref linkend="sql-createforeigntable">, and
!     <xref linkend="sql-importforeignschema">.
     </para>
   </sect1>

diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index e5b9e66..3db8ef1 100644
*** a/doc/src/sgml/event-trigger.sgml
--- b/doc/src/sgml/event-trigger.sgml
***************
*** 604,609 ****
--- 604,615 ----
          <entry align="center"><literal>X</literal></entry>
         </row>
         <row>
+         <entry align="left"><literal>IMPORT FOREIGN SCHEMA</literal></entry>
+         <entry align="center"><literal>X</literal></entry>
+         <entry align="center"><literal>X</literal></entry>
+         <entry align="center"><literal>-</literal></entry>
+        </row>
+        <row>
          <entry align="left"><literal>SELECT INTO</literal></entry>
          <entry align="center"><literal>X</literal></entry>
          <entry align="center"><literal>X</literal></entry>
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index 6b5c8b7..edfdcba 100644
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
*************** AcquireSampleRowsFunc (Relation relation
*** 696,701 ****
--- 696,761 ----

     </sect2>

+    <sect2 id="fdw-callbacks-import">
+     <title>FDW Routines For <command>IMPORT FOREIGN SCHEMA</></title>
+
+     <para>
+ <programlisting>
+ List *
+ ImportForeignSchema (ImportForeignSchemaStmt *stmt, Oid serverOid);
+ </programlisting>
+
+      Obtain a list of foreign table creation commands.  This function is
+      called when executing <command>IMPORT FOREIGN SCHEMA</>, and is passed
+      the parse tree for that statement, as well as the OID of the foreign
+      server to use.  It should return a list of C strings, each of which
+      must contain a <command>CREATE FOREIGN TABLE</> command.  These strings
+      will be parsed and executed by the core server.
+     </para>
+
+     <para>
+      Within the <structname>ImportForeignSchemaStmt</> struct,
+      <structfield>remote_schema</> is the name of the remote schema from
+      which tables are to be imported.
+      <structfield>list_type</> identifies how to filter table names:
+      <literal>FDW_IMPORT_SCHEMA_ALL</> means that all tables in the remote
+      schema should be imported (in this case <structfield>table_list</> is
+      empty), <literal>FDW_IMPORT_SCHEMA_LIMIT_TO</> means to include only
+      tables listed in <structfield>table_list</>,
+      and <literal>FDW_IMPORT_SCHEMA_EXCEPT</> means to exclude the tables
+      listed in <structfield>table_list</>.
+      <structfield>options</> is a list of options used for the import process.
+      The meanings of the options are up to the FDW.
+      For example, an FDW could use an option to define whether the
+      <literal>NOT NULL</> attributes of columns should be imported.
+      These options need not have anything to do with those supported by the
+      FDW as database object options.
+     </para>
+
+     <para>
+      The FDW may ignore the <structfield>local_schema</> field of
+      the <structname>ImportForeignSchemaStmt</>, because the core server
+      will automatically insert that name into the parsed <command>CREATE
+      FOREIGN TABLE</> commands.
+     </para>
+
+     <para>
+      The FDW does not have to concern itself with implementing the filtering
+      specified by <structfield>list_type</> and <structfield>table_list</>,
+      either, as the core server will automatically skip any returned commands
+      for tables excluded according to those options.  However, it's often
+      useful to avoid the work of creating commands for excluded tables in the
+      first place.  The function <function>IsImportableForeignTable()</> may be
+      useful to test whether a given foreign-table name will pass the filter.
+     </para>
+
+     <para>
+      If the FDW does not support importing table definitions, the
+      <function>ImportForeignSchema</> pointer can be set to <literal>NULL</>.
+     </para>
+
+    </sect2>
+
     </sect1>

     <sect1 id="fdw-helpers">
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index e6f6e20..152a0e3 100644
*** a/doc/src/sgml/postgres-fdw.sgml
--- b/doc/src/sgml/postgres-fdw.sgml
***************
*** 49,55 ****
     </listitem>
     <listitem>
      <para>
!      Create a foreign table, using <xref linkend="sql-createforeigntable">,
       for each remote table you want to access.  The columns of the foreign
       table must match the referenced remote table.  You can, however, use
       table and/or column names different from the remote table's, if you
--- 49,56 ----
     </listitem>
     <listitem>
      <para>
!      Create a foreign table, using <xref linkend="sql-createforeigntable">
!      or <xref linkend="sql-importforeignschema">,
       for each remote table you want to access.  The columns of the foreign
       table must match the referenced remote table.  You can, however, use
       table and/or column names different from the remote table's, if you
***************
*** 291,296 ****
--- 292,333 ----

     </variablelist>
    </sect3>
+
+   <sect3>
+    <title>Importing Options</title>
+
+    <para>
+     <filename>postgres_fdw</> is able to import foreign table definitions
+     using <xref linkend="sql-importforeignschema">.  This command creates
+     foreign table definitions on the local server that match tables or
+     views present on the remote server.  If the remote tables to be imported
+     have columns of user-defined data types, the local server must have types
+     of the same names.
+    </para>
+
+    <para>
+     Importing behavior can be customized with the following options
+     (given in the <command>IMPORT FOREIGN SCHEMA</> command):
+    </para>
+
+    <variablelist>
+     <varlistentry>
+      <term><literal>import_not_null</literal></term>
+      <listitem>
+       <para>
+        This option controls whether <literal>NOT NULL</> column
+        constraints are included in the definitions of foreign tables imported
+        from a foreign server. The default is <literal>true</>.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+
+    <para>
+     At present, column default expressions, collations, and constraints other
+     than <literal>NOT NULL</> will not be imported from the remote tables.
+    </para>
+   </sect3>
   </sect2>

   <sect2>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 1b0962c..b685e16 100644
*** a/doc/src/sgml/ref/allfiles.sgml
--- b/doc/src/sgml/ref/allfiles.sgml
*************** Complete list of usable sgml source file
*** 131,136 ****
--- 131,137 ----
  <!ENTITY explain            SYSTEM "explain.sgml">
  <!ENTITY fetch              SYSTEM "fetch.sgml">
  <!ENTITY grant              SYSTEM "grant.sgml">
+ <!ENTITY importForeignSchema SYSTEM "import_foreign_schema.sgml">
  <!ENTITY insert             SYSTEM "insert.sgml">
  <!ENTITY listen             SYSTEM "listen.sgml">
  <!ENTITY load               SYSTEM "load.sgml">
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 4a8cf38..46a20ef 100644
*** a/doc/src/sgml/ref/create_foreign_table.sgml
--- b/doc/src/sgml/ref/create_foreign_table.sgml
*************** SERVER film_server;
*** 231,236 ****
--- 231,237 ----
     <member><xref linkend="sql-dropforeigntable"></member>
     <member><xref linkend="sql-createtable"></member>
     <member><xref linkend="sql-createserver"></member>
+    <member><xref linkend="sql-importforeignschema"></member>
    </simplelist>
   </refsect1>
  </refentry>
diff --git a/doc/src/sgml/ref/import_foreign_schema.sgml b/doc/src/sgml/ref/import_foreign_schema.sgml
index ...bdcc265 .
*** a/doc/src/sgml/ref/import_foreign_schema.sgml
--- b/doc/src/sgml/ref/import_foreign_schema.sgml
***************
*** 0 ****
--- 1,168 ----
+ <!--
+ doc/src/sgml/ref/import_foreign_schema.sgml
+ PostgreSQL documentation
+ -->
+
+ <refentry id="SQL-IMPORTFOREIGNSCHEMA">
+  <indexterm zone="sql-importforeignschema">
+   <primary>IMPORT FOREIGN SCHEMA</primary>
+  </indexterm>
+
+  <refmeta>
+   <refentrytitle>IMPORT FOREIGN SCHEMA</refentrytitle>
+   <manvolnum>7</manvolnum>
+   <refmiscinfo>SQL - Language Statements</refmiscinfo>
+  </refmeta>
+
+  <refnamediv>
+   <refname>IMPORT FOREIGN SCHEMA</refname>
+   <refpurpose>import table definitions from a foreign server</refpurpose>
+  </refnamediv>
+
+  <refsynopsisdiv>
+ <synopsis>
+ IMPORT FOREIGN SCHEMA <replaceable class="PARAMETER">remote_schema</replaceable>
+ [ { LIMIT TO | EXCEPT } ( <replaceable class="PARAMETER">table_name</replaceable> [, ...] ) ]
+ FROM SERVER <replaceable class="PARAMETER">server_name</replaceable>
+ INTO <replaceable class="PARAMETER">local_schema</replaceable>
+ [ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>'
[,... ] ) ] 
+ </synopsis>
+  </refsynopsisdiv>
+
+  <refsect1 id="SQL-IMPORTFOREIGNSCHEMA-description">
+   <title>Description</title>
+
+   <para>
+    <command>IMPORT FOREIGN SCHEMA</command> creates foreign tables that
+    represent tables existing on a foreign server.  The new foreign tables
+    will be owned by the user issuing the command and are created with
+    the correct column definitions and options to match the remote tables.
+   </para>
+
+   <para>
+    By default, all tables and views existing in a particular schema on the
+    foreign server are imported.  Optionally, the list of tables can be limited
+    to a specified subset, or specific tables can be excluded.  The new foreign
+    tables are all created in the target schema, which must already exist.
+   </para>
+
+   <para>
+    To use <command>IMPORT FOREIGN SCHEMA</command>, the user must have
+    <literal>USAGE</literal> privilege on the foreign server, as well as
+    <literal>CREATE</literal> privilege on the target schema.
+   </para>
+  </refsect1>
+
+  <refsect1>
+   <title>Parameters</title>
+
+   <variablelist>
+
+    <varlistentry>
+     <term><replaceable class="PARAMETER">remote_schema</replaceable></term>
+     <listitem>
+      <para>
+       The remote schema to import from. The specific meaning of a remote schema
+       depends on the foreign data wrapper in use.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
+     <term><literal>LIMIT TO ( <replaceable class="PARAMETER">table_name</replaceable> [, ...] )</literal></term>
+     <listitem>
+      <para>
+       Import only foreign tables matching one of the given table names.
+       Other tables existing in the foreign schema will be ignored.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
+     <term><literal>EXCEPT ( <replaceable class="PARAMETER">table_name</replaceable> [, ...] )</literal></term>
+     <listitem>
+      <para>
+       Exclude specified foreign tables from the import.  All tables
+       existing in the foreign schema will be imported except the
+       ones listed here.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
+     <term><replaceable class="PARAMETER">server_name</replaceable></term>
+     <listitem>
+      <para>
+       The foreign server to import from.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
+     <term><replaceable class="PARAMETER">local_schema</replaceable></term>
+     <listitem>
+      <para>
+       The schema in which the imported foreign tables will be created.
+      </para>
+     </listitem>
+    </varlistentry>
+
+    <varlistentry>
+     <term><literal>OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable
class="PARAMETER">value</replaceable>'[, ...] )</literal></term> 
+     <listitem>
+      <para>
+       Options to be used during the import.
+       The allowed option names and values are specific to each foreign
+       data wrapper.
+      </para>
+     </listitem>
+    </varlistentry>
+   </variablelist>
+  </refsect1>
+
+  <refsect1 id="SQL-IMPORTFOREIGNSCHEMA-examples">
+   <title>Examples</title>
+
+   <para>
+    Import table definitions from a remote schema <structname>foreign_films</>
+    on server <structname>film_server</>, creating the foreign tables in
+    local schema <structname>films</>:
+
+ <programlisting>
+ IMPORT FOREIGN SCHEMA foreign_films
+     FROM SERVER film_server INTO films;
+ </programlisting>
+    </para>
+
+   <para>
+    As above, but import only the two tables <structname>actors</> and
+    <literal>directors</> (if they exist):
+
+ <programlisting>
+ IMPORT FOREIGN SCHEMA foreign_films LIMIT TO (actors, directors)
+     FROM SERVER film_server INTO films;
+ </programlisting>
+    </para>
+
+  </refsect1>
+
+  <refsect1 id="SQL-IMPORTFOREIGNSCHEMA-compatibility">
+   <title>Compatibility</title>
+
+   <para>
+    The <command>IMPORT FOREIGN SCHEMA</command> command conforms to the
+    <acronym>SQL</acronym> standard, except that the <literal>OPTIONS</>
+    clause is a <productname>PostgreSQL</> extension.
+   </para>
+
+  </refsect1>
+
+  <refsect1>
+   <title>See Also</title>
+
+   <simplelist type="inline">
+    <member><xref linkend="sql-createforeigntable"></member>
+    <member><xref linkend="sql-createserver"></member>
+   </simplelist>
+  </refsect1>
+ </refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index a6575f5..6ec1263 100644
*** a/doc/src/sgml/reference.sgml
--- b/doc/src/sgml/reference.sgml
***************
*** 159,164 ****
--- 159,165 ----
     &explain;
     &fetch;
     &grant;
+    &importForeignSchema;
     &insert;
     &listen;
     &load;
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 110fe00..754264e 100644
*** a/src/backend/commands/event_trigger.c
--- b/src/backend/commands/event_trigger.c
*************** check_ddl_tag(const char *tag)
*** 250,256 ****
          pg_strcasecmp(tag, "REFRESH MATERIALIZED VIEW") == 0 ||
          pg_strcasecmp(tag, "ALTER DEFAULT PRIVILEGES") == 0 ||
          pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0 ||
!         pg_strcasecmp(tag, "DROP OWNED") == 0)
          return EVENT_TRIGGER_COMMAND_TAG_OK;

      /*
--- 250,257 ----
          pg_strcasecmp(tag, "REFRESH MATERIALIZED VIEW") == 0 ||
          pg_strcasecmp(tag, "ALTER DEFAULT PRIVILEGES") == 0 ||
          pg_strcasecmp(tag, "ALTER LARGE OBJECT") == 0 ||
!         pg_strcasecmp(tag, "DROP OWNED") == 0 ||
!         pg_strcasecmp(tag, "IMPORT FOREIGN SCHEMA") == 0)
          return EVENT_TRIGGER_COMMAND_TAG_OK;

      /*
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index 8ab9c43..ab4ed6c 100644
*** a/src/backend/commands/foreigncmds.c
--- b/src/backend/commands/foreigncmds.c
***************
*** 15,22 ****

  #include "access/heapam.h"
  #include "access/htup_details.h"
- #include "access/xact.h"
  #include "access/reloptions.h"
  #include "catalog/dependency.h"
  #include "catalog/indexing.h"
  #include "catalog/objectaccess.h"
--- 15,22 ----

  #include "access/heapam.h"
  #include "access/htup_details.h"
  #include "access/reloptions.h"
+ #include "access/xact.h"
  #include "catalog/dependency.h"
  #include "catalog/indexing.h"
  #include "catalog/objectaccess.h"
***************
*** 27,35 ****
--- 27,37 ----
  #include "catalog/pg_type.h"
  #include "catalog/pg_user_mapping.h"
  #include "commands/defrem.h"
+ #include "foreign/fdwapi.h"
  #include "foreign/foreign.h"
  #include "miscadmin.h"
  #include "parser/parse_func.h"
+ #include "tcop/utility.h"
  #include "utils/acl.h"
  #include "utils/builtins.h"
  #include "utils/lsyscache.h"
***************
*** 37,42 ****
--- 39,54 ----
  #include "utils/syscache.h"


+ typedef struct
+ {
+     char       *tablename;
+     char       *cmd;
+ } import_error_callback_arg;
+
+ /* Internal functions */
+ static void import_error_callback(void *arg);
+
+
  /*
   * Convert a DefElem list to the text array format that is used in
   * pg_foreign_data_wrapper, pg_foreign_server, pg_user_mapping, and
*************** CreateForeignTable(CreateForeignTableStm
*** 1427,1429 ****
--- 1439,1571 ----

      heap_close(ftrel, RowExclusiveLock);
  }
+
+ /*
+  * Import a foreign schema
+  */
+ void
+ ImportForeignSchema(ImportForeignSchemaStmt *stmt)
+ {
+     ForeignServer *server;
+     ForeignDataWrapper *fdw;
+     FdwRoutine *fdw_routine;
+     AclResult    aclresult;
+     List       *cmd_list;
+     ListCell   *lc;
+
+     /* Check that the foreign server exists and that we have USAGE on it */
+     server = GetForeignServerByName(stmt->server_name, false);
+     aclresult = pg_foreign_server_aclcheck(server->serverid, GetUserId(), ACL_USAGE);
+     if (aclresult != ACLCHECK_OK)
+         aclcheck_error(aclresult, ACL_KIND_FOREIGN_SERVER, server->servername);
+
+     /* Check that the schema exists and we have CREATE permissions on it */
+     (void) LookupCreationNamespace(stmt->local_schema);
+
+     /* Get the FDW and check it supports IMPORT */
+     fdw = GetForeignDataWrapper(server->fdwid);
+     if (!OidIsValid(fdw->fdwhandler))
+         ereport(ERROR,
+                 (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+                  errmsg("foreign-data wrapper \"%s\" has no handler",
+                         fdw->fdwname)));
+     fdw_routine = GetFdwRoutine(fdw->fdwhandler);
+     if (fdw_routine->ImportForeignSchema == NULL)
+         ereport(ERROR,
+                 (errcode(ERRCODE_FDW_NO_SCHEMAS),
+                  errmsg("foreign-data wrapper \"%s\" does not support IMPORT FOREIGN SCHEMA",
+                         fdw->fdwname)));
+
+     /* Call FDW to get a list of commands */
+     cmd_list = fdw_routine->ImportForeignSchema(stmt, server->serverid);
+
+     /* Parse and execute each command */
+     foreach(lc, cmd_list)
+     {
+         char       *cmd = (char *) lfirst(lc);
+         import_error_callback_arg callback_arg;
+         ErrorContextCallback sqlerrcontext;
+         List       *raw_parsetree_list;
+         ListCell   *lc2;
+
+         /*
+          * Setup error traceback support for ereport().  This is so that any
+          * error in the generated SQL will be displayed nicely.
+          */
+         callback_arg.tablename = NULL;    /* not known yet */
+         callback_arg.cmd = cmd;
+         sqlerrcontext.callback = import_error_callback;
+         sqlerrcontext.arg = (void *) &callback_arg;
+         sqlerrcontext.previous = error_context_stack;
+         error_context_stack = &sqlerrcontext;
+
+         /*
+          * Parse the SQL string into a list of raw parse trees.
+          */
+         raw_parsetree_list = pg_parse_query(cmd);
+
+         /*
+          * Process each parse tree (we allow the FDW to put more than one
+          * command per string, though this isn't really advised).
+          */
+         foreach(lc2, raw_parsetree_list)
+         {
+             CreateForeignTableStmt *cstmt = lfirst(lc2);
+
+             /*
+              * Because we only allow CreateForeignTableStmt, we can skip parse
+              * analysis, rewrite, and planning steps here.
+              */
+             if (!IsA(cstmt, CreateForeignTableStmt))
+                 elog(ERROR,
+                      "foreign-data wrapper \"%s\" returned incorrect statement type %d",
+                      fdw->fdwname, (int) nodeTag(cstmt));
+
+             /* Ignore commands for tables excluded by filter options */
+             if (!IsImportableForeignTable(cstmt->base.relation->relname, stmt))
+                 continue;
+
+             /* Enable reporting of current table's name on error */
+             callback_arg.tablename = cstmt->base.relation->relname;
+
+             /* Ensure creation schema is the one given in IMPORT statement */
+             cstmt->base.relation->schemaname = pstrdup(stmt->local_schema);
+
+             /* Execute statement */
+             ProcessUtility((Node *) cstmt,
+                            cmd,
+                            PROCESS_UTILITY_SUBCOMMAND, NULL,
+                            None_Receiver, NULL);
+
+             /* Be sure to advance the command counter between subcommands */
+             CommandCounterIncrement();
+
+             callback_arg.tablename = NULL;
+         }
+
+         error_context_stack = sqlerrcontext.previous;
+     }
+ }
+
+ /*
+  * error context callback to let us supply the failing SQL statement's text
+  */
+ static void
+ import_error_callback(void *arg)
+ {
+     import_error_callback_arg *callback_arg = (import_error_callback_arg *) arg;
+     int            syntaxerrposition;
+
+     /* If it's a syntax error, convert to internal syntax error report */
+     syntaxerrposition = geterrposition();
+     if (syntaxerrposition > 0)
+     {
+         errposition(0);
+         internalerrposition(syntaxerrposition);
+         internalerrquery(callback_arg->cmd);
+     }
+
+     if (callback_arg->tablename)
+         errcontext("importing foreign table \"%s\"",
+                    callback_arg->tablename);
+ }
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index 6d548b7..4f5f6ae 100644
*** a/src/backend/foreign/foreign.c
--- b/src/backend/foreign/foreign.c
*************** GetFdwRoutineForRelation(Relation relati
*** 400,405 ****
--- 400,446 ----


  /*
+  * IsImportableForeignTable - filter table names for IMPORT FOREIGN SCHEMA
+  *
+  * Returns TRUE if given table name should be imported according to the
+  * statement's import filter options.
+  */
+ bool
+ IsImportableForeignTable(const char *tablename,
+                          ImportForeignSchemaStmt *stmt)
+ {
+     ListCell   *lc;
+
+     switch (stmt->list_type)
+     {
+         case FDW_IMPORT_SCHEMA_ALL:
+             return true;
+
+         case FDW_IMPORT_SCHEMA_LIMIT_TO:
+             foreach(lc, stmt->table_list)
+             {
+                 RangeVar   *rv = (RangeVar *) lfirst(lc);
+
+                 if (strcmp(tablename, rv->relname) == 0)
+                     return true;
+             }
+             return false;
+
+         case FDW_IMPORT_SCHEMA_EXCEPT:
+             foreach(lc, stmt->table_list)
+             {
+                 RangeVar   *rv = (RangeVar *) lfirst(lc);
+
+                 if (strcmp(tablename, rv->relname) == 0)
+                     return false;
+             }
+             return true;
+     }
+     return false;                /* shouldn't get here */
+ }
+
+
+ /*
   * deflist_to_tuplestore - Helper function to convert DefElem list to
   * tuplestore usable in SRF.
   */
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 8d3d5a7..3088578 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*************** _copyCreateForeignTableStmt(const Create
*** 3567,3572 ****
--- 3567,3587 ----
      return newnode;
  }

+ static ImportForeignSchemaStmt *
+ _copyImportForeignSchemaStmt(const ImportForeignSchemaStmt *from)
+ {
+     ImportForeignSchemaStmt *newnode = makeNode(ImportForeignSchemaStmt);
+
+     COPY_STRING_FIELD(server_name);
+     COPY_STRING_FIELD(remote_schema);
+     COPY_STRING_FIELD(local_schema);
+     COPY_SCALAR_FIELD(list_type);
+     COPY_NODE_FIELD(table_list);
+     COPY_NODE_FIELD(options);
+
+     return newnode;
+ }
+
  static CreateTrigStmt *
  _copyCreateTrigStmt(const CreateTrigStmt *from)
  {
*************** copyObject(const void *from)
*** 4477,4482 ****
--- 4492,4500 ----
          case T_CreateForeignTableStmt:
              retval = _copyCreateForeignTableStmt(from);
              break;
+         case T_ImportForeignSchemaStmt:
+             retval = _copyImportForeignSchemaStmt(from);
+             break;
          case T_CreateTrigStmt:
              retval = _copyCreateTrigStmt(from);
              break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e7b49f6..1b07db6 100644
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
*************** _equalCreateForeignTableStmt(const Creat
*** 1769,1774 ****
--- 1769,1787 ----
  }

  static bool
+ _equalImportForeignSchemaStmt(const ImportForeignSchemaStmt *a, const ImportForeignSchemaStmt *b)
+ {
+     COMPARE_STRING_FIELD(server_name);
+     COMPARE_STRING_FIELD(remote_schema);
+     COMPARE_STRING_FIELD(local_schema);
+     COMPARE_SCALAR_FIELD(list_type);
+     COMPARE_NODE_FIELD(table_list);
+     COMPARE_NODE_FIELD(options);
+
+     return true;
+ }
+
+ static bool
  _equalCreateTrigStmt(const CreateTrigStmt *a, const CreateTrigStmt *b)
  {
      COMPARE_STRING_FIELD(trigname);
*************** equal(const void *a, const void *b)
*** 2943,2948 ****
--- 2956,2964 ----
          case T_CreateForeignTableStmt:
              retval = _equalCreateForeignTableStmt(a, b);
              break;
+         case T_ImportForeignSchemaStmt:
+             retval = _equalImportForeignSchemaStmt(a, b);
+             break;
          case T_CreateTrigStmt:
              retval = _equalCreateTrigStmt(a, b);
              break;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index c182212..9573a9b 100644
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*************** _outCreateForeignTableStmt(StringInfo st
*** 2012,2017 ****
--- 2012,2030 ----
  }

  static void
+ _outImportForeignSchemaStmt(StringInfo str, const ImportForeignSchemaStmt *node)
+ {
+     WRITE_NODE_TYPE("IMPORTFOREIGNSCHEMASTMT");
+
+     WRITE_STRING_FIELD(server_name);
+     WRITE_STRING_FIELD(remote_schema);
+     WRITE_STRING_FIELD(local_schema);
+     WRITE_ENUM_FIELD(list_type, ImportForeignSchemaType);
+     WRITE_NODE_FIELD(table_list);
+     WRITE_NODE_FIELD(options);
+ }
+
+ static void
  _outIndexStmt(StringInfo str, const IndexStmt *node)
  {
      WRITE_NODE_TYPE("INDEXSTMT");
*************** _outNode(StringInfo str, const void *obj
*** 3119,3124 ****
--- 3132,3140 ----
              case T_CreateForeignTableStmt:
                  _outCreateForeignTableStmt(str, obj);
                  break;
+             case T_ImportForeignSchemaStmt:
+                 _outImportForeignSchemaStmt(str, obj);
+                 break;
              case T_IndexStmt:
                  _outIndexStmt(str, obj);
                  break;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ba7d091..a113809 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** typedef struct PrivTarget
*** 111,116 ****
--- 111,123 ----
      List       *objs;
  } PrivTarget;

+ /* Private struct for the result of import_qualification production */
+ typedef struct ImportQual
+ {
+     ImportForeignSchemaType type;
+     List       *table_names;
+ } ImportQual;
+
  /* ConstraintAttributeSpec yields an integer bitmask of these flags: */
  #define CAS_NOT_DEFERRABLE            0x01
  #define CAS_DEFERRABLE                0x02
*************** static Node *makeRecursiveViewSelect(cha
*** 212,217 ****
--- 219,225 ----
      ResTarget            *target;
      struct PrivTarget    *privtarget;
      AccessPriv            *accesspriv;
+     struct ImportQual    *importqual;
      InsertStmt            *istmt;
      VariableSetStmt        *vsetstmt;
  }
*************** static Node *makeRecursiveViewSelect(cha
*** 238,245 ****
          DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt
          DropUserStmt DropdbStmt DropTableSpaceStmt DropFdwStmt
          DropForeignServerStmt DropUserMappingStmt ExplainStmt FetchStmt
!         GrantStmt GrantRoleStmt IndexStmt InsertStmt ListenStmt LoadStmt
!         LockStmt NotifyStmt ExplainableStmt PreparableStmt
          CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
          RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt RevokeRoleStmt
          RuleActionStmt RuleActionStmtOrEmpty RuleStmt
--- 246,253 ----
          DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt
          DropUserStmt DropdbStmt DropTableSpaceStmt DropFdwStmt
          DropForeignServerStmt DropUserMappingStmt ExplainStmt FetchStmt
!         GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt
!         ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt
          CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
          RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt RevokeRoleStmt
          RuleActionStmt RuleActionStmtOrEmpty RuleStmt
*************** static Node *makeRecursiveViewSelect(cha
*** 322,327 ****
--- 330,337 ----
  %type <ival>    defacl_privilege_target
  %type <defelt>    DefACLOption
  %type <list>    DefACLOptionList
+ %type <ival>    import_qualification_type
+ %type <importqual> import_qualification

  %type <list>    stmtblock stmtmulti
                  OptTableElementList TableElementList OptInherit definition
*************** static Node *makeRecursiveViewSelect(cha
*** 556,562 ****

      HANDLER HAVING HEADER_P HOLD HOUR_P

!     IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P
      INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
      INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
      INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
--- 566,572 ----

      HANDLER HAVING HEADER_P HOLD HOUR_P

!     IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P
      INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
      INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
      INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
*************** stmt :
*** 802,807 ****
--- 812,818 ----
              | FetchStmt
              | GrantStmt
              | GrantRoleStmt
+             | ImportForeignSchemaStmt
              | IndexStmt
              | InsertStmt
              | ListenStmt
*************** AlterForeignTableStmt:
*** 4275,4280 ****
--- 4286,4337 ----
  /*****************************************************************************
   *
   *        QUERY:
+  *                IMPORT FOREIGN SCHEMA remote_schema
+  *                [ { LIMIT TO | EXCEPT } ( table_list ) ]
+  *                FROM SERVER server_name INTO local_schema [ OPTIONS (...) ]
+  *
+  ****************************************************************************/
+
+ ImportForeignSchemaStmt:
+         IMPORT_P FOREIGN SCHEMA name import_qualification
+           FROM SERVER name INTO name create_generic_options
+             {
+                 ImportForeignSchemaStmt *n = makeNode(ImportForeignSchemaStmt);
+                 n->server_name = $8;
+                 n->remote_schema = $4;
+                 n->local_schema = $10;
+                 n->list_type = $5->type;
+                 n->table_list = $5->table_names;
+                 n->options = $11;
+                 $$ = (Node *) n;
+             }
+         ;
+
+ import_qualification_type:
+         LIMIT TO                 { $$ = FDW_IMPORT_SCHEMA_LIMIT_TO; }
+         | EXCEPT                 { $$ = FDW_IMPORT_SCHEMA_EXCEPT; }
+         ;
+
+ import_qualification:
+         import_qualification_type '(' relation_expr_list ')'
+             {
+                 ImportQual *n = (ImportQual *) palloc(sizeof(ImportQual));
+                 n->type = $1;
+                 n->table_names = $3;
+                 $$ = n;
+             }
+         | /*EMPTY*/
+             {
+                 ImportQual *n = (ImportQual *) palloc(sizeof(ImportQual));
+                 n->type = FDW_IMPORT_SCHEMA_ALL;
+                 n->table_names = NIL;
+                 $$ = n;
+             }
+         ;
+
+ /*****************************************************************************
+  *
+  *        QUERY:
   *             CREATE USER MAPPING FOR auth_ident SERVER name [OPTIONS]
   *
   *****************************************************************************/
*************** unreserved_keyword:
*** 12909,12914 ****
--- 12966,12972 ----
              | IMMEDIATE
              | IMMUTABLE
              | IMPLICIT_P
+             | IMPORT_P
              | INCLUDING
              | INCREMENT
              | INDEX
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 3423898..07e0b98 100644
*** a/src/backend/tcop/utility.c
--- b/src/backend/tcop/utility.c
*************** check_xact_readonly(Node *parsetree)
*** 202,207 ****
--- 202,208 ----
          case T_AlterTableSpaceOptionsStmt:
          case T_AlterTableSpaceMoveStmt:
          case T_CreateForeignTableStmt:
+         case T_ImportForeignSchemaStmt:
          case T_SecLabelStmt:
              PreventCommandIfReadOnly(CreateCommandTag(parsetree));
              break;
*************** ProcessUtilitySlow(Node *parsetree,
*** 1196,1201 ****
--- 1197,1206 ----
                  RemoveUserMapping((DropUserMappingStmt *) parsetree);
                  break;

+             case T_ImportForeignSchemaStmt:
+                 ImportForeignSchema((ImportForeignSchemaStmt *) parsetree);
+                 break;
+
              case T_CompositeTypeStmt:    /* CREATE TYPE (composite) */
                  {
                      CompositeTypeStmt *stmt = (CompositeTypeStmt *) parsetree;
*************** CreateCommandTag(Node *parsetree)
*** 1853,1858 ****
--- 1858,1867 ----
              tag = "CREATE FOREIGN TABLE";
              break;

+         case T_ImportForeignSchemaStmt:
+             tag = "IMPORT FOREIGN SCHEMA";
+             break;
+
          case T_DropStmt:
              switch (((DropStmt *) parsetree)->removeType)
              {
*************** GetCommandLogLevel(Node *parsetree)
*** 2518,2523 ****
--- 2527,2533 ----
          case T_CreateUserMappingStmt:
          case T_AlterUserMappingStmt:
          case T_DropUserMappingStmt:
+         case T_ImportForeignSchemaStmt:
              lev = LOGSTMT_DDL;
              break;

diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h
index 5ec9374..0ebdbc1 100644
*** a/src/include/commands/defrem.h
--- b/src/include/commands/defrem.h
*************** extern Oid    AlterUserMapping(AlterUserMap
*** 124,129 ****
--- 124,130 ----
  extern Oid    RemoveUserMapping(DropUserMappingStmt *stmt);
  extern void RemoveUserMappingById(Oid umId);
  extern void CreateForeignTable(CreateForeignTableStmt *stmt, Oid relid);
+ extern void ImportForeignSchema(ImportForeignSchemaStmt *stmt);
  extern Datum transformGenericOptions(Oid catalogId,
                          Datum oldOptions,
                          List *options,
diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h
index 1b735da..dc0a7fc 100644
*** a/src/include/foreign/fdwapi.h
--- b/src/include/foreign/fdwapi.h
*************** typedef bool (*AnalyzeForeignTable_funct
*** 100,105 ****
--- 100,108 ----
                                                   AcquireSampleRowsFunc *func,
                                                      BlockNumber *totalpages);

+ typedef List *(*ImportForeignSchema_function) (ImportForeignSchemaStmt *stmt,
+                                                            Oid serverOid);
+
  /*
   * FdwRoutine is the struct returned by a foreign-data wrapper's handler
   * function.  It provides pointers to the callback functions needed by the
*************** typedef struct FdwRoutine
*** 144,149 ****
--- 147,155 ----

      /* Support functions for ANALYZE */
      AnalyzeForeignTable_function AnalyzeForeignTable;
+
+     /* Support functions for IMPORT FOREIGN SCHEMA */
+     ImportForeignSchema_function ImportForeignSchema;
  } FdwRoutine;


*************** typedef struct FdwRoutine
*** 151,155 ****
--- 157,163 ----
  extern FdwRoutine *GetFdwRoutine(Oid fdwhandler);
  extern FdwRoutine *GetFdwRoutineByRelId(Oid relid);
  extern FdwRoutine *GetFdwRoutineForRelation(Relation relation, bool makecopy);
+ extern bool IsImportableForeignTable(const char *tablename,
+                          ImportForeignSchemaStmt *stmt);

  #endif   /* FDWAPI_H */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 7b0088f..067c768 100644
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
*************** typedef enum NodeTag
*** 357,362 ****
--- 357,363 ----
      T_AlterTableSpaceMoveStmt,
      T_SecLabelStmt,
      T_CreateForeignTableStmt,
+     T_ImportForeignSchemaStmt,
      T_CreateExtensionStmt,
      T_AlterExtensionStmt,
      T_AlterExtensionContentsStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff126eb..8364bef 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterForeignServerStmt
*** 1791,1797 ****
  } AlterForeignServerStmt;

  /* ----------------------
!  *        Create FOREIGN TABLE Statements
   * ----------------------
   */

--- 1791,1797 ----
  } AlterForeignServerStmt;

  /* ----------------------
!  *        Create FOREIGN TABLE Statement
   * ----------------------
   */

*************** typedef struct DropUserMappingStmt
*** 1832,1837 ****
--- 1832,1860 ----
  } DropUserMappingStmt;

  /* ----------------------
+  *        Import Foreign Schema Statement
+  * ----------------------
+  */
+
+ typedef enum ImportForeignSchemaType
+ {
+     FDW_IMPORT_SCHEMA_ALL,        /* all relations wanted */
+     FDW_IMPORT_SCHEMA_LIMIT_TO, /* include only listed tables in import */
+     FDW_IMPORT_SCHEMA_EXCEPT    /* exclude listed tables from import */
+ } ImportForeignSchemaType;
+
+ typedef struct ImportForeignSchemaStmt
+ {
+     NodeTag        type;
+     char       *server_name;    /* FDW server name */
+     char       *remote_schema;    /* remote schema name to query */
+     char       *local_schema;    /* local schema to create objects in */
+     ImportForeignSchemaType list_type;    /* type of table list */
+     List       *table_list;        /* List of RangeVar */
+     List       *options;        /* list of options to pass to FDW */
+ } ImportForeignSchemaStmt;
+
+ /* ----------------------
   *        Create TRIGGER Statement
   * ----------------------
   */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 04e9810..b52e507 100644
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
*************** PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAM
*** 184,189 ****
--- 184,190 ----
  PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD)
  PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD)
  PG_KEYWORD("implicit", IMPLICIT_P, UNRESERVED_KEYWORD)
+ PG_KEYWORD("import", IMPORT_P, UNRESERVED_KEYWORD)
  PG_KEYWORD("in", IN_P, RESERVED_KEYWORD)
  PG_KEYWORD("including", INCLUDING, UNRESERVED_KEYWORD)
  PG_KEYWORD("increment", INCREMENT, UNRESERVED_KEYWORD)
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index ff203b2..e4dedb0 100644
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
*************** DROP TRIGGER trigtest_before_row ON fore
*** 1193,1198 ****
--- 1193,1208 ----
  DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
  DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
  DROP FUNCTION dummy_trigger();
+ -- IMPORT FOREIGN SCHEMA
+ IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
+ ERROR:  foreign-data wrapper "foo" has no handler
+ IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
+ ERROR:  foreign-data wrapper "foo" has no handler
+ IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR
+ ERROR:  foreign-data wrapper "foo" has no handler
+ IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public
+ OPTIONS (option1 'value1', option2 'value2'); -- ERROR
+ ERROR:  foreign-data wrapper "foo" has no handler
  -- DROP FOREIGN TABLE
  DROP FOREIGN TABLE no_table;                                    -- ERROR
  ERROR:  foreign table "no_table" does not exist
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index 0f0869e..de9dbc8 100644
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
*************** DROP TRIGGER trigtest_after_row ON forei
*** 514,519 ****
--- 514,526 ----

  DROP FUNCTION dummy_trigger();

+ -- IMPORT FOREIGN SCHEMA
+ IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
+ IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
+ IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR
+ IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public
+ OPTIONS (option1 'value1', option2 'value2'); -- ERROR
+
  -- DROP FOREIGN TABLE
  DROP FOREIGN TABLE no_table;                                    -- ERROR
  DROP FOREIGN TABLE IF EXISTS no_table;

Re: IMPORT FOREIGN SCHEMA statement

From

Michael Paquier

Date:

10 July 2014, 04:28:18

On Thu, Jul 10, 2014 at 9:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:
> I've started looking at this patch. I wonder whether it's really such
> a great idea to expect the FDW to return a list of parsetrees for
> CREATE FOREIGN TABLE commands; that seems like a recipe for breakage
> anytime we change the parsetree representation, say add a field to
> ColumnDef. The alternative I'm thinking about is to have the FDW pass
> back a list of strings, which would be textual CREATE FOREIGN TABLE
> commands.

Here's a rather-heavily-editorialized draft patch that does it like that.

This patch takes the viewpoint I espoused nearby of allowing names in
the LIMIT TO clause that aren't present on the remote server. If we
decide we want to hew to the letter of the standard on that, I'd be
inclined to enforce this in the core code, not in individual FDWs as
the submitted patch did. (I didn't much like that implementation
anyway, since it didn't tell you which name it was unhappy about.)

Woah, thanks. I've just been through this patch and it looks great.

I guess that this implementation is enough as a first shot, particularly regarding the complexity that default expression import would bring up for postgres_fdw (point raised during the review, but not really discussed afterwards).

Regards,

--
Michael

Re: IMPORT FOREIGN SCHEMA statement

From

Tom Lane

Date:

10 July 2014, 04:30:32

Michael Paquier <michael.paquier@gmail.com> writes:
> I guess that this implementation is enough as a first shot, particularly
> regarding the complexity that default expression import would bring up for
> postgres_fdw (point raised during the review, but not really discussed
> afterwards).

Yeah.  I'm actually more concerned about the lack of collation import,
but that's unfixable unless we can figure out how to identify collations
in a platform-independent way.
        regards, tom lane

Re: IMPORT FOREIGN SCHEMA statement

From

Ronan Dunklau

Date:

10 July 2014, 10:07:14

Le mercredi 9 juillet 2014 21:30:05 Tom Lane a écrit :
> Michael Paquier <michael.paquier@gmail.com> writes:
> > I guess that this implementation is enough as a first shot, particularly
> > regarding the complexity that default expression import would bring up for
> > postgres_fdw (point raised during the review, but not really discussed
> > afterwards).
>
> Yeah.  I'm actually more concerned about the lack of collation import,
> but that's unfixable unless we can figure out how to identify collations
> in a platform-independent way.
>
>             regards, tom lane

Thank you for working on this patch, I'm not really fond of building strings
in a FDW for the core to parse them again but I don't see any other solution
to the problem you mentioned.

Similarly to what we do for the schema, we should also check that the server
in the parsed statement is the one we are importing from.

--
Ronan Dunklau
http://dalibo.com - http://dalibo.org

Re: IMPORT FOREIGN SCHEMA statement

From

Tom Lane

Date:

10 July 2014, 17:27:00

I wrote:
> Michael Paquier <michael.paquier@gmail.com> writes:
>> I guess that this implementation is enough as a first shot, particularly
>> regarding the complexity that default expression import would bring up for
>> postgres_fdw (point raised during the review, but not really discussed
>> afterwards).

> Yeah.  I'm actually more concerned about the lack of collation import,
> but that's unfixable unless we can figure out how to identify collations
> in a platform-independent way.

I had second thoughts about this: now that the code is generating a
textual CREATE FOREIGN TABLE command, it would actually be just about
trivial to pull back the text representation of the remote's default
expression and attempt to apply it to the foreign table.  Likewise we
could ignore the platform-dependency issues in collations and just
attempt to apply whatever collation name is reported by the remote.

Arguably both of these things would be too risky to do by default,
but if we make them be controlled by IMPORT options, why not?

In the case of a default expression, AFAICS there are two possible
failure modes.  The local server might not have some function or
operator used by the remote; in which case, you'd get a pretty easy
to understand error message, and it would be obvious what to do if
you wanted to fix it.  Or we might have a similarly-named function
or operator that behaves differently (an important special case
is where the function is volatile and you'd get a different answer
locally, eg nextval()).  Well, that's a problem, and we'd need to
document it, but I don't see that that risk is so bad that we should
refuse to import any default expressions ever.  Especially when
common cases like "0" or "now()" can be expected to work fine.

Similarly, in the case of a collation name, we might not have the
same locale name, which would be annoying but not dangerous.  Or
we might have a similarly-named locale that actually has sorting
rules different from the remote's.  But that would only be a real
risk when pulling from a remote that's on a different OS from the
local server, and in any case whatever discrepancy might exist is
unlikely to be worse than failing to import a collation spec at all.
Not importing the remote's spec is *guaranteed* to be wrong.

So I propose we invent a couple more import options, say
import_default and import_collate, and make the postgres_fdw
code do the obvious thing with them.  import_default should
probably default to false, but I'm about halfway tempted to
say that import_collate should default to true --- if you're
importing a collatable column and you don't have a matching
locale locally, it seems like it'd be better if we complain
about that by default.

Comments?
        regards, tom lane

Re: IMPORT FOREIGN SCHEMA statement

From

Tom Lane

Date:

10 July 2014, 17:37:35

Ronan Dunklau <ronan.dunklau@dalibo.com> writes:
> Similarly to what we do for the schema, we should also check that the server 
> in the parsed statement is the one we are importing from.

Hm.  The FDW would really have to go out of its way to put the wrong thing
there, so is this worth the trouble?  It's not like the creation schema
where you can easily omit it from the command; the syntax requires it.
        regards, tom lane

Re: IMPORT FOREIGN SCHEMA statement

From

Tom Lane

Date:

10 July 2014, 22:06:32

I wrote:
> So I propose we invent a couple more import options, say
> import_default and import_collate, and make the postgres_fdw
> code do the obvious thing with them.  import_default should
> probably default to false, but I'm about halfway tempted to
> say that import_collate should default to true --- if you're
> importing a collatable column and you don't have a matching
> locale locally, it seems like it'd be better if we complain
> about that by default.

I've committed this patch with that addition and some minor additional
cleanup.
        regards, tom lane

Re: IMPORT FOREIGN SCHEMA statement

From

Michael Paquier

Date:

11 July 2014, 02:12:20

On Fri, Jul 11, 2014 at 4:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:
> So I propose we invent a couple more import options, say
> import_default and import_collate, and make the postgres_fdw
> code do the obvious thing with them. import_default should
> probably default to false, but I'm about halfway tempted to
> say that import_collate should default to true --- if you're
> importing a collatable column and you don't have a matching
> locale locally, it seems like it'd be better if we complain
> about that by default.

I've committed this patch with that addition and some minor additional
cleanup.

Thanks!

--
Michael