Re: PostgreSQL FDW update

I needed something to test the FDW API patch with, and didn't want to
get involved in the COPY API changes, and also wanted to have something
that needs real connection management and can push down quals. So I
updated the postgresql_fdw patch to work with the latest FDW patch.

Here. It's a bit of a mess, but it works for simple queries..

It requires a small change to the FDW api
(fdw-api-add-serverid-userid.patch). I added server oid and user oid
fields to the FdwPlan - that seems like basic information that most
FDW's will need, so it seems awkward to require the FDW to wrap them in
Const nodes and a List.

These are also available in my git repository at
git://git.postgresql.org/git/users/heikki/postgres.git, branches fdw2
and postgresql_fdw.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, Feb 8, 2011 at 3:52 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> I needed something to test the FDW API patch with, and didn't want to get
> involved in the COPY API changes, and also wanted to have something that
> needs real connection management and can push down quals. So I updated the
> postgresql_fdw patch to work with the latest FDW patch.
>
> Here. It's a bit of a mess, but it works for simple queries..
>
> It requires a small change to the FDW api
> (fdw-api-add-serverid-userid.patch). I added server oid and user oid fields
> to the FdwPlan - that seems like basic information that most FDW's will
> need, so it seems awkward to require the FDW to wrap them in Const nodes and
> a List.
>
> These are also available in my git repository at
> git://git.postgresql.org/git/users/heikki/postgres.git, branches fdw2 and
> postgresql_fdw.

So how close are we to getting the core support committed?

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

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> I needed something to test the FDW API patch with, and didn't want to
> get involved in the COPY API changes, and also wanted to have something
> that needs real connection management and can push down quals. So I
> updated the postgresql_fdw patch to work with the latest FDW patch.

> Here. It's a bit of a mess, but it works for simple queries..

I'm going to set this CF item back to Waiting on Author, because (a)
there doesn't seem to be an actual concrete patch presented at the
moment, only a stack of hard-to-follow deltas, and (b) the patch
certainly needs updates for the extension mechanism and committed
FDW API anyway.

> It requires a small change to the FDW api
> (fdw-api-add-serverid-userid.patch). I added server oid and user oid
> fields to the FdwPlan - that seems like basic information that most
> FDW's will need, so it seems awkward to require the FDW to wrap them in
> Const nodes and a List.

FYI, I removed those fields from the committed API patch. I don't
think it's a good idea to cache potentially-changeable things in a
plan node when you can perfectly well fetch them at runtime. Caching
user OID is flat wrong anyway, as there's no guarantee that a cached
plan will be executed under the same userid it was made with.

regards, tom lane

On Mon, Feb 21, 2011 at 10:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> I needed something to test the FDW API patch with, and didn't want to
>> get involved in the COPY API changes, and also wanted to have something
>> that needs real connection management and can push down quals. So I
>> updated the postgresql_fdw patch to work with the latest FDW patch.
>
>> Here. It's a bit of a mess, but it works for simple queries..
>
> I'm going to set this CF item back to Waiting on Author, because (a)
> there doesn't seem to be an actual concrete patch presented at the
> moment, only a stack of hard-to-follow deltas, and (b) the patch
> certainly needs updates for the extension mechanism and committed
> FDW API anyway.

Is anyone actually working on a new version of this patch sufficiently
rapidly that we can expect a new version in the next day or two?

If not, I think we mark this one Returned with Feedback and revisit it for 9.2.

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

On Tue, 22 Feb 2011 11:33:25 -0500
Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Is anyone actually working on a new version of this patch sufficiently
> rapidly that we can expect a new version in the next day or two?
>
> If not, I think we mark this one Returned with Feedback and revisit it for 9.2.

I'm working on it.

Fixes for new FDW API have been done, but there are some problems in
SQL generation codes, such as SELECT clause optimization (omitting
unused column from SELECT clause). It would take a while, but I'll
post revised version of the patch tomorrow.

Regards,
--
Shigeru Hanada

On Wed, 23 Feb 2011 20:30:05 +0900
Shigeru HANADA <hanada(at)metrosystems(dot)co(dot)jp> wrote:

>
> On Tue, 22 Feb 2011 11:33:25 -0500
> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > Is anyone actually working on a new version of this patch sufficiently
> > rapidly that we can expect a new version in the next day or two?
> >
> > If not, I think we mark this one Returned with Feedback and revisit it for 9.2.
>
> I'm working on it.
>
> Fixes for new FDW API have been done, but there are some problems in
> SQL generation codes, such as SELECT clause optimization (omitting
> unused column from SELECT clause). It would take a while, but I'll
> post revised version of the patch tomorrow.

Attached is a revised version of postgresql_fdw patch. I started from
Heikki's latest patch, and modified some points:

1) use new FDW API
2) use EXTENSION framework
3) SELECT clause optimization (use NULL for unused columns)
4) show remote query in EXPLAIN output

WHERE clause pushdown was implemented in Heikki's version, so I didn't
touch around it. Now I'm working on cost estimation and connection
management, but they would need some more work.

--
Shigeru Hanada

On Thu, Feb 24, 2011 at 8:13 AM, Shigeru HANADA
<hanada(at)metrosystems(dot)co(dot)jp> wrote:
>
> On Wed, 23 Feb 2011 20:30:05 +0900
> Shigeru HANADA <hanada(at)metrosystems(dot)co(dot)jp> wrote:
>
>>
>> On Tue, 22 Feb 2011 11:33:25 -0500
>> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> > Is anyone actually working on a new version of this patch sufficiently
>> > rapidly that we can expect a new version in the next day or two?
>> >
>> > If not, I think we mark this one Returned with Feedback and revisit it for 9.2.
>>
>> I'm working on it.
>>
>> Fixes for new FDW API have been done, but there are some problems in
>> SQL generation codes, such as SELECT clause optimization (omitting
>> unused column from SELECT clause).  It would take a while, but I'll
>> post revised version of the patch tomorrow.
>
> Attached is a revised version of postgresql_fdw patch.  I started from
> Heikki's latest patch, and modified some points:
>
> 1) use new FDW API
> 2) use EXTENSION framework
> 3) SELECT clause optimization (use NULL for unused columns)
> 4) show remote query in EXPLAIN output
>
> WHERE clause pushdown was implemented in Heikki's version, so I didn't
> touch around it.  Now I'm working on cost estimation and connection
> management, but they would need some more work.

So this is still work-in-progress? When do you expect a final version?

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

On Thu, Feb 24, 2011 at 9:06 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Feb 24, 2011 at 8:13 AM, Shigeru HANADA
> <hanada(at)metrosystems(dot)co(dot)jp> wrote:
>>
>> On Wed, 23 Feb 2011 20:30:05 +0900
>> Shigeru HANADA <hanada(at)metrosystems(dot)co(dot)jp> wrote:
>>
>>>
>>> On Tue, 22 Feb 2011 11:33:25 -0500
>>> Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> > Is anyone actually working on a new version of this patch sufficiently
>>> > rapidly that we can expect a new version in the next day or two?
>>> >
>>> > If not, I think we mark this one Returned with Feedback and revisit it for 9.2.
>>>
>>> I'm working on it.
>>>
>>> Fixes for new FDW API have been done, but there are some problems in
>>> SQL generation codes, such as SELECT clause optimization (omitting
>>> unused column from SELECT clause).  It would take a while, but I'll
>>> post revised version of the patch tomorrow.
>>
>> Attached is a revised version of postgresql_fdw patch.  I started from
>> Heikki's latest patch, and modified some points:
>>
>> 1) use new FDW API
>> 2) use EXTENSION framework
>> 3) SELECT clause optimization (use NULL for unused columns)
>> 4) show remote query in EXPLAIN output
>>
>> WHERE clause pushdown was implemented in Heikki's version, so I didn't
>> touch around it.  Now I'm working on cost estimation and connection
>> management, but they would need some more work.
>
> So this is still work-in-progress?  When do you expect a final version?

Since it sounds like this still needs more work, I'm going to mark it
Returned with Feedback. I hope we integrate this during the 9.2
cycle.

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