Re: New hook after raw parsing, before analyze - Mailing list pgsql-hackers

Thanks for the link.

I want flexibility. Here is a situation: my hook knows the size of tableA and tableB on the legacy side. It should be
ableto decide wether to offload the join/filter onto the legacy side or not. At the same time it can start transferring
thedata to real Postgres tables in the background. When the data is here in the local DB it can change the rewrite to
usethe local tables instead for the new queries. 

Answering your question:

I don’t know what you mean by pseudo table. The “fdw_tableA_tableB” is a real FDW table with a catalog entry, with real
catalogcolumns. tableA and tableB are non-real tables in my setup. My understanding is the raw_parse-transform-analyze
triplefirst checks the catalog in the transform phase. That’s why I want the hook to be after raw_parse but before
transform,so the transform phase won’t see tableA, tableB and their columns, thus I don’t expect errors. 

Thank you, David


Please find my answers inline.

2014.02.13. dátummal, 13:28 időpontban Kohei KaiGai <kaigai@kaigai.gr.jp> írta:

> See the discussion of Custom-Scan API.
> https://commitfest.postgresql.org/action/patch_view?id=1282
>
> I believe my third patch is what you really want to do...
>
>> This rewritten query would be handled by the FDW table that I previously added to the catalog.
>>
>> The reason I want this new hook is that I don't want tableA and tableB to be in the catalog.
>>
> I'd like to see why you wants the pseudo table "fdw_tableA_tableB" to
> be in the catalog,
> instead of the "tableA" and "tableB". In addition, parser shall raise
> an error if referenced
> columns (as a part of "tableA" or "tableB") are not in-catalog because
> of name lookup
> error.
>
> Thanks,
>
>
> 2014-02-13 19:01 GMT+09:00 David Beck <dbeck@starschema.net>:
>> Hello Hackers,
>>
>> I work on a foreign data wrapper for a legacy system. I generally find the hook system very useful and flexible way
toextend Postgres. 
>> The post parse analyze hook almost fits what I need, but I have a few use cases where I would need to tap right into
theparsed queries but before any catalog based validation is done. 
>> Please find the attached trivial patch for this new hook.
>>
>> One of the use cases I have is this:
>>
>> I have table like data structures in the source system for the FDW I work on.
>> These tables are sometimes too big and the source system is able to filter and join them with limitations, thus it
isnot optimal to transfer the data to Postgres. 
>> At the same time I want the users to think in terms of the original tables.
>>
>> The idea is to rewrite the SQL queries like this:
>>
>>  "SELECT * FROM tableA a, tableB b WHERE a.id=b.id AND a.col1=1234 AND b.col2=987"
>>
>> to:
>>
>>  "SELECT * FROM fdw_tableA_tableB ab WHERE ab.col1=1234 AND ab.col2=987"
>>
>>
>> This rewritten query would be handled by the FDW table that I previously added to the catalog.
>>
>> The reason I want this new hook is that I don't want tableA and tableB to be in the catalog.
>>
>> Looking forward to hear your thoughts, opinions, comments.
>>
>> Best regards, David
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
>
>
> --
> KaiGai Kohei <kaigai@kaigai.gr.jp>