Re: New hook after raw parsing, before analyze

2014.02.15. dátummal, 0:46 időpontban Greg Stark <stark(at)mit(dot)edu> írta:

> On Fri, Feb 14, 2014 at 9:16 PM, David Beck <dbeck(at)starschema(dot)net> wrote:
>> Another point I liked in mysql is the possibility to write info schema plugins: http://dev.mysql.com/doc/refman/5.1/en/writing-information-schema-plugins.html
>> Like a virtual catalog. Is there anything similar in Postgres?
>
> The documentation you linked to describes how to provide
> information_schema plugins but not why you would want to do such a
> thing. I'm not seeing why this would be useful. The information_schema
> schema is described by the standard so creating new views in it isn't
> needed very often and the schema for the existing views doesn't change
> very often. I can see why a plugin might want to add rows to the views
> but that doesn't seem to be what this feature is about.

Another reason I was thinking about dynamic catalog and/or query rewrite is the project I work on is a data integration platform. Right now it is in the feasibility study phase and Postgres+extension looks to be the strongest option.

The legacy system we want to interface with has over 150k table like objects. Our platform’s task is to provide a relational view on top of them.

I know that it is unlikely the users to use all 150k tables. I would expect may be 10-100 are used in practice, but I didn’t want to figure out which 100, neither want to create all 150k catalog entries in advance.

I was also dreaming about the possibility to transfer the small enough objects to Postgres tables in the background and spare the communication with the legacy system and let Postgres do the joins on these.

The solution I was thinking about is this:

- when the query arrives a smart rewrite would know 1) what tables are local 2) what tables need new catalog entries 3) what can be joined on the other side
- the rewriter would potentially add SQL statements in the beginning of the query for creating the missing FDW catalog entries if needed
- the FDW would be handled by the same extension so they can easily talk to each other about the status of the objects, so the rewriter would know if the background transfer of the small table is completed and should do the rewrite accordingly

I know these are pretty far from the functionality and traditional operation of an RDBMS… but if you look at the FDW examples like do a select on a Google Imap mailbox, it is not that far from Postgres

Best regards, David