Re: FOREIGN TABLE doc fix

Hi hackers,

At first I've posted to pgsql-docs but there is no reply at present.

http://archives.postgresql.org/pgsql-docs/2011-06/msg00006.php

So I post revised patch to pgsql-hackers, because the patch is for
documents under development for 9.1 beta2. Please let me know if this
was wrong list to discuss these issues.

Attached patch includes fixes for FOREIGN TABLE documents:

1) "NOT NULL" should be added to syntax of ALTER FOREIGN TABLE ADD
COLUMN command.

2) Mentions about unsupported features should be removed from ALTER
FOREIGN TABLE document. They had been proposed but haven't been
committed for 9.1. These unsupported features are not mentioned in
CREATE FOREIGN TABLE document.

- table inheritance
- oid system column
- CHECK constraint
- per-column FDW options

3) It would be useful to mention differences between ordinary tables and
foreign tables in CREATE FOREIGN TABLE document.

- NOT NULL constraint is just for optimization, at least at present
- serial/bigserial can't be used because they need default value

The description in the patch are based on the advice which were posted
by Thom Brown and Robert Haas in the thread:

http://archives.postgresql.org/pgsql-hackers/2011-03/msg01949.php

Regards,
--
Shigeru Hanada

2011/6/9 Shigeru Hanada <hanada(at)metrosystems(dot)co(dot)jp>:
> Attached patch includes fixes for FOREIGN TABLE documents:

I committed the changes to ALTER FOREIGN TABLE, but I think the
changes to CREATE FOREIGN TABLE need more thought. The first of the
two hunks you've proposed to add doesn't seem necessary to me, and the
second one seems like it belongs in a chapter on how to write a
foreign data wrapper correctly, rather than here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Thanks for the review.

(2011/06/12 13:21), Robert Haas wrote:
> 2011/6/9 Shigeru Hanada<hanada(at)metrosystems(dot)co(dot)jp>:
>> Attached patch includes fixes for FOREIGN TABLE documents:
>
> I committed the changes to ALTER FOREIGN TABLE, but I think the
> changes to CREATE FOREIGN TABLE need more thought. The first of the
> two hunks you've proposed to add doesn't seem necessary to me, and the
> second one seems like it belongs in a chapter on how to write a
> foreign data wrapper correctly, rather than here.

Agreed. How about the section for IterateForeignScan() in "50.1.
Foreign Data Wrapper Callback Routines"[1] for the second hunk? It
seems proper place to describe responsibility about applying NOT NULL
constraint, because it would be where the author works for the issue.
The section also mentions responsibility of column signature matching.

By the way, I found another document issue. "5.10. Foreign Data"[2] says
that FDW for PG is available alike FDW for files, but postgresql_fdw
won't be available for 9.1 release, at least as a bundled extension.
ISTM that such mention should be removed to avoid misunderstanding.

Please find attached the revised patch.

[1] http://developer.postgresql.org/pgdocs/postgres/fdw-routines.html
[2] http://developer.postgresql.org/pgdocs/postgres/ddl-foreign-data.html

Regards,
--
Shigeru Hanada

2011/6/13 Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>:
> Thanks for the review.
>
> (2011/06/12 13:21), Robert Haas wrote:
>> 2011/6/9 Shigeru Hanada<hanada(at)metrosystems(dot)co(dot)jp>:
>>> Attached patch includes fixes for FOREIGN TABLE documents:
>>
>> I committed the changes to ALTER FOREIGN TABLE, but I think the
>> changes to CREATE FOREIGN TABLE need more thought.  The first of the
>> two hunks you've proposed to add doesn't seem necessary to me, and the
>> second one seems like it belongs in a chapter on how to write a
>> foreign data wrapper correctly, rather than here.
>
> Agreed.  How about the section for IterateForeignScan() in "50.1.
> Foreign Data Wrapper Callback Routines"[1] for the second hunk?  It
> seems proper place to describe responsibility about applying NOT NULL
> constraint, because it would be where the author works for the issue.
> The section also mentions responsibility of column signature matching.
>
> By the way, I found another document issue. "5.10. Foreign Data"[2] says
> that FDW for PG is available alike FDW for files, but postgresql_fdw
> won't be available for 9.1 release, at least as a bundled extension.
> ISTM that such mention should be removed to avoid misunderstanding.
>
> Please find attached the revised patch.

