Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-10 Thread Ronan Dunklau
Le mercredi 9 juillet 2014 21:30:05 Tom Lane a écrit : Michael Paquier michael.paqu...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-10 Thread Tom Lane
I wrote: Michael Paquier michael.paqu...@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).

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-10 Thread Tom Lane
Ronan Dunklau ronan.dunk...@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?

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-10 Thread Tom Lane
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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-10 Thread Michael Paquier
On Fri, Jul 11, 2014 at 4:06 AM, Tom Lane t...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Tom Lane
Michael Paquier michael.paqu...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Alvaro Herrera
Tom Lane wrote: Michael Paquier michael.paqu...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Michael Paquier
On Thu, Jul 10, 2014 at 2:30 AM, Tom Lane t...@sss.pgh.pa.us wrote: Michael Paquier michael.paqu...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Tom Lane
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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Michael Paquier
On Thu, Jul 10, 2014 at 9:52 AM, Tom Lane t...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-09 Thread Tom Lane
Michael Paquier michael.paqu...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-07 Thread Robert Haas
On Mon, May 26, 2014 at 12:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Albe Laurenz laurenz.a...@wien.gv.at writes: In addition to data type mapping questions (which David already raised) I have one problem when I think of the Oracle FDW: Oracle follows the SQL standard in folding table names

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-07 Thread Ronan Dunklau
Le lundi 7 juillet 2014 07:58:33 Robert Haas a écrit : On Mon, May 26, 2014 at 12:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Albe Laurenz laurenz.a...@wien.gv.at writes: In addition to data type mapping questions (which David already raised) I have one problem when I think of the Oracle FDW:

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-01 Thread Albe Laurenz
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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-01 Thread Ronan Dunklau
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 largely simplified flow, resulting in the patch attached. [...] Ronan, what do you think of those patches? I

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-07-01 Thread Michael Paquier
On Tue, Jul 1, 2014 at 4:23 PM, Ronan Dunklau ronan.dunk...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-30 Thread Ronan Dunklau
Le lundi 30 juin 2014 16:43:38 Michael Paquier a écrit : On Thu, Jun 19, 2014 at 11:00 PM, Michael Paquier michael.paqu...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-30 Thread Michael Paquier
On Mon, Jun 30, 2014 at 6:01 PM, Ronan Dunklau ronan.dunk...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-19 Thread Michael Paquier
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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-17 Thread Michael Paquier
On Tue, Jun 17, 2014 at 4:36 AM, Ronan Dunklau ronan.dunk...@dalibo.com wrote: Le lundi 16 juin 2014 16:07:51 Michael Paquier a écrit : On Sat, May 24, 2014 at 5:08 AM, Ronan Dunklau ronan.dunk...@dalibo.com 2) The query I am seeing on this spec offers the possiblitily to query TABLE_NAME LIKE

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-17 Thread Michael Paquier
On Tue, Jun 17, 2014 at 5:06 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Jun 17, 2014 at 4:36 AM, Ronan Dunklau ronan.dunk...@dalibo.com wrote: Now, we should think about what options may be desirable for postgres_fdw. An option to include triggers in what is fetched? I am

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-16 Thread Atri Sharma
On Mon, Jun 16, 2014 at 11:28 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Mon, May 26, 2014 at 6:23 AM, Ronan Dunklau ronan.dunk...@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,

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-16 Thread Ronan Dunklau
Le lundi 16 juin 2014 11:32:38 Atri Sharma a écrit : On Mon, Jun 16, 2014 at 11:28 AM, Michael Paquier michael.paqu...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-16 Thread Michael Paquier
On Sat, May 24, 2014 at 5:08 AM, Ronan Dunklau ronan.dunk...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-16 Thread Stephen Frost
* Ronan Dunklau (ronan.dunk...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-06-15 Thread Michael Paquier
On Mon, May 26, 2014 at 6:23 AM, Ronan Dunklau ronan.dunk...@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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-28 Thread David Fetter
On Tue, May 27, 2014 at 09:41:06AM -0400, Stephen Frost wrote: * David Fetter (da...@fetter.org) wrote: - We make type mappings settable at the level of: - FDW - Instance (a.k.a. cluster) - Database - Schema - Table - Column While I like the general idea,

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-28 Thread Stephen Frost
* David Fetter (da...@fetter.org) wrote: On Tue, May 27, 2014 at 09:41:06AM -0400, Stephen Frost wrote: * David Fetter (da...@fetter.org) wrote: - We make type mappings settable at the level of: - FDW - Instance (a.k.a. cluster) - Database - Schema - Table

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Ronan Dunklau
Additionally, I don't really see how that would be useful in a general case. With an in-core defined meaning of type transformation, any FDW that doesn't fit exactly into the model would have a hard time. For example, what happens if an FDW is only ever capable of returning text ?

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Stephen Frost
* David Fetter (da...@fetter.org) wrote: - We make type mappings settable at the level of: - FDW - Instance (a.k.a. cluster) - Database - Schema - Table - Column While I like the general idea, you seem to be making this appear much more complex than it actually is.

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Stephen Frost
* David Fetter (da...@fetter.org) wrote: In the easy case of PostgreSQL, you might also be able to establish whether the UDT in the already defined locally case above is identical to the one defined remotely, but I don't think it's possible even in principle for non-PostgreSQL remote systems,

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: Albe Laurenz laurenz.a...@wien.gv.at writes: Oracle follows the SQL standard in folding table names to upper case. So this would normally result in a lot of PostgreSQL foreign tables with upper case names, which would be odd and unpleasant. I

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Stephen Frost
* Ronan Dunklau (ronan.dunk...@dalibo.com) wrote: So, without extending the syntax too much, we could add options: IMPORT FOREIGN SCHEMA remote_schema FROM SERVER server_name INTO local_schema [ { LIMIT TO | EXCEPT } (table_name [, ...])] [ OPTIONS (option_list) ] This option list could

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Petr Jelinek
On 27/05/14 13:49, Ronan Dunklau wrote: Between this and the type-mapping questions, it seems likely that we're going to need a way for IMPORT FOREIGN SCHEMA to accept user-supplied control options, which would in general be specific to the FDW being used. (Another thing the SQL committee

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Alvaro Herrera
Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: Albe Laurenz laurenz.a...@wien.gv.at writes: Oracle follows the SQL standard in folding table names to upper case. So this would normally result in a lot of PostgreSQL foreign tables with upper case names, which would be odd

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Stephen Frost
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Stephen Frost wrote: It seems like it would often be desirable for the Oracle FDW to smash all-upper-case names to all-lower-case while importing, so that no quoting is needed on either side. I doubt though that this is so desirable

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: Sure, I was being a bit over-simplistic. As was mentioned up-thread, the option would rather be flip all-uppercase to lowercase and all- lowercase to uppercase, quote any which are mixed, or something along those lines. What I was trying to get at is

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: This, plus the generic ability to pass an OPTIONS clause to the IMPORT (allowing you to have different defaults for different IMPORT statements) and having it be transactional, as you mention, appears to be

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-27 Thread Ronan Dunklau
Le mardi 27 mai 2014 09:52:27 Stephen Frost a écrit : * David Fetter (da...@fetter.org) wrote: In the easy case of PostgreSQL, you might also be able to establish whether the UDT in the already defined locally case above is identical to the one defined remotely, but I don't think it's

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-26 Thread Albe Laurenz
Ronan Dunklau wrote: 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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-26 Thread Tom Lane
Albe Laurenz laurenz.a...@wien.gv.at writes: In addition to data type mapping questions (which David already raised) I have one problem when I think of the Oracle FDW: Oracle follows the SQL standard in folding table names to upper case. So this would normally result in a lot of PostgreSQL

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-25 Thread David Fetter
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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-25 Thread Ronan Dunklau
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

Re: [HACKERS] IMPORT FOREIGN SCHEMA statement

2014-05-25 Thread David Fetter
On Sun, May 25, 2014 at 11:23:41PM +0200, Ronan Dunklau 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