In the first article, we talked about the minimum required functions to be able to add a foreign data wrapper to a PostgreSQL database. Alas, we couldn't do much with it, as it didn't have other capabilities. And one of the most interesting capabilities of a FDW is to access remote datas. In other words, read them.

So, in this blog post, we'll see how we can add reading capabilities to our simple FDW.

In the previous blog post, we said that there are four hook functions to implement to get this capability:

  • BeginForeignScan, to begin executing a foreign scan
  • IterateForeignScan, to fetch one row from the foreign source
  • ReScanForeignScan, to restart the scan from the beginning
  • EndForeignScan, to end the scan and release resources

Actually, we also need some functions used by the planner:

  • Before 9.2
    • PlanForeignScan (<9.2)
  • In 9.2
    • GetForeignRelSize (>=9.2), to obtain relation size estimates for a foreign table
    • GetForeignPaths (>=9.2), to create possible access paths for a scan on a foreign table
    • GetForeignPlan (>=9.2), to create a ForeignScan plan node from the selected foreign access path

We first need to declare the functions:

/*
 * Callback functions
 */

/* Planner functions */
#if (PG_VERSION_NUM >= 90200)
static void simpleGetForeignRelSize(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid);
static void simpleGetForeignPaths(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid);
static ForeignScan *simpleGetForeignPlan(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid,
    ForeignPath *best_path,
    List *tlist,
    List *scan_clauses);
#else
static FdwPlan *simplePlanForeignScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel);
#endif

/* Executor reading functions */
static void simpleBeginForeignScan(ForeignScanState *node, int eflags);
static TupleTableSlot *simpleIterateForeignScan(ForeignScanState *node);
static void simpleReScanForeignScan(ForeignScanState *node);
static void simpleEndForeignScan(ForeignScanState *node);

And we need to set the hooks in the handler function, which looks like this now:

Datum
simple_fdw_handler(PG_FUNCTION_ARGS)
{
	FdwRoutine *fdwroutine = makeNode(FdwRoutine);

	elog(DEBUG1,"entering function %s",__func__);

	/* assign the handlers for the FDW */
#if (PG_VERSION_NUM >= 90200)
	fdwroutine->GetForeignRelSize = simpleGetForeignRelSize;
	fdwroutine->GetForeignPaths = simpleGetForeignPaths;
	fdwroutine->GetForeignPlan = simpleGetForeignPlan;
#else
	fdwroutine->PlanForeignScan = simplePlanForeignScan;
#endif
	fdwroutine->BeginForeignScan = simpleBeginForeignScan;
	fdwroutine->IterateForeignScan = simpleIterateForeignScan;
	fdwroutine->ReScanForeignScan = simpleReScanForeignScan;
	fdwroutine->EndForeignScan = simpleEndForeignScan;

	PG_RETURN_POINTER(fdwroutine);
}

The new code is the eight lines starting with fdwroutine->, and the ifdef statement. They set each of the hook functions we will need to read our remote datas.

For now, let's do something really simple so that we can compile it, run it, and understand how it works. We'll start with this code:

static void
simpleGetForeignRelSize(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid)
{
	SimpleFdwPlanState *fdw_private;

	elog(DEBUG1,"entering function %s",__func__);

	baserel->rows = 0;

	fdw_private = palloc0(sizeof(SimpleFdwPlanState));
	baserel->fdw_private = (void *) fdw_private;
}

static void
simpleGetForeignPaths(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid)
{
	Cost		startup_cost,
				total_cost;

	elog(DEBUG1,"entering function %s",__func__);

	startup_cost = 0;
	total_cost = startup_cost + baserel->rows;

	add_path(baserel, (Path *)
		create_foreignscan_path(root, baserel,
			 baserel->rows,
			 startup_cost,
			 total_cost,
			 NIL,
			 NULL,
			 NIL));
}

static ForeignScan *
simpleGetForeignPlan(PlannerInfo *root,
    RelOptInfo *baserel,
    Oid foreigntableid,
    ForeignPath *best_path,
    List *tlist,
    List *scan_clauses)
{
	Index		scan_relid = baserel->relid;

	elog(DEBUG1,"entering function %s",__func__);

	scan_clauses = extract_actual_clauses(scan_clauses, false);

	return make_foreignscan(tlist,
		scan_clauses,
		scan_relid,
		NIL,
		NIL);
}

static void
simpleBeginForeignScan(ForeignScanState *node,
						  int eflags)
{
	elog(DEBUG1,"entering function %s",__func__);
}


static TupleTableSlot *
simpleIterateForeignScan(ForeignScanState *node)
{
	TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;

	elog(DEBUG1,"entering function %s",__func__);

	ExecClearTuple(slot);

	return slot;
}


