Skip to content

GSoC2011 Proposal

Project Title

Enhancing Foreign-data Wrapper (FDW) functionality for PostgreSQL

Name of proposer and Email

Zheng Yang <zhengyang4k@gmail.com>

Synopsis

PostgreSQL foreign-data wrapper (FDW) functionality is still very rudimentary: there is no support for parsing complex quals and joined query is not supported by current version of FDW API. My plan for this summer is to firstly implement a CouchDB FDW, followed by developing a generic ODBC FDW for windows and one of the *nix platform (probably on Ubuntu), last but not least to add join pushdown support for FDW API.

Benefits to the PostgreSQL Community

  1. CouchDB is a key-value datastore using NoSql. A CouchDB FDW can help PostgreSQL to collaborate with this famous post-modern database.
  2. ODBC FDW gives PostgreSQl access to a larger variety of RDBMS as long as they have implemented ODBC interface.
  3. Adding join pushdown feature in FDW API will enable PostgreSQL to do more complex operation (join) on foreign tables.
  4. Generic Web Service FDW that can access to a SOAP/RESTful resource.

Quantifiable results

  • 1. Implement a CouchDB FDW that is capable of read-only access to CouchDB’s document object.
  • 2. Implement a ODBC FDW that is capable of read-only access to databases that are ODBC driver enabled on Windows (using Microsoft Driver Manager) and Linux (using iODBC).
  • 3. Adding functionalities in FDW API that enables reading multiple foreign tables to support join operation.
  • 4. Enhance the current FDW API to provide utility for understanding complex quals, hence improving the foreign table scan efficiency.
  • 5*. If there is additional time, I am also interested in implementing this generic Web service FDW that can access to a SOAP/RESTful resource with URI and field mapping specified in FDW options as suggested by Jehan-Guillaume (ioguix) de Rorthais.

Project Details

  1. 1. CouchDB FDW: 
    In general, the main work of developing a FDW is to implement the 6 callback functions:
  • * PlanForeignScan
    * ExplainForeignScan
    * BeginForeignScan
    * IterateForeignScan
    * ReScanForeignScan
    * EndForeignScan
  1. I will be using quals property inside ScanState->Plan node to extract the condition in WHERE clause and using libcurl and libjson to call and parse relative services provided by CouchDB RESTful/JSON API,  based on the qualifications extracted from the SELECT statement.
  2. ODBC FDW:
    Similar manner will be used to create a cross platform FDW that can access databases that have implemented ODBC interface (Windows driver manager and iODBC driver manager).
  3. Join pushdown & Complex quals pushdown:
    Enable FDW to open several foreign tables at the same time to perform join operation. The conditions for join and WHERE qualifications will be pushed down to the foreign side.
    Complex quals with UDFs will need a mapping on a per DBMS basis.
  4. If there is additional time, I am also interested in implementing the generic Web service FDW that can access to a SOAP/RESTful resource with URI and field mapping specified in FDW options.

Inch-stones

1. CouchDB FDW

  • * Study on CouchDB RESTful HTTP/JSON API
  • * Implementing 6 callback functions (will need to dig a bit further to get the quals inside the ScanState->PlanState as what Andrew has suggested in the mailing list)
  • * Testing and Debugging
  • * Documentation

2. ODBC FDW

  • * Study on iODBC driver manager and Microsoft driver manager
  • * Implementing 6 callback functions (the FDW should be able to understand complex quals)
  • * Testing and Debugging
  • * Documentation

3. Join pushdown

  • * Enable access multiple foreign table at the same time.
  • * Optimize join efficiency on foreign tables by applying pre-join on foreign side.
  • * Testing and Debugging
  • * Documentation

4. Complex Qual parsing
  • * With the experience from implementing ODBC FDW. see if we are able to come up with a unified service interface for parsing complex quals.
  • * Testing and Debugging
  • * Documentation

Project Schedule

Estimated duration: From 27 April (Accepted student proposal announced) to 16 August (Suggested ‘pencil down’ date) ~ (about 16 weeks)

Draft roadmap:

1. Late April 2011 (Week 1): Research & reading mysql_fdw and redis_fdw

2. May 2011 (Week 2~5):

  • 1. Warm up and implement CouchDB FDW.
  • 2. Implement ODBC FDW for iODBC driver on one of the linux distribution.

3. June 2011 (Week 6~9):

  • 1. Implement ODBC FDW for Microsoft driver on Windows.
  • 2. Start discuss and implement the join pushdown support for FDW API

4. July 2011 (Week 10~13): Finish join pushdown feature.

5. August 2011 (Week 14~16):

  • 1. Adding service for parsing complex quals for FDW API.
  • 2. Documentation and Testing. (Implement Web Service FDW if there is enough time left)

Completeness Criteria

Create a CouchDB FDW and a OCBC FDW with the features listed in quantifiable results.

Bio

I am Zheng Yang from School of Computing, National University of Singapore (NUS), currently a 3rd year computing student.
I am very interested in database technology and hope this project can help me to understand more on PostgreSQL,  this world’s most advanced open source database.
I have attached a CV in an external link <http://nine-chapters.com/resume/zhengyang_gsoc.pdf>

3 Comments

  1. Adelie wrote:

    I can arleady tell that’s gonna be super helpful.

    Sunday, August 7, 2011 at 12:46 pm | Permalink
  2. Aeron Chair wrote:

    Great! thanks for the share!

    Sunday, November 20, 2011 at 3:49 am | Permalink
  3. Jean wrote:

    I suggest adding a facebook like button for the blog!

    Tuesday, November 29, 2011 at 9:11 am | Permalink

Post a Comment

Your email is never published nor shared. Required fields are marked *
*
*