Re: [HACKERS] FDW for PostgreSQL

2013-03-28 Thread Stephen Frost
Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: Another thing I was wondering about, but did not change, is that if we're having the remote transaction inherit the local transaction's isolation level, shouldn't it inherit the READ ONLY property as well? Apologies for bringing this up pretty

Re: [HACKERS] FDW for PostgreSQL

2013-03-28 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: Apologies for bringing this up pretty late, but wrt writable FDW transaction levels, I was *really* hoping that we'd be able to implement autonomous transactions on top of writeable FDWs. It looks like there's no way to do this using the postgres_fdw

Re: [HACKERS] FDW for PostgreSQL

2013-03-28 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: TBH I think this is a fairly bad idea. You can get that behavior via dblink if you need it, While I appreciate that dblink can do it, I simply don't see it as a good solution to this. but there's no way to do it in an FDW without ending up with

Re: [HACKERS] FDW for PostgreSQL

2013-03-28 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: ... The only way to make this sane at all would be to provide user control of which operations go to which connections; which is inherent in dblink's API but is simply not a concept in the FDW universe. And I

Re: [HACKERS] FDW for PostgreSQL

2013-03-28 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: I don't want to have API features that make connections explicit, because I don't think that can be shoehorned into the FDW model without considerable strain and weird corner cases. It seems we're talking past each other here. I'm not particularly

Re: [HACKERS] FDW for PostgreSQL

2013-02-22 Thread Thom Brown
On 21 February 2013 10:30, Tom Lane t...@sss.pgh.pa.us wrote: Shigeru Hanada shigeru.han...@gmail.com writes: [ postgres_fdw.v5.patch ] Applied with a lot of revisions. Bit of an issue with selecting rows: postgres=# SELECT * FROM animals; id | animal_name | animal_type | lifespan

Re: [HACKERS] FDW for PostgreSQL

2013-02-22 Thread Tom Lane
Thom Brown t...@linux.com writes: Bit of an issue with selecting rows: Ooops, looks like I screwed up the logic for whole-row references. Will fix, thanks for the test case! regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To

Re: [HACKERS] FDW for PostgreSQL

