Re: GSoC 2012

Hello all, again.

Finally, I have made a decision to work on Firebird FDW. I'm sorry for length of the letter, here is a list of content (if you wont read whole the message):
1. What I was doing during this time (from my first message to this one) and Why I finally decided to implement Firebird FDW?
2. What i want from Firebird FDW?
3. Specific questions I have to solve before proposing quantifiable results and schedule.

1. There is many interesting projects in TODO list, but I have not found any with which I am at least on 50% familiar as I am with Firebird's architecture or with Firebird's API, so I can't assure the completeness of such projects. Furthermore, I still remember how I was digging into internals of another projects and I am not as familiar with PostgreSQL that I have to be for doing something in its Core. So, I started looking for information about FDWs. I have done:
1) I got PostgreSQL source code and built it on Ubuntu. I opened project using Eclipse and I tried to debug forked postgresql process with client connected to it, to be sure that it works fine.
2) I used file_fdw as client, looked at its sources to get first impression about FDW internals and also tried to debug it, to be sure that I understand how to do it within Eclipse.
3) I have read client's and developer's documentation about FDWs; looked at FDW list on wiki (to choose something more complex than file_fdw to study and to estimate complexity of Firebird's one); Have read 2011 mail archive with FDW questions and looked at 2 presentations.
4) Tried to use mysql_fdw, but as I understood it uses old FDW API, so I downloaded Oracle_fdw to be familiar with modern API. Unfortunaly, Oracle-xe was not working on my Ubuntu (Probably, it works well, but I have to spend more time to set it up. May be I'll return to it, if it will be necessary). Anyway I was studying source code, readme and changelog and I found there several interesting optimizations, such as where push-down and connection pool (in library cache). At this moment my interest in Firebird FDW strongly grew up :) and I started to think about "quantifiable results".

2. Of course, I want Firebird FDW to be as good as Oracle's one is:) But, I cant be sure, that I'll complete it all in 3-4 month. So I have to choose most important of features to be implemented first. Next list contains features I want to implement marked as "+" for most important features and "?" for features I can implement later:
+ Types compatibility should be as full as it is possible to implement.
+ Translate most common Firebird SQL's and API's errors to PostgreSQL's analogues.
+ Plan and Cost output [3.1].
? Connections pool optimization (as it is in Oracle).
? Predicates push-down (may be not only "where", to handle situations like it was described here: http://archives.postgresql.org/pgsql-students/2011-03/msg00036.php).
? Field compatibility (link fields not only on their position, but on type, name, and some other properties [3.2]).

3. Here is several (2 at this moment) questions I have to solve before cost estimation and schedule creation can be done:
1) Firebird shows very simple query plan without cardinality or selectivity values. Furthermore, it even does not maintain it internally (as I know it can be implemented in Firebird 3, but anyway I have to think about cost estimation depending on lack of statistics). So. probably, I have to get cardinality as Count(...) and then let PostgreSQL to estimate cost and selectivity based on heuristic rules. I'm pretty sure in positive answer, but "Can PostgreSQL do it?"
2) According to http://people.planetpostgresql.org/andrew/uploads/fdw2.pdf FKs, PKs, Constraints and defaults are not implemented because PostgreSQL can't manage them on foreign tables. NOT NULL is maintained because it can't be changed in foreign DBMS, so we can be sure in it (actually field can be recreated, so we can't be sure. Btw is it taken into account in Oracle FDW?), if I am right. But we can map all of these field properties from Firebird's metadata to PostgreSQL's (FK only between foreign tables, not between servers). To check it for every user's query is too high prise for such mapping, but (I am not sure about it, because we have something like read-only transactions) as Firebird implements multi-version transaction system - we can be sure that in context of one transaction this values cant be changed. And we can track it inside of connection pool. We can get several advantages from it:
- It will be necessary if FDWs will be not read-only in future.
- I think, PostgreSQL optimizer can make better decisions based on constraints sometimes?
- And, finally, it can be useful for database user/client to know a character of data.


--
Thank you!
Smyatkin Maxim.