Re: minimizing the target list for foreign data wrappers - Mailing list pgsql-hackers

In case anyone is interested, I tried it and it doesn't seem to work.
It looks like some other plan element already has the target-list
tuple baked. Now I'm trying to decide whether to give up on FDW. It's
a shame because it's such a sweet facility, but at this point, I just
don't think that it's mature enough for what I need to do.
Regards,
David Gudeman


On Mon, Apr 22, 2013 at 11:27 AM, David Gudeman <dave.gudeman@gmail.com> wrote:
> Re-reading my first email I thought it was a little confusing, so here
> is some clarification. In GetForeignPlan, tlist seems to be a target
> list for a basic "select *" from the foreign table. For the ith
> TargetEntry te in tlist, it seems that te->expr is a var with
> varattno=i. I was mis-remembering and calling varattno "attrno" in the
> original email.
>
> My assumption is that the plan elements that use the output of the FDW
> plan node will access columns indirectly using tlist. In other words,
> I'm assuming that if there is a reference to a column c of the foreign
> table, this column will be represented as a Var with varattno being an
> offset into tlist. So if c is column number 3, for example, you get
> its value by looking up TargetEntry number 3 in tlist and evaluate the
> expr column for that TargetEntry. So if I change the  Var in the expr
> column so the varattno points to a different column in the output
> tuple, then everything will work.
>
> The two risky assumptions I'm making are 1. that it actually uses this
> indirect way of looking up columns in a foreign table and 2. that it
> actually uses the tlist that I pass in when I call make_foreignscan().
>
> Can anyone confirm or deny these assumptions?
>
> Thanks.
>
> On Sun, Apr 21, 2013 at 6:57 PM, David Gudeman <dave.gudeman@gmail.com> wrote:
>>
>> A few years ago I wrote a roll-your-own foreign-data-wrapper system for Postgres because Postgres didn't have one at
thetime (some details here (http://unobtainabol.blogspot.com/2013/04/dave-foreign-data-introuction.html) if anyone is
interested).Now I'm being tasked to move it to Postgres 9.2.x and I'd like to use FDW if possible. 
>>
>> One of the problems I'm having is that in my application, the foreign tables typically have hundreds of columns
whiletypical queries only access a dozen or so (the foreign server is a columnar SQL database). Furthermore, there is
nosize optimization for NULL values passed back from the foreign server, so if I return all of the columns from the
table--even as NULLs-- the returned data size will be several times the size that it needs to be. My application cannot
toleratethis level of inefficiency, so I need to return minimal columns from the foreign table. 
>>
>> The documentation doesn't say how to do this, but looking at the code I think it is possible. In GetForeignPlan()
youhave to pass on the tlist argument, which I presume means that the query plan will use the tlist that I pass in,
right?If so, then it should be possible for me to write a function that takes tlist and baserel->reltargetlist and
returna version of tlist that knows which foreign-table columns are actually used, and replaces the rest with a NULL
constant.
>>
>> For example, suppose the original tlist is this: [VAR(attrno=1), VAR(attrno=2), VAR(attrno=3)] and reltarget list
saysthat I only need args 1 and 3. Then the new tlist would look like this: [VAR(attrno=1), CONST(val=NULL),
VAR(attrno=2)]where the attrno of the last VAR has been reduced by one because the 2 column is no longer there. 
>>
>> I did something very much like this in my roll-your-own version of FDW so I know basically how to do it, but I did
itat the pre-planning stage and I'm not sure how much is already packed into the other plan nodes at this point. Maybe
it'stoo late to change the target list? 
>>
>> Can anyone give me some advice or warnings on this? I'd hate to go to the trouble of implementing and testing it
onlyto find that I'm making some bogus assumptions. 
>>
>> Thanks,
>> David Gudeman
>>