Committed, with some additional word-smithing.

Incidentally, are you planning to revive the PostgreSQL FDW for 9.2?
That would be a killer feature.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Incidentally, are you planning to revive the PostgreSQL FDW for 9.2?
> That would be a killer feature.

Even more killer would be that it could be built/packaged as an
extension, and use for 9.1 too ;-)

a.

--
Aidan Van Dyk                                             Create like a god,
aidan(at)highrise(dot)ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

On Mon, Jun 13, 2011 at 9:03 AM, Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
> On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Incidentally, are you planning to revive the PostgreSQL FDW for 9.2?
>> That would be a killer feature.
>
> Even more killer would be that it could be built/packaged as an
> extension, and use for 9.1 too ;-)

+1!

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jun 13, 2011 at 9:03 AM, Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
>> On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> Incidentally, are you planning to revive the PostgreSQL FDW for 9.2?
>>> That would be a killer feature.

>> Even more killer would be that it could be built/packaged as an
>> extension, and use for 9.1 too ;-)

> +1!

Don't hold your breath. We'll probably be making enough changes in the
FDW infrastructure (particularly planner support) that making an FDW
work on both 9.1 and 9.2 would be an exercise in frustration, if it's
even possible.

regards, tom lane

On Mon, Jun 13, 2011 at 3:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jun 13, 2011 at 9:03 AM, Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
>>> On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>>> Incidentally, are you planning to revive the PostgreSQL FDW for 9.2?
>>>> That would be a killer feature.
>
>>> Even more killer would be that it could be built/packaged as an
>>> extension, and use for 9.1 too ;-)
>
>> +1!
>
> Don't hold your breath.  We'll probably be making enough changes in the
> FDW infrastructure (particularly planner support) that making an FDW
> work on both 9.1 and 9.2 would be an exercise in frustration, if it's
> even possible.