2013-02-22 Thread Thom Brown
On 22 February 2013 14:10, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: Bit of an issue with selecting rows: Ooops, looks like I screwed up the logic for whole-row references. Will fix, thanks for the test case! Retried after your changes and all is well. Thanks Tom.

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Shigeru Hanada shigeru.han...@gmail.com writes: [ postgres_fdw.v5.patch ] Applied with a lot of revisions. There are still a number of loose ends and things that need to be discussed: I'm not at all happy with the planner support functions --- as designed, that code is basically incapable of

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Albe Laurenz
Tom Lane wrote: Applied with a lot of revisions. I am thrilled. As I mentioned earlier, I think it would be better to force the remote session's search_path setting to just pg_catalog and then reduce the amount of explicit schema naming in the queries --- any opinions about that? I think

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Andres Freund
On 2013-02-21 14:23:35 +, Albe Laurenz wrote: Tom Lane wrote: Another thing I was wondering about, but did not change, is that if we're having the remote transaction inherit the local transaction's isolation level, shouldn't it inherit the READ ONLY property as well? That seems to me

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Albe Laurenz laurenz.a...@wien.gv.at writes: Tom Lane wrote: As I mentioned earlier, I think it would be better to force the remote session's search_path setting to just pg_catalog and then reduce the amount of explicit schema naming in the queries --- any opinions about that? I think that

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2013-02-21 14:23:35 +, Albe Laurenz wrote: Tom Lane wrote: Another thing I was wondering about, but did not change, is that if we're having the remote transaction inherit the local transaction's isolation level, shouldn't it inherit the READ

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Andres Freund
On 2013-02-21 09:58:57 -0500, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-02-21 14:23:35 +, Albe Laurenz wrote: Tom Lane wrote: Another thing I was wondering about, but did not change, is that if we're having the remote transaction inherit the local

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2013-02-21 09:58:57 -0500, Tom Lane wrote: How exactly would it do that via an FDW? Surely if the user tries to execute INSERT/UPDATE/DELETE against a foreign table, the command would get rejected in a read-only transaction, long before we even

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Andres Freund
On 2013-02-21 10:21:34 -0500, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-02-21 09:58:57 -0500, Tom Lane wrote: How exactly would it do that via an FDW? Surely if the user tries to execute INSERT/UPDATE/DELETE against a foreign table, the command would get

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: Sure, it might fail if you use READ ONLY explicitly. Or the code might check it. The point is that one might not have choice about the READ ONLY state of the local transaction if its a HS standby as all transactions are READ ONLY there. [ shrug...

Re: [HACKERS] FDW for PostgreSQL

2013-02-21 Thread Albe Laurenz
Tom Lane wrote: I think that the question what to push down is a different question from checking column data types, because there we can rely on the type input functions to reject bad values. Unfortunately, that's a very myopic view of the situation: there are many cases where datatype

Re: [HACKERS] FDW for PostgreSQL

2013-02-20 Thread Shigeru Hanada
On Fri, Feb 15, 2013 at 12:58 AM, Tom Lane t...@sss.pgh.pa.us wrote: [ rereads that... ] Hm, I did make some good points. But having seen the end result of this way, I'm still not very happy; it still looks like a maintenance problem. Maybe some additional flags in ruleutils.c is the least

Re: [HACKERS] FDW for PostgreSQL

2013-02-20 Thread Tom Lane
Shigeru Hanada shigeru.han...@gmail.com writes: On Fri, Feb 15, 2013 at 12:58 AM, Tom Lane t...@sss.pgh.pa.us wrote: [ rereads that... ] Hm, I did make some good points. But having seen the end result of this way, I'm still not very happy; it still looks like a maintenance problem. Maybe

Re: [HACKERS] FDW for PostgreSQL

2013-02-17 Thread Tom Lane
Shigeru Hanada shigeru.han...@gmail.com writes: On Sun, Feb 17, 2013 at 8:44 AM, Tom Lane t...@sss.pgh.pa.us wrote: These don't seem to me like names that we ought to be exposing at the SQL command level. Why not just schema, table, column? Or perhaps schema_name, table_name, column_name if

Re: [HACKERS] FDW for PostgreSQL

2013-02-16 Thread Tom Lane
Continuing to look at this patch ... I'm wondering if any particular discussion went into choosing the FDW option names nspname, relname, and colname. These don't seem to me like names that we ought to be exposing at the SQL command level. Why not just schema, table, column? Or perhaps

Re: [HACKERS] FDW for PostgreSQL

2013-02-16 Thread Shigeru Hanada
On Sun, Feb 17, 2013 at 8:44 AM, Tom Lane t...@sss.pgh.pa.us wrote: Continuing to look at this patch ... I'm wondering if any particular discussion went into choosing the FDW option names nspname, relname, and colname. IIRC, there was no deep discussion about those option names. I simply

Re: [HACKERS] FDW for PostgreSQL

2013-02-14 Thread Shigeru Hanada
On Thu, Feb 14, 2013 at 1:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: * The code seems to always use GetOuterUserId() to select the foreign user mapping to use. This seems entirely wrong. For instance it will do the wrong thing inside a SECURITY DEFINER function, where surely the relevant

Re: [HACKERS] FDW for PostgreSQL

2013-02-14 Thread Albe Laurenz
Shigeru Hanada wrote: Tom Lane wrote: It ought to be pulling the rows back a few at a time, and that's not going to work well if multiple scans are sharing the same connection. (We might be able to dodge that by declaring a cursor for each scan, but I'm not convinced that such a

Re: [HACKERS] FDW for PostgreSQL

2013-02-14 Thread Shigeru Hanada
On Thu, Feb 14, 2013 at 6:45 PM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Shigeru Hanada wrote: Tom Lane wrote: It ought to be pulling the rows back a few at a time, and that's not going to work well if multiple scans are sharing the same connection. (We might be able to dodge

Re: [HACKERS] FDW for PostgreSQL

2013-02-14 Thread Tom Lane
Kohei KaiGai kai...@kaigai.gr.jp writes: 2013/2/14 Tom Lane t...@sss.pgh.pa.us: * deparse.c contains a depressingly large amount of duplication of logic from ruleutils.c, and can only need more as we expand the set of constructs that can be pushed to the remote end. This doesn't seem like a

Re: [HACKERS] FDW for PostgreSQL

2013-02-14 Thread Tom Lane
Shigeru Hanada shigeru.han...@gmail.com writes: On Thu, Feb 14, 2013 at 1:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: * AFAICT, the patch expects to use a single connection for all operations initiated under one foreign server + user mapping pair. I don't think this can possibly be workable.

Re: [HACKERS] FDW for PostgreSQL

2013-02-13 Thread Tom Lane
Shigeru Hanada shigeru.han...@gmail.com writes: [ postgres_fdw.v5.patch ] I started to look at this patch today. There seems to be quite a bit left to do to make it committable. I'm willing to work on it, but there are some things that need discussion: * The code seems to always use

Re: [HACKERS] FDW for PostgreSQL

2013-02-13 Thread Kohei KaiGai
2013/2/14 Tom Lane t...@sss.pgh.pa.us: * deparse.c contains a depressingly large amount of duplication of logic from ruleutils.c, and can only need more as we expand the set of constructs that can be pushed to the remote end. This doesn't seem like a maintainable approach. Was there a

Re: [HACKERS] FDW for PostgreSQL

2012-11-28 Thread Kohei KaiGai
2012/11/28 Shigeru Hanada shigeru.han...@gmail.com: On Sun, Nov 25, 2012 at 5:24 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I checked the v4 patch, and I have nothing to comment anymore. So, could you update the remaining EXPLAIN with VERBOSE option stuff? Thanks for the review. Here

Re: [HACKERS] FDW for PostgreSQL

2012-11-28 Thread Kohei KaiGai
2012/11/28 Kohei KaiGai kai...@kaigai.gr.jp: it is reasonable. So, postgre_fdw is OK for me. pgsql_fdw is also welcome. Sorry, s/postgre_fdw/postgres_fdw/g Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to

Re: [HACKERS] FDW for PostgreSQL

2012-11-24 Thread Kohei KaiGai
2012/11/21 Shigeru Hanada shigeru.han...@gmail.com: Thank for the comment! On Tue, Nov 20, 2012 at 10:23 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I also think the new use_remote_explain option is good. It works fine when we try to use this fdw over the network with latency more or less.

Re: [HACKERS] FDW for PostgreSQL

2012-11-22 Thread Shigeru Hanada
After playing with some big SQLs for testing, I came to feel that showing every remote query in EXPLAIN output is annoying, especially when SELECT * is unfolded to long column list. AFAIK no plan node shows so many information in a line, so I'm inclined to make postgres_fdw to show it only when

Re: [HACKERS] FDW for PostgreSQL

2012-11-22 Thread Kohei KaiGai
2012/11/22 Shigeru Hanada shigeru.han...@gmail.com: After playing with some big SQLs for testing, I came to feel that showing every remote query in EXPLAIN output is annoying, especially when SELECT * is unfolded to long column list. AFAIK no plan node shows so many information in a line, so

Re: [HACKERS] FDW for PostgreSQL

2012-11-22 Thread Albe Laurenz
Kohei KaiGai wrote: 2012/11/22 Shigeru Hanada shigeru.han...@gmail.com: After playing with some big SQLs for testing, I came to feel that showing every remote query in EXPLAIN output is annoying, especially when SELECT * is unfolded to long column list. AFAIK no plan node shows so many

Re: [HACKERS] FDW for PostgreSQL

2012-11-21 Thread Kohei KaiGai
2012/11/21 Shigeru Hanada shigeru.han...@gmail.com: Thank for the comment! On Tue, Nov 20, 2012 at 10:23 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I also think the new use_remote_explain option is good. It works fine when we try to use this fdw over the network with latency more or less.

Re: [HACKERS] FDW for PostgreSQL

2012-11-21 Thread Shigeru Hanada
On Wed, Nov 21, 2012 at 7:31 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: At execute_query(), it stores the retrieved rows onto tuplestore of festate-tuples at once. Doesn't it make problems when remote- table has very big number of rows? No. postgres_fdw uses single-row processing mode of

Re: [HACKERS] FDW for PostgreSQL

2012-11-20 Thread Kohei KaiGai
2012/11/15 Shigeru Hanada shigeru.han...@gmail.com: Hi Kaigai-san, Sorry for delayed response. I updated the patch, although I didn't change any about timing issue you and Fujita-san concern. 1) add some FDW options for cost estimation. Default behavior is not changed. 2) get rid of

Re: [HACKERS] FDW for PostgreSQL

2012-11-06 Thread Shigeru HANADA
Sorry for delayed response. On Sun, Oct 21, 2012 at 3:16 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2012/10/11 Etsuro Fujita fujita.ets...@lab.ntt.co.jp: I've reviewed your patch quickly. I noticed that the patch has been created in a slightly different way from the guidelines:

Re: [HACKERS] FDW for PostgreSQL

2012-11-06 Thread Kohei KaiGai
2012/11/6 Shigeru HANADA shigeru.han...@gmail.com: Sorry for delayed response. On Sun, Oct 21, 2012 at 3:16 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2012/10/11 Etsuro Fujita fujita.ets...@lab.ntt.co.jp: I've reviewed your patch quickly. I noticed that the patch has been created in a

Re: [HACKERS] FDW for PostgreSQL

2012-11-06 Thread Tom Lane
Kohei KaiGai kai...@kaigai.gr.jp writes: 2012/11/6 Shigeru HANADA shigeru.han...@gmail.com: Indeed postgresql_fdw touches baserestrictinfo in GetForeignRelSize, but it's because of optimization for better width estimate. The doc Fujita-san pointed says that: The actual identification of

Re: [HACKERS] FDW for PostgreSQL

2012-11-06 Thread 花田 茂
On 2012/11/07, at 1:35, Tom Lane t...@sss.pgh.pa.us wrote: Isn't it possible to pick-up only columns to be used in targetlist or local qualifiers, without modification of baserestrictinfo? What the doc means to suggest is that you can look through the baserestrictinfo list and then record

Re: [HACKERS] FDW for PostgreSQL

2012-11-06 Thread Tom Lane
=?iso-2022-jp?B?GyRCMlZFRBsoQiAbJEJMUBsoQg==?= shigeru.han...@gmail.com writes: ClassifyConditions creates new lists, local_conds and remote_conds, which have cells which point RestrictInfo(s) in baserestrictinfo. It doesn't copy RestrictInfo for new lists, but I think it's ok because

Re: [HACKERS] FDW for PostgreSQL

2012-10-23 Thread Alvaro Herrera
Shigeru HANADA escribió: Please examine attached v2 patch (note that is should be applied onto latest dblink_fdw_validator patch). Tom committed parts of the dblink_fdw_validator patch, but not the removal, so it seems this patch needs to be rebased on top of that somehow. I am not able to

Re: [HACKERS] FDW for PostgreSQL

2012-10-20 Thread Kohei KaiGai
2012/10/11 Etsuro Fujita fujita.ets...@lab.ntt.co.jp: Hi Hanada-san, Please examine attached v2 patch (note that is should be applied onto latest dblink_fdw_validator patch). I've reviewed your patch quickly. I noticed that the patch has been created in a slightly different way from the

Re: [HACKERS] FDW for PostgreSQL

2012-10-11 Thread Etsuro Fujita
Hi Hanada-san, Please examine attached v2 patch (note that is should be applied onto latest dblink_fdw_validator patch). I've reviewed your patch quickly. I noticed that the patch has been created in a slightly different way from the guidelines:

Re: [HACKERS] FDW for PostgreSQL

2012-10-03 Thread Kohei KaiGai
Hanada-san, I tried to check this patch. Because we also had some discussion on this extension through the last two commit fests, I have no fundamental design arguments. So, let me drop in the implementation detail of this patch. At the postgresql_fdw/deparse.c, * Even though deparseVar() is

Re: [HACKERS] FDW for PostgreSQL

2012-10-03 Thread Merlin Moncure
On Fri, Sep 14, 2012 at 9:25 AM, Shigeru HANADA shigeru.han...@gmail.com wrote: Hi all, I'd like to propose FDW for PostgreSQL as a contrib module again. Attached patch is updated version of the patch proposed in 9.2 development cycle. very nice. - binary transfer (only against servers