static void
simpleReScanForeignScan(ForeignScanState *node)
{
	elog(DEBUG1,"entering function %s",__func__);
}


static void
simpleEndForeignScan(ForeignScanState *node)
{
	elog(DEBUG1,"entering function %s",__func__);
}

That's quite a lot of code. Simply put, all executor void functions just call elog so that we know when the function is called. The only executor returning function is the one returning a tuple. We simply return an empty tuple right now. The planner functions do as less as they can to be able to do a SELECT statement. We'll explain what they do later.

Let's compile that code, and try it.

$ make
cp sql/simple_fdw.sql sql/simple_fdw--0.0.1.sql
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I. -I/opt/postgresql-9.2/include/server -I/opt/postgresql-9.2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/simple_fdw.o src/simple_fdw.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -shared -o simple_fdw.so src/simple_fdw.o -L/opt/postgresql-9.2/lib -Wl,--as-needed -Wl,-rpath,'/opt/postgresql-9.2/lib',--enable-new-dtags  
$ make install
/usr/bin/mkdir -p '/opt/postgresql-9.2/lib'
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/extension'
/usr/bin/mkdir -p '/opt/postgresql-9.2/share/extension'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  simple_fdw.so '/opt/postgresql-9.2/lib/simple_fdw.so'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./simple_fdw.control '/opt/postgresql-9.2/share/extension/'
/bin/sh /opt/postgresql-9.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644  sql/simple_fdw--0.0.1.sql '/opt/postgresql-9.2/share/extension/'

OK, everything is in place. Let's create a database, and add our objects in it:

$ createdb my_simple_test
$ psql my_simple_test 
Expanded display is used automatically.
psql (9.2.4)
Type "help" for help.

my_simple_test=# CREATE EXTENSION simple_fdw;
CREATE EXTENSION
my_simple_test=# CREATE SERVER simple_server FOREIGN DATA WRAPPER simple_fdw;
CREATE SERVER
my_simple_test=# CREATE FOREIGN TABLE simple_table() SERVER simple_server;
CREATE FOREIGN TABLE

Now, let's read our new foreign table, but first we need to make sure we get DEBUG1 log messages:

my_simple_test=# SET client_min_messages TO debug1;
LOG:  duration: 0.188 ms  statement: set client_min_messages to debug1;
SET
my_simple_test=# SELECT * FROM simple_table ;
DEBUG:  entering function simple_fdw_handler
DEBUG:  entering function simpleGetForeignRelSize
DEBUG:  entering function simpleGetForeignPaths
DEBUG:  entering function simpleGetForeignPlan
DEBUG:  entering function simple_fdw_handler
DEBUG:  entering function simpleBeginForeignScan
DEBUG:  entering function simpleIterateForeignScan
DEBUG:  entering function simpleEndForeignScan
LOG:  duration: 0.438 ms  statement: select * from simple_table ;
(No rows)

So, what happened? the SELECT statement is executed in two parts: the planner part, and the executor part. The planner called our handler function to make sure that the hook functions were set. Then it called each of the functions it needed: simpleGetForeignRelSize, simpleGetForeignPaths, and simpleGetForeignPlan, as I'm on a PostgreSQL 9.2 release. Then the executor called once again the handler function. And then it started reading the table: it called simpleBeginForeignScan to prepare the reading, simpleIterateForeignScan as much as needed till it got an empty tuple (so only once in our case), and then simpleEndForeignScan to clean everything that needed to get cleaned. We got no rows because our code was that simple.

Now, we need to make it actually read something. We'll use an sqlite database as our example, but the idea is the same for every FDW you can find.

To read a table on an sqlite database, you need to open a database connection, prepare a query, and then loop on all the tuples returned by your query. At the end, you'll have to close the database connection. Here is a simple code that do it alone (I mean, outside of a FDW):

  sqlite3 *db;
  sqlite3_stmt * pStmt;
  const char * pzTail;

  rc = sqlite3_open("/path/to/database_file.db", &db);
  rc = sqlite3_prepare(db, "SELECT * FROM t1", -1, &pStmt, &pzTail);

  do {
        rc = sqlite3_step(pStmt);
        if (rc == SQLITE_ROW) {
            printf("value col 1 %d\n", sqlite3_column_int(pStmt, 0));
            printf("value col 2 %s\n", sqlite3_column_text(pStmt, 1));
        }
    } while (rc == SQLITE_ROW);

  sqlite3_close(db);