Oh joy. There's a GSoC student working on 2 non-trivial FDW's right
now, and I have a couple I've been working on. If we're going to make
the API incompatible to that extent, we might as well not bother :-(

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Mon, Jun 13, 2011 at 3:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Don't hold your breath. We'll probably be making enough changes in the
>> FDW infrastructure (particularly planner support) that making an FDW
>> work on both 9.1 and 9.2 would be an exercise in frustration, if it's
>> even possible.

> Oh joy. There's a GSoC student working on 2 non-trivial FDW's right
> now, and I have a couple I've been working on. If we're going to make
> the API incompatible to that extent, we might as well not bother :-(

Oh, that's by no means a waste of time --- we need some examples to help
us figure out where the pain points are. I'm just saying that the best
ways to do things will probably change quite a bit as we introduce
solutions for the pain points. And I don't intend to be too concerned
about preserving backwards compatibility at this stage.

regards, tom lane

On Mon, Jun 13, 2011 at 3:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Mon, Jun 13, 2011 at 3:20 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Don't hold your breath.  We'll probably be making enough changes in the
>>> FDW infrastructure (particularly planner support) that making an FDW
>>> work on both 9.1 and 9.2 would be an exercise in frustration, if it's
>>> even possible.
>
>> Oh joy. There's a GSoC student working on 2 non-trivial FDW's right
>> now, and I have a couple I've been working on. If we're going to make
>> the API incompatible to that extent, we might as well not bother :-(
>
> Oh, that's by no means a waste of time --- we need some examples to help
> us figure out where the pain points are.  I'm just saying that the best
> ways to do things will probably change quite a bit as we introduce
> solutions for the pain points.  And I don't intend to be too concerned
> about preserving backwards compatibility at this stage.

No problem with providing feedback on pain points, however we're
trying to write production quality code that can be used by people
sooner rather than later, in my case, in my own time. If^WNow I know
I'm likely to have to rewrite it for 9.2, it's significantly harder to
find any kind of enthusiasm to work on it for 9.1.

I think we need to figure out a way to maintain a certain level of
backwards compatibility that isn't going to require massive rewrites,
or people just won't bother with SQL/MED until they know the API is
stable. I know I realised it would change, but I assumed we would
either add new optional function calls, or implement a v2 interface
whilst continuing to support the v1 interface.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jun 13, 2011 at 10:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jun 13, 2011 at 9:03 AM, Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
>>> On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>>> Incidentally, are you planning to revive the PostgreSQL FDW for 9.2?
>>>> That would be a killer feature.
>
>>> Even more killer would be that it could be built/packaged as an
>>> extension, and use for 9.1 too ;-)
>
>> +1!
>
> Don't hold your breath.  We'll probably be making enough changes in the
> FDW infrastructure (particularly planner support) that making an FDW
> work on both 9.1 and 9.2 would be an exercise in frustration, if it's
> even possible.

Well, so far the people who seem willing to work on such changes are
not exactly thick on the ground, so I think it might be a little
premature to speculate about what changes they might make when they
show up.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Mon, Jun 13, 2011 at 3:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Oh, that's by no means a waste of time --- we need some examples to help
>> us figure out where the pain points are. I'm just saying that the best
>> ways to do things will probably change quite a bit as we introduce
>> solutions for the pain points. And I don't intend to be too concerned
>> about preserving backwards compatibility at this stage.

> No problem with providing feedback on pain points, however we're
> trying to write production quality code that can be used by people
> sooner rather than later, in my case, in my own time. If^WNow I know
> I'm likely to have to rewrite it for 9.2, it's significantly harder to
> find any kind of enthusiasm to work on it for 9.1.

> I think we need to figure out a way to maintain a certain level of
> backwards compatibility that isn't going to require massive rewrites,
> or people just won't bother with SQL/MED until they know the API is
> stable. I know I realised it would change, but I assumed we would
> either add new optional function calls, or implement a v2 interface
> whilst continuing to support the v1 interface.

The problem here is not so much that we're going to change APIs as that
we don't *have* APIs, in the sense of something we're committing to not
changing. Until we do, you're pretty much coding on sand. I don't
intend to be bound by some concept of "we can't change the planner
because somebody somewhere might be depending on XYZ in their
first-generation FDW". If you're not willing to adapt, then yes, you
should not be writing FDWs yet. Depending on what your idea of "stable"
is, maybe you shouldn't be writing FDWs ever.

Of course, how much you're affected by all this depends on what you're
doing. Something like file_fdw doesn't really have any intelligent
planning to do, so it's likely that it wouldn't get broken too badly by
any changes in the near future --- though I'd still hope that eventually
we have some cleaner APIs for it to call than what you see in its
estimate_costs() today. But a postgresql FDW will need to get pretty
darn intimate with the planner in order to be any good, and I absolutely
will *not* make any promises that code like that will continue to work
unchanged in future versions. To do so would be tantamount to decreeing
that all progress on the planner stopped dead yesterday.

regards, tom lane

On 06/13/2011 10:25 AM, Dave Page wrote:
>
>> Don't hold your breath. We'll probably be making enough changes in the
>> FDW infrastructure (particularly planner support) that making an FDW
>> work on both 9.1 and 9.2 would be an exercise in frustration, if it's
>> even possible.
> Oh joy. There's a GSoC student working on 2 non-trivial FDW's right
> now, and I have a couple I've been working on. If we're going to make
> the API incompatible to that extent, we might as well not bother :-(
>

If nobody bothers then there won't be any experience on which to base a
stable API. In particular, I think it's crucial that we get working FDWs
for MySQL, SQLServer and Oracle ASAP.

cheers

andrew

On Mon, Jun 13, 2011 at 4:38 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> On 06/13/2011 10:25 AM, Dave Page wrote:
>>
>>> Don't hold your breath.  We'll probably be making enough changes in the
>>> FDW infrastructure (particularly planner support) that making an FDW
>>> work on both 9.1 and 9.2 would be an exercise in frustration, if it's
>>> even possible.
>>
>> Oh joy. There's a GSoC student working on 2 non-trivial FDW's right
>> now, and I have a couple I've been working on. If we're going to make
>> the API incompatible to that extent, we might as well not bother :-(
>>
>
> If nobody bothers then there won't be any experience on which to base a
> stable API. In particular, I think it's crucial that we get working FDWs for
> MySQL, SQLServer and Oracle ASAP.

Yeah - MySQL is one of the ones I've been hacking on. It's hard to be
motivated if its going to need a complete rewrite within a year
though. I'll still have to work on it, as I've committed to giving
talks on it, but others might not bother to even start.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jun 13, 2011 at 3:54 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> Yeah - MySQL is one of the ones I've been hacking on. It's hard to be
> motivated if its going to need a complete rewrite within a year
> though. I'll still have to work on it, as I've committed to giving
> talks on it, but others might not bother to even start.

It's a double-edged sword. If nobody writes anything, because
everyone is afraid to possibly having to change things, nothing will
never need to be changed ;-)

a.

--
Aidan Van Dyk                                             Create like a god,
aidan(at)highrise(dot)ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

On Mon, Jun 13, 2011 at 4:08 PM, Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
> On Mon, Jun 13, 2011 at 3:54 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>> Yeah - MySQL is one of the ones I've been hacking on. It's hard to be
>> motivated if its going to need a complete rewrite within a year
>> though. I'll still have to work on it, as I've committed to giving
>> talks on it, but others might not bother to even start.
>
> It's a double-edged sword.  If nobody writes anything, because
> everyone is afraid to possibly having to change things, nothing will
> never need to be changed ;-)

It might be that the process of writing the MySQL FDW code would show
off things that'll need to get changed.

So the breakage might turn out to be Dave's fault! :-)

[Seriously.]

We really won't know what needs fixing/improving until nontrivial FDWs
get written, and it would be somewhat ironic, but really not hugely
surprising, if Dave wound up requesting changes to the underlying API
to *properly* support what he writes.

There's some degree of irony and amusement to be found here, but
nothing that strikes me as disturbing.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Mon, Jun 13, 2011 at 5:29 PM, Christopher Browne <cbbrowne(at)gmail(dot)com> wrote:
> On Mon, Jun 13, 2011 at 4:08 PM, Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
>> On Mon, Jun 13, 2011 at 3:54 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>>> Yeah - MySQL is one of the ones I've been hacking on. It's hard to be
>>> motivated if its going to need a complete rewrite within a year
>>> though. I'll still have to work on it, as I've committed to giving
>>> talks on it, but others might not bother to even start.
>>
>> It's a double-edged sword.  If nobody writes anything, because
>> everyone is afraid to possibly having to change things, nothing will
>> never need to be changed ;-)
>
> It might be that the process of writing the MySQL FDW code would show
> off things that'll need to get changed.
>
> So the breakage might turn out to be Dave's fault!  :-)
>
> [Seriously.]
>
> We really won't know what needs fixing/improving until nontrivial FDWs
> get written, and it would be somewhat ironic, but really not hugely
> surprising, if Dave wound up requesting changes to the underlying API
> to *properly* support what he writes.
>
> There's some degree of irony and amusement to be found here, but
> nothing that strikes me as disturbing.

Oh, I can imagine that happening; what I would expect though is that
we make some attempt to retain compatibility to avoid the need for
total rewrites of FDWs as Tom seems to be expecting.

BTW; it seems to me this should be documented, as it could really hack
off developers. I can't see anything in the docs to imply the API
might be radically redesigned.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jun 13, 2011 at 12:38 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Mon, Jun 13, 2011 at 5:29 PM, Christopher Browne <cbbrowne(at)gmail(dot)com> wrote:
>> On Mon, Jun 13, 2011 at 4:08 PM, Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
>>> On Mon, Jun 13, 2011 at 3:54 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>
>>>> Yeah - MySQL is one of the ones I've been hacking on. It's hard to be
>>>> motivated if its going to need a complete rewrite within a year
>>>> though. I'll still have to work on it, as I've committed to giving
>>>> talks on it, but others might not bother to even start.
>>>
>>> It's a double-edged sword.  If nobody writes anything, because
>>> everyone is afraid to possibly having to change things, nothing will
>>> never need to be changed ;-)
>>
>> It might be that the process of writing the MySQL FDW code would show
>> off things that'll need to get changed.
>>
>> So the breakage might turn out to be Dave's fault!  :-)
>>
>> [Seriously.]
>>
>> We really won't know what needs fixing/improving until nontrivial FDWs
>> get written, and it would be somewhat ironic, but really not hugely
>> surprising, if Dave wound up requesting changes to the underlying API
>> to *properly* support what he writes.
>>
>> There's some degree of irony and amusement to be found here, but
>> nothing that strikes me as disturbing.
>
> Oh, I can imagine that happening; what I would expect though is that
> we make some attempt to retain compatibility to avoid the need for
> total rewrites of FDWs as Tom seems to be expecting.
>
> BTW; it seems to me this should be documented, as it could really hack
> off developers. I can't see anything in the docs to imply the API
> might be radically redesigned.

