January 1, 2014

PostgreSQL | firebird_fdw - A Foreign Data Wrapper (FDW) for Firebird

Well I've finally fulfilled one of my 2013 New Year's resolutions, which was to write some kind of foreign data wrapper, so with no further ado here is the initial implementation. It is basically working but is still very much in the proof-of-concept/unstable/may-eat-your-data stage. Having said that, if anyone has a practical use for this I'd be very interested to hear about it - my main motivation for creating this FDW is to learn more about PostgreSQL internals.

Why Firebird?

I've been playing around with Firebird on and off for quite a few years, mainly to sanity-check SQL against a RDBMS with a reasonably sane implementation. It's a nice database, albeit hampered by a lack of resources, especially for documentation and development. I've never had an occasion to use it for a serious project or even encountered an installation in the wild, though it does seem to be popular in Russia and Brazil among other places, and given its Borland heritage it's not surprising that it's the go-to database of choice for Delphi developers. Anyway I thought I'd give it a crack as it wasn't on the list.

Wrapping the Firebird API - libfq

The first problem was that I had no idea about the Firebird C API, or even if there was one. There is, of course, and its documentation consists primarily of this PDF dating from 1999 covering Interbase 6 augmented with whatever information could be gleaned from various mailing lists, blogs and perusing the source of Firebird drivers such as DBD::Firebird. The second problem was that the C API is quite low level and somewhat cumbersome to work with - to cut a long story short I ended up creating a wrapper library, "libfq", to encapuslate the pain. As the name suggests it has similarities to libpq (from which I took the liberty of borrowing some utility code), although it is by no means a 1:1 emulation and is still feature-incomplete. It did also enable me to create a simple command-line client for Firebird, fbsql, which (surprise suprise) is inspired by psql (though to be precise, fbsql was the testbed for developing libfq and getting familiar with Firebird before attacking the FDW itself).

Creating the FDW

Andrew Dunstan's blackhole_fdw was an excellent starting point, providing a skeleton framework pre-commented with relevant excerpts from the documentation. The FDWs for other RDBMSs (postgres_fdw, mysql_fdw and oracle_fdw) allowed me to peek over the shoulders of giants and examine useful examples of how to create a working implementation.

Biggest sticking point was working out how to pass a unique row identifier (Firebird provides a system column "RDB$DB_KEY" to identify individual rows, which is a bit of a strange beast as it is output as a text datatype but the data received is actually a 64 bit integer which requires all sorts of handling exceptions, but that's a whole other story) through to the FDW update and delete functions. I ended up smuggling it, split into two 4-byte segments, through the PostgreSQL tuple header's CTID and OID fields.


I ended up spending a lot more time than anticipated working on the Firebird end of things, which means the FDW functionality is still pretty basic. The to-do list, in no particular order, is:

  • add Firebird transaction supports
  • add character set/encoding support
  • add support for incomplete datatypes (primarily BLOBs and better TIMESTAMP support)
  • add connection caching
  • add pushdowns

(Note: the items on the above list have since been largely implemented.)

Posted at 8:28 AM