Of course, this is a really simple code, without error checking and stuff like that. Anyway, that's enough to work on our simple FDW. Remember that we have at least three functions to code:

  • simpleBeginForeignScan will open the database connection (sqlite3_open)
  • simpleIterateForeignScan will prepare the query (sqlite3_prepare) and fetch the first tuple (sqlite3_step) if it didn't do that already, otherwise it will only fetch the next tuple (sqlite3_step)
  • and simpleEndForeignScan will close the database connection (sqlite3_close)

The question is: how can we give the connection handle and the statement handle created in the first and second function to the second and third functions? and the answer is: with some private Foreign Data Wrapper structure that will be stored in the ForeignScanState structure.

So, onto the code. The first thing we need to do is add the sqlite header file:

#include <sqlite3.h>

We also need to declare our private data structure:

/*
 * FDW-specific information for ForeignScanState.fdw_state.
 */
typedef struct simpleFdwExecutionState
{
	sqlite3       *conn;
	sqlite3_stmt  *result;
	char          *query;
} SimpleFdwExecutionState;

This structure will contain the connection handle, the statement handle, and the query string.

Now, we will write the simpleBeginForeignScan. It will open the connection, and prepare the query. As our foreign table accepts one option, the table name, we'll take advantage of this to name the table we want to read. We don't yet have the code to get the options back. We'll write a function to get them. Here is its declaration:

static void simpleGetOptions(Oid foreigntableid, char **database, char **table);

And here is its code:

/*
 * Fetch the options for a simple_fdw foreign table.
 */
static void
simpleGetOptions(Oid foreigntableid, char **database, char **table)
{
	ForeignTable   *f_table;
	ForeignServer  *f_server;
	List           *options;
	ListCell       *lc;

	/*
	 * Extract options from FDW objects.
	 */
	f_table = GetForeignTable(foreigntableid);
	f_server = GetForeignServer(f_table->serverid);

	options = NIL;
	options = list_concat(options, f_table->options);
	options = list_concat(options, f_server->options);

	/* Loop through the options */
	foreach(lc, options)
	{
		DefElem *def = (DefElem *) lfirst(lc);

		if (strcmp(def->defname, "database") == 0)
			*database = defGetString(def);

		if (strcmp(def->defname, "table") == 0)
			*table = defGetString(def);
	}

	/* Check we have the options we need to proceed */
	if (!*database && !*table)
		ereport(ERROR,
			(errcode(ERRCODE_SYNTAX_ERROR),
			errmsg("a database and a table must be specified")
			));
}

The code is easy to read. We first get a handle on the foreign table and on the foreign server. We put all options into the options variable, and we loop through it. We check that we have the two options we need and we set the variables for them. It errors out if we don't have the two options we need (ie, the database name and the table name).

Now that we can get the two options through this function, we can write the code of the simpleBeginForeignScan hook function:

static void
simpleBeginForeignScan(ForeignScanState *node,
						  int eflags)
{
	sqlite3                  *db;
	SimpleFdwExecutionState  *festate;
	char                     *svr_database = NULL;
	char                     *svr_table = NULL;
	char                     *query;
    size_t                   len;

	elog(DEBUG1,"entering function %s",__func__);

	/* Fetch options  */
	simpleGetOptions(RelationGetRelid(node->ss.ss_currentRelation), &svr_database, &svr_table);

	/* Connect to the server */
	if (sqlite3_open(svr_database, &db)) {
		ereport(ERROR,
			(errcode(ERRCODE_FDW_OUT_OF_MEMORY),
			errmsg("Can't open sqlite database %s: %s", svr_database, sqlite3_errmsg(db))
			));
		sqlite3_close(db);
	}

	/* Build the query */
    len = strlen(svr_table) + 15;
    query = (char *)palloc(len);
    snprintf(query, len, "SELECT * FROM %s", svr_table);

	/* Stash away the state info we have already */
	festate = (SimpleFdwExecutionState *) palloc(sizeof(SimpleFdwExecutionState));
	node->fdw_state = (void *) festate;
	festate->conn = db;
	festate->result = NULL;
	festate->query = query;
}

Once again, it's quite easy to read. We first grab the database and table names. Then, we open the database with the sqlite3_open function. If it didn't work, we use ereport to report the error. If everything is fine, we build the query string, which will simply be a "SELECT * FROM" and our table name. Finally, we allocate some memory for our SimpleFdwExecutionState structure, and we push the connection handle and the query string in it.

Now that the FDW can open a connection, we need to be able to execute a query, and fetch each resulting row. This is the aim of the simpleIterateForeignScan function. It will prepare the query if it hasn't done so, and then it will fetch the next tuple. Here is its code:

static TupleTableSlot *
simpleIterateForeignScan(ForeignScanState *node)
{
	char        **values;
	HeapTuple   tuple;
	int         x;
	const char  *pzTail;
	int         rc;

	SimpleFdwExecutionState *festate = (SimpleFdwExecutionState *) node->fdw_state;
	TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;

	elog(DEBUG1,"entering function %s",__func__);

	/* Execute the query, if required */
	if (!festate->result)
	{
		rc = sqlite3_prepare(festate->conn, festate->query, -1, &festate->result, &pzTail);
		if (rc!=SQLITE_OK) {
			ereport(ERROR,
				(errcode(ERRCODE_FDW_UNABLE_TO_CREATE_EXECUTION),
				errmsg("SQL error during prepare: %s", sqlite3_errmsg(festate->conn))
				));
			sqlite3_close(festate->conn);
		}
	}

	ExecClearTuple(slot);

	/* get the next record, if any, and fill in the slot */
	if (sqlite3_step(festate->result) == SQLITE_ROW)
	{
		/* Build the tuple */
		values = (char **) palloc(sizeof(char *) * sqlite3_column_count(festate->result));

		for (x = 0; x < sqlite3_column_count(festate->result); x++)
			values[x] = sqlite3_column_text(festate->result, x);

		tuple = BuildTupleFromCStrings(
			TupleDescGetAttInMetadata(node->ss.ss_currentRelation->rd_att),
			values);
		ExecStoreTuple(tuple, slot, InvalidBuffer, false);
	}

	/* then return the slot */
	return slot;
}

sqlite3_column_count gives us the number of columns in the resulting tuple, and we use that in a for loop, to get each of the values. BuildTupleFromCStrings builds a HeapTuple from all gathered values, and ExecStoreTuple stores the HeapTuple in the slot.

Finally, we need the ending function. It will clean every part of memory we allocated to scan the remote table. So, it's pretty simple: we have to deallocate the query string, the statement handle, and the database handle:

static void
simpleEndForeignScan(ForeignScanState *node)
{
	SQLiteFdwExecutionState *festate = (SQLiteFdwExecutionState *) node->fdw_state;

	elog(DEBUG1,"entering function %s",__func__);

	if (festate->result)
	{
		sqlite3_finalize(festate->result);
		festate->result = NULL;
	}

	if (festate->conn)
	{
		sqlite3_close(festate->conn);
		festate->conn = NULL;
	}

	if (festate->query)
	{
		pfree(festate->query);
		festate->query = 0;
	}

}

And we're done. We should test it right away. I'll use the Chinook sample database (file ChinookDatabase1.4_CompleteVersion.zip) as an example database. So here is how we can use the simple FDW:

my_simple_test=# DROP EXTENSION simple_fdw cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to server simple_server
drop cascades to foreign table simple_table
DROP EXTENSION
my_simple_test=# CREATE EXTENSION simple_fdw;
CREATE EXTENSION
my_simple_test=# CREATE SERVER simple_server
    FOREIGN DATA WRAPPER simple_fdw
    OPTIONS (database '/home/guillaume/FDW/example/chinook/Chinook_Sqlite.sqlite');
CREATE SERVER
my_simple_test=# CREATE FOREIGN TABLE genre (g_id integer, g_name text)
    SERVER simple_server
    OPTIONS (table 'Genre');
CREATE FOREIGN TABLE
my_simple_test=# SELECT * FROM genre;
 g_id |       g_name       
------+--------------------
    1 | Rock
    2 | Jazz
    3 | Metal
    4 | Alternative & Punk
    5 | Rock And Roll
    6 | Blues
    7 | Latin
    8 | Reggae
    9 | Pop
   10 | Soundtrack
   11 | Bossa Nova
   12 | Easy Listening
   13 | Heavy Metal
   14 | R&B/Soul
   15 | Electronica/Dance
   16 | World
   17 | Hip Hop/Rap
   18 | Science Fiction
   19 | TV Shows
   20 | Sci Fi & Fantasy
   21 | Drama
   22 | Comedy
   23 | Alternative
   24 | Classical
   25 | Opera
(25 rows)

That worked perfectly!

So, we had to create quite a lof of functions to be able to read some remote data: add some planner hook functions, add some executor reading hook functions, add some helper functions (actually one to get the list of options). That wasn't hard to do, but there are plenty of things to know.

To be honest, we didn't put many thoughts on the planner functions. Even if we get 25 rows, the planner still thinks we'll have 0 rows, and that it will cost nothing to read the remote data. And we didn't use the simpleReScanForeignScan function.

But this is enough for today. We'll go deeper in the planner hook functions in another blog post.

By the way, the simple FDW is available on this github repository: Writing A FDW. This repository will contain all the code written for this serie of blog posts.