And I'm still unconvinced that it's needed. I think we're going to
end up adding on things that are missing and maybe replacing things
that are just stubs, but I don't see why we'd whack it around just for
fun.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jun 13, 2011 at 12:38 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>> BTW; it seems to me this should be documented, as it could really hack
>> off developers. I can't see anything in the docs to imply the API
>> might be radically redesigned.

> And I'm still unconvinced that it's needed. I think we're going to
> end up adding on things that are missing and maybe replacing things
> that are just stubs, but I don't see why we'd whack it around just for
> fun.

I think we're talking past each other. The point I'm trying to make is
that there are no defined/documented APIs for most of the planner, and
so any FDW that needs to do nontrivial planning stuff will need to reach
into pieces of the code that we've historically felt free to change as
needed. We can't just suddenly decide that all that code is now locked
down on the grounds that somebody might be touching it. As an example,
assuming that I figure out how to do generalized parameterized inner
plans in 9.2, whether or not the changes required might break somebody's
FDW is simply not going to be a consideration.

Once we have some idea of exactly which aspects of the planner FDWs are
likely to need to depend on, we can write down some API contracts and
then try to adhere to them. But we don't have those today, and I don't
think it's profitable to try to write them until we have some more
experience with writing nontrivial FDWs.

In practice this might turn out to be less of a problem than Dave
thinks. We've made plenty of changes in the past that could affect
third-party selectivity functions, and lately we've been adding planner
hooks that likewise are seeing call contexts that change from version to
version; but I've not heard very many complaints about those
instabilities. So maybe the average FDW won't find this to be a big
deal either. What I was reacting to at the start of this sub-thread was
the idea that the remote-postgresql FDW in particular would be
cross-version compatible. That's not an "average" FDW; I think that it
will have enough planner dependencies to be a poster child for these
issues.

regards, tom lane

On Mon, Jun 13, 2011 at 6:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jun 13, 2011 at 12:38 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>> BTW; it seems to me this should be documented, as it could really hack
>>> off developers. I can't see anything in the docs to imply the API
>>> might be radically redesigned.
>
>> And I'm still unconvinced that it's needed.  I think we're going to
>> end up adding on things that are missing and maybe replacing things
>> that are just stubs, but I don't see why we'd whack it around just for
>> fun.
>
> I think we're talking past each other.  The point I'm trying to make is
> that there are no defined/documented APIs for most of the planner, and
> so any FDW that needs to do nontrivial planning stuff will need to reach
> into pieces of the code that we've historically felt free to change as
> needed.  We can't just suddenly decide that all that code is now locked
> down on the grounds that somebody might be touching it.  As an example,
> assuming that I figure out how to do generalized parameterized inner
> plans in 9.2, whether or not the changes required might break somebody's
> FDW is simply not going to be a consideration.

Hmm, I wonder if you're correct (as usual :-p). I thought you were
talking about the API as defined here:
http://www.postgresql.org/docs/9.1/static/fdw-routines.html, not
internal planner stuff. I agree that if I use that (and I have, but
only minimally), it should be on my own head.

> In practice this might turn out to be less of a problem than Dave
> thinks.  We've made plenty of changes in the past that could affect
> third-party selectivity functions, and lately we've been adding planner
> hooks that likewise are seeing call contexts that change from version to
> version; but I've not heard very many complaints about those
> instabilities.

I've certainly seen similar issues with the debugger plugin for
example - but that's not using a documented API, bar a couple of
hooks.

> So maybe the average FDW won't find this to be a big
> deal either.  What I was reacting to at the start of this sub-thread was
> the idea that the remote-postgresql FDW in particular would be
> cross-version compatible.  That's not an "average" FDW; I think that it
> will have enough planner dependencies to be a poster child for these
> issues.

That I can understand - you'll have to forgive me for reading more
into "making an FDW work on both 9.1 and 9.2 would be an exercise in
frustration, if it's even possible." though :-)

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jun 13, 2011 at 1:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jun 13, 2011 at 12:38 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>> BTW; it seems to me this should be documented, as it could really hack
>>> off developers. I can't see anything in the docs to imply the API
>>> might be radically redesigned.
>
>> And I'm still unconvinced that it's needed.  I think we're going to
>> end up adding on things that are missing and maybe replacing things
>> that are just stubs, but I don't see why we'd whack it around just for
>> fun.
>
> I think we're talking past each other.  The point I'm trying to make is
> that there are no defined/documented APIs for most of the planner, and
> so any FDW that needs to do nontrivial planning stuff will need to reach
> into pieces of the code that we've historically felt free to change as
> needed.  We can't just suddenly decide that all that code is now locked
> down on the grounds that somebody might be touching it.  As an example,
> assuming that I figure out how to do generalized parameterized inner
> plans in 9.2, whether or not the changes required might break somebody's
> FDW is simply not going to be a consideration.
>
> Once we have some idea of exactly which aspects of the planner FDWs are
> likely to need to depend on, we can write down some API contracts and
> then try to adhere to them.  But we don't have those today, and I don't
> think it's profitable to try to write them until we have some more
> experience with writing nontrivial FDWs.
>
> In practice this might turn out to be less of a problem than Dave
> thinks.  We've made plenty of changes in the past that could affect
> third-party selectivity functions, and lately we've been adding planner
> hooks that likewise are seeing call contexts that change from version to
> version; but I've not heard very many complaints about those
> instabilities.  So maybe the average FDW won't find this to be a big
> deal either.  What I was reacting to at the start of this sub-thread was
> the idea that the remote-postgresql FDW in particular would be
> cross-version compatible.  That's not an "average" FDW; I think that it
> will have enough planner dependencies to be a poster child for these
> issues.

But my point is: any FDW code Dave rights now is not going to have
major dependencies on the planner that will potentially require
extensive reworking in the future because it won't have any real
dependencies on the planner at all. It's not like we have an API and
we're planning to change it: what we have to talk to the planner right
now is little more than a stub. Unless I miss my guess, the work Dave
et al are doing right now is just around making PostgreSQL talk to X,
for various values of X. Now, if we expose an API to allow qual
pushdown, all of those FDWs will need to be updated if they want to
support qual pushdown (and maybe even a little bit, if they don't).
But the work of, say, making it possible to translate MySQL tuples
into PostgreSQL tuples doesn't seem likely to be wasted.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Jun 13, 2011 at 7:08 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> But my point is: any FDW code Dave rights now is not going to have
> major dependencies on the planner that will potentially require
> extensive reworking in the future because it won't have any real
> dependencies on the planner at all.  It's not like we have an API and
> we're planning to change it: what we have to talk to the planner right
> now is little more than a stub.  Unless I miss my guess, the work Dave
> et al are doing right now is just around making PostgreSQL talk to X,
> for various values of X.  Now, if we expose an API to allow qual
> pushdown, all of those FDWs will need to be updated if they want to
> support qual pushdown (and maybe even a little bit, if they don't).
> But the work of, say, making it possible to translate MySQL tuples
> into PostgreSQL tuples doesn't seem likely to be wasted.

Right - that's what I thought Tom was saying would be junked.

I've already implemented some simple qual pushdown in the redis FDW,
and am planning to do something similar for MySQL - however I won't be
surprised if I have to rewrite redisGetQual in
https://github.com/dpage/redis_fdw/blob/master/redis_fdw.c for
example.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Mon, Jun 13, 2011 at 6:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think we're talking past each other.

> Hmm, I wonder if you're correct (as usual :-p). I thought you were
> talking about the API as defined here:
> http://www.postgresql.org/docs/9.1/static/fdw-routines.html, not
> internal planner stuff. I agree that if I use that (and I have, but
> only minimally), it should be on my own head.

Well, you'll notice that that document is mighty handwavy about exactly
what PlanForeignScan needs to do to accomplish its responsibilities...

But as far as breaking things at that level of detail is concerned, the
main thing I can foresee is that doing a parameterized inner scan on a
foreign table is both extremely desirable, and unsupportable given this
contract for PlanForeignScan. We'll need to either add more parameters
to it or invent a different entry point for considering parameterized
scans.

regards, tom lane

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> But my point is: any FDW code Dave rights now is not going to have
> major dependencies on the planner that will potentially require
> extensive reworking in the future because it won't have any real
> dependencies on the planner at all. It's not like we have an API and
> we're planning to change it: what we have to talk to the planner right
> now is little more than a stub.

No, what we have to talk to the planner right now is "look through all
of src/include/optimizer/ and call whatever you want, and maybe lift
some code out of src/backend/optimizer/ if the function you need isn't
exported". I agree that a lot of basic FDW work can be done without
much of any planner contact, but as soon as you do get interested in
having non-brain-dead planning behavior in your FDW, you're going to be
doing stuff that way. We're going to want to codify it a bit better
than that; but it's not going to be practical to do so until some people
have taken the plunge and written some code on the understanding that
it's probably throwaway code.

regards, tom lane

Dave Page <dpage(at)pgadmin(dot)org> writes:
> I've already implemented some simple qual pushdown in the redis FDW,
> and am planning to do something similar for MySQL - however I won't be
> surprised if I have to rewrite redisGetQual in
> https://github.com/dpage/redis_fdw/blob/master/redis_fdw.c for
> example.

OK, *now* we're on the same page. This sort of experimentation is
exactly what I'm talking about: we'll certainly want to rewrite the code
once we have better infrastructure, but it's necessary to write some
throwaway code while we're learning what infrastructure FDWs want.

(I find it a bit weird btw that you seem to be doing that at execution
time not plan time...)

regards, tom lane

(2011/06/13 21:30), Robert Haas wrote:
> Committed, with some additional word-smithing.

Thanks.

> Incidentally, are you planning to revive the PostgreSQL FDW for 9.2?
> That would be a killer feature.

Yes, I would like to work on pgsql_fdw and other SQL/MED issues in 9.2
development cycle. I'm planning to start major pgsql_fdw work after CF
2011-06, and before that I'm going to work on some common SQL/MED issues
such as per-column options and table inheritance.

Regards,
--
Shigeru Hanada

On Mon, Jun 13, 2011 at 8:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> I've already implemented some simple qual pushdown in the redis FDW,
>> and am planning to do something similar for MySQL - however I won't be
>> surprised if I have to rewrite redisGetQual in
>> https://github.com/dpage/redis_fdw/blob/master/redis_fdw.c for
>> example.
>
> OK, *now* we're on the same page.  This sort of experimentation is
> exactly what I'm talking about: we'll certainly want to rewrite the code
> once we have better infrastructure, but it's necessary to write some
> throwaway code while we're learning what infrastructure FDWs want.
>
> (I find it a bit weird btw that you seem to be doing that at execution
> time not plan time...)

That's largely because as we discussed a while back
(http://archives.postgresql.org/pgsql-hackers/2011-04/msg00084.php),
persisting data from plan time to scan time isn't exactly clean.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Right, but I think he needs the "it's not easy, here's the whole
workflow" overview first.

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE

On Mon, Jun 13, 2011 at 04:54:13PM +0100, Dave Page wrote:
> On Mon, Jun 13, 2011 at 4:38 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> >
> >
> > On 06/13/2011 10:25 AM, Dave Page wrote:
> >>
> >>> Don't hold your breath.  We'll probably be making enough changes in the
> >>> FDW infrastructure (particularly planner support) that making an FDW
> >>> work on both 9.1 and 9.2 would be an exercise in frustration, if it's
> >>> even possible.
> >>
> >> Oh joy. There's a GSoC student working on 2 non-trivial FDW's right
> >> now, and I have a couple I've been working on. If we're going to make
> >> the API incompatible to that extent, we might as well not bother :-(
> >>
> >
> > If nobody bothers then there won't be any experience on which to base a
> > stable API. In particular, I think it's crucial that we get working FDWs for
> > MySQL, SQLServer and Oracle ASAP.
>
> Yeah - MySQL is one of the ones I've been hacking on. It's hard to be
> motivated if its going to need a complete rewrite within a year
> though. I'll still have to work on it, as I've committed to giving
> talks on it, but others might not bother to even start.

I think PostgreSQL has a better track record (especially recently) than
most open source projects at supporting the shared incremental creation and
improvement of first-class features. Yes, getting the first cut of FDW
in place was hard: now it's time for users of that feature to take the
leap of faith and write some code. The faith bit is that others _will_
come forward to help with the rewrites necessary to make it work (or
work better) for their